官方文档
https://docs.oracle.com/database/121/SBYDB/rcmbackp.htm#SBYDB4987
E.3.1 Creating a Standby Database with Active Database Duplication主备规划
| 主库 | 备库 | ||
| 数据库版本 | 12.1.0.2.0 | 12.1.0.2.0 | 数据库版本必须一致 |
| IP | 192.0.2.101 | 192.0.2.102 | |
| 主机名 | host01.example.com | host02.example.com | |
| DB_UNIQUE_NAME | PROD5H1 | PROD5H2 | 必须不一致 |
| DB_NAME | PROD5 | PROD5 | 数据库名必须一致 |
| INSTANCE_NAME | PROD5 | PROD5 | 实例名不要求一致 |
| SERVICE_NAME | PROD5H1.example.com | PROD5H2.example.com | 服务名必须不一致 |
| TNSNAMES | primary | standby | 必须不一致 |
| OS | OEL6.5 | OEL6.5 |
相关参数
1、官方文档
https://docs.oracle.com/database/121/SBYDB/init_params.htm#SBYDB4901
https://docs.oracle.com/database/121/SBYDB/create_ps.htm#SBYDB4720
2、DB_NAME
数据库名，主备库必须相同
3、DB_UQIQUE_NAME
数据库唯一名字，主备库不能相同，DG使用该名称来识别主备库
4、LOG_ARCHIVE_CONFIG
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD5,SBDB5)'
定义DG中所有有效的DB_UNIQUE_NMAE
5、FAL_SERVER和FAL_CLIENT值均为tnsname，主要解决日志GAP
FAL_SERVER表示对方的tns，FAL_CLIENT表示自己的tns
6、DB_FILE_NAME_CONVERT
DB_FILE_NAME_CONVERT=备库数据库数据文件目录，主库数据库数据文件目录，可以使用db_unique_name替换
7、LOG_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT=备库数据库日志文件目录，主库数据库日志文件目录，可以使用db_unique_name替换
8、LOG_ARCHIVE_DEST_1
以下是日志文件的合法值
ONLINE_LOGFILES仅在归档ORL（online redo log file）文件时有效
STANDBY_LOGFILES仅在归档SRL（Standby Redo log）文件时有效
ALL_LOGFILES上上面两个类型都有效
以下是角色的合法值
PRIMARY_ROLE仅在主库中有效
STANDBY_ROLE仅在备库中有效
ALL_ROLES主备都有效
主库日志传输
LOCATION表示主库本地归档日志目录
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD5';
9、LOG_ARCHIVE_DEST_2
备库日志接收
SERVICE表示远程归档日志目录
LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SBDB5';主库服务器
1、修改参数db_unique_name
SYS@PROD5> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string PROD5
SYS@PROD5> alter system set db_unique_name=PROD5H1 scope=spfile;
System altered.
2、开归档
SYS@PROD5> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Current log sequence 16
SYS@PROD5>
SYS@PROD5> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4560M
SYS@PROD5> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD5> startup mount
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2923824 bytes
Variable Size 838861520 bytes
Database Buffers 369098752 bytes
Redo Buffers 13852672 bytes
Database mounted.
SYS@PROD5> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Current log sequence 16
SYS@PROD5> alter database archivelog;
Database altered.
SYS@PROD5> alter database open;
Database altered.
SYS@PROD5> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string PROD5H1
3、开闪回
SYS@PROD5> select flashback_on,force_logging from v$database;
FLASHBACK_ON FORCE_LOGGING
------------------ ---------------------------------------
NO NO
SYS@PROD5> alter database flashback on;
Database altered.
4、开强记日志
SYS@PROD5> alter database force logging;
Database altered.
SYS@PROD5> select flashback_on,force_logging from v$database;
FLASHBACK_ON FORCE_LOGGING
------------------ ---------------------------------------
YES YES
5、添加standby logfile
备用日志组比主库日志组多一组
SYS@PROD5> col member for a50
SYS@PROD5> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/PROD5/redo03.log
2 /u01/app/oracle/oradata/PROD5/redo02.log
1 /u01/app/oracle/oradata/PROD5/redo01.log
SYS@PROD5> select group#,bytes/1024/1024 M from v$log;
GROUP# M
---------- ----------
1 50
2 50
3 50
alter database add standby logfile group 4 '/u01/app/oracle/oradata/PROD5/redo04.log' size 50m;
alter database add standby logfile group 5 '/u01/app/oracle/oradata/PROD5/redo05.log' size 50m;
alter database add standby logfile group 6 '/u01/app/oracle/oradata/PROD5/redo06.log' size 50m;
alter database add standby logfile group 7 '/u01/app/oracle/oradata/PROD5/redo07.log' size 50m;
SYS@PROD5> alter database add standby logfile group 4 '/u01/app/oracle/oradata/PROD5/redo04.log' size 50m;
Database altered.
SYS@PROD5> alter database add standby logfile group 5 '/u01/app/oracle/oradata/PROD5/redo05.log' size 50m;
Database altered.
SYS@PROD5> alter database add standby logfile group 6 '/u01/app/oracle/oradata/PROD5/redo06.log' size 50m;
Database altered.
SYS@PROD5> alter database add standby logfile group 7 '/u01/app/oracle/oradata/PROD5/redo07.log' size 50m;
Database altered.
SYS@PROD5> select GROUP# from v$standby_log;
GROUP#
----------
4
5
6
7
6、修改主库参数文件
SYS@PROD5> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/12.1.0
/dbhome_1/dbs/spfilePROD5.ora
SYS@PROD5> create pfile from spfile;
File created.
[oracle@host01 ~]$ cd $ORACLE_HOME/dbs
[oracle@host01 dbs]$ ll -h initPROD5.ora
-rw-r--r-- 1 oracle oinstall 1023 Aug 6 23:03 initPROD5.ora
[oracle@host01 dbs]$ vim initPROD5.ora
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD5H1,PROD5H2)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD5H1'
LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD5H2'
LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
FAL_SERVER='standby'
FAL_CLIENT='primary'
DB_FILE_NAME_CONVERT='PROD5H2','PROD5H1'
LOG_FILE_NAME_CONVERT='PROD5H2','PROD5H1'
standby_file_management=AUTO
重新生成spfile
SYS@PROD5> create spfile from pfile;
create spfile from pfile
*
ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance
SYS@PROD5> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD5> create spfile from pfile;
File created.
SYS@PROD5> startup
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2923824 bytes
Variable Size 838861520 bytes
Database Buffers 369098752 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
SYS@PROD5> show parameter DB_FILE_NAME_CONVERT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string PROD5H2, PROD5H1
SYS@PROD5> show parameter LOG_FILE_NAME_CONVERT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_file_name_convert string PROD5H2, PROD5H1
7、查看主库日志
[oracle@host01 ~]$ tail -100f /u01/app/oracle/diag/rdbms/prod5h1/PROD5/trace/alert_PROD5.log
Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host02.example.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PROD5H2.example.com)(CID=(PROGRAM=oracle)(HOST=host01.example.com)(USER=oracle))))
VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 07-AUG-2023 07:14:06
Tracing not turned on.
Tns error struct:
ns main err code: 12564
TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
8、将主库pfile拷贝至备库
[oracle@host01 dbs]$ scp initPROD5.ora oracle@host02:/u01/app/oracle/product/12.1.0/dbhome_1/dbs
The authenticity of host 'host02 (192.0.2.102)' can't be established.
RSA key fingerprint is dd:a8:ad:6a:6c:3d:04:07:4b:8c:9c:b1:cd:11:d1:26.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'host02' (RSA) to the list of known hosts.
oracle@host02's password:
initPROD5.ora 100% 1488 1.5KB/s 00:00
9、将主库密码文件拷贝至备库
[oracle@host01 dbs]$ scp orapwPROD5 oracle@host02:/u01/app/oracle/product/12.1.0/dbhome_1/dbs
oracle@host02's password:
orapwPROD5 100% 7680 7.5KB/s 00:00备库服务器
1、修改备库参数
[oracle@host02 ~]$ cd $ORACLE_HOME/dbs
[oracle@host02 dbs]$ vim initPROD5.ora
*.db_unique_name='PROD5H2'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD5H1,PROD5H2)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD5H2'
LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD5H1'
LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
FAL_SERVER='primary'
FAL_CLIENT='standby'
DB_FILE_NAME_CONVERT='PROD5H1','PROD5H2'
LOG_FILE_NAME_CONVERT='PROD5H1','PROD5H2'
standby_file_management=AUTO
2、备库创建相关目录
[oracle@host02 dbs]$ mkdir -p /u01/app/oracle/oradata/PROD5
[oracle@host02 dbs]$ mkdir -p /u01/app/oracle/admin/PROD5/adump
[oracle@host02 dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/PROD5
3、备库创建spfile启动到nomount
[oracle@host02 ~]$ export ORACLE_SID=PROD5
[oracle@host02 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 7 07:21:17 2023
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SYS@PROD5> create spfile from pfile;
File created.
SYS@PROD5> startup nomount
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2923824 bytes
Variable Size 838861520 bytes
Database Buffers 369098752 bytes
Redo Buffers 13852672 bytes主库网络配置
1、主库静态配置文件
[oracle@host01 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD5H1.example.com)
(SID_NAME=PROD5)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
)
(SID_DESC=
(GLOBAL_DBNAME=PROD5H1_DGMGRL.example.com)
(SID_NAME=PROD5)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
)
)
2、主库动态配置文件
[oracle@host01 ~]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/
SYS@PROD5> show parameter service_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string PROD5H1.example.com
[oracle@host01 admin]$ lsnrctl status
Service "PROD5H1.example.com" has 1 instance(s).
Instance "PROD5", status READY, has 1 handler(s) for this service...
[oracle@host01 admin]$ vim tnsnames.ora
primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD5H1.example.com)
)
)
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host02.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD5H2.example.com)
)
)
3、主库监听reload
[oracle@host01 admin]$ lsnrctl reload
[oracle@host01 admin]$ lsnrctl status
Services Summary...
Service "PROD5H1.example.com" has 1 instance(s).
Instance "PROD5", status READY, has 1 handler(s) for this service...
Service "PRODCDB.example.com" has 2 instance(s).
Instance "PRODCDB", status UNKNOWN, has 1 handler(s) for this service...
Instance "PRODCDB", status READY, has 1 handler(s) for this service...
[oracle@host01 admin]$ lsnrctl reload
[oracle@host01 admin]$ lsnrctl status
Services Summary...
Service "PROD5H1.example.com" has 2 instance(s).
Instance "PROD5", status UNKNOWN, has 1 handler(s) for this service...
Instance "PROD5", status READY, has 1 handler(s) for this service...
Service "PROD5H1_DGMGRL.example.com" has 1 instance(s).
Instance "PROD5", status UNKNOWN, has 1 handler(s) for this service...备库网络配置
1、备库静态配置文件
[oracle@host02 ~]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/
[oracle@host02 admin]$ vim listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host02.example.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD5H2.example.com)
(SID_NAME=PROD5)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
)
(SID_DESC=
(GLOBAL_DBNAME=PROD5H2_DGMGRL.example.com)
(SID_NAME=PROD5)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
)
)
2、备库动态配置文件
[oracle@host02 admin]$ vim tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD5H1.example.com)
)
)
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host02.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD5H2.example.com)
)
)
3、备库监听reload
[oracle@host02 admin]$ lsnrctl reload
[oracle@host02 admin]$ lsnrctl status
Services Summary...
Service "PROD5H2.example.com" has 1 instance(s).
Instance "PROD5", status UNKNOWN, has 1 handler(s) for this service...
Service "PROD5H2_DGMGRL.example.com" has 1 instance(s).
Instance "PROD5", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully网络测试
1、备库测试
[oracle@host02 admin]$ sqlplus sys/oracle@primary as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 7 07:35:51 2023
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options
SYS@primary>
[oracle@host02 admin]$ sqlplus sys/oracle@standby as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 7 07:36:18 2023
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SYS@standby>
2、主库测试
[oracle@host01 admin]$ sqlplus sys/oracle@primary as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 7 07:36:37 2023
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options
SYS@primary>
[oracle@host01 admin]$ sqlplus sys/oracle@standby as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 7 07:36:50 2023
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SYS@standby> 创建物理备库
1、创建物理备库
[oracle@host02 admin]$ rman target sys/oracle@primary auxiliary sys/oracle@standby
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Aug 7 07:38:07 2023
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD5 (DBID=1606298904)
connected to auxiliary database: PROD5 (not mounted)
RMAN> DUPLICATE TARGET DATABASE
2> FOR STANDBY
3> FROM ACTIVE DATABASE
4> DORECOVER
5> NOFILENAMECHECK;
Starting Duplicate Db at 07-AUG-23
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=13 device type=DISK
current log archived
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwPROD5' auxiliary format
'/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwPROD5' ;
}
executing Memory Script
Starting backup at 07-AUG-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
Finished backup at 07-AUG-23
contents of Memory Script:
{
restore clone from service 'primary' standby controlfile;
}
executing Memory Script
Starting restore at 07-AUG-23
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/PROD5/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/PROD5/control02.ctl
Finished restore at 07-AUG-23
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/PROD5/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/PROD5/system01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/PROD5/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/PROD5/undotbs01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/PROD5/example01.dbf";
set newname for datafile 6 to
"/u01/app/oracle/oradata/PROD5/users01.dbf";
restore
from service 'primary' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/PROD5/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 07-AUG-23
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD5/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD5/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD5/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD5/example01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/PROD5/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 07-AUG-23
sql statement: alter system archive log current
current log archived
contents of Memory Script:
{
restore clone force from service 'primary'
archivelog from scn 2153618;
switch clone datafile all;
}
executing Memory Script
Starting restore at 07-AUG-23
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=18
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=19
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=20
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 07-AUG-23
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1144223080 file name=/u01/app/oracle/oradata/PROD5/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1144223080 file name=/u01/app/oracle/oradata/PROD5/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1144223080 file name=/u01/app/oracle/oradata/PROD5/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=1144223080 file name=/u01/app/oracle/oradata/PROD5/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=1144223080 file name=/u01/app/oracle/oradata/PROD5/users01.dbf
contents of Memory Script:
{
set until scn 2154077;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 07-AUG-23
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD5H2/archivelog/2023_08_07/o1_mf_1_18_lf0d35dw_.arc
archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD5H2/archivelog/2023_08_07/o1_mf_1_19_lf0d36jf_.arc
archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD5H2/archivelog/2023_08_07/o1_mf_1_20_lf0d37lb_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/PROD5H2/archivelog/2023_08_07/o1_mf_1_18_lf0d35dw_.arc thread=1 sequence=18
archived log file name=/u01/app/oracle/fast_recovery_area/PROD5H2/archivelog/2023_08_07/o1_mf_1_19_lf0d36jf_.arc thread=1 sequence=19
archived log file name=/u01/app/oracle/fast_recovery_area/PROD5H2/archivelog/2023_08_07/o1_mf_1_20_lf0d37lb_.arc thread=1 sequence=20
media recovery complete, elapsed time: 00:00:00
Finished recover at 07-AUG-23
Finished Duplicate Db at 07-AUG-23主备库状态
1、查看主库日志
[oracle@host01 ~]$ tail -100f /u01/app/oracle/diag/rdbms/prod5h1/PROD5/trace/alert_PROD5.log
Mon Aug 07 07:44:35 2023
ALTER SYSTEM ARCHIVE LOG
Mon Aug 07 07:44:35 2023
Thread 1 advanced to log sequence 20 (LGWR switch)
Current log# 2 seq# 20 mem# 0: /u01/app/oracle/oradata/PROD5/redo02.log
Mon Aug 07 07:44:35 2023
Archived Log entry 4 added for thread 1 sequence 19 ID 0x5fbef418 dest 1:
Mon Aug 07 07:44:36 2023
ALTER SYSTEM ARCHIVE LOG
Mon Aug 07 07:44:36 2023
Thread 1 cannot allocate new log, sequence 21
Checkpoint not complete
Current log# 2 seq# 20 mem# 0: /u01/app/oracle/oradata/PROD5/redo02.log
Mon Aug 07 07:44:37 2023
Thread 1 advanced to log sequence 21 (LGWR switch)
Current log# 3 seq# 21 mem# 0: /u01/app/oracle/oradata/PROD5/redo03.log
Mon Aug 07 07:44:37 2023
Archived Log entry 5 added for thread 1 sequence 20 ID 0x5fbef418 dest 1:
3、查看备库日志
[oracle@host02 ~]$ tail -100f /u01/app/oracle/diag/rdbms/prod5h2/PROD5/trace/alert_PROD5.log
Completed: alter database clear logfile group 7
RFS connections are allowed
SYS@primary> select open_mode,PROTECTION_MODE,DATABASE_ROLE,DB_UNIQUE_NAME from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_
-------------------- -------------------- ---------------- ----------
READ WRITE MAXIMUM PERFORMANCE PRIMARY PROD5H1
SYS@standby> col DB_UNIQUE_NAME for a10
SYS@standby> select open_mode,PROTECTION_MODE,DATABASE_ROLE,DB_UNIQUE_NAME from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE DB_UNIQUE_
-------------------- -------------------- ---------------- ----------
MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY PROD5H2实时日志应用开启和关闭
1、查看实时日志应用
SYS@standby> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';
CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR RFS 1 22 IDLE
2、开启实时日志应用
SYS@standby> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SYS@standby> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';
CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR RFS 1 22 IDLE
N/A MRP0 1 22 APPLYING_LOG
SYS@standby> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
PROD5 READ ONLY WITH APPLY PHYSICAL STANDBY
3、查看备库日志
Mon Aug 07 07:56:14 2023
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Mon Aug 07 07:56:14 2023
Attempt to start background Managed Standby Recovery process (PROD5)
Starting background process MRP0
Mon Aug 07 07:56:14 2023
MRP0 started with pid=37, OS id=10888
Mon Aug 07 07:56:14 2023
MRP0: Background Managed Standby Recovery process started (PROD5)
Mon Aug 07 07:56:19 2023
Started logmerger process
Mon Aug 07 07:56:19 2023
Managed Standby Recovery starting Real Time Apply
Mon Aug 07 07:56:19 2023
Parallel Media Recovery started with 2 slaves
Mon Aug 07 07:56:19 2023
Waiting for all non-current ORLs to be archived...
Mon Aug 07 07:56:19 2023
All non-current ORLs have been archived.
Mon Aug 07 07:56:19 2023
Media Recovery Log /u01/app/oracle/fast_recovery_area/PROD5H2/archivelog/2023_08_07/o1_mf_1_21_lf0dcjrg_.arc
Media Recovery Waiting for thread 1 sequence 22 (in transit)
Mon Aug 07 07:56:19 2023
Recovery of Online Redo Log: Thread 1 Group 4 Seq 22 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/PROD5/redo04.log
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
4、取消实时日志应用
SYS@standby> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SYS@standby> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';
CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR RFS 1 22 IDLE
5、查看备库日志
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Mon Aug 07 08:03:34 2023
MRP0: Background Media Recovery cancelled with status 16037
Mon Aug 07 08:03:34 2023
Errors in file /u01/app/oracle/diag/rdbms/prod5h2/PROD5/trace/PROD5_pr00_10892.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 2157462
Mon Aug 07 08:03:34 2023
Errors in file /u01/app/oracle/diag/rdbms/prod5h2/PROD5/trace/PROD5_pr00_10892.trc:
ORA-16037: user requested cancel of managed recovery operation
Mon Aug 07 08:03:34 2023
MRP0: Background Media Recovery process shutdown (PROD5)
Mon Aug 07 08:03:35 2023
Managed Standby Recovery Canceled (PROD5)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL确认归档日志是否应用成功
SYS@standby> COLUMN NAME FORMAT A24
SYS@standby> COLUMN VALUE FORMAT A16
SYS@standby> COLUMN DATUM_TIME FORMAT A24
SYS@standby>
SYS@standby> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
NAME VALUE DATUM_TIME
------------------------ ---------------- ------------------------
transport lag +00 00:00:00 08/09/2023 07:09:29
apply lag +00 00:00:00 08/09/2023 07:09:29
apply finish time +00 00:00:00.000
estimated startup time 7
参数都是0说明日志应用成功主备切换switchover
switchover：主动切换，不会破坏dg架构
1、主库切换前环境检查
SYS@primary> col OPEN_MODE for a10
SYS@primary> col SWITCHOVER_STATUS for a10
SYS@primary> select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER
--------- ---------- -------------------- ---------------- ----------
PROD5 READ WRITE MAXIMUM PERFORMANCE PRIMARY TO STANDBY
SYS@primary> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected
SYS@primary> select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO';
THREAD# SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- ---------- --------- --------- ---------
1 16 21-DEC-15 07-AUG-23 NO
1 17 07-AUG-23 07-AUG-23 NO
1 18 07-AUG-23 07-AUG-23 NO
1 19 07-AUG-23 07-AUG-23 NO
1 20 07-AUG-23 07-AUG-23 NO
1 21 07-AUG-23 07-AUG-23 NO
1 22 07-AUG-23 07-AUG-23 NO
1 23 07-AUG-23 07-AUG-23 NO
1 24 07-AUG-23 07-AUG-23 NO
9 rows selected.
SYS@primary> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string PROD5H2, PROD5H1
db_name string PROD5
db_unique_name string PROD5H1
global_names boolean FALSE
instance_name string PROD5
lock_name_space string
log_file_name_convert string PROD5H2, PROD5H1
pdb_file_name_convert string
processor_group_name string
service_names string PROD5H1.example.com
2、备库环境检查
SYS@standby> col NAME for a5
SYS@standby> col OPEN_MODE for a20
SYS@standby> col SWITCHOVER_STATUS for a15
SYS@standby> select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STAT
----- -------------------- -------------------- ---------------- ---------------
PROD5 READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SYS@standby> COLUMN NAME FORMAT A24
SYS@standby> COLUMN VALUE FORMAT A16
SYS@standby> COLUMN DATUM_TIME FORMAT A24
SYS@standby>
SYS@standby> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
NAME VALUE DATUM_TIME
------------------------ ---------------- ------------------------
transport lag +00 00:00:00 08/09/2023 07:09:29
apply lag +00 00:00:00 08/09/2023 07:09:29
apply finish time +00 00:00:00.000
estimated startup time 7
SYS@standby> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string PROD5H1, PROD5H2
db_name string PROD5
db_unique_name string PROD5H2
global_names boolean FALSE
instance_name string PROD5
lock_name_space string
log_file_name_convert string PROD5H1, PROD5H2
pdb_file_name_convert string
processor_group_name string
service_names string PROD5H2.example.com
3、主库切换成物理备库
SYS@primary> alter database commit to switchover to physical standby;
Database altered.
4、查看主库日志
[oracle@host01 ~]$ tail -100f /u01/app/oracle/diag/rdbms/prod5h1/PROD5/trace/alert_PROD5.log
Wed Aug 09 07:41:46 2023
alter database commit to switchover to physical standby
Wed Aug 09 07:41:46 2023
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 18862] (PROD5)
Wed Aug 09 07:41:46 2023
Waiting for target standby to receive all redo
Wed Aug 09 07:41:46 2023
Waiting for all non-current ORLs to be archived...
Wed Aug 09 07:41:46 2023
All non-current ORLs have been archived.
Wed Aug 09 07:41:46 2023
Waiting for all FAL entries to be archived...
Wed Aug 09 07:41:46 2023
All FAL entries have been archived.
Wed Aug 09 07:41:46 2023
Waiting for potential Physical Standby switchover target to become synchronized...
Wed Aug 09 07:41:47 2023
Active, synchronized Physical Standby switchover target has been identified
Preventing updates and queries at the Primary
Generating and shipping final logs to target standby
Switchover End-Of-Redo Log thread 1 sequence 25 has been fixed
Switchover: Primary highest seen SCN set to 0x0.0x22b289
ARCH: Noswitch archival of thread 1, sequence 25
ARCH: End-Of-Redo Branch archival of thread 1 sequence 25
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Standby redo logfile selected for thread 1 sequence 25 for destination LOG_ARCHIVE_DEST_2
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received all redo
Waiting for target standby to apply all redo
Final check for a synchronized target standby. Check will be made once.
LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
Active, synchronized target has been identified
Target has also received all redo
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/prod5h1/PROD5/trace/PROD5_ora_18862.trc
Converting the primary database to a new standby database
Clearing standby activation ID 1606349848 (0x5fbef418)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Archivelog for thread 1 sequence 25 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover: Complete - Database shutdown required
USER (ospid: 18862): terminating the instance
Wed Aug 09 07:41:49 2023
Instance terminated by USER, pid = 18862
Completed: alter database commit to switchover to physical standby
Shutting down instance (abort)
License high water mark = 22
Wed Aug 09 07:41:49 2023
Instance shutdown complete
5、查看备库日志
[oracle@host02 ~]$ tail -100f /u01/app/oracle/diag/rdbms/prod5h2/PROD5/trace/alert_PROD5.log
Wed Aug 09 07:41:48 2023
RFS[3]: Assigned to RFS process (PID:11963)
RFS[3]: Selected log 5 for thread 1 sequence 25 dbid 1606298904 branch 893851740
Wed Aug 09 07:41:48 2023
Archived Log entry 8 added for thread 1 sequence 25 ID 0x5fbef418 dest 1:
Wed Aug 09 07:41:48 2023
Resetting standby activation ID 1606349848 (0x5fbef418)
Wed Aug 09 07:41:48 2023
Media Recovery End-Of-Redo indicator encountered
Wed Aug 09 07:41:48 2023
Media Recovery Continuing
Wed Aug 09 07:41:48 2023
RFS[4]: Assigned to RFS process (PID:11961)
RFS[4]: Possible network disconnect with primary database
Wed Aug 09 07:41:48 2023
Media Recovery Waiting for thread 1 sequence 26
6、新备库开库
SYS@primary> startup
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2923824 bytes
Variable Size 838861520 bytes
Database Buffers 369098752 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
7、查看主库变成了备库
SYS@primary> select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER
--------- ---------- -------------------- ---------------- ----------
PROD5 READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY TO PRIMARY
SYS@primary> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string PROD5H2, PROD5H1
db_name string PROD5
db_unique_name string PROD5H1
global_names boolean FALSE
instance_name string PROD5
lock_name_space string
log_file_name_convert string PROD5H2, PROD5H1
pdb_file_name_convert string
processor_group_name string
service_names string PROD5H1.example.com
8、物理备库切换成主库
SYS@standby> alter database commit to switchover to primary;
Database altered.
SYS@standby> select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STAT
----- -------------------- -------------------- ---------------- ---------------
PROD5 MOUNTED MAXIMUM PERFORMANCE PRIMARY NOT ALLOWED
9、新主库开库
SYS@standby> alter database open;
Database altered.
SYS@standby> select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STAT
----- -------------------- -------------------- ---------------- ---------------
PROD5 READ WRITE MAXIMUM PERFORMANCE PRIMARY TO STANDBY
10、新备库开启实时日志应用
SYS@primary> alter database recover managed standby database disconnect from session;
Database altered.
11、查看新备库日志
Wed Aug 09 07:50:26 2023
Attempt to start background Managed Standby Recovery process (PROD5)
Starting background process MRP0
Wed Aug 09 07:50:26 2023
MRP0 started with pid=42, OS id=19986
Wed Aug 09 07:50:26 2023
MRP0: Background Managed Standby Recovery process started (PROD5)
Wed Aug 09 07:50:31 2023
Started logmerger process
Wed Aug 09 07:50:31 2023
Managed Standby Recovery starting Real Time Apply
Wed Aug 09 07:50:31 2023
Parallel Media Recovery started with 2 slaves
Wed Aug 09 07:50:32 2023
Block change tracking file is current.
Starting background process CTWR
Wed Aug 09 07:50:32 2023
CTWR started with pid=46, OS id=19994
Wed Aug 09 07:50:32 2023
Block change tracking service is active.
Wed Aug 09 07:50:32 2023
Waiting for all non-current ORLs to be archived...
Wed Aug 09 07:50:32 2023
All non-current ORLs have been archived.
Clearing online redo logfile 1 /u01/app/oracle/oradata/PROD5/redo01.log
Clearing online log 1 of thread 1 sequence number 25
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/PROD5/redo02.log
Clearing online log 2 of thread 1 sequence number 23
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u01/app/oracle/oradata/PROD5/redo03.log
Clearing online log 3 of thread 1 sequence number 24
Completed: alter database recover managed standby database disconnect from session
Clearing online redo logfile 3 complete
Wed Aug 09 07:50:33 2023
Media Recovery Log /u01/app/oracle/fast_recovery_area/PROD5H1/archivelog/2023_08_09/o1_mf_1_26_lf5o3hns_.arc
Media Recovery Waiting for thread 1 sequence 27 (in transit)
Wed Aug 09 07:50:33 2023
Recovery of Online Redo Log: Thread 1 Group 4 Seq 27 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/PROD5/redo04.log
12、查看新备库开启了实时日志应用
SYS@primary> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';
CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR RFS 1 27 IDLE
N/A MRP0 1 27 APPLYING_LOG
13、确认归档日志是否应用成功
SYS@primary> COLUMN NAME FORMAT A24
SYS@primary> COLUMN VALUE FORMAT A16
SYS@primary> COLUMN DATUM_TIME FORMAT A24
SYS@primary> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
NAME VALUE DATUM_TIME
------------------------ ---------------- ------------------------
transport lag +00 00:00:00 08/09/2023 08:04:02
apply lag +00 00:00:00 08/09/2023 08:04:02
apply finish time +00 00:00:00.000
estimated startup time 8主备回切switchover
1、主库切换前环境检查
SYS@standby> col OPEN_MODE for a10
SYS@standby> col SWITCHOVER_STATUS for a10
SYS@standby> select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER
--------- ---------- -------------------- ---------------- ----------
PROD5 READ WRITE MAXIMUM PERFORMANCE PRIMARY TO STANDBY
SYS@standby>
SYS@standby> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected
SYS@standby> select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO';
THREAD# SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- ---------- --------- --------- ---------
1 26 09-AUG-23 09-AUG-23 NO
1 26 09-AUG-23 09-AUG-23 NO
SYS@standby> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string PROD5H1, PROD5H2
db_name string PROD5
db_unique_name string PROD5H2
global_names boolean FALSE
instance_name string PROD5
lock_name_space string
log_file_name_convert string PROD5H1, PROD5H2
pdb_file_name_convert string
processor_group_name string
service_names string PROD5H2.example.com
SYS@standby>
2、备库环境检查
SYS@primary> col NAME for a5
SYS@primary> col OPEN_MODE for a20
SYS@primary> col SWITCHOVER_STATUS for a15
SYS@primary> select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STAT
----- -------------------- -------------------- ---------------- ---------------
PROD5 READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SYS@primary> COLUMN NAME FORMAT A24
SYS@primary> COLUMN VALUE FORMAT A16
SYS@primary> COLUMN DATUM_TIME FORMAT A24
SYS@primary> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
NAME VALUE DATUM_TIME
------------------------ ---------------- ------------------------
transport lag +00 00:00:00 08/10/2023 06:35:45
apply lag +00 00:00:00 08/10/2023 06:35:45
apply finish time +00 00:00:00.000
estimated startup time 8
SYS@primary> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string PROD5H2, PROD5H1
db_name string PROD5
db_unique_name string PROD5H1
global_names boolean FALSE
instance_name string PROD5
lock_name_space string
log_file_name_convert string PROD5H2, PROD5H1
pdb_file_name_convert string
processor_group_name string
service_names string PROD5H1.example.com
3、主库切备库
SYS@standby> alter database commit to switchover to physical standby;
Database altered.
4、主库日志
[oracle@host02 ~]$ tail -100f /u01/app/oracle/diag/rdbms/prod5h2/PROD5/trace/alert_PROD5.log
Thu Aug 10 06:36:36 2023
alter database commit to switchover to physical standby
Thu Aug 10 06:36:36 2023
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 12575] (PROD5)
Thu Aug 10 06:36:36 2023
Waiting for target standby to receive all redo
Thu Aug 10 06:36:36 2023
Waiting for all non-current ORLs to be archived...
Thu Aug 10 06:36:36 2023
All non-current ORLs have been archived.
Thu Aug 10 06:36:36 2023
Waiting for all FAL entries to be archived...
Thu Aug 10 06:36:36 2023
All FAL entries have been archived.
Thu Aug 10 06:36:36 2023
Waiting for potential Physical Standby switchover target to become synchronized...
Thu Aug 10 06:36:37 2023
Active, synchronized Physical Standby switchover target has been identified
Preventing updates and queries at the Primary
Generating and shipping final logs to target standby
Switchover End-Of-Redo Log thread 1 sequence 27 has been fixed
Switchover: Primary highest seen SCN set to 0x0.0x246f47
ARCH: Noswitch archival of thread 1, sequence 27
ARCH: End-Of-Redo Branch archival of thread 1 sequence 27
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Standby redo logfile selected for thread 1 sequence 27 for destination LOG_ARCHIVE_DEST_2
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received all redo
Waiting for target standby to apply all redo
Final check for a synchronized target standby. Check will be made once.
LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
Active, synchronized target has been identified
Target has also received all redo
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/prod5h2/PROD5/trace/PROD5_ora_12575.trc
Converting the primary database to a new standby database
Clearing standby activation ID 1856681335 (0x6eaab577)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Archivelog for thread 1 sequence 27 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover: Complete - Database shutdown required
USER (ospid: 12575): terminating the instance
Thu Aug 10 06:36:39 2023
Instance terminated by USER, pid = 12575
Completed: alter database commit to switchover to physical standby
Shutting down instance (abort)
License high water mark = 18
Thu Aug 10 06:36:39 2023
Instance shutdown complete
5、备库日志
[oracle@host01 ~]$ tail -100f /u01/app/oracle/diag/rdbms/prod5h1/PROD5/trace/alert_PROD5.log
Thu Aug 10 06:36:38 2023
RFS[3]: Assigned to RFS process (PID:20646)
RFS[3]: Selected log 4 for thread 1 sequence 27 dbid 1606298904 branch 893851740
Thu Aug 10 06:36:38 2023
Archived Log entry 17 added for thread 1 sequence 27 ID 0x6eaab577 dest 1:
Thu Aug 10 06:36:38 2023
RFS[4]: Assigned to RFS process (PID:20644)
RFS[4]: Possible network disconnect with primary database
Thu Aug 10 06:36:38 2023
RFS[2]: Possible network disconnect with primary database
Thu Aug 10 06:36:38 2023
Resetting standby activation ID 1856681335 (0x6eaab577)
Thu Aug 10 06:36:38 2023
Media Recovery End-Of-Redo indicator encountered
Thu Aug 10 06:36:38 2023
Media Recovery Continuing
Media Recovery Waiting for thread 1 sequence 28
6、开库
SYS@standby> startup
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2923824 bytes
Variable Size 838861520 bytes
Database Buffers 369098752 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.
7、主库变成了备库
SYS@standby> select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER
--------- ---------- -------------------- ---------------- ----------
PROD5 READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY TO PRIMARY
SYS@primary> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string PROD5H2, PROD5H1
db_name string PROD5
db_unique_name string PROD5H1
global_names boolean FALSE
instance_name string PROD5
lock_name_space string
log_file_name_convert string PROD5H2, PROD5H1
pdb_file_name_convert string
processor_group_name string
service_names string PROD5H1.example.com
8、备库切主库
SYS@primary> alter database commit to switchover to primary;
Database altered.
SYS@primary> select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STAT
----- -------------------- -------------------- ---------------- ---------------
PROD5 MOUNTED MAXIMUM PERFORMANCE PRIMARY NOT ALLOWED
9、开库
SYS@primary> alter database open;
Database altered.
SYS@primary> select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STAT
----- -------------------- -------------------- ---------------- ---------------
PROD5 READ WRITE MAXIMUM PERFORMANCE PRIMARY TO STANDBY
10、新备库开启实时日志应用
SYS@standby> alter database recover managed standby database disconnect from session;
Database altered.
SYS@standby> select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER
--------- ---------- -------------------- ---------------- ----------
PROD5 READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
WITH APPLY
11、备库日志
[oracle@host02 ~]$ tail -100f /u01/app/oracle/diag/rdbms/prod5h2/PROD5/trace/alert_PROD5.log
alter database recover managed standby database disconnect from session
Thu Aug 10 06:42:16 2023
Attempt to start background Managed Standby Recovery process (PROD5)
Starting background process MRP0
Thu Aug 10 06:42:16 2023
MRP0 started with pid=41, OS id=12747
Thu Aug 10 06:42:16 2023
MRP0: Background Managed Standby Recovery process started (PROD5)
Thu Aug 10 06:42:21 2023
Started logmerger process
Thu Aug 10 06:42:21 2023
Managed Standby Recovery starting Real Time Apply
Thu Aug 10 06:42:21 2023
Parallel Media Recovery started with 2 slaves
Thu Aug 10 06:42:21 2023
Block change tracking file is current.
Starting background process CTWR
Thu Aug 10 06:42:21 2023
CTWR started with pid=45, OS id=12755
Thu Aug 10 06:42:21 2023
Block change tracking service is active.
Thu Aug 10 06:42:21 2023
Waiting for all non-current ORLs to be archived...
Thu Aug 10 06:42:21 2023
All non-current ORLs have been archived.
Clearing online redo logfile 1 /u01/app/oracle/oradata/PROD5/redo01.log
Clearing online log 1 of thread 1 sequence number 26
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/PROD5/redo02.log
Clearing online log 2 of thread 1 sequence number 27
Clearing online redo logfile 2 complete
Thu Aug 10 06:42:22 2023
Media Recovery Log /u01/app/oracle/fast_recovery_area/PROD5H2/archivelog/2023_08_10/o1_mf_1_28_lf85h6nd_.arc
Completed: alter database recover managed standby database disconnect from session
Media Recovery Waiting for thread 1 sequence 29 (in transit)
Thu Aug 10 06:42:22 2023
Recovery of Online Redo Log: Thread 1 Group 5 Seq 29 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/PROD5/redo05.log
12、查看进程状态
SYS@standby> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 1 28 CLOSING
ARCH ARCH 0 0 CONNECTED
LGWR RFS 1 29 IDLE
UNKNOWN RFS 0 0 IDLE
ARCH RFS 0 0 IDLE
N/A MRP0 1 29 APPLYING_LOG
8 rows selected.
13、确认归档日志是否应用成功
SYS@standby> COLUMN NAME FORMAT A24
SYS@standby> COLUMN VALUE FORMAT A16
SYS@standby> COLUMN DATUM_TIME FORMAT A24
SYS@standby> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
NAME VALUE DATUM_TIME
------------------------ ---------------- ------------------------
transport lag +00 00:00:00 08/09/2023 08:04:02
apply lag +00 00:00:00 08/09/2023 08:04:02
apply finish time +00 00:00:00.000
estimated startup time 8创建dgbroker
1、登录dgmgrl
[oracle@host01 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> help
The following commands are available:
add Adds a member to the broker configuration
connect Connects to an Oracle database instance
convert Converts a database from one type to another
create Creates a broker configuration
disable Disables a configuration, a member, or fast-start failover
edit Edits a configuration or a member
enable Enables a configuration, a member, or fast-start failover
exit Exits the program
failover Changes a standby database to be the primary database
help Displays description and syntax for a command
quit Exits the program
reinstate Changes a database marked for reinstatement into a viable standby
rem Comment to be ignored by DGMGRL
remove Removes a configuration or a member
show Displays information about a configuration or a member
shutdown Shuts down a currently running Oracle database instance
sql Executes a SQL statement
start Starts the fast-start failover observer
startup Starts an Oracle database instance
stop Stops the fast-start failover observer
switchover Switches roles between a primary and standby database
validate Performs an exhaustive set of validations for a database
Use "help <command>" to see syntax for individual commands
2、创建配置
DGMGRL> connect sys/oracle@primary;
Connected as SYSDBA.
DGMGRL> CREATE CONFIGURATION dg_prod5 AS PRIMARY DATABASE IS PROD5H1 CONNECT IDENTIFIER IS primary;
Error:
ORA-16525: The Oracle Data Guard broker is not yet available.
3、主备库开启dgbroker
SYS@primary> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE
SYS@primary> alter system set dg_broker_start=true;
System altered.
SYS@standby> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE
SYS@standby> alter system set dg_broker_start=true;
System altered.
SYS@standby> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE
主备库修改静态配置文件，添加DGMGRL
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD5H1.example.com)
(SID_NAME=PROD5)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
)
(SID_DESC=
(GLOBAL_DBNAME=PROD5H1_DGMGRL.example.com)
(SID_NAME=PROD5)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
)
)
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD5H2.example.com)
(SID_NAME=PROD5)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
)
(SID_DESC=
(GLOBAL_DBNAME=PROD5H2_DGMGRL.example.com)
(SID_NAME=PROD5)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
)
)
4、创建配置
[oracle@host01 ~]$ dgmgrl
DGMGRL> connect sys/oracle@primary
Connected as SYSDBA.
DGMGRL> CREATE CONFIGURATION dg_prod5 AS PRIMARY DATABASE IS PROD5H1 CONNECT IDENTIFIER IS primary;
Configuration "dg_prod5" created with primary database "prod5h1"
5、添加备库失败
DGMGRL> ADD DATABASE PROD5H2 AS CONNECT IDENTIFIER IS standby;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added
Failed.
SYS@standby> show parameter LOG_ARCHIVE_DEST_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_
DEST VALID_FOR=(ALL_LOGFILES,A
LL_ROLES) DB_UNIQUE_NAME=PROD5
H2
SYS@standby> show parameter LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=primary LGWR ASYNC VAL
ID_FOR=(ONLINE_LOGFILES,PRIMAR
Y_ROLE) DB_UNIQUE_NAME=PROD5H1
SYS@standby> alter system set LOG_ARCHIVE_DEST_1='';
alter system set LOG_ARCHIVE_DEST_1=''
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16028: new LOG_ARCHIVE_DEST_1 causes less destinations than
LOG_ARCHIVE_MIN_SUCCEED_DEST requires
6、修改备库参数LOG_ARCHIVE_DEST_2
参考mos文档Doc ID 1582179.1
SYS@standby> alter system set LOG_ARCHIVE_DEST_2='';
System altered.
7、添加备库成功
DGMGRL> ADD DATABASE PROD5H2 AS CONNECT IDENTIFIER IS standby;
Database "prod5h2" added
8、查看配置
DGMGRL> show configuration;
Configuration - dg_prod5
Protection Mode: MaxPerformance
Members:
prod5h1 - Primary database
prod5h2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - dg_prod5
Protection Mode: MaxPerformance
Members:
prod5h1 - Primary database
prod5h2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 6 seconds ago)物理备库切换成快照备库
1、物理备库切换成快照备库
DGMGRL> CONVERT DATABASE prod5h2 to SNAPSHOT STANDBY;
Converting database "prod5h2" to a Snapshot Standby database, please wait...
Database "prod5h2" converted successfully
SYS@standby> select database_role,protection_mode,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE OPEN_MODE
---------------- -------------------- --------------------
SNAPSHOT STANDBY MAXIMUM PERFORMANCE READ WRITE
2、快照备库切换成物理备库
DGMGRL> CONVERT DATABASE prod5h2 to PHYSICAL STANDBY;
Converting database "prod5h2" to a Physical Standby database, please wait...
Operation requires shut down of instance "PROD5" on database "prod5h2"
Shutting down instance "PROD5"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires start up of instance "PROD5" on database "prod5h2"
Starting instance "PROD5"...
ORACLE instance started.
Database mounted.
Continuing to convert database "prod5h2" ...
Database "prod5h2" converted successfully
DGMGRL>
SYS@standby> select database_role,protection_mode,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE MOUNTED
DGMGRL> edit database PROD5H1 set property LogXptMode=SYNC;
Property "logxptmode" updated
DGMGRL> edit database PROD5H2 set property LogXptMode=SYNC;
Property "logxptmode" updated
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.
DGMGRL> show configuration
Configuration - dg_prod5
Protection Mode: MaxAvailability
Members:
prod5h1 - Primary database
prod5h2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 44 seconds ago)
SYS@standby> select database_role,protection_mode,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MOUNTED使用dgbroker进行switchover
1、将备库切换成主库
DGMGRL> SWITCHOVER TO prod5h2;
Performing switchover NOW, please wait...
Operation requires a connection to instance "PROD5" on database "prod5h2"
Connecting to instance "PROD5"...
Connected as SYSDBA.
New primary database "prod5h2" is opening...
Operation requires start up of instance "PROD5" on database "prod5h1"
Starting instance "PROD5"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prod5h2"
SYS@standby> select database_role,protection_mode,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE OPEN_MODE
---------------- -------------------- --------------------
PRIMARY MAXIMUM AVAILABILITY READ WRITE
SYS@primary> select database_role,protection_mode,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MOUNTED
DGMGRL> show configuration;
Configuration - dg_prod5
Protection Mode: MaxAvailability
Members:
prod5h2 - Primary database
prod5h1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 38 seconds ago)
2、主库回切
DGMGRL> SWITCHOVER TO prod5h1;
Performing switchover NOW, please wait...
Operation requires a connection to instance "PROD5" on database "prod5h1"
Connecting to instance "PROD5"...
Connected as SYSDBA.
New primary database "prod5h1" is opening...
Operation requires start up of instance "PROD5" on database "prod5h2"
Starting instance "PROD5"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "prod5h1"
SYS@standby> select database_role,protection_mode,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MOUNTED
SYS@primary> select database_role,protection_mode,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE OPEN_MODE
---------------- -------------------- --------------------
PRIMARY MAXIMUM AVAILABILITY READ WRITE
DGMGRL> show configuration;
Configuration - dg_prod5
Protection Mode: MaxAvailability
Members:
prod5h1 - Primary database
prod5h2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 38 seconds ago)自动故障转移failover
failover：自动故障转移，会破坏dg架构
1、备库开闪回
SYS@standby> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
需要先关闭实时日志应用
SYS@standby> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SYS@standby> alter database flashback on;
Database altered.
开启实时日志应用
SYS@standby> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
2、后台启动 observer
[oracle@host02 ~]$ dgmgrl sys/oracle@standby "start observer" &
[1] 8634
[oracle@host02 ~]$ DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
Observer started
[oracle@host02 ~]$ ps -ef | grep 8634
oracle 8634 4019 0 09:17 pts/4 00:00:00 dgmgrl start observer
oracle 8646 4019 0 09:18 pts/4 00:00:00 grep 8634
3、启动failover
DGMGRL> help enable
Enables a configuration, a member, or fast-start failover
Syntax:
ENABLE CONFIGURATION;
ENABLE { BACKUP_APPLIANCE | DATABASE | FAR_SYNC }
<object name>;
ENABLE FAST_START FAILOVER [CONDITION <condition>];
DGMGRL> ENABLE FAST_START FAILOVER
Enabled.
4、查看配置
DGMGRL> show configuration
Configuration - dg_prod5
Protection Mode: MaxAvailability
Members:
prod5h1 - Primary database
prod5h2 - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS (status updated 7 seconds ago)
查看日志
Thu Aug 17 09:18:51 2023
Fast-Start Failover (FSFO) has been enabled between:
Primary = "prod5h1"
Standby = "prod5h2"
Starting background process FSFP
Thu Aug 17 09:18:51 2023
FSFP started with pid=56, OS id=7519模拟failover自动故障转移实验
1、主备库要求开启闪回
SYS@primary> select flashback_on,force_logging from v$database;
FLASHBACK_ON FORCE_LOGGING
------------------ ---------------------------------------
YES YES
SYS@standby> select flashback_on,force_logging from v$database;
FLASHBACK_ON FORCE_LOGGING
------------------ ---------------------------------------
YES YES
2、只支持最大可用和最大性能两种模式，将最大性能模式切换为最大可用模式
DGMGRL> edit database prodh5h1 set property LogXptMode='SYNC';
DGMGRL> edit database prodh5h2 set property LogXptMode='SYNC';
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
3、设置FastStartFailoverTarget故障切换目标
DGMGRL> EDIT DATABASE prodh5h1 SET PROPERTY FastStartFailoverTarget = prodh5h2;
DGMGRL> EDIT DATABASE prodh5h2 SET PROPERTY FastStartFailoverTarget = prodh5h1;
DGMGRL> show database verbose prod5h1
Database - prod5h1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PROD5
FastStartFailoverTarget = 'prod5h2'
DGMGRL> show database verbose prod5h2
Database - prod5h2
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 3.00 KByte/s
Active Apply Rate: 179.00 KByte/s
Maximum Apply Rate: 179.00 KByte/s
Real Time Query: OFF
Instance(s):
PROD5
FastStartFailoverTarget = 'prod5h1'
4、故障转移默认阈值是30秒
DGMGRL> show configuration verbose;
Configuration - dg_prod5
Protection Mode: MaxAvailability
Members:
prod5h1 - Primary database
prod5h2 - (*) Physical standby database
(*) Fast-Start Failover target
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
Fast-Start Failover: ENABLED
Threshold: 30 seconds
Target: prod5h2
Observer: host02.example.com
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Observer Reconnect: (none)
Observer Override: FALSE
Configuration Status:
SUCCESS
5、模拟主库断电
DGMGRL> show configuration
Configuration - dg_prod5
Protection Mode: MaxAvailability
Members:
prod5h1 - Primary database
prod5h2 - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS (status updated 33 seconds ago)
SYS@primary> shutdown abort
ORACLE instance shut down.
6、查看主库日志
Fri Aug 18 08:25:25 2023
Shutting down instance (abort)
License high water mark = 12
Fri Aug 18 08:25:25 2023
USER (ospid: 12198): terminating the instance
Fri Aug 18 08:25:26 2023
Instance terminated by USER, pid = 12198
Fri Aug 18 08:25:26 2023
Instance shutdown complete
7、查看备库日志
Fri Aug 18 08:25:25 2023主库发生故障
Fri Aug 18 08:25:25 2023
RFS[4]: Assigned to RFS process (PID:9189)
RFS[4]: Possible network disconnect with primary database
Fri Aug 18 08:25:25 2023
RFS[1]: Possible network disconnect with primary database
Fri Aug 18 08:25:25 2023
RFS[2]: Possible network disconnect with primary database
Fri Aug 18 08:25:25 2023
RFS[3]: Possible network disconnect with primary database
Fri Aug 18 08:25:57 2023
Attempting Fast-Start Failover because the threshold of 30 seconds has elapsed.
Fri Aug 18 08:25:57 2023
Data Guard Broker: A zero data loss Fast-Start Failover will now be attempted
超过30秒阈值，执行failover
Fri Aug 18 08:25:57 2023
ALTER DATABASE FAILOVER TO PROD5H2
Fri Aug 18 08:25:57 2023
Terminal Recovery requested in process 9203
Terminal Recovery: Stopping real time apply
Fri Aug 18 08:25:57 2023
MRP0: Background Media Recovery cancelled with status 16037
Fri Aug 18 08:25:57 2023
Errors in file /u01/app/oracle/diag/rdbms/prod5h2/PROD5/trace/PROD5_pr00_9207.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 2557063
Fri Aug 18 08:25:57 2023
Errors in file /u01/app/oracle/diag/rdbms/prod5h2/PROD5/trace/PROD5_pr00_9207.trc:
ORA-16037: user requested cancel of managed recovery operation
Fri Aug 18 08:25:57 2023
MRP0: Background Media Recovery process shutdown (PROD5)
Fri Aug 18 08:25:58 2023
Terminal Recovery: Stopped real time apply
Fri Aug 18 08:25:58 2023
Attempt to do a Terminal Recovery (PROD5)
Fri Aug 18 08:25:58 2023
Media Recovery Start: Managed Standby Recovery (PROD5)
Fri Aug 18 08:25:58 2023
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Terminal Recovery timestamp is '08/18/2023 08:25:58'
Terminal Recovery: applying standby redo logs.
Terminal Recovery: thread 1 seq# 46 redo required
执行switchover，备库切换城主库，变成主库的scn是2557062
Fri Aug 18 08:25:58 2023
ALTER DATABASE SWITCHOVER TO PRIMARY (PROD5)
Maximum wait for role transition is 15 minutes.
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/prod5h2/PROD5/trace/PROD5_rsm0_9203.trc
Standby terminal recovery start SCN: 2557063
RESETLOGS after incomplete recovery UNTIL CHANGE 2557064 time 08/18/2023 08:25:25
Online logfile pre-clearing operation disabled by switchover
Online log /u01/app/oracle/oradata/PROD5/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/PROD5/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/PROD5/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 2557062
Fri Aug 18 08:25:58 2023
Setting recovery target incarnation to 4
Fri Aug 18 08:25:58 2023
Switchover: Complete - Database mounted as primary
Completed: ALTER DATABASE FAILOVER TO PROD5H2
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
Completed: ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
ALTER DATABASE OPEN
SYS@standby> select STANDBY_BECAME_PRIMARY_SCN,DB_UNIQUE_NAME,DATABASE_ROLE from v$database;
STANDBY_BECAME_PRIMARY_SCN DB_UNIQUE_NAME DATABASE_ROLE
-------------------------- ------------------------------ ----------------
2557062 PROD5H2 PRIMARY
SYS@standby> select name,log_mode,open_mode,protection_mode,database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE
--------- ------------ -------------------- --------------------
DATABASE_ROLE
----------------
PROD5 ARCHIVELOG READ WRITE MAXIMUM AVAILABILITY
PRIMARY
8、查看dgbroker状态 prod5h1需要恢复
DGMGRL> connect sys/oracle@primary
Connected as SYSDBA.
DGMGRL> show configuration
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
Configuration details cannot be determined by DGMGRL
DGMGRL> connect sys/oracle@standby
Connected as SYSDBA.
DGMGRL> show configuration
Configuration - dg_prod5
Protection Mode: MaxAvailability
Members:
prod5h2 - Primary database
Warning: ORA-16817: unsynchronized fast-start failover configuration
prod5h1 - (*) Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: ENABLED
Configuration Status:
WARNING (status updated 46 seconds ago)恢复备库
1、打开备库，只能启动到mount状态，这个过程，备库会自动执行flashback database，闪回到变成备库的时间点
SYS@primary> startup
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2923824 bytes
Variable Size 838861520 bytes
Database Buffers 369098752 bytes
Redo Buffers 13852672 bytes
Database mounted.
ORA-16649: possible failover to another database prevents this database from
being opened
2、查看备库日志
Completed: ALTER DATABASE MOUNT
Fri Aug 18 08:41:04 2023
ALTER DATABASE OPEN
Data Guard Broker initializing...
Fri Aug 18 08:41:08 2023
Starting Data Guard Broker (DMON)
Starting background process INSV
Fri Aug 18 08:41:08 2023
INSV started with pid=29, OS id=14278
Fri Aug 18 08:41:08 2023
Data Guard Broker initialization complete
Data Guard: verifying database primary role...
Starting background process NSV1
Fri Aug 18 08:41:12 2023
NSV1 started with pid=30, OS id=14280
Starting background process RSM0
Fri Aug 18 08:41:17 2023
RSM0 started with pid=31, OS id=14282
Data Guard: version check completed
Data Guard determines a failover has occurred - this is no longer a primary database
ORA-16649 signalled during: ALTER DATABASE OPEN...
Fri Aug 18 08:41:17 2023
Checker run found 1 new persistent data failures
Fri Aug 18 08:41:21 2023
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Fri Aug 18 08:41:21 2023
ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;
Starting background process TMON
Fri Aug 18 08:41:21 2023
TMON started with pid=32, OS id=14290
ARCH: STARTING ARCH PROCESSES
Starting background process ARC0
Fri Aug 18 08:41:21 2023
ARC0 started with pid=33, OS id=14292
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
Fri Aug 18 08:41:21 2023
ARC0: STARTING ARCH PROCESSES
Starting background process ARC1
Fri Aug 18 08:41:21 2023
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Starting background process ARC2
Fri Aug 18 08:41:21 2023
ARC1 started with pid=34, OS id=14294
Fri Aug 18 08:41:21 2023
ARC2 started with pid=35, OS id=14296
Starting background process ARC3
ARC1: Archival started
ARC2: Archival started
Fri Aug 18 08:41:21 2023
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
Fri Aug 18 08:41:21 2023
ARC1: Becoming the heartbeat ARCH
Fri Aug 18 08:41:21 2023
ARC3 started with pid=30, OS id=14298
Fri Aug 18 08:41:21 2023
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
执行闪回FLASHBACK DATABASE TO SCN 2557062
将数据库切换成物理备库alter database convert to physical standby
Starting background process NSV1
Fri Aug 18 08:41:40 2023
NSV1 started with pid=36, OS id=14302
FLASHBACK DATABASE TO SCN 2557062
Fri Aug 18 08:41:43 2023
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Started logmerger process
Fri Aug 18 08:41:44 2023
Parallel Media Recovery started with 2 slaves
Fri Aug 18 08:41:44 2023
Recovery of Online Redo Log: Thread 1 Group 2 Seq 46 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/PROD5/redo02.log
Fri Aug 18 08:41:44 2023
Incomplete Recovery applied until change 2557063 time 08/18/2023 08:25:25
Fri Aug 18 08:41:44 2023
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO SCN 2557062
alter database convert to physical standby
Fri Aug 18 08:41:44 2023
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (PROD5)
3、open数据库
SYS@primary> alter database open;
Database altered.
SYS@primary> select name,log_mode,open_mode,protection_mode,database_role from v$database;
NAME LOG_MODE OPEN_MODE PROTECTION_MODE
--------- ------------ -------------------- --------------------
DATABASE_ROLE
----------------
PROD5 ARCHIVELOG READ ONLY WITH APPLY MAXIMUM AVAILABILITY
PHYSICAL STANDBY
4、查看dgbroker状态已经恢复
DGMGRL> show configuration;
Configuration - dg_prod5
Protection Mode: MaxAvailability
Members:
prod5h2 - Primary database
prod5h1 - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS (status updated 22 seconds ago)恢复最初主备状态
1、执行switchover主备切换
DGMGRL> switchover to prod5h1;
Performing switchover NOW, please wait...
Operation requires a connection to instance "PROD5" on database "prod5h1"
Connecting to instance "PROD5"...
Connected as SYSDBA.
New primary database "prod5h1" is opening...
Operation requires start up of instance "PROD5" on database "prod5h2"
Starting instance "PROD5"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "prod5h1"
2、查看配置
DGMGRL> show configuration
Configuration - dg_prod5
Protection Mode: MaxAvailability
Members:
prod5h1 - Primary database
prod5h2 - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS (status updated 22 seconds ago)
1210
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
