1.配置DG为物理standby
主备配置LISTENER.ORA
[oracle@primary admin]$ cat
listener.ora
# listener.ora Network Configuration File:
/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION
=
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.ht8888.ht3)(PORT =
1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC
=
(GLOBAL_DBNAME = tianbaobao)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = tianbaobao)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@primary dbs]$ lsnrctl
reload
[oracle@standby admin]$ cat
listener.ora
# listener.ora Network Configuration File:
/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION
=
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.ht8888.ht3)(PORT =
1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC
=
(GLOBAL_DBNAME = tianbaobao)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = tianbaobao)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@standby dbs]$ lsnrctl
reload
主备配置TNSNAMES.ORA
[oracle@primary admin]$ cat
tnsnames.ora
# tnsnames.ora Network Configuration File:
/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.ht8888.ht3)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tianbaobao)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.ht8888.ht3)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tianbaobao)
)
)
[oracle@standby admin]$ cat
tnsnames.ora
# tnsnames.ora Network Configuration File:
/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.ht8888.ht3)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tianbaobao)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.ht8888.ht3)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tianbaobao)
)
)
主库参数配置:
开启归档模式
SQL> archive log
list
Database log
mode Archive Mode
Automatic
archival Enabled
Archive
destination USE_DB_RECOVERY_FILE_DEST
Oldest online log
sequence 3
Next log sequence to archive 5
Current log
sequence 5
SQL>
FORCE
LOGGING设置为YES,避免一些操作不记录日志,从而无法保存两端数据一致。
SQL> alter database force
logging;
Database altered.
SQL> select force_logging from
v$database;
FOR
---
YES
设置配置DG相关参数
SQL> alter system set
db_unique_name='primary'
scope=spfile;
System altered.
SQL> alter system set
log_archive_config='DG_CONFIG=(primary,standby)';
System altered.
SQL> alter system set
log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/
valid_for=(all_logfiles,all_roles) db_unique_name=primary'
scope=spfile;
System altered.
SQL> alter system set
log_archive_dest_2='SERVICE=standby
valid_for=(online_logfiles,primary_role) db_unique_name=standby'
scope=spfile;
System altered.
SQL> alter system set
log_archive_format='arch_%r_%t_%s.arc'
scope=spfile;
System altered.
SQL> alter system set
fal_client='primary' scope=spfile;
System altered.
SQL> alter system set
fal_server='standby'
scope=spfile;
System altered.
SQL> alter system set
standby_file_management=AUTO;
System altered.
添加STANDBY REDO LOG,便与以后切换后使用。
SQL> alter database add standby
logfile group 4
'/u01/app/oracle/oradata/tianbaobao/standby_redo04.log' size
50M;
Database altered.
SQL> alter database add standby
logfile group 5
'/u01/app/oracle/oradata/tianbaobao/standby_redo05.log' size
50M;
Database altered.
SQL> alter database add standby
logfile group 6
'/u01/app/oracle/oradata/tianbaobao/standby_redo06.log' size
50M;
Database altered.
SQL> alter database add standby
logfile group 7
'/u01/app/oracle/oradata/tianbaobao/standby_redo07.log' size
50M;
Database altered.
SQL> shutdown
immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed
Size 2253664 bytes
Variable Size 1493175456
bytes
Database Buffers 100663296
bytes
Redo
Buffers 7319552 bytes
Database mounted.
Database opened.
SQL>
主库拷贝密码文件至备库
[oracle@primary tianbaobao]$ scp
/u01/app/oracle/product/11.2.0/db_1/dbs/orapwtianbaobao
standby:/u01/app/oracle/product/11.2.0/db_1/dbs/
主备TNSPING,IT'S OK?
[oracle@primary admin]$ tnsping
primary
[oracle@primary admin]$ tnsping standby
[oracle@standby admin]$ tnsping primary
[oracle@standby admin]$ tnsping standby
备库上创建基本目录
[oracle@standby admin]$ mkdir -p
/u01/app/oracle/admin/tianbaobao/{adump,dpdump,pfile} [oracle@standby admin]$ mkdir -p
/u01/app/oracle/oradata/tianbaobao
[oracle@standby admin]$ mkdir -p
/u01/app/oracle/fast_recovery_area/tianbaobao
备库创建PFILE文件,这个位置注意DBNAME,11g限制最长8位。
[oracle@standby dbs]$ echo
'db_name=tianbaob' >
/u01/app/oracle/product/11.2.0/db_1/dbs/inittianbaobao.ora
启动备库到NOMOUNT
[oracle@standby dbs]$ sqlplus / as
sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 19 19:07:34
2015
Copyright (c) 1982, 2013, Oracle. All rights
reserved.
Connected to an idle instance.
SQL> startup
nomount
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed
Size 2251816 bytes
Variable Size 159384536 bytes
Database
Buffers 50331648 bytes
Redo
Buffers 5189632 bytes
SQL>
DUPLICATE TO STANDBY开始
[oracle@primary dbs]$ rman
target sys/oracle@primary
auxiliary sys/oracle@standby
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 19
18:57:44 2015
Copyright (c) 1982, 2011, Oracle and/or its
affiliates. All rights reserved.
connected to target database: TIANBAOB (DBID=2088615787)
connected to auxiliary database (not started)
RMAN> run{
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> allocate auxiliary channel c3 device type disk;
5> allocate auxiliary channel c4 device type disk;
6> duplicate target database for standby from active database
nofilenamecheck
7> dorecover
8> spfile
9> parameter_value_convert 'primary','standby'
10> set db_unique_name='standby'
11> set
log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/
valid_for=(all_logfiles,all_roles) db_unique_name=standby'
12> set log_archive_dest_2='SERVICE=primary valid_for=(online_logfile,primary_role)
db_unique_name=primary'
13> set log_archive_max_processes='5'
14> set standby_file_management='AUTO'
15> set fal_client='standby'
16> set fal_server='primary';
17> release channel c1;
18> release channel c2;
19> release channel c3;
20> release channel c4;
21> }
备库检查
[oracle@standby dbs]$ sqlplus / as
sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 19 19:12:46
2015
Copyright (c) 1982, 2013, Oracle. All rights
reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
SQL> select
instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
tianbaobao MOUNTED
SQL> alter database
open;
Database altered.
SQL> select
open_mode,name,database_role,db_unique_name from
v$database;
OPEN_MODE NAME DATABASE_ROLE DB_UNIQUE_NAME
-------------------- --------- ----------------
------------------------------
READ
ONLY TIANBAOB PHYSICAL STANDBY standby
SQL>
应用归档日志方式进行数据同步
SQL> alter system set
log_archive_dest_2='SERVICE=standby arch noaffirm
valid_for=(online_logfiles,primary_role) db_unique_name=standby'
scope=spfile;
System altered.
SQL>
应用在线日志方式进行数据同步
SYNC:
SQL> alter system set
log_archive_dest_2='SERVICE=standby lgwr sync affirm
valid_for=(online_logfiles,primary_role) db_unique_name=standby'
scope=spfile;
System altered.
ASYNC:
SQL> alter system set
log_archive_dest_2='SERVICE=standby lgwr async noaffirm
valid_for=(online_logfiles,primary_role)
db_unique_name=standby';
System altered.
SQL>
配置Active Standby
SQL> select open_mode from
v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database recover
managed standby database cancel;
Database altered.
SQL> alter database
open;
Database altered.
SQL> select open_mode from
v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> alter database recover
managed standby database using current logfile
disconnect;
Database altered.
SQL> select open_mode from
v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL>
状态“READ ONLY WITH
APPLY”即表示此时备库处于Read
Only状态的同时可以接受主库传过来的日志进行恢复,以便达到备库可以即时查看到主库变化的目的。
2.配置Standby之最大性能,最大高可用和最大保护模式
STANDBY之最大保护模式
SQL> shutdown
immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed
Size 2253664 bytes
Variable Size 1493175456
bytes
Database Buffers 100663296
bytes
Redo
Buffers 7319552 bytes
Database mounted.
SQL> alter database set standby
database to maximize protection;
Database altered.
SQL> alter database open;
Database altered.
SQL> select
open_mode,name,database_role,protection_mode,protection_level,db_unique_name
from v$database;
OPEN_MODE NAME DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL DB_UNIQUE_NAME
-------------------- --------- ----------------
-------------------- --------------------
------------------------------
READ
WRITE TIANBAOB PRIMARY MAXIMUM
PROTECTION MAXIMUM
PROTECTION primary
STANDBY之最大可用模式
SQL> shutdown
immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed
Size 2253664 bytes
Variable Size 1493175456
bytes
Database Buffers 100663296
bytes
Redo
Buffers 7319552 bytes
Database mounted.
SQL> alter database set standby
database to maximize availability;
Database altered.
SQL> alter database
open;
Database altered.
SQL> select
open_mode,name,database_role,protection_mode,protection_level,db_unique_name
from v$database;
OPEN_MODE NAME DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL DB_UNIQUE_NAME
-------------------- --------- ----------------
-------------------- --------------------
------------------------------
READ
WRITE TIANBAOB PRIMARY MAXIMUM AVAILABILITY
MAXIMUM AVAILABILITY primary
STANDBY之最大性能模式
SQL> alter system set
log_archive_dest_2='SERVICE=standby arch noaffirm
valid_for=(online_logfiles,primary_role)
db_unique_name=standby';
System altered.
SQL> shutdown
immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed
Size 2253664 bytes
Variable Size 1493175456
bytes
Database Buffers 100663296
bytes
Redo
Buffers 7319552 bytes
Database mounted.
SQL> alter database set standby
database to maximize performance;
Database altered.
SQL> alter database
open;
Database altered.
SQL> select
open_mode,name,database_role,protection_mode,protection_level,db_unique_name
from v$database;
OPEN_MODE NAME DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL DB_UNIQUE_NAME
-------------------- --------- ----------------
-------------------- --------------------
------------------------------
READ
WRITE TIANBAOB PRIMARY MAXIMUM
PERFORMANCE MAXIMUM PERFORMANCE primary
3.配置DG为逻辑standby模式
1.创建physical standby
2.备库停止apply日志文件
SQL> alter database recover
managed standby database cancel;
Database altered.
SQL>
3.修改主库参数文件
设置归档路径用于存放由primary传过来后生成的standby归档文件,参数log_archive_dest_N
valid_for部分修改成STANDBY_LOGFILES,STANDBY_ROLE,如果你以后不想主库转换成逻辑standby
角色可以跳过此步。
alter system set log_archive_dest_3='location=/u01/app/oracle/fast_recovery_area/standbyarch VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=standby';
4.生成LogMiner字典信息
避免有些特定操作备库无法接受sql语句,故而生成记录的元数据信息到redo
log,这里需要注意备库的,shared_pool_size,undo_retention大小是否满足需求。
SQL> exec
dbms_logstdby.build;
PL/SQL procedure successfully completed.
SQL>
5.物理standby转换逻辑standby。
这里要注意查看一下双向listener是否开启,否认报错如下,后一直处于等待状态,启动listener后正常转换成功。
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.4.0 -
Production
TCP/IP NT Protocol Adapter for Linux: Version
11.2.0.4.0 - Production
Time: 21-MAR-2015 11:12:13
Tracing not turned on.
Tns error struct:
ns main err
code: 12541
TNS-12541: TNS:no listener
ns secondary
err code: 12560
nt main err
code: 511
TNS-00511: No listener
nt secondary
err code: 111
nt OS err
code: 0
SQL> alter database recover to
logical standby standby;
Database altered.
SQL>
6.OPEN逻辑standby,开启redo实时应用。
用resetlogs方式打开逻辑standby,开启redo应用,为逻辑standby添加standby redologs日志组,以便实时应用redo数据
SQL> shutdown
immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed
Size 2253664 bytes
Variable Size 1493175456
bytes
Database Buffers 100663296
bytes
Redo
Buffers 7319552 bytes
Database mounted.
SQL> alter database open
resetlogs;
Database altered.
SQL> alter database start
logical standby apply immediate;
Database altered.
关闭redo应用,开启redo应用
SQL> alter database stop logical
standby apply;
Database altered.
SQL> alter database start
logical standby apply;
Database altered.
7.检查状态
SQL> select
open_mode,name,database_role,protection_mode,protection_level,db_unique_name
from v$database;
OPEN_MODE NAME DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL DB_UNIQUE_NAME
-------------------- --------- ----------------
-------------------- --------------------
------------------------------
READ
WRITE STANDBY LOGICAL
STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
standby
SQL>
[root@standby ~]# ps -ef|grep
mrp
root 2873 2852 0 11:41
pts/2 00:00:00 grep mrp
[root@standby ~]# ps -ef|grep
lsp
oracle 2823 1 0 11:38
? 00:00:00 ora_lsp0_tianbaobao
root 2875 2852 0 11:41
pts/2 00:00:00 grep lsp
IT'
OVER!!!!