目录
资源配置
| 主备角色 | 主机名 | IP | 实例名 |
| 主库 | racstd1 | 192.168.213.109 | std11 |
| racstd2 | 192.168.213.110 | std12 | |
| 备库 | primary1 | 192.168.213.101 | stdg11 |
| primary2 | 192.168.213.102 | stdg12 |
一、主库集群操作
1.1.主库增加standbylog
SQL> select group#,thread#,sequence#,bytes/1024/1024,members,status from v$log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS
---------- ---------- ---------- --------------- ---------- ----------------
1 1 27 50 2 CURRENT
2 1 26 50 2 INACTIVE
3 2 9 50 2 CURRENT
4 2 8 50 2 INACTIVE
增加日志
alter database add standby logfile thread 1 group 5 ('+DATA') size 50M,group 6 ('+DATA') size 50M,group 7 ('+DATA') size 50M;
alter database add standby logfile thread 2 group 8 ('+DATA') size 50M,group 9 ('+DATA') size 50M,group 10 ('+DATA') size 50M;
select group#,member from v$logfile;
注意:standbylog 要比原日志组至少多一组，日志大小建议与当前数据库配置保持一致。
1.2.主库开启force logging及归档
开启强记日志
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 26
Current log sequence 27
SQL>
##开归档
[oracle@racstd1 ~]$ srvctl stop database -d standby
[oracle@racstd1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 6 15:01:19 2024
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 7532969984 bytes
Fixed Size 2941920 bytes
Variable Size 1426066464 bytes
Database Buffers 6090129408 bytes
Redo Buffers 13832192 bytes
Database mounted.
##归档开启（如归档已开启请忽略这一步）
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 26
Next log sequence to archive 27
Current log sequence 27
SQL>
1.3.主库配置参数
alter system set log_archive_config='dg_config=(standby,primary)' scope=spfile sid='*';
alter system set log_archive_dest_1='location=+ARCH valid_for=(all_logfiles,all_roles) db_unique_name=standby' scope=spfile sid='*';
alter system set log_archive_dest_2='service=primary valid_for=(online_logfiles,primary_role) db_unique_name=primary' scope=spfile sid='*';
alter system set standby_file_management='auto' scope=spfile sid='*';
alter system set fal_server='primary' scope=spfile sid='*';
alter system set fal_client='standby' scope=spfile sid='*';
alter system set db_file_name_convert='+DATA/PRIMARY/DATAFILE/','+DATA/STANDBY/DATAFILE/','+DATA/PRIMARY/DATAFILE/','+DATA/STANDBY/TEMPFILE/' scope=spfile sid='*';
alter system set log_file_name_convert='+DATA/PRIMARY/ONLINELOG','+DATA/STANDBY/ONLINELOG' scope=spfile sid='*';
##重启生效
[oracle@racstd1 tmp]$ srvctl stop database -d standby
[oracle@racstd1 tmp]$ srvctl start database -d standby
[oracle@racstd1 tmp]$ srvctl status database -d standby
Instance std11 is running on node racstd1
Instance std12 is running on node racstd2
1.4.生成参数文件并将参数文件、密码文件拷贝至备库
1.4.1参数文件处理
[oracle@racstd1 ~]$ sqlplus / as sysdba
SQL> create pfile='/tmp/initstd.ora' from spfile;
File created.
##将参数文件传到备库
[oracle@racstd1 tmp]$ scp initstd.ora oracle@192.168.213.101:/u01/app/oracle/product/12.1.0/dbhome_1/dbs
The authenticity of host '192.168.213.101 (192.168.213.101)' can't be established.
RSA key fingerprint is de:d5:a9:a1:b1:84:63:49:84:02:4b:d2:ff:30:e5:de.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.213.101' (RSA) to the list of known hosts.
oracle@192.168.213.101's password:
initstd.ora 100% 1500 1.5KB/s 00:00
1.4.2密码文件处理
主库密码文件到备库，最开始放在$ORACLE_HOME/dbs目录下，后续配置完成后，需要将密码文件存放在ASM磁盘组中并进行改名。
[root@racstd1 ~]# su - grid
[grid@racstd1 ~]$ asmcmd
ASMCMD> pwd
+DATA/STANDBY/PASSWORD
ASMCMD> ls
pwdstandby.256.1170868583
ASMCMD> pwcopy +DATA/STANDBY/PASSWORD/pwdstandby.256.1170868583 /tmp
注意:需要拷贝到tmp目录其他目录会报权限错误
[root@racstd1 tmp]# scp pwdstandby.256.1170868583 192.168.213.101:/tmp
The authenticity of host '192.168.213.101 (192.168.213.101)' can't be established.
RSA key fingerprint is de:d5:a9:a1:b1:84:63:49:84:02:4b:d2:ff:30:e5:de.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.213.101' (RSA) to the list of known hosts.
root@192.168.213.101's password:
pwdstandby.256.1170868583 100% 7680 7.5KB/s 00:00
二、备库操作
2.1.备库修改参数文件
cd $ORACLE_HOME/dbs
vi initstd.ora
先把涉及到实例名的参数改成一致主库是std*备库我们改成stdg*跟主库区别开
2.1.1创建adump目录并在参数文件修改（两节点均创建）
cd /u01/app/oracle/admin/
mkdir -p stdg/adump
参数文件中修改
2.1.2增加dg相关参数
##确认主库的数据文件、临时文件、日志文件目录
ASMCMD> cd DATAFILE
ASMCMD> pwd
+DATA/STANDBY/DATAFILE
ASMCMD> cd ..
ASMCMD> cd TEMPFILE/
ASMCMD> pwd
+DATA/STANDBY/TEMPFILE
ASMCMD> cd ..
ASMCMD> cd ONLINELOG/
ASMCMD> pwd
+DATA/STANDBY/ONLINELOG
*.db_file_name_convert='+DATA/STANDBY/DATAFILE/','+DATA/PRIMARY/DATAFILE/','+DATA/STANDBY/TEMPFILE/','+DATA/PRIMARY/TEMPFILE/'
*.db_unique_name='primary'
*.log_archive_config='DG_CONFIG=(standby,primary)'
*.log_archive_dest_1='LOCATION=+ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
*.log_archive_dest_2='SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.log_file_name_convert='+DATA/STANDBY/ONLINELOG','+DATA/PRIMARY/ONLINELOG'
*.fal_server='standby'
*.fal_client='primary'
参数文件：
stdg11.__data_transfer_cache_size=0
stdg12.__data_transfer_cache_size=0
stdg11.__db_cache_size=5821693952
stdg12.__db_cache_size=5821693952
stdg11.__java_pool_size=16777216
stdg12.__java_pool_size=16777216
stdg11.__large_pool_size=33554432
stdg12.__large_pool_size=33554432
stdg11.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
stdg12.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
stdg11.__pga_aggregate_target=2516582400
stdg12.__pga_aggregate_target=2516582400
stdg11.__sga_target=7532969984
stdg12.__sga_target=7532969984
stdg11.__shared_io_pool_size=385875968
stdg12.__shared_io_pool_size=385875968
stdg11.__shared_pool_size=1258291200
stdg12.__shared_pool_size=1258291200
stdg11.__streams_pool_size=0
stdg12.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/stdg/adump'
*.audit_trail='db'
#*.cluster_database=TRUE
*.compatible='12.1.0.2.0'
*.control_files='+DATA/PRIMARY/CONTROLFILE/controlfile01.ctl','+ARCH/PRIMARY/CONTROLFILE/controlfile02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='standby' --注意数据库名要与主库一致否则会报错
*.db_recovery_file_dest='+ARCH'
*.db_recovery_file_dest_size=4785m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stdg1XDB)'
stdg11.instance_number=1
stdg12.instance_number=2
*.open_cursors=300
*.pga_aggregate_target=2392m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=7176m
stdg12.thread=2
stdg11.thread=1
stdg11.undo_tablespace='UNDOTBS1'
stdg12.undo_tablespace='UNDOTBS2'
*.db_file_name_convert='+DATA/STANDBY/DATAFILE/','+DATA/PRIMARY/DATAFILE/','+DATA/STANDBY/TEMPFILE/','+DATA/PRIMARY/TEMPFILE/'
*.db_unique_name='primary'
*.log_archive_config='DG_CONFIG=(standby,primary)'
*.log_archive_dest_1='LOCATION=+ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
*.log_archive_dest_2='SERVICE=BMACDB1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.log_file_name_convert='+DATA/STANDBY/ONLINELOG','+DATA/PRIMARY/ONLINELOG'
*.fal_server='standby'
*.fal_client='primary'
注意:集群参数暂时屏蔽
2.1.3备库asm创建目录结构
ASMCMD> mkdir PRIMARY
ASMCMD> cd PRIMARY
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir TEMPFILE
ASMCMD> mkdir DATAFILE
ASMCMD> mkdir CONTROLFILE
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
ASMCMD> pwd
+DATA/PRIMARY
2.1.4备库将主库asm传过来的密码文件放到dbs目录下
cd $ORACLE_HOME/dbs
cp /tmp/pwdstandby.256.1170868583 orapwstdg11
三、配置监听
##主备库都改
cd /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/
vi tnsnames.ora
standby=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.213.109)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.213.110)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =standby)
)
)
primary=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.213.101)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.213.102)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =primary)
)
)
测试
四、恢复库
4.1主库进行全库备份
[root@racstd1 ~]# mkdir -p /backup
[root@racstd1 ~]# chown -R oracle:oinstall /backup/
[root@racstd1 ~]#
su - oracle
[oracle@racstd1 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Jun 7 09:21:11 2024
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: STANDBY (DBID=1982604216)
RMAN>
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/backup/data_%d_%T_%s_%p' tag 'data';
sql 'alter system archive log current';
backup archivelog all format='/backup/arch_%d_%T_%s_%p' tag 'arc';
backup current controlfile for standby format='/backup/ctl_%d_%T_%s_%p' tag 'cur';
release channel c1;
release channel c2;
}
4.2将主库备份传至备库
[oracle@racstd1 ~]$ cd /backup/
[oracle@racstd1 backup]$ ls
arch_STANDBY_20240607_5_1 arch_STANDBY_20240607_7_1 data_STANDBY_20240607_1_1 data_STANDBY_20240607_3_1
arch_STANDBY_20240607_6_1 ctl_STANDBY_20240607_8_1 data_STANDBY_20240607_2_1 data_STANDBY_20240607_4_1
[oracle@racstd1 backup]$ du -s *
55272 arch_STANDBY_20240607_5_1
47648 arch_STANDBY_20240607_6_1
12 arch_STANDBY_20240607_7_1
18848 ctl_STANDBY_20240607_8_1
877900 data_STANDBY_20240607_1_1
702140 data_STANDBY_20240607_2_1
18848 data_STANDBY_20240607_3_1
96 data_STANDBY_20240607_4_1
##目标端创建目录
[root@primary1 ~]# mkdir -p /backup
[root@primary1 ~]# chown -R oracle:oinstall /backup/
##传送备份到备库
[oracle@racstd1 backup]$ scp * oracle@192.168.213.101:/backup/
4.3备库恢复主库备份
[root@primary1 ~]# cd /backup/
[root@primary1 backup]# ls
arch_STANDBY_20240607_5_1 arch_STANDBY_20240607_7_1 data_STANDBY_20240607_1_1 data_STANDBY_20240607_3_1
arch_STANDBY_20240607_6_1 ctl_STANDBY_20240607_8_1 data_STANDBY_20240607_2_1 data_STANDBY_20240607_4_1
[root@primary1 ~]# su - oracle
[oracle@primary1 ~]$ export ORACLE_SID=stdg11
[oracle@primary1 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Fri Jun 7 09:38:49 2024
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 7532969984 bytes
Fixed Size 2941920 bytes
Variable Size 1358957600 bytes
Database Buffers 6157238272 bytes
Redo Buffers 13832192 bytes
RMAN>
restore standby controlfile from '/backup/ctl_STANDBY_20240607_8_1';
alter database mount;
catalog start with "/backup";
Run{
allocate channel c1 type disk;
allocate channel c2 type disk;
restore database;
switch datafile all;
switch tempfile all;
recover database;
release channel c1;
release channel c2;
}
开库
[oracle@primary1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jun 7 10:09:52 2024
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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
STANDBY MOUNTED
SQL> alter database open;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
STANDBY READ ONLY
1.1创建spfile标准化
SQL> create pfile='/tmp/initstdg11.ora' from spfile;
File created.
SQL> create spfile='+DATA/PRIMARY/PARAMETERFILE/spfilestdg.ora' from pfile='/tmp/initstdg11.ora';
File created.
##asmcmd查看
[oracle@primary1 dbs]$ rm -f spfilestdg11.ora
[oracle@primary1 dbs]$ rm -f /tmp/initstdg11.ora
1.1.1修改一二节点pfile内容
[oracle@primary1 dbs]$ vi initstdg11.ora
spfile='+DATA/PRIMARY/PARAMETERFILE/spfilestdg.ora
[oracle@primary1 dbs]$ scp initstdg11.ora oracle@primary2:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initstdg12.ora
initstdg11.ora 100% 52 0.1KB/s 00:00
[root@primary2 ~]# su - oracle
[oracle@primary2 ~]$ cd $ORACLE_HOME/dbs
[oracle@primary2 dbs]$ ls
hc_racpm2.dat id_racpm2.dat init.ora initstdg12.ora
[oracle@primary2 dbs]$ cat initstdg12.ora
spfile='+DATA/PRIMARY/PARAMETERFILE/spfilestdg.ora'
1.2拷贝密码文件到asm
[root@primary1 tmp]# chmod 777 pwdstandby.256.1170868583
[root@primary1 tmp]# ls -ld pwdstandby.256.1170868583
-rwxrwxrwx 1 grid oinstall 7680 Jun 6 15:30 pwdstandby.256.1170868583
[root@primary1 tmp]#
ASMCMD> cp /tmp/pwdstandby.256.1170868583 orapwdprimary
copying /tmp/pwdstandby.256.1170868583 -> +data/primary/password/orapwdprimary
[root@primary1 tmp]# su - oracle
[oracle@primary1 ~]$ cd $ORACLE_HOME/dbs
[oracle@primary1 dbs]$ ls
hc_racpm1.dat hc_stdg11.dat id_racpm1.dat init.ora initstdg11.ora initstd.ora orapwstdg11 snapcf_stdg11.f
[oracle@primary1 dbs]$ rm -f orapwstdg11
五、注册服务
5.1环境变量配置
[oracle@primary1 ~]$ vi .bash_profile
export ORACLE_SID=stdg11
[oracle@primary1 ~]$ . .bash_profile
5.2改参数
[oracle@primary1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jun 7 10:41:01 2024
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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 7532969984 bytes
Fixed Size 2941920 bytes
Variable Size 1358957600 bytes
Database Buffers 6157238272 bytes
Redo Buffers 13832192 bytes
Database mounted.
SQL> show parameter cluster;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string
alter system set cluster_database=true scope=spfile;
alter system set cluster_database_instances=2 scope=spfile;
SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
5.3增加服务
srvctl add database -db primary -dbname standby -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 -dbtype RAC -role PHYSICAL_STANDBY
srvctl add instance -db primary -instance stdg11 -node primary1
srvctl add instance -db primary -instance stdg12 -node primary2
srvctl modify database -db primary -spfile '+DATA/PRIMARY/PARAMETERFILE/spfilestdg.ora' -pwfile '+DATA/PRIMARY/PASSWORD/orapwdprimary'
srvctl modify database -db primary -diskgroup ARCH,DATA
srvctl config database -d primary
srvctl start database -db primary -startoption open
六、备库应用日志
6.1查看主备库状态
select DBID,NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATAGUARD_BROKER,DB_UNIQUE_NAME from v$database;
select INST_ID,INSTANCE_NUMBER,INSTANCE_NAME,STATUS,DATABASE_STATUS,INSTANCE_ROLE from gv$instance;
主库是读写
开启flashback
SQL> select open_mode,flashback_on from v$database;
OPEN_MODE FLASHBACK_ON
-------------------- ------------------
READ ONLY NO
SQL> alter database flashback on;
Database altered.
SQL> select open_mode,flashback_on from v$database;
OPEN_MODE FLASHBACK_ON
-------------------- ------------------
READ ONLY YES
SQL> select process,status,sequence#,THREAD# from v$managed_standby where PROCESS='MRP0';
no rows selected
6.2开启日志应用
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select process,status,sequence#,THREAD# from v$managed_standby where PROCESS='MRP0';
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
MRP0 WAIT_FOR_LOG 28 2
SQL> select process,status,sequence#,THREAD# from v$managed_standby where PROCESS='MRP0';
##最大性能模式半同步状态正常
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
MRP0 WAIT_FOR_LOG 28 2
6.2验证同步状态
主库切日志
SQL> alter system switch logfile;
System altered.
备库观察日志
备库可以马上应用到日志说明同步无问题
主库查看sequence
SQL> select sequence#,applied from v$archived_log order by sequence# asc;
备库查看sequence
主备库sequence一致 同步正常