--备库：
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std READ ONLY WITH APPLY PHYSICAL STANDBY YES YES
SQL> select * from albert.travelskydba;
ID1 ID2 ID3
---------- ---------- ----------
1 2 3
主库误删除用户：
SQL> drop user albert cascade;
User dropped.
备库：
SQL> select * from albert.travelskydba;
select * from albert.travelskydba
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> create restore point tra guarantee flashback database; --创建resotre point
Restore point created.
SQL> flashback database to scn 20668180;
Flashback complete.
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;--将备库打开至可读可写状态
Database altered.
SQL> alter database open;
Database altered.
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std READ WRITE PRIMARY YES YES
SQL> select * from albert.travelskydba; --数据恢复完成，将此用户导出，导入恢复至主库
ID1 ID2 ID3
---------- ---------- ----------
1 2 3
SQL> create directory tra_dir as '/home/ora11g/';
Directory created.
SQL>
SQL> grant read,write,execute on directory tra_dir to public;
Grant succeeded.
[ora11g@albert ]$ expdp schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200325 logfile=expdp_20200325.log
Export: Release 11.2.0.4.0 - Production on Wed Mar 25 09:59:48 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200325 logfile=expdp_20200325.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "ALBERT"."TRAVELSKYDBA" 5.812 KB 1 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/ora11g/albert_dmp.20200325
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Mar 25 10:00:08 2020 elapsed 0 00:00:17
[ora11g@albert ~]$ scp albert_dmp.20200325 ******:/home/ora11g
The authenticity of host '*******' can't be established.
RSA key fingerprint is 45:b9:52:12:86:55:8d:e5:33:8b:b8:59:02:68:81:ff.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '******' (RSA) to the list of known hosts.
ora11g@******'s password:
albert_dmp.20200325 100% 180KB 180.0KB/s 00:00
--导入主库：
SQL> create directory tra_dir as '/home/ora11g/';
Directory created.
SQL> grant read,write,execute on directory tra_dir to public;
Grant succeeded.
[ora11g@vm-kvm17146-ora ~]$ impdp schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200325 logfile=impdp_20200325.log
Import: Release 11.2.0.4.0 - Production on Wed Mar 25 20:28:48 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": /******** AS SYSDBA schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200325 logfile=impdp_20200325.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ALBERT"."TRAVELSKYDBA" 5.812 KB 1 rows
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Wed Mar 25 20:28:59 2020 elapsed 0 00:00:04
主库数据恢复：
SQL> select * from albert.travelskydba;
ID1 ID2 ID3
---------- ---------- ----------
1 2 3
主库数据已经恢复，但是我们现在要继续处理备库，让备库继续进行实时应用
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4108759040 bytes
Fixed Size 2259680 bytes
Variable Size 1577059616 bytes
Database Buffers 2516582400 bytes
Redo Buffers 12857344 bytes
Database mounted.
SQL> flashback database to restore point tra; --闪回至restore point
Flashback complete.
SQL> alter database convert to physical standby; 更新control_file，database_role为physical standby
Database altered.
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std MOUNTED PHYSICAL STANDBY YES YES
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
主库：
SQL> set linesize 200
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;
PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS WRITING LNS 1 6972 90 0 0
SQL> alter system switch logfile;
System altered.
SQL> set linesize 200
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0
2 ;
PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS WRITING LNS 1 6973 4 0 0
备库：
SQL> set linesize 200
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;
PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
RFS IDLE LGWR 1 6973 8 0 0
MRP0 APPLYING_LOG N/A 1 6973 8 5 5
SQL> select * from albert.travelskydba;
ID1 ID2 ID3
---------- ---------- ----------
1 2 3
我们还可以利用Oracle snapshot方式进行备库的read write模式打开
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FOR FLASHBACK_ON CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba READ WRITE PRIMARY YES NO 20755536
SQL> select * from albert.travelskydba;
ID1 ID2 ID3
---------- ---------- ----------
1 2 3
--备库：
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FOR FLASHBACK_ON CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std READ ONLY WITH APPLY PHYSICAL STANDBY YES YES 20755688
SQL> select * from albert.travelskydba;
ID1 ID2 ID3
---------- ---------- ----------
1 2 3
--主库误删除数据：
SQL> drop user albert cascade;
User dropped.
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FOR FLASHBACK_ON CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba READ WRITE PRIMARY YES NO 20755761
--备库：
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FOR FLASHBACK_ON CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std READ ONLY WITH APPLY PHYSICAL STANDBY YES YES 20755770
SQL> select * from albert.travelskydba;
select * from albert.travelskydba
*
ERROR at line 1:
ORA-00942: table or view does not exist
准备恢复数据：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4108759040 bytes
Fixed Size 2259680 bytes
Variable Size 1577059616 bytes
Database Buffers 2516582400 bytes
Redo Buffers 12857344 bytes
Database mounted.
SQL> show parameter broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /oracle/oradata_Travelskydba/Travelskydba/dataguardconfig/dg_config0
1.ora
dg_broker_config_file2 string /oracle/oradata_Travelskydba/Travelskydba/dataguardconfig/dg_config0
2.ora
dg_broker_start boolean TRUE
SQL> alter system set dg_broker_start=false;
System altered.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> flashback database to scn 20755688;
Flashback complete.
SQL> alter database open;
Database altered.
SQL> select * from albert.travelskydba;
ID1 ID2 ID3
---------- ---------- ----------
1 2 3
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4108759040 bytes
Fixed Size 2259680 bytes
Variable Size 1577059616 bytes
Database Buffers 2516582400 bytes
Redo Buffers 12857344 bytes
Database mounted.
SQL>
SQL> alter database convert to snapshot standby;
Database altered.
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FOR FLASHBACK_ON CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std MOUNTED SNAPSHOT STANDBY YES YES 0
SQL> alter database open;
Database altered.
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FOR FLASHBACK_ON CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std READ WRITE SNAPSHOT STANDBY YES YES 20755867
SQL> select * from albert.travelskydba;
ID1 ID2 ID3
---------- ---------- ----------
1 2 3
[ora11g@albert ~]$ expdp schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200326 logfile=expdp_20200326.log
Export: Release 11.2.0.4.0 - Production on Thu Mar 26 01:12:23 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200326 logfile=expdp_20200326.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "ALBERT"."TRAVELSKYDBA" 5.812 KB 1 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/ora11g/albert_dmp.20200326
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Mar 26 01:12:44 2020 elapsed 0 00:00:18
--数据导入过程省略--
--备库恢复至standby模式：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4108759040 bytes
Fixed Size 2259680 bytes
Variable Size 1577059616 bytes
Database Buffers 2516582400 bytes
Redo Buffers 12857344 bytes
Database mounted.
SQL> alter database convert to physical standby;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4108759040 bytes
Fixed Size 2259680 bytes
Variable Size 1577059616 bytes
Database Buffers 2516582400 bytes
Redo Buffers 12857344 bytes
Database mounted.
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FOR FLASHBACK_ON CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std MOUNTED PHYSICAL STANDBY YES YES 20755688 --之前闪回的SCN点，flashback database to scn 20755688;
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FOR FLASHBACK_ON CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std MOUNTED PHYSICAL STANDBY YES YES 20757332
SQL> /
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FOR FLASHBACK_ON CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std MOUNTED PHYSICAL STANDBY YES YES 20757332
SQL> alter database open;
Database altered.
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FOR FLASHBACK_ON CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std MOUNTED PHYSICAL STANDBY YES YES 20757498
主库：
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;
PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS WRITING LNS 1 19 206 0 0
SQL>
SQL> alter system switch logfile;
System altered.
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;
PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS WRITING LNS 1 20 6 0 0
备库：
SQL> set linesize 200
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;
PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
MRP0 APPLYING_LOG N/A 1 19 142 5 5
RFS IDLE LGWR 1 19 142 0 0
SQL> /
PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
MRP0 APPLYING_LOG N/A 1 20 55 5 5
RFS IDLE LGWR 1 20 55 0 0
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'
SQL> set line 1000 pagesize 200
SQL> select name,open_mode,CONTROLFILE_CHANGE#,CHECKPOINT_CHANGE#,CURRENT_SCN,DATABASE_ROLE from V$database;
NAME OPEN_MODE CONTROLFILE_CHANGE# CHECKPOINT_CHANGE# CURRENT_SCN DATABASE_ROLE
--------- -------------------- ------------------- ------------------ ----------- ----------------
Travelskydba MOUNTED 7669047 966955 7669046 PHYSICAL STANDBY
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
ALTER DATABASE ACTIVATE STANDBY DATABASE
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'
此时，我准备重建控制文件，进行不完全恢复操作
QL> alter database backup controlfile to trace as '/home/ora11g/albert.ctl';
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
albert.ctl文件内容：
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA1459T" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/oracle/redo_Travelskydba1/Travelskydba_DG/redo11.log',
'/oracle/redo_Travelskydba1/Travelskydba_DG/redo12.log'
) SIZE 300M BLOCKSIZE 512,
GROUP 2 (
'/oracle/redo_Travelskydba1/Travelskydba_DG/redo21.log',
'/oracle/redo_Travelskydba1/Travelskydba_DG/redo22.log'
) SIZE 300M BLOCKSIZE 512,
GROUP 3 (
'/oracle/redo_Travelskydba1/Travelskydba_DG/redo31.log',
'/oracle/redo_Travelskydba1/Travelskydba_DG/redo32.log'
) SIZE 300M BLOCKSIZE 512
-- STANDBY LOGFILE
-- GROUP 4 '/oracle/redo_Travelskydba1/Travelskydba_DG/standby_redo04.log' SIZE 300M BLOCKSIZE 512,
-- GROUP 5 '/oracle/redo_Travelskydba1/Travelskydba_DG/standby_redo05.log' SIZE 300M BLOCKSIZE 512
DATAFILE
'/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf',
'/oracle/oradata_Travelskydba1/Travelskydba_DG/sysaux01.dbf',
'/oracle/oradata_Travelskydba1/Travelskydba_DG/undotbs01.dbf',
'/oracle/oradata_Travelskydba1/Travelskydba_DG/users01.dbf',
'/oracle/oradata_Travelskydba1/Travelskydba_DG/users02.dbf',
'/oracle/oradata_Travelskydba1/Travelskydba_DG/users03.dbf',
'/oracle/oradata_Travelskydba1/Travelskydba_DG/users04.dbf'
CHARACTER SET AL32UTF8
;
[ora11g@ALBERT ~]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 28 12:36:00 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> @albert.ctl
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2257720 bytes
Variable Size 1191185608 bytes
Database Buffers 2080374784 bytes
Redo Buffers 16527360 bytes
Control file created. --控制文件重建完毕
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 7669047 generated at 11/04/2019 23:00:11 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata_Travelskydba1/Travelskydba_DG/1_247_1017488189.dbf
ORA-00280: change 7669047 for thread 1 is in sequence #247
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs; --仍然无法打开数据库
alter database open resetlogs
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'
修改数据库alter system set “_allow_resetlogs_corruption” =true scope=spfile;参数，再次进行不完全恢复（此操作会有严重丢失数据风险，生产系统请慎用）
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2257720 bytes
Variable Size 1191185608 bytes
Database Buffers 2080374784 bytes
Redo Buffers 16527360 bytes
SQL> alter system set "_allow_resetlogs_corruption" =true scope=spfile;
System altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> @albert.ctl
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2257720 bytes
Variable Size 1191185608 bytes
Database Buffers 2080374784 bytes
Redo Buffers 16527360 bytes
Control file created.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 7669047 generated at 11/04/2019 23:00:11 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata_Travelskydba1/Travelskydba_DG/1_247_1017488189.dbf
ORA-00280: change 7669047 for thread 1 is in sequence #247
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [0], [7669055], [0],
[7996264], [4194545], [], [], [], [], [], []
Process ID: 12086
Session ID: 1705 Serial number: 3
此时出现ORA-00600 2262错误，此时距离成功已经不远，只需要推进SCN即可，
SQL> shutdown abort;
ORACLE instance shut down.
SQL> @albert.ctl
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2257720 bytes
Variable Size 1191185608 bytes
Database Buffers 2080374784 bytes
Redo Buffers 16527360 bytes
Control file created.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 7779049 generated at 03/28/2020 12:54:12 needed for thread 1
ORA-00289: suggestion : /oracle/oradata_Travelskydba1/Travelskydba_DG/1_2_1036241636.dbf
ORA-00280: change 7779049 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'
ORA-01112: media recovery not started
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
SQL> oradebug poke 0x06001AE70 4 8279047
BEFORE: [06001AE70, 06001AE74) = 00000000
AFTER: [06001AE70, 06001AE74) = 007E5407
SQL> alter database open resetlogs; --数据库成功打开
Database altered.
SQL> set line 1000 pagesize 200
SQL> select name,open_mode,CONTROLFILE_CHANGE#,CHECKPOINT_CHANGE#,CURRENT_SCN,DATABASE_ROLE from V$database;
NAME OPEN_MODE CONTROLFILE_CHANGE# CHECKPOINT_CHANGE# CURRENT_SCN DATABASE_ROLE
--------- -------------------- ------------------- ------------------ ----------- ----------------
Travelskydba READ WRITE 8280898 8279049 8280903 PRIMARY
上周，应用人员误操作在平台上tablespace name 打错名字，从而想删除表空间进行重新建立。重建后此数据库备库触发了文件系统满的报警，备库文件系统使用率已经为100%，但是数据库值班人员观察到备库之前建立错误的数据文件已经不在，为何文件系统存储中文件却没有删掉？之前怎么没有出现如此的情况？
我随后做了一个实验给他，原因是在drop tablespace时，没有加上including contents and datafiles
--主库：
SQL> select username,count(*) from gv$session group by username order by 2 desc;
USERNAME COUNT(*)
------------------------------ ----------
174
ZABBIX 6
SYS 5
DBSNMP 4
PUBLIC 1
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create tablespace albert datafile '+DG_DATA' size 1g;
Tablespace created.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
ALBERT --刚刚创建的表空间
FILE# NAME
---------- -----------------------------------------------------------------
1 +DG_DATA/Travelskydba/datafile/system.259.1002299725
2 +DG_DATA/Travelskydba/datafile/sysaux.260.1002299741
3 +DG_DATA/Travelskydba/datafile/undotbs1.261.1002299753
4 +DG_DATA/Travelskydba/datafile/undotbs2.263.1002299769
5 +DG_DATA/Travelskydba/datafile/users.264.1002299781
6 +DG_DATA/Travelskydba/datafile/tbs_test_1025_1.269.1022603323
7 +DG_DATA/Travelskydba/datafile/tbs_test_1025_2.270.1022603429
8 +DG_DATA/Travelskydba/datafile/albert.271.1035217783
--备库：
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
ALBERT
SQL> select file#,name from v$datafile;
FILE# NAME
---------- -----------------------------------------------------------------
1 /oracle/oradata_Travelskydba/Travelskydba/system.259.1002299725
2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429
8 /oracle/oradata_Travelskydba/Travelskydba/albert.271.1035217783
8 rows selected.
一、加入including contents and datafiles
--主库删除：
SQL> drop tablespace albert including contents and datafiles;
Tablespace dropped.
--备库确认，正常删除掉
SQL> select file#,name from v$datafile;
FILE# NAME
---------- -----------------------------------------------------------------
1 /oracle/oradata_Travelskydba/Travelskydba/system.259.1002299725
2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429
7 rows selected.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
8 rows selected.
total 68780320
drwxr-xr-x 2 ora11g dba 4096 Oct 14 11:51 dataguardconfig
-rw-r----- 1 ora11g dba 24526848 Oct 23 15:31 stdctl02.ctl.bak
-rw-r----- 1 ora11g dba 24526848 Oct 23 15:31 stdctl01.ctl.bak
-rw-r----- 1 ora11g dba 10485768192 Oct 23 15:34 temp.262.1002299765
-rw-r----- 1 ora11g dba 2097160192 Mar 16 15:56 users.264.1002299781
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_2.270.1022603429
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_1.269.1022603323
-rw-r----- 1 ora11g dba 5242888192 Mar 16 16:35 sysaux.260.1002299741
-rw-r----- 1 ora11g dba 5242888192 Mar 16 16:35 undotbs1.261.1002299753
-rw-r----- 1 ora11g dba 5242888192 Mar 16 16:35 undotbs2.263.1002299769
-rw-r----- 1 ora11g dba 7340040192 Mar 16 16:35 system.259.1002299725
-rw-r----- 1 ora11g dba 24526848 Mar 16 16:35 stdctl02.ctl
-rw-r----- 1 ora11g dba 24526848 Mar 16 16:35 stdctl01.ctl
此时文件已经正常删除掉。
二、主库不加including contents and datafiles;
--主库创建文件：
SQL> create tablespace albert_leung datafile '+DG_DATA' size 2g;
Tablespace created.
--备库确认：
FILE# NAME
---------- -----------------------------------------------------------------
1 /oracle/oradata_Travelskydba/Travelskydba/system.259.1002299725
2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429
8 /oracle/oradata_Travelskydba/Travelskydba/albert_leung.271.1035218283
8 rows selected.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
ALBERT_LEUNG
drwxr-xr-x 2 ora11g dba 4096 Oct 14 11:51 dataguardconfig
-rw-r----- 1 ora11g dba 24526848 Oct 23 15:31 stdctl02.ctl.bak
-rw-r----- 1 ora11g dba 24526848 Oct 23 15:31 stdctl01.ctl.bak
-rw-r----- 1 ora11g dba 10485768192 Oct 23 15:34 temp.262.1002299765
-rw-r----- 1 ora11g dba 2097160192 Mar 16 15:56 users.264.1002299781
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_2.270.1022603429
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_1.269.1022603323
-rw-r----- 1 ora11g dba 5242888192 Mar 16 16:35 sysaux.260.1002299741
-rw-r----- 1 ora11g dba 5242888192 Mar 16 16:38 undotbs1.261.1002299753
-rw-r----- 1 ora11g dba 2147491840 Mar 16 16:38 albert_leung.271.1035218283
-rw-r----- 1 ora11g dba 5242888192 Mar 16 16:38 undotbs2.263.1002299769
-rw-r----- 1 ora11g dba 7340040192 Mar 16 16:38 system.259.1002299725
-rw-r----- 1 ora11g dba 24526848 Mar 16 16:38 stdctl02.ctl
-rw-r----- 1 ora11g dba 24526848 Mar 16 16:38 stdctl01.ctl
--主库删除文件：
SQL> drop tablespace albert_leung;
Tablespace dropped.
--备库数据字典：
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
8 rows selected
SQL> select FILE#,NAME from v$datafile;
FILE# NAME
---------- -----------------------------------------------------------------
2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429
--可以看到，备库中的数据字典中已经删除掉，也就是说controlfile中该记录已经被删除
--但是观察到，备库文件系统，物理的数据文件还在，并没有删除。
[ora11g@vm-vmw45661-ora Travelskydba]$ ls -l
total 68780320
-rw-r----- 1 ora11g dba 2147491840 Mar 16 16:38 albert_leung.271.1035218283 --主库CHECKPOINT后不更新
drwxr-xr-x 2 ora11g dba 4096 Oct 14 11:51 dataguardconfig
-rw-r----- 1 ora11g dba 24526848 Mar 16 16:43 stdctl01.ctl
-rw-r----- 1 ora11g dba 24526848 Oct 23 15:31 stdctl01.ctl.bak
-rw-r----- 1 ora11g dba 24526848 Mar 16 16:43 stdctl02.ctl
-rw-r----- 1 ora11g dba 24526848 Oct 23 15:31 stdctl02.ctl.bak
-rw-r----- 1 ora11g dba 5242888192 Mar 16 16:41 sysaux.260.1002299741
-rw-r----- 1 ora11g dba 7340040192 Mar 16 16:41 system.259.1002299725
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_1.269.1022603323
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_2.270.1022603429
-rw-r----- 1 ora11g dba 10485768192 Oct 23 15:34 temp.262.1002299765
-rw-r----- 1 ora11g dba 5242888192 Mar 16 16:41 undotbs1.261.1002299753
-rw-r----- 1 ora11g dba 5242888192 Mar 16 16:41 undotbs2.263.1002299769
-rw-r----- 1 ora11g dba 2097160192 Mar 16 15:56 users.264.1002299781
[ora11g@vm-vmw45661-ora Travelskydba]$
--此时，可以使用操作系统命令rm删除掉文件系统中命名错误的数据文件，因为controlfile中已经没有此记录，所以不会涉及数据库的实例恢复。
这个实验，没有加上including contents and datafiles 我只是回答了他第一个问题，但是第二个问题：“为什么之前我删除tablespace的时候，也没有加，怎么就没事呢？
根因在于Oracle Managed Files,这次出问题的此套备库没有使用OMF特性，也就是drop tablespace后，Oracle不会帮你自动的去清理物理文件，只会删除controlfile中的记录，但是之前同事所提到的没事的状况，是因为备库为RAC+ASM架构，默认采用了OMF管理数据文件，所以及时没有添加 including contents and datafiles 也会帮你自动清理文件。
官方原文如下：
1、 Unlike files that are not managed by the database, when an Oracle managed data file or temp file is dropped, the filename is removed from the control file and the file is automatically deleted from the file
2、 When recovery of a standby database encounters redo for the deletion of a tablespace, it deletes any Oracle managed data files in the local file system. Note that this is independent of the INCLUDING DATAFILES option issued at the primary database.
“Specify DEFAULT if you want to omit a limit for this resource in this profile. A user assigned this profile is subject to the limit for this resource specified in the DEFAULT profile. The DEFAULT profile initially defines unlimited resources. You can change those limits with the ALTERPROFILE statement.
Any user who is not explicitly assigned a profile is subject to the limits defined in the DEFAULT profile. Also, if the profile that is explicitly assigned to a user omits limits for some resources or specifies DEFAULT for some limits, then the user is subject to the limits on those resources defined by the DEFAULT profile.”
1.切换前确认主备库是否具备切换条件，关注是否Error与delay_time
select thread#,
DB_UNIQUE_NAME,
DEST_NAME,
TARGET,
DATABASE_MODE,
error,
RECOVERY_MODE,
GAP_STATUS,
APPLIED_SEQ#,
current_scn,
applied_scn,
to_char((scn_to_timestamp(current_scn)),'yyyymmdd hh24:mi:ss') current_scn_time,
to_char((scn_to_timestamp(applied_scn)),'yyyymmdd hh24:mi:ss') applied_scn_time,
scn_to_timestamp(current_scn)- scn_to_timestamp(current_scn) as delay_time
from (SELECT al.thread#,
ads.dest_id,
ads.DEST_NAME,
ads.TYPE || ' ' || ad.TARGET TARGET,
ADS.DATABASE_MODE,
ads.STATUS,
ads.error,
ads.RECOVERY_MODE,
ads.DB_UNIQUE_NAME,
ads.DESTINATION,
ads.GAP_STATUS,
(SELECT sequence#
FROM v$log na
WHERE na.thread# = al.thread#
and status = 'CURRENT') Current_Seq#,
Last_Archived,
al.APPLIED_SEQ#,
ad.applied_scn,
current_scn
FROM (SELECT dest_id,
thread#,
d.current_scn,
MAX(sequence#) Last_Archived,
max(CASE
WHEN APPLIED = 'YES' THEN
sequence#
end) APPLIED_SEQ#
FROM v$archived_log V, v$database d
WHERE V.resetlogs_change# = d.RESETLOGS_CHANGE#
group by dest_id, thread#, d.current_scn) al,
v$archive_dest_status ads,
v$archive_dest AD
WHERE al.dest_id(+) = ads.dest_id
AND ads.STATUS != 'INACTIVE'
AND AD.DEST_ID = ADS.DEST_ID and ad.APPLIED_SCN <> 0
ORDER BY al.thread#, ads.dest_id);
SQL> select name,database_role,switchover_status from v$database;
--确认为 to stanby或SESSIONS ACTIVE状态
2.SERVICE处理：
备库添加：
SQL> show parameter service;
NAME TYPE VALUE
------------------- ----------- ------------------------------
service_names string ORA1436B,dbaas
SQL> alter system set service_names='ORA1436B,dbaas,Albert';
System altered.
SQL> show parameter service;
NAME TYPE VALUE
---------------- ----------- ------------------------------
service_names string ORA1436B,dbaas,Albert
主库修改：
SQL> show parameter service;
NAME TYPE VALUE
-------------- ----------- ------------------------------
service_names string ORA1436B,dbaas,Albert
SQL> alter system set service_names='ORA1436B,dbaas';
System altered.
SQL> show parameter service;
NAME TYPE VALUE
------------- ----------- ------------------------------
service_names string ORA1436B,dbaas
3.RAC主库切换前保留1个节点
登陆到主库其他节点
SQL>SHUTDOWN IMMEDIATE
4.主库SWITCHOVER
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
或
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY with session shutdown;
注：如果有主库有活动的 session 可以使用此选项，否则转换会遇到 ORA-01093 错误或直接KILL活动会话
5.主库打开至Mount状态：
sqlplus / as sysdba
SQL> startup mount
6.备库SWITCH OVER操作
检查备库状态，是否可以切换
SQL> select name,database_role,switchover_status from v$database;
--确认是否为to primary
原备库取消恢复进程：
SQL> alter database recover managed standby database cancel;
原备库（新主库）switchover 操作
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
可能会抛出报ORA-16139: media recovery required，是是由于未应用日志引起，西药先执行执行 ：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT
FROM SESSION;
8.原主库，新备库启用日志应用：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
9.确认主备库数据库数据字典状态与数据库日志有无异常，报错
select name,db_unique_name,database_role,open_mode from v$database;
主库进行日志切换，查看备库的日志，看是否开始接收并应用
SQL>select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in ('LNS','RFS','MRP0') and THREAD# <> 0
备库：
SQL> show parameter service;
NAME TYPE VALUE
---------------------- ----------- ------------------------------
service_names string ORALIANGCE,dbaas
SQL> alter system set service_names='ORALIANGCE,dbaas,Albert';
System altered.
SQL> show parameter service;
NAME TYPE VALUE
----------------------- ----------- ------------------------------
service_names string ORALIANGCE,dbaas,Albert
主库：
SQL> show parameter service;
NAME TYPE VALUE
------------------------- ----------- ------------------------------
service_names string ORALIANGCE,dbaas,Albert
SQL> alter system set service_names='ORALIANGCE,dbaas';
System altered.
SQL> show parameter service;
NAME TYPE VALUE
--------------------------- ----------- ------------------------------
service_names string ORALIANGCE,dbaas
1.4 主备库配置，同步传输确认
主库上执行：
select thread#,
DB_UNIQUE_NAME,
DEST_NAME,
TARGET,
DATABASE_MODE,
error,
RECOVERY_MODE,
GAP_STATUS,
APPLIED_SEQ#,
current_scn,
applied_scn,
to_char((scn_to_timestamp(current_scn)),'yyyymmdd hh24:mi:ss') current_scn_time,
to_char((scn_to_timestamp(applied_scn)),'yyyymmdd hh24:mi:ss') applied_scn_time,
scn_to_timestamp(current_scn)- scn_to_timestamp(current_scn) as delay_time
from (SELECT al.thread#,
ads.dest_id,
ads.DEST_NAME,
ads.TYPE || ' ' || ad.TARGET TARGET,
ADS.DATABASE_MODE,
ads.STATUS,
ads.error,
ads.RECOVERY_MODE,
ads.DB_UNIQUE_NAME,
ads.DESTINATION,
ads.GAP_STATUS,
(SELECT sequence#
FROM v$log na
WHERE na.thread# = al.thread#
and status = 'CURRENT') Current_Seq#,
Last_Archived,
al.APPLIED_SEQ#,
ad.applied_scn,
current_scn
FROM (SELECT dest_id,
thread#,
d.current_scn,
MAX(sequence#) Last_Archived,
max(CASE
WHEN APPLIED = 'YES' THEN
sequence#
end) APPLIED_SEQ#
FROM v$archived_log V, v$database d
WHERE V.resetlogs_change# = d.RESETLOGS_CHANGE#
group by dest_id, thread#, d.current_scn) al,
v$archive_dest_status ads,
v$archive_dest AD
WHERE al.dest_id(+) = ads.dest_id
AND ads.STATUS != 'INACTIVE'
AND AD.DEST_ID = ADS.DEST_ID and ad.APPLIED_SCN <> 0
ORDER BY al.thread#, ads.dest_id)
[ora11g@~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration
Configuration - DG_Config
Protection Mode: MaxPerformance
Databases:
ORALIANGCE - Primary database
ORALIANGCEstd - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
2、查看主备库数据传输是否异常
DGMGRL>
DGMGRL> show database ORALIANGCEstd
Database - ORALIANGCEstd
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 13.00 KByte/s
Real Time Query: ON
Instance(s):
ORALIANGCE
Database Status:
SUCCESS
备库中的Transport Lag，Apply Lag是否正常
1.5 broker switcher切换
[ora11g@]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> switchover to ORALIANGCEstd
Performing switchover NOW, please wait...
Operation requires a connection to instance "ORALIANGCE" on database "ORALIANGCEstd"
Connecting to instance "ORALIANGCE"...
Connected.
New primary database "ORALIANGCEstd" is opening...
Operation requires startup of instance "ORALIANGCE" on database "ORALIANGCE"
Starting instance "ORALIANGCE"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "ORALIANGCEstd"
DGMGRL>
Broker会帮助你封装主备库实例启停，DG角色切换等命令
1.6 切换后检查
1.如RAC-RAC间切换，需要检查RAC各个节点是否启动
2.主备库数据字典检查
新主库：
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORALIANGCE ORALIANGCE PHYSICAL STANDBY READ ONLY WITH APPLY
新备库：
SQL> select name,db_unique_name,database_role,open_mode from v$database；
SQL> set linesize 200
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in ('LNS','RFS','MRP0') and THREAD# <> 0;
PROCESS STATUS CLIENT_P THREAD# SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
RFS IDLE LGWR 1 6864 469 0 0
MRP0 APPLYING_LOG N/A 1 6864 469 5 5
3.alert日志检查：需要仔细检查主备库的alert日志中有无其他ORA报错，如使用HugePage，需要确认HugePage是否正确。
4.应用SERVICE监听注册情况
![](http://albertdba.com/"wp-content/uploads"/2020/02/20200221-2-1024x426.png)
![](http://albertdba.com/"wp-content/uploads"/2020/02/20200221-3-1024x329.png)
![](http://albertdba.com/"wp-content/uploads"/2020/02/20200221-13-1024x283.png)
![](http://albertdba.com/"wp-content/uploads"/2020/02/20200220-4-1024x803.png)
![](http://albertdba.com/"wp-content/uploads"/2020/02/20200221-6-1024x408.png)
![](http://albertdba.com/"wp-content/uploads"/2020/02/20200221-7-1024x591.png)
![](http://albertdba.com/"wp-content/uploads"/2020/02/20200221-10-1024x686.png)
![](http://albertdba.com/"wp-content/uploads"/2020/02/20200220-9-1024x517.png)
![](http://albertdba.com/"wp-content/uploads"/2020/02/20200221-11-1024x679.png)
![](http://albertdba.com/"wp-content/uploads"/2020/02/20200215-1-1024x621.png)
![](http://albertdba.com/"wp-content/uploads"/2020/02/20200215-2-1024x375.png)
![](http://albertdba.com/"wp-content/uploads"/2020/02/20200208-2-1024x41.png)