Oracle 11.2.0.3 linux x86_64 使用sqlplus 搭建 dataguard
DB version: 11.2.0.3
OS version: oel 6.8 x86_64
192.168.56.102 nodea
db_name=PD1
db_unique_name=PD1DG1
192.168.56.103 nodeb
db_name=PD1
db_unique_name=PD1DG2
这两台机器安装好 oracle RDBMS software，
在nodea创建PD1数据库及监听，开启归档模式
在nodeb上创建好监听
/*******************************************************************************
*******************************************************************************/
nodea 修改 listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PD1DG1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = PD1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = nodea)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
nodea 修改 tnsnames.ora
TNS_PD1DG1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PD1DG1)
)
)
TNS_PD1DG2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.103)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PD1DG2)
)
)
TNS_PD1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))
)
nodea 修改参数，创建pfile
alter database force logging;
alter system set db_name='PD1' scope=spfile;
alter system set db_unique_name='PD1DG1' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(PD1DG1,PD1DG2)';
alter system set log_archive_dest_1='LOCATION=/u04/archivelog/PD1/ valid_for=(online_logfiles,primary_role) db_unique_name=PD1DG1' scope=spfile;
alter system set log_archive_dest_2='SERVICE=TNS_PD1DG2 lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=PD1DG2' scope=spfile;
alter system set log_archive_dest_3='LOCATION=/u04/standbylog/PD1/ valid_for=(standby_logfile,standby_role) db_unique_name=PD1DG1' scope=spfile;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;
alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=spfile;
alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile;
alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
alter system set fal_client='TNS_PD1DG1' scope=spfile;
alter system set fal_server='TNS_PD1DG2' scope=spfile;
alter system set local_listener='TNS_PD1' scope=spfile;
alter system set standby_file_management=auto scope=spfile;
alter database add standby logfile group 4 '/u01/app/oracle/oradata/PD1/standby04.log' size 50M;
alter database add standby logfile group 5 '/u01/app/oracle/oradata/PD1/standby05.log' size 50M;
alter database add standby logfile group 6 '/u01/app/oracle/oradata/PD1/standby06.log' size 50M;
alter database add standby logfile group 7 '/u01/app/oracle/oradata/PD1/standby07.log' size 50M;
alter system set log_file_name_convert='/u01/app/oracle/oradata/PD1/','/u01/app/oracle/oradata/PD1/' scope =spfile;
#这两个都需要添加，即使目录一致，也需要添加
#后面的实验证明11.2.0.3版本 log_file_name_convert 必须添加
*.db_file_name_convert='/u03/oradata/PD1/','/u03/oradata/PD1/'
shutdown immediate
startup
create pfile='/u01/initPD1.ora' from spfile;
nodea 创建密码文件
orapwd file=$ORACLE_HOME/dbs/orapwPD1 password=oracle entries=5 ignorecase=y force=y
/*******************************************************************************
*******************************************************************************/
nodea 的 initPD1.ora、orapwPD1 复制到 nodeb 的 $ORACLE_HOME/dbs 目录下
修改 initPD1.ora 的内容，符合 standby
[oracle@nodeb dbs]$ ls -l
total 12
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r--r-- 1 oracle oinstall 1464 Apr 17 19:21 initPD1.ora
-rw-r----- 1 oracle oinstall 2048 Apr 17 19:21 orapwPD1
[oracle@nodeb dbs]$ vi initPD1.ora
*.archive_lag_target=60
*.audit_file_dest='/u01/app/oracle/admin/PD1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/PD1/control01.ctl','/u01/app/oracle/oradata/PD1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PD1'
*.db_unique_name='PD1DG2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PD1XDB)'
*.fal_client='TNS_PD1DG2'
*.fal_server='TNS_PD1DG1'
*.local_listener='TNS_PD1'
*.log_archive_config='DG_CONFIG=(PD1DG1,PD1DG2)'
*.log_archive_dest_1='LOCATION=/u04/archivelog/PD1/ valid_for=(all_logfiles,primary_role) db_unique_name=PD1DG2'
*.log_archive_dest_2='SERVICE=TNS_PD1DG1 lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=PD1DG1'
*.log_archive_dest_3='LOCATION=/u04/standbylog/PD1/ valid_for=(standby_logfile,standby_role) db_unique_name=PD1DG2'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=836763648
*.open_cursors=1000
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=335
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@nodeb dbs]$ mkdir -p /u01/app/oracle/admin/PD1/adump
[oracle@nodeb dbs]$ mkdir -p /u01/app/oracle/oradata/PD1/
nodea 的 listener.ora、tnsnames.ora 复制到 nodeb 的 $ORACLE_HOME/network/admin目录下
修改对应的内容，符合 standby
listener.ora 必须修改为静态注册，否则RMAN会报错
[oracle@nodeb 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.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PD1DG2)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = PD1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
/*******************************************************************************
*******************************************************************************/
在备库nodeb启动到nomount状态，然后连接主库进行duplicate操作
[oracle@nodeb dbs]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 17 19:47:39 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba;
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initPD1.ora';
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 490737024 bytes
Database Buffers 339738624 bytes
Redo Buffers 2396160 bytes
File created.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount ;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 490737024 bytes
Database Buffers 339738624 bytes
Redo Buffers 2396160 bytes
Connection to Auxiliary using connect string failed with ORA-12528 (文档 ID 419440.1)
[oracle@nodeb dbs]$ rman target sys/oracle@TNS_PD1DG1 auxiliary sys/oracle@TNS_PD1DG2
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Apr 17 21:12:01 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PD1 (DBID=3861488697)
connected to auxiliary database: PD1 (not mounted)
RMAN> duplicate target database for standby nofilenamecheck from active database;
Starting Duplicate Db at 17-APR-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwPD1' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwPD1' ;
}
executing Memory Script
Starting backup at 17-APR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
Finished backup at 17-APR-17
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/PD1/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/PD1/control02.ctl' from
'/u01/app/oracle/oradata/PD1/control01.ctl';
}
executing Memory Script
Starting backup at 17-APR-17
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/db_1/dbs/snapcf_PD1.f tag=TAG20170417T211213 RECID=5 STAMP=941577133
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 17-APR-17
Starting restore at 17-APR-17
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 17-APR-17
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
"/u02/oradata/PD1/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u02/oradata/PD1/system01.dbf";
set newname for datafile 2 to
"/u02/oradata/PD1/sysaux01.dbf";
set newname for datafile 3 to
"/u02/oradata/PD1/undotbs01.dbf";
set newname for datafile 4 to
"/u02/oradata/PD1/users01.dbf";
set newname for datafile 5 to
"/u02/oradata/PD1/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u02/oradata/PD1/system01.dbf" datafile
2 auxiliary format
"/u02/oradata/PD1/sysaux01.dbf" datafile
3 auxiliary format
"/u02/oradata/PD1/undotbs01.dbf" datafile
4 auxiliary format
"/u02/oradata/PD1/users01.dbf" datafile
5 auxiliary format
"/u02/oradata/PD1/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u02/oradata/PD1/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 17-APR-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u02/oradata/PD1/system01.dbf
output file name=/u02/oradata/PD1/system01.dbf tag=TAG20170417T211222
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u02/oradata/PD1/sysaux01.dbf
output file name=/u02/oradata/PD1/sysaux01.dbf tag=TAG20170417T211222
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u02/oradata/PD1/example01.dbf
output file name=/u02/oradata/PD1/example01.dbf tag=TAG20170417T211222
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u02/oradata/PD1/undotbs01.dbf
output file name=/u02/oradata/PD1/undotbs01.dbf tag=TAG20170417T211222
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u02/oradata/PD1/users01.dbf
output file name=/u02/oradata/PD1/users01.dbf tag=TAG20170417T211222
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 17-APR-17
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=5 STAMP=941577217 file name=/u02/oradata/PD1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=941577218 file name=/u02/oradata/PD1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=941577218 file name=/u02/oradata/PD1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=941577218 file name=/u02/oradata/PD1/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=941577218 file name=/u02/oradata/PD1/example01.dbf
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PD1/redo01.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PD1/redo02.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PD1/redo03.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 4 thread 0: '/u01/app/oracle/oradata/PD1/standby04.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 5 thread 0: '/u01/app/oracle/oradata/PD1/standby05.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 6 thread 0: '/u01/app/oracle/oradata/PD1/standby06.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 7 thread 0: '/u01/app/oracle/oradata/PD1/standby07.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
Finished Duplicate Db at 17-APR-17
报这个错误
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 7 thread 0: '/u01/app/oracle/oradata/PD1/standby07.log'
该告警的处理参考文章：https://blogs.oracle.com/Database4CN/entry/11g_%E6%96%B0%E7%89%B9%E6%80%A7_active_database_duplication1
解决方法：
在主备数据库设置LOG_FILE_NAME_CONVERT参数，即使参数的两个值相同也需要设置。例如：
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/PD1/','/u01/app/oracle/oradata/PD1/'，
该参数需要重启实例才能生效，之后清除备用数据库，
在备库重新执行duplicate操作即可。
/*******************************************************************************
*******************************************************************************/
将备库置于active dataguard模式下,duplicate for standby 完成后，备库处于 mount 状态，需要手动操作进行恢复操作。
[oracle@nodeb ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 17 21:26:05 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
MOUNTED PHYSICAL STANDBY PD1DG2
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
MOUNTED PHYSICAL STANDBY PD1DG2
SQL> select status from v$standby_log;
STATUS
----------
ACTIVE
UNASSIGNED
UNASSIGNED
UNASSIGNED
SQL> select member from v$logfile;
MEMBER --------------------------------------------------------------------------------
/u01/app/oracle/oradata/PD1/redo03.log
/u01/app/oracle/oradata/PD1/redo02.log
/u01/app/oracle/oradata/PD1/redo01.log
/u01/app/oracle/oradata/PD1/standby04.log
/u01/app/oracle/oradata/PD1/standby05.log
/u01/app/oracle/oradata/PD1/standby06.log
/u01/app/oracle/oradata/PD1/standby07.log
7 rows selected.
修改dataguard的数据保护模式为最高可用性模式，根据oracle文档的解释，最高可用性数据保护模式需要先满足以下几个条件
SQL> select db_unique_name,protection_mode,protection_level from v$database;
DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
------------------------------ -------------------- --------------------
PD1DG2 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
SQL> select db_unique_name,protection_mode,protection_level from v$database;
DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
------------------------------ -------------------- --------------------
PD1DG1 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
SQL> alter database set standby database to maximize availability;
Database altered.
SQL> select db_unique_name,protection_mode,protection_level from v$database;
DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
------------------------------ -------------------- --------------------
PD1DG1 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL> select db_unique_name,protection_mode,protection_level from v$database;
DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
------------------------------ -------------------- --------------------
PD1DG2 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
将备库shutdown后，主库的PROTECTION_LEVEL将变为RESYNCHRONIZATION
SQL> select db_unique_name,protection_mode,protection_level from v$database;
DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
------------------------------ -------------------- --------------------
PD1DG2 MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> select db_unique_name,protection_mode,protection_level from v$database;
DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
------------------------------ -------------------- --------------------
PD1DG1 MAXIMUM AVAILABILITY RESYNCHRONIZATION
