-- 主备库Grid及Oracle Home 目录
/u01/app/grid/product/19.0.0/gihome_1
/u01/app/oraInventory
/u01/app/oracle/product/19.0.0/dbhome_1
-- 主库开启强制日志模式、归档模式
SQL> alter database force logging;
SQL> select force_logging from v$database;
SQL> archive log list;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> alter system set sga_max_size=180G scope=spfile;
SQL> alter system set sga_target=180G scope=spfile;
SQL> alter system set pga_aggregate_target=30G scope=spfile;
-- 主库查询添加 stby redo logfile
SQL> select group#, members, bytes from v$log;
SQL> select member from v$logfile;
SQL> alter database add standby logfile group 7 ('+DATA') size 1G;
SQL> alter database add standby logfile group 8 ('+DATA') size 1G;
SQL> alter database add standby logfile group 9 ('+DATA') size 1G;
SQL> alter database add standby logfile group 10 ('+DATA') size 1G;
SQL> alter database add standby logfile group 11 ('+DATA') size 1G;
SQL> alter database add standby logfile group 12 ('+DATA') size 1G;
SQL> alter database add standby logfile group 13 ('+DATA') size 1G;
-- 分别在主备库配置监听并启动,直接使用 netmgr 工具生成 添加如下内容
$ vi listener.ora
-- prmy
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ynsb)
(ORACLE_HOME =/u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = ynsb)
)
)
-- stby
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ynsbsby)
(ORACLE_HOME =/u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = ynsbsby)
)
)
$ lsnrctl reload
-- 主备库配置 tnsnames.ora 内容如下
$ vi tnsnames.ora
LISTENER_YNSB =
(ADDRESS = (PROTOCOL = TCP)(HOST = sjz)(PORT = 1521))
YNSB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sjz)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ynsb)
)
)
YNSBSBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sjz)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ynsbsby)
)
)
-- 配置好后拷贝到备库
$ scp $ORACLE_HOME/network/admin/ * oracle@172.30.2.54:$ORACLE_HOME/network/admin/
-- 在备库创建必要的目录,参考主库的pfile中的路径：
$ mkdir -p /u01/app/oracle/fast_recovery_area
$ mkdir -p /u01/app/oracle/oradata/... ...
$ mkdir -p /u01/app/oracle/admin/ynsb/adump
ASMCMD> cd +DATA
ASMCMD> mkdir YNSBSBY
ASMCMD> cd YNSBSBY
ASMCMD> mkdir DATAFILE
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir PARAMETERFILE
ASMCMD> mkdir TEMPFILE
-- 主库创建 pfile 文件并修改 pfile 如下参数
*.DB_NAME='ynsb'
*.DB_UNIQUE_NAME='ynsb'
alter system set standby_file_management='AUTO';
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ynsb,ynsbsby)'; -- DB_UNIQUE_NAME
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/arch/archive_log/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ynsb';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=YNSBSBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) LGWR AFFIRM SYNC DB_UNIQUE_NAME=ynsbsby';
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.archive_lag_target=1800
*.LOG_ARCHIVE_MAX_PROCESSES=30
-- TNSNAME
alter system set FAL_SERVER='YNSB';
alter system set FAL_CLIENT='YNSBSBY';
alter system set LOG_FILE_NAME_CONVERT='/arch/archive_log/','/arch/archive_log/' scope=spfile;
alter system set DB_FILE_NAME_CONVERT='+DATA/YNSB/DATAFILE/','+DATA/YNSBSBY/DATAFILE/','+DATA/YNSB/TEMPFILE/','+DATA/YNSBSBY/TEMPFILE/' scope=spfile;
-- 将主库的口令文件及修改后的参数文件copy到备库
$ scp /home/oracle/temp.ora oracle@192.168.1.101:/home/oracle/temp.ora
$ scp $ORACLE_HOME/dbs/orapwdprmy oracle@192.168.1.101:$ORACLE_HOME/dbs/orapwdstby
-- 主库的参数文件copy到备库并修改如下参数
*.db_unique_name='ynsbsby'
*.log_archive_config='dg_config=(ynsb,ynsbsby)'
*.log_archive_dest_1='location=/arch/archive_log/ valid_for=(all_logfiles,all_roles) db_unique_name=ynsbsby'
*.log_archive_dest_2='service=YNSB valid_for=(online_logfiles,PRIMARY_ROLE) lgwr affirm sync db_unique_name=ynsb'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='YNSB' -- TNSNAME
*.fal_client='YNSBSBY'
-- 用新参数重启主数据库：
SQL> shutdown immediate
SQL> create spfile from pfile;
SQL> startup
SQL> alter pluggable database pdborcl open;
-- 用 spfile 将备库启动到nomount 状态
SQL> startup nomount pfile='/home/oracle/temp.ora'
SQL> create spfile='+DATA/YNSBSBY/PARAMETERFILE/spfileynsbsby.ora' from pfile='/home/oracle/temp.ora';
SQL> shutdown immediate
-- 添加注册数据库到 ASM 中
$ srvctl add database -db ynsbsby -o /u01/app/oracle/product/19.0/db_1
$ srvctl modify database -db ynsbsby -role physical_standby -spfile '+DATA/YNSBSBY/PARAMETERFILE/spfileynsbsby.ora'
-- 开始进行 Active duplicate， 执行完毕后，数据库自动进入mount状态
$ rman target sys/oracle123@YNSB auxiliary sys/oracle123@YNSBSBY;
RMAN> duplicate target database for standby from active database;
-- 主库配置
rman target /
CONFIGURE ARCHIVELOG DELETION POLICY TO applied on standby;
-- 打开备库并并启动 apply
SQL> select open_mode from v$database;
SQL> alter database open;
-- 备库是只读的
-- 查看主备库
SQL> select log_mode,open_mode ,database_role from v$database;
-- 备库启动 real-time apply：
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> select open_mode from v$database;
-- 验证DG
SQL> alter system switch logfile;
-- 主备切换
-- 正常切换：主库：
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
-- 备库：
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
-- 主切备
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL standby;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> ALTER DATABASE RECOVER MANAGED standby DATABASE DISCONNECT FROM SESSION;
-- 备切主
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO primary;
SQL> select status from v$instance;
SQL> alter database open;
-- 打开备库
-- 注：如打开新备库出现如下错误的解决方式：
SQL> alter database open;
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed stby database using current logfile disconnect ;
-- 注意：切换后验证DG
-- 主备启停
-- 先启动备库：
$ lsnrctl start
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN READ ONLY;
SQL> ALTER DATABASE RECOVER MANAGED standby DATABASE DISCONNECT FROM SESSION;
-- 查看备库状态和模式
SQL> select name,open_mode,protection_mode,database_role from v$database;
-- 再启动主库：
$ lsnrctl start
SQL> startup
-- 查看主库状态和模式：
SQL> select name,open_mode,protection_mode,database_role from v$database;
-- 在主库归档当前日志：
SQL> alter system archive log current;
-- 监控备库：
-- 查看stby库的log_archive_dest_1下应该有archive产生。
-- 查寻v$archived_log，有新的日志记录出现。最大sequence#，应该为主库v$log中current状态日志的序列号减一。
SQL>select sequence#,applied,first_time,next_time,resetlogs_change#,completion_time from v$archived_log order by sequence#;
-- 查看服务是否启动：
SQL> select process,status,client_process,sequence#,BLOCK# from v$managed_standby;
-- 查看是否有遗漏的归档日志
SQL> select * from v$archive_gap;
-- 关闭 dataguard
-- 先关闭主库：
SQL> alter system archive log current;
SQL>SHUTDOWN IMMEDIATE;
-- 再关闭备库：
SQL> ALTER DATABASE RECOVER MANAGED standby DATABASE CANCEL;
SQL>SHUTDOWN IMMEDIATE;
