前6篇都是讲的RMAN的一些基础理论和备份的东西,从今天开始进入恢复演练,恢复生产数据库是每个DBA都不愿意看到,但必须去面对。平时还要做好应急情况的预演练。
ORACLE启动时数据文科和控制文件的SCN对比
ORACLE进行正常关闭的时候,会把内存中的脏数据都写入磁盘,使用当前的SCN标记每个数据头文件和控制文件。启动数据库的时候比较数据文件和控制文件中的SCN如果相等就正常启动,如果控制文件SCN大于数据文件SCN说明需要恢复,小于则说明控制文件是旧的数据文件。
通过下面的sql来比较二者的SCN,分别看下可插拔数据库12C中的和10G的:
即使正常请情况下12C中的每个数据文件的SCN不一定全一样,二十每个容器的数据文件SCN一致:
--SQL语句如下:
SET LINES 182
COL name FORM a80
COL status FORM A8
COL file# FORM 9999
COL con_id FORM 9999
COL control_file_SCN FORM 999999999999999
COL datafile_SCN FORM 999999999999999
--
SELECT
a.name
,a.status
,a.file#
,a.con_id
,a.checkpoint_change# control_file_SCN
,b.checkpoint_change# datafile_SCN
,CASE
WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup Normal'
WHEN ((b.checkpoint_change#) = 0) THEN 'File Missing?'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Rec. Req.'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File'
ELSE 'what the ?'
END datafile_status
FROM v$datafile a -- control file SCN for datafile
,v$datafile_header b -- datafile header SCN
WHERE a.file# = b.file#
ORDER BY a.con_id,a.file#;
--执行结果如下:
sys@CYANG> SET LINES 182
sys@CYANG> COL name FORM a70
sys@CYANG> COL status FORM A8
sys@CYANG> COL file# FORM 9999
sys@CYANG> COL con_id FORM 9999
sys@CYANG> COL control_file_SCN FORM 999999999999999
sys@CYANG> COL datafile_SCN FORM 999999999999999
sys@CYANG> col name form a66
sys@CYANG> /
NAME STATUS FILE# CON_ID CONTROL_FILE_SCN DATAFILE_SCN DATAFILE_STATUS
------------------------------------------------------------------ -------- ----- ------ ---------------- ---------------- ----------------
C:\APP\ORACLE\ORADATA\CYANG\DATAFILE\O1_MF_SYSTEM_BYCLTN26_.DBF SYSTEM 1 1 4098689 4098689 Startup Normal
C:\APP\ORACLE\ORADATA\CYANG\DATAFILE\O1_MF_SYSAUX_BYCLRWV7_.DBF ONLINE 3 1 4098689 4098689 Startup Normal
C:\APP\ORACLE\ORADATA\CYANG\DATAFILE\O1_MF_UNDOTBS1_BYCLWPHZ_.DBF ONLINE 5 1 4098689 4098689 Startup Normal
C:\APP\ORACLE\ORADATA\CYANG\DATAFILE\O1_MF_USERS_BYCLWOB1_.DBF ONLINE 6 1 4098689 4098689 Startup Normal
C:\APP\ORACLE\RECCATLODDIR\RECCAT01.DBF ONLINE 11 1 4098689 4098689 Startup Normal
C:\APP\ORACLE\ORADATA\CYANG\DATAFILE\O1_MF_SYSTEM_BYCM0ZWL_.DBF SYSTEM 2 2 2453693 2453693 Startup Normal
C:\APP\ORACLE\ORADATA\CYANG\DATAFILE\O1_MF_SYSAUX_BYCM0ZW8_.DBF ONLINE 4 2 2453693 2453693 Startup Normal
C:\APP\ORACLE\ORADATA\CYANG\DATAFILE\O1_MF_SYSTEM_BYCMNHWC_.DBF SYSTEM 7 3 3868150 3868150 Startup Normal
C:\APP\ORACLE\ORADATA\CYANG\DATAFILE\O1_MF_SYSAUX_BYCMNHW0_.DBF ONLINE 8 3 3868150 3868150 Startup Normal
C:\APP\ORACLE\ORADATA\CYANG\DATAFILE\O1_MF_USERS_BYCMNHWP_.DBF ONLINE 9 3 3868150 3868150 Startup Normal
C:\APP\ORACLE\ORADATA\CYANG\DATAFILE\O1_MF_EXAMPLE_BYCMNHVM_.DBF ONLINE 10 3 3868150 3868150 Startup Normal
已选择 11 行。
而在不是可插拔结构的数据库中,正常情况下SCN都是一致的,如10G中的:
--sql语句如下:
SET LINES 188
COL name FORM a50
COL status FORM A8
COL file# FORM 9999
COL con_id FORM 9999
COL control_file_SCN FORM 999999999999999
COL datafile_SCN FORM 999999999999999
--
SELECT
a.name
,a.status
,a.file#
,a.checkpoint_change# control_file_SCN
,b.checkpoint_change# datafile_SCN
,CASE
WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup Normal'
WHEN ((b.checkpoint_change#) = 0) THEN 'File Missing?'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Rec. Req.'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File'
ELSE 'what the ?'
END datafile_status
FROM v$datafile a -- control file SCN for datafile
,v$datafile_header b -- datafile header SCN
WHERE a.file# = b.file#
ORDER BY a.file#;
--结果如下
sys@ALMTEST> /
NAME STATUS FILE# CONTROL_FILE_SCN DATAFILE_SCN DATAFILE_STATUS
-------------------------------------------------- -------- ----- ---------------- ---------------- ----------------
/almtest/oracle/oradata/almtest/system01.dbf SYSTEM 1 9476193352337 9476193352337 Startup Normal
/almtest/oracle/oradata/almtest/undotbs01.dbf ONLINE 2 9476193352337 9476193352337 Startup Normal
/almtest/oracle/oradata/almtest/sysaux01.dbf ONLINE 3 9476193352337 9476193352337 Startup Normal
/almtest/oracle/oradata/almtest/users01.dbf ONLINE 4 9476193352337 9476193352337 Startup Normal
/almtest/oracle/oradata/almtest/alm01.dbf ONLINE 5 9476193352337 9476193352337 Startup Normal
/almtest/oracle/oradata/almtest/alm02.dbf ONLINE 6 9476193352337 9476193352337 Startup Normal
/almtest/oracle/oradata/almtest/alm03.dbf ONLINE 7 9476193352337 9476193352337 Startup Normal
/almtest/oracle/oradata/almtest/reccat01.dbf ONLINE 8 9476193352337 9476193352337 Startup Normal
/almtest/oracle/oradata/almtest/users02.dbf ONLINE 10 9476193352337 9476193352337 Startup Normal
/almtest/oracle/oradata/almtest/EAM.dbf ONLINE 11 9476193352337 9476193352337 Startup Normal
/almtest/oracle/oradata/almtest/ARCH.dbf ONLINE 12 9476193352337 9476193352337 Startup Normal
11 rows selected.
在v$datafile_header 动态视图中确认是否需要恢复
用下面这个sql来确认,error和recover 列中都能报告问题,如recover中的值为yesl和null值说明出现问题:
select file#,status,error,recover from v$datafile_header;
--12C 中根容器下执行下,发现只有根容器的几个数据文件不需要恢复。种子容器的可以忽略,pdb中的数据文件也为nll,推断是pdb没有启动
sys@CYANG> select con_id,file#,status,error,recover from v$datafile_header order by con_id;
CON_ID FILE# STATUS ERROR REC
------ ----- -------- ----------------------------------------------------------------- ---
1 1 ONLINE NO
1 6 ONLINE NO
1 11 ONLINE NO
1 5 ONLINE NO
1 3 ONLINE NO
2 4 ONLINE
2 2 ONLINE
3 10 ONLINE
3 7 ONLINE
3 9 ONLINE
3 8 ONLINE
已选择 11 行。
--连接pdb,并启动
sys@CYANG> conn sys/******@localhost:1521/pyang1 as sysdba
已连接。
idle> select con_id,file#,status,error,recover from v$datafile_header order by con_id;
CON_ID FILE# STATUS ERROR REC
---------- ---------- ------- ----------------------------------------------------------------- ---
0 5 ONLINE NO
3 10 ONLINE
3 9 ONLINE
3 7 ONLINE
3 8 ONLINE
--启动,再查下发现没有需要恢复的了
idle> startup
插接式数据库已打开。
idle> /
CON_ID FILE# STATUS ERROR REC
---------- ---------- ------- ----------------------------------------------------------------- ---
0 5 ONLINE NO
3 10 ONLINE NO
3 9 ONLINE NO
3 7 ONLINE NO
3 8 ONLINE NO
--重新连接cdb,再查下发现除了种子数据库的两个数据文件,其他的都没有需要恢复的了。
idle> conn sys/**** as sysdba
已连接。
sys@CYANG> show con_id
CON_ID
------------------------------
1
sys@CYANG> select con_id,file#,status,error,recover from v$datafile_header order by con_id;
CON_ID FILE# STATUS ERROR REC
---------- ---------- ------- ----------------------------------------------------------------- ---
1 1 ONLINE NO
1 6 ONLINE NO
1 11 ONLINE NO
1 5 ONLINE NO
1 3 ONLINE NO
2 4 ONLINE
2 2 ONLINE
3 10 ONLINE NO
3 7 ONLINE NO
3 9 ONLINE NO
3 8 ONLINE NO
已选择 11 行。
sys@CYANG>