ORACLE单实例搭建ADG
环境：
Primary:
DB Name : etldb
DB Version : Oracle 11g
IP: 192.168.1.1
Standby:
DB Version : Oracle 11g
DB Name : etldb
DB_UNIQUE_NAME :etldbdr
IP: 192.168.1.2
1.Primary库开启归档模式：
SQL> select name, open_mode,cdb from v$database;
NAME OPEN_MODE
--------- --------------------
etldb READ WRITE
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>
- 检查Primary库密码文件
[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@rac1 dbs]$ ls -ltr orapwUOIN1CON
-rw-r-----. 1 oracle dba 3584 Dec 14 12:26 orapwetldb
[oracle@rac1 dbs]$
3.配置Primary的Standby Redo Log
SQL> set lines 180
SQL> col MEMBER for a60
SQL> select b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;
THREAD# GROUP# MEMBER BYTES
---------- ---------- ------------------------------------------------------------ ----------
1 3 /oradata/etldb/redo03.log 209715200
1 2 /oradata/etldb/redo02.log 209715200
1 1 /oradata/etldb/redo01.log 209715200
SQL>
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/oradata/etldb/redo04.log') SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/oradata/etldb/redo05.log') SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/oradata/etldb/redo06.log') SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/oradata/etldb/redo07.log') SIZE 200M;
Database altered.
SQL>
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ------------------------------------------------------------ --- ----------
3 ONLINE /oradata/etldb/redo03.log NO 0
2 ONLINE /oradata/etldb/redo02.log NO 0
1 ONLINE /oradata/etldb/redo01.log NO 0
4 STANDBY /oradata/etldb/redo04.log NO 0
5 STANDBY /oradata/etldb/redo05.log NO 0
6 STANDBY /oradata/etldb/redo06.log NO 0
7 STANDBY /oradata/etldb/redo07.log NO 0
7 rows selected.
SQL>
SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;
GROUP# MEMBER BYTES
---------- ----------------------------------------- -------------
4 /oradata/etldb/redo04.log 209715200
5 /oradata/etldb/redo05.log 209715200
6 /oradata/etldb/redo06.log 209715200
7 /oradata/etldb/redo07.log 209715200
SQL>
3.检查Primary库的归档模式
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/etldb/
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL>
4.设置Primary库初始化Parameters参数
SQL> alter system set db_unique_name='etldb' scope=spfile;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(etldb,etldbdr)' scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/oradata/etldb VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=etldb' scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=etldbdr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=etldbdr' scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 scope=both;
System altered.
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET fal_client=etldbdr scope=both;
System altered.
SQL> ALTER SYSTEM SET fal_server=etldb scope=both;
System altered.
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/oradata/etldbdr','/oradata/etldb' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/oradata/etldbdr','/oradata/etldb' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
SQL>
SQL> create pfile='/home/oracle/initetldb.ora' from spfile;
File created.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0 - 64bit Production
[oracle@rac1 ~]$ cat /home/oracle/initetldb.ora
etldb.__data_transfer_cache_size=0
etldb.__db_cache_size=369098752
etldb.__inmemory_ext_roarea=0
etldb.__inmemory_ext_rwarea=0
etldb.__java_pool_size=16777216
etldb.__large_pool_size=33554432
etldb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
etldb.__pga_aggregate_target=587202560
etldb.__sga_target=687865856
etldb.__shared_io_pool_size=33554432
etldb.__shared_pool_size=218103808
etldb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/etldb/adump'
*.audit_trail='db'
*.compatible='11.2.0'
*.control_files='/oradata/etldb/control01.ctl','/oradata/ETLDB/controlfile/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/oradata/etldbdr','/oradata/etldb'
*.db_name='etldb'
*.db_unique_name='etldb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=etldbXDB)'
*.fal_client='etldb'
*.fal_server='etldbdr'
*.log_archive_config='DG_CONFIG=(etldb,etldbdr)'
*.log_archive_dest_1='LOCATION=/oradata/etldb VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=etldb'
*.log_archive_dest_2='SERVICE=etldbdr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=etldbdr'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.log_file_name_convert='/oradata/etldbdr','/oradata/etldb'
*.memory_target=1201m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@rac1 ~]$
5.备份主库数据配置备库
mmkdir /oradata/rman_backup
[oracle@rac1 rman_backup]$ rman target /
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
backup incremental level 0 database format='/oradata/backup_rman/database_%d_%u_%s';
sql 'alter system archive log current';
backup format '/oradata/backup_rman/arch_%d_%u_%s' diskratio=0 archivelog all delete input;
backup format '/oradata/backup_rman/control_%d_%u_%s' current controlfile;
release channel c1;
release channel c2;
release channel c3;
}
[oracle@rac1 backup_rman]$ ls -ltr
-rwxrwxr-x. 1 oracle oinstall 976 Jan 4 05:45 BACKUP_ETLDB.sh
-rw-r-----. 1 oracle oinstall 6463488 Jan 5 17:13 database_ETLDB_19tmj2i0_41
-rw-r-----. 1 oracle oinstall 435650560 Jan 5 17:13 database_ETLDB_18tmj2i0_40
-rw-r-----. 1 oracle oinstall 726351872 Jan 5 17:14 database_ETLDB_17tmj2i0_39
-rw-r-----. 1 oracle oinstall 112978944 Jan 5 17:14 arch_ETLDB_1dtmj2ja_45
-rw-r-----. 1 oracle oinstall 125304832 Jan 5 17:14 arch_ETLDB_1ctmj2ja_44
-rw-r-----. 1 oracle oinstall 229672448 Jan 5 17:14 arch_ETLDB_1btmj2j9_43
-rw-r-----. 1 oracle oinstall 5603328 Jan 5 17:14 arch_ETLDB_1etmj2jh_46
-rw-r-----. 1 oracle oinstall 10960896 Jan 5 17:14 control_ETLDB_1gtmj2jk_48
6.密码文件scp到备库
[oracle@node01 dbs]$scp orapwetldb oracle@192.168.1.2:$ORACLE_HOME/bs/orapwetldb
7.备库创建备份目录
mkdir /oradata/etldbdr_backup -p
8.把备份文件scp到备库(主库执行)
[oracle@node01 backup_rman]$ scp -rp * oracle@192.168.1.2:/oradata/etldbdr_backup
9.把主库spfile文件传到备库
[oracle@node01 ~]$ scp $ORACLE_HOME/dbs/initetldb.ora oracle@192.168.1.2:$ORACLE_HOME/dbs/
10.配置主库的监听
[oracle@node01 admin]$ cat listener.ora
listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/network/admin/listener.ora
Generated by Oracle configuration tools.
SID_LIST_LISTENER_11g =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = etldb)
(SID_NAME = etldb)
)
)
LISTENER_11g =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1(PORT = 1621))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
11.配置主库的TNS
[oracle@node01 admin]$ cat tnsnames.ora
tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/network/admin/tnsnames.ora
Generated by Oracle configuration tools.
ETLDBDR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ETLDBDR)
)
)
ETLDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1621))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ETLDB)
)
)
[oracle@node01 admin]$$ lsnrctl status LISTENER_11g
12.配置备库的TNS
[oracle@node02 admin]$ cat listener.ora
listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/network/admin/listener.ora
Generated by Oracle configuration tools.
SID_LIST_LISTENER_11g =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ETLDBDR)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0)
(SID_NAME = ETLDBDR)
)
)
LISTENER_11g =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1621))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
[oracle@rac2 admin]$ cat tnsnames.ora
tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/network/admin/tnsnames.ora
Generated by Oracle configuration tools.
ETLDBDR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1621))
)
(CONNECT_DATA =
(SERVICE_NAME = ETLDBDR)
)
)
ETLDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1621))
)
(CONNECT_DATA =
(SERVICE_NAME = ETLDB)
)
)
[oracle@rac2 admin]$ lsnrctl status LISTENER_11g
13.配置从库的初始化文件
[oracle@rac2 UOIN1CON_DG]$ cat initUOIN1CON_DG.ora
ETLDBDR.__data_transfer_cache_size=0
ETLDBDR.__db_cache_size=369098752
ETLDBDR.__inmemory_ext_roarea=0
ETLDBDR.__inmemory_ext_rwarea=0
ETLDBDR.__java_pool_size=16777216
ETLDBDR.__large_pool_size=33554432
ETLDBDR.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ETLDBDR.__pga_aggregate_target=587202560
ETLDBDR.__sga_target=687865856
ETLDBDR.__shared_io_pool_size=33554432
ETLDBDR.__shared_pool_size=218103808
ETLDBDR.streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/etldb/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/oradata/etldbdr/control01.ctl','/ordata/ETLDBDR/CONTROL/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/oradata/etldb','/oradata/etldbdr'
*.db_name='etldb'
*.db_unique_name='etldbdr'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ETLDBDR_XDB)'
*.fal_client='etldb'
*.fal_server='etldbdr'
*.log_archive_config='DG_CONFIG=(etldb,etldbdr)'
*.log_archive_dest_1='LOCATION=/oradata/etldb VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=etldb'
*.log_archive_dest_2='SERVICE=etldbdr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=etldbdr'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t%s%r.arc'
*.log_archive_max_processes=30
*.log_file_name_convert='/oradata/etldb','/oradata/etldbdr'
*.memory_target=1201m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
14.创建备库的必须的目录
[oracle@node02 ~]$ mkdir -p /u01/app/oracle/admin/etldb/adump
15.启动数据库到nomount状态
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initetldb.ora';
ORACLE instance started.
Total System Global Area 1275068416 bytes
Fixed Size 8620272 bytes
Variable Size 939525904 bytes
Database Buffers 318767104 bytes
Redo Buffers 8155136 bytes
SQL>
SQL> create spfile from pfile='$ORACLE_HOME/dbs/initetldb.ora';
File created.
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1275068416 bytes
Fixed Size 8620272 bytes
Variable Size 939525904 bytes
Database Buffers 318767104 bytes
Redo Buffers 8155136 bytes
SQL>
16.rman恢复备库控制文件
rman>restore controfile from '/oradata/etldbdr/control_ETLDB_1gtmj2jk_48';
rman> sql 'alter database mount';
17.rman恢复数据文件
在restore database之前先看看源库的数据文件/临时文件 路径和目标库是否相同，如果不相同，需要用下面方法进行修改
catalog start with '/oradata/etldbdr_backup/'
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
SET NEWNAME FOR DATAFILE '/oradata/etldb/system01.dbf' to '/oradata/etldbdr/system01.dbf';
SET NEWNAME FOR DATAFILE '/oradata/etldb/sysaux01.dbf' to '/oradata/etldbdr/sysaux01.dbf';
SET NEWNAME FOR DATAFILE '/oradata/etldb/undotbs01.dbf' to '/oradata/etldbdr/undotbs01.dbf';
SET NEWNAME FOR DATAFILE '/oradata/etldb/hrsys.dbf' to '/oradata/etldbdr/hrsys.dbf';
...
restore database;
switch datafile all;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
17.配置备库Standby redo logs
SQL> set lines 190
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;
NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE
UOIN1CON MOUNTED UOIN1CON_DG PHYSICAL STANDBY MAXIMUM PERFORMANCE
SQL>
SQL> col member for a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
3 ONLINE /oradata/etldbdr/redo03.log NO 0
2 ONLINE /oradata/etldbdr/redo02.log NO 0
1 ONLINE /oradata/etldbdr/redo01.log NO 0
4 STANDBY /oradata/etldbdr/redo04.log NO 0
5 STANDBY /oradata/etldbdr/redo05.log NO 0
6 STANDBY /oradata/etldbdr/redo06.log NO 0
7 STANDBY /oradata/etldbdr/redo07.log NO 0
7 rows selected.
SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;
GROUP# MEMBER BYTES
4 /oradata/etldbdr/redo04.log 209715200
5 /oradata/etldbdr/redo05.log 209715200
6 /oradata/etldbdr/redo06.log 209715200
7 /oradata/etldbdr/redo07.log 209715200
SQL>
18.开启备库MRPO进程
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL>
19.检查同步状态
On Primary
主库操作：
SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;
NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME
ETLDB READ WRITE PRIMARY ETLDB
SQL> select max(sequence#) from v$archived_log where archived='YES';
MAX(SEQUENCE#)
47
SQL>
On STANDBY
备库操作：
SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;
NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME
UOIN1CON MOUNTED PHYSICAL STANDBY UOIN1CON_DG
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
47
SQL>
20.主库切换日志进行测试备库同步状态
SQL> set lines 180
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;
NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE
UOIN1CON READ WRITE UOIN1CON PRIMARY MAXIMUM PERFORMANCE
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL>
21.检查备库
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;
NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE
etldb MOUNTED etldbdr PHYSICAL STANDBY MAXIMUM PERFORMANCE
SQL>
SQL> alter database recover managed standby database cancel;
Database altered.
SQL>
SQL> alter database open;
Database altered.
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;
NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE
etldb READ ONLY etldbdr PHYSICAL STANDBY MAXIMUM PERFORMANCE
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> /
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;
NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE
etldb READ ONLY WITH APPLY etldbdr PHYSICAL STANDBY MAXIMUM PERFORMANCE
浙公网安备 33010602011771号