DB1:
IP:192.168.6.101
ORACLE_SID:orcl1
DB2:
IP:192.168.6.102
ORACLE_SID:orcl2
---------------------------------------------------------------------------------------------------------------------------------
1、主库上检查主库数据库是否启用了强制日志模式并启用归档,如果不是,则启用强制日志模式并开启日志归档
select name, log_mode, force_logging from v$database;
-- 启用强制日志
alter database force logging;
-- 开启归档模式(需要重启实例到mount状态)
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;
2、主库上创建standby redolog日志组
-- 首先查询下当前实例日志组个数
select thread#,group#,bytes/1024/1024 from v$log;
-- standby redo log日志组数量=(每个实例日志组个数+1)*实例数
-- 当前实例日志组个数为3,新建4个standby redolog 日志组,编号从4开始
SQL> alter database add standby logfile group 4 '/data/oracle/oradata/orcl1/standby_redo04.log' size 50M;
SQL> alter database add standby logfile group 5 '/data/oracle/oradata/orcl1/standby_redo05.log' size 50M;
SQL> alter database add standby logfile group 6 '/data/oracle/oradata/orcl1/standby_redo06.log' size 50M;
SQL> alter database add standby logfile group 7 '/data/oracle/oradata/orcl1/standby_redo07.log' size 50M;
-- 查看日志组成员情况
SQL> set pagesize 100
SQL> col member for a60
SQL> select group#,member from v$logfile order by group#;
3、修改主库和备库监听文件,设置静态监听
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME= orcl1)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=orcl1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.101)(PORT = 1521))
)
)
-- 重启监听,查看监听状态
lsnrctl stop/start/reload/status
4、编辑主库和备库的网络服务名tnaname.ora文件,该文件中同时配置主库和备库的TNS描述,即主库和备库的tnsnames.ora文件是一模一样的。配置类似如下:
PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.101)(PORT = 1521))
(CONNECT_DATA =
(SID = orcl1)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.102)(PORT = 1521))
(CONNECT_DATA =
(SID = orcl2)
)
)
-- 测试到对方是否连通
主库上: tnsping STANDBY
备库上:tnsping PRIMARY
5、拷贝主库的密码文件、pfile文件到备库,同时修改备库的pfile文件内容
-- 1)在主库上操作
-- 手动创建arc目录,放到规划的/data/arc下面
mkdir /data/arc
cd $ORACLE_HOME/dbs
-- 可以看到已经有密码文件orapworcl1,可以再强制重新生成下:
orapwd file=orapworcl1 password=Olm2024 force=y
-- force=y表示强制覆盖有的密码文件,接下来将密码文件SCP拷贝至备机
scp orapworcl1 oracle@192.168.6.102:$ORACLE_HOME/dbs/orapworcl2
-- 修改主库的pfile文件
SQL> show prarameter spfile;
SQL> create pfile from spfile;
cd /$ORACLE_HOME/dbs
vim initorcl1.ora,以下部分为新增内容:
*.db_unique_name='PRIMARY'
*.log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)'
*.log_archive_dest_1='location=/data/arc valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=PRIMARY'
*.log_archive_dest_2='service=STANDBY lgwr async valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=STANDBY'
*.standby_file_management='AUTO'
*.fal_client='PRIMARY'
*.fal_server='STANDBY'
-- 启动主库
SQL> shutdown immediate;
SQL> create spfile from file;
SQL> startup;
-- 查看pfile是否生效
SQL > show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfileorcl1.ora
-- 查看归档日志路径设置生效
SQL> archive log list;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/arc
Oldest online log sequence 105
Next log sequence to archive 107
Current log sequence 107
2)在备库上操作
-- 手动创建归档目录
mkdir /data/arc
-- 修改备库的pfile文件
SQL> show prarameter spfile;
SQL> create pfile from spfile;
vim initorcl2.ora,以下部分为新增内容:
*.db_unique_name='STANDBY'
*.log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)'
*.log_archive_dest_1='location=/data/arc valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=STANDBY'
*.log_archive_dest_2='service=PRIMARY lgwr async valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=RRIMARY'
*.standby_file_management='AUTO'
*.fal_client='STANDBY'
*.fal_server='PRIMARY'
*.db_file_name_convert='/data/orcl1','/data/orcl2'
*.log_file_name_convert='/data/orcl1','/data/orcl2'
注意:由于主库和备库的db_name不一致,需要修改成主库的db_name,即:*.db_name='orcl1'
-- 启动备库到nomount
SQL > create spfile from file;
SQL > startup no mount;
6、利用RMAN在备库上恢复主库
-- 创建连接
rman target sys/Olm2024@primary auxiliary sys/Olm2024@standby
-- 执行恢复,这个命令可以直接恢复数据文件、standby日志文件和控制文件。
duplicate target database for standby from active database nofilenamecheck;
7、手动切换日志文件,看主备库的归档日志序号是否一致
SQL> alter system switch log file;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/arc
Oldest online log sequence 131
Next log sequence to archive 133
Current log sequence 133
-- 备库上执行
DDatabase log mode Archive Mode
Automatic archival Enabled
Archive destination /data/arc
Oldest online log sequence 131
Next log sequence to archive 0
Current log sequence 133
可见主库和备库的归档序列号是一致的。
8、后续操作:Open Read Only standby数据库并且开启实时日志应用
-- 备库恢复完成后,数据库的状态是mounted,并且此时DG的保护模式是最高性能,查询备库:
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
-- 停止备库
shutdown immediate
-- 启动备库
startup;
-- 一般的可以将备库设置成只读模式以便启用备机实时查询:
SQL> select process,client_process,sequence#,status from v$managed_standby;
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
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 131 CLOSING
ARCH ARCH 132 CLOSING
RFS ARCH 0 IDLE
RFS UNKNOWN 0 IDLE
RFS LGWR 133 IDLE
MRP0 N/A 133 APPLYING_LOG
7 rows selected.
-- 测试: 创建一个表,并插入数据
SQL> CREATE TABLE test AS SELECT * FROM dba_users;
Table created.
SQL> select count(*) from test;
COUNT(*)
----------
29
-- 在备库查看是否存在表test
SQL> select count(*) from test;
COUNT(*)
----------
29
-------------------至此,ADG搭建完成-----------------------------------
-- 关闭ADG
主库shutdown——>备库取消应用归档日志——>关闭备库——>关闭主库和备库的lsnrctl监听。
1:主库上:SQL> shutdown immediate
2:备库上:SQL> alter database recover managed standby database cancel;
3:备库上:SQL> shutdown immediate
4:主库和备库:
[oracle@PRIMARY ~]$ lsnrctl stop
[oracle@STANDBY ~]$ lsnrctl stop
-- 打开ADG
启动主库和备库lsnrctl监听——>启动备库——>启动主库——>切换主库日志
1:主库和备库:
[oracle@PRIMARY ~]$ lsnrctl start
[oracle@STANDBY ~]$ lsnrctl start
2:启动备库:
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database open read write;
SQL> alter database recover managed standby database using current logfile disconnect
3:启动主库:
SQL> startup
4:切换主库日志
SQL> alter system switch logfile;
备库将开始应用主库传输过来的归档日志。
-- 相关操作
-- 查看保护模式
select database_role,protection_mode,protection_level,open_mode from v$database;
-- 查看当前进程
select process,client_process,sequence#,status from v$managed_standby;
-- 开启实时日志应用
SQL> recover managed standby database using current logfile disconnect from session;