RMAN的备份及恢复-丢失所有控制文件

　　1.数据库基本信息
　　
　　[oracle@standby oracle]$ sqlplus "/ as sysdba"
　　
　　SQL*Plus: Release 9.2.0.4.0 - Production on Wed Mar 9 09:55:14 2005
　　
　　Copyright (c) 1982, 2002, Oracle Corporation.　All rights reserved.
　　
　　Connected to:
　　Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
　　With the Partitioning option
　　JServer Release 9.2.0.4.0 - Production
　　
　　SQL> select name from v$datafile;
　　
　　NAME
　　--------------------------------------------------------------------------------
　　/opt/oracle/oradata/primary/system01.dbf
　　/opt/oracle/oradata/primary/undotbs01.dbf
　　/opt/oracle/oradata/primary/users01.dbf
　　
　　SQL> archive log list;
　　Database log mode　　　　　Archive Mode
　　Automatic archival　　　　 Enabled
　　Archive destination　　　　/opt/oracle/oradata/primary/archive
　　Oldest online log sequence　　 122
　　Next log sequence to archive　 124
　　Current log sequence　　　 124 网管网www_bitscn_com
　　SQL> select name from v$archived_log;
　　
　　NAME
　　--------------------------------------------------------------------------------
　　/opt/oracle/oradata/primary/archive/1_109.dbf
　　/opt/oracle/oradata/primary/archive/1_110.dbf
　　/opt/oracle/oradata/primary/archive/1_111.dbf
　　/opt/oracle/oradata/primary/archive/1_112.dbf
　　/opt/oracle/oradata/primary/archive/1_113.dbf
　　/opt/oracle/oradata/primary/archive/1_114.dbf
　　/opt/oracle/oradata/primary/archive/1_115.dbf
　　/opt/oracle/oradata/primary/archive/1_116.dbf
　　/opt/oracle/oradata/primary/archive/1_117.dbf
　　/opt/oracle/oradata/primary/archive/1_118.dbf
　　/opt/oracle/oradata/primary/archive/1_119.dbf
　　
　　NAME
　　--------------------------------------------------------------------------------
　　/opt/oracle/oradata/primary/archive/1_120.dbf
　　/opt/oracle/oradata/primary/archive/1_121.dbf
　　/opt/oracle/oradata/primary/archive/1_122.dbf
　　/opt/oracle/oradata/primary/archive/1_123.dbf
　　
　　15 rows selected.
　　
　　SQL> select name from v$controlfile;
　　
　　NAME
　　--------------------------------------------------------------------------------
　　/opt/oracle/oradata/primary/control01.ctl
　　/opt/oracle/oradata/primary/control02.ctl
　　/opt/oracle/oradata/primary/control03.ctl
　　
　　SQL> exit
　　Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
　　With the Partitioning option
　　JServer Release 9.2.0.4.0 - Production
　　
　　2.启用控制文件的自动备份
　　
　　[oracle@standby oracle]$ rman target /
　　
　　Recovery Manager: Release 9.2.0.4.0 - Production
　　
　　Copyright (c) 1995, 2002, Oracle Corporation.　All rights reserved.
　　
　　connected to target database: PRIMARY (DBID=1367687269)
　　
　　RMAN> configure controlfile autobackup on;
　　
　　using target database controlfile instead of recovery catalog
　　new RMAN configuration parameters:
　　CONFIGURE CONTROLFILE AUTOBACKUP ON;
　　new RMAN configuration parameters are successfully stored
　　
　　RMAN> exit
　　
　　Recovery Manager complete.
　　
　　3.执行RMAN全备份
　　
　　[oracle@standby oracle]$ ls
　　10g　admin　dictionary.ora　initprimary.ora　jre　oradata　oraInventory　oui
　　[oracle@standby oracle]$ mkdir orabak
　　[oracle@standby oracle]$ rman target /
　　
　　Recovery Manager: Release 9.2.0.4.0 - Production
　　
　　Copyright (c) 1995, 2002, Oracle Corporation.　All rights reserved.
　　
　　connected to target database: PRIMARY (DBID=1367687269)
　　
　　RMAN> run {
　　2> backup database
　　3> format '/opt/oracle/orabak/full_%d_%T_%s'
　　4> plus archivelog
　　5> format '/opt/oracle/orabak/arch_%d_%T_%s'
　　6> delete all input; }
　　
　　Starting backup at 09-MAR-05
　　current log archived
　　using target database controlfile instead of recovery catalog
　　allocated channel: ORA_DISK_1 中国网管论坛bbs.bitsCN.com
　　channel ORA_DISK_1: sid=14 devtype=DISK
　　channel ORA_DISK_1: starting archive log backupset
　　channel ORA_DISK_1: specifying archive log(s) in backup set
　　input archive log thread=1 sequence=109 recid=1 stamp=539688042
　　input archive log thread=1 sequence=110 recid=2 stamp=539688042
　　input archive log thread=1 sequence=111 recid=3 stamp=539688043
　　input archive log thread=1 sequence=112 recid=4 stamp=539735252
　　input archive log thread=1 sequence=113 recid=5 stamp=539789259
　　input archive log thread=1 sequence=114 recid=6 stamp=539844028
　　input archive log thread=1 sequence=115 recid=7 stamp=539899304
　　input archive log thread=1 sequence=116 recid=8 stamp=539954539
　　input archive log thread=1 sequence=117 recid=9 stamp=539972835
　　input archive log thread=1 sequence=118 recid=10 stamp=541574463
　　input archive log thread=1 sequence=119 recid=11 stamp=543757271
　　input archive log thread=1 sequence=120 recid=12 stamp=545854003 中国网管论坛bbs.bitsCN.com
　　input archive log thread=1 sequence=121 recid=13 stamp=547951007
　　input archive log thread=1 sequence=122 recid=14 stamp=550047742
　　input archive log thread=1 sequence=123 recid=15 stamp=552403943
　　input archive log thread=1 sequence=124 recid=16 stamp=552478112
　　channel ORA_DISK_1: starting piece 1 at 09-MAR-05
　　channel ORA_DISK_1: finished piece 1 at 09-MAR-05
　　piece handle=/opt/oracle/orabak/arch_PRIMARY_20050309_1 comment=NONE
　　channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
　　channel ORA_DISK_1: deleting archive log(s)
　　archive log filename=/opt/oracle/oradata/primary/archive/1_109.dbf recid=1 stamp=539688042
　　archive log filename=/opt/oracle/oradata/primary/archive/1_110.dbf recid=2 stamp=539688042
　　archive log filename=/opt/oracle/oradata/primary/archive/1_111.dbf recid=3 stamp=539688043
　　archive log filename=/opt/oracle/oradata/primary/archive/1_112.dbf recid=4 stamp=539735252
　　archive log filename=/opt/oracle/oradata/primary/archive/1_113.dbf recid=5 stamp=539789259

网管u家u.bitsCN.com

　　archive log filename=/opt/oracle/oradata/primary/archive/1_114.dbf recid=6 stamp=539844028
　　archive log filename=/opt/oracle/oradata/primary/archive/1_115.dbf recid=7 stamp=539899304
　　archive log filename=/opt/oracle/oradata/primary/archive/1_116.dbf recid=8 stamp=539954539
　　archive log filename=/opt/oracle/oradata/primary/archive/1_117.dbf recid=9 stamp=539972835
　　archive log filename=/opt/oracle/oradata/primary/archive/1_118.dbf recid=10 stamp=541574463
　　archive log filename=/opt/oracle/oradata/primary/archive/1_119.dbf recid=11 stamp=543757271
　　archive log filename=/opt/oracle/oradata/primary/archive/1_120.dbf recid=12 stamp=545854003
　　archive log filename=/opt/oracle/oradata/primary/archive/1_121.dbf recid=13 stamp=547951007
　　archive log filename=/opt/oracle/oradata/primary/archive/1_122.dbf recid=14 stamp=550047742
　　archive log filename=/opt/oracle/oradata/primary/archive/1_123.dbf recid=15 stamp=552403943
　　archive log filename=/opt/oracle/oradata/primary/archive/1_124.dbf recid=16 stamp=552478112
　　Finished backup at 09-MAR-05
　　
　　Starting backup at 09-MAR-05
　　using channel ORA_DISK_1
　　channel ORA_DISK_1: starting full datafile backupset
　　channel ORA_DISK_1: specifying datafile(s) in backupset
　　input datafile fno=00001 name=/opt/oracle/oradata/primary/system01.dbf
　　input datafile fno=00002 name=/opt/oracle/oradata/primary/undotbs01.dbf
　　input datafile fno=00003 name=/opt/oracle/oradata/primary/users01.dbf
　　channel ORA_DISK_1: starting piece 1 at 09-MAR-05
　　channel ORA_DISK_1: finished piece 1 at 09-MAR-05
　　piece handle=/opt/oracle/orabak/full_PRIMARY_20050309_2 comment=NONE
　　channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
　　Finished backup at 09-MAR-05
　　
　　Starting backup at 09-MAR-05
　　current log archived
　　using channel ORA_DISK_1
　　channel ORA_DISK_1: starting archive log backupset
　　channel ORA_DISK_1: specifying archive log(s) in backup set
　　input archive log thread=1 sequence=125 recid=17 stamp=552478150 网管联盟bitsCN_com
　　channel ORA_DISK_1: starting piece 1 at 09-MAR-05
　　channel ORA_DISK_1: finished piece 1 at 09-MAR-05
　　piece handle=/opt/oracle/orabak/arch_PRIMARY_20050309_3 comment=NONE
　　channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
　　channel ORA_DISK_1: deleting archive log(s)
　　archive log filename=/opt/oracle/oradata/primary/archive/1_125.dbf recid=17 stamp=552478150
　　Finished backup at 09-MAR-05
　　
　　Starting Control File and SPFILE Autobackup at 09-MAR-05
　　piece handle=/opt/oracle/product/9.2.0/dbs/c-1367687269-20050309-00 comment=NONE
　　Finished Control File and SPFILE Autobackup at 09-MAR-05
　　
　　RMAN> exit
　　
　　Recovery Manager complete.
　　
　　
　　[NextPage]
　　
　　4.移除所有控制文件及数据文件
　　
　　[oracle@standby oracle]$ sqlplus "/ as sysdba"
　　
　　SQL*Plus: Release 9.2.0.4.0 - Production on Wed Mar 9 10:11:23 2005
　　
　　Copyright (c) 1982, 2002, Oracle Corporation.　All rights reserved. 中国网管论坛bbs.bitsCN.com
　　
　　Connected to:
　　Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
　　With the Partitioning option
　　JServer Release 9.2.0.4.0 - Production
　　
　　SQL> shutdown immediate;
　　Database closed.
　　Database dismounted.
　　ORACLE instance shut down.
　　SQL> exit
　　Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
　　With the Partitioning option
　　JServer Release 9.2.0.4.0 - Production
　　[oracle@standby oracle]$ cd oradata/
　　[oracle@standby oradata]$ ls
　　primary
　　
　　[oracle@standby oradata]$ mv primary/ primarybak
　　[oracle@standby oradata]$ mkdir primary
　　[oracle@standby oradata]$ ls
　　primary　primarybak
　　
　　5.从自动备份中恢复控制文件
　　
　　[oracle@standby oradata]$ rman target /
　　
　　Recovery Manager: Release 9.2.0.4.0 - Production
　　
　　Copyright (c) 1995, 2002, Oracle Corporation.　All rights reserved.
　　
　　connected to target database (not started)
　　
　　RMAN> startup nomount;
　　
　　Oracle instance started
　　
　　Total System Global Area　　 135337420 bytes
　　
　　Fixed Size　　　　　　　452044 bytes
　　Variable Size　　　　109051904 bytes
　　Database Buffers　　　　　25165824 bytes
　　Redo Buffers　　　　　　667648 bytes
　　
　　RMAN> restore controlfile to '/opt/oracle/oradata/control01.ctl' from autobackup;
　　
　　Starting restore at 09-MAR-05
　　
　　using target database controlfile instead of recovery catalog
　　allocated channel: ORA_DISK_1
　　channel ORA_DISK_1: sid=11 devtype=DISK
　　RMAN-00571: ===========================================================
　　RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
　　RMAN-00571: ===========================================================
　　RMAN-03002: failure of restore command at 03/09/2005 10:15:05
　　RMAN-06495: must explicitly specify DBID with SET DBID command
　　
　　RMAN> set DBID=1367687269 网管网www.bitscn.com
　　
　　executing command: SET DBID
　　
　　RMAN> restore controlfile to '/opt/oracle/oradata/control01.ctl' from autobackup;
　　
　　Starting restore at 09-MAR-05
　　
　　using channel ORA_DISK_1
　　channel ORA_DISK_1: looking for autobackup on day: 20050309
　　channel ORA_DISK_1: autobackup found: c-1367687269-20050309-00
　　channel ORA_DISK_1: controlfile restore from autobackup complete
　　Finished restore at 09-MAR-05
　　
　　RMAN> exit
　　
　　Recovery Manager complete.
　　
　　6.你可能需要修改spfile文件
　　
　　当然如果文件位置等信息没有变化就无需修改
　　
　　[oracle@standby oradata]$ sqlplus '/ as sysdba'
　　
　　SQL*Plus: Release 9.2.0.4.0 - Production on Wed Mar 9 10:19:53 2005
　　
　　Copyright (c) 1982, 2002, Oracle Corporation.　All rights reserved.
　　
　　Connected to:
　　Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
　　With the Partitioning option
　　JServer Release 9.2.0.4.0 - Production

网管网www.bitscn.com

　　
　　SQL> create pfile from spfile;
　　
　　File created.
　　
　　SQL> !
　　[oracle@standby oradata]$ cd $ORACLE_HOME/dbs
　　[oracle@standby dbs]$ vi initprimary.ora
　　
　　*.aq_tm_processes=0
　　*.background_dump_dest='/opt/oracle/admin/primary/bdump'
　　*.compatible='9.2.0.0.0'
　　*.control_files='/opt/oracle/oradata/control01.ctl'
　　*.core_dump_dest='/opt/oracle/admin/primary/cdump'
　　*.db_block_size=8192
　　*.db_cache_size=25165824
　　*.db_domain=''
　　*.db_file_multiblock_read_count=16
　　*.db_name='primary'
　　*.fast_start_mttr_target=300
　　*.hash_join_enabled=TRUE
　　*.instance_name='primary'
　　*.java_pool_size=0
　　*.job_queue_processes=0
　　*.large_pool_size=8388608
　　*.log_archive_dest_1='LOCATION=/opt/oracle/oradata/primary/archive'
　　*.log_archive_format='%t_%s.dbf'
　　*.log_archive_start=true
　　*.open_cursors=300
　　*.pga_aggregate_target=25165824
　　*.processes=150
　　*.query_rewrite_enabled='FALSE'

中国网管联盟bitsCN.com

　　*.remote_login_passwordfile='EXCLUSIVE'
　　*.resource_manager_plan='SYSTEM_PLAN'
　　*.shared_pool_size=83886080
　　*.sort_area_size=524288
　　*.star_transformation_enabled='FALSE'
　　*.timed_statistics=TRUE
　　*.undo_management='AUTO'
　　*.undo_retention=10800
　　*.undo_tablespace='UNDOTBS1'
　　*.user_dump_dest='/opt/oracle/admin/primary/udump'
　　*.utl_file_dir='/opt/oracle'
　　~
　　~
　　~
　　~
　　~
　　"initprimary.ora" 34L, 1044C written
　　[oracle@standby dbs]$ exit
　　exit
　　
　　SQL> shutdown immediate;
　　ORA-01507: database not mounted
　　
　　ORACLE instance shut down.
　　SQL> create spfile from pfile;
　　
　　File created.
　　
　　SQL> startup mount;
　　ORACLE instance started.
　　
　　Total System Global Area　135337420 bytes
　　Fixed Size　　　　　 452044 bytes
　　Variable Size　　　　 109051904 bytes
　　Database Buffers　　　 25165824 bytes
　　Redo Buffers　　　　 667648 bytes 网管bitscn_com
　　Database mounted.
　　SQL> exit
　　Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
　　With the Partitioning option
　　JServer Release 9.2.0.4.0 - Production
　　
　　7.使用rman进行恢复
　　
　　[oracle@standby oradata]$ rman target /
　　
　　Recovery Manager: Release 9.2.0.4.0 - Production
　　
　　Copyright (c) 1995, 2002, Oracle Corporation.　All rights reserved.
　　
　　connected to target database: PRIMARY (DBID=1367687269)
　　
　　RMAN> restore database;
　　
　　Starting restore at 09-MAR-05
　　
　　using target database controlfile instead of recovery catalog
　　allocated channel: ORA_DISK_1
　　channel ORA_DISK_1: sid=11 devtype=DISK
　　channel ORA_DISK_1: starting datafile backupset restore
　　channel ORA_DISK_1: specifying datafile(s) to restore from backup set
　　restoring datafile 00001 to /opt/oracle/oradata/primary/system01.dbf
　　restoring datafile 00002 to /opt/oracle/oradata/primary/undotbs01.dbf
　　restoring datafile 00003 to /opt/oracle/oradata/primary/users01.dbf
　　channel ORA_DISK_1: restored backup piece 1
　　piece handle=/opt/oracle/orabak/full_PRIMARY_20050309_2 tag=TAG20050309T100844 params=NULL
　　channel ORA_DISK_1: restore complete
　　Finished restore at 09-MAR-05
　　
　　RMAN> recover database;
　　
　　Starting recover at 09-MAR-05
　　using channel ORA_DISK_1
　　
　　starting media recovery
　　
　　channel ORA_DISK_1: starting archive log restore to default destination
　　channel ORA_DISK_1: restoring archive log
　　archive log thread=1 sequence=125
　　channel ORA_DISK_1: restored backup piece 1
　　piece handle=/opt/oracle/orabak/arch_PRIMARY_20050309_3 tag=TAG20050309T100910 params=NULL
　　channel ORA_DISK_1: restore complete
　　archive log filename=/opt/oracle/oradata/primary/archive1_125.dbf thread=1 sequence=125
　　unable to find archive log
　　archive log thread=1 sequence=126
　　RMAN-00571: ===========================================================
　　RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
　　RMAN-00571: ===========================================================
　　RMAN-03002: failure of recover command at 03/09/2005 10:44:02
　　RMAN-06054: media recovery requesting unknown log: thread 1 scn 6691197
　　
　　RMAN> alter database open resetlogs;
　　
　　database opened
　　
　　RMAN>
　　
　　至此恢复完成。