centos 7.5搭建oracle DG
一.背景
1.IP分配 主库：192.168.12.5 node1 备库：192.168.12.6 node2 2.环境 主库已安装数据库软件，已建库，并有业务数据 备库已安装数据库软件，未建库
二、配置主备库网络
1.主库监听：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.5)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
2.主库网络服务：
ORADB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.5)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb)
)
)
ORADBSTD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.6)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradbstd)
)
)
3.备库监听：
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.12.6)(PORT=1521))
)
)
4.备库网络服务：
ORADB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.5)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb)
)
)
ORADBSTD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.6)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradbstd)
)
)
5.测试主备网络：
主库：
[oracle@node1 admin]$ tnsping oradb
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 25-JUL-2020 00:08:29
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.5)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradb)))
OK (0 msec)
[oracle@node1 admin]$ tnsping oradbstd
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 25-JUL-2020 00:08:31
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.6)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbstd)))
OK (0 msec)
备库：
[oracle@node2 admin]$ tnsping oradb
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 25-JUL-2020 00:12:22
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.5)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradb)))
OK (0 msec)
[oracle@node2 admin]$ tnsping oradbstd
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 25-JUL-2020 00:12:28
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.6)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbstd)))
OK (0 msec)
全部都为OK，主备网络配置完成。
三、主库开启归档模式，开启force logging
SQL> alter database archivelog; Database altered. SQL> alter database force logging; Database altered. SQL> alter database open; Database altered.
四、主库生成文本参数文件，并修改参数
oradb.__db_cache_size=318767104 oradb.__java_pool_size=4194304 oradb.__large_pool_size=8388608 oradb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment oradb.__pga_aggregate_target=306184192 oradb.__sga_target=457179136 oradb.__shared_io_pool_size=0 oradb.__shared_pool_size=117440512 oradb.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/oradb/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/oradb/control01.ctl','/u01/app/oracle/fast_recovery_area/oradb/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='oradb' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4385144832 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=oradbXDB)' *.memory_target=763363328 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' DB_UNIQUE_NAME=oradb LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradb,oradbstd)' LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb' LOG_ARCHIVE_DEST_2= 'SERVICE=oradbstd ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradbstd' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE FAL_SERVER=oradbstd DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/oradbstd','/u01/app/oracle/oradata/oradb' LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/oradbstd','/u01/app/oracle/oradata/oradb' STANDBY_FILE_MANAGEMENT=AUTO
五、主库使用pfile启动，并创建新的spfile
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initoradb.ora' ORACLE instance started. Total System Global Area 759943168 bytes Fixed Size 2257112 bytes Variable Size 499126056 bytes Database Buffers 255852544 bytes Redo Buffers 2707456 bytes Database mounted. Database opened. SQL> create spfile from pfile; File created.
六、主库备份
RMAN> run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup database format '/u01/app/oracle/rmanbak/full_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '/u01/app/oracle/rmanbak/arch_%d_%T_%s_%p';
backup current controlfile for standby format '/u01/app/oracle/rmanbak/ctl_bf';
release channel c1;
release channel c2;
release channel c3;
}
七、备库创建相关目录
[oracle@node2 ~]$ mkdir -p /u01/app/oracle/admin/oradbstd/{a,dp}dump
[oracle@node2 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/oradbstd
[oracle@node2 ~]$ mkdir -p /u01/app/oracle/oradata/oradbstd
[oracle@node2 ~]$ mkdir -p /u01/app/oracle/archivelog
[oracle@node2 ~]$ mkdir -p /u01/app/oracle/rmanbak
八、主库将参数文件、密码文件、备份文件传送到备库
[oracle@node1 dbs]$ scp orapworadb 192.168.12.6:/u01/app/oracle/product/11.2.0/db_1/dbs/orapworadbstd oracle@192.168.12.6's password: orapworadb 100% 1536 1.0MB/s 00:00 [oracle@node1 dbs]$ scp initoradb.ora 192.168.12.6:/u01/app/oracle/product/11.2.0/db_1/dbs/initoradbstd.ora oracle@192.168.12.6's password: initoradb.ora 100% 1491 802.1KB/s 00:00 [oracle@node1 rmanbak]$ scp * 192.168.12.6:/u01/app/oracle/rmanbak/ oracle@192.168.12.6's password: arch_ORADB_20200728_40_1 100% 15MB 63.6MB/s 00:00 arch_ORADB_20200728_41_1 100% 8864KB 71.9MB/s 00:00 arch_ORADB_20200728_42_1 100% 3678KB 72.3MB/s 00:00 arch_ORADB_20200728_43_1 100% 87KB 31.3MB/s 00:00 ctl_bf 100% 9568KB 72.5MB/s 00:00 full_ORADB_20200728_35_1 100% 642MB 67.9MB/s 00:09 full_ORADB_20200728_36_1 100% 392MB 59.1MB/s 00:06 full_ORADB_20200728_37_1 100% 12MB 38.5MB/s 00:00 full_ORADB_20200728_38_1 100% 9568KB 57.0MB/s 00:00 full_ORADB_20200728_39_1 100% 96KB 30.8MB/s 00:00
九、修改备库参数文件
oradb.__db_cache_size=318767104 oradb.__java_pool_size=4194304 oradb.__large_pool_size=8388608 oradb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment oradb.__pga_aggregate_target=306184192 oradb.__sga_target=457179136 oradb.__shared_io_pool_size=0 oradb.__shared_pool_size=117440512 oradb.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/oradbstd/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/oradbstd/control01.ctl','/u01/app/oracle/fast_recovery_area/oradbstd/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='oradb' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4385144832 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=oradbstdXDB)' *.memory_target=763363328 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' DB_UNIQUE_NAME=oradbstd LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradbstd,oradb)' LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradbstd' LOG_ARCHIVE_DEST_2= 'SERVICE=oradb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE FAL_SERVER=oradb DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/oradb','/u01/app/oracle/oradata/oradbstd' LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/oradb','/u01/app/oracle/oradata/oradbstd' STANDBY_FILE_MANAGEMENT=AUTO
十、备库使用文本参数文件启动数据库到nomount状态
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initoradbstd.ora'; ORACLE instance started. Total System Global Area 759943168 bytes Fixed Size 2257112 bytes Variable Size 499126056 bytes Database Buffers 255852544 bytes Redo Buffers 2707456 bytes
十一、从备份文件中恢复控制文件
RMAN> restore standby controlfile from '/u01/app/oracle/rmanbak/ctl_bf'; Starting restore at 27-JUL-20 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/oradbstd/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/oradbstd/control02.ctl Finished restore at 27-JUL-20
十二、启动数据库到mount状态，并将备份文件注册到rman
RMAN> sql 'alter database mount'; sql statement: alter database mount released channel: ORA_DISK_1 RMAN> catalog start with '/u01/app/oracle/rmanbak'; Starting implicit crosscheck backup at 27-JUL-20 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK Crosschecked 8 objects Finished implicit crosscheck backup at 27-JUL-20 Starting implicit crosscheck copy at 27-JUL-20 using channel ORA_DISK_1 Finished implicit crosscheck copy at 27-JUL-20 searching for all files in the recovery area cataloging files... no files cataloged searching for all files that match the pattern /u01/app/oracle/rmanbak List of Files Unknown to the Database ===================================== File Name: /u01/app/oracle/rmanbak/ctl_bf Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u01/app/oracle/rmanbak/ctl_bf
十三、使用备份恢复数据库
RMAN> run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
2> 3> 4> 5> 6> set newname for datafile 1 to '/u01/app/oracle/oradata/oradbstd/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/oradbstd/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/oradbstd/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/oradbstd/users01.dbf';
set newname for datafile 5 to '/u01/app/oracle/oradata/oradbstd/gen_cfg01.dbf';
set newname for datafile 6 to '/u01/app/oracle/oradata/oradbstd/gen_ers01.dbf';
set newname for datafile 7 to '/u01/app/oracle/oradata/oradbstd/gen_etl01.dbf';
set newname for datafile 8 to '/u01/app/oracle/oradata/oradbstd/gen_ods01.dbf';
set newname for datafile 9 to '/u01/app/oracle/oradata/oradbstd/gen_log01.dbf';
set newname for datafile 10 to '/u01/app/oracle/oradata/oradbstd/gen_rps01.dbf';
set newname for datafile 11 to '/u01/app/oracle/oradata/oradbstd/gen_ctiadaptor01.dbf';
set newname for datafile 12 to '/u01/app/oracle/oradata/oradbstd/gen_cdr.dbf';
restore database;
switch datafile all;
release channel c1;
release channel c2;
release channel c3;
}7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=1 device type=DISK
allocated channel: c2
channel c2: SID=29 device type=DISK
allocated channel: c3
channel c3: SID=30 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 28-JUL-20
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00005 to /u01/app/oracle/oradata/oradbstd/gen_cfg01.dbf
channel c1: restoring datafile 00006 to /u01/app/oracle/oradata/oradbstd/gen_ers01.dbf
channel c1: restoring datafile 00007 to /u01/app/oracle/oradata/oradbstd/gen_etl01.dbf
channel c1: restoring datafile 00008 to /u01/app/oracle/oradata/oradbstd/gen_ods01.dbf
channel c1: reading from backup piece /u01/app/oracle/rmanbak/full_ORADB_20200728_37_1
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00002 to /u01/app/oracle/oradata/oradbstd/sysaux01.dbf
channel c2: restoring datafile 00003 to /u01/app/oracle/oradata/oradbstd/undotbs01.dbf
channel c2: restoring datafile 00004 to /u01/app/oracle/oradata/oradbstd/users01.dbf
channel c2: restoring datafile 00009 to /u01/app/oracle/oradata/oradbstd/gen_log01.dbf
channel c2: reading from backup piece /u01/app/oracle/rmanbak/full_ORADB_20200728_36_1
channel c3: starting datafile backup set restore
channel c3: specifying datafile(s) to restore from backup set
channel c3: restoring datafile 00001 to /u01/app/oracle/oradata/oradbstd/system01.dbf
channel c3: restoring datafile 00010 to /u01/app/oracle/oradata/oradbstd/gen_rps01.dbf
channel c3: restoring datafile 00011 to /u01/app/oracle/oradata/oradbstd/gen_ctiadaptor01.dbf
channel c3: restoring datafile 00012 to /u01/app/oracle/oradata/oradbstd/gen_cdr.dbf
channel c3: reading from backup piece /u01/app/oracle/rmanbak/full_ORADB_20200728_35_1
channel c1: piece handle=/u01/app/oracle/rmanbak/full_ORADB_20200728_37_1 tag=TAG20200728T112945
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:03
channel c2: piece handle=/u01/app/oracle/rmanbak/full_ORADB_20200728_36_1 tag=TAG20200728T112945
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:07
channel c3: piece handle=/u01/app/oracle/rmanbak/full_ORADB_20200728_35_1 tag=TAG20200728T112945
channel c3: restored backup piece 1
channel c3: restore complete, elapsed time: 00:00:07
Finished restore at 28-JUL-20
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=1046980245 file name=/u01/app/oracle/oradata/oradbstd/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=1046980245 file name=/u01/app/oracle/oradata/oradbstd/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=1046980245 file name=/u01/app/oracle/oradata/oradbstd/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=1046980245 file name=/u01/app/oracle/oradata/oradbstd/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=1046980245 file name=/u01/app/oracle/oradata/oradbstd/gen_cfg01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=1046980245 file name=/u01/app/oracle/oradata/oradbstd/gen_ers01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=1046980245 file name=/u01/app/oracle/oradata/oradbstd/gen_etl01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=9 STAMP=1046980245 file name=/u01/app/oracle/oradata/oradbstd/gen_ods01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=1046980245 file name=/u01/app/oracle/oradata/oradbstd/gen_log01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=11 STAMP=1046980245 file name=/u01/app/oracle/oradata/oradbstd/gen_rps01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=12 STAMP=1046980245 file name=/u01/app/oracle/oradata/oradbstd/gen_ctiadaptor01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=13 STAMP=1046980245 file name=/u01/app/oracle/oradata/oradbstd/gen_cdr.dbf
released channel: c1
released channel: c2
released channel: c3
十四、应用备份的归档日志恢复数据库
RMAN> recover database; Starting recover at 28-JUL-20 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1 device type=DISK starting media recovery archived log for thread 1 with sequence 29 is already on disk as file /u01/app/oracle/archivelog/1_29_1044690845.dbf channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=25 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=26 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=27 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=28 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rmanbak/arch_ORADB_20200728_43_1 channel ORA_DISK_1: piece handle=/u01/app/oracle/rmanbak/arch_ORADB_20200728_43_1 tag=TAG20200728T112952 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 archived log file name=/u01/app/oracle/archivelog/1_25_1044690845.dbf thread=1 sequence=25 archived log file name=/u01/app/oracle/archivelog/1_26_1044690845.dbf thread=1 sequence=26 archived log file name=/u01/app/oracle/archivelog/1_27_1044690845.dbf thread=1 sequence=27 archived log file name=/u01/app/oracle/archivelog/1_28_1044690845.dbf thread=1 sequence=28 archived log file name=/u01/app/oracle/archivelog/1_29_1044690845.dbf thread=1 sequence=29 unable to find archived log archived log thread=1 sequence=30 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 07/28/2020 19:54:12 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 30 and starting SCN of 1046847
十五、启动备库
RMAN> sql 'alter database open'; sql statement: alter database open
十六、主备库添加standby log
主库： SQL> alter database add standby logfile '/u01/app/oracle/oradata/oradb/stbylog01.log' size 50M; Database altered. SQL> alter database add standby logfile '/u01/app/oracle/oradata/oradb/stbylog02.log' size 50M; Database altered. SQL> alter database add standby logfile '/u01/app/oracle/oradata/oradb/stbylog03.log' size 50M; Database altered. SQL> alter database add standby logfile '/u01/app/oracle/oradata/oradb/stbylog04.log' size 50M; Database altered. 备库： SQL> alter database add standby logfile '/u01/app/oracle/oradata/oradbstd/stbylog01.log' size 50M; Database altered. SQL>alter database add standby logfile '/u01/app/oracle/oradata/oradbstd/stbylog02.log' size 50M; Database altered. SQL>alter database add standby logfile '/u01/app/oracle/oradata/oradbstd/stbylog03.log' size 50M; Database altered. SQL>alter database add standby logfile '/u01/app/oracle/oradata/oradbstd/stbylog04.log' size 50M; Database altered.
十七、备库应用日志
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.
