RMAN的备份及恢复-丢失所有控制文件
2024-07-21 02:33:58
供稿：网友
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
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
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
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
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
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.
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
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
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'
*.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
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>
至此恢复完成。
