ORACLE 12C RAC ADG（MAA)
来源：互联网 发布：淘宝卖的军用水壶真假 编辑：程序博客网 时间：2024/06/05 01:58
前言总结：
本次的ORACLE 12C ACTIVE DATAGUARD的安装过程，表述较为浅显，后期会更加深入的研究12C ADG的各功能
总体安装情况，步骤与11g并未有太大区别，此处做记录供大家参考
背景：
类型 os version db version Public ip vip 类型 db_unique_name service_name 备注
主库 OEL6.3 12.1.0.2 192.168.1.161 192.168.1.163 Rac vmiko12c vmiko12c 有库
192.168.1.162 192.168.1.164
备库 OEL6.3 12.1.0.2 192.168.1.121 单机 m12cst m12cst Db soft
准备：
ORACLE RAC 12C 安装，主库本次以standard rac 的模式安装，并未研究flex rac,也并未启动flex asm模式，数据库采取CDB&PDB模式，备库install software only.
ps：备机OS的准备使用的vbox clone硬盘的功能：
>cd /d D:\oracle\vbox
>VBoxManage clonehd "D:\oracle\vbox-machine\12102-12c\node1\vmiko1.vdi" "D:\oracle\vbox-machine\12102-12c\standby-host\12cstandbyOS.vdi"
VBOX新建主机--添加已存在的硬盘（详细过程略）
具体流程总结：
1，主备库listener.ora&tnsnames.ora准备，并测试主备库之间的tns互通姓
2，准备备库的pfile,起到nomount状态，使用Rman 运行duplicate脚本进行备库的复制
3，测试switchover
============================
STEP1 tnsname配置：
个人觉得tns的配置成功大概就完成了DATAGUARD搭建工作的百分之七十，其中对于service_name,网络别名的理解要非常清晰，否则在搭建过程中难免问题一堆。
1，监听文件的准备（listener.ora)
主库：
实例1listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = vmiko12c)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = vmiko12c1)
)
)
实例2listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = vmiko12c)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = vmiko12c2)
)
)
查看此时的监听状态：
[oracle@miko12c1 tmp]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-3月 -2015 17:34:56
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 16-3月 -2015 17:22:49
Uptime 0 days 0 hr. 12 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/12.1.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/miko12c1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.161)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.163)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "-MGMTDBXDB" has 1 instance(s).
Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "_mgmtdb" has 1 instance(s).
Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "miko12c_cluster" has 1 instance(s).
Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "vmiko12c1", status READY, has 1 handler(s) for this service...
Service "vmiko12c" has 2 instance(s).
Instance "vmiko12c1", status UNKNOWN, has 1 handler(s) for this service...
Instance "vmiko12c1", status READY, has 1 handler(s) for this service...
Service "vmiko12cXDB" has 1 instance(s).
Instance "vmiko12c1", status READY, has 1 handler(s) for this service...
Service "vpdb" has 1 instance(s).
Instance "vmiko12c1", status READY, has 1 handler(s) for this service...
The command completed successfully
可以看到主库的静态监听已启用，service_name即为：vmiko12c
2，备库listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = m12cst)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = miko12cstd)
)
)
查看备库的监听情况：
[oracle@miko12cstd trace]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-3月 -2015 17:38:07
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 16-3月 -2015 17:36:55
Uptime 0 days 0 hr. 1 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/miko12cstd/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=miko12cstd)(PORT=1521)))
Services Summary...
Service "m12cst" has 2 instance(s).
Instance "miko12cstd", status UNKNOWN, has 1 handler(s) for this service...
Instance "miko12cstd", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "miko12cstd", status READY, has 1 handler(s) for this service...
Service "vmiko12cXDB" has 1 instance(s).
Instance "miko12cstd", status READY, has 1 handler(s) for this service...
Service "vpdb" has 1 instance(s).
Instance "miko12cstd", status READY, has 1 handler(s) for this service...
The command completed successfully
可以看到备库的静态监听也起到效果，可以看到service_name=m12cst的有2个条目，一个为listener.ora里面设置的静态监听，一个为pmon注册的动态监听
3，主备库tnsnames.ora内容：
m12cst =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.121)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = m12cst)
)
)
miko12c =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.163)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.164)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = vmiko12c)
)
)
测试上述两个网络别名是否可用
主库执行sqlplus system/oracle@[url=]m12cst[/url]
备库执行sqlplus system/oracle@miko12c
看上述命令是否会报错
二，准备使用RMAN脚本进行备库的恢复
1，运行前的准备
a)主库开启归档
srvctl start database -d vmiko12c -o mount
alter database archivelog;
alter database open;
b)主库开启force logging
alter database force logging;
c)standby log创建
alter database add standby logfile thread 1 group 13 '+DATADG' size 50m;
alter database add standby logfile thread 1 group 14 '+DATADG' size 50m;
alter database add standby logfile thread 1 group 15 '+DATADG' size 50m;
alter database add standby logfile thread 1 group 16 '+DATADG' size 50m;
alter database add standby logfile thread 2 group 17 '+DATADG' size 50m;
alter database add standby logfile thread 2 group 18 '+DATADG' size 50m;
alter database add standby logfile thread 2 group 19 '+DATADG' size 50m;
alter database add standby logfile thread 2 group 20 '+DATADG' size 50m;
d)备库路径创建（主要是一长串字母性的文件夹要与ASM相温和，即asm的dg有一个这样的文件夹，我们就必须在备库的文件系统上建同样的文件夹，在12c 每个这样的路径都代表一个pdb，OMF管理会自动去管理这些文件夹，新的datafile的创建会自动分配到对应的PDB的路径下面去）
mkdir -p /u01/app/oracle/admin/m12cst/adump
mkdir -p /u01/app/oracle/oradata/vmiko12c/1026B1D66AF4527BE053A101A8C0AA7F/tempfile
mkdir -p /u01/app/oracle/oradata/vmiko12c/1026f9bf1891703ce053a101a8c04071/tempfile
mkdir -p /u01/app/oracle/oradata/vmiko12c/1026f9bf1891703ce053a101a8c04071/datafile
mkdir -p /u01/app/oracle/oradata/vmiko12c/104fede898b019c5e053a101a8c04973/tempfile
mkdir -p /u01/app/oracle/oradata/vmiko12c/104fede898b019c5e053a101a8c04973/datafile
mkdir -p /u01/app/oracle/oradata/vmiko12c/FD9AC20F64D244D7E043B6A9E80A2F2F/datafile
mkdir -p /u01/app/oracle/oradata/vmiko12c/ONLINELOG/
mkdir -p /u01/app/oracle/oradata/vmiko12c/vpdb/datafile/
mkdir -p /u01/app/oracle/oradata/vmiko12c/
e)密码文件创建
node1:orapwd file=$ORACLE_HOME/dbs/orapwvmiko12c1 password=oracle entries=5
----> 传输一个密码文件到备机上面，并改名成orapwm12cstd
ps: 12c以后密码文件都放在了asm diskgroup里面，关于这方面的知识还没深入研究，此时为搭DG，暂时不考虑
2，主库参数的设定：
alter system set log_archive_config='DG_CONFIG=(m12cst,vmiko12c)' scope=spfile ; ------------------> db_unique_name （备库pfile）
alter system set log_archive_dest_1='LOCATION=+DATADG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=vmiko12c' scope=spfile;
alter system set log_archive_dest_2='SERVICE=m12cst LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=m12cst' scope=spfile;
alter system set db_file_name_convert='/u01/app/oracle/oradata/', '+datadg' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/', '+datadg' scope=spfile;
alter system set fal_client='vmiko12c' scope=both; --------> 自己的service_name
alter system set fal_server='m12cst' scope=both; --------> 备库的service_name
alter system set standby_file_management=AUTO scope=spfile;
alter system set LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc' scope=spfile;
3，准备备库的pfile文件以及使用这个pfile起到nomount状态
*.db_name=vmiko12c
*.db_block_size=8192
*.db_unique_name=m12cst
4，连接RMAN操作单元
rman target sys/oracle@miko12c auxiliarysys/oracle@m12cst
5，运行RMAN 命令快
run
{
allocate channel prmy1 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database nofilenamecheck
spfile
set db_unique_name='m12cst'
set db_file_name_convert='+datadg','/u01/app/oracle/oradata/'
set log_file_name_convert= '+datadg','/u01/app/oracle/oradata/'
set control_files='/u01/app/oracle/oradata/control01.ctl'
set log_archive_max_processes='10'
set fal_client='m12cst'
set fal_server='vmiko12c'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(m12cst,vmiko12c)'
set log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=m12cst'
set log_archive_dest_2='service=vmiko12c LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=vmiko12c'
set audit_file_dest='/u01/app/oracle/admin/m12cst/adump'
set diagnostic_dest='/u01/app/oracle/'
SET CLUSTER_DATABASE='FALSE'
set REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
;
}
6，备注：
出错需要重新RMAN以及startup nomount;
startup nomount pfile='$ORACLE_HOME/dbs/initmiko12cstd.ora';
7，日志输出：
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=80 instance=vmiko12c1 device type=DISK
allocated channel: stby
channel stby: SID=22 device type=DISK
Starting Duplicate Db at 07-3月 -15
contents of Memory Script:
{
backup as copy reuse
targetfile '+DATADG/VMIKO12C/PASSWORD/pwdvmiko12c.256.872888703' auxiliary format
'/u01/app/oracle/product/12.1.0/db_1/dbs/orapwmiko12cstd' ;
restore clone from service 'miko12c' spfile to
'/u01/app/oracle/product/12.1.0/db_1/dbs/spfilemiko12cstd.ora';
sql clone "alter system set spfile= ''/u01/app/oracle/product/12.1.0/db_1/dbs/spfilemiko12cstd.ora''";
}
executing Memory Script
Starting backup at 07-3月 -15
Finished backup at 07-3月 -15
Starting restore at 07-3月 -15
channel stby: starting datafile backup set restore
channel stby: using network backup set from service miko12c
channel stby: restoring SPFILE
output file name=/u01/app/oracle/product/12.1.0/db_1/dbs/spfilemiko12cstd.ora
channel stby: restore complete, elapsed time: 00:00:02
Finished restore at 07-3月 -15
sql statement: alter system set spfile= ''/u01/app/oracle/product/12.1.0/db_1/dbs/spfilemiko12cstd.ora''
contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''m12cst'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''+datadg'', ''/u01/app/oracle/oradata/'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''+datadg'', ''/u01/app/oracle/oradata/'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/control01.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes =
10 comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''m12cst'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''vmiko12c'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_config =
''dg_config=(m12cst,vmiko12c)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=/u01/app/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=m12cst'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=vmiko12c LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=vmiko12c'' comment=
'''' scope=spfile";
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/m12cst/adump'' comment=
'''' scope=spfile";
sql clone "alter system set diagnostic_dest =
''/u01/app/oracle/'' comment=
'''' scope=spfile";
sql clone "alter system set CLUSTER_DATABASE =
FALSE comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''m12cst'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''+datadg'', ''/u01/app/oracle/oradata/'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''+datadg'', ''/u01/app/oracle/oradata/'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/control01.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 10 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''m12cst'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''vmiko12c'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(m12cst,vmiko12c)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=/u01/app/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=m12cst'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=vmiko12c LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=vmiko12c'' comment= '''' scope=spfile
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/m12cst/adump'' comment= '''' scope=spfile
sql statement: alter system set diagnostic_dest = ''/u01/app/oracle/'' comment= '''' scope=spfile
sql statement: alter system set CLUSTER_DATABASE = FALSE comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1241513984 bytes
Fixed Size 2923872 bytes
Variable Size 452985504 bytes
Database Buffers 771751936 bytes
Redo Buffers 13852672 bytes
allocated channel: stby
channel stby: SID=21 device type=DISK
contents of Memory Script:
{
restore clone from service 'miko12c' standby controlfile;
}
executing Memory Script
Starting restore at 07-3月 -15
channel stby: starting datafile backup set restore
channel stby: using network backup set from service miko12c
channel stby: restoring control file
channel stby: restore complete, elapsed time: 00:00:08
output file name=/u01/app/oracle/oradata/control01.ctl
Finished restore at 07-3月 -15
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
"/u01/app/oracle/oradata//vmiko12c/tempfile/temp.264.872888959";
set newname for tempfile 2 to
"/u01/app/oracle/oradata//vmiko12c/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/pdbseed_temp012015-02-28_09-10-37-pm.dbf";
set newname for tempfile 3 to
"/u01/app/oracle/oradata//vmiko12c/1026f9bf1891703ce053a101a8c04071/tempfile/temp.274.872890263";
set newname for tempfile 4 to
"/u01/app/oracle/oradata//vmiko12c/104fede898b019c5e053a101a8c04973/tempfile/temp.303.873325333";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata//vmiko12c/datafile/system.258.872888799";
set newname for datafile 3 to
"/u01/app/oracle/oradata//vmiko12c/datafile/sysaux.257.872888739";
set newname for datafile 4 to
"/u01/app/oracle/oradata//vmiko12c/datafile/undotbs1.260.872888877";
set newname for datafile 5 to
"/u01/app/oracle/oradata//vmiko12c/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/system.266.872888977";
set newname for datafile 6 to
"/u01/app/oracle/oradata//vmiko12c/datafile/users.259.872888875";
set newname for datafile 7 to
"/u01/app/oracle/oradata//vmiko12c/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/sysaux.265.872888977";
set newname for datafile 8 to
"/u01/app/oracle/oradata//vmiko12c/datafile/undotbs2.268.872889709";
set newname for datafile 9 to
"/u01/app/oracle/oradata//vmiko12c/vpdb/datafile/system01.bdf";
set newname for datafile 10 to
"/u01/app/oracle/oradata//vmiko12c/vpdb/datafile/sysaux01.bdf";
set newname for datafile 11 to
"/u01/app/oracle/oradata//vmiko12c/vpdb/datafile/users01.bdf";
set newname for datafile 15 to
"/u01/app/oracle/oradata//vmiko12c/104fede898b019c5e053a101a8c04973/datafile/system.302.873325285";
set newname for datafile 16 to
"/u01/app/oracle/oradata//vmiko12c/104fede898b019c5e053a101a8c04973/datafile/sysaux.273.873325285";
set newname for datafile 17 to
"/u01/app/oracle/oradata//vmiko12c/104fede898b019c5e053a101a8c04973/datafile/tbs_pdb2.304.873325341";
restore
from service 'miko12c' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata//vmiko12c/tempfile/temp.264.872888959 in control file
renamed tempfile 2 to /u01/app/oracle/oradata//vmiko12c/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/pdbseed_temp012015-02-28_09-10-37-pm.dbf in control file
renamed tempfile 3 to /u01/app/oracle/oradata//vmiko12c/1026f9bf1891703ce053a101a8c04071/tempfile/temp.274.872890263 in control file
renamed tempfile 4 to /u01/app/oracle/oradata//vmiko12c/104fede898b019c5e053a101a8c04973/tempfile/temp.303.873325333 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 07-3月 -15
channel stby: starting datafile backup set restore
channel stby: using network backup set from service miko12c
channel stby: specifying datafile(s) to restore from backup set
channel stby: restoring datafile 00001 to /u01/app/oracle/oradata//vmiko12c/datafile/system.258.872888799
...
省略的部分为各个datafile的复制情况，不赘言表了
三，switchover
主库:
alter database switchover to m12cst verify;
alter database switchover to m12cst
原备库 新主库：
alter database open;
原主库，新备库：
startup
本次的ORACLE 12C ACTIVE DATAGUARD的安装过程，表述较为浅显，后期会更加深入的研究12C ADG的各功能
总体安装情况，步骤与11g并未有太大区别，此处做记录供大家参考
背景：
类型 os version db version Public ip vip 类型 db_unique_name service_name 备注
主库 OEL6.3 12.1.0.2 192.168.1.161 192.168.1.163 Rac vmiko12c vmiko12c 有库
192.168.1.162 192.168.1.164
备库 OEL6.3 12.1.0.2 192.168.1.121 单机 m12cst m12cst Db soft
准备：
ORACLE RAC 12C 安装，主库本次以standard rac 的模式安装，并未研究flex rac,也并未启动flex asm模式，数据库采取CDB&PDB模式，备库install software only.
ps：备机OS的准备使用的vbox clone硬盘的功能：
>cd /d D:\oracle\vbox
>VBoxManage clonehd "D:\oracle\vbox-machine\12102-12c\node1\vmiko1.vdi" "D:\oracle\vbox-machine\12102-12c\standby-host\12cstandbyOS.vdi"
VBOX新建主机--添加已存在的硬盘（详细过程略）
具体流程总结：
1，主备库listener.ora&tnsnames.ora准备，并测试主备库之间的tns互通姓
2，准备备库的pfile,起到nomount状态，使用Rman 运行duplicate脚本进行备库的复制
3，测试switchover
============================
STEP1 tnsname配置：
个人觉得tns的配置成功大概就完成了DATAGUARD搭建工作的百分之七十，其中对于service_name,网络别名的理解要非常清晰，否则在搭建过程中难免问题一堆。
1，监听文件的准备（listener.ora)
主库：
实例1listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = vmiko12c)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = vmiko12c1)
)
)
实例2listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = vmiko12c)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = vmiko12c2)
)
)
查看此时的监听状态：
[oracle@miko12c1 tmp]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-3月 -2015 17:34:56
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 16-3月 -2015 17:22:49
Uptime 0 days 0 hr. 12 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/12.1.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/miko12c1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.161)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.163)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "-MGMTDBXDB" has 1 instance(s).
Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "_mgmtdb" has 1 instance(s).
Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "miko12c_cluster" has 1 instance(s).
Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "vmiko12c1", status READY, has 1 handler(s) for this service...
Service "vmiko12c" has 2 instance(s).
Instance "vmiko12c1", status UNKNOWN, has 1 handler(s) for this service...
Instance "vmiko12c1", status READY, has 1 handler(s) for this service...
Service "vmiko12cXDB" has 1 instance(s).
Instance "vmiko12c1", status READY, has 1 handler(s) for this service...
Service "vpdb" has 1 instance(s).
Instance "vmiko12c1", status READY, has 1 handler(s) for this service...
The command completed successfully
可以看到主库的静态监听已启用，service_name即为：vmiko12c
2，备库listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = m12cst)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = miko12cstd)
)
)
查看备库的监听情况：
[oracle@miko12cstd trace]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 16-3月 -2015 17:38:07
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 16-3月 -2015 17:36:55
Uptime 0 days 0 hr. 1 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/miko12cstd/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=miko12cstd)(PORT=1521)))
Services Summary...
Service "m12cst" has 2 instance(s).
Instance "miko12cstd", status UNKNOWN, has 1 handler(s) for this service...
Instance "miko12cstd", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "miko12cstd", status READY, has 1 handler(s) for this service...
Service "vmiko12cXDB" has 1 instance(s).
Instance "miko12cstd", status READY, has 1 handler(s) for this service...
Service "vpdb" has 1 instance(s).
Instance "miko12cstd", status READY, has 1 handler(s) for this service...
The command completed successfully
可以看到备库的静态监听也起到效果，可以看到service_name=m12cst的有2个条目，一个为listener.ora里面设置的静态监听，一个为pmon注册的动态监听
3，主备库tnsnames.ora内容：
m12cst =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.121)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = m12cst)
)
)
miko12c =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.163)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.164)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = vmiko12c)
)
)
测试上述两个网络别名是否可用
主库执行sqlplus system/oracle@[url=]m12cst[/url]
备库执行sqlplus system/oracle@miko12c
看上述命令是否会报错
二，准备使用RMAN脚本进行备库的恢复
1，运行前的准备
a)主库开启归档
srvctl start database -d vmiko12c -o mount
alter database archivelog;
alter database open;
b)主库开启force logging
alter database force logging;
c)standby log创建
alter database add standby logfile thread 1 group 13 '+DATADG' size 50m;
alter database add standby logfile thread 1 group 14 '+DATADG' size 50m;
alter database add standby logfile thread 1 group 15 '+DATADG' size 50m;
alter database add standby logfile thread 1 group 16 '+DATADG' size 50m;
alter database add standby logfile thread 2 group 17 '+DATADG' size 50m;
alter database add standby logfile thread 2 group 18 '+DATADG' size 50m;
alter database add standby logfile thread 2 group 19 '+DATADG' size 50m;
alter database add standby logfile thread 2 group 20 '+DATADG' size 50m;
d)备库路径创建（主要是一长串字母性的文件夹要与ASM相温和，即asm的dg有一个这样的文件夹，我们就必须在备库的文件系统上建同样的文件夹，在12c 每个这样的路径都代表一个pdb，OMF管理会自动去管理这些文件夹，新的datafile的创建会自动分配到对应的PDB的路径下面去）
mkdir -p /u01/app/oracle/admin/m12cst/adump
mkdir -p /u01/app/oracle/oradata/vmiko12c/1026B1D66AF4527BE053A101A8C0AA7F/tempfile
mkdir -p /u01/app/oracle/oradata/vmiko12c/1026f9bf1891703ce053a101a8c04071/tempfile
mkdir -p /u01/app/oracle/oradata/vmiko12c/1026f9bf1891703ce053a101a8c04071/datafile
mkdir -p /u01/app/oracle/oradata/vmiko12c/104fede898b019c5e053a101a8c04973/tempfile
mkdir -p /u01/app/oracle/oradata/vmiko12c/104fede898b019c5e053a101a8c04973/datafile
mkdir -p /u01/app/oracle/oradata/vmiko12c/FD9AC20F64D244D7E043B6A9E80A2F2F/datafile
mkdir -p /u01/app/oracle/oradata/vmiko12c/ONLINELOG/
mkdir -p /u01/app/oracle/oradata/vmiko12c/vpdb/datafile/
mkdir -p /u01/app/oracle/oradata/vmiko12c/
e)密码文件创建
node1:orapwd file=$ORACLE_HOME/dbs/orapwvmiko12c1 password=oracle entries=5
----> 传输一个密码文件到备机上面，并改名成orapwm12cstd
ps: 12c以后密码文件都放在了asm diskgroup里面，关于这方面的知识还没深入研究，此时为搭DG，暂时不考虑
2，主库参数的设定：
alter system set log_archive_config='DG_CONFIG=(m12cst,vmiko12c)' scope=spfile ; ------------------> db_unique_name （备库pfile）
alter system set log_archive_dest_1='LOCATION=+DATADG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=vmiko12c' scope=spfile;
alter system set log_archive_dest_2='SERVICE=m12cst LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=m12cst' scope=spfile;
alter system set db_file_name_convert='/u01/app/oracle/oradata/', '+datadg' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/', '+datadg' scope=spfile;
alter system set fal_client='vmiko12c' scope=both; --------> 自己的service_name
alter system set fal_server='m12cst' scope=both; --------> 备库的service_name
alter system set standby_file_management=AUTO scope=spfile;
alter system set LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc' scope=spfile;
3，准备备库的pfile文件以及使用这个pfile起到nomount状态
*.db_name=vmiko12c
*.db_block_size=8192
*.db_unique_name=m12cst
4，连接RMAN操作单元
rman target sys/oracle@miko12c auxiliarysys/oracle@m12cst
5，运行RMAN 命令快
run
{
allocate channel prmy1 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database nofilenamecheck
spfile
set db_unique_name='m12cst'
set db_file_name_convert='+datadg','/u01/app/oracle/oradata/'
set log_file_name_convert= '+datadg','/u01/app/oracle/oradata/'
set control_files='/u01/app/oracle/oradata/control01.ctl'
set log_archive_max_processes='10'
set fal_client='m12cst'
set fal_server='vmiko12c'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(m12cst,vmiko12c)'
set log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=m12cst'
set log_archive_dest_2='service=vmiko12c LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=vmiko12c'
set audit_file_dest='/u01/app/oracle/admin/m12cst/adump'
set diagnostic_dest='/u01/app/oracle/'
SET CLUSTER_DATABASE='FALSE'
set REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
;
}
6，备注：
出错需要重新RMAN以及startup nomount;
startup nomount pfile='$ORACLE_HOME/dbs/initmiko12cstd.ora';
7，日志输出：
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=80 instance=vmiko12c1 device type=DISK
allocated channel: stby
channel stby: SID=22 device type=DISK
Starting Duplicate Db at 07-3月 -15
contents of Memory Script:
{
backup as copy reuse
targetfile '+DATADG/VMIKO12C/PASSWORD/pwdvmiko12c.256.872888703' auxiliary format
'/u01/app/oracle/product/12.1.0/db_1/dbs/orapwmiko12cstd' ;
restore clone from service 'miko12c' spfile to
'/u01/app/oracle/product/12.1.0/db_1/dbs/spfilemiko12cstd.ora';
sql clone "alter system set spfile= ''/u01/app/oracle/product/12.1.0/db_1/dbs/spfilemiko12cstd.ora''";
}
executing Memory Script
Starting backup at 07-3月 -15
Finished backup at 07-3月 -15
Starting restore at 07-3月 -15
channel stby: starting datafile backup set restore
channel stby: using network backup set from service miko12c
channel stby: restoring SPFILE
output file name=/u01/app/oracle/product/12.1.0/db_1/dbs/spfilemiko12cstd.ora
channel stby: restore complete, elapsed time: 00:00:02
Finished restore at 07-3月 -15
sql statement: alter system set spfile= ''/u01/app/oracle/product/12.1.0/db_1/dbs/spfilemiko12cstd.ora''
contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''m12cst'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''+datadg'', ''/u01/app/oracle/oradata/'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''+datadg'', ''/u01/app/oracle/oradata/'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/control01.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes =
10 comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''m12cst'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''vmiko12c'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_config =
''dg_config=(m12cst,vmiko12c)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=/u01/app/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=m12cst'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=vmiko12c LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=vmiko12c'' comment=
'''' scope=spfile";
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/m12cst/adump'' comment=
'''' scope=spfile";
sql clone "alter system set diagnostic_dest =
''/u01/app/oracle/'' comment=
'''' scope=spfile";
sql clone "alter system set CLUSTER_DATABASE =
FALSE comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''m12cst'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''+datadg'', ''/u01/app/oracle/oradata/'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''+datadg'', ''/u01/app/oracle/oradata/'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/control01.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 10 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''m12cst'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''vmiko12c'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(m12cst,vmiko12c)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=/u01/app/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=m12cst'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=vmiko12c LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=vmiko12c'' comment= '''' scope=spfile
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/m12cst/adump'' comment= '''' scope=spfile
sql statement: alter system set diagnostic_dest = ''/u01/app/oracle/'' comment= '''' scope=spfile
sql statement: alter system set CLUSTER_DATABASE = FALSE comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1241513984 bytes
Fixed Size 2923872 bytes
Variable Size 452985504 bytes
Database Buffers 771751936 bytes
Redo Buffers 13852672 bytes
allocated channel: stby
channel stby: SID=21 device type=DISK
contents of Memory Script:
{
restore clone from service 'miko12c' standby controlfile;
}
executing Memory Script
Starting restore at 07-3月 -15
channel stby: starting datafile backup set restore
channel stby: using network backup set from service miko12c
channel stby: restoring control file
channel stby: restore complete, elapsed time: 00:00:08
output file name=/u01/app/oracle/oradata/control01.ctl
Finished restore at 07-3月 -15
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
"/u01/app/oracle/oradata//vmiko12c/tempfile/temp.264.872888959";
set newname for tempfile 2 to
"/u01/app/oracle/oradata//vmiko12c/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/pdbseed_temp012015-02-28_09-10-37-pm.dbf";
set newname for tempfile 3 to
"/u01/app/oracle/oradata//vmiko12c/1026f9bf1891703ce053a101a8c04071/tempfile/temp.274.872890263";
set newname for tempfile 4 to
"/u01/app/oracle/oradata//vmiko12c/104fede898b019c5e053a101a8c04973/tempfile/temp.303.873325333";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata//vmiko12c/datafile/system.258.872888799";
set newname for datafile 3 to
"/u01/app/oracle/oradata//vmiko12c/datafile/sysaux.257.872888739";
set newname for datafile 4 to
"/u01/app/oracle/oradata//vmiko12c/datafile/undotbs1.260.872888877";
set newname for datafile 5 to
"/u01/app/oracle/oradata//vmiko12c/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/system.266.872888977";
set newname for datafile 6 to
"/u01/app/oracle/oradata//vmiko12c/datafile/users.259.872888875";
set newname for datafile 7 to
"/u01/app/oracle/oradata//vmiko12c/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/sysaux.265.872888977";
set newname for datafile 8 to
"/u01/app/oracle/oradata//vmiko12c/datafile/undotbs2.268.872889709";
set newname for datafile 9 to
"/u01/app/oracle/oradata//vmiko12c/vpdb/datafile/system01.bdf";
set newname for datafile 10 to
"/u01/app/oracle/oradata//vmiko12c/vpdb/datafile/sysaux01.bdf";
set newname for datafile 11 to
"/u01/app/oracle/oradata//vmiko12c/vpdb/datafile/users01.bdf";
set newname for datafile 15 to
"/u01/app/oracle/oradata//vmiko12c/104fede898b019c5e053a101a8c04973/datafile/system.302.873325285";
set newname for datafile 16 to
"/u01/app/oracle/oradata//vmiko12c/104fede898b019c5e053a101a8c04973/datafile/sysaux.273.873325285";
set newname for datafile 17 to
"/u01/app/oracle/oradata//vmiko12c/104fede898b019c5e053a101a8c04973/datafile/tbs_pdb2.304.873325341";
restore
from service 'miko12c' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata//vmiko12c/tempfile/temp.264.872888959 in control file
renamed tempfile 2 to /u01/app/oracle/oradata//vmiko12c/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/pdbseed_temp012015-02-28_09-10-37-pm.dbf in control file
renamed tempfile 3 to /u01/app/oracle/oradata//vmiko12c/1026f9bf1891703ce053a101a8c04071/tempfile/temp.274.872890263 in control file
renamed tempfile 4 to /u01/app/oracle/oradata//vmiko12c/104fede898b019c5e053a101a8c04973/tempfile/temp.303.873325333 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 07-3月 -15
channel stby: starting datafile backup set restore
channel stby: using network backup set from service miko12c
channel stby: specifying datafile(s) to restore from backup set
channel stby: restoring datafile 00001 to /u01/app/oracle/oradata//vmiko12c/datafile/system.258.872888799
...
省略的部分为各个datafile的复制情况，不赘言表了
三，switchover
主库:
alter database switchover to m12cst verify;
alter database switchover to m12cst
原备库 新主库：
alter database open;
原主库，新备库：
startup
0 0
- ORACLE 12C RAC ADG（MAA)
- 用Oracle 12c的MAA架构保障应用程序的连续性
- Oracle MAA 说明
- Oracle 12C DG 搭建(RAC-RAC/RAC-单机）
- RAC+ADG apply PSU+PATCH
- MAA
- Oracle MAA--Maximum Availability Architecture
- oracle adg搭建
- 深入理解Oracle RAC 12c
- 深入理解Oracle RAC 12c 笔记
- Oracle 12c RAC 搭建手册
- Oracle 12c RAC MGMTDB 说明
- Oracle 12c RAC 修改SCAN 配置
- RedHat6.5 安装Oracle 12c RAC
- ORACLE-12C-RAC INSTALL
- RedHat6.5 安装Oracle 12c RAC
- Centos 6.5 安装Oracle 12C RAC
- ORACLE 12C R2 RAC实战首发
- html5的三种样式
- 数据库知名博主
- POJ-1694（贪心）
- iOS 开发基础
- ZOJ 3864 Quiz for EXO-L
- ORACLE 12C RAC ADG（MAA)
- 采用后台绘图方法绘制完成后要释放内存
- 理解“统一编址与独立编址”，“I/O端口与I/O内存”
- LeetCode_Remove Duplicates from Sorted List II
- android计算器设计总结
- uva12235 状压dp
- HDU 1829 A Bug's Life
- .net中XML编程总结
- ubuntu初步配置
