20161101 ADG
%%%%%%%%%%%%%%%initprod.ora%%%%%%%%%%%%%%%%%%%%%%%%%
....
*.log_file_name_convert='/u01/app/oracle/oracle/standby','/u01/app/oracle/oracle/prod'
#前面的值表示转换前的形式，后面的值表示转换后的形式
....
1 环境
hpcs01 单机 节点1
db_name=shihfa
db_unique_name=shihfa1
sid=shihfa
IP 133.224.218.60
hpcs02 单机 节点2
db_name=shihfa
db_unique_name=shihfa2
sid=shihfa
ip 133.224.218.61
2 2个节点本地监听和网络配置listener.ora tnsnames.ora
#hpcs01
#listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = shihfa)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1)
(SID_NAME = shihfa)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 133.224.218.60)(PORT = 1521))
)
ADR_BASE_LISTENER = /oracle/app/oracle
#tnsnames.ora
SHIHFA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 133.224.218.60)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = shihfa)
)
)
SHIHFA2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 133.224.218.61)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = shihfa)
)
)
#hpcs02
#listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = shihfa)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1)
(SID_NAME = shihfa)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 133.224.218.61)(PORT = 1521))
)
ADR_BASE_LISTENER = /oracle/app/oracle
#tnsnames.ora
SHIHFA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 133.224.218.60)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = shihfa)
)
)
SHIHFA2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 133.224.218.61)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = shihfa)
)
)
3 参数文件
节点1hpcs01
3组日志，每组日志大小100m
oracle@hpcs01[/home/oracle]$sqlplus / as sysdba
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> alter system set db_unique_name='shihfa1' scope=spfile;
System altered.
SQL> alter system set log_archive_config='DG_CONFIG=(shihfa1,shihfa2)';
System altered.
SQL> alter system set log_archive_dest_1='LOCATION=/oracle/app/oracle/arch_shihfa/arch1 valid_for=(all_logfiles,primary_role) db_unique_name=shihfa1' scope=spfile;
System altered.
SQL> alter system set log_archive_dest_2='SERVICE=shihfa2 lgwr async valid_for=(online_logfile,primary_role) db_unique_name=shihfa2';
System altered.
SQL> alter system set log_archive_dest_3='LOCATION=/oracle/app/oracle/arch_shihfa/stdbylog valid_for=(standby_logfile,standby_role) db_unique_name=shihfa1' scope=spfile;
System altered.
SQL> alter system set fal_client='shihfa1';
System altered.
SQL> alter system set fal_server='shihfa2';
System altered.
SQL> alter system set standby_file_management=auto;
System altered.
SQL> alter database add standby logfile group 5 '/oracle/app/oracle/oradata/shihfa/ora_stdby01.dbf' size 100M;
Database altered.
SQL> alter database add standby logfile group 6 '/oracle/app/oracle/oradata/shihfa/ora_stdby02.dbf' size 100M;
Database altered.
SQL> alter database add standby logfile group 7 '/oracle/app/oracle/oradata/shihfa/ora_stdby03.dbf' size 100M;
Database altered.
SQL> alter database add standby logfile group 8 '/oracle/app/oracle/oradata/shihfa/ora_stdby04.dbf' size 100M;
Database altered.
SQL> alter system set log_file_name_convert='/oracle/app/oracle/oradata/shihfa/','/oracle/app/oracle/oradata/shihfa/' scope=spfile;
System altered.
SQL> alter system set db_file_name_convert='/oracle/app/oracle/oradata/shihfa/','/oracle/app/oracle/oradata/shihfa/' scope=spfile;
System altered.
shutdown immediate
startup mount
alter database archvielog
alter database open
SQL> create pfile='/tmp/initshihfa.ora' from spfile;
File created.
oracle@hpcs01[/home/oracle]$cat /tmp/initshihfa.ora
shihfa.__db_cache_size=2449473536
shihfa.__java_pool_size=67108864
shihfa.__large_pool_size=83886080
shihfa.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
shihfa.__pga_aggregate_target=2147483648
shihfa.__sga_target=3221225472
shihfa.__shared_io_pool_size=0
shihfa.__shared_pool_size=587202560
shihfa.__streams_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/shihfa/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oracle/app/oracle/oradata/shihfa/control01.ctl','/oracle/app/oracle/oradata/shihfa/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/oracle/app/oracle/oradata/shihfa/','/oracle/app/oracle/oradata/shihfa/'
*.db_name='shihfa'
*.db_unique_name='shihfa1'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=shihfaXDB)'
*.fal_client='shihfa1'
*.fal_server='shihfa2'
*.log_archive_config='DG_CONFIG=(shihfa1,shihfa2)'
*.log_archive_dest_1='LOCATION=/oracle/app/oracle/arch_shihfa/arch1 valid_for=(all_logfiles,primary_role) db_unique_name=shihfa1'
*.log_archive_dest_2='SERVICE=shihfa2 lgwr async valid_for=(online_logfile,primary_role) db_unique_name=shihfa2'
*.log_archive_dest_3='LOCATION=/oracle/app/oracle/arch_shihfa/stdbylog valid_for=(standby_logfile,standby_role) db_unique_name=shihfa1'
*.log_file_name_convert='/oracle/app/oracle/oradata/shihfa/','/oracle/app/oracle/oradata/shihfa/'
*.memory_target=5368709120
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=555
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
拷贝节点1的pfile文件和口令文件到二节点hpcs02
更改传到二节点pfile文件如下
oracle@hpcs02[/home/oracle]$cat /tmp/initshihfa.ora
*.audit_file_dest='/oracle/app/oracle/admin/shihfa/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oracle/app/oracle/oradata/shihfa/control01.ctl','/oracle/app/oracle/oradata/shihfa/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/oracle/app/oracle/oradata/shihfa/','/oracle/app/oracle/oradata/shihfa/'
*.db_name='shihfa'
*.db_unique_name='shihfa2'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=shihfaXDB)'
*.fal_client='shihfa1'
*.fal_server='shihfa2'
*.log_archive_config='DG_CONFIG=(shihfa1,shihfa2)'
*.log_archive_dest_1='LOCATION=/oracle/app/oracle/arch_shihfa/arch1 valid_for=(all_logfiles,primary_role) db_unique_name=shihfa2'
*.log_archive_dest_2='SERVICE=shihfa1 lgwr async valid_for=(online_logfile,primary_role) db_unique_name=shihfa1'
*.log_archive_dest_3='LOCATION=/oracle/app/oracle/arch_shihfa/stdbylog valid_for=(standby_logfile,standby_role) db_unique_name=shihfa2'
*.log_file_name_convert='/oracle/app/oracle/oradata/shihfa/','/oracle/app/oracle/oradata/shihfa/'
*.memory_target=5368709120
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=555
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
oracle@hpcs02[/home/oracle]$sqlplus / as sysdba
SQL> create spfile from pfile='/tmp/initshihfa.ora'
4 恢复
hpcs01 恢复操作
oracle@hpcs01[/home/oracle]$export ORACLE_SID=shihfa
oracle@hpcs01[/home/oracle]$rman target sys/oracle@shihfa1 auxiliary sys/oracle@shihfa2
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 1 09:23:06 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: SHIHFA (DBID=2006774421)
connected to auxiliary database: SHIHFA (not mounted)
RMAN> duplicate target database for standby nofilenamecheck from active database;
Starting Duplicate Db at 01-NOV-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=296 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/oracle/app/oracle/product/11.2.0/db_1/dbs/orapwshihfa' auxiliary format
'/oracle/app/oracle/product/11.2.0/db_1/dbs/orapwshihfa' ;
}
executing Memory Script
Starting backup at 01-NOV-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=688 device type=DISK
Finished backup at 01-NOV-17
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/oracle/app/oracle/oradata/shihfa/control01.ctl';
restore clone controlfile to '/oracle/app/oracle/oradata/shihfa/control02.ctl' from
'/oracle/app/oracle/oradata/shihfa/control01.ctl';
}
executing Memory Script
Starting backup at 01-NOV-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_shihfa.f tag=TAG20171101T092324 RECID=1 STAMP=958901005
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 01-NOV-17
Starting restore at 01-NOV-17
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 01-NOV-17
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/oracle/app/oracle/oradata/shihfa/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/oracle/app/oracle/oradata/shihfa/system01.dbf";
set newname for datafile 2 to
"/oracle/app/oracle/oradata/shihfa/sysaux01.dbf";
set newname for datafile 3 to
"/oracle/app/oracle/oradata/shihfa/undotbs01.dbf";
set newname for datafile 4 to
"/oracle/app/oracle/oradata/shihfa/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/oracle/app/oracle/oradata/shihfa/system01.dbf" datafile
2 auxiliary format
"/oracle/app/oracle/oradata/shihfa/sysaux01.dbf" datafile
3 auxiliary format
"/oracle/app/oracle/oradata/shihfa/undotbs01.dbf" datafile
4 auxiliary format
"/oracle/app/oracle/oradata/shihfa/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oracle/app/oracle/oradata/shihfa/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 01-NOV-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/oracle/app/oracle/oradata/shihfa/undotbs01.dbf
output file name=/oracle/app/oracle/oradata/shihfa/undotbs01.dbf tag=TAG20171101T092335
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oracle/app/oracle/oradata/shihfa/system01.dbf
output file name=/oracle/app/oracle/oradata/shihfa/system01.dbf tag=TAG20171101T092335
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oracle/app/oracle/oradata/shihfa/sysaux01.dbf
output file name=/oracle/app/oracle/oradata/shihfa/sysaux01.dbf tag=TAG20171101T092335
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oracle/app/oracle/oradata/shihfa/users01.dbf
output file name=/oracle/app/oracle/oradata/shihfa/users01.dbf tag=TAG20171101T092335
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-NOV-17
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=958901123 file name=/oracle/app/oracle/oradata/shihfa/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=958901123 file name=/oracle/app/oracle/oradata/shihfa/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=958901123 file name=/oracle/app/oracle/oradata/shihfa/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=958901123 file name=/oracle/app/oracle/oradata/shihfa/users01.dbf
Finished Duplicate Db at 01-NOV-17
RMAN>
在复制操作过程后 在1节点切换多组日志 二节点显示如下
oracle@hpcs02[/home/oracle]$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 1 09:27:03 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, OLAP and Data Mining options
SQL> select open_mode,database_role,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
MOUNTED PHYSICAL STANDBY TO PRIMARY
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oracle/app/oracle/oradata/shihfa/system01.dbf'
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> select open_mode,database_role,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ ONLY PHYSICAL STANDBY NOT ALLOWED
SQL> alter database recover managed standby database using current logfile disconnect ;
Database altered.
SQL> select open_mode,database_role,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
浙公网安备 33010602011771号