Data guard搭建
一、准备工作
Oracle版本11.2.0.1，操作系统版本时红帽5.5
基本步骤
1.1打开force log
SQL> ALTER DATABASE FORCE LOGGING;
1.2备份主库
run {
sql 'alter system switch logfile';
shutdown immediate;
startup mount;
backup database format '/dsk3/backup/prod_%d_%t.bak';
alter database open;
sql 'alter system switch logfile';
}
RMAN> run {
2> sql 'alter system switch logfile';
3> shutdown immediate;
4> startup mount;
5> backup database format '/dsk3/backup/prod_%d_%t.bak';
6> alter database open;
7> sql 'alter system switch logfile';
8> }
using target database control file instead of recovery catalog
sql statement: alter system switch logfile
database closed
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 234884772 bytes
Database Buffers 595591168 bytes
Redo Buffers 5160960 bytes
Starting backup at 07-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/prod/system01.dbf
input datafile file number=00002 name=/oradata/prod/sysaux01.dbf
input datafile file number=00003 name=/oradata/prod/undotbs01.dbf
input datafile file number=00004 name=/oradata/prod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-MAY-16
channel ORA_DISK_1: finished piece 1 at 07-MAY-16
piece handle=/dsk3/backup/prod_PROD_911229277.bak tag=TAG20160507T151437 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-MAY-16
channel ORA_DISK_1: finished piece 1 at 07-MAY-16
piece handle=/dsk3/backup/prod_PROD_911229343.bak tag=TAG20160507T151437 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-MAY-16
database opened
sql statement: alter system switch logfile
二、编辑pfile
2.1主库的pfile
prod.__db_cache_size=595591168
prod.__java_pool_size=4194304
prod.__large_pool_size=4194304
prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod.__pga_aggregate_target=104857600
prod.__sga_target=838860800
prod.__shared_io_pool_size=0
prod.__shared_pool_size=222298112
prod.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/prod/control01.ctl','/oradata/prod/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.log_archive_dest_1='location=/dsk3/arch'
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=838860800
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=bjdb
LOG_ARCHIVE_CONFIG='DG_CONFIG=(bjdb,shdb)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/dsk3/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=bjdb'
LOG_ARCHIVE_DEST_2=
'SERVICE=shdb ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=shdb'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=3
FAL_SERVER=shdb
DB_FILE_NAME_CONVERT='/oradata/shdb','/oradata/prod/'
LOG_FILE_NAME_CONVERT=
'/dsk1/sh/onlinelog','/dsk1/bj/onlinelog/','/dsk2/sh/onlinelog/','/dsk2/bj/onlinelog/'
STANDBY_FILE_MANAGEMENT=AUTO
2.2备库的pfile
DB_NAME=prod
DB_UNIQUE_NAME=shdb
LOG_ARCHIVE_CONFIG='DG_CONFIG=(bjdb,shdb)'
CONTROL_FILES='/oradata/shdb/sh01.ctl', '/oradata/shdb/sh02.ctl'
DB_FILE_NAME_CONVERT='/oradata/prod/','/oradata/shdb/'
这里的这个参数如下设置也是可以的，只要/oradata/shdb/路径已经创建。
#DB_FILE_NAME_CONVERT='prod','shdb'
LOG_FILE_NAME_CONVERT=
'/dsk1/bj/onlinelog/','/dsk1/sh/onlinelog/','/dsk2/bj/onlinelog/','/dsk2/sh/onlinelog/'
LOG_ARCHIVE_DEST_1=
'LOCATION=/dsk3/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=shdb'
LOG_ARCHIVE_DEST_2=
'SERVICE=bjdb ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=bjdb'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=bjdb
三、重启主库到mount
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/home/oracle/init.ora' nomount;
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 234884772 bytes
Database Buffers 595591168 bytes
Redo Buffers 5160960 bytes
SQL> set pagesize 200
SQL> show parameter log
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_syslog_level string
commit_logging string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
enable_ddl_logging boolean FALSE
log_archive_config string DG_CONFIG=(bjdb,shdb)
log_archive_dest string
log_archive_dest_1 string LOCATION=/dsk3/arch
VALID_FOR=(ALL_LOGFILES,ALL_
ROLES)
DB_UNIQUE_NAME=bjdb
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string SERVICE=shdb ASYNC
VALID_FOR=(ONLINE_LOGFILES,P
RIMARY_ROLE)
DB_UNIQUE_NAME=shdb
log_archive_dest_20 string
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 3
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
log_buffer integer 4980736
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
log_file_name_convert string /dsk1/sh/onlinelog, /dsk1/bj/o
nlinelog/, /dsk2/sh/onlinelog/
, /dsk2/bj/onlinelog/
remote_login_passwordfile string EXCLUSIVE
sec_case_sensitive_logon boolean TRUE
sec_max_failed_login_attempts integer 10
SQL>
SQL>
SQL>
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /oradata/shdb, /oradata/prod/
db_name string prod
db_unique_name string bjdb
global_names boolean FALSE
instance_name string prod
lock_name_space string
log_file_name_convert string /dsk1/sh/onlinelog, /dsk1/bj/o
nlinelog/, /dsk2/sh/onlinelog/
, /dsk2/bj/onlinelog/
service_names string bjdb
SQL>
SQL>
SQL> create spfile from pfile='/home/oracle/init.ora';
File created.
SQL>
SQL>
SQL>
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 234884772 bytes
Database Buffers 595591168 bytes
Redo Buffers 5160960 bytes
Database mounted.
SQL> show parameter log
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_syslog_level string
commit_logging string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
enable_ddl_logging boolean FALSE
log_archive_config string DG_CONFIG=(bjdb,shdb)
log_archive_dest string
log_archive_dest_1 string LOCATION=/dsk3/arch
VALID_FOR=(ALL_LOGFILES,ALL_
ROLES)
DB_UNIQUE_NAME=bjdb
四、拷贝主库到备库
4.1拷贝数据文件
[oracle@bj prod]$ scp * sh:/oradata/shdb
oracle@sh's password:
control01.ctl 100% 9520KB 4.7MB/s 00:02
control02.ctl 100% 9520KB 4.7MB/s 00:02
redo01.log 100% 50MB 5.0MB/s 00:10
sysaux01.dbf 100% 600MB 5.6MB/s 01:47
system01.dbf 100% 700MB 5.5MB/s 02:07
temp01.dbf 100% 20MB 5.0MB/s 00:04
undotbs01.dbf 100% 275MB 5.4MB/s 00:51
users01.dbf 100% 5128KB 2.5MB/s 00:02
[oracle@bj prod]$
4.2拷贝密码文件和pfile
[oracle@bj ~]$ scp init.ora sh:$ORACLE_HOME/dbs/
oracle@sh's password:
init.ora 100% 1420 1.4KB/s 00:00
[oracle@bj ~]$
[oracle@bj dbs]$ scp orapwprod sh:$ORACLE_HOME/dbs/orapwshdb
oracle@sh's password:
orapwprod 100% 1536 1.5KB/s 00:01
[oracle@bj dbs]$
4.3生成备库控制文件，并拷贝到备库
QL> alter database create standby controlfile as '/home/oracle/sh.ctl';
Database altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
[oracle@bj ~]$ ll
total 9836
-rw-rw-r-- 1 oracle oracle 30 Mar 26 2013 afiedt.buf
-rw-rw-r-- 1 oracle oracle 1420 May 7 15:06 init.ora
-rw-r----- 1 oracle oracle 289727 Mar 26 2013 install2013-03-26_00-35-09.log
drwxr-x--- 3 oracle oracle 4096 Mar 25 2013 oradiag_oracle
-rw-rw---- 1 oracle oracle 9748480 May 7 15:41 sh.ctl
[oracle@bj ~]$
[oracle@bj ~]$ scp sh.ctl sh:/oradata/shdb/sh01.ctl
oracle@sh's password:
sh.ctl 100% 9520KB 9.3MB/s 00:01
[oracle@bj ~]$ scp sh.ctl sh:/oradata/shdb/sh02.ctl
oracle@sh's password:
sh.ctl 100% 9520KB 9.3MB/s 00:01
[oracle@bj ~]$
五、配置tns并启动备库
5.1主库备库都启动监听
[oracle@sh admin]$ vi tnsnames.ora
BJDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bj)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bjdb)
)
)
SHDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sh)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = shdb)
)
)
5.2启动备库
export ORACLE_SID=shdb
SQL> startup pfile='/home/oracle/init.ora' nomount;
ORACLE instance started.
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /oradata/prod/, /oradata/shdb
db_name string prod
db_unique_name string shdb
global_names boolean FALSE
instance_name string shdb
lock_name_space string
log_file_name_convert string /dsk1/bj/onlinelog/, /dsk1/sh/
onlinelog/, /dsk2/bj/onlinelog
/, /dsk2/sh/onlinelog/
service_names string shdb
SQL> create spfile from pfile='/home/oracle/init.ora';
六、使用备份恢复的方式创建dg
6.1使用duplicate方式搭建dg
6.1.1编辑主库备库的pfile
主库
[oracle@bj ~]$ more init.ora
prod.__db_cache_size=595591168
prod.__java_pool_size=4194304
prod.__large_pool_size=4194304
prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod.__pga_aggregate_target=104857600
prod.__sga_target=838860800
prod.__shared_io_pool_size=0
prod.__shared_pool_size=222298112
prod.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/prod/control01.ctl','/oradata/prod/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.log_archive_dest_1='location=/dsk3/arch'
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=838860800
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=bjdb
LOG_ARCHIVE_CONFIG='DG_CONFIG=(bjdb,shdb)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/dsk3/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=bjdb'
LOG_ARCHIVE_DEST_2=
'SERVICE=shdb ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=shdb'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=3
FAL_SERVER=shdb
DB_FILE_NAME_CONVERT='/oradata/shdb','/oradata/prod/'
LOG_FILE_NAME_CONVERT='/dsk1/sh/onlinelog','/dsk1/bj/onlinelog/','/dsk2/sh/onlinelog/','/dsk2/bj
/onlinelog/'
STANDBY_FILE_MANAGEMENT=AUTO
------------------------------------------------------------------------
备库
[oracle@sh ~]$ more init.ora
shdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.CONTROL_FILES='/oradata/shdb/sh01.ctl','/oradata/shdb/sh02.ctl'
*.DB_FILE_NAME_CONVERT='/oradata/prod/','/oradata/shdb/'
*.DB_NAME='prod'
*.DB_UNIQUE_NAME='shdb'
*.FAL_SERVER='bjdb'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(bjdb,shdb)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/dsk3/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=shdb'
*.LOG_ARCHIVE_DEST_2='SERVICE=bjdb ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=bjdb'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_FILE_NAME_CONVERT='/dsk1/bj/onlinelog/','/dsk1/sh/onlinelog/','/dsk2/bj/onlinelog/','/dsk2
/sh/onlinelog/'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT='AUTO'
6.1.2备库需要配置静态监听
先连接到主库
RMAN> connect target sys@JED
在连接到备库，前提是配置静态监听
RMAN> connect auxiliary sys@JED2
在通过这条明天创建备库
RMAN> duplicate target database for standby from active database;
连接报错，需要配置静态监听
[oracle@sh shdb]$ rman target / auxiliary sys/oracle@shdb
Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 7 22:19:38 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (not mounted)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
[oracle@sh shdb]$ cd
配置静态监听
[oracle@sh admin]$ mv listener.ora listener.ora.bak
[oracle@sh admin]$ vi listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sh)(PORT = 1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = shdb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = shdb)
)
)
[oracle@sh admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 07-MAY-2016 22:27:58
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/sh/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sh)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sh)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 07-MAY-2016 22:27:58
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/sh/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sh)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "shdb" has 1 instance(s).
Instance "shdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@sh admin]$
同时连接主库和备库
[oracle@sh ~]$ rman target sys/oracle@bjdb auxiliary sys/oracle@shdb
Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 7 22:35:23 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=222874664)
connected to auxiliary database: PROD (not mounted)
RMAN>
6.1.3使用dulicate命令创建备库
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 07-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=17 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprod' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwshdb' ;
}
executing Memory Script
Starting backup at 07-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
Finished backup at 07-MAY-16
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/oradata/shdb/sh01.ctl';
restore clone controlfile to '/oradata/shdb/sh02.ctl' from
'/oradata/shdb/sh01.ctl';
}
executing Memory Script
Starting backup at 07-MAY-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_prod.f tag=TAG20160507T224404 RECID=3 STAMP=911256244
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 07-MAY-16
Starting restore at 07-MAY-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 07-MAY-16
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/oradata/shdb/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/oradata/shdb/system01.dbf";
set newname for datafile 2 to
"/oradata/shdb/sysaux01.dbf";
set newname for datafile 3 to
"/oradata/shdb/undotbs01.dbf";
set newname for datafile 4 to
"/oradata/shdb/users01.dbf";
set newname for datafile 5 to
"/oradata/shdb/tbs01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/oradata/shdb/system01.dbf" datafile
2 auxiliary format
"/oradata/shdb/sysaux01.dbf" datafile
3 auxiliary format
"/oradata/shdb/undotbs01.dbf" datafile
4 auxiliary format
"/oradata/shdb/users01.dbf" datafile
5 auxiliary format
"/oradata/shdb/tbs01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradata/shdb/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 07-MAY-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oradata/prod/system01.dbf
output file name=/oradata/shdb/system01.dbf tag=TAG20160507T224414
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:58
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oradata/prod/sysaux01.dbf
output file name=/oradata/shdb/sysaux01.dbf tag=TAG20160507T224414
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:47
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/oradata/prod/undotbs01.dbf
output file name=/oradata/shdb/undotbs01.dbf tag=TAG20160507T224414
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:27
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/oradata/prod/tbs01.dbf
output file name=/oradata/shdb/tbs01.dbf tag=TAG20160507T224414
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oradata/prod/users01.dbf
output file name=/oradata/shdb/users01.dbf tag=TAG20160507T224414
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 07-MAY-16
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=911255907 file name=/oradata/shdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=911255907 file name=/oradata/shdb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=911255907 file name=/oradata/shdb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=911255907 file name=/oradata/shdb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=911255907 file name=/oradata/shdb/tbs01.dbf
Finished Duplicate Db at 07-MAY-16
RMAN>
6.1.4使用打开备库的介质恢复功能
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL>
SQL>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL>
SQL>
SQL>
6.2使用备份恢复的方式搭建dg
七、一些问题
7.1备库db_file_name_convert设置错误
主库切换日志，备库接收到日志，单并没有进行介质恢复
[oracle@bj dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat May 7 16:23:32 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL>
备库接收到归档日志
[oracle@sh trace]$ tail -f /u01/app/oracle/diag/rdbms/shdb/shdb/trace/alert_shdb.log
Sat May 07 16:19:48 2016
RFS[3]: Assigned to RFS process 719
RFS[3]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 1060
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Opened log for thread 1 sequence 61 dbid 222874664 branch 811036328
Archived Log entry 4 added for thread 1 sequence 61 rlc 811036328 ID 0xd494f28 dest 2:
RFS[3]: Opened log for thread 1 sequence 62 dbid 222874664 branch 811036328
Sat May 07 16:20:42 2016
RFS[4]: Assigned to RFS process 725
RFS[4]: Identified database type as 'physical standby': Client is ARCH pid 1058
Sat May 07 16:23:32 2016
Archived Log entry 5 added for thread 1 sequence 62 rlc 811036328 ID 0xd494f28 dest 2:
RFS[3]: Opened log for thread 1 sequence 63 dbid 222874664 branch 811036328
Sat May 07 16:24:09 2016
Archived Log entry 6 added for thread 1 sequence 63 rlc 811036328 ID 0xd494f28 dest 2:
RFS[3]: Opened log for thread 1 sequence 64 dbid 222874664 branch 811036328
[oracle@sh dsk3]$ cd arch
[oracle@sh arch]$ ll
total 1008
-rw-rw---- 1 oracle oracle 159232 May 7 16:19 1_58_811036328.dbf
-rw-rw---- 1 oracle oracle 48640 May 7 16:19 1_59_811036328.dbf
-rw-rw---- 1 oracle oracle 481792 May 7 16:19 1_60_811036328.dbf
-rw-rw---- 1 oracle oracle 54272 May 7 16:19 1_61_811036328.dbf
-rw-rw---- 1 oracle oracle 244736 May 7 16:23 1_62_811036328.dbf
-rw-rw---- 1 oracle oracle 52429312 May 7 16:23 1_63_811036328.dbf
主库切换日志后，备库归档目录下日志增加。
[oracle@sh arch]$ ll
total 1016
-rw-rw---- 1 oracle oracle 159232 May 7 16:19 1_58_811036328.dbf
-rw-rw---- 1 oracle oracle 48640 May 7 16:19 1_59_811036328.dbf
-rw-rw---- 1 oracle oracle 481792 May 7 16:19 1_60_811036328.dbf
-rw-rw---- 1 oracle oracle 54272 May 7 16:19 1_61_811036328.dbf
-rw-rw---- 1 oracle oracle 244736 May 7 16:23 1_62_811036328.dbf
-rw-rw---- 1 oracle oracle 24576 May 7 16:24 1_63_811036328.dbf
-rw-rw---- 1 oracle oracle 52429312 May 7 16:24 1_64_811036328.dbf
[oracle@sh arch]$
开启日志恢复后告警日志报错
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
/dsk3/arch/1_58_811036328.dbf
/dsk3/arch/1_60_811036328.dbf
/dsk3/arch/1_59_811036328.dbf
/dsk3/arch/1_61_811036328.dbf
/dsk3/arch/1_62_811036328.dbf
/dsk3/arch/1_63_811036328.dbf
6 rows selected.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>recover managed standby database cancel;
Sat May 07 16:29:42 2016
Errors in file /u01/app/oracle/diag/rdbms/shdb/shdb/trace/shdb_dbw0_678.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/oradata/shdbsystem01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/shdb/shdb/trace/shdb_dbw0_678.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/oradata/shdbsysaux01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/shdb/shdb/trace/shdb_dbw0_678.trc:
ORA-01157: cannot identify/lock data file
从新设置这个参数，从起恢复后正常
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /oradata/prod/, /oradata/shdb
db_name string prod
db_unique_name string shdb
global_names boolean FALSE
instance_name string shdb
lock_name_space string
log_file_name_convert string /dsk1/bj/onlinelog/, /dsk1/sh/
onlinelog/, /dsk2/bj/onlinelog
/, /dsk2/sh/onlinelog/
service_names string shdb
7.2备库日志文件的报错处理
备库告警报日志错误，清除此错误
RA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/dsk2/sh/onlinelog/redo03a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/dsk1/sh/onlinelog/redo03b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/shdb/shdb/trace/shdb_mrp0_1094.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/dsk2/sh/onlinelog/redo03a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/dsk1/sh/onlinelog/redo03b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 3 complete
Mv日志文件
[root@sh onlinelog]# ll
total 153780
-rw-rw---- 1 oracle oracle 52429312 May 7 16:35 redo01a.bak
-rw-rw---- 1 oracle oracle 52429312 May 7 16:35 redo02a.bak
-rw-rw---- 1 oracle oracle 52429312 May 7 16:35 redo03a.bak
取消介质恢复
SQL>recover managed standby database cancel;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> alter database clear logfile group 1;
Database altered.
Clear之后所有的日志文件都进行了重建
[oracle@sh onlinelog]$ ll
total 307560
-rw-rw---- 1 oracle oracle 52429312 May 7 16:35 redo01b.bak
-rw-rw---- 1 oracle oracle 52429312 May 7 17:00 redo01b.log
-rw-rw---- 1 oracle oracle 52429312 May 7 16:35 redo02b.bak
-rw-rw---- 1 oracle oracle 52429312 May 7 16:51 redo02b.log
-rw-rw---- 1 oracle oracle 52429312 May 7 16:35 redo03b.bak
-rw-rw---- 1 oracle oracle 52429312 May 7 16:51 redo03b.log
SQL> select group#,thread#,bytes/1024/1024 mb ,status,SEQUENCE# from v$log
2 ;
GROUP# THREAD# MB STATUS SEQUENCE#
---------- ---------- ---------- ---------------- ----------
1 1 50 CLEARING_CURRENT 73
3 1 50 CLEARING 72
2 1 50 CLEARING 71
主库进行日志切换，备库也随之切换
SQL> alter system switch logfile;
System altered.
主库
SQL> select group#,thread#,bytes/1024/1024 mb ,status,SEQUENCE# from v$log;
GROUP# THREAD# MB STATUS SEQUENCE#
---------- ---------- ---------- ---------------- ----------
1 1 50 ACTIVE 73
2 1 50 CURRENT 74
3 1 50 INACTIVE 72
备库状态
SQL> select group#,thread#,bytes/1024/1024 mb ,status,SEQUENCE# from v$log;
GROUP# THREAD# MB STATUS SEQUENCE#
---------- ---------- ---------- ---------------- ----------
1 1 50 CLEARING 73
3 1 50 CLEARING 72
2 1 50 CLEARING_CURRENT 74
打开日志应用
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
日志没有报错
Completed: alter database recover managed standby database disconnect from session
Media Recovery Waiting for thread 1 sequence 74 (in transit)
7.3切换主库和备库
7.3.1查看主库和备库的状态
主库
SQL> r
1* select name,dbid,database_role,PROTECTION_MODE from v$database
NAME DBID DATABASE_ROLE PROTECTION_MODE
---------- ---------- ---------------- --------------------
PROD 222874664 PRIMARY MAXIMUM PERFORMANCE
SQL>
SQL>
SQL>
备库
SQL> select name,dbid,database_role,PROTECTION_MODE from v$database;
NAME DBID DATABASE_ROLE PROTECTION_MODE
-------------------- ---------- ---------------- --------------------
PROD 222874664 PHYSICAL STANDBY MAXIMUM PERFORMANCE
SQL>
7.3.2查看主库和备库是否允许切换
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
89
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
89
SQL> select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
---------- ---------------- -------------------- --------------------
PROD PRIMARY MAXIMUM PERFORMANCE TO STANDBY
SQL>
SQL> select name,database_role,protection_mode,switchover_status from v$database;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
-------------------- ---------------- -------------------- --------------------
PROD PHYSICAL STANDBY MAXIMUM PERFORMANCE NOT ALLOWED
SQL> select username,sid from v$session where username is not null;
USERNAME SID
------------------------------ ----------
SYS 1
SQL>
7.3.3切换主备库
切换主库，正常下主库处于open状态
SQL> alter database commit to switchover to physical standby;
Database altered.
SQL> select name,database_role,protection_mode,switchover_status from v$database;
select name,database_role,protection_mode,switchover_status from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 234884772 bytes
Database Buffers 595591168 bytes
Redo Buffers 5160960 bytes
Database mounted.
打开介质应用
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
切换备库
此时备库的状态会自动变成 to primary
SQL> r
1* select name,database_role,protection_mode,switchover_status from v$database
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
-------------------- ---------------- -------------------- --------------------
PROD PHYSICAL STANDBY MAXIMUM PERFORMANCE TO PRIMARY
SQL>
切换的时候可以再mount，也可以再read only open状态
SQL> alter database commit to switchover to primary;
Database altered.
SQL> select name,database_role,protection_mode,switchover_status from v$database
2 ;
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
-------------------- ---------------- -------------------- --------------------
PROD PRIMARY MAXIMUM PERFORMANCE NOT ALLOWED
SQL>
切换完成后从新启动数据库
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2865
Session ID: 19 Serial number: 9
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
[oracle@sh ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat May 7 23:26:31 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
Database mounted.
Database opened.
SQL>
7.3.4验证主库和备库是否一致
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
94
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
96
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
94
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
96
7.4主库RESOLVABLE GAP
该状态表示从库上有未应用的日志，我从新打开从库的日志应用来处理。状态变正常
主库查看
1* select name,database_role,protection_mode,switchover_status from v$database
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
PROD PRIMARY MAXIMUM PERFORMANCE RESOLVABLE GAP
从库执行
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
主库查看
SQL> r
1* select name,database_role,protection_mode,switchover_status from v$database
NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
--------- ---------------- -------------------- --------------------
PROD PRIMARY MAXIMUM PERFORMANCE TO STANDBY
7.5 data guard模式切换
Oracle data guard有三种模式分别是，最大性能，最大保护，最高可用性，这三种模式最大性能，数据安全性最低，对主库的影响最小。
7.5.1查看主库和备库的状态
主库
SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
备库
SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
修改主库log_archive_dest_2
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=shdb ASYNC
VALID_FOR=(ONLINE_LOGFILES,P
RIMARY_ROLE)
DB_UNIQUE_NAME=shdb
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=shdb lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=shdb';
System altered.
SQL> show parameter LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=shdb LGWR SYNC AFFIRM
VALID_FOR=(ONLINE_LOGFILES,PRI
MARY_ROLE) DB_UNIQUE_NAME=shdb
7.5.2为备库添加standby log
SQL> select sequence#,group#,bytes/1024/1024 from v$log;
SEQUENCE# GROUP# BYTES/1024/1024
---------- ---------- ---------------
129 1 50
131 3 50
130 2 50
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/dsk1/sh/onlinelog/redo02b.log
/dsk1/sh/onlinelog/redo01b.log
/dsk2/sh/onlinelog/redo01a.log
/dsk2/sh/onlinelog/redo02a.log
/dsk1/sh/onlinelog/redo03b.log
/dsk2/sh/onlinelog/redo03a.log
6 rows selected.
SQL> alter database add standby logfile group 10 '/dsk1/sh/onlinelog/redo10a.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 11 '/dsk1/sh/onlinelog/redo11a.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 12 '/dsk1/sh/onlinelog/redo12a.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 13 '/dsk1/sh/onlinelog/redo13a.log' size 50m;
Database altered.
SQL>
SQL> col member for a40
SQL> r
1* select GROUP#,status,member from v$logfile
GROUP# STATUS MEMBER
---------- ------- ----------------------------------------
2 /dsk1/sh/onlinelog/redo02b.log
1 /dsk1/sh/onlinelog/redo01b.log
1 /dsk2/sh/onlinelog/redo01a.log
2 /dsk2/sh/onlinelog/redo02a.log
3 /dsk1/sh/onlinelog/redo03b.log
3 /dsk2/sh/onlinelog/redo03a.log
10 /dsk1/sh/onlinelog/redo10a.log
11 /dsk1/sh/onlinelog/redo11a.log
12 /dsk1/sh/onlinelog/redo12a.log
13 /dsk1/sh/onlinelog/redo13a.log
7.5.3修改主备库为最大可用性
SQL> alter database set standby database to maximize availability;
Database altered.
SQL> alter database set standby database to maximize availability;
Database altered.
SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL>
最大性能
SQL> alter database set standby database to maximize performance;
Database altered.
SQL> select NAME,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
NAME PROTECTION_MODE PROTECTION_LEVEL
--------- -------------------- --------------------
PROD MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
最大保护
alter database set standby database to maximize protection;
7.6备库异常关闭
在最大保护模式下，如果备库和备库的监听没有启动，那么主库无法启动，而且如果备库异常关闭，那么主库会在几分钟之内被关闭（测试环境下五分钟）。
A-03113: end-of-file on communication channel
Error 3113 for archive log file 2 to 'shdb'
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
LGWR: All standby destinations have failed
******************************************************
WARNING: All standby database destinations have failed
WARNING: Instance shutdown required to protect primary
******************************************************
LGWR (ospid: 2622): terminating the instance due to error 16098
Instance terminated by LGWR, pid = 2622