11g-dg-failover测试

原创大柏树 2024-11-30

468

一.环境

11.2.0.4 单实例 + dg

二.背景：

有个客户，需要迁移数据库。因为同版本，我推荐使用dg迁移。
业务方可以做到向多个数据库写数据，所以切换之后依靠业务就可以实现两个库的数据同步，为迁移多了一重保障。

步骤如下：

1.搭建好dg同步

2.然后停止业务

3.备库也打开为读写状态，主备同步中断

4.业务分别向两个库写入相同的数据，保证两边数据一致。

5.找时间进行切换，有问题还可以切换回原库。

我们只测试搭建dg，然后把主库打开为读写状态。

三.搭建dg同步

3.1.主库开启归档和forcelogging


alter database force logging;
SQL> select LOG_MODE,FORCE_LOGGING from v$database;

LOG_MODE		 FORCE_
------------------------ ------
ARCHIVELOG		 YES

SQL>
复制

3.2.备库配置静态监听

--修改之前
[oracle@single admin]$ cd $ORACLE_HOME
[oracle@single dbhome_1]$ cd network/admin/
[oracle@single admin]$ vi listener.ora 

# listener.ora Network Configuration File: /data/app/oracle/product/11.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = single)(PORT = 1521))
      #(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /data/app/oracle

#VALID_NODE_CHECKING_REGISTRATION_LISTENER=1
[oracle@single admin]$ cat listener.ora 
# listener.ora Network Configuration File: /data/app/oracle/product/11.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /data/app/oracle/product/11.1.0/dbhome_1)
      (PROGRAM = extproc)
    )
   (SID_DESC =
     (GLOBAL_DBNAME = test_dg)
     (Oracle_HOME = /data/app/oracle/product/11.1.0/dbhome_1)
     (SID_NAME = test_dg)
   )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.181)(PORT = 1521))
      #(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /data/app/oracle

#VALID_NODE_CHECKING_REGISTRATION_LISTENER=1
[oracle@single admin]$ 
[oracle@single admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-11月-2024 23:05:11

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.59.181)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                29-11月-2024 23:04:36
Uptime                    0 days 0 hr. 0 min. 35 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/app/oracle/product/11.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /data/app/oracle/diag/tnslsnr/single/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.59.181)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "test_dg" has 1 instance(s).
  Instance "test_dg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@single admin]$
复制

3.3.主备都配置tnsnames.ora

[oracle@single ~]$ cd $ORACLE_HOME
[oracle@single dbhome_1]$ cd network/admin/
[oracle@single admin]$ cat tnsnames.ora
TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.180)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )


TEST_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.181)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test_dg)
    )
  )

--测试tnsping
[oracle@single admin]$ tnsping test

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 29-11月-2024 23:12:27

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.180)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test)))
OK (10 msec)
[oracle@single admin]$ tnsping test_dg

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 29-11月-2024 23:12:31

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.181)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test_dg)))
OK (0 msec)
[oracle@single admin]$

复制

3.4.主库生成参数文件、standby contronfile文件

SQL> create pfile='/data/expdp/pfile.ora' from spfile;

File created.

SQL> alter database create standby controlfile as '/data/expdp/control01.ctl';

Database altered.

SQL>
复制

3.5.主库全备

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup database format '/data/expdp/single_backup_%U';
release channel c1;
release channel c2;
release channel c3;
}


[oracle@single expdp]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on 星期五 11月 29 23:19:09 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2481109099)

RMAN> run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup database format '/data/expdp/single_backup_%U';
release channel c1;
release channel c2;
release channel c3;
}
复制

3.6.scp 参数文件、控制文件、备份、密码文件到备库

[oracle@single expdp]$ pwd
/data/expdp
[oracle@single expdp]$ ll
总用量 1249156
-rw-r----- 1 oracle oinstall   9748480 11月 29 23:17 control01.ctl
-rw-r--r-- 1 oracle oinstall      1052 11月 29 23:17 pfile.ora
-rw-r----- 1 oracle oinstall 675061760 11月 29 23:19 single_backup_013bcmnh_1_1
-rw-r----- 1 oracle oinstall 419577856 11月 29 23:19 single_backup_023bcmnh_1_1
-rw-r----- 1 oracle oinstall 164847616 11月 29 23:19 single_backup_033bcmnh_1_1
-rw-r----- 1 oracle oinstall   9797632 11月 29 23:19 single_backup_043bcmnl_1_1
-rw-r----- 1 oracle oinstall     98304 11月 29 23:19 single_backup_053bcmnm_1_1
[oracle@single expdp]$ scp * 192.168.59.181:/data/expdp
oracle@192.168.59.181's password: 

[oracle@single expdp]$ cd $ORACLE_HOME       
[oracle@single dbhome_1]$ cd dbs/
[oracle@single dbs]$ ll
总用量 9584
-rw-rw---- 1 oracle oinstall    1544 10月 21 16:41 hc_orclascii.dat
-rw-rw---- 1 oracle oinstall    1544 10月 21 16:42 hc_orcl.dat
-rw-rw---- 1 oracle oinstall    1544 11月 29 22:52 hc_test.dat
-rw-r----- 1 oracle oinstall     166 5月  30 2024 initDBUA2625720.ora
-rw-r----- 1 oracle oinstall     166 5月  30 2024 initDBUA2642120.ora
-rw-r----- 1 oracle oinstall     166 5月  30 2024 initDBUA2706512.ora
-rw-r--r-- 1 oracle oinstall    2851 5月  15 2009 init.ora
-rw-r----- 1 oracle oinstall      24 5月  30 2024 lkORCLASCI
-rw-r----- 1 oracle oinstall      24 4月  10 2024 lkORCLDG
-rw-r----- 1 oracle oinstall      24 11月 20 2023 lkTEST
-rw-r----- 1 oracle oinstall    1536 5月  30 2024 orapwDBUA2625720
-rw-r----- 1 oracle oinstall    1536 5月  30 2024 orapwDBUA2642120
-rw-r----- 1 oracle oinstall    1536 5月  30 2024 orapwDBUA2706512
-rw-r----- 1 oracle oinstall    1536 3月  22 2022 orapworclbak
-rw-r----- 1 oracle oinstall    1536 10月 21 16:45 orapwtest
-rw-r----- 1 oracle oinstall 9748480 11月 29 23:19 snapcf_test.f
-rw-r----- 1 oracle oinstall    3584 11月 29 22:53 spfiletest.ora
[oracle@single dbs]$ scp orapwtest 192.168.59.181:/data/expdp
oracle@192.168.59.181's password: 
orapwtest                                                     100% 1536   841.4KB/s   00:00    
[oracle@single dbs]$ 
复制

3.7.修改备库参数文件并启动备库到nomount阶段

修改之前：
[oracle@single expdp]$ cat pfile.ora 
test.__db_cache_size=436207616
test.__java_pool_size=16777216
test.__large_pool_size=33554432
test.__oracle_base='/data/app/oracle'#ORACLE_BASE set from environment
test.__pga_aggregate_target=486539264
test.__sga_target=704643072
test.__shared_io_pool_size=0
test.__shared_pool_size=167772160
test.__streams_pool_size=33554432
*.audit_file_dest='/data/app/oracle/admin/test/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/data/app/oracle/oradata/test/control01.ctl','/data/app/oracle/fast_recovery_area/test/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='test'
*.db_recovery_file_dest='/data/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=43851448320
*.diagnostic_dest='/data/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.enable_goldengate_replication=TRUE
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1183842304
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@single expdp]$

修改之后：
[oracle@single expdp]$ cat pfile.ora 
test_dg.__db_cache_size=436207616
test_dg.__java_pool_size=16777216
test_dg.__large_pool_size=33554432
test_dg.__oracle_base='/data/app/oracle'#ORACLE_BASE set from environment
test_dg.__pga_aggregate_target=486539264
test_dg.__sga_target=704643072
test_dg.__shared_io_pool_size=0
test_dg.__shared_pool_size=167772160
test_dg.__streams_pool_size=33554432
*.audit_file_dest='/data/app/oracle/admin/test/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/data/app/oracle/oradata/test/control01.ctl','/data/app/oracle/fast_recovery_area/test/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='test'
*.db_recovery_file_dest='/data/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=43851448320
*.diagnostic_dest='/data/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.enable_goldengate_replication=TRUE
*.log_archive_format='%t_%s_%r.log'
*.memory_target=1183842304
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.DB_UNIQUE_NAME=test_dg
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(test,test_dg)' 
*.LOG_ARCHIVE_DEST_1= 'LOCATION=/data/app/oracle/archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test_dg'
*.LOG_ARCHIVE_DEST_2= 'SERVICE=test ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=test
*.DB_FILE_NAME_CONVERT='test','test_dg'
*.LOG_FILE_NAME_CONVERT='test','test_dg'
*.STANDBY_FILE_MANAGEMENT=AUTO
[oracle@single expdp]$ 

--修改ORACLE_SID
[oracle@single ~]$ cat .bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
	. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH
export ORACLE_BASE=/data/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/dbhome_1
export ORACLE_SID=test_dg
export OGG_HOME=/home/oracle/ogg
export PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$OGG_HOME:/lib/usr/lib:/usr/local/lib
export INVENTORY_LOCATION=/data/app/oraInventory
alias o='sqlplus / as sysdba'
export LANG=zh_CN.gbk
#export LANG=zh_CN.UTF-8
#export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
export DATA_SOURCE_NAME=sys/oracle@oracle1?as=sysdba
[oracle@single ~]$ 
[oracle@single expdp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期五 11月 29 23:34:50 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/data/expdp/pfile.ora';
ORACLE instance started.

Total System Global Area 1185853440 bytes
Fixed Size		    2252664 bytes
Variable Size		  771752072 bytes
Database Buffers	  402653184 bytes
Redo Buffers		    9195520 bytes
SQL> 
复制

3.8.把控制文件按照参数文件的位置拷贝，并启动到mount阶段

[oracle@single expdp]$ cp control01.ctl /data/app/oracle/oradata/test/control01.ctl
[oracle@single expdp]$ cp control01.ctl /data/app/oracle/fast_recovery_area/test/control02.ctl
[oracle@single expdp]$ 
SQL> alter database mount;

Database altered.

SQL>
复制

3.9.备库拷贝密码文件到ORACLE_HOME

[oracle@single dbs]$ mv orapwtest orapwtest_dg
[oracle@single dbs]$ pwd
/data/app/oracle/product/11.1.0/dbhome_1/dbs
[oracle@single dbs]$
复制

3.10.修改主库参数

alter system set log_archive_config='DG_CONFIG=(test,test_dg)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/data/app/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=test' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=test_dg ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test_dg' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
alter system set FAL_SERVER=test_dg scope=both;
alter system set DB_FILE_NAME_CONVERT='test_dg','test'
alter system set LOG_FILE_NAME_CONVERT='test_dg','test'
alter system set STANDBY_FILE_MANAGEMENT=AUTO
复制

3.11.restore 备库

[oracle@single ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on 星期六 11月 30 00:06:55 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2481109099, not open)

RMAN> catalog start with '/data/expdp';

Starting implicit crosscheck backup at 30-11月-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
Finished implicit crosscheck backup at 30-11月-24

Starting implicit crosscheck copy at 30-11月-24
using channel ORA_DISK_1
Finished implicit crosscheck copy at 30-11月-24

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /data/expdp

List of Files Unknown to the Database
=====================================
File Name: /data/expdp/control01.ctl
File Name: /data/expdp/single_backup_013bcmnh_1_1
File Name: /data/expdp/single_backup_023bcmnh_1_1
File Name: /data/expdp/single_backup_033bcmnh_1_1
File Name: /data/expdp/single_backup_043bcmnl_1_1
File Name: /data/expdp/single_backup_053bcmnm_1_1
File Name: /data/expdp/pfile.ora

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /data/expdp/control01.ctl
File Name: /data/expdp/single_backup_013bcmnh_1_1
File Name: /data/expdp/single_backup_023bcmnh_1_1
File Name: /data/expdp/single_backup_033bcmnh_1_1
File Name: /data/expdp/single_backup_043bcmnl_1_1
File Name: /data/expdp/single_backup_053bcmnm_1_1

List of Files Which Where Not Cataloged
=======================================
File Name: /data/expdp/pfile.ora
  RMAN-07517: Reason: The file header is corrupted

RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/data/expdp/single_backup_013bcmnh_1_1 RECID=1 STAMP=1186358828
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/data/expdp/single_backup_023bcmnh_1_1 RECID=2 STAMP=1186358829
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/data/expdp/single_backup_033bcmnh_1_1 RECID=3 STAMP=1186358829
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/data/expdp/single_backup_043bcmnl_1_1 RECID=4 STAMP=1186358829
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/data/expdp/single_backup_053bcmnm_1_1 RECID=5 STAMP=1186358829
Crosschecked 5 objects


RMAN>


复制

3.12.recover 备库

SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> 

alert日志中出现如下信息：
Clearing online redo logfile 3 complete
Media Recovery Log /data/app/oracle/archivelog/1_12_1182962669.log
Media Recovery Log /data/app/oracle/archivelog/1_13_1182962669.log
Media Recovery Log /data/app/oracle/archivelog/1_14_1182962669.log
Media Recovery Waiting for thread 1 sequence 15 (in transit)
复制

3.13.打开备库，创建standby logfile并开启实时应用

--主库查看standby redo log
--standby redo log
set lines 200 pages 9999 LONG 5000
col member for a80
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$standby_log a,v$logfile b where a.group#=b.group#; 

SQL> set lines 200 pages 9999 LONG 5000
col member for a80
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$standby_log a,v$logfile b where a.group#=b.group#; SQL> SQL> 

no rows selected

SQL> alter database add standby logfile thread 1 group 4 size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 group 5 size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 group 6 size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 group 7 size 50M;

Database altered.

SQL> set lines 200 pages 9999 LONG 5000
col member for a80
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$standby_log a,v$logfile b where a.group#=b.group#; SQL> SQL> 

   THREAD#     GROUP# MEMBER								              TYPE		      MB
---------- ---------- -------------------------------------------------------------------------------- -------------- ----------
	 1	    4 /data/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_4_mnmtbgxz_.log        STANDBY		      50
	 1	    5 /data/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_5_mnmtbwr9_.log        STANDBY		      50
	 1	    6 /data/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_6_mnmtc6l0_.log        STANDBY		      50
	 1	    7 /data/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_7_mnmtck68_.log        STANDBY		      50

SQL> 


--主库查看redo log
--redo log
set lines 200 pages 9999 LONG 5000
col member for a80
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$log a,v$logfile b where a.group#=b.group#;

SQL> set lines 200 pages 9999 LONG 5000
col member for a80
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$log a,v$logfile b where a.group#=b.group#;SQL> SQL> 

   THREAD#     GROUP# MEMBER								              TYPE		      MB
---------- ---------- -------------------------------------------------------------------------------- -------------- ----------
	 1	    3 /data/app/oracle/oradata/test/redo03.log				              ONLINE		      50
	 1	    2 /data/app/oracle/oradata/test/redo02.log				              ONLINE		      50
	 1	    1 /data/app/oracle/oradata/test/redo01.log				              ONLINE		      50

SQL> 

--备库查看standby redo log
--standby redo log
set lines 200 pages 9999 LONG 5000
col member for a80
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$standby_log a,v$logfile b where a.group#=b.group#; 




--备库查看redo log
--redo log
set lines 200 pages 9999 LONG 5000
col member for a80
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$log a,v$logfile b where a.group#=b.group#;
SQL> --redo log
set lines 200 pages 9999 LONG 5000
col member for a80
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$log a,v$logfile b where a.group#=b.group#;SQL> SQL> SQL> 

   THREAD#     GROUP# MEMBER									       TYPE	       MB
---------- ---------- -------------------------------------------------------------------------------- -------------- ----------
	 1	    1 /data/app/oracle/oradata/test_dg/redo01.log				       ONLINE	       50
	 1	    3 /data/app/oracle/oradata/test_dg/redo03.log				       ONLINE	       50
	 1	    2 /data/app/oracle/oradata/test_dg/redo02.log				       ONLINE	       50

SQL> 

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database add standby logfile thread 1 group 4 size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 group 5 size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 group 6 size 50M;

Database altered.

SQL> alter database add standby logfile thread 1 group 7 size 50M;

Database altered.

SQL> set lines 200 pages 9999 LONG 5000
col member for a80
select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$standby_log a,v$logfile b where a.group#=b.group#; SQL> SQL> 

   THREAD#     GROUP# MEMBER									       TYPE	       MB
---------- ---------- -------------------------------------------------------------------------------- -------------- ----------
	 1	    4 /data/app/oracle/fast_recovery_area/TEST_DG/onlinelog/o1_mf_4_mnmthzco_.log      STANDBY	       50
	 1	    5 /data/app/oracle/fast_recovery_area/TEST_DG/onlinelog/o1_mf_5_mnmtj8of_.log      STANDBY	       50
	 1	    6 /data/app/oracle/fast_recovery_area/TEST_DG/onlinelog/o1_mf_6_mnmtjnom_.log      STANDBY	       50
	 1	    7 /data/app/oracle/fast_recovery_area/TEST_DG/onlinelog/o1_mf_7_mnmtjwyx_.log      STANDBY	       50

SQL>
SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session nodelay;

Database altered.

SQL> 
复制

四.failover

4.1.创建还原点

--主库
CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;
--备库
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;
Restore point created.
SQL> 
SQL> recover managed standby database using current logfile disconnect from session nodelay;
Media recovery complete.
SQL> 

复制

4.2.主库停止日志传输

SQL> alter system set log_archive_dest_state_2=defer scope=both;

System altered.

SQL>
复制

4.3.备库强制变为主库

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> recover managed standby database finish force;
Media recovery complete.
SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> select status from v$instance;

STATUS
------------------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE			 OPEN_MODE
-------------------------------- ----------------------------------------
PRIMARY 			 READ WRITE

SQL> 
复制

主库日志如下：

ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;
Sat Nov 30 23:03:54 2024
LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Error 3135 for archive log file 1 to 'test_dg'
Errors in file /data/app/oracle/diag/rdbms/test/test/trace/test_nsa2_8498.trc:
ORA-03135: 杩..澶卞.?.郴
LNS: Failed to archive log 1 thread 1 sequence 16 (3135)
Sat Nov 30 23:04:39 2024
RFS[1]: Assigned to RFS process 11563
RFS[1]: Database mount ID mismatch [0x9416b842:0x94171e2b] (2484516930:2484543019)
Sat Nov 30 23:04:39 2024
RFS[2]: Assigned to RFS process 11561
RFS[1]: Not using real application clusters
RFS[2]: Database mount ID mismatch [0x9416b842:0x94171e2b] (2484516930:2484543019)
RFS[2]: Not using real application clusters
Sat Nov 30 23:04:47 2024
RFS[3]: Assigned to RFS process 11572
RFS[3]: Database mount ID mismatch [0x9416b842:0x94171e2b] (2484516930:2484543019)
RFS[3]: Not using real application clusters
复制

备库日志如下：

Sat Nov 30 23:03:44 2024
ALTER DATABASE RECOVER  managed standby database cancel  
Sat Nov 30 23:03:44 2024
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /data/app/oracle/diag/rdbms/test_dg/test_dg/trace/test_dg_pr00_11730.trc:
ORA-16037: ?ㄦ.宸茶.姹..娑..绠℃.澶..浣
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 2653143
Sat Nov 30 23:03:45 2024
MRP0: Background Media Recovery process shutdown (test_dg)
Managed Standby Recovery Canceled (test_dg)
Completed: ALTER DATABASE RECOVER  managed standby database cancel  
ALTER DATABASE RECOVER  managed standby database finish force  
Attempt to do a Terminal Recovery (test_dg)
Media Recovery Start: Managed Standby Recovery (test_dg)
 started logmerger process
Sat Nov 30 23:03:51 2024
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 4 slaves
Media Recovery Waiting for thread 1 sequence 16 (in transit)
Killing 3 processes with pids 9075,9077,9143 (all RFS, wait for I/O) in order to disallow current and future RFS connections. Requested by OS process 12069
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Terminal Recovery timestamp is '11/30/2024 23:03:54'
Terminal Recovery: applying standby redo logs.
Terminal Recovery: thread 1 seq# 16 redo required
Terminal Recovery: 
Recovery of Online Redo Log: Thread 1 Group 4 Seq 16 Reading mem 0
  Mem# 0: /data/app/oracle/fast_recovery_area/TEST_DG/onlinelog/o1_mf_4_mnmthzco_.log
Identified End-Of-Redo (failover) for thread 1 sequence 16 at SCN 0xffff.ffffffff
Incomplete Recovery applied until change 2653150 time 11/30/2024 23:03:52
Media Recovery Complete (test_dg)
Terminal Recovery: successful completion
Sat Nov 30 23:03:54 2024
Forcing ARSCN to IRSCN for TR 0:2653150ARCH: Archival stopped, error occurred. Will continue retrying

ORACLE Instance test_dg - Archival ErrorAttempt to set limbo arscn 0:2653150 irscn 0:2653150 

Resetting standby activation ID 2481094251 (0x93e27e6b)
ORA-16014: ?ュ? 4 sequence# 16 ?.?妗? 娌℃.?..?..?..
ORA-00312: ?..?ュ? 4 绾跨? 1: '/data/app/oracle/fast_recovery_area/TEST_DG/onlinelog/o1_mf_4_mnmthzco_.log'
Completed: ALTER DATABASE RECOVER  managed standby database finish force  
Sat Nov 30 23:04:16 2024
alter database commit to switchover to primary with session shutdown
ALTER DATABASE SWITCHOVER TO PRIMARY (test_dg)
Maximum wait for role transition is 15 minutes.
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Sat Nov 30 23:04:16 2024
SMON: disabling cache recovery
Backup controlfile written to trace file /data/app/oracle/diag/rdbms/test_dg/test_dg/trace/test_dg_ora_11675.trc
Standby terminal recovery start SCN: 2653143
RESETLOGS after incomplete recovery UNTIL CHANGE 2653150
Online log /data/app/oracle/oradata/test_dg/redo01.log: Thread 1 Group 1 was previously cleared
Online log /data/app/oracle/oradata/test_dg/redo02.log: Thread 1 Group 2 was previously cleared
Online log /data/app/oracle/oradata/test_dg/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 2653142
Sat Nov 30 23:04:17 2024
Setting recovery target incarnation to 3
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
Switchover: Complete - Database mounted as primary
Completed: alter database commit to switchover to primary with session shutdown
Sat Nov 30 23:04:37 2024
alter database open
Sat Nov 30 23:04:37 2024
Assigning activation ID 2484516930 (0x9416b842)
Thread 1 advanced to log sequence 2 (thread open)
Sat Nov 30 23:04:37 2024
ARC0: Becoming the 'no SRL' ARCH
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: /data/app/oracle/oradata/test_dg/redo02.logSat Nov 30 23:04:37 2024

ARC1: Becoming the 'no SRL' ARCH
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Nov 30 23:04:37 2024
SMON: enabling cache recovery
ARC0: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
Sat Nov 30 23:04:37 2024
NSA2 started with pid=18, OS id=12131 
[11675] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:6667334 end:6667884 diff:550 (5 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
Sat Nov 30 23:04:38 2024
idle dispatcher 'D000' terminated, pid = (17, 1)
Starting background process SMCO
Sat Nov 30 23:04:38 2024
SMCO started with pid=25, OS id=12136 
No Resource Manager plan active
ARC0: Error 16009 Creating archive log file to 'test'
Sat Nov 30 23:04:39 2024
PING[ARC2]: Heartbeat failed to connect to standby 'test'. Error is 16009.
Archived Log entry 5 added for thread 1 sequence 1 ID 0x9416b842 dest 1:
Archiver process freed from errors. No longer stopped
Starting background process QMNC
Sat Nov 30 23:04:41 2024
QMNC started with pid=28, OS id=12143 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Thread 1 advanced to log sequence 3 (LGWR switch)
  Current log# 3 seq# 3 mem# 0: /data/app/oracle/oradata/test_dg/redo03.log
ARC0: STARTING ARCH PROCESSES
Sat Nov 30 23:04:43 2024
ARC4 started with pid=29, OS id=12146 
ARC4: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
krsk_srl_archive_int: Enabling archival of deferred physical standby SRLs
Archived Log entry 6 added for thread 1 sequence 16 ID 0x93e27e6b dest 1:
Archived Log entry 7 added for thread 1 sequence 2 ID 0x9416b842 dest 1:
Shutting down archive processes
ARCH shutting down
ARC4: Archival stopped
FAL[server, ARC0]: Error 16009 creating remote archivelog file 'test'
FAL[server, ARC0]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance test_dg - Archival Error. Archiver continuing.
Sat Nov 30 23:04:52 2024
Completed: alter database open
Sat Nov 30 23:04:52 2024
Starting background process CJQ0
Sat Nov 30 23:04:52 2024
CJQ0 started with pid=31, OS id=12173 
Setting Resource Manager plan SCHEDULER[0x32DE]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sat Nov 30 23:04:55 2024
Starting background process VKRM
Sat Nov 30 23:04:55 2024
VKRM started with pid=32, OS id=12178 
Sat Nov 30 23:07:14 2024
Thread 1 advanced to log sequence 4 (LGWR switch)
  Current log# 1 seq# 4 mem# 0: /data/app/oracle/oradata/test_dg/redo01.log
Sat Nov 30 23:07:14 2024
Archived Log entry 8 added for thread 1 sequence 3 ID 0x9416b842 dest 1:
复制

五.恢复到还原点

原主库：

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1185853440 bytes
Fixed Size		    2252664 bytes
Variable Size		  771752072 bytes
Database Buffers	  402653184 bytes
Redo Buffers		    9195520 bytes
Database mounted.
SQL> flashback database to restore point SWITCHOVER_START_GRP;

Flashback complete.

SQL> select status from v$instance;

STATUS
------------------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项


SQL> alter database open resetlogs;

Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE			 OPEN_MODE
-------------------------------- ----------------------------------------
PRIMARY 			 READ WRITE

SQL> 
复制

原备库：

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1185853440 bytes
Fixed Size		    2252664 bytes
Variable Size		  788529288 bytes
Database Buffers	  385875968 bytes
Redo Buffers		    9195520 bytes
Database mounted.
SQL> flashback database to restore point SWITCHOVER_START_GRP;
Flashback complete.
SQL> alter database open;      
alter database open
*
ERROR at line 1:
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项

SQL> alter database open resetlogs;
Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE			 OPEN_MODE
-------------------------------- ----------------------------------------
PRIMARY 			 READ WRITE

SQL> 
复制

可以看到，failover通过restore point是无法回退的。