一.环境
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是无法回退的。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
