如何理解Oracle 12c CDB的ADG
这篇文章将为大家详细讲解有关如何理解Oracle 12c CDB的ADG，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。
实验环境
OS：oracle linux 6.6
DB：oracle 12.1.0.1
主库：chicago, pdb1, pdb2
备库：boston, pdb1, pdb2
主库操作
0. 确认tnsping可以在主库与备库之间相符访问。如果能ping通，但是tnsping不通很有可能是防火墙没有关闭
[root@snow~]#serviceiptablesstop
1. 主库数据库文件、控制文件、日志文件的规划
[oracle@snow~]$sqlplus/assysdba
SQL*Plus:Release12.1.0.1.0ProductiononFriMay117:47:282015
Copyright(c)1982,2013,Oracle. Allrightsreserved.
Connectedto:
OracleDatabase12cEnterpriseEditionRelease12.1.0.1.0-64bitProduction
WiththePartitioning,OLAP,AdvancedAnalyticsandRealApplicationTestingoptions
SYS@chicago>showpdbs;
CON_IDCON_NAME OPENMODE RESTRICTED
------------------------------------------------------------
2PDB$SEED READONLY NO
3PDB1 READWRITENO
SYS@chicago>
selectnamefromv$datafile
union
selectnamefromv$controlfile
union
selectmemberfromv$logfile
union
selectnamefromv$tempfile
union
selectvaluefromv$parameterwherename='spfile';
NAME
--------------------------------------------------------------------------------
/home/oracle/dbfile/chicago/control01.ctl
/home/oracle/dbfile/chicago/control02.ctl
/home/oracle/dbfile/chicago/pdb1/SAMPLE_SCHEMA_users01.dbf
/home/oracle/dbfile/chicago/pdb1/example01.dbf
/home/oracle/dbfile/chicago/pdb1/pdb1_temp01.dbf
/home/oracle/dbfile/chicago/pdb1/sysaux01.dbf
/home/oracle/dbfile/chicago/pdb1/system01.dbf
/home/oracle/dbfile/chicago/pdbseed/pdbseed_temp01.dbf
/home/oracle/dbfile/chicago/pdbseed/sysaux01.dbf
/home/oracle/dbfile/chicago/pdbseed/system01.dbf
/home/oracle/dbfile/chicago/redo01.log
NAME
--------------------------------------------------------------------------------
/home/oracle/dbfile/chicago/redo02.log
/home/oracle/dbfile/chicago/redo03.log
/home/oracle/dbfile/chicago/sysaux01.dbf
/home/oracle/dbfile/chicago/system01.dbf
/home/oracle/dbfile/chicago/temp01.dbf
/home/oracle/dbfile/chicago/undotbs01.dbf
/home/oracle/dbfile/chicago/users01.dbf
/u01/app/oracle/product/12.1.0.1/db_1/dbs/spfilechicago.ora
19rowsselected.
2. 主库现有3组redo，为此创建4组standby logfile group
SYS@chicago>selectgroup#,bytes/1024/1024fromv$log;
GROUP#BYTES/1024/1024
-------------------------
1 50
2 50
3 50
SYS@chicago>
alter database add standby logfile group 4 ('/home/oracle/dbfile/chicago/redo04.log') size 50m;
alter database add standby logfile group 5 ('/home/oracle/dbfile/chicago/redo05.log') size 50m;
alter database add standby logfile group 6 ('/home/oracle/dbfile/chicago/redo06.log') size 50m;
alter database add standby logfile group 7 ('/home/oracle/dbfile/chicago/redo07.log') size 50m;
3. 主库设置foece logging
SYS@chicago>alterdatabaseforcelogging;
SYS@chicago>shutdownimmediate;
4. 主库创建pfile
SYS@chicago>createpfilefromspfile;
SYS@chicago > exit
5. 向主库的pfile添加内容
[oracle@snow ~]$ cd $ORACLE_HOME/dbs
[oracle@snow dbs]$ vi initchicago.ora
*.audit_file_dest='/u01/app/oracle/admin/chicago/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='/home/oracle/dbfile/chicago/control01.ctl','/home/oracle/dbfile/chicago/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='chicago'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=chicagoXDB)'
*.log_archive_dest_1='LOCATION=/home/oracle/arch'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=1440m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#for ADG
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/home/oracle/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
'SERVICE=boston ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=boston
FAL_CLIENT=chicago
DB_FILE_NAME_CONVERT='boston','chicago'
LOG_FILE_NAME_CONVERT='boston','chicago'
STANDBY_FILE_MANAGEMENT=AUTO
6. 复制主库的pfile文件，修改为备库的pfile
[oracle@snow dbs]$ cp initchicago.ora initboston.ora
[oracle@snow dbs]$ vi initboston.ora
*.audit_file_dest='/u01/app/oracle/admin/boston/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='/home/oracle/dbfile/boston/control01.ctl','/home/oracle/dbfile/boston/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='chicago'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=chicagoXDB)'
*.log_archive_dest_1='LOCATION=/home/oracle/arch'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=1440m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#for ADG
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG='DG_CONFIG=(boston,chicago)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/home/oracle/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
'SERVICE=boston ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=chicago
FAL_CLIENT=boston
DB_FILE_NAME_CONVERT='chicago','boston'
LOG_FILE_NAME_CONVERT='chicago','boston'
STANDBY_FILE_MANAGEMENT=AUTO
7. 将备库的pfile文件scp到备库的/u01/app/oracle/product/12.1.0/db_1/dbs
[oracle@snow dbs]$ scp initboston.ora boston:/u01/app/oracle/product/12.1.0/db_1/dbs
oracle@boston's password:
initboston.ora 100% 1141 1.1KB/s 00:00
8. 复制主库的密码文件，修改为备库的密码文件并scp到备库的/u01/app/oracle/product/12.1.0/db_1/dbs
[oracle@snow dbs]$ cp orapwchicago orapwboston
[oracle@snow dbs]$ scp orapwboston boston:/u01/app/oracle/product/12.1.0/db_1/dbs
oracle@boston's password:
orapwboston 100% 7680 7.5KB/s 00:00
9. 修改主库的$ORACLE_HOME/network/admin/tnsnames.ora，手工添加（蓝色）备库的boston部分。其中CHICAGO为CDB的链接字符串，PDB1为PDB的链接字符串。
[oracle@snow dbs]$ cd $ORACLE_HOME/network/admin
[oracle@snow admin]$ vi tnsnames.ora
BOSTON=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.228.13)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=boston)
)
)
CHICAGO=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=snow.oracle.com)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=chicago)
)
)
PDB1=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.228.12)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=pdb1)
)
)
备库操作
10. 创建备库所需目录
和主库一样确认防火墙已经关闭
[root@boston~]#serviceiptablesstop
[oracle@boston ~]$ export ORACLE_SID=boston
[oracle@boston ~]$ mkdir -p $ORACLE_BASE/admin/boston/adump
[oracle@boston ~]$ mkdir -p dbfile/boston/pdbseed
[oracle@boston ~]$ mkdir -p dbfile/boston/pdb1
注意：如果只创建了dbfile/boston目录，没有创建pdbseed和pdb1目录的话再后面的duplicate操作会失败。
ORA-19504:failedtocreatefile"/home/oracle/dbfile/boston/pdbseed/system01.dbf"
11. 为备库创建静态监听，此步骤可以使用netmgr
[oracle@boston~]$vi$ORACLE_HOME/network/admin/listener.ora
#listener.oraNetworkConfigurationFile:/u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
#GeneratedbyOracleconfigurationtools.
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=boston)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1)
(SID_NAME=boston)
)
)
LISTENER=
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=boston)(PORT=1521))
)
(DESCRIPTION=
(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))
)
)
ADR_BASE_LISTENER=/u01/app/oracle
12. 修改备库tnsnames.ora，添加主库的CHICAGO(蓝色)
[oracle@boston ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
BOSTON=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.228.13)(PORT=1521))
)
(CONNECT_DATA=
(SERVICE_NAME=boston)
)
)
CHICAGO=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.228.12)(PORT=1521))
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=chicago)
)
)
13. 备库reload监听器
[oracle@boston ~]$ lsnrctl
LSNRCTL> reload
主库操作
14. 主库从修改后的pfile创建spfile，启动实例。此时关于ADG的配置信息将随之实例的启动生效。
[oracle@snow~]$sqlplussys/oracle@chicagoassysdba
SQL*Plus:Release12.1.0.1.0ProductiononFriMay118:13:452015
Copyright(c)1982,2013,Oracle. Allrightsreserved.
Connectedtoanidleinstance.
SYS@chicago>createspfilefrompfile;
Filecreated.
SYS@chicago>startup
备库操作
15. 备库从修改后的pfile创建spfile，启动实例到nomount状态。
[oracle@boston dbs]$ sqlplus sys/oracle@boston as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed May 6 13:03:06 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@boston > create spfile from pfile;
File created.
SYS@boston > startup nomount;
ORACLE instance started.
Total System Global Area 459304960 bytes
Fixed Size 2289544 bytes
Variable Size 381681784 bytes
Database Buffers 71303168 bytes
Redo Buffers 4030464 bytes
SYS@boston > exit
16. 使用rman duplicate来在线复制主库。rman部分输出比较多，命名语句使用高亮表标示，其它字体为屏幕输出。
[oracle@boston~]$rmantargetsys/oracle@chicagoauxiliarysys/oracle@boston
RecoveryManager:Release12.1.0.1.0-ProductiononMonMay2514:27:162015
Copyright(c)1982,2013,Oracleand/oritsaffiliates. Allrightsreserved.
connectedtotargetdatabase:CHICAGO(DBID=1488305431)
connectedtoauxiliarydatabase:CHICAGO(notmounted)
RMAN>duplicatetargetdatabaseforstandbyfromactivedatabasedorecovernofilenamecheck;
StartingDuplicateDbat25-MAY-15
usingtargetdatabasecontrolfileinsteadofrecoverycatalog
allocatedchannel:ORA_AUX_DISK_1
channelORA_AUX_DISK_1:SID=20devicetype=DISK
currentlogarchived
contentsofMemoryScript:
{
backupascopyreuse
targetfile '/u01/app/oracle/product/12.1.0.1/db_1/dbs/orapwchicago'auxiliaryformat
'/u01/app/oracle/product/12.1.0/db_1/dbs/orapwboston' ;
}
executingMemoryScript
Startingbackupat25-MAY-15
allocatedchannel:ORA_DISK_1
channelORA_DISK_1:SID=265devicetype=DISK
Finishedbackupat25-MAY-15
contentsofMemoryScript:
{
restoreclonefromservice 'chicago'standbycontrolfile;
}
executingMemoryScript
Startingrestoreat25-MAY-15
usingchannelORA_AUX_DISK_1
channelORA_AUX_DISK_1:startingdatafilebackupsetrestore
channelORA_AUX_DISK_1:usingnetworkbackupsetfromservicechicago
channelORA_AUX_DISK_1:restoringcontrolfile
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:01
outputfilename=/home/oracle/dbfile/boston/control01.ctl
outputfilename=/home/oracle/dbfile/boston/control02.ctl
Finishedrestoreat25-MAY-15
contentsofMemoryScript:
{
sqlclone'alterdatabasemountstandbydatabase';
}
executingMemoryScript
sqlstatement:alterdatabasemountstandbydatabase
contentsofMemoryScript:
{
setnewnamefortempfile 1to
"/home/oracle/dbfile/boston/temp01.dbf";
setnewnamefortempfile 2to
"/home/oracle/dbfile/boston/pdbseed/pdbseed_temp01.dbf";
setnewnamefortempfile 3to
"/home/oracle/dbfile/boston/pdb1/pdb1_temp01.dbf";
switchclonetempfileall;
setnewnamefordatafile 1to
"/home/oracle/dbfile/boston/system01.dbf";
setnewnamefordatafile 3to
"/home/oracle/dbfile/boston/sysaux01.dbf";
setnewnamefordatafile 4to
"/home/oracle/dbfile/boston/undotbs01.dbf";
setnewnamefordatafile 5to
"/home/oracle/dbfile/boston/pdbseed/system01.dbf";
setnewnamefordatafile 6to
"/home/oracle/dbfile/boston/users01.dbf";
setnewnamefordatafile 7to
"/home/oracle/dbfile/boston/pdbseed/sysaux01.dbf";
setnewnamefordatafile 8to
"/home/oracle/dbfile/boston/pdb1/system01.dbf";
setnewnamefordatafile 9to
"/home/oracle/dbfile/boston/pdb1/sysaux01.dbf";
setnewnamefordatafile 10to
"/home/oracle/dbfile/boston/pdb1/SAMPLE_SCHEMA_users01.dbf";
setnewnamefordatafile 11to
"/home/oracle/dbfile/boston/pdb1/example01.dbf";
restore
fromservice 'chicago' clonedatabase
;
sql'altersystemarchivelogcurrent';
}
executingMemoryScript
executingcommand:SETNEWNAME
executingcommand:SETNEWNAME
executingcommand:SETNEWNAME
renamedtempfile1to/home/oracle/dbfile/boston/temp01.dbfincontrolfile
renamedtempfile2to/home/oracle/dbfile/boston/pdbseed/pdbseed_temp01.dbfincontrolfile
renamedtempfile3to/home/oracle/dbfile/boston/pdb1/pdb1_temp01.dbfincontrolfile
executingcommand:SETNEWNAME
executingcommand:SETNEWNAME
executingcommand:SETNEWNAME
executingcommand:SETNEWNAME
executingcommand:SETNEWNAME
executingcommand:SETNEWNAME
executingcommand:SETNEWNAME
executingcommand:SETNEWNAME
executingcommand:SETNEWNAME
executingcommand:SETNEWNAME
Startingrestoreat25-MAY-15
usingchannelORA_AUX_DISK_1
channelORA_AUX_DISK_1:startingdatafilebackupsetrestore
channelORA_AUX_DISK_1:usingnetworkbackupsetfromservicechicago
channelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupset
channelORA_AUX_DISK_1:restoringdatafile00001to/home/oracle/dbfile/boston/system01.dbf
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:07
channelORA_AUX_DISK_1:startingdatafilebackupsetrestore
channelORA_AUX_DISK_1:usingnetworkbackupsetfromservicechicago
channelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupset
channelORA_AUX_DISK_1:restoringdatafile00003to/home/oracle/dbfile/boston/sysaux01.dbf
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:07
channelORA_AUX_DISK_1:startingdatafilebackupsetrestore
channelORA_AUX_DISK_1:usingnetworkbackupsetfromservicechicago
channelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupset
channelORA_AUX_DISK_1:restoringdatafile00004to/home/oracle/dbfile/boston/undotbs01.dbf
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:03
channelORA_AUX_DISK_1:startingdatafilebackupsetrestore
channelORA_AUX_DISK_1:usingnetworkbackupsetfromservicechicago
channelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupset
channelORA_AUX_DISK_1:restoringdatafile00005to/home/oracle/dbfile/boston/pdbseed/system01.dbf
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:03
channelORA_AUX_DISK_1:startingdatafilebackupsetrestore
channelORA_AUX_DISK_1:usingnetworkbackupsetfromservicechicago
channelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupset
channelORA_AUX_DISK_1:restoringdatafile00006to/home/oracle/dbfile/boston/users01.dbf
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:01
channelORA_AUX_DISK_1:startingdatafilebackupsetrestore
channelORA_AUX_DISK_1:usingnetworkbackupsetfromservicechicago
channelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupset
channelORA_AUX_DISK_1:restoringdatafile00007to/home/oracle/dbfile/boston/pdbseed/sysaux01.dbf
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:07
channelORA_AUX_DISK_1:startingdatafilebackupsetrestore
channelORA_AUX_DISK_1:usingnetworkbackupsetfromservicechicago
channelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupset
channelORA_AUX_DISK_1:restoringdatafile00008to/home/oracle/dbfile/boston/pdb1/system01.dbf
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:03
channelORA_AUX_DISK_1:startingdatafilebackupsetrestore
channelORA_AUX_DISK_1:usingnetworkbackupsetfromservicechicago
channelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupset
channelORA_AUX_DISK_1:restoringdatafile00009to/home/oracle/dbfile/boston/pdb1/sysaux01.dbf
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:07
channelORA_AUX_DISK_1:startingdatafilebackupsetrestore
channelORA_AUX_DISK_1:usingnetworkbackupsetfromservicechicago
channelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupset
channelORA_AUX_DISK_1:restoringdatafile00010to/home/oracle/dbfile/boston/pdb1/SAMPLE_SCHEMA_users01.dbf
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:01
channelORA_AUX_DISK_1:startingdatafilebackupsetrestore
channelORA_AUX_DISK_1:usingnetworkbackupsetfromservicechicago
channelORA_AUX_DISK_1:specifyingdatafile(s)torestorefrombackupset
channelORA_AUX_DISK_1:restoringdatafile00011to/home/oracle/dbfile/boston/pdb1/example01.dbf
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:03
Finishedrestoreat25-MAY-15
sqlstatement:altersystemarchivelogcurrent
currentlogarchived
contentsofMemoryScript:
{
restorecloneforcefromservice 'chicago'
archivelogfromscn 2165528;
switchclonedatafileall;
}
executingMemoryScript
Startingrestoreat25-MAY-15
usingchannelORA_AUX_DISK_1
channelORA_AUX_DISK_1:startingarchivedlogrestoretodefaultdestination
channelORA_AUX_DISK_1:usingnetworkbackupsetfromservicechicago
channelORA_AUX_DISK_1:restoringarchivedlog
archivedlogthread=1sequence=82
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:01
channelORA_AUX_DISK_1:startingarchivedlogrestoretodefaultdestination
channelORA_AUX_DISK_1:usingnetworkbackupsetfromservicechicago
channelORA_AUX_DISK_1:restoringarchivedlog
archivedlogthread=1sequence=83
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:01
channelORA_AUX_DISK_1:startingarchivedlogrestoretodefaultdestination
channelORA_AUX_DISK_1:usingnetworkbackupsetfromservicechicago
channelORA_AUX_DISK_1:restoringarchivedlog
archivedlogthread=1sequence=84
channelORA_AUX_DISK_1:restorecomplete,elapsedtime:00:00:01
Finishedrestoreat25-MAY-15
datafile1switchedtodatafilecopy
inputdatafilecopyRECID=7STAMP=880640897filename=/home/oracle/dbfile/boston/system01.dbf
datafile3switchedtodatafilecopy
inputdatafilecopyRECID=8STAMP=880640897filename=/home/oracle/dbfile/boston/sysaux01.dbf
datafile4switchedtodatafilecopy
inputdatafilecopyRECID=9STAMP=880640897filename=/home/oracle/dbfile/boston/undotbs01.dbf
datafile5switchedtodatafilecopy
inputdatafilecopyRECID=10STAMP=880640897filename=/home/oracle/dbfile/boston/pdbseed/system01.dbf
datafile6switchedtodatafilecopy
inputdatafilecopyRECID=11STAMP=880640897filename=/home/oracle/dbfile/boston/users01.dbf
datafile7switchedtodatafilecopy
inputdatafilecopyRECID=12STAMP=880640897filename=/home/oracle/dbfile/boston/pdbseed/sysaux01.dbf
datafile8switchedtodatafilecopy
inputdatafilecopyRECID=13STAMP=880640897filename=/home/oracle/dbfile/boston/pdb1/system01.dbf
datafile9switchedtodatafilecopy
inputdatafilecopyRECID=14STAMP=880640897filename=/home/oracle/dbfile/boston/pdb1/sysaux01.dbf
datafile10switchedtodatafilecopy
inputdatafilecopyRECID=15STAMP=880640897filename=/home/oracle/dbfile/boston/pdb1/SAMPLE_SCHEMA_users01.dbf
datafile11switchedtodatafilecopy
inputdatafilecopyRECID=16STAMP=880640897filename=/home/oracle/dbfile/boston/pdb1/example01.dbf
contentsofMemoryScript:
{
setuntilscn 2165825;
recover
standby
clonedatabase
deletearchivelog
;
}
executingMemoryScript
executingcommand:SETuntilclause
Startingrecoverat25-MAY-15
usingchannelORA_AUX_DISK_1
startingmediarecovery
archivedlogforthread1withsequence82isalreadyondiskasfile/home/oracle/arch/1_82_878586712.arc
archivedlogforthread1withsequence83isalreadyondiskasfile/home/oracle/arch/1_83_878586712.arc
archivedlogforthread1withsequence84isalreadyondiskasfile/home/oracle/arch/1_84_878586712.arc
archivedlogfilename=/home/oracle/arch/1_82_878586712.arcthread=1sequence=82
archivedlogfilename=/home/oracle/arch/1_83_878586712.arcthread=1sequence=83
archivedlogfilename=/home/oracle/arch/1_84_878586712.arcthread=1sequence=84
mediarecoverycomplete,elapsedtime:00:00:00
Finishedrecoverat25-MAY-15
FinishedDuplicateDbat25-MAY-15
到此为止12c CDB ADG创建完毕。
备库创建成功后，CDB处于mount状态，角色处于物理备库。 逐步开启ADG。
[oracle@boston~]$sqlplus/assysdba
SQL*Plus:Release12.1.0.1.0ProductiononMonMay2514:30:122015
Copyright(c)1982,2013,Oracle. Allrightsreserved.
Connectedto:
OracleDatabase12cEnterpriseEditionRelease12.1.0.1.0-64bitProduction
WiththePartitioning,OLAP,AdvancedAnalyticsandRealApplicationTestingoptions
SYS@boston>selectopen_mode,database_rolefromv$database;
OPEN_MODE DATABASE_ROLE
------------------------------------
MOUNTED PHYSICALSTANDBY
备库执行ALTER DATABAE OPEN会将CDB置于open read only状态。
SYS@boston>ALTERDATABASEOPEN;
Databasealtered.
备库执行ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT将实时应用主库传递过来的归档日志。和前面开启的open read only叠加在一起成为active dataguard
SYS@boston>ALTERDATABASERECOVERMANAGEDSTANDBYDATABASEUSINGARCHIVEDLOGFILEDISCONNECT;
Databasealtered.
SYS@boston>selectopen_mode,database_rolefromv$database;
OPEN_MODE DATABASE_ROLE
------------------------------------
READONLYWITHAPPLYPHYSICALSTANDBY
开启ADG的过程，只会将CDB置于open read only，并不会将mount状态的pdb1也开启为open read only。
SYS@boston>showpdbs;
CON_IDCON_NAME OPENMODE RESTRICTED
------------------------------------------------------------
2PDB$SEED READONLY NO
3PDB1 MOUNTED
SYS@boston>alterpluggabledatabaseallopen;
Pluggabledatabasealtered.
测试ADG效果，在主库的pdb1创建表空间后在备库验证是否可以同步。
主库在pdb创建一个表空间tbs1
SYS@chicago>showpdbs;
CON_IDCON_NAME OPENMODE RESTRICTED
------------------------------------------------------------
2PDB$SEED READONLY NO
3PDB1 MOUNTED
SYS@chicago>
SYS@chicago>alterpluggabledatabaseallopen;
Pluggabledatabasealtered.
SYS@chicago>altersessionsetcontainer=pdb1;
Sessionaltered.
SYS@chicago>selectnamefromv$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/dbfile/chicago/undotbs01.dbf
/home/oracle/dbfile/chicago/pdb1/system01.dbf
/home/oracle/dbfile/chicago/pdb1/sysaux01.dbf
/home/oracle/dbfile/chicago/pdb1/SAMPLE_SCHEMA_users01.dbf
/home/oracle/dbfile/chicago/pdb1/example01.dbf
SYS@chicago>createtablespacetbs1datafile'/home/oracle/dbfile/chicago/pdb1/tbs1.dbf'size5m;
Tablespacecreated.
SYS@chicago>colfile_namefora58
SYS@chicago>setlines150
SYS@chicago>selectfile_name,file_id,con_idfromcdb_data_files;
FILE_NAME FILE_ID CON_ID
------------------------------------------------------------------------------
/home/oracle/dbfile/chicago/pdb1/system01.dbf 8 3
/home/oracle/dbfile/chicago/pdb1/sysaux01.dbf 9 3
/home/oracle/dbfile/chicago/pdb1/SAMPLE_SCHEMA_users01.dbf 10 3
/home/oracle/dbfile/chicago/pdb1/example01.dbf 11 3
/home/oracle/dbfile/chicago/pdb1/tbs1.dbf 12 3
在备库验证表空间tbs1同步成功。但是延时比较大，等了好久才同步成功。
SYS@boston>selectfile_name,file_id,con_idfromcdb_data_files;
FILE_NAME FILE_ID CON_ID
------------------------------------------------------------------------------
/home/oracle/dbfile/boston/pdb1/example01.dbf 11 3
/home/oracle/dbfile/boston/pdb1/SAMPLE_SCHEMA_users01.dbf 10 3
/home/oracle/dbfile/boston/pdb1/sysaux01.dbf 9 3
/home/oracle/dbfile/boston/pdb1/tbs1.dbf 12 3
/home/oracle/dbfile/boston/pdb1/system01.dbf 8 3
最后测试主库添加pdb2，备库的同步效果。使用图形工具dbca在CDB chicago上创建新的pdb。
主库创建pdb2的过程中后台alert输出如下：
CREATEPLUGGABLEDATABASEpdb2ADMINUSERsnowIDENTIFIEDBY*ROLES=(CONNECT) file_name_convert=('/home/oracle/dbfile/chicago/pdbseed/system01.dbf','/home/oracle/dbfile/chicago/pdb2/system01.dbf',
'/home/oracle/dbfile/chicago/pdbseed/sysaux01.dbf','/home/oracle/dbfile/chicago/pdb2/sysaux01.dbf','/home/oracle/dbfile/chicago/pdbseed/pdbseed_temp01.dbf','/home/oracle/dbfile/chicago/pdb2/temp01.dbf')
SatMay0201:27:442015
****************************************************************
PluggableDatabasePDB2withpdbid-4iscreatedasUNUSABLE.
IfanyerrorsareencounteredbeforethepdbismarkedasNEW,
thenthepdbmustbedropped
****************************************************************
Deletingoldfile#5fromfile$
Deletingoldfile#7fromfile$
Addingnewfile#13tofile$(oldfile#5)
Addingnewfile#14tofile$(oldfile#7)
Successfullycreatedinternalservicepdb2atopen
ALTERSYSTEM:Flushingbuffercacheinst=0container=4local
****************************************************************
Postplugoperationsarenowcomplete.
PluggabledatabasePDB2withpdbid-4isnowmarkedasNEW.
****************************************************************
Completed:CREATEPLUGGABLEDATABASEpdb2ADMINUSERsnowIDENTIFIEDBY*ROLES=(CONNECT) file_name_convert=('/home/oracle/dbfile/chicago/pdbseed/system01.dbf','/home/oracle/dbfile/chicago/pdb2/system01.dbf',
'/home/oracle/dbfile/chicago/pdbseed/sysaux01.dbf','/home/oracle/dbfile/chicago/pdb2/sysaux01.dbf','/home/oracle/dbfile/chicago/pdbseed/pdbseed_temp01.dbf','/home/oracle/dbfile/chicago/pdb2/temp01.dbf')
alterpluggabledatabasepdb2open
PluggabledatabasePDB2dictionarycheckbeginning
PluggableDatabasePDB2Dictionarycheckcomplete
Duetolimitedspaceinsharedpool(need6094848bytes,have3981120bytes),limitingResourceManagerentitiesfrom2048to32
OpeningpdbPDB2(4)withnoResourceManagerplanactive
SatMay0201:27:472015
SettingResourceManagerplanSCHEDULER[0x420E]:DEFAULT_MAINTENANCE_PLANviaschedulerwindow
SettingResourceManagerplanDEFAULT_MAINTENANCE_PLANatpdbPDB2(4)viaparameter
SatMay0201:27:472015
XDBinstalled.
XDBinitialized.
PluggabledatabasePDB2openedreadwrite
Completed:alterpluggabledatabasepdb2open
CREATESMALLFILETABLESPACE"USERS"LOGGING DATAFILE'/home/oracle/dbfile/chicago/pdb2/pdb2_users01.dbf'SIZE5MREUSEAUTOEXTENDONNEXT 1280KMAXSIZEUNLIMITED EXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENT AUTO
Completed:CREATESMALLFILETABLESPACE"USERS"LOGGING DATAFILE'/home/oracle/dbfile/chicago/pdb2/pdb2_users01.dbf'SIZE5MREUSEAUTOEXTENDONNEXT 1280KMAXSIZEUNLIMITED EXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENT AUTO
ALTERDATABASEDEFAULTTABLESPACE"USERS"
Completed:ALTERDATABASEDEFAULTTABLESPACE"USERS"
SatMay0201:28:002015
TABLESYS.WRI$_OPTSTAT_HISTHEAD_HISTORY:ADDEDINTERVALPARTITIONSYS_P221(42125)VALUESLESSTHAN(TO_DATE('2015-05-0300:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
TABLESYS.WRI$_OPTSTAT_HISTGRM_HISTORY:ADDEDINTERVALPARTITIONSYS_P241(42125)VALUESLESSTHAN(TO_DATE('2015-05-0300:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
SatMay0201:28:492015
Thread1cannotallocatenewlog,sequence90
Privatestrandflushnotcomplete
Currentlog#2seq#89mem#0:/home/oracle/dbfile/chicago/redo02.log
SatMay0201:28:522015
Thread1advancedtologsequence90(LGWRswitch)
Currentlog#3seq#90mem#0:/home/oracle/dbfile/chicago/redo03.log
SatMay0201:28:532015
ArchivedLogentry87addedforthread1sequence89ID0x58b61e17dest1:
SatMay0201:28:532015
TT00:Standbyredologfileselectedforthread1sequence90fordestinationLOG_ARCHIVE_DEST_2
与此同时在备库端观察alert日志查看同步效果。通过日志输出，可以观察到新的数据文件创建到新的目录pdb2下。
boston alert.log
Recoverysuccessfullycopiedfile/home/oracle/dbfile/boston/pdb2/system01.dbffrom/home/oracle/dbfile/boston/pdbseed/system01.dbf
Recoverycreatedfile/home/oracle/dbfile/boston/pdb2/system01.dbf
Successfullyaddeddatafile13tomediarecovery
Datafile#13:'/home/oracle/dbfile/boston/pdb2/system01.dbf'
RecoverycopiedfilesfortablespaceSYSAUX
Recoverysuccessfullycopiedfile/home/oracle/dbfile/boston/pdb2/sysaux01.dbffrom/home/oracle/dbfile/boston/pdbseed/sysaux01.dbf
Recoverycreatedfile/home/oracle/dbfile/boston/pdb2/sysaux01.dbf
Successfullyaddeddatafile14tomediarecovery
Datafile#14:'/home/oracle/dbfile/boston/pdb2/sysaux01.dbf'
Recoverycreatedfile/home/oracle/dbfile/boston/pdb2/pdb2_users01.dbf
Successfullyaddeddatafile15tomediarecovery
Datafile#15:'/home/oracle/dbfile/boston/pdb2/pdb2_users01.dbf'
MediaRecoveryWaitingforthread1sequence90(intransit)
RFS[2]:Selectedlog5forthread1sequence91dbid1488305431branch878586712
MonMay2515:12:532015
主库创建后的实例列表和文件分布
SYS@chicago>showpdbs;
CON_IDCON_NAME OPENMODE RESTRICTED
------------------------------------------------------------
2PDB$SEED READONLY NO
3PDB1 READWRITENO
4PDB2 READWRITENO
SYS@chicago>selectnamefromv$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/dbfile/chicago/system01.dbf
/home/oracle/dbfile/chicago/sysaux01.dbf
/home/oracle/dbfile/chicago/undotbs01.dbf
/home/oracle/dbfile/chicago/pdbseed/system01.dbf
/home/oracle/dbfile/chicago/users01.dbf
/home/oracle/dbfile/chicago/pdbseed/sysaux01.dbf
/home/oracle/dbfile/chicago/pdb1/system01.dbf
/home/oracle/dbfile/chicago/pdb1/sysaux01.dbf
/home/oracle/dbfile/chicago/pdb1/SAMPLE_SCHEMA_users01.dbf
/home/oracle/dbfile/chicago/pdb1/example01.dbf
/home/oracle/dbfile/chicago/pdb1/tbs1.dbf
NAME
--------------------------------------------------------------------------------
/home/oracle/dbfile/chicago/pdb2/system01.dbf
/home/oracle/dbfile/chicago/pdb2/sysaux01.dbf
/home/oracle/dbfile/chicago/pdb2/pdb2_users01.dbf
14rowsselected.
备库的实例列表和文件分布
SYS@boston>showpdbs;
CON_IDCON_NAME OPENMODE RESTRICTED
------------------------------------------------------------
2PDB$SEED READONLY NO
3PDB1 READONLY NO
4PDB2 MOUNTED
SYS@boston>selectnamefromv$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/dbfile/boston/system01.dbf
/home/oracle/dbfile/boston/sysaux01.dbf
/home/oracle/dbfile/boston/undotbs01.dbf
/home/oracle/dbfile/boston/pdbseed/system01.dbf
/home/oracle/dbfile/boston/users01.dbf
/home/oracle/dbfile/boston/pdbseed/sysaux01.dbf
/home/oracle/dbfile/boston/pdb1/system01.dbf
/home/oracle/dbfile/boston/pdb1/sysaux01.dbf
/home/oracle/dbfile/boston/pdb1/SAMPLE_SCHEMA_users01.dbf
/home/oracle/dbfile/boston/pdb1/example01.dbf
/home/oracle/dbfile/boston/pdb1/tbs1.dbf
NAME
--------------------------------------------------------------------------------
/home/oracle/dbfile/boston/pdb2/system01.dbf
/home/oracle/dbfile/boston/pdb2/sysaux01.dbf
/home/oracle/dbfile/boston/pdb2/pdb2_users01.dbf
14rowsselected.
一些补充
主库新建pdb2后不会自动添加tnsnames，为了客户端连接需要手工添加蓝色pdb2部分。
[oracle@snowadmin]$moretnsnames.ora
#tnsnames.oraNetworkConfigurationFile:/u01/app/oracle/product/12.1.0.1/db_1/network/admin/tnsnames.ora
#GeneratedbyOracleconfigurationtools.
BOSTON=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=boston)(PORT=1521))
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=boston)
)
)
CHICAGO=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.228.12)(PORT=1521))
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=chicago)
)
)
LISTENER_ALEX=
(ADDRESS=(PROTOCOL=TCP)(HOST=snow.oracle.com)(PORT=1521))
PDB1=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.228.12)(PORT=1521))
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=pdb1)
)
)
PDB2 =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.228.12)(PORT=1521))
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=pdb2)
)
)
主库创建pdb2，备库应用日志后也创建了pdb2，并且在制定目录/home/oracle/dbfile/boston下创建了pdb2目录来存放数据文件。
这本来是不需要担心的地方，但是在HP Unix会出现问题。备库应用日志后无法创建pdb的目录导致创建失败。每当主库创建一个新的pdb之前需要在备库手工创建相应的目录才可以。
[oracle@bostonboston]$ll
total2351032
-rw-r-----1oracleoinstall 18268160May2515:26control01.ctl
-rw-r-----1oracleoinstall 18268160May2515:26control02.ctl
drwxr-xr-x2oracleoinstall 4096May2514:43pdb1
drwxr-x---2oracleoinstall 4096May2515:12pdb2
drwxr-xr-x2oracleoinstall 4096May2514:31pdbseed
-rw-r-----1oracleoinstall 52429312May2514:28redo01.log
-rw-r-----1oracleoinstall 52429312May2514:28redo02.log
-rw-r-----1oracleoinstall 52429312May2514:28redo03.log
-rw-r-----1oracleoinstall 52429312May2515:12redo04.log
-rw-r-----1oracleoinstall 52429312May2515:26redo05.log
-rw-r-----1oracleoinstall 52429312May2514:28redo06.log
-rw-r-----1oracleoinstall 52429312May2514:28redo07.log
-rw-r-----1oracleoinstall807411712May2515:13sysaux01.dbf
-rw-r-----1oracleoinstall828383232May2515:13system01.dbf
-rw-r-----1oracleoinstall 92282880May2514:31temp01.dbf
-rw-r-----1oracleoinstall361766912May2515:13undotbs01.dbf
-rw-r-----1oracleoinstall 5251072May2515:13users01.dbf
关于如何理解Oracle 12c CDB的ADG就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
