有客户需要RAC ASM环境到单机ASM环境的创建步骤，由于MOS上的英文note中没有提及对这个过程中可能遇到的问题如何解决，故这里讲全部创建步骤和问题解决记录如下以供参考。
一、环境：
1、Primary
11204两节点rac
[oracle@rac11g1 ~]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
192.168.56.18 rac11g1.fsm.com rac11g1
192.168.56.19 rac11g2.fsm.com rac11g2
192.168.210.18 rac11g1-priv.fsm.com rac11g1-priv
192.168.210.19 rac11g2-priv.fsm.com rac11g2-priv
192.168.56.28 rac11g1-vip.fsm.com rac11g1-vip
192.168.56.29 rac11g2-vip.fsm.com rac11g2-vip
192.168.56.8 rac11g-cluster.fsm.com rac11g-cluster
192.168.56.118 restart.oracle.com restart
[oracle@rac11g1 ~]$
[grid@rac11g1 ~]$ crsctl stat res -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.CRS.dg
ONLINE ONLINE rac11g1
ONLINE ONLINE rac11g2
ora.DATA.dg
ONLINE ONLINE rac11g1
ONLINE ONLINE rac11g2
ora.LISTENER.lsnr
ONLINE ONLINE rac11g1
ONLINE ONLINE rac11g2
ora.asm
ONLINE ONLINE rac11g1 Started
ONLINE ONLINE rac11g2 Started
ora.gsd
OFFLINE OFFLINE rac11g1
OFFLINE OFFLINE rac11g2
ora.net1.network
ONLINE ONLINE rac11g1
ONLINE ONLINE rac11g2
ora.ons
ONLINE ONLINE rac11g1
ONLINE ONLINE rac11g2
ora.registry.acfs
ONLINE ONLINE rac11g1
ONLINE ONLINE rac11g2
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE rac11g1
ora.cvu
1 ONLINE ONLINE rac11g1
ora.db11g.db
1 ONLINE ONLINE rac11g1 Open
2 ONLINE ONLINE rac11g2 Open
ora.oc4j
1 ONLINE ONLINE rac11g1
ora.rac11g1.vip
1 ONLINE ONLINE rac11g1
ora.rac11g2.vip
1 ONLINE ONLINE rac11g2
ora.scan1.vip
1 ONLINE ONLINE rac11g1
2、Standby
单节点Oracle Restart环境
-bash-3.2$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
192.168.56.118 restart.oracle.com restart
192.168.56.8 rac11g-cluster.fsm.com rac11g-cluster
192.168.56.28 rac11g1-vip.fsm.com rac11g1-vip
192.168.56.29 rac11g2-vip.fsm.com rac11g2-vip
二、配置步骤
1、配置tnsnames，三个主机保持一致：
DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac11g-cluster)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db11g)
)
)
DB11G1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac11g1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db11g)
(instance_name=db11g1)
)
)
DB11G2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac11g2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db11g)
(instance_name=db11g2)
)
)
DB11G_stby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = restart)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db11g_stby)
)
)
2、配置备库静态监听
-bash-3.2$ su – grid
Password:
-bash-3.2$ which crsctl
/u01/app/11.2.0/grid/bin/crsctl
-bash-3.2$ vi /u01/app/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = restart.oracle.com)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=db11g_stby)
(SID_NAME=db11g_stby)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
)
)
ADR_BASE_LISTENER = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
<=============注：ORACLE_HOME=一定要写RDBMS home的路径，否则会造成备库sqlplus sys/oracle@db11g_stby as sysdba报错ORA-01017
-bash-3.2$ srvctl stop listener
-bash-3.2$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is not running
-bash-3.2$ srvctl start listener
-bash-3.2$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 10-JUN-2017 08:34:47
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 – Production
Start Date 10-JUN-2017 08:34:41
Uptime 0 days 0 hr. 0 min. 6 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/restart/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=restart.oracle.com)(PORT=1521)))
Services Summary…
Service “db11g_stby” has 1 instance(s).
Instance “db11g_stby”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
-bash-3.2$
3、同步密码文件
[oracle@rac11g1 ~]$ scp /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdb11g1 restart:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdb11g_stby
oracle@restart’s password:
orapwdb11g1 100% 1536 1.5KB/s 00:00
[oracle@rac11g1 ~]$
使用密码文件登录主库两节点，发现1成功，2失败：
<<<备库
-bash-3.2$ sqlplus sys/oracle@db11g1 as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 10 08:38:05 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and OLAP options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and OLAP options
-bash-3.2$ sqlplus sys/oracle@db11g2 as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 10 08:38:10 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12537: TNS:connection closed
Enter user-name: ^C^[[A
<<<主库节点1
[oracle@rac11g1 admin]$ sqlplus sys/oracle@db11g2 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jun 10 08:42:09 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12537: TNS:connection closed
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
在节点2使用grid用户访问rdbms的oracle binary文件，发现无权限：
[oracle@rac11g2 ~]$ su – grid
Password:
[grid@rac11g2 ~]$ ls -l /home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/oracle
ls: /home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/oracle: Permission denied
但该文件权限没问题：
[oracle@rac11g2 ~]$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s–x 1 oracle oinstall 239626943 Mar 19 2016 /home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/oracle
进一步检查发现问题出自底层目录缺少组权限：
<<<节点2
[grid@rac11g2 ~]$ ll /home
total 16
drwxr-xr-x 3 root oinstall 4096 Mar 19 2016 11.2.0
drwxr-xr-x 7 grid oinstall 4096 Mar 19 2016 grid
drwx—— 4 oracle oinstall 4096 Jun 10 08:27 oracle 《—-
[grid@rac11g2 ~]$ su – oracle
<<<节点1
[grid@rac11g1 ~]$ ll /home
total 16
drwxr-xr-x 3 root oinstall 4096 Mar 19 2016 11.2.0
drwxr-xr-x 7 grid oinstall 4096 Mar 19 2016 grid
drwxr-xr-x 5 oracle oinstall 4096 Mar 19 2016 oracle
[grid@rac11g1 ~]$
参照节点1权限进行授权后，问题解决：
[oracle@rac11g2 ~]$ chmod 755 /home/oracle
[oracle@rac11g2 ~]$ ll /home
total 16
drwxr-xr-x 3 root oinstall 4096 Mar 19 2016 11.2.0
drwxr-xr-x 7 grid oinstall 4096 Mar 19 2016 grid
drwxr-xr-x 4 oracle oinstall 4096 Jun 10 08:27 oracle
[oracle@rac11g2 ~]$ exit
logout
[grid@rac11g2 ~]$ ls -l /home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/oracle
-rwsr-s–x 1 oracle oinstall 239626943 Mar 19 2016 /home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/oracle
[grid@rac11g2 ~]$
<<<备库
-bash-3.2$ sqlplus sys/oracle@db11g as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 10 09:04:10 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and OLAP options
SQL>
-bash-3.2$ sqlplus sys/oracle@db11g1 as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 10 09:04:40 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and OLAP options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and OLAP options
-bash-3.2$ sqlplus sys/oracle@db11g2 as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 10 09:04:45 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and OLAP options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and OLAP options
[oracle@rac11g1 ~]$ sqlplus sys/oracle@db11g_stby as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jun 10 11:28:39 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL>
4、创建standby controlfile，并拷贝到备库
[oracle@rac11g1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jun 10 09:08:09 2017
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, Real Application Clusters, Automatic Storage Management and OLAP options
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/standbyctl.ctl’;
Database altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and OLAP options
[oracle@rac11g1 admin]$ scp /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/standbyctl.ctl restart:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
oracle@restart’s password:
standbyctl.ctl 100% 18MB 17.6MB/s 00:01
[oracle@rac11g1 admin]$
5、创建备库的pfile
在主库spfile的基础上修改出备库的pfile：
SQL> create pfile=’/home/oracle/pfile.txt’ from spfile;
File created.
vi /home/oracle/pfile.txt
*.audit_file_dest=’/home/oracle/app/oracle/admin/db11g_stby/adump’
*.audit_trail=’db’
*.cluster_database=false
*.control_files=’/u01/app/oracle/product/11.2.0/dbhome_1/dbs/standbyctl.ctl ‘
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_domain=”
*.db_name=’db11g’
*.db_unique_name=’db11g_stby‘
*.diagnostic_dest=‘/u01/app/oracle’
*.log_archive_dest_1=’LOCATION=+DATA’
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’exclusive’
*.thread=1
*.undo_tablespace=’UNDOTBS1′
*.standby_file_management=’AUTO’
*.fal_server=’db11g1′,’db11g2′
*.log_archive_config=’DG_CONFIG=(db11g,db11g_stby)’
*.log_archive_dest_2=’SERVICE=db11g NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db11g’
*.log_archive_dest_state_2=’ENABLE’
*.fal_client=’dg_stby’
*.db_file_name_convert=’+DATA/db11g’,’+DATA/db11g_stby’
*.log_file_name_convert=’+DATA/db11g’,’+DATA/db11g_stby’
保存pfile到备库，并使用它创建备库的spfile，创建audit目录并启动实例到nomount状态：
-bash-3.2$ export ORACLE_SID=db11g_stby
-bash-3.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 10 10:20:35 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile=’/u01/app/oracle/pfile.txt’;
File created.
SQL> !mkdir -p /home/oracle/app/oracle/admin/db11g_stby/adump
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 490737024 bytes
Database Buffers 339738624 bytes
Redo Buffers 2396160 bytes
SQL>
6、duplicate备库
在主库上：
[oracle@rac11g1 ~]$ export ORACLE_SID=db11g1
[oracle@rac11g1 ~]$ rman target / auxiliary sys/oracle@db11g_stby
Recovery Manager: Release 11.2.0.4.0 – Production on Sat Jun 10 11:31:45 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB11G (DBID=376554982)
PL/SQL package SYS.DBMS_BACKUP_RESTORE version 11.02.00.03 in AUXILIARY database is not current
PL/SQL package SYS.DBMS_RCVMAN version 11.02.00.03 in AUXILIARY database is not current
connected to auxiliary database: DB11G (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;
Starting Duplicate Db at 10-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=24 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile ‘/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdb11g1’ auxiliary format
‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdb11g_stby’ ;
}
executing Memory Script
Starting backup at 10-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 instance=db11g1 device type=DISK
Finished backup at 10-JUN-17
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/standbyctl.ctl ‘;
}
executing Memory Script
Starting backup at 10-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_db11g1.f tag=TAG20170610T113324 RECID=2 STAMP=946294405
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 10-JUN-17
contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/10/2017 11:33:29
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 06/10/2017 11:33:29
RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby database
ORA-00201: control file version 11.2.0.4.0 incompatible with ORACLE version 11.2.0.0.0
ORA-00202: control file: ‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/standbyctl.ctl ‘
RMAN>
<===========这里证明了11204对11203无法做dataguard，data guard是不能跨版本的（物理备库转成逻辑备库后可用于滚动升级） 。
升级备库的GI和rdbms到11204版本后，上述问题消失：
<<<备库
SQL> alter system set compatible=’11.2.0.4.0′ scope=spfile;
System altered.
SQL> shutdown immediate
startup noORA-01507: database not mounted
mount
ORACLE instance shut down.
SQL> ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 490736720 bytes
Database Buffers 339738624 bytes
Redo Buffers 2371584 bytes
SQL>
<<<主库
[oracle@rac11g1 ~]$ rman target / auxiliary sys/oracle@db11g_stby
Recovery Manager: Release 11.2.0.4.0 – Production on Sat Jun 10 14:09:15 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB11G (DBID=376554982)
connected to auxiliary database: DB11G (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;
Starting Duplicate Db at 10-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=25 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile ‘/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdb11g1’ auxiliary format
‘/u01/app/oracle/product/11.2.0/dbhome_2/dbs/orapwdb11g_stby’ ;
}
executing Memory Script
Starting backup at 10-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 instance=db11g1 device type=DISK
Finished backup at 10-JUN-17
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/standbyctl.ctl ‘;
}
executing Memory Script
Starting backup at 10-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_db11g1.f tag=TAG20170610T140924 RECID=4 STAMP=946303765
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 10-JUN-17
contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 1 to
“+data”;
switch clone tempfile all;
set newname for datafile 1 to
“+data”;
set newname for datafile 2 to
“+data”;
set newname for datafile 3 to
“+data”;
set newname for datafile 4 to
“+data”;
set newname for datafile 5 to
“+data”;
backup as copy reuse
datafile 1 auxiliary format
“+data” datafile
2 auxiliary format
“+data” datafile
3 auxiliary format
“+data” datafile
4 auxiliary format
“+data” datafile
5 auxiliary format
“+data” ;
sql ‘alter system archive log current’;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +data in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 10-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/db11g/datafile/system.256.906885291
output file name=+DATA/db11g_stby/datafile/system.260.946303775 tag=TAG20170610T140935
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/db11g/datafile/sysaux.257.906885293
output file name=+DATA/db11g_stby/datafile/sysaux.265.946303801 tag=TAG20170610T140935
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/db11g/datafile/undotbs1.258.906885293
output file name=+DATA/db11g_stby/datafile/undotbs1.264.946303817 tag=TAG20170610T140935
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/db11g/datafile/undotbs2.264.906885597
output file name=+DATA/db11g_stby/datafile/undotbs2.263.946303819 tag=TAG20170610T140935
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=+DATA/db11g/datafile/users.259.906885293
output file name=+DATA/db11g_stby/datafile/users.262.946303821 tag=TAG20170610T140935
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 10-JUN-17
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like “+DATA/db11g/archivelog/2017_06_10/thread_1_seq_9.273.946303823” auxiliary format
“+DATA” archivelog like
“+DATA/db11g/archivelog/2017_06_10/thread_1_seq_7.270.946297317” auxiliary format
“+DATA” archivelog like
“+DATA/db11g/archivelog/2017_06_10/thread_1_seq_8.272.946302773” auxiliary format
“+DATA” archivelog like
“+DATA/db11g/archivelog/2017_06_10/thread_2_seq_5.274.946303823” auxiliary format
“+DATA” ;
catalog clone start with “+DATA”;
switch clone datafile all;
}
executing Memory Script
Starting backup at 10-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=9 RECID=7 STAMP=946303824
output file name=+DATA/db11g_stby/archivelog/2017_06_10/thread_1_seq_9.261.946303825 RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=7 RECID=4 STAMP=946297316
output file name=+DATA/db11g_stby/archivelog/2017_06_10/thread_1_seq_7.259.946303827 RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=8 RECID=6 STAMP=946302773
output file name=+DATA/db11g_stby/archivelog/2017_06_10/thread_1_seq_8.258.946303827 RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=5 RECID=8 STAMP=946303824
output file name=+DATA/db11g_stby/archivelog/2017_06_10/thread_2_seq_5.257.946303829 RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 10-JUN-17
searching for all files that match the pattern +DATA
List of Files Unknown to the Database
=====================================
File Name: +data/DB11G_STBY/ARCHIVELOG/2017_06_10/thread_1_seq_9.261.946303825
File Name: +data/DB11G_STBY/ARCHIVELOG/2017_06_10/thread_1_seq_7.259.946303827
File Name: +data/DB11G_STBY/ARCHIVELOG/2017_06_10/thread_1_seq_8.258.946303827
File Name: +data/DB11G_STBY/ARCHIVELOG/2017_06_10/thread_2_seq_5.257.946303829
File Name: +data/DB11G_STBY/DATAFILE/SYSTEM.260.946303775
File Name: +data/DB11G_STBY/DATAFILE/SYSAUX.265.946303801
File Name: +data/DB11G_STBY/DATAFILE/UNDOTBS1.264.946303817
File Name: +data/DB11G_STBY/DATAFILE/UNDOTBS2.263.946303819
File Name: +data/DB11G_STBY/DATAFILE/USERS.262.946303821
File Name: +data/ASM/ASMPARAMETERFILE/REGISTRY.253.904020973
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: +data/DB11G_STBY/ARCHIVELOG/2017_06_10/thread_1_seq_9.261.946303825
File Name: +data/DB11G_STBY/ARCHIVELOG/2017_06_10/thread_1_seq_7.259.946303827
File Name: +data/DB11G_STBY/ARCHIVELOG/2017_06_10/thread_1_seq_8.258.946303827
File Name: +data/DB11G_STBY/ARCHIVELOG/2017_06_10/thread_2_seq_5.257.946303829
File Name: +data/DB11G_STBY/DATAFILE/SYSTEM.260.946303775
File Name: +data/DB11G_STBY/DATAFILE/SYSAUX.265.946303801
File Name: +data/DB11G_STBY/DATAFILE/UNDOTBS1.264.946303817
File Name: +data/DB11G_STBY/DATAFILE/UNDOTBS2.263.946303819
File Name: +data/DB11G_STBY/DATAFILE/USERS.262.946303821
List of Files Which Where Not Cataloged
=======================================
File Name: +data/ASM/ASMPARAMETERFILE/REGISTRY.253.904020973
RMAN-07518: Reason: Foreign database file DBID: 0 Database Name:
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=946303830 file name=+DATA/db11g_stby/datafile/system.260.946303775
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=946303830 file name=+DATA/db11g_stby/datafile/sysaux.265.946303801
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=946303830 file name=+DATA/db11g_stby/datafile/undotbs1.264.946303817
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=946303830 file name=+DATA/db11g_stby/datafile/users.262.946303821
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=946303830 file name=+DATA/db11g_stby/datafile/undotbs2.263.946303819
contents of Memory Script:
{
set until scn 1105997;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 10-JUN-17
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file +DATA/db11g_stby/archivelog/2017_06_10/thread_1_seq_9.261.946303825
archived log for thread 2 with sequence 5 is already on disk as file +DATA/db11g_stby/archivelog/2017_06_10/thread_2_seq_5.257.946303829
archived log file name=+DATA/db11g_stby/archivelog/2017_06_10/thread_1_seq_9.261.946303825 thread=1 sequence=9
archived log file name=+DATA/db11g_stby/archivelog/2017_06_10/thread_2_seq_5.257.946303829 thread=2 sequence=5
media recovery complete, elapsed time: 00:00:00
Finished recover at 10-JUN-17
Finished Duplicate Db at 10-JUN-17
RMAN>
7、恢复备库控制文件到ASM
<<<备库
SQL> show parameter control
NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/standbyctl.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> alter system set control_files=’+DATA’ scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 490736720 bytes
Database Buffers 339738624 bytes
Redo Buffers 2371584 bytes
SQL> exit
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
-bash-3.2$ rman target /
Recovery Manager: Release 11.2.0.4.0 – Production on Sat Jun 10 14:23:17 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB11G (not mounted)
RMAN> restore controlfile from ‘/u01/app/oracle/product/11.2.0/dbhome_2/dbs/standbyctl.ctl’;
Starting restore at 10-JUN-2017 14:23:40
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/db11g_stby/controlfile/current.269.946304621
Finished restore at 10-JUN-2017 14:23:42
RMAN>
8、配置主库参数
[oracle@rac11g1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jun 10 14:31:22 2017
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, Real Application Clusters, Automatic Storage Management and OLAP options
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’DG_CONFIG=(db11g,db11g_stby)’;
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db11g_stby’;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET FAL_SERVER=db11g_stby;
alter system set STANDBY_FILE_MANAGEMENT=auto;
ALTER SYSTEM SET FAL_CLIENT=db11g1 sid=’db11g1′;
ALTER SYSTEM SET FAL_CLIENT=db11g2 sid=’db11g2′;
System altered.
SQL>
Database altered.
SQL>
System altered.
SQL>
System altered.
SQL>
System altered.
SQL>
System altered.
SQL> SQL>
System altered.
SQL>
System altered.
SQL> select FORCE_LOGGING from gv$database;
FOR
—
YES
YES
9、在主备库创建standby log file
<<<主库
SQL> alter database add standby logfile thread 1 group 5 ‘+DATA’ size 50m;
alter database add standby logfile thread 2 group 6 ‘+DATA’ size 50m;
alter database add standby logfile thread 1 group 7 ‘+DATA’ size 50m;
alter database add standby logfile thread 2 group 8 ‘+DATA’ size 50m;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
<<<备库
SQL> alter database add standby logfile thread 1 group 5 ‘+DATA’ size 50m;
alter database add standby logfile thread 2 group 5 ‘+DATA’ size 50m;
Database altered.
SQL> alter database add standby logfile thread 2 group 6 ‘+DATA’ size 50m;
alter database add standby logfile thread 1 group 7 ‘+DATA’ size 50m;
alter database add standby logfile thread 2 group 8 ‘+DATA’ size 50m;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
10、将备库置于active dataguard模式下
<<<备库
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL>
11、添加备库资源到OCR
-bash-3.2$ srvctl add database -d db11g_stby -o /u01/app/oracle/product/11.2.0/dbhome_2 -p /u01/app/oracle/product/11.2.0/dbhome_2/dbs/spfiledb11g_stby.ora
-bash-3.2$
12、检查主备库状态
<<<备库
SQL> select process,status,sequence#,thread# from v$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
——— ———— ———- ———-
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CLOSING 30 1
ARCH CONNECTED 0 0
RFS IDLE 0 0
RFS IDLE 31 1
RFS IDLE 0 0
MRP0 APPLYING_LOG 31 1
8 rows selected.
SQL> SELECT ‘Last Applied : ‘ Logs,
2 TO_CHAR(next_time,’DD-MON-YY:HH24:MI:SS’) TIME,thread#,sequence#
3 FROM v$archived_log
4 WHERE sequence# =
5 (SELECT MAX(sequence#) FROM v$archived_log WHERE applied=’YES’
6 )
7 UNION
8 SELECT ‘Last Received : ‘ Logs,
9 TO_CHAR(next_time,’DD-MON-YY:HH24:MI:SS’) TIME,thread#,sequence#
10 FROM v$archived_log
11 WHERE sequence# =
12 (SELECT MAX(sequence#) FROM v$archived_log );
LOGS TIME THREAD# SEQUENCE#
—————- ——————— ———- ———-
Last Applied : 12-JUN-17:13:55:06 1 29
Last Received : 12-JUN-17:14:00:18 1 30
<<<主库
SQL> SELECT
2 (SELECT name FROM V$DATABASE
3 ) name,
4 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
5 ) Current_primary_seq,
6 (SELECT MAX (sequence#)
7 FROM v$archived_log
8 WHERE TRUNC(next_time) > SYSDATE – 1
9 AND dest_id = 2
10 ) max_stby,
11 (SELECT NVL (
12 (SELECT MAX (sequence#) – MIN (sequence#)
13 FROM v$archived_log
14 WHERE TRUNC(next_time) > SYSDATE – 1
15 AND dest_id = 2
16 AND applied = ‘NO’
17 ), 0)
18 FROM DUAL
19 ) “To be applied”,
20 (
21 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 1
22 ) –
23 (SELECT MAX (sequence#) FROM v$archived_log WHERE dest_id = 2
24 )) “To be Shipped”
25 FROM DUAL;
NAME CURRENT_PRIMARY_SEQ MAX_STBY To be applied To be Shipped
——— ——————- ———- ————- ————-
DB11G 30 30 0 0