Linux Oracle ADG数据库同步配置

本文参考自
https://www.cnblogs.com/zheng-hong-bo/p/12374078.html

set linesize 200;
set long 100000;

环境信息： 教程中IP
yxn-c76-oracle11g-172.16.102.20 main 10.211.55.100
yxn-c76-oracle11g-172.16.102.21 standby 10.211.55.101

查看DG

SELECT * FROM v$option where parameter=‘Oracle Data Guard’;

主端

select name,log_mode,force_logging from v$database;
alter database force logging;

开启强制日志模式

ALTER DATABASE NO FORCE LOGGING;

关闭强制日志模式

主端开启归档模式

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;

在主库创建密码文件，将密码文件拷贝至备机。

cd $ORACLE_HOME/dbs
orapwd file=orapworcl password=123456 force=y

scp orapworcl oracle@172.16.102.21:$ORACLE_HOME/dbs/orapworcl/orapworcl

主库上创建standby redolog日志组。

SQL> select thread#,group#,bytes/1024/1024 from v$log;

THREAD# GROUP# BYTES/1024/1024

---

     1          1              50
     1          2              50
     1          3              50

当前实例日志组个数为3，所以需要至少需要创建（3+1）*1=4组standby redolog，大小均为50M。

col member for a50;
select group#,member from v$logfile;

GROUP# MEMBER

---

     3 /home/oracle/app/oracle/oradata/orcl/redo03.log
     2 /home/oracle/app/oracle/oradata/orcl/redo02.log
     1 /home/oracle/app/oracle/oradata/orcl/redo01.log

新建4个standby redolog日志组，编号从4开始：

SQL>
alter database add standby logfile group 4 ‘/home/oracle/app/oracle/oradata/orcl/standby_redo04.log’ size 50M;
alter database add standby logfile group 5 ‘/home/oracle/app/oracle/oradata/orcl/standby_redo05.log’ size 50M;
alter database add standby logfile group 6 ‘/home/oracle/app/oracle/oradata/orcl/standby_redo06.log’ size 50M;
alter database add standby logfile group 7 ‘/home/oracle/app/oracle/oradata/orcl/standby_redo07.log’ size 50M;

set pagesize 100;
col member for a60;
select group#,member from v$logfile order by group#;

SQL> set pagesize 100;
col member for a60;
select group#,member from v$logfile order by group#;SQL> SQL>

GROUP# MEMBER

---

     1 /home/oracle/app/oracle/oradata/orcl/redo01.log
     2 /home/oracle/app/oracle/oradata/orcl/redo02.log
     3 /home/oracle/app/oracle/oradata/orcl/redo03.log
     4 /home/oracle/app/oracle/oradata/orcl/standby_redo04.log
     5 /home/oracle/app/oracle/oradata/orcl/standby_redo05.log
     6 /home/oracle/app/oracle/oradata/orcl/standby_redo06.log
     7 /home/oracle/app/oracle/oradata/orcl/standby_redo07.log

7 rows selected.

修改主库和备库监听文件，设置静态监听

cd $ORACLE_HOME/network/admin/
vim listener.ora

主库的listener.ora文件改成：
[oracle@apollo admin]$ cat listener.ora

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME= orcl)
(ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=orcl)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.102.20)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /home/oracle/app/oracle

备库的listener.ora文件：

[oracle@neptune admin]$ cat listener.ora

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME= orcl)
(ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=orcl)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.102.21)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /home/oracle/app/oracle

主库和备库的监听文件listener.ora按照上面改好后，重启监听服务

lsnrctl stop

lsnrctl status

lsnrctl start

lsnrctl status

编辑主库和备库的网络服务名tnsname.ora文件

主库和备库的tnsnames.ora文件是一模一样的。

PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.102.20)(PORT = 1521))
(CONNECT_DATA =
(SID=orcl)
)
)

STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.102.21)(PORT = 1521))
(CONNECT_DATA =
(SID=orcl)
)
)

主库和备库的tnsnames.ora文件是一模一样的。

配好后，tnsping一下对方，看是否通的：

主库tnsping备库：

[oracle@apollo admin]$ tnsping STANDBY

配置主端pfile

show parameter spfile;

create pfile from spfile;

orcl.__db_cache_size=3087007744
orcl.__java_pool_size=67108864
orcl.__large_pool_size=67108864
orcl.__oracle_base=’/home/oracle/app/oracle’#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=2684354560
orcl.__sga_target=4026531840
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=738197504
orcl.__streams_pool_size=0
*.audit_file_dest=’/home/oracle/app/oracle/admin/orcl/adump’
*.audit_trail=‘db’
*.compatible=‘11.2.0.0.0’
*.control_files=’/home/oracle/app/oracle/oradata/orcl/control01.ctl’,’/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl’
*.db_block_size=8192
*.db_domain=’’
*.db_name=‘orcl’
*.db_recovery_file_dest=’/home/oracle/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest=’/home/oracle/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.memory_target=6662651904
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=‘EXCLUSIVE’
*.undo_tablespace=‘UNDOTBS1’

*.fal_client=‘PRIMARY’
*.fal_server=‘STANDBY’
*.log_archive_config=‘DG_CONFIG=(PRIMARY,STANDBY)’
*.log_archive_dest_1=‘location=/home/oracle/app/oracle/oradata/orcl/arc valid_for=(all_logfiles,all_roles) db_unique_name=PRIMARY’
*.log_archive_dest_2=‘service=STANDBY lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=STANDBY’
*.standby_file_management=‘AUTO’
*.db_unique_name=‘PRIMARY’

log_archive_dest_1 为主端归档路径/home/oracle/app/oracle/oradata/orcl/arc

在主库手动创建arc目录

cd /home/oracle/app/oracle/oradata/orcl
mkdir -p arc

shutdown immediate;
create spfile from pfile;
startup open;

show parameter spfile;

路径存在则说明是用create的spfile加载数据库

create pfile from spfile;

create pfile=’/home/oracle/pfile.ora’ from spfile;

查看生成的pfile,看是否生效

直接将pfile从主库拷贝到备库，然后在备库修改pfile文件

scp initorcl.ora oracle@172.16.102.21:/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs

切到备库，编辑pfile文件

vim /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora

orcl.__db_cache_size=3087007744
orcl.__java_pool_size=67108864
orcl.__large_pool_size=67108864
orcl.__oracle_base=’/home/oracle/app/oracle’#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=2684354560
orcl.__sga_target=4026531840
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=738197504
orcl.__streams_pool_size=0
*.audit_file_dest=’/home/oracle/app/oracle/admin/orcl/adump’
*.audit_trail=‘db’
*.compatible=‘11.2.0.0.0’
*.control_files=’/home/oracle/app/oracle/oradata/orcl/control01.ctl’,’/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl’
*.db_block_size=8192
*.db_domain=’’
*.db_name=‘orcl’
*.db_recovery_file_dest=’/home/oracle/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest=’/home/oracle/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.memory_target=6662651904
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=‘EXCLUSIVE’
*.undo_tablespace=‘UNDOTBS1’

*.fal_client=‘STANDBY’
*.fal_server=‘PRIMARY’
*.log_archive_config=‘DG_CONFIG=(PRIMARY,STANDBY)’
*.log_archive_dest_1=‘location=/home/oracle/app/oracle/oradata/orcl/arc valid_for=(all_logfiles,all_roles) db_unique_name=STANDBY’
*.log_archive_dest_2=‘service=STANDBY lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=PRIMARY’
*.standby_file_management=‘AUTO’
*.db_unique_name=‘STANDBY’
*.db_file_name_convert=’/home/oracle/app/oracle/oradata/orcl/’,’/home/oracle/app/oracle/oradata/orcl/’,’/home/oracle/Oracle_datafile/’,’/home/oracle/Oracle_datafile/’
*.log_file_name_convert=’/home/oracle/app/oracle/oradata/orcl/’,’/home/oracle/app/oracle/oradata/orcl/’

db_file_name_convert

db_file_name_convert 主数据库和备用数据库的数据文件转换目录对映（如果两数据库的目录结构不一样），如果有多个对映，逐一指明对映关系。

格式： *.db_file_name_convert=‘主数据库数据文件目录’,‘备用数据库数据文件目录’

log_file_name_convert

指明主数据库和备用数据库的log文件转换目录对映。

格式： *. log_file_name_convert=‘主数据库redolog目录’,‘备用数据库redolog目录’

查看数据文件路径

select name from v$datafile;

查看redolog文件路径

SELECT MEMBER FROM v$logfile;

备库上手工创建相应目录

mkdir -p /home/oracle/app/oracle/oradata/orcl/arc

mkdir -p /home/oracle/app/oracle/admin/orcl/adump

备库上从pfile创建spfile，重启数据库至nomount状态

shutdown immediate;
create spfile from pfile;
startup nomount;
show parameter spfile;

create pfile=’/home/oracle/pfile.ora’ from spfile;

利用RMAN在备库上恢复主库。

rman target sys/123456@primary auxiliary sys/123456@standby

duplicate target database for standby from active database nofilenamecheck;

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

以下是回显信息

RMAN>
RMAN>
RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 24-AUG-20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=189 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile ‘/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl’ auxiliary format
‘/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl’ ;
}
executing Memory Script

Starting backup at 24-AUG-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
Finished backup at 24-AUG-20

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/home/oracle/app/oracle/oradata/orcl/control01.ctl’;
restore clone controlfile to ‘/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl’ from
‘/home/oracle/app/oracle/oradata/orcl/control01.ctl’;
}
executing Memory Script

Starting backup at 24-AUG-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20200824T175549 RECID=4 STAMP=1049306150
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
Finished backup at 24-AUG-20

Starting restore at 24-AUG-20
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 24-AUG-20

contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script

sql statement: alter database mount standby database
Using previous duplicated file /home/oracle/app/oracle/oradata/orcl/system01.dbf for datafile 1 with checkpoint SCN of 1069782
Using previous duplicated file /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf for datafile 2 with checkpoint SCN of 1070042
Using previous duplicated file /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf for datafile 3 with checkpoint SCN of 1070101
Using previous duplicated file /home/oracle/app/oracle/oradata/orcl/users01.dbf for datafile 4 with checkpoint SCN of 1070110
Using previous duplicated file /home/oracle/app/oracle/oradata/orcl/example01.dbf for datafile 5 with checkpoint SCN of 1070104

contents of Memory Script:
{
set newname for tempfile 1 to
“/home/oracle/app/oracle/oradata/orcl/temp01.dbf”;
set newname for tempfile 2 to
“/home/oracle/Oracle_datafile/user_temp.dbf”;
switch clone tempfile all;
set newname for datafile 6 to
“/home/oracle/Oracle_datafile/user_data.dbf”;
backup as copy reuse
datafile 6 auxiliary format
“/home/oracle/Oracle_datafile/user_data.dbf” ;
sql ‘alter system archive log current’;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/app/oracle/oradata/orcl/temp01.dbf in control file
renamed tempfile 2 to /home/oracle/Oracle_datafile/user_temp.dbf in control file

executing command: SET NEWNAME

Starting backup at 24-AUG-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/home/oracle/Oracle_datafile/user_data.dbf
output file name=/home/oracle/Oracle_datafile/user_data.dbf tag=TAG20200824T175557
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 24-AUG-20

sql statement: alter system archive log current

contents of Memory Script:
{
backup as copy reuse
archivelog like “/home/oracle/app/oracle/oradata/orcl/arc/1_15_1049255031.dbf” auxiliary format
“/home/oracle/app/oracle/oradata/orcl/arc/1_15_1049255031.dbf” archivelog like
“/home/oracle/app/oracle/oradata/orcl/arc/1_16_1049255031.dbf” auxiliary format
“/home/oracle/app/oracle/oradata/orcl/arc/1_16_1049255031.dbf” archivelog like
“/home/oracle/app/oracle/oradata/orcl/arc/1_17_1049255031.dbf” auxiliary format
“/home/oracle/app/oracle/oradata/orcl/arc/1_17_1049255031.dbf” archivelog like
“/home/oracle/app/oracle/oradata/orcl/arc/1_18_1049255031.dbf” auxiliary format
“/home/oracle/app/oracle/oradata/orcl/arc/1_18_1049255031.dbf” archivelog like
“/home/oracle/app/oracle/oradata/orcl/arc/1_19_1049255031.dbf” auxiliary format
“/home/oracle/app/oracle/oradata/orcl/arc/1_19_1049255031.dbf” archivelog like
“/home/oracle/app/oracle/oradata/orcl/arc/1_20_1049255031.dbf” auxiliary format
“/home/oracle/app/oracle/oradata/orcl/arc/1_20_1049255031.dbf” archivelog like
“/home/oracle/app/oracle/oradata/orcl/arc/1_21_1049255031.dbf” auxiliary format
“/home/oracle/app/oracle/oradata/orcl/arc/1_21_1049255031.dbf” ;
catalog clone archivelog “/home/oracle/app/oracle/oradata/orcl/arc/1_15_1049255031.dbf”;
catalog clone archivelog “/home/oracle/app/oracle/oradata/orcl/arc/1_16_1049255031.dbf”;
catalog clone archivelog “/home/oracle/app/oracle/oradata/orcl/arc/1_17_1049255031.dbf”;
catalog clone archivelog “/home/oracle/app/oracle/oradata/orcl/arc/1_18_1049255031.dbf”;
catalog clone archivelog “/home/oracle/app/oracle/oradata/orcl/arc/1_19_1049255031.dbf”;
catalog clone archivelog “/home/oracle/app/oracle/oradata/orcl/arc/1_20_1049255031.dbf”;
catalog clone archivelog “/home/oracle/app/oracle/oradata/orcl/arc/1_21_1049255031.dbf”;
catalog clone datafilecopy “/home/oracle/app/oracle/oradata/orcl/system01.dbf”,
“/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf”,
“/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf”,
“/home/oracle/app/oracle/oradata/orcl/users01.dbf”,
“/home/oracle/app/oracle/oradata/orcl/example01.dbf”;
switch clone datafile 1 to datafilecopy
“/home/oracle/app/oracle/oradata/orcl/system01.dbf”;
switch clone datafile 2 to datafilecopy
“/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf”;
switch clone datafile 3 to datafilecopy
“/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf”;
switch clone datafile 4 to datafilecopy
“/home/oracle/app/oracle/oradata/orcl/users01.dbf”;
switch clone datafile 5 to datafilecopy
“/home/oracle/app/oracle/oradata/orcl/example01.dbf”;
switch clone datafile all;
}
executing Memory Script

Starting backup at 24-AUG-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=15 RECID=4 STAMP=1049302240
output file name=/home/oracle/app/oracle/oradata/orcl/arc/1_15_1049255031.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=16 RECID=5 STAMP=1049303781
output file name=/home/oracle/app/oracle/oradata/orcl/arc/1_16_1049255031.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=17 RECID=6 STAMP=1049303785
output file name=/home/oracle/app/oracle/oradata/orcl/arc/1_17_1049255031.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=18 RECID=7 STAMP=1049304157
output file name=/home/oracle/app/oracle/oradata/orcl/arc/1_18_1049255031.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=19 RECID=8 STAMP=1049305237
output file name=/home/oracle/app/oracle/oradata/orcl/arc/1_19_1049255031.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=20 RECID=9 STAMP=1049305238
output file name=/home/oracle/app/oracle/oradata/orcl/arc/1_20_1049255031.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=21 RECID=10 STAMP=1049306158
output file name=/home/oracle/app/oracle/oradata/orcl/arc/1_21_1049255031.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 24-AUG-20

cataloged archived log
archived log file name=/home/oracle/app/oracle/oradata/orcl/arc/1_15_1049255031.dbf RECID=1 STAMP=1049306166

cataloged archived log
archived log file name=/home/oracle/app/oracle/oradata/orcl/arc/1_16_1049255031.dbf RECID=2 STAMP=1049306166

cataloged archived log
archived log file name=/home/oracle/app/oracle/oradata/orcl/arc/1_17_1049255031.dbf RECID=3 STAMP=1049306166

cataloged archived log
archived log file name=/home/oracle/app/oracle/oradata/orcl/arc/1_18_1049255031.dbf RECID=4 STAMP=1049306166

cataloged archived log
archived log file name=/home/oracle/app/oracle/oradata/orcl/arc/1_19_1049255031.dbf RECID=5 STAMP=1049306166

cataloged archived log
archived log file name=/home/oracle/app/oracle/oradata/orcl/arc/1_20_1049255031.dbf RECID=6 STAMP=1049306166

cataloged archived log
archived log file name=/home/oracle/app/oracle/oradata/orcl/arc/1_21_1049255031.dbf RECID=7 STAMP=1049306166

cataloged datafile copy
datafile copy file name=/home/oracle/app/oracle/oradata/orcl/system01.dbf RECID=4 STAMP=1049306166
cataloged datafile copy
datafile copy file name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf RECID=5 STAMP=1049306166
cataloged datafile copy
datafile copy file name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf RECID=6 STAMP=1049306166
cataloged datafile copy
datafile copy file name=/home/oracle/app/oracle/oradata/orcl/users01.dbf RECID=7 STAMP=1049306166
cataloged datafile copy
datafile copy file name=/home/oracle/app/oracle/oradata/orcl/example01.dbf RECID=8 STAMP=1049306166

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1049306166 file name=/home/oracle/app/oracle/oradata/orcl/system01.dbf

datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=1049306166 file name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1049306166 file name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=1049306166 file name=/home/oracle/app/oracle/oradata/orcl/users01.dbf

datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=1049306166 file name=/home/oracle/app/oracle/oradata/orcl/example01.dbf

datafile 6 switched to datafile copy
input datafile copy RECID=9 STAMP=1049306167 file name=/home/oracle/Oracle_datafile/user_data.dbf
Finished Duplicate Db at 24-AUG-20

RMAN> quit;

至此，ADG搭建完成，补充说明下duplicate target database for standby from active database nofilenamecheck;这个命令，这个命令可以直接恢复数据文件、standby日志文件和控制文件。

检查主库和备库的归档序列号是否一致

alter system switch logfile;

主库（先手动切换一下日志文件），这个命令每执行一次，Current log sequence就会加一

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/app/oracle/oradata/orcl/arc
Oldest online log sequence 26
Next log sequence to archive 28
Current log sequence 28
SQL>

Current log sequence为28

备库

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/app/oracle/oradata/orcl/arc
Oldest online log sequence 26
Next log sequence to archive 0
Current log sequence 28
SQL>

Current log sequence也为28，可见主库和备库的归档序列号是一致的

备库恢复完成后，数据库的状态是mounted，并且此时DG的保护模式是最高性能，查询备库：

SQL> select database_role,protection_mode,protection_level,open_mode from v$database;

DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE

---

PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED

shutdown immediate;
startup open;

重启后查看数据库的状态是只读

SQL> select database_role,protection_mode,protection_level,open_mode from v$database;

DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE

---

PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY

SQL> select process,client_process,sequence#,status from v$managed_standby;

PROCESS CLIENT_P SEQUENCE# STATUS

---

ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 28 CLOSING
ARCH ARCH 0 CONNECTED
RFS N/A 0 IDLE
RFS LGWR 29 IDLE

6 rows selected.

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

SQL> select process,client_process,sequence#,status from v$managed_standby;

PROCESS CLIENT_P SEQUENCE# STATUS

---

ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 28 CLOSING
ARCH ARCH 0 CONNECTED
RFS N/A 0 IDLE
RFS LGWR 29 IDLE
MRP0 N/A 29 APPLYING_LOG

7 rows selected.

SQL>