Oracle 19c DG物理备库搭建（RMAN方式）

1.设置主库

代码如下（示例）：

SYS@orcl>startup mount;
SYS@orcl>alter database archivelog;
SYS@orcl>alter database open;
确认归档模式
SYS@orcl>archive log list;
查询实例的归档模式和附加日志
SYS@orcl>select log_mode,force_logging from v$database;
开启附加日志模式
alter database set standby nologging for data availability; --高可用模式
alter database set standby nologging for load performance； --性能模式
查询redo日志数量
SYS@orcl>select group#,status,type,member from v$logfile;
查询redo日志容量
SYS@orcl>select group# ,sequence#, bytes/104448,status from v$log;
给主库创建stand by的redo日志组，方便角色切换为备库时使用
容量要和redo相同，数量是redo当前数量+1
SYS@orcl>alter database add standby logfile group 4 ('/u01/app/oracle/oradata/ORCL/stredo04.log') size 200m;
Database altered.

SYS@orcl>alter database add standby logfile group 6 ('/u01/app/oracle/oradata/ORCL/stredo06.log') size 200m;

Database altered.

SYS@orcl>alter database add standby logfile group 7 ('/u01/app/oracle/oradata/ORCL/stredo07.log') size 200m;

Database altered.
确认standby redo logfile创建成功
SYS@orcl>select group#,status,used from v$standby_log;

2.设置备库

代码如下（示例）：

复制主库密码文件和参数文件到备库改名为
initstddb.ora（create spfile的时候要把原来那个挪走，然后把这个改成initorcl.ora才能成功）
orapwstddb

备库生成spfile并启动实例到nomount
SYS@stddb> shutdown immediate;
SYS@stddb> create spfile from pfile;
SYS@stddb> startup nomount;

3.修改备库的pfile

*.audit_file_dest='/u01/app/oracle/admin/stddb/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/stddb/control01.ctl','/u01/app/oracle/fast_recovery_area/stddb/control02.ctl'
*.db_block_size=8192
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=12732m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_ORCL'
*.memory_target=1507m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=3000
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'


*.db_unique_name='stddb'
*.log_archive_config='dg_config=(pridb,stddb)'
*.fal_client='stddb' --调换位置与主库相反
*.fal_server='pridb'
本地归档文件路径
*.log_archive_dest_1='location=/u01/app/oracle/arch/stddb valid_for=(all_logfiles,all_roles) db_unique_name=stddb'
远程归档文件传输给的服务名和唯一库ID-这里要填写主库的
*.log_archive_dest_2='service=pridb valid_for=(online_logfiles,primary_role) db_unique_name=pridb'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
这里前面要填主库的本机绝对路径，后面为备库的转换路径，格式要一致，要么都加'/'，要么都不加
*.log_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/stddb/'
*.db_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/stddb/'
STANDBY_FILE_MANAGEMENT=AUTO

4.创建备库需要的文件夹

mkdir -p /u01/app/oracle/admin/stddb/adump
mkdir -p /u01/app/oracle/oradata/stddb/
mkdir -p /u01/app/oracle/arch/stddb/
mkdir -p /u01/app/oracle/oradata/stddb/
mkdir -pv /u01/app/oracle/oradata/stddb/{orclpdb,pdbseed}

5.修改主库和备库的监听

主库listener.ora

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
 )
SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (GLOBAL_DBNAME = pridb.up.com)
     (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1)
     (SID_NAME = orcl)
   )
 (SID_DESC =
     (GLOBAL_DBNAME = stddb.up.com)
     (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1)
     (SID_NAME = orcl)
   )
 )

主库tnsnames.ora

LISTENER_ORCL =
 (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c)(PORT = 1521))
ORCL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = orcl)
   )
 )
ORCLPDB =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = orclpdb)
   )
 )

STDDB =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.201)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = stddb.up.com)
   )
 )
PRIDB =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.200)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = pridb.up.com)
   )
 )

备库listener.ora

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c2)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
 )
SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (GLOBAL_DBNAME = stddb.up.com)
     (ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1)
     (SID_NAME = orcl)
   )
 )

备库tnsnames.ora

LISTENER_ORCL =
 (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c2)(PORT = 1521))
ORCL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c2)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = orcl)
   )
 )
ORCLPDB =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c2)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = orclpdb)
   )
 )

STDDB =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.201)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = stddb.up.com)
   )
 )
PRIDB =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.200)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = pridb.up.com)
   )
 )

6.测试网络互通性

[oracle@主库 ~]$ tnsping stddb
[oracle@备库 ~]$ tnsping pridb
测试登录
sqlplus sys/oracle@stddb as sysdba
sqlplus sys/oracle@pridb as sysdba

7.使用RMAN备份主库

mkdir ~/dgback --建立备份目录
rman target /
关闭主库的控制文件自动备份功能（19c默认开启）
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP off;
指定备份目录
configure channel device type disk format '/home/oracle/dgback/%d_%I_%s_%p.bkp';
执行备份
backup as compressed backupset database include current controlfile for standby plus archivelog;

8.restore备库

拷贝数据文件到备库
[oracle@oracle19c ~/dgback]$ scp * 172.16.0.201:${PWD}

执行备库恢复
备库上需要把主库拷贝来的pfile改名为当前实例initorcl.ora
然后在nomount模式下执行
create spfile from pfile
启动RMAN 连接目标库与辅助数据库
rman target sys/oracle@pridb auxiliary sys/oracle@stddb
执行duplicate过程
RMAN> duplicate target database for standby;

9.recovery备库

开启备库
SYS@orcl>alter database open;
查看数据库角色
SYS@orcl>select database_role from v$database;
查看归档状态
SYS@orcl>select sequence#,applied from v$archived_log order by sequence#;
开启日志应用服务
alter database recover managed standby database disconnect from session;
在主库切换几次日志
SYS@pridb>alter system switch logfile;
然后回到备库确认主库的归档文件已经在备库引用
SYS@stddb>select sequence#,applied from v$archived_log order by sequence#;
查看数据库状态
SYS@stddb>select name,open_mode from v$database;
停止日志应用服务
alter database recover managed standby database cancel;
启动备库到OPEN状态
SYS@stddb>alter database open;
查看备库状态，目前为只读状态
SYS@stddb>select name,open_mode from v$database;

备库在OPEN状态下开启日志应用服务
SYS@orcl> alter database recover managed standby database using current logfile disconnect;
此时备库可变为读写分离状态下的只读库
SYS@orcl>select name,open_mode from v$database;

NAME      OPEN_MODE
ORCL      READ ONLY WITH APPLY