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异机恢复步骤及故障处理的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 构建之法之单元测试及设计流程
- 下一篇： 服务器双网卡冗余备份技术的实现