OracleラーニングのRMAN(十二)実戦復帰--制御ファイルの紛失
RMAN> run {
backup database
format '/backup/full_%d_%T_%s'
plus archivelog
format '/backup/arch_%d_%T_%s'
delete all input;
}
Starting backup at 2015/07/09 09:35:03
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=28 RECID=27 STAMP=884590414
channel ORA_DISK_1: starting piece 1 at 2015/07/09 09:35:03
channel ORA_DISK_1: finished piece 1 at 2015/07/09 09:35:04
piece handle=/backup/arch_DEVDB_20150709_115 tag=TAG20150709T093503 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/backup/archivelog/1_28_884357806.dbf RECID=27 STAMP=884590414
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=28 STAMP=884597703
channel ORA_DISK_1: starting piece 1 at 2015/07/09 09:35:05
channel ORA_DISK_1: finished piece 1 at 2015/07/09 09:35:06
piece handle=/backup/arch_DEVDB_20150709_116 tag=TAG20150709T093503 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/backup/archivelog/1_1_884591314.dbf RECID=28 STAMP=884597703
Finished backup at 2015/07/09 09:35:06
Starting backup at 2015/07/09 09:35:06
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/devdb/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/devdb/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/devdb/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/devdb/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/devdb/users01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/devdb/idx01.dbf
channel ORA_DISK_1: starting piece 1 at 2015/07/09 09:35:06
channel ORA_DISK_1: finished piece 1 at 2015/07/09 09:36:41
piece handle=/backup/full_DEVDB_20150709_117 tag=TAG20150709T093506 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2015/07/09 09:36:42
channel ORA_DISK_1: finished piece 1 at 2015/07/09 09:36:43
piece handle=/backup/full_DEVDB_20150709_118 tag=TAG20150709T093506 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015/07/09 09:36:43
Starting backup at 2015/07/09 09:36:43
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=29 STAMP=884597804
channel ORA_DISK_1: starting piece 1 at 2015/07/09 09:36:44
channel ORA_DISK_1: finished piece 1 at 2015/07/09 09:36:45
piece handle=/backup/arch_DEVDB_20150709_119 tag=TAG20150709T093644 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/backup/archivelog/1_2_884591314.dbf RECID=29 STAMP=884597804
Finished backup at 2015/07/09 09:36:45
RMAN>2.データベース内のテーブルのデータ変更.
SQL> conn scott/tiger
Connected.
SQL> select count(*) from emp_new;
COUNT(*)
----------
229376
SQL> delete from emp_new where deptno=10;
49152 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from emp_new;
COUNT(*)
----------
1802243.データベースを閉じます.
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>4.制御ファイルの削除
SQL> ! rm /u01/app/oracle/oradata/devdb/control01.ctl
SQL> ! rm /u01/app/oracle/fast_recovery_area/devdb/control02.ctl5.rmanを使用して制御ファイルを復元する
RMAN> restore controlfile from '//backup/full_DEVDB_20150709_118';
Starting restore at 2015/07/09 09:46:41
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/devdb/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/devdb/control02.ctl
Finished restore at 2015/07/09 09:46:43
RMAN>6.データベースをmount状態に起動する
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_17.データベースのリストアとリカバリ
RMAN> restore database;
Starting restore at 2015/07/09 09:48:56
Starting implicit crosscheck backup at 2015/07/09 09:48:56
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 2015/07/09 09:48:56
Starting implicit crosscheck copy at 2015/07/09 09:48:56
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2015/07/09 09:48:56
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/devdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/devdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/devdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/devdb/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/devdb/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/devdb/idx01.dbf
channel ORA_DISK_1: reading from backup piece /backup/full_DEVDB_20150709_117
channel ORA_DISK_1: piece handle=/backup/full_DEVDB_20150709_117 tag=TAG20150709T093506
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 2015/07/09 09:50:12
RMAN> recover database;
Starting recover at 2015/07/09 09:51:57
using channel ORA_DISK_1
datafile 6 not processed because file is read-only
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/oradata/devdb/redo02.log
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/oradata/devdb/redo03.log
archived log file name=/u01/app/oracle/oradata/devdb/redo02.log thread=1 sequence=2
archived log file name=/u01/app/oracle/oradata/devdb/redo03.log thread=1 sequence=3
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01190: control file or data file 6 is from before the last RESETLOGS
ORA-01110: data file 6: '/u01/app/oracle/oradata/devdb/idx01.dbf'
media recovery complete, elapsed time: 00:00:04
Finished recover at 2015/07/09 09:52:02回復が完了したことがわかります.しかし、ログによると、resetlogsがデータベースを開くとエラーが表示されます.
開けてみよう
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/09/2015 09:53:21
ORA-01190: control file or data file 6 is from before the last RESETLOGS
ORA-01110: data file 6: '/u01/app/oracle/oradata/devdb/idx01.dbf'データベースを復元するときに、このような情報があります.
datafile 6 not processed because file is read-only理由が見つかりました.データベースのバックアップを行うとき、datafile 6は読み取り専用です.バックアップが完了したら、テーブルemp_を変更します.newのデータの場合、datafile 6を読み書きモードに変更しました.リカバリ時にdatafile 6をリカバリしなかった.データファイルが最終的に一致しなくなります.
したがって,表領域が読み取り専用から読み書きモードになると,必ずフルバックアップを行うと結論できる.
私たちはdatafile 6 offlineにします.データベースを再度開きます.
SQL> alter database datafile 6 offline;
Database altered.
RMAN> alter database open resetlogs;
database opened
RMAN>resetlogsで開いたデータベースを使用していますが、データベースのリカバリにはオンラインログも適用されます.では、データは失われていないはずです.検査をします
SQL> conn scott/tiger
Connected.
SQL> select count(*) from emp_new;
COUNT(*)
----------
180224
SQL>しかし、私たちはよく考えてみると、実は問題があります.私が失ったのは制御ファイルだけで、本当にデータベースを復元するべきではありません.手動で管理されたバックアップ・リカバリの場合は、次の文を実行するだけでリカバリが完了します.
SQL> recover database using backup controlfile;回復のスピードはずっと速くなります.どうしてRMANは似たような機能を提供していないのですか?