RMAN异机恢复步骤及故障处理
生活随笔
收集整理的这篇文章主要介绍了
RMAN异机恢复步骤及故障处理
小编觉得挺不错的,现在分享给大家,帮大家做个参考.
一、測试机安装OS+Oracle Software。包含配置oracle用户、组和环境变量（略）
二、開始异机恢复
1. 复制源库最新备份集、初始化參数、password文件到測试机 [oracle@ora10g backupsets]$ scp *20141012* 192.168.1.213:/tmp The authenticity of host '192.168.1.213 (192.168.1.213)' can't be established. RSA key fingerprint is 78:0e:33:cb:3f:04:e4:5d:d1:71:29:a4:3f:3a:79:41. Are you sure you want to continue connecting (yes/no)?
假设没有生成过initora10g.ora初始化參数文件。则创建一个： SQL> create pfile from spfile; 创建完默认路径是放在$ORACLE_HOME/dbs以下
2. 改动初始化參数 [root@bak tmp]# cat initora10g.ora ora10g.__db_cache_size=0 ora10g.__java_pool_size=0 ora10g.__large_pool_size=0 ora10g.__shared_pool_size=0 ora10g.__streams_pool_size=0 *.audit_file_dest='/oracle/admin/ora10g/adump' *.background_dump_dest='/oracle/admin/ora10g/bdump' *.compatible='10.2.0.1.0' *.control_files='/oradata/ora10g/control01.ctl','/oradata/ora10g/control02.ctl','/oradata/ora10g/control03.ctl'#Restore Controlfile *.core_dump_dest='/oracle/admin/ora10g/cdump' *.db_block_size=8192 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='ora10g' *.db_recovery_file_dest='/oracle/flash_recovery_area' *.db_recovery_file_dest_size=2147483648 *.dispatchers='(PROTOCOL=TCP) (SERVICE=ora10gXDB)' *.job_queue_processes=10 *.log_archive_format='%t_%s_%r.dbf' *.nls_language='SIMPLIFIED CHINESE' *.nls_territory='CHINA' *.open_cursors=300 *.pga_aggregate_target=0 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=0 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/oracle/admin/ora10g/udump'
注意。如果我这里測试机使用的文件夹和源库不同，注意红色部分为改动的内容
3. 把password文件和改动好的初始化參数文件复制到測试机对应位置 [oracle@bak ~]$ mv /tmp/initora10g.ora $ORACLE_HOME/dbs [oracle@bak ~]$ mv /tmp/orapwora10g $ORACLE_HOME/dbs
4. 在測试机依据初始化參数文件里指定的路径创建文件夹（注意文件夹必须对于oracle用户有读写权限） [root@bak tmp]# mkdir /oradata/ora10g -p [root@bak tmp]# mkdir /oracle/admin/ora10g/adump -p [root@bak tmp]# mkdir /oracle/admin/ora10g/bdump [root@bak tmp]# mkdir /oracle/admin/ora10g/cdump [root@bak tmp]# mkdir /oracle/admin/ora10g/udump [root@bak tmp]# chown oracle:oinstall /oradata -R [root@bak tmp]# chmod 755 /oradata -R [root@bak tmp]# chown oracle:oinstall /oracle -R [root@bak tmp]# chmod 755 /oracle -R
5. 启动实例到nomount SQL> startup nomount ORA-00371: not enough shared pool memory, should be atleast 72265318 bytes
因为如果測试机配置要比源库低，而临时不确定怎样分配SGA，刚才在初始化參数中把内存分配的值，都设置成了0。包含sga_target，既然没有自己主动分配内存组件，那么手动设置sga_target=1G
SQL> ! [oracle@bak ~]$ vim /u01/app/oracle/product/10.2.0/db_1/dbs/initora10g.ora ... *.sga_target=1G
... 改动保存后。又一次启动实例
[oracle@bak ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 12 17:25:50 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux Error: 2: No such file or directory
因为刚才忘记创建了/oracle/flash_recovery_area这个文件夹，因此报错，假设測试机并不想启用FRA的话，能够把该參数凝视掉
SQL> ! [oracle@bak ~]$ vim /u01/app/oracle/product/10.2.0/db_1/dbs/initora10g.ora
... #*.db_recovery_file_dest='/oracle/flash_recovery_area' #*.db_recovery_file_dest_size=2147483648 ...
[oracle@bak ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 12 17:28:08 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount ORACLE instance started.
Total System Global Area 1073741824 bytes Fixed Size 1223512 bytes Variable Size 264242344 bytes Database Buffers 805306368 bytes Redo Buffers 2969600 bytes SQL>
注意，假设登陆SQLPLUS时碰到无法用OS Local验证，仅仅需用netca创建一个监听就可以解决
6. 恢复測试机控制文件 [oracle@bak ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Oct 12 17:42:07 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ora10g (not mounted)
RMAN> restore controlfile from '/tmp/ora10g-c-4175411955-20141012-00.ctl';
Starting restore at 12-OCT-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:05 output filename=/oradata/ora10g/control01.ctl output filename=/oradata/ora10g/control02.ctl output filename=/oradata/ora10g/control03.ctl Finished restore at 12-OCT-14
RMAN> alter database mount;
database mounted released channel: ORA_DISK_1
7. 恢复測试机数据文件 RMAN> restore database;
Starting restore at 12-OCT-14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 10/12/2014 17:43:29 ORA-01220: file based sort illegal before database is open
RMAN> host! [oracle@bak ~]$ oerr ora 01220 01220, 00000, "file based sort illegal before database is open" // *Cause: A query issued against a fixed table or view required a temporary // segment for sorting before the database was open. Only in-memory // sorts are supported before the database is open. // *Action: Re-phrase the query to avoid a large sort, increase the values // of the SORT_AREA_SIZE and/or SORT_AREA_RETAINED_SIZE // initialization parameters to enable the sort to be done in memory.
SQL> show parameter sort_
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sort_area_retained_size integer 0 sort_area_size integer 65536
查看源库也是这个配置，感觉提示中添加SORT_AREA_SIZE and/or SORT_AREA_RETAINED_SIZE是误导，怀疑是pga_aggregate_target设置为0引起的，有点不解的是，为什么RMAN做restore database还须要用到排序区
[oracle@bak ~]$ vim $ORACLE_HOME/dbs/initora10g.ora
... *.pga_aggregate_target=90M --调整到和源库一致
...
调整完该參数后重新启动实例，再连接RMAN尝试一次
SQL> startup nomount force ORACLE instance started.
Total System Global Area 1073741824 bytes Fixed Size 1223512 bytes Variable Size 264242344 bytes Database Buffers 805306368 bytes Redo Buffers 2969600 bytes SQL> show parameter pga
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 90M
[oracle@bak ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Oct 12 17:59:06 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ora10g (not mounted)
RMAN> restore database;
Starting restore at 12-OCT-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 10/12/2014 17:59:15 ORA-01507: database not mounted
RMAN> alter database mount;
database mounted released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 12-OCT-14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 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 /u01/app/oracle/oradata/ora10g/system01.dbf restoring datafile 00002 to /u01/app/oracle/oradata/ora10g/undotbs01.dbf restoring datafile 00003 to /u01/app/oracle/oradata/ora10g/sysaux01.dbf restoring datafile 00004 to /u01/app/oracle/oradata/ora10g/users01.dbf restoring datafile 00005 to /u01/app/oracle/oradata/ora10g/example01.dbf restoring datafile 00006 to /u01/app/oracle/oradata/ora10g/zlm01.dbf channel ORA_DISK_1: reading from backup piece /u01/orabackup/backupsets/ora10g-4175411955_20141012_860776704_316.db ORA-19870: error reading backup piece /u01/orabackup/backupsets/ora10g-4175411955_20141012_860776704_316.db ORA-19505: failed to identify file "/u01/orabackup/backupsets/ora10g-4175411955_20141012_860776704_316.db" ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 failover to previous backup
channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/app/oracle/oradata/ora10g/system01.dbf restoring datafile 00002 to /u01/app/oracle/oradata/ora10g/undotbs01.dbf restoring datafile 00003 to /u01/app/oracle/oradata/ora10g/sysaux01.dbf restoring datafile 00004 to /u01/app/oracle/oradata/ora10g/users01.dbf restoring datafile 00005 to /u01/app/oracle/oradata/ora10g/example01.dbf restoring datafile 00006 to /u01/app/oracle/oradata/ora10g/zlm01.dbf channel ORA_DISK_1: reading from backup piece /u01/orabackup/backupsets/ora10g-4175411955_20141011_860666488_312.db ORA-19870: error reading backup piece /u01/orabackup/backupsets/ora10g-4175411955_20141011_860666488_312.db ORA-19505: failed to identify file "/u01/orabackup/backupsets/ora10g-4175411955_20141011_860666488_312.db" ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 failover to previous backup
channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/app/oracle/oradata/ora10g/system01.dbf restoring datafile 00002 to /u01/app/oracle/oradata/ora10g/undotbs01.dbf restoring datafile 00003 to /u01/app/oracle/oradata/ora10g/sysaux01.dbf restoring datafile 00004 to /u01/app/oracle/oradata/ora10g/users01.dbf restoring datafile 00005 to /u01/app/oracle/oradata/ora10g/example01.dbf restoring datafile 00006 to /u01/app/oracle/oradata/ora10g/zlm01.dbf channel ORA_DISK_1: reading from backup piece /u01/orabackup/backupsets/ora10g-4175411955_20140928_859456907_308.db ORA-19870: error reading backup piece /u01/orabackup/backupsets/ora10g-4175411955_20140928_859456907_308.db ORA-19505: failed to identify file "/u01/orabackup/backupsets/ora10g-4175411955_20140928_859456907_308.db" ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 failover to previous backup
creating datafile fno=6 name=/u01/app/oracle/oradata/ora10g/zlm01.dbf RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 10/12/2014 17:59:33 ORA-01119: error in creating database file '/u01/app/oracle/oradata/ora10g/zlm01.dbf' ORA-27040: file create error, unable to create file Linux Error: 2: No such file or directory
RMAN>
注意，这里有2个问题： 1.找备份集的路径为/u01/orabackup/backupsets/，而在測试机上并没有此路径。之前仅仅是把备份集拷贝到了/tmp文件夹以下而已。并且能够发现一个非常有趣的现象。除了找最新的备份集xxx_316.db之外，还会往前去找之前的xxx_312.db,xxx_308这2个备份集，当然，这也是找不到的 2.刚才在初始化參数中还改动过了数据文件路径，对于这些信息的改变。刚恢复出来的控制文件自然是不知道的 对于第1个问题，须要把备份集的新位置告知RMAN，能够用catalog start with 'xxx' 对于第2个问题，须要用set newname for datafile xxx 来调整，并用run脚本执行
RMAN> catalog start with '/tmp';
searching for all files that match the pattern /tmp no files found to be unknown to the database
RMAN> exit
Recovery Manager complete.
[root@bak ~]# ll / |grep tmp drwxrwxrwx 4 root root 4096 Oct 12 18:28 tmp
[oracle@bak ~]$ ll /tmp total 181172 srwxr-xr-x 1 root root 0 Aug 22 13:28 mapping-root -rw-r----- 1 oracle oinstall 4440064 Oct 12 16:46 ora10g-4175411955_20141012_860776699_315.arc -rw-r----- 1 oracle oinstall 172843008 Oct 12 16:47 ora10g-4175411955_20141012_860776704_316.db -rw-r----- 1 oracle oinstall 427520 Oct 12 16:47 ora10g-4175411955_20141012_860776830_317.arc -rw-r----- 1 oracle oinstall 7602176 Oct 12 16:47 ora10g-c-4175411955-20141012-00.ctl srw------- 1 root root 0 Aug 22 13:28 scim-panel-socket:0-root
虽然备份集在tmp文件夹下，可是属主为root，RMAN自然无法对其进行操作。而对于tmp文件夹。也不方便把它作为oracle自己的文件夹，由于系统本身也会对该文件夹进行操作。由于那么我们为oracle用户单独创建个文件夹存放这些归档日志，并赋予权限
[root@bak ~]# mkdir /oracle/backupsets -p
[root@bak ~]# chmod 755 /oracle/backupsets -R [root@bak ~]# mv /tmp/*20141012* /oracle/backupsets
[oracle@bak ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Oct 12 18:31:16 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORA10G (DBID=4175411955, not open)
RMAN> catalog start with '/oracle/backupsets';
using target database control file instead of recovery catalog searching for all files that match the pattern /oracle/backupsets
List of Files Unknown to the Database ===================================== File Name: /oracle/backupsets/ora10g-4175411955_20141012_860776704_316.db File Name: /oracle/backupsets/ora10g-4175411955_20141012_860776830_317.arc File Name: /oracle/backupsets/ora10g-c-4175411955-20141012-00.ctl File Name: /oracle/backupsets/ora10g-4175411955_20141012_860776699_315.arc
Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done
List of Cataloged Files ======================= File Name: /oracle/backupsets/ora10g-4175411955_20141012_860776704_316.db File Name: /oracle/backupsets/ora10g-4175411955_20141012_860776830_317.arc File Name: /oracle/backupsets/ora10g-c-4175411955-20141012-00.ctl File Name: /oracle/backupsets/ora10g-4175411955_20141012_860776699_315.arc
RMAN>
如今能够把文件夹catalog到控制文件了。事实上ora10g-c-4175411955-20141012-00.ctl已经用不到。我们须要的是.db,.arc这几个备份集
注意，这里会有一个failover to的操作。先是去/u01/orabackup/backupsets读取，可是发现没有，但不会像之前那么样报错。而是转到了刚才我们catalog过的文件夹“/oracle/backupsets”中去读取，这次非常顺利就把数据文件恢复出来了
[oracle@bak ~]$ ll -lrth /oradata/ora10g/ total 1.2G -rw-r----- 1 oracle oinstall 21M Oct 12 18:36 zlm01.dbf -rw-r----- 1 oracle oinstall 31M Oct 12 18:36 users01.dbf -rw-r----- 1 oracle oinstall 101M Oct 12 18:36 example01.dbf -rw-r----- 1 oracle oinstall 166M Oct 12 18:37 undotbs01.dbf -rw-r----- 1 oracle oinstall 271M Oct 12 18:37 sysaux01.dbf -rw-r----- 1 oracle oinstall 561M Oct 12 18:38 system01.dbf -rw-r----- 1 oracle oinstall 7.2M Oct 12 18:50 control03.ctl -rw-r----- 1 oracle oinstall 7.2M Oct 12 18:50 control02.ctl -rw-r----- 1 oracle oinstall 7.2M Oct 12 18:50 control01.ctl
8. 还原測试机数据库 RMAN> recover database;
Starting recover at 12-OCT-14 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=24 channel ORA_DISK_1: reading from backup piece /u01/orabackup/backupsets/ora10g-4175411955_20141012_860776830_317.arc channel ORA_DISK_1: restored backup piece 1 failover to piece handle=/oracle/backupsets/ora10g-4175411955_20141012_860776830_317.arc tag=ARC_BAK channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_24_858698568.dbf thread=1 sequence=24 unable to find archive log archive log thread=1 sequence=25 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 10/12/2014 18:52:11 RMAN-06054: media recovery requesting unknown log: thread 1 seq 25 lowscn 1091432
RMAN> exit
Recovery Manager complete.
因为RMAN是不全然恢复，无法保证数据与源库是全然一致的，仅仅能恢复到做备份集的那个时刻
[oracle@bak ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 12 18:56:43 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
SQL> alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/ora10g/redo01.log' ORA-27040: file create error, unable to create file Linux Error: 2: No such file or directory
SQL> alter database add logfile '/oradata/ora10g/redo01.log' size 50m;
Database altered.
SQL> alter database add logfile '/oradata/ora10g/redo02.log' size 50m; alter database add logfile '/oradata/redo02.log' size 50m * ERROR at line 1: ORA-19502: write error on file "/oradata/ora10g/redo02.log", blockno 26625 (blocksize=512) ORA-27072: File I/O error Linux Error: 2: No such file or directory Additional information: 4 Additional information: 26625 Additional information: 52736
SQL> ! [oracle@bak ~]$ df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 7.7G 7.3G 13M 100% / /dev/sda1 99M 12M 82M 13% /boot tmpfs 506M 0 506M 0% /dev/shm /dev/sdb1 5.0G 541M 4.2G 12% /data
非常不幸。用作存放数据文件的磁盘是挂在/以下的，正好碰到磁盘空间不足。仅仅剩13M了。导致无法创建之后的redo日志 把备份集删除，释放部分磁盘空间
[oracle@bak ~]$ cd /oracle/backupsets
[oracle@bak backupsets]$ rm -f * rm: cannot remove `ora10g-4175411955_20141012_860776699_315.arc': Permission denied rm: cannot remove `ora10g-4175411955_20141012_860776704_316.db': Permission denied rm: cannot remove `ora10g-4175411955_20141012_860776830_317.arc': Permission denied rm: cannot remove `ora10g-c-4175411955-20141012-00.ctl': Permission denied [oracle@bak backupsets]$ exit logout [root@bak oradata]# cd /oracle/backupsets/ [root@bak backupsets]# ll total 181172 -rw-r----- 1 oracle oinstall 4440064 Oct 12 16:46 ora10g-4175411955_20141012_860776699_315.arc -rw-r----- 1 oracle oinstall 172843008 Oct 12 16:47 ora10g-4175411955_20141012_860776704_316.db -rw-r----- 1 oracle oinstall 427520 Oct 12 16:47 ora10g-4175411955_20141012_860776830_317.arc -rw-r----- 1 oracle oinstall 7602176 Oct 12 16:47 ora10g-c-4175411955-20141012-00.ctl [root@bak backupsets]# rm -f * [root@bak backupsets]# ll total 0 [root@bak backupsets]# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 7.7G 7.1G 240M 97% / /dev/sda1 99M 12M 82M 13% /boot tmpfs 506M 0 506M 0% /dev/shm /dev/sdb1 5.0G 541M 4.2G 12% /data
[root@bak backupsets]# su - oracle [oracle@bak ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 12 19:21:27 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount ORACLE instance started.
Total System Global Area 1073741824 bytes Fixed Size 1223512 bytes Variable Size 264242344 bytes Database Buffers 805306368 bytes Redo Buffers 2969600 bytes Database mounted. SQL> alter database add logfile '/oradata/ora10g/redo01.log' size 50m;
Database altered.
SQL> alter database add logfile '/oradata/ora10g/redo02.log' size 50m;
Database altered.
SQL> alter database add logfile '/oradata/ora10g/redo03.log' size 50m;
Database altered.
SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/ora10g/redo01.log' ORA-27040: file create error, unable to create file Linux Error: 2: No such file or directory
因为控制文件里记录的redo日志是在/u01/app/oracle/oradata/ora10g以下的，而因为測试机改到/oradata/ora10g以下。因此要改动控制文件里的内容，我们来重建一下控制文件
SQL> oradebug setmypid Statement processed. SQL> alter database backup controlfile to trace;
Database altered.
SQL> oradebug tracefile_name /oracle/admin/ora10g/udump/ora10g_ora_30187.trc SQL> !
查看ora10g_ora_30187.trc，复制当中resetlog部分的重建控制文件的SQL语句出来
CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/ora10g/redo01.log' SIZE 50M, GROUP 2 '/u01/app/oracle/oradata/ora10g/redo02.log' SIZE 50M, GROUP 3 '/u01/app/oracle/oradata/ora10g/redo03.log' SIZE 50M, GROUP 4 '/oradata/ora10g/redo01.log' SIZE 50M, GROUP 5 '/oradata/ora10g/redo02.log' SIZE 50M, GROUP 6 '/oradata/ora10g/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/oradata/ora10g/system01.dbf', '/oradata/ora10g/undotbs01.dbf', '/oradata/ora10g/sysaux01.dbf', '/oradata/ora10g/users01.dbf', '/oradata/ora10g/example01.dbf', '/oradata/ora10g/zlm01.dbf' CHARACTER SET ZHS16GBK ;
能够看到，刚才创建控制redo logfile时并没有指定group xxx，默认就会从未使用的组号開始命名。所以这里相应的就是group 4，group 5。group 6。因此仅仅要把之前3组的语句删除，再重建一下控制文件就可以
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted. ORACLE instance shut down. SQL> startup nomount ORACLE instance started.
Total System Global Area 1073741824 bytes Fixed Size 1223512 bytes Variable Size 264242344 bytes Database Buffers 805306368 bytes Redo Buffers 2969600 bytes SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS ARCHIVELOG DATAFILE MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100
MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 4 '/oradata/ora10g/redo01.log' SIZE 50M, GROUP 5 '/oradata/ora10g/redo02.log' SIZE 50M, GROUP 6 '/oradata/ora10g/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/oradata/ora10g/system01.dbf', '/oradata/ora10g/undotbs01.dbf', '/oradata/ora10g/sysaux01.dbf', '/oradata/ora10g/users01.dbf', '/oradata/ora10g/example01.dbf', '/oradata/ora10g/zlm01.dbf' CHARACTER SET ZHS16GBK ;
Control file created.
SQL> alter database mount;
alter database mount * ERROR at line 1: ORA-01100: database already mounted
SQL> alter database open resetlogs;
Database altered.
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE ---------- ---------------- READ WRITE PRIMARY
SQL> select group#,sequence#,(bytes/1024/1024) "SIZE(MB)",archived,status from v$log;
GROUP# SEQUENCE# SIZE(MB) ARC STATUS ---------- ---------- ---------- --- ---------------- 4 0 50 YES UNUSED 5 0 50 YES UNUSED 6 1 50 NO CURRENT
至此，数据库已经顺利恢复完成，当然，假设认为redo logfile从group 4開始有点不顺眼。那么能够再做一下调整
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1073741824 bytes Fixed Size 1223512 bytes Variable Size 264242344 bytes Database Buffers 805306368 bytes Redo Buffers 2969600 bytes Database mounted.
SQL> alter database drop logfile '/oradata/ora10g/redo01.log';
Database altered.
SQL> alter database drop logfile '/oradata/ora10g/redo02.log';
alter database drop logfile '/oradata/ora10g/redo02.log' * ERROR at line 1: ORA-01567: dropping log 5 would leave less than 2 log files for instance ora10g (thread 1) ORA-00312: online log 5 thread 1: '/oradata/ora10g/redo02.log'
oralce要求1个数据库实例至少要有2组日志。因此不同意删除剩余2组日志
SQL> alter database drop logfile '/oradata/ora10g/redo03.log'; alter database drop logfile '/oradata/ora10g/redo03.log' * ERROR at line 1: ORA-01623: log 6 is current log for instance ora10g (thread 1) - cannot drop ORA-00312: online log 6 thread 1: '/oradata/ora10g/redo03.log'
无法删除日志组6，是由于它是当前使用的日志文件。
SQL> alter database add logfile group 1 '/oradata/ora10g/redo01.log' size 50m; alter database add logfile group 1 '/oradata/ora10g/redo01.log' size 50m * ERROR at line 1: ORA-00301: error in adding log file '/oradata/ora10g/redo01.log' - file cannot be created ORA-27038: created file already exists Additional information: 1
因为仅仅是从控制文件里删除。而在OS物理级别该文件依旧存在，所以提示无法创建
SQL> ! [oracle@bak ~]$ ll /oradata/ora10g total 1349168 -rw-r----- 1 oracle oinstall 7389184 Oct 12 19:55 control01.ctl -rw-r----- 1 oracle oinstall 7389184 Oct 12 19:55 control02.ctl -rw-r----- 1 oracle oinstall 7389184 Oct 12 19:55 control03.ctl -rw-r----- 1 oracle oinstall 104865792 Oct 12 19:49 example01.dbf -rw-r----- 1 oracle oinstall 52429312 Oct 12 19:36 redo01.log -rw-r----- 1 oracle oinstall 52429312 Oct 12 19:36 redo02.log -rw-r----- 1 oracle oinstall 52429312 Oct 12 19:49 redo03.log -rw-r----- 1 oracle oinstall 283123712 Oct 12 19:49 sysaux01.dbf -rw-r----- 1 oracle oinstall 587210752 Oct 12 19:49 system01.dbf -rw-r----- 1 oracle oinstall 173023232 Oct 12 19:49 undotbs01.dbf -rw-r----- 1 oracle oinstall 31465472 Oct 12 19:49 users01.dbf -rw-r----- 1 oracle oinstall 20979712 Oct 12 19:49 zlm01.dbf [oracle@bak ~]$ rm -f /oradata/ora10g/redo01.log [oracle@bak ~]$ exit exit
SQL> alter database add logfile group 1 '/oradata/ora10g/redo01.log' size 50m;
Database altered.
先在OS上物理删除日志组1相应的文件redo01.log，再又一次加入，加入时指定新的组号group 1
SQL> alter database drop logfile '/oradata/ora10g/redo02.log';
Database altered.
加入完日志组1，就能够删除日志组2，由于仅仅要满足仍然有2组日志这个条件就可以
SQL> ! [oracle@bak ~]$ rm -f /oradata/ora10g/redo02.log [oracle@bak ~]$ exit exit
SQL> alter database add logfile group 2 '/oradata/ora10g/redo02.log' size 50m;
Database altered.
相同地，继续完毕日志组2的更新，先物理删除文件，再指定组名加入
因为日志组6是当前日志。因此不能直接删除，须要先切换日志
SQL> alter system switch logfile; alter system switch logfile * ERROR at line 1: ORA-01109: database not open
SQL> alter database open;
Database altered.
SQL> select group#,sequence#,(bytes/1024/1024) "SIZE(MB)",archived,status from v$log;
GROUP# SEQUENCE# SIZE(MB) ARC STATUS ---------- ---------- ---------- --- ---------------- 1 0 50 YES UNUSED 2 0 50 YES UNUSED 6 1 50 NO CURRENT
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,(bytes/1024/1024) "SIZE(MB)",archived,status from v$log;
GROUP# SEQUENCE# SIZE(MB) ARC STATUS ---------- ---------- ---------- --- ---------------- 1 2 50 NO CURRENT 2 0 50 YES UNUSED 6 1 50 NO ACTIVE
切换一次日志后。group 1成为当前日志组。如今能够删除日志组3了
SQL> alter database drop logfile '/oradata/ora10g/redo03.log';
Database altered.
SQL> ! [oracle@bak ~]$ rm -f /oradata/ora10g/redo03.log [oracle@bak ~]$ exit exit
SQL> alter database add logfile group 3 '/oradata/ora10g/redo03.log' size 50m;
Database altered.
SQL> select group#,sequence#,(bytes/1024/1024) "SIZE(MB)",archived,status from v$log;
GROUP# SEQUENCE# SIZE(MB) ARC STATUS ---------- ---------- ---------- --- ---------------- 1 2 50 NO CURRENT 2 0 50 YES UNUSED 3 0 50 YES UNUSED
如今，已经把日志组编号调整到正常状态了，当然了，假设不改也不会影响数据库的正常使用
二、開始异机恢复
1. 复制源库最新备份集、初始化參数、password文件到測试机 [oracle@ora10g backupsets]$ scp *20141012* 192.168.1.213:/tmp The authenticity of host '192.168.1.213 (192.168.1.213)' can't be established. RSA key fingerprint is 78:0e:33:cb:3f:04:e4:5d:d1:71:29:a4:3f:3a:79:41. Are you sure you want to continue connecting (yes/no)?
yes
Warning: Permanently added '192.168.1.213' (RSA) to the list of known hosts. oracle@192.168.1.213's password: Connection closed by 192.168.1.213 lost connection [oracle@ora10g backupsets]$ scp *20141012* 192.168.1.213:/tmp oracle@192.168.1.213's password: ora10g-4175411955_20141012_860776699_315.arc 100% 4336KB 2.1MB/s 00:02 ora10g-4175411955_20141012_860776704_316.db 100% 165MB 2.1MB/s 01:20 ora10g-4175411955_20141012_860776830_317.arc 100% 418KB 417.5KB/s 00:00 ora10g-c-4175411955-20141012-00.ctl 100% 7424KB 2.4MB/s 00:03 [oracle@ora10g backupsets]$ scp $ORACLE_HOME/dbs/initora10g.ora oracle@192.168.1.213:/tmp oracle@192.168.1.213's password: initora10g.ora 100% 1136 1.1KB/s 00:00 [oracle@ora10g backupsets]$ scp $ORACLE_HOME/dbs/orapwora10g oracle@192.168.1.213:/tmp oracle@192.168.1.213's password: orapwora10g 100% 1536 1.5KB/s 00:00假设没有生成过initora10g.ora初始化參数文件。则创建一个： SQL> create pfile from spfile; 创建完默认路径是放在$ORACLE_HOME/dbs以下
2. 改动初始化參数 [root@bak tmp]# cat initora10g.ora ora10g.__db_cache_size=0 ora10g.__java_pool_size=0 ora10g.__large_pool_size=0 ora10g.__shared_pool_size=0 ora10g.__streams_pool_size=0 *.audit_file_dest='/oracle/admin/ora10g/adump' *.background_dump_dest='/oracle/admin/ora10g/bdump' *.compatible='10.2.0.1.0' *.control_files='/oradata/ora10g/control01.ctl','/oradata/ora10g/control02.ctl','/oradata/ora10g/control03.ctl'#Restore Controlfile *.core_dump_dest='/oracle/admin/ora10g/cdump' *.db_block_size=8192 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='ora10g' *.db_recovery_file_dest='/oracle/flash_recovery_area' *.db_recovery_file_dest_size=2147483648 *.dispatchers='(PROTOCOL=TCP) (SERVICE=ora10gXDB)' *.job_queue_processes=10 *.log_archive_format='%t_%s_%r.dbf' *.nls_language='SIMPLIFIED CHINESE' *.nls_territory='CHINA' *.open_cursors=300 *.pga_aggregate_target=0 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=0 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/oracle/admin/ora10g/udump'
注意。如果我这里測试机使用的文件夹和源库不同，注意红色部分为改动的内容
3. 把password文件和改动好的初始化參数文件复制到測试机对应位置 [oracle@bak ~]$ mv /tmp/initora10g.ora $ORACLE_HOME/dbs [oracle@bak ~]$ mv /tmp/orapwora10g $ORACLE_HOME/dbs
4. 在測试机依据初始化參数文件里指定的路径创建文件夹（注意文件夹必须对于oracle用户有读写权限） [root@bak tmp]# mkdir /oradata/ora10g -p [root@bak tmp]# mkdir /oracle/admin/ora10g/adump -p [root@bak tmp]# mkdir /oracle/admin/ora10g/bdump [root@bak tmp]# mkdir /oracle/admin/ora10g/cdump [root@bak tmp]# mkdir /oracle/admin/ora10g/udump [root@bak tmp]# chown oracle:oinstall /oradata -R [root@bak tmp]# chmod 755 /oradata -R [root@bak tmp]# chown oracle:oinstall /oracle -R [root@bak tmp]# chmod 755 /oracle -R
5. 启动实例到nomount SQL> startup nomount ORA-00371: not enough shared pool memory, should be atleast 72265318 bytes
因为如果測试机配置要比源库低，而临时不确定怎样分配SGA，刚才在初始化參数中把内存分配的值，都设置成了0。包含sga_target，既然没有自己主动分配内存组件，那么手动设置sga_target=1G
SQL> ! [oracle@bak ~]$ vim /u01/app/oracle/product/10.2.0/db_1/dbs/initora10g.ora ... *.sga_target=1G
... 改动保存后。又一次启动实例
[oracle@bak ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 12 17:25:50 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux Error: 2: No such file or directory
因为刚才忘记创建了/oracle/flash_recovery_area这个文件夹，因此报错，假设測试机并不想启用FRA的话，能够把该參数凝视掉
SQL> ! [oracle@bak ~]$ vim /u01/app/oracle/product/10.2.0/db_1/dbs/initora10g.ora
... #*.db_recovery_file_dest='/oracle/flash_recovery_area' #*.db_recovery_file_dest_size=2147483648 ...
[oracle@bak ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 12 17:28:08 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount ORACLE instance started.
Total System Global Area 1073741824 bytes Fixed Size 1223512 bytes Variable Size 264242344 bytes Database Buffers 805306368 bytes Redo Buffers 2969600 bytes SQL>
注意，假设登陆SQLPLUS时碰到无法用OS Local验证，仅仅需用netca创建一个监听就可以解决
6. 恢复測试机控制文件 [oracle@bak ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Oct 12 17:42:07 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ora10g (not mounted)
RMAN> restore controlfile from '/tmp/ora10g-c-4175411955-20141012-00.ctl';
Starting restore at 12-OCT-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:05 output filename=/oradata/ora10g/control01.ctl output filename=/oradata/ora10g/control02.ctl output filename=/oradata/ora10g/control03.ctl Finished restore at 12-OCT-14
RMAN> alter database mount;
database mounted released channel: ORA_DISK_1
7. 恢复測试机数据文件 RMAN> restore database;
Starting restore at 12-OCT-14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 10/12/2014 17:43:29 ORA-01220: file based sort illegal before database is open
RMAN> host! [oracle@bak ~]$ oerr ora 01220 01220, 00000, "file based sort illegal before database is open" // *Cause: A query issued against a fixed table or view required a temporary // segment for sorting before the database was open. Only in-memory // sorts are supported before the database is open. // *Action: Re-phrase the query to avoid a large sort, increase the values // of the SORT_AREA_SIZE and/or SORT_AREA_RETAINED_SIZE // initialization parameters to enable the sort to be done in memory.
SQL> show parameter sort_
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sort_area_retained_size integer 0 sort_area_size integer 65536
查看源库也是这个配置，感觉提示中添加SORT_AREA_SIZE and/or SORT_AREA_RETAINED_SIZE是误导，怀疑是pga_aggregate_target设置为0引起的，有点不解的是，为什么RMAN做restore database还须要用到排序区
[oracle@bak ~]$ vim $ORACLE_HOME/dbs/initora10g.ora
... *.pga_aggregate_target=90M --调整到和源库一致
...
调整完该參数后重新启动实例，再连接RMAN尝试一次
SQL> startup nomount force ORACLE instance started.
Total System Global Area 1073741824 bytes Fixed Size 1223512 bytes Variable Size 264242344 bytes Database Buffers 805306368 bytes Redo Buffers 2969600 bytes SQL> show parameter pga
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 90M
[oracle@bak ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Oct 12 17:59:06 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ora10g (not mounted)
RMAN> restore database;
Starting restore at 12-OCT-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 devtype=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 10/12/2014 17:59:15 ORA-01507: database not mounted
RMAN> alter database mount;
database mounted released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 12-OCT-14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 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 /u01/app/oracle/oradata/ora10g/system01.dbf restoring datafile 00002 to /u01/app/oracle/oradata/ora10g/undotbs01.dbf restoring datafile 00003 to /u01/app/oracle/oradata/ora10g/sysaux01.dbf restoring datafile 00004 to /u01/app/oracle/oradata/ora10g/users01.dbf restoring datafile 00005 to /u01/app/oracle/oradata/ora10g/example01.dbf restoring datafile 00006 to /u01/app/oracle/oradata/ora10g/zlm01.dbf channel ORA_DISK_1: reading from backup piece /u01/orabackup/backupsets/ora10g-4175411955_20141012_860776704_316.db ORA-19870: error reading backup piece /u01/orabackup/backupsets/ora10g-4175411955_20141012_860776704_316.db ORA-19505: failed to identify file "/u01/orabackup/backupsets/ora10g-4175411955_20141012_860776704_316.db" ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 failover to previous backup
channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/app/oracle/oradata/ora10g/system01.dbf restoring datafile 00002 to /u01/app/oracle/oradata/ora10g/undotbs01.dbf restoring datafile 00003 to /u01/app/oracle/oradata/ora10g/sysaux01.dbf restoring datafile 00004 to /u01/app/oracle/oradata/ora10g/users01.dbf restoring datafile 00005 to /u01/app/oracle/oradata/ora10g/example01.dbf restoring datafile 00006 to /u01/app/oracle/oradata/ora10g/zlm01.dbf channel ORA_DISK_1: reading from backup piece /u01/orabackup/backupsets/ora10g-4175411955_20141011_860666488_312.db ORA-19870: error reading backup piece /u01/orabackup/backupsets/ora10g-4175411955_20141011_860666488_312.db ORA-19505: failed to identify file "/u01/orabackup/backupsets/ora10g-4175411955_20141011_860666488_312.db" ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 failover to previous backup
channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/app/oracle/oradata/ora10g/system01.dbf restoring datafile 00002 to /u01/app/oracle/oradata/ora10g/undotbs01.dbf restoring datafile 00003 to /u01/app/oracle/oradata/ora10g/sysaux01.dbf restoring datafile 00004 to /u01/app/oracle/oradata/ora10g/users01.dbf restoring datafile 00005 to /u01/app/oracle/oradata/ora10g/example01.dbf restoring datafile 00006 to /u01/app/oracle/oradata/ora10g/zlm01.dbf channel ORA_DISK_1: reading from backup piece /u01/orabackup/backupsets/ora10g-4175411955_20140928_859456907_308.db ORA-19870: error reading backup piece /u01/orabackup/backupsets/ora10g-4175411955_20140928_859456907_308.db ORA-19505: failed to identify file "/u01/orabackup/backupsets/ora10g-4175411955_20140928_859456907_308.db" ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 failover to previous backup
creating datafile fno=6 name=/u01/app/oracle/oradata/ora10g/zlm01.dbf RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 10/12/2014 17:59:33 ORA-01119: error in creating database file '/u01/app/oracle/oradata/ora10g/zlm01.dbf' ORA-27040: file create error, unable to create file Linux Error: 2: No such file or directory
RMAN>
注意，这里有2个问题： 1.找备份集的路径为/u01/orabackup/backupsets/，而在測试机上并没有此路径。之前仅仅是把备份集拷贝到了/tmp文件夹以下而已。并且能够发现一个非常有趣的现象。除了找最新的备份集xxx_316.db之外，还会往前去找之前的xxx_312.db,xxx_308这2个备份集，当然，这也是找不到的 2.刚才在初始化參数中还改动过了数据文件路径，对于这些信息的改变。刚恢复出来的控制文件自然是不知道的 对于第1个问题，须要把备份集的新位置告知RMAN，能够用catalog start with 'xxx' 对于第2个问题，须要用set newname for datafile xxx 来调整，并用run脚本执行
RMAN> catalog start with '/tmp';
searching for all files that match the pattern /tmp no files found to be unknown to the database
RMAN> exit
Recovery Manager complete.
[root@bak ~]# ll / |grep tmp drwxrwxrwx 4 root root 4096 Oct 12 18:28 tmp
[oracle@bak ~]$ ll /tmp total 181172 srwxr-xr-x 1 root root 0 Aug 22 13:28 mapping-root -rw-r----- 1 oracle oinstall 4440064 Oct 12 16:46 ora10g-4175411955_20141012_860776699_315.arc -rw-r----- 1 oracle oinstall 172843008 Oct 12 16:47 ora10g-4175411955_20141012_860776704_316.db -rw-r----- 1 oracle oinstall 427520 Oct 12 16:47 ora10g-4175411955_20141012_860776830_317.arc -rw-r----- 1 oracle oinstall 7602176 Oct 12 16:47 ora10g-c-4175411955-20141012-00.ctl srw------- 1 root root 0 Aug 22 13:28 scim-panel-socket:0-root
虽然备份集在tmp文件夹下，可是属主为root，RMAN自然无法对其进行操作。而对于tmp文件夹。也不方便把它作为oracle自己的文件夹，由于系统本身也会对该文件夹进行操作。由于那么我们为oracle用户单独创建个文件夹存放这些归档日志，并赋予权限
[root@bak ~]# mkdir /oracle/backupsets -p
[root@bak ~]# chmod 755 /oracle/backupsets -R [root@bak ~]# mv /tmp/*20141012* /oracle/backupsets
[oracle@bak ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Oct 12 18:31:16 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORA10G (DBID=4175411955, not open)
RMAN> catalog start with '/oracle/backupsets';
using target database control file instead of recovery catalog searching for all files that match the pattern /oracle/backupsets
List of Files Unknown to the Database ===================================== File Name: /oracle/backupsets/ora10g-4175411955_20141012_860776704_316.db File Name: /oracle/backupsets/ora10g-4175411955_20141012_860776830_317.arc File Name: /oracle/backupsets/ora10g-c-4175411955-20141012-00.ctl File Name: /oracle/backupsets/ora10g-4175411955_20141012_860776699_315.arc
Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done
List of Cataloged Files ======================= File Name: /oracle/backupsets/ora10g-4175411955_20141012_860776704_316.db File Name: /oracle/backupsets/ora10g-4175411955_20141012_860776830_317.arc File Name: /oracle/backupsets/ora10g-c-4175411955-20141012-00.ctl File Name: /oracle/backupsets/ora10g-4175411955_20141012_860776699_315.arc
RMAN>
如今能够把文件夹catalog到控制文件了。事实上ora10g-c-4175411955-20141012-00.ctl已经用不到。我们须要的是.db,.arc这几个备份集
RMAN>run{
set newname for datafile 1 to"/oradata/ora10g/system01.dbf";
set newname for datafile 2 to"/oradata/ora10g/undotbs01.dbf";
set newname for datafile 3 to"/oradata/ora10g/sysaux01.dbf";
set newname for datafile 4 to"/oradata/ora10g/users01.dbf";
set newname for datafile 5 to"/oradata/ora10g/example01.dbf";
set newname for datafile 6 to"/oradata/ora10g/zlm01.dbf";
restore database;
switch datafile all;
}
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 12-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 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 /oradata/ora10g/system01.dbf
restoring datafile 00002 to /oradata/ora10g/undotbs01.dbf
restoring datafile 00003 to /oradata/ora10g/sysaux01.dbf
restoring datafile 00004 to /oradata/ora10g/users01.dbf
restoring datafile 00005 to /oradata/ora10g/example01.dbf
restoring datafile 00006 to /oradata/ora10g/zlm01.dbf
channel ORA_DISK_1: reading from backup piece /u01/orabackup/backupsets/ora10g-4175411955_20141012_860776704_316.db
channel ORA_DISK_1: restored backup piece 1
failover to piece handle=/oracle/backupsets/ora10g-4175411955_20141012_860776704_316.db tag=DB_BAK
channel ORA_DISK_1: restore complete, elapsed time: 00:02:17
Finished restore at 12-OCT-14
datafile 1 switched to datafile copy
input datafile copy recid=18 stamp=860783911 filename=/oradata/ora10g/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=19 stamp=860783911 filename=/oradata/ora10g/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=20 stamp=860783911 filename=/oradata/ora10g/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=21 stamp=860783911 filename=/oradata/ora10g/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=22 stamp=860783911 filename=/oradata/ora10g/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=23 stamp=860783911 filename=/oradata/ora10g/zlm01.dbf
RMAN>
注意，这里会有一个failover to的操作。先是去/u01/orabackup/backupsets读取，可是发现没有，但不会像之前那么样报错。而是转到了刚才我们catalog过的文件夹“/oracle/backupsets”中去读取，这次非常顺利就把数据文件恢复出来了
[oracle@bak ~]$ ll -lrth /oradata/ora10g/ total 1.2G -rw-r----- 1 oracle oinstall 21M Oct 12 18:36 zlm01.dbf -rw-r----- 1 oracle oinstall 31M Oct 12 18:36 users01.dbf -rw-r----- 1 oracle oinstall 101M Oct 12 18:36 example01.dbf -rw-r----- 1 oracle oinstall 166M Oct 12 18:37 undotbs01.dbf -rw-r----- 1 oracle oinstall 271M Oct 12 18:37 sysaux01.dbf -rw-r----- 1 oracle oinstall 561M Oct 12 18:38 system01.dbf -rw-r----- 1 oracle oinstall 7.2M Oct 12 18:50 control03.ctl -rw-r----- 1 oracle oinstall 7.2M Oct 12 18:50 control02.ctl -rw-r----- 1 oracle oinstall 7.2M Oct 12 18:50 control01.ctl
8. 还原測试机数据库 RMAN> recover database;
Starting recover at 12-OCT-14 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=24 channel ORA_DISK_1: reading from backup piece /u01/orabackup/backupsets/ora10g-4175411955_20141012_860776830_317.arc channel ORA_DISK_1: restored backup piece 1 failover to piece handle=/oracle/backupsets/ora10g-4175411955_20141012_860776830_317.arc tag=ARC_BAK channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_24_858698568.dbf thread=1 sequence=24 unable to find archive log archive log thread=1 sequence=25 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 10/12/2014 18:52:11 RMAN-06054: media recovery requesting unknown log: thread 1 seq 25 lowscn 1091432
RMAN> exit
Recovery Manager complete.
因为RMAN是不全然恢复，无法保证数据与源库是全然一致的，仅仅能恢复到做备份集的那个时刻
[oracle@bak ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 12 18:56:43 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
SQL> alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/ora10g/redo01.log' ORA-27040: file create error, unable to create file Linux Error: 2: No such file or directory
SQL> alter database add logfile '/oradata/ora10g/redo01.log' size 50m;
Database altered.
SQL> alter database add logfile '/oradata/ora10g/redo02.log' size 50m; alter database add logfile '/oradata/redo02.log' size 50m * ERROR at line 1: ORA-19502: write error on file "/oradata/ora10g/redo02.log", blockno 26625 (blocksize=512) ORA-27072: File I/O error Linux Error: 2: No such file or directory Additional information: 4 Additional information: 26625 Additional information: 52736
SQL> ! [oracle@bak ~]$ df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 7.7G 7.3G 13M 100% / /dev/sda1 99M 12M 82M 13% /boot tmpfs 506M 0 506M 0% /dev/shm /dev/sdb1 5.0G 541M 4.2G 12% /data
非常不幸。用作存放数据文件的磁盘是挂在/以下的，正好碰到磁盘空间不足。仅仅剩13M了。导致无法创建之后的redo日志 把备份集删除，释放部分磁盘空间
[oracle@bak ~]$ cd /oracle/backupsets
[oracle@bak backupsets]$ rm -f * rm: cannot remove `ora10g-4175411955_20141012_860776699_315.arc': Permission denied rm: cannot remove `ora10g-4175411955_20141012_860776704_316.db': Permission denied rm: cannot remove `ora10g-4175411955_20141012_860776830_317.arc': Permission denied rm: cannot remove `ora10g-c-4175411955-20141012-00.ctl': Permission denied [oracle@bak backupsets]$ exit logout [root@bak oradata]# cd /oracle/backupsets/ [root@bak backupsets]# ll total 181172 -rw-r----- 1 oracle oinstall 4440064 Oct 12 16:46 ora10g-4175411955_20141012_860776699_315.arc -rw-r----- 1 oracle oinstall 172843008 Oct 12 16:47 ora10g-4175411955_20141012_860776704_316.db -rw-r----- 1 oracle oinstall 427520 Oct 12 16:47 ora10g-4175411955_20141012_860776830_317.arc -rw-r----- 1 oracle oinstall 7602176 Oct 12 16:47 ora10g-c-4175411955-20141012-00.ctl [root@bak backupsets]# rm -f * [root@bak backupsets]# ll total 0 [root@bak backupsets]# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 7.7G 7.1G 240M 97% / /dev/sda1 99M 12M 82M 13% /boot tmpfs 506M 0 506M 0% /dev/shm /dev/sdb1 5.0G 541M 4.2G 12% /data
[root@bak backupsets]# su - oracle [oracle@bak ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Oct 12 19:21:27 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount ORACLE instance started.
Total System Global Area 1073741824 bytes Fixed Size 1223512 bytes Variable Size 264242344 bytes Database Buffers 805306368 bytes Redo Buffers 2969600 bytes Database mounted. SQL> alter database add logfile '/oradata/ora10g/redo01.log' size 50m;
Database altered.
SQL> alter database add logfile '/oradata/ora10g/redo02.log' size 50m;
Database altered.
SQL> alter database add logfile '/oradata/ora10g/redo03.log' size 50m;
Database altered.
SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/ora10g/redo01.log' ORA-27040: file create error, unable to create file Linux Error: 2: No such file or directory
因为控制文件里记录的redo日志是在/u01/app/oracle/oradata/ora10g以下的，而因为測试机改到/oradata/ora10g以下。因此要改动控制文件里的内容，我们来重建一下控制文件
SQL> oradebug setmypid Statement processed. SQL> alter database backup controlfile to trace;
Database altered.
SQL> oradebug tracefile_name /oracle/admin/ora10g/udump/ora10g_ora_30187.trc SQL> !
查看ora10g_ora_30187.trc，复制当中resetlog部分的重建控制文件的SQL语句出来
CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/ora10g/redo01.log' SIZE 50M, GROUP 2 '/u01/app/oracle/oradata/ora10g/redo02.log' SIZE 50M, GROUP 3 '/u01/app/oracle/oradata/ora10g/redo03.log' SIZE 50M, GROUP 4 '/oradata/ora10g/redo01.log' SIZE 50M, GROUP 5 '/oradata/ora10g/redo02.log' SIZE 50M, GROUP 6 '/oradata/ora10g/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/oradata/ora10g/system01.dbf', '/oradata/ora10g/undotbs01.dbf', '/oradata/ora10g/sysaux01.dbf', '/oradata/ora10g/users01.dbf', '/oradata/ora10g/example01.dbf', '/oradata/ora10g/zlm01.dbf' CHARACTER SET ZHS16GBK ;
能够看到，刚才创建控制redo logfile时并没有指定group xxx，默认就会从未使用的组号開始命名。所以这里相应的就是group 4，group 5。group 6。因此仅仅要把之前3组的语句删除，再重建一下控制文件就可以
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted. ORACLE instance shut down. SQL> startup nomount ORACLE instance started.
Total System Global Area 1073741824 bytes Fixed Size 1223512 bytes Variable Size 264242344 bytes Database Buffers 805306368 bytes Redo Buffers 2969600 bytes SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS ARCHIVELOG DATAFILE MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100
MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 4 '/oradata/ora10g/redo01.log' SIZE 50M, GROUP 5 '/oradata/ora10g/redo02.log' SIZE 50M, GROUP 6 '/oradata/ora10g/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/oradata/ora10g/system01.dbf', '/oradata/ora10g/undotbs01.dbf', '/oradata/ora10g/sysaux01.dbf', '/oradata/ora10g/users01.dbf', '/oradata/ora10g/example01.dbf', '/oradata/ora10g/zlm01.dbf' CHARACTER SET ZHS16GBK ;
Control file created.
SQL> alter database mount;
alter database mount * ERROR at line 1: ORA-01100: database already mounted
SQL> alter database open resetlogs;
Database altered.
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE ---------- ---------------- READ WRITE PRIMARY
SQL> select group#,sequence#,(bytes/1024/1024) "SIZE(MB)",archived,status from v$log;
GROUP# SEQUENCE# SIZE(MB) ARC STATUS ---------- ---------- ---------- --- ---------------- 4 0 50 YES UNUSED 5 0 50 YES UNUSED 6 1 50 NO CURRENT
至此，数据库已经顺利恢复完成，当然，假设认为redo logfile从group 4開始有点不顺眼。那么能够再做一下调整
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1073741824 bytes Fixed Size 1223512 bytes Variable Size 264242344 bytes Database Buffers 805306368 bytes Redo Buffers 2969600 bytes Database mounted.
SQL> alter database drop logfile '/oradata/ora10g/redo01.log';
Database altered.
SQL> alter database drop logfile '/oradata/ora10g/redo02.log';
alter database drop logfile '/oradata/ora10g/redo02.log' * ERROR at line 1: ORA-01567: dropping log 5 would leave less than 2 log files for instance ora10g (thread 1) ORA-00312: online log 5 thread 1: '/oradata/ora10g/redo02.log'
oralce要求1个数据库实例至少要有2组日志。因此不同意删除剩余2组日志
SQL> alter database drop logfile '/oradata/ora10g/redo03.log'; alter database drop logfile '/oradata/ora10g/redo03.log' * ERROR at line 1: ORA-01623: log 6 is current log for instance ora10g (thread 1) - cannot drop ORA-00312: online log 6 thread 1: '/oradata/ora10g/redo03.log'
无法删除日志组6，是由于它是当前使用的日志文件。
当然了，即使不是当前日志也无法删除，由于相同要遵循至少剩余2组日志的必要条件
SQL> alter database add logfile group 1 '/oradata/ora10g/redo01.log' size 50m; alter database add logfile group 1 '/oradata/ora10g/redo01.log' size 50m * ERROR at line 1: ORA-00301: error in adding log file '/oradata/ora10g/redo01.log' - file cannot be created ORA-27038: created file already exists Additional information: 1
因为仅仅是从控制文件里删除。而在OS物理级别该文件依旧存在，所以提示无法创建
SQL> ! [oracle@bak ~]$ ll /oradata/ora10g total 1349168 -rw-r----- 1 oracle oinstall 7389184 Oct 12 19:55 control01.ctl -rw-r----- 1 oracle oinstall 7389184 Oct 12 19:55 control02.ctl -rw-r----- 1 oracle oinstall 7389184 Oct 12 19:55 control03.ctl -rw-r----- 1 oracle oinstall 104865792 Oct 12 19:49 example01.dbf -rw-r----- 1 oracle oinstall 52429312 Oct 12 19:36 redo01.log -rw-r----- 1 oracle oinstall 52429312 Oct 12 19:36 redo02.log -rw-r----- 1 oracle oinstall 52429312 Oct 12 19:49 redo03.log -rw-r----- 1 oracle oinstall 283123712 Oct 12 19:49 sysaux01.dbf -rw-r----- 1 oracle oinstall 587210752 Oct 12 19:49 system01.dbf -rw-r----- 1 oracle oinstall 173023232 Oct 12 19:49 undotbs01.dbf -rw-r----- 1 oracle oinstall 31465472 Oct 12 19:49 users01.dbf -rw-r----- 1 oracle oinstall 20979712 Oct 12 19:49 zlm01.dbf [oracle@bak ~]$ rm -f /oradata/ora10g/redo01.log [oracle@bak ~]$ exit exit
SQL> alter database add logfile group 1 '/oradata/ora10g/redo01.log' size 50m;
Database altered.
先在OS上物理删除日志组1相应的文件redo01.log，再又一次加入，加入时指定新的组号group 1
SQL> alter database drop logfile '/oradata/ora10g/redo02.log';
Database altered.
加入完日志组1，就能够删除日志组2，由于仅仅要满足仍然有2组日志这个条件就可以
SQL> ! [oracle@bak ~]$ rm -f /oradata/ora10g/redo02.log [oracle@bak ~]$ exit exit
SQL> alter database add logfile group 2 '/oradata/ora10g/redo02.log' size 50m;
Database altered.
相同地，继续完毕日志组2的更新，先物理删除文件，再指定组名加入
因为日志组6是当前日志。因此不能直接删除，须要先切换日志
SQL> alter system switch logfile; alter system switch logfile * ERROR at line 1: ORA-01109: database not open
SQL> alter database open;
Database altered.
SQL> select group#,sequence#,(bytes/1024/1024) "SIZE(MB)",archived,status from v$log;
GROUP# SEQUENCE# SIZE(MB) ARC STATUS ---------- ---------- ---------- --- ---------------- 1 0 50 YES UNUSED 2 0 50 YES UNUSED 6 1 50 NO CURRENT
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,(bytes/1024/1024) "SIZE(MB)",archived,status from v$log;
GROUP# SEQUENCE# SIZE(MB) ARC STATUS ---------- ---------- ---------- --- ---------------- 1 2 50 NO CURRENT 2 0 50 YES UNUSED 6 1 50 NO ACTIVE
切换一次日志后。group 1成为当前日志组。如今能够删除日志组3了
SQL> alter database drop logfile '/oradata/ora10g/redo03.log';
Database altered.
SQL> ! [oracle@bak ~]$ rm -f /oradata/ora10g/redo03.log [oracle@bak ~]$ exit exit
SQL> alter database add logfile group 3 '/oradata/ora10g/redo03.log' size 50m;
Database altered.
SQL> select group#,sequence#,(bytes/1024/1024) "SIZE(MB)",archived,status from v$log;
GROUP# SEQUENCE# SIZE(MB) ARC STATUS ---------- ---------- ---------- --- ---------------- 1 2 50 NO CURRENT 2 0 50 YES UNUSED 3 0 50 YES UNUSED
如今，已经把日志组编号调整到正常状态了，当然了，假设不改也不会影响数据库的正常使用
总结
以上是生活随笔为你收集整理的RMAN异机恢复步骤及故障处理的全部内容，希望文章能够帮你解决所遇到的问题。
- 上一篇： 构建之法之单元测试及设计流程
- 下一篇： 服务器双网卡冗余备份技术的实现