ORACLE 11.2.0.4 dg搭建及對DDL的支援驗證
今天，有同事問ORACLE 11.2.0.4 dataguard是否對DDL支援；由於，我工作中oracle 11g的dataguard環境很少，所以對她的研究不是很多；因此，對於有疑問的知識，最好的辦法就是用實驗資料進行驗證了。
首先，先說明下實驗結論：ORACLE 11.2.0.4的dataguard對DDL是支援的。
伺服器環境：
主庫
[oracle@oradbs ~]$ uname -a
Linux oradbs 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
[oracle@oradbs ~]$
備庫
[oracle@oratest ~]$ uname -a
Linux oratest 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
[oracle@oratest ~]$
資料庫版本：
主庫
[oracle@oradbs ~]$ sqlplus -v
SQL*Plus: Release 11.2.0.4.0 Production
[oracle@oradbs ~]$
備庫
[oracle@oratest ~]$ sqlplus -v
SQL*Plus: Release 11.2.0.4.0 Production
[oracle@oratest ~]$
搭建好的11.2.0.4 oracle dataguard:
主庫：
[oracle@oradbs ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 1 09:07:11 2016
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
select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
ORADB PRIMARY
SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID =2;
STATUS GAP_STATUS
--------- ------------------------
VALID RESOLVABLE GAP
備庫：
[oracle@oratest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 1 08:17:36 2016
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 name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
ORADB PHYSICAL STANDBY
SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID =2;
STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP
SQL> COL NAME FOR A30
SQL> COL VALUE FOR A40
SQL> SET LINESIZE 1000
SQL> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS
NAME VALUE DATUM_TIME
------------------------------ ---------------------------------------- ------------------------------
transport lag +00 00:00:00 08/31/2016 22:52:56
apply lag +00 00:00:00 08/31/2016 22:52:56
apply finish time
estimated startup time 5
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
19 31-AUG-16 31-AUG-16 YES
oracle 11.2.0.4 dataguard對DDL支援測試：
驗證使用者建立語句：
主庫：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/arch
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
SQL> create user zhul identified by zhul;
User created.
SQL> alter system switch logfile;
System altered.
備庫：
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
19 31-AUG-16 31-AUG-16 YES
20 31-AUG-16 31-AUG-16 YES
21 31-AUG-16 31-AUG-16 YES
22 31-AUG-16 31-AUG-16 YES
SQL> select username from dba_users where username='ZHUL';
no rows selected
SQL> /
USERNAME
------------------------------
ZHUL
注意：主庫建立完使用者，備庫立即查詢是查不到的，原因是當前的DATAGUARD是最大效能模式（如下圖），需要主庫切換歸檔並且備庫應用完歸檔後，主庫的使用者建立才能查詢到。
驗證表空間建立語句：
主庫：
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oracle/oradata/oradb/users01.dbf
/home/oracle/oracle/oradata/oradb/undotbs01.dbf
/home/oracle/oracle/oradata/oradb/sysaux01.dbf
/home/oracle/oracle/oradata/oradb/system01.dbf
SQL> create tablespace test datafile '/home/oracle/oracle/oradata/oradb/test.dbf' size 100m;
Tablespace created.
SQL> alter system switch logfile;
System altered.
備庫：
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oracle/oradata/oradb/users01.dbf
/home/oracle/oracle/oradata/oradb/undotbs01.dbf
/home/oracle/oracle/oradata/oradb/sysaux01.dbf
/home/oracle/oracle/oradata/oradb/system01.dbf
SQL> /
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oracle/oradata/oradb/users01.dbf
/home/oracle/oracle/oradata/oradb/undotbs01.dbf
/home/oracle/oracle/oradata/oradb/sysaux01.dbf
/home/oracle/oracle/oradata/oradb/system01.dbf
/home/oracle/oracle/oradata/oradb/test.dbf
ORACLE 11.2.0.4 dg搭建及對DDL的支援驗證最終結論是：ORACLE 11.2.0.4 對DDL是支援的。
本次測試環境的搭建過程：
主備節點主機：RHEL5.5
資料庫版本：ORACLE 11.2.0.4
備庫建立方法：可以使用RMAN備份主庫到備庫主機進行恢復，也可以使用duplicate直接在主庫主機複製主庫到備機生成備庫，本次實驗採用duplicate複製主庫到備機生成備庫的方法
前提：在相同平臺作業系統下安裝相同版本oracle11.2.0.4的資料庫軟體，不建立資料庫
宣告：本次DATAGURAD的搭建，僅僅是對DDL支援的驗證實驗，資料庫部署環境及引數設定均不是合理的
調整主備機的監聽及TNS解析檔案
1、主庫
[oracle@oradbs admin]$ cat listener.ora
# listener.ora Network Configuration File: /home/oracle/oracle/product/11.2.0.3/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradbs)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=oradb)
(SID_NAME=oradb)
(ORACLE_HOME=/home/oracle/oracle/product/11.2.0.3/db)))
ADR_BASE_LISTENER = /home/oracle/oracle
[oracle@oradbs admin]$
[oracle@oradbs admin]$ cat tnsnames.ora
beiku =(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb)
)
)
zhuku = (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb)
)
)
[oracle@oradbs admin]$
[oracle@oradbs ~]$ cat .bash_profile
# .bash_profile
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/home/oracle/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.3/db
export ORACLE_SID=oradb
export NLS_LANG=
export PATH=$PATH:$ORACLE_HOME/bin
[oracle@oradbs ~]$
2、備庫
[oracle@oratest admin]$ cat listener.ora
# listener.ora Network Configuration File: /oradata/oracle/product/11.2.0.4/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oratest)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=oradb)
(SID_NAME=oradb)
(ORACLE_HOME=/oradata/oracle/product/11.2.0.4/db)))
ADR_BASE_LISTENER = /oradata/oracle
[oracle@oratest admin]$
[oracle@oratest admin]$ cat tnsnames.ora
beiku =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb)
)
)
zhuku =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb)
)
)
[oracle@oratest admin]$
[oracle@oratest ~]$ cat .bash_profile
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/oradata/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db
export ORACLE_SID=oradb
export ORACLE_UNQNAME=beiku
export NLS_LANG=
export PATH=$PATH:$ORACLE_HOME/bin
主庫的主要操作
1、主庫設定歸檔模式，啟動強日誌模式
SQL> select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG NO
SQL> alter database force logging;
Database altered.
SQL> select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG YES
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/oracle/product/11.2.0.3/db/dbs/arch
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
2、主庫修改歸檔路徑
SQL> alter system set log_archive_dest_1='LOCATION=/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb';
System altered.
SQL> alter system set log_archive_dest_2='SERVICE=beiku async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb';
System altered.
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=beiku async valid_for=
(online_logfile,primary_role)
db_unique_name=oradb
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=/oradata/arch VALID_F
OR=(ALL_LOGFILES,ALL_ROLES) DB
_UNIQUE_NAME=oradb
3、主庫新增dg相關日誌組
SQL>select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /home/oracle/oracle/oradata/oradb/redo03.log
2 ONLINE /home/oracle/oracle/oradata/oradb/redo02.log
1 ONLINE /home/oracle/oracle/oradata/oradb/redo01.log
3 rows selected.
SQL> alter database add standby logfile thread 1 group 4 ('/home/oracle/oracle/oradata/oradb/redo04.log') size 50M;
alter database add standby logfile thread 1 group 5 ('/home/oracle/oracle/oradata/oradb/redo05.log') size 50M;
alter database add standby logfile thread 1 group 6 ('/home/oracle/oracle/oradata/oradb/redo06.log') size 50M;
alter database add standby logfile thread 1 group 7 ('/home/oracle/oracle/oradata/oradb/redo07.log') size 50M;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /home/oracle/oracle/oradata/oradb/redo03.log
2 ONLINE /home/oracle/oracle/oradata/oradb/redo02.log
1 ONLINE /home/oracle/oracle/oradata/oradb/redo01.log
4 STANDBY /home/oracle/oracle/oradata/oradb/redo04.log
5 STANDBY /home/oracle/oracle/oradata/oradb/redo05.log
6 STANDBY /home/oracle/oracle/oradata/oradb/redo06.log
7 STANDBY /home/oracle/oracle/oradata/oradb/redo07.log
7 rows selected.
4、主庫修改歸檔最大程式數
SQL> show parameter log_archive_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 4
SQL> alter system set log_archive_max_processes=30;
System altered.
SQL> show parameter log_archive_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 30
5、主庫修改dg日誌控制引數
SQL> alter system set log_archive_config='dg_config=(oradb,beiku)';
System altered.
SQL> alter system set fal_server=beiku;
System altered.
SQL> alter system set fal_client=oradb;
System altered.
SQL> alter system set standby_file_management=auto;
System altered.
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(oradb,beiku)
6、主庫生成啟動備庫的pfile引數檔案
SQL> create pfile from spfile;
File created.
7、主庫生成資料庫的密碼檔案
[oracle@oradbs dbs]$ orapwd file=orapworadb password=oracle
8、傳送pfile和密碼檔案到備機
[oracle@oradbs dbs]$ scp orapworadb 192.168.56.3:/oradata/oracle/product/11.2.0.4/db/dbs/
oracle@192.168.56.3's password:
orapworadb 100% 1536 1.5KB/s 00:00
[oracle@oradbs dbs]$ cd ..
[oracle@oradbs ~]$ scp pfile.ora 192.168.56.3:/home/oracle/
oracle@192.168.56.3's password:
pfile.ora 100% 1182 1.2KB/s 00:00
[oracle@oradbs ~]$
9、確認主庫監聽啟動
[oracle@oradbs ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 01-SEP-2016 11:03:44
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oradbs)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 31-AUG-2016 23:01:26
Uptime 0 days 12 hr. 2 min. 18 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /home/oracle/oracle/diag/tnslsnr/oradbs/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradbs)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oradb" has 2 instance(s).
Instance "oradb", status UNKNOWN, has 1 handler(s) for this service...
Instance "oradb", status READY, has 1 handler(s) for this service...
Service "oradbXDB" has 1 instance(s).
Instance "oradb", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oradbs ~]$
備庫的主要操作
1、修改/home/oracle/pfile.ora檔案，
DB_UNIQUE_NAME=oradb
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=BEIKU
FAL_CLIENT=ORADB
STANDBY_FILE_MANAGEMENT=AUTO
2、備庫使用pfile啟動到nomount
[oracle@oratest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 31 22:32:46 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/pfile.ora';
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 683674664 bytes
Database Buffers 150994944 bytes
Redo Buffers 2355200 bytes
3、備庫監聽啟動
[oracle@oratest admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 31-AUG-2016 22:11:38
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /oradata/oracle/product/11.2.0.4/db/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /oradata/oracle/product/11.2.0.4/db/network/admin/listener.ora
Log messages written to /oradata/oracle/diag/tnslsnr/oratest/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oratest)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oratest)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 31-AUG-2016 22:11:38
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradata/oracle/product/11.2.0.4/db/network/admin/listener.ora
Listener Log File /oradata/oracle/diag/tnslsnr/oratest/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oratest)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oradb" has 1 instance(s).
Instance "oradb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
4、監聽及TNS解析服務可用性測試
[oracle@oratest admin]$ sqlplus sys/oracle@zhuku as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 31 22:12:39 2016
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> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oratest admin]$
[oracle@oratest ~]$ sqlplus sys/oracle@beiku as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 1 10:36:28 2016
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> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oratest admin]$
主庫duplicate備庫到備機
1、主庫使用rman連線到備庫例項
[oracle@oradbs admin]$ rman target sys/oracle@zhuku auxiliary sys/oracle@beiku
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 31 23:26:57 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB (DBID=2674606220)
connected to auxiliary database: ORADB (not mounted)
RMAN>
2、主庫執行duplicate
RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;
Starting Duplicate Db at 31-AUG-16
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 '/home/oracle/oracle/product/11.2.0.3/db/dbs/orapworadb' auxiliary format
'/oradata/oracle/product/11.2.0.4/db/dbs/orapworadb' ;
}
executing Memory Script
Starting backup at 31-AUG-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
Finished backup at 31-AUG-16
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/home/oracle/oracle/oradata/oradb/control01.ctl';
restore clone controlfile to '/home/oracle/oracle/oradata/oradb/control02.ctl' from
'/home/oracle/oracle/oradata/oradb/control01.ctl';
}
executing Memory Script
Starting backup at 31-AUG-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/home/oracle/oracle/product/11.2.0.3/db/dbs/snapcf_oradb.f tag=TAG20160831T232714 RECID=3 STAMP=921367635
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
Finished backup at 31-AUG-16
Starting restore at 31-AUG-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 31-AUG-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
"/home/oracle/oracle/oradata/oradb/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/home/oracle/oracle/oradata/oradb/system01.dbf";
set newname for datafile 2 to
"/home/oracle/oracle/oradata/oradb/sysaux01.dbf";
set newname for datafile 3 to
"/home/oracle/oracle/oradata/oradb/undotbs01.dbf";
set newname for datafile 4 to
"/home/oracle/oracle/oradata/oradb/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/home/oracle/oracle/oradata/oradb/system01.dbf" datafile
2 auxiliary format
"/home/oracle/oracle/oradata/oradb/sysaux01.dbf" datafile
3 auxiliary format
"/home/oracle/oracle/oradata/oradb/undotbs01.dbf" datafile
4 auxiliary format
"/home/oracle/oracle/oradata/oradb/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/oracle/oradata/oradb/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 31-AUG-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/oracle/oradata/oradb/system01.dbf
output file name=/home/oracle/oracle/oradata/oradb/system01.dbf tag=TAG20160831T232721
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/oracle/oradata/oradb/sysaux01.dbf
output file name=/home/oracle/oracle/oradata/oradb/sysaux01.dbf tag=TAG20160831T232721
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/oracle/oradata/oradb/undotbs01.dbf
output file name=/home/oracle/oracle/oradata/oradb/undotbs01.dbf tag=TAG20160831T232721
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=/home/oracle/oracle/oradata/oradb/users01.dbf
output file name=/home/oracle/oracle/oradata/oradb/users01.dbf tag=TAG20160831T232721
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 31-AUG-16
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/oradata/arch/1_19_921341455.dbf" auxiliary format
"/oradata/arch/1_19_921341455.dbf" ;
catalog clone archivelog "/oradata/arch/1_19_921341455.dbf";
switch clone datafile all;
}
executing Memory Script
Starting backup at 31-AUG-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=19 RECID=16 STAMP=921367665
output file name=/oradata/arch/1_19_921341455.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 31-AUG-16
cataloged archived log
archived log file name=/oradata/arch/1_19_921341455.dbf RECID=1 STAMP=921364551
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=921364551 file name=/home/oracle/oracle/oradata/oradb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=921364551 file name=/home/oracle/oracle/oradata/oradb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=921364551 file name=/home/oracle/oracle/oradata/oradb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=921364551 file name=/home/oracle/oracle/oradata/oradb/users01.dbf
contents of Memory Script:
{
set until scn 984934;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 31-AUG-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file /oradata/arch/1_19_921341455.dbf
archived log file name=/oradata/arch/1_19_921341455.dbf thread=1 sequence=19
media recovery complete, elapsed time: 00:00:00
Finished recover at 31-AUG-16
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/home/oracle/oracle/oradata/oradb/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: '/home/oracle/oracle/oradata/oradb/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: '/home/oracle/oracle/oradata/oradb/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 1: '/home/oracle/oracle/oradata/oradb/redo04.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 1: '/home/oracle/oracle/oradata/oradb/redo05.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 1: '/home/oracle/oracle/oradata/oradb/redo06.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 1: '/home/oracle/oracle/oradata/oradb/redo07.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
Finished Duplicate Db at 31-AUG-16
RMAN> quit
Recovery Manager complete.
備庫建立dg相關的日誌
SQL> alter database add standby logfile thread 1 group 12 ('/home/oracle/oracle/oradata/oradb/redo12.log') size 50M;
alter database add standby logfile thread 1 group 11 ('/home/oracle/oracle/oradata/oradb/redo11.log') size 50M;
alter database add standby logfile thread 1 group 10 ('/home/oracle/oracle/oradata/oradb/redo10.log') size 50M;
alter database add standby logfile thread 1 group 9 ('/home/oracle/oracle/oradata/oradb/redo09.log') size 50M;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
檢視是否有gap日誌中斷
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
19 31-AUG-16 31-AUG-16 YES
主庫切換日誌
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/arch
Oldest online log sequence 18
Next log sequence to archive 20
Current log sequence 20
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/arch
Oldest online log sequence 20
Next log sequence to archive 22
Current log sequence 22
備庫檢視日誌應用同步
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
19 31-AUG-16 31-AUG-16 YES
20 31-AUG-16 31-AUG-16 YES
21 31-AUG-16 31-AUG-16 YES
檢視主庫告警日誌，發現如下內容也說明dg搭建成功
Wed Aug 31 23:44:49 2016
ARC2: Archival destination is a Primary RAC instance: 'beiku'
Wed Aug 31 23:44:49 2016
ARCf: Archival destination is a Primary RAC instance: 'beiku'
Wed Aug 31 23:44:52 2016
Thread 1 advanced to log sequence 22 (LGWR switch)
Current log# 1 seq# 22 mem# 0: /home/oracle/oracle/oradata/oradb/redo01.log
Wed Aug 31 23:44:52 2016
Archived Log entry 18 added for thread 1 sequence 21 ID 0x9f6aea8c dest 1:
Wed Aug 31 23:44:52 2016
ARC4: Archival destination is a Primary RAC instance: 'beiku'
Wed Aug 31 23:44:52 2016
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Archival destination is a Primary RAC instance: 'beiku'
Wed Aug 31 23:48:38 2016
Thread 1 advanced to log sequence 23 (LGWR switch)
備庫檢視告警日誌有如下內容：
Media Recovery Waiting for thread 1 sequence 20
Completed: alter database recover managed standby database using current logfile disconnect from session
Wed Aug 31 22:52:54 2016
RFS[1]: Assigned to RFS process 11189
RFS[1]: Opened log for thread 1 sequence 20 dbid -1620361076 branch 921341455
Archived Log entry 2 added for thread 1 sequence 20 rlc 921341455 ID 0x9f6aea8c dest 2:
Wed Aug 31 22:52:54 2016
Media Recovery Log /oradata/arch/1_20_921341455.dbf
Media Recovery Waiting for thread 1 sequence 21
Wed Aug 31 22:52:57 2016
RFS[2]: Assigned to RFS process 11193
RFS[2]: Opened log for thread 1 sequence 21 dbid -1620361076 branch 921341455
Archived Log entry 3 added for thread 1 sequence 21 rlc 921341455 ID 0x9f6aea8c dest 2:
如果實驗中有不對的地方，敬請看客指正！
至此，ORACLE 11.2.0.4 DATAGUARD的實驗環境搭建完成！
首先，先說明下實驗結論：ORACLE 11.2.0.4的dataguard對DDL是支援的。
伺服器環境：
主庫
[oracle@oradbs ~]$ uname -a
Linux oradbs 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
[oracle@oradbs ~]$
備庫
[oracle@oratest ~]$ uname -a
Linux oratest 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
[oracle@oratest ~]$
資料庫版本：
主庫
[oracle@oradbs ~]$ sqlplus -v
SQL*Plus: Release 11.2.0.4.0 Production
[oracle@oradbs ~]$
備庫
[oracle@oratest ~]$ sqlplus -v
SQL*Plus: Release 11.2.0.4.0 Production
[oracle@oratest ~]$
搭建好的11.2.0.4 oracle dataguard:
主庫：
[oracle@oradbs ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 1 09:07:11 2016
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
select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
ORADB PRIMARY
SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID =2;
STATUS GAP_STATUS
--------- ------------------------
VALID RESOLVABLE GAP
備庫：
[oracle@oratest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 1 08:17:36 2016
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 name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
ORADB PHYSICAL STANDBY
SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID =2;
STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP
SQL> COL NAME FOR A30
SQL> COL VALUE FOR A40
SQL> SET LINESIZE 1000
SQL> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS
NAME VALUE DATUM_TIME
------------------------------ ---------------------------------------- ------------------------------
transport lag +00 00:00:00 08/31/2016 22:52:56
apply lag +00 00:00:00 08/31/2016 22:52:56
apply finish time
estimated startup time 5
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
19 31-AUG-16 31-AUG-16 YES
oracle 11.2.0.4 dataguard對DDL支援測試：
驗證使用者建立語句：
主庫：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/arch
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
SQL> create user zhul identified by zhul;
User created.
SQL> alter system switch logfile;
System altered.
備庫：
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
19 31-AUG-16 31-AUG-16 YES
20 31-AUG-16 31-AUG-16 YES
21 31-AUG-16 31-AUG-16 YES
22 31-AUG-16 31-AUG-16 YES
SQL> select username from dba_users where username='ZHUL';
no rows selected
SQL> /
USERNAME
------------------------------
ZHUL
注意：主庫建立完使用者，備庫立即查詢是查不到的，原因是當前的DATAGUARD是最大效能模式（如下圖），需要主庫切換歸檔並且備庫應用完歸檔後，主庫的使用者建立才能查詢到。
驗證表空間建立語句：
主庫：
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oracle/oradata/oradb/users01.dbf
/home/oracle/oracle/oradata/oradb/undotbs01.dbf
/home/oracle/oracle/oradata/oradb/sysaux01.dbf
/home/oracle/oracle/oradata/oradb/system01.dbf
SQL> create tablespace test datafile '/home/oracle/oracle/oradata/oradb/test.dbf' size 100m;
Tablespace created.
SQL> alter system switch logfile;
System altered.
備庫：
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oracle/oradata/oradb/users01.dbf
/home/oracle/oracle/oradata/oradb/undotbs01.dbf
/home/oracle/oracle/oradata/oradb/sysaux01.dbf
/home/oracle/oracle/oradata/oradb/system01.dbf
SQL> /
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oracle/oradata/oradb/users01.dbf
/home/oracle/oracle/oradata/oradb/undotbs01.dbf
/home/oracle/oracle/oradata/oradb/sysaux01.dbf
/home/oracle/oracle/oradata/oradb/system01.dbf
/home/oracle/oracle/oradata/oradb/test.dbf
ORACLE 11.2.0.4 dg搭建及對DDL的支援驗證最終結論是：ORACLE 11.2.0.4 對DDL是支援的。
本次測試環境的搭建過程：
主備節點主機：RHEL5.5
資料庫版本：ORACLE 11.2.0.4
備庫建立方法：可以使用RMAN備份主庫到備庫主機進行恢復，也可以使用duplicate直接在主庫主機複製主庫到備機生成備庫，本次實驗採用duplicate複製主庫到備機生成備庫的方法
前提：在相同平臺作業系統下安裝相同版本oracle11.2.0.4的資料庫軟體，不建立資料庫
宣告：本次DATAGURAD的搭建，僅僅是對DDL支援的驗證實驗，資料庫部署環境及引數設定均不是合理的
調整主備機的監聽及TNS解析檔案
1、主庫
[oracle@oradbs admin]$ cat listener.ora
# listener.ora Network Configuration File: /home/oracle/oracle/product/11.2.0.3/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradbs)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=oradb)
(SID_NAME=oradb)
(ORACLE_HOME=/home/oracle/oracle/product/11.2.0.3/db)))
ADR_BASE_LISTENER = /home/oracle/oracle
[oracle@oradbs admin]$
[oracle@oradbs admin]$ cat tnsnames.ora
beiku =(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb)
)
)
zhuku = (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb)
)
)
[oracle@oradbs admin]$
[oracle@oradbs ~]$ cat .bash_profile
# .bash_profile
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/home/oracle/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.3/db
export ORACLE_SID=oradb
export NLS_LANG=
export PATH=$PATH:$ORACLE_HOME/bin
[oracle@oradbs ~]$
2、備庫
[oracle@oratest admin]$ cat listener.ora
# listener.ora Network Configuration File: /oradata/oracle/product/11.2.0.4/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oratest)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=oradb)
(SID_NAME=oradb)
(ORACLE_HOME=/oradata/oracle/product/11.2.0.4/db)))
ADR_BASE_LISTENER = /oradata/oracle
[oracle@oratest admin]$
[oracle@oratest admin]$ cat tnsnames.ora
beiku =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb)
)
)
zhuku =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oradb)
)
)
[oracle@oratest admin]$
[oracle@oratest ~]$ cat .bash_profile
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/oradata/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db
export ORACLE_SID=oradb
export ORACLE_UNQNAME=beiku
export NLS_LANG=
export PATH=$PATH:$ORACLE_HOME/bin
主庫的主要操作
1、主庫設定歸檔模式，啟動強日誌模式
SQL> select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG NO
SQL> alter database force logging;
Database altered.
SQL> select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG YES
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/oracle/product/11.2.0.3/db/dbs/arch
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
2、主庫修改歸檔路徑
SQL> alter system set log_archive_dest_1='LOCATION=/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb';
System altered.
SQL> alter system set log_archive_dest_2='SERVICE=beiku async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb';
System altered.
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=beiku async valid_for=
(online_logfile,primary_role)
db_unique_name=oradb
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=/oradata/arch VALID_F
OR=(ALL_LOGFILES,ALL_ROLES) DB
_UNIQUE_NAME=oradb
3、主庫新增dg相關日誌組
SQL>select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /home/oracle/oracle/oradata/oradb/redo03.log
2 ONLINE /home/oracle/oracle/oradata/oradb/redo02.log
1 ONLINE /home/oracle/oracle/oradata/oradb/redo01.log
3 rows selected.
SQL> alter database add standby logfile thread 1 group 4 ('/home/oracle/oracle/oradata/oradb/redo04.log') size 50M;
alter database add standby logfile thread 1 group 5 ('/home/oracle/oracle/oradata/oradb/redo05.log') size 50M;
alter database add standby logfile thread 1 group 6 ('/home/oracle/oracle/oradata/oradb/redo06.log') size 50M;
alter database add standby logfile thread 1 group 7 ('/home/oracle/oracle/oradata/oradb/redo07.log') size 50M;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /home/oracle/oracle/oradata/oradb/redo03.log
2 ONLINE /home/oracle/oracle/oradata/oradb/redo02.log
1 ONLINE /home/oracle/oracle/oradata/oradb/redo01.log
4 STANDBY /home/oracle/oracle/oradata/oradb/redo04.log
5 STANDBY /home/oracle/oracle/oradata/oradb/redo05.log
6 STANDBY /home/oracle/oracle/oradata/oradb/redo06.log
7 STANDBY /home/oracle/oracle/oradata/oradb/redo07.log
7 rows selected.
4、主庫修改歸檔最大程式數
SQL> show parameter log_archive_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 4
SQL> alter system set log_archive_max_processes=30;
System altered.
SQL> show parameter log_archive_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 30
5、主庫修改dg日誌控制引數
SQL> alter system set log_archive_config='dg_config=(oradb,beiku)';
System altered.
SQL> alter system set fal_server=beiku;
System altered.
SQL> alter system set fal_client=oradb;
System altered.
SQL> alter system set standby_file_management=auto;
System altered.
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(oradb,beiku)
6、主庫生成啟動備庫的pfile引數檔案
SQL> create pfile from spfile;
File created.
7、主庫生成資料庫的密碼檔案
[oracle@oradbs dbs]$ orapwd file=orapworadb password=oracle
8、傳送pfile和密碼檔案到備機
[oracle@oradbs dbs]$ scp orapworadb 192.168.56.3:/oradata/oracle/product/11.2.0.4/db/dbs/
oracle@192.168.56.3's password:
orapworadb 100% 1536 1.5KB/s 00:00
[oracle@oradbs dbs]$ cd ..
[oracle@oradbs ~]$ scp pfile.ora 192.168.56.3:/home/oracle/
oracle@192.168.56.3's password:
pfile.ora 100% 1182 1.2KB/s 00:00
[oracle@oradbs ~]$
9、確認主庫監聽啟動
[oracle@oradbs ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 01-SEP-2016 11:03:44
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oradbs)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 31-AUG-2016 23:01:26
Uptime 0 days 12 hr. 2 min. 18 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /home/oracle/oracle/diag/tnslsnr/oradbs/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradbs)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oradb" has 2 instance(s).
Instance "oradb", status UNKNOWN, has 1 handler(s) for this service...
Instance "oradb", status READY, has 1 handler(s) for this service...
Service "oradbXDB" has 1 instance(s).
Instance "oradb", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oradbs ~]$
備庫的主要操作
1、修改/home/oracle/pfile.ora檔案，
DB_UNIQUE_NAME=oradb
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=BEIKU
FAL_CLIENT=ORADB
STANDBY_FILE_MANAGEMENT=AUTO
2、備庫使用pfile啟動到nomount
[oracle@oratest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 31 22:32:46 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/pfile.ora';
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 683674664 bytes
Database Buffers 150994944 bytes
Redo Buffers 2355200 bytes
3、備庫監聽啟動
[oracle@oratest admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 31-AUG-2016 22:11:38
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /oradata/oracle/product/11.2.0.4/db/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /oradata/oracle/product/11.2.0.4/db/network/admin/listener.ora
Log messages written to /oradata/oracle/diag/tnslsnr/oratest/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oratest)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oratest)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 31-AUG-2016 22:11:38
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradata/oracle/product/11.2.0.4/db/network/admin/listener.ora
Listener Log File /oradata/oracle/diag/tnslsnr/oratest/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oratest)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oradb" has 1 instance(s).
Instance "oradb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
4、監聽及TNS解析服務可用性測試
[oracle@oratest admin]$ sqlplus sys/oracle@zhuku as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 31 22:12:39 2016
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> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oratest admin]$
[oracle@oratest ~]$ sqlplus sys/oracle@beiku as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 1 10:36:28 2016
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> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oratest admin]$
主庫duplicate備庫到備機
1、主庫使用rman連線到備庫例項
[oracle@oradbs admin]$ rman target sys/oracle@zhuku auxiliary sys/oracle@beiku
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 31 23:26:57 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADB (DBID=2674606220)
connected to auxiliary database: ORADB (not mounted)
RMAN>
2、主庫執行duplicate
RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;
Starting Duplicate Db at 31-AUG-16
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 '/home/oracle/oracle/product/11.2.0.3/db/dbs/orapworadb' auxiliary format
'/oradata/oracle/product/11.2.0.4/db/dbs/orapworadb' ;
}
executing Memory Script
Starting backup at 31-AUG-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
Finished backup at 31-AUG-16
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/home/oracle/oracle/oradata/oradb/control01.ctl';
restore clone controlfile to '/home/oracle/oracle/oradata/oradb/control02.ctl' from
'/home/oracle/oracle/oradata/oradb/control01.ctl';
}
executing Memory Script
Starting backup at 31-AUG-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/home/oracle/oracle/product/11.2.0.3/db/dbs/snapcf_oradb.f tag=TAG20160831T232714 RECID=3 STAMP=921367635
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
Finished backup at 31-AUG-16
Starting restore at 31-AUG-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 31-AUG-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
"/home/oracle/oracle/oradata/oradb/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/home/oracle/oracle/oradata/oradb/system01.dbf";
set newname for datafile 2 to
"/home/oracle/oracle/oradata/oradb/sysaux01.dbf";
set newname for datafile 3 to
"/home/oracle/oracle/oradata/oradb/undotbs01.dbf";
set newname for datafile 4 to
"/home/oracle/oracle/oradata/oradb/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/home/oracle/oracle/oradata/oradb/system01.dbf" datafile
2 auxiliary format
"/home/oracle/oracle/oradata/oradb/sysaux01.dbf" datafile
3 auxiliary format
"/home/oracle/oracle/oradata/oradb/undotbs01.dbf" datafile
4 auxiliary format
"/home/oracle/oracle/oradata/oradb/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/oracle/oradata/oradb/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 31-AUG-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/oracle/oradata/oradb/system01.dbf
output file name=/home/oracle/oracle/oradata/oradb/system01.dbf tag=TAG20160831T232721
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/oracle/oradata/oradb/sysaux01.dbf
output file name=/home/oracle/oracle/oradata/oradb/sysaux01.dbf tag=TAG20160831T232721
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/oracle/oradata/oradb/undotbs01.dbf
output file name=/home/oracle/oracle/oradata/oradb/undotbs01.dbf tag=TAG20160831T232721
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=/home/oracle/oracle/oradata/oradb/users01.dbf
output file name=/home/oracle/oracle/oradata/oradb/users01.dbf tag=TAG20160831T232721
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 31-AUG-16
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/oradata/arch/1_19_921341455.dbf" auxiliary format
"/oradata/arch/1_19_921341455.dbf" ;
catalog clone archivelog "/oradata/arch/1_19_921341455.dbf";
switch clone datafile all;
}
executing Memory Script
Starting backup at 31-AUG-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=19 RECID=16 STAMP=921367665
output file name=/oradata/arch/1_19_921341455.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 31-AUG-16
cataloged archived log
archived log file name=/oradata/arch/1_19_921341455.dbf RECID=1 STAMP=921364551
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=921364551 file name=/home/oracle/oracle/oradata/oradb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=921364551 file name=/home/oracle/oracle/oradata/oradb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=921364551 file name=/home/oracle/oracle/oradata/oradb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=921364551 file name=/home/oracle/oracle/oradata/oradb/users01.dbf
contents of Memory Script:
{
set until scn 984934;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 31-AUG-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file /oradata/arch/1_19_921341455.dbf
archived log file name=/oradata/arch/1_19_921341455.dbf thread=1 sequence=19
media recovery complete, elapsed time: 00:00:00
Finished recover at 31-AUG-16
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/home/oracle/oracle/oradata/oradb/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: '/home/oracle/oracle/oradata/oradb/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: '/home/oracle/oracle/oradata/oradb/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 1: '/home/oracle/oracle/oradata/oradb/redo04.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 1: '/home/oracle/oracle/oradata/oradb/redo05.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 1: '/home/oracle/oracle/oradata/oradb/redo06.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 1: '/home/oracle/oracle/oradata/oradb/redo07.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
Finished Duplicate Db at 31-AUG-16
RMAN> quit
Recovery Manager complete.
備庫建立dg相關的日誌
SQL> alter database add standby logfile thread 1 group 12 ('/home/oracle/oracle/oradata/oradb/redo12.log') size 50M;
alter database add standby logfile thread 1 group 11 ('/home/oracle/oracle/oradata/oradb/redo11.log') size 50M;
alter database add standby logfile thread 1 group 10 ('/home/oracle/oracle/oradata/oradb/redo10.log') size 50M;
alter database add standby logfile thread 1 group 9 ('/home/oracle/oracle/oradata/oradb/redo09.log') size 50M;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.
備庫將資料庫置為只讀模式
SQL> alter database open read only;
Database altered.
備庫開啟同步SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
檢視是否有gap日誌中斷
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
19 31-AUG-16 31-AUG-16 YES
主庫切換日誌
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/arch
Oldest online log sequence 18
Next log sequence to archive 20
Current log sequence 20
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/arch
Oldest online log sequence 20
Next log sequence to archive 22
Current log sequence 22
備庫檢視日誌應用同步
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
19 31-AUG-16 31-AUG-16 YES
20 31-AUG-16 31-AUG-16 YES
21 31-AUG-16 31-AUG-16 YES
檢視主庫告警日誌，發現如下內容也說明dg搭建成功
Wed Aug 31 23:44:49 2016
ARC2: Archival destination is a Primary RAC instance: 'beiku'
Wed Aug 31 23:44:49 2016
ARCf: Archival destination is a Primary RAC instance: 'beiku'
Wed Aug 31 23:44:52 2016
Thread 1 advanced to log sequence 22 (LGWR switch)
Current log# 1 seq# 22 mem# 0: /home/oracle/oracle/oradata/oradb/redo01.log
Wed Aug 31 23:44:52 2016
Archived Log entry 18 added for thread 1 sequence 21 ID 0x9f6aea8c dest 1:
Wed Aug 31 23:44:52 2016
ARC4: Archival destination is a Primary RAC instance: 'beiku'
Wed Aug 31 23:44:52 2016
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Archival destination is a Primary RAC instance: 'beiku'
Wed Aug 31 23:48:38 2016
Thread 1 advanced to log sequence 23 (LGWR switch)
備庫檢視告警日誌有如下內容：
Media Recovery Waiting for thread 1 sequence 20
Completed: alter database recover managed standby database using current logfile disconnect from session
Wed Aug 31 22:52:54 2016
RFS[1]: Assigned to RFS process 11189
RFS[1]: Opened log for thread 1 sequence 20 dbid -1620361076 branch 921341455
Archived Log entry 2 added for thread 1 sequence 20 rlc 921341455 ID 0x9f6aea8c dest 2:
Wed Aug 31 22:52:54 2016
Media Recovery Log /oradata/arch/1_20_921341455.dbf
Media Recovery Waiting for thread 1 sequence 21
Wed Aug 31 22:52:57 2016
RFS[2]: Assigned to RFS process 11193
RFS[2]: Opened log for thread 1 sequence 21 dbid -1620361076 branch 921341455
Archived Log entry 3 added for thread 1 sequence 21 rlc 921341455 ID 0x9f6aea8c dest 2:
如果實驗中有不對的地方，敬請看客指正！
至此，ORACLE 11.2.0.4 DATAGUARD的實驗環境搭建完成！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29357786/viewspace-2124329/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle DG支援搭建的平臺列表Oracle
- ORACLE 11.2.0.4 DG(Broker) for linux 部署OracleLinux
- 驗證碼前端對各瀏覽器的支援前端瀏覽器
- Oracle RAC+DG搭建Oracle
- 關於Oracle12C rac DDL日誌的測試驗證Oracle
- Oracle DG搭建1(duplicate方式)Oracle
- ORACLE DG 11G 搭建Oracle
- Oracle DG異構主備支援Oracle
- 驗證DG最大效能模式下使用ARCH/LGWR及STANDBY LOG的不同情況模式
- 驗證碼原理及驗證
- Oracle DG搭建2(冷備方式)Oracle
- 【DG】搭建（二）及相關測試
- 【DG】Oracle 19c使用dbca來搭建物理DGOracle
- oracle 11gR2 對CRS dg做映象dgOracle
- DG搭建
- oracle11g 搭建 rac+dgOracle
- oracle 11g dg搭建筆記Oracle筆記
- 搭建oracle 11.2.0.4環境下的goldengate複製OracleGo
- oracle---oracle的單向ogg搭建流程（DML+DDL）Oracle
- OEL6.3 64位部署ORACLE 11gR2(11.2.0.4) RAC+DG（9）建立DGOracle
- Oracle的身份驗證Oracle
- Oracle的驗證方式Oracle
- ORACLE 11gR2 11.2.0.4 一步一步 物理DGOracle
- oracle goldengate 配置DML&DDL實驗OracleGo
- 【DG】搭建（一）
- iOS 對 HTTPS 證書鏈的驗證iOSHTTP
- 物理DG、邏輯DG和快照DG的搭建（視訊講解）
- Oracle 11g單主搭建物理DGOracle
- linux-oracle11g-dg搭建日誌LinuxOracle
- Oracle DG（Data Guard）支援異構平臺說明Oracle
- 讓你的 validate 支援場景驗證
- 配置GoldenGate啟動DDL支援同步DDL操作Go
- Oracle RAC中驗證LUN_ID對應情況Oracle
- iOS 中對 HTTPS 證書鏈的驗證iOSHTTP
- DG搭建配置方案
- Oracle：DG 的 switchoverOracle
- ORACLE RAC TO RAC DG搭建過程中可能遇到的問題Oracle
- Oracle 11.2.0.4的安裝Oracle