1.check the force_logging(primary)
select FORCE_LOGGING from v$database;
ALTER DATABASE FORCE LOGGING;
select FORCE_LOGGING from v$database;
2.archive mode(primary)
archive log list
4.add the standby logfile(primary)
4.1 check the size of the online redologfile
select group#,thread#,bytes/1024/1024 fromv$log order by 2,1;
----check the number of the standby redolog
SELECT GROUP#,thread#, BYTES/1024/1024 FROMV$STANDBY_LOG;
4.2 add the standby logfile for everythread.
----thread 1
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1group 21 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1group 22 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1group 23 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1group 24 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1group 25 SIZE 512M;
----thread 2
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2group 26 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2group 27 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2group 28 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2group 29 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2group 30 SIZE 512M;
----thread 3
ALTER DATABASE ADD STANDBY LOGFILE THREAD 3group 31 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 3group 32 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 3group 33 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 3group 34 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 3group 35 SIZE 512M;
------------
SELECT GROUP#,thread#, BYTES/1024/1024 FROMV$STANDBY_LOG order by 2,1;
#configure the tnsnames.ora for each node:
vi /var/opt/grid/tnsnames.ora
----------------------------------
ndcukomsp1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP)(HOST = ndc-uk-scan-pr1.prod.gecwalmart.com)(PORT = 61290))
(CONNECT_DATA =
(SERVER =DEDICATED)
(SERVICE_NAME = ndcukomsp1.gecwalmart.com)
)
)
ggukomsp1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP)(HOST = ndc-uk-scan-gg-pr2.prod.walmart.com)(PORT = 61290))
(CONNECT_DATA =
(SERVER =DEDICATED)
(SERVICE_NAME = ggukomsp1.gecwalmart.com)
)
)
#check listener.ora
#check soft link:
[oracledb@ndc-bd07-dl580-05 admin]$ ls -ltr
total 32
-rw-r--r-- 1 oracledb oinstall 205May 11 2011 shrept.lst
drwxr-xr-x 2 oracledb oinstall 4096 Jun20 2012 samples
-rw-r----- 1 oracledb oinstall 697Oct 27 2012 tnsnames.ora.old
lrwxrwxrwx 1 oracledb oinstall 26 Jun 17 19:09 listener.ora -> /var/opt/grid/listener.ora
lrwxrwxrwx 1 oracledb oinstall 26 Jun 17 19:10 tnsnames.ora -> /var/opt/grid/tnsnames.ora
#check remote_listener
#check local_listener
#check if locat listener works fine.
--------------------------------------
5.modify the parameter on the primarydatabase.
show parameter db_unique_name
show parameter LOG_ARCHIVE_CONFIG
alter system setLOG_ARCHIVE_CONFIG='DG_CONFIG=(ndcukomsp1,ggukomsp1)';
defer the log_archive_dest_state,except thelog_archive_dest_state_1
---
alter system setlog_archive_dest_state_10=defer;
alter system setlog_archive_dest_state_11=defer;
alter system setlog_archive_dest_state_12=defer;
alter system set log_archive_dest_state_13=defer;
alter system setlog_archive_dest_state_14=defer;
alter system setlog_archive_dest_state_15=defer;
alter system setlog_archive_dest_state_16=defer;
alter system setlog_archive_dest_state_17=defer;
alter system set log_archive_dest_state_18=defer;
alter system setlog_archive_dest_state_19=defer;
alter system setlog_archive_dest_state_2=defer;
alter system setlog_archive_dest_state_20=defer;
alter system setlog_archive_dest_state_21=defer;
alter system setlog_archive_dest_state_22=defer;
alter system setlog_archive_dest_state_23=defer;
alter system setlog_archive_dest_state_24=defer;
alter system setlog_archive_dest_state_25=defer;
alter system setlog_archive_dest_state_26=defer;
alter system setlog_archive_dest_state_27=defer;
alter system setlog_archive_dest_state_28=defer;
alter system setlog_archive_dest_state_29=defer;
alter system setlog_archive_dest_state_3=defer;
alter system setlog_archive_dest_state_30=defer;
alter system setlog_archive_dest_state_31=defer;
alter system setlog_archive_dest_state_4=defer;
alter system setlog_archive_dest_state_5=defer;
alter system setlog_archive_dest_state_6=defer;
alter system setlog_archive_dest_state_7=defer;
alter system setlog_archive_dest_state_8=defer;
alter system setlog_archive_dest_state_9=defer;
--------------------------------------------------
--show parameter LOG_ARCHIVE_DEST_1
alter system setLOG_ARCHIVE_DEST_1='LOCATION=+FRA01/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=ndcukomsp1';
--show parameter LOG_ARCHIVE_DEST_2
alter system setLOG_ARCHIVE_DEST_2='service=ggukomsp1 LGWR ASYNC NOAFFIRM delay=0 optionalcompression=disable max_failure=0 max_connections=1 reopen=30db_unique_name=ggukomsp1 net_timeout=120 valid_for=(all_logfiles,primary_role)';
alter system set fal_client='ndcukomsp1';
alter system set fal_server='ggukomsp1';
alter system setstandby_file_management=auto; #
--------
scp the password file to all the ndc,edcnodes and rename the password file on each node
------
#on node 1:
scp orapwukomsp11ndc-bd07-dl580-07.prod.gecwalmart.com:/u01/app/oracle/product/11203/dbs/orapwukomsp12
scp orapwukomsp11ndc-bd08-dl580-05.prod.gecwalmart.com:/u01/app/oracle/product/11203/dbs/orapwukomsp13
scp orapwukomsp11ndc-epod008-dl580-5.prod.walmart.com:/u01/app/oracle/product/11203/dbs/orapwukomsp11
scp orapwukomsp11ndc-epod009-dl580-5.prod.walmart.com:/u01/app/oracle/product/11203/dbs/orapwukomsp12
scp orapwukomsp11ndc-epod010-dl580-5.prod.walmart.com:/u01/app/oracle/product/11203/dbs/orapwukomsp13
------
create pfile from spfile(primary)
create pfile='/tmp/rjin/oms.ora' fromspfile;
edit the pfile then copy to edc
#example:
*.audit_file_dest='/u01/app/oracle/dbbase/admin/ggukomsp1/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
#*.control_files='+DATA01/ggukomsp1/controlfile/current.616.818303971','+FRA01/ggukomsp1/controlfile/current.9194.818303971'
*.db_block_size=8192
*.db_create_file_dest='+DATA01'
*.db_create_online_log_dest_1='+DATA01'
*.db_create_online_log_dest_2='+FRA01'
*.db_domain='gecwalmart.com'
*.db_flashback_retention_target=259200
*.db_name='ukomsp1'
*.db_recovery_file_dest='+FRA01'
*.db_recovery_file_dest_size=536870912000
*.db_unique_name='ggukomsp1'
*.diagnostic_dest='/u01/app/oracle/dbbase'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=ukomsp1XDB)'
*.fal_client='ggukomsp1'
*.fal_server='ndcukomsp1'
ukomsp13.instance_number=3
ukomsp11.instance_number=1
ukomsp12.instance_number=2
*.log_archive_config='DG_CONFIG=(ndcukomsp1,ggukomsp1)'
*.log_archive_dest_1='LOCATION=+FRA01/VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ggukomsp1'
*.log_archive_dest_2='service=ndcukomsp1LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0max_connections=1 reopen=30 db_unique_name=ndcukomsp1 net_timeout=120valid_for=(all_logfiles,primary_role)'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_10='DEFER'
*.log_archive_dest_state_11='DEFER'
*.log_archive_dest_state_12='DEFER'
*.log_archive_dest_state_13='DEFER'
*.log_archive_dest_state_14='DEFER'
*.log_archive_dest_state_15='DEFER'
*.log_archive_dest_state_16='DEFER'
*.log_archive_dest_state_17='DEFER'
*.log_archive_dest_state_18='DEFER'
*.log_archive_dest_state_19='DEFER'
*.log_archive_dest_state_2='DEFER'
*.log_archive_dest_state_20='DEFER'
*.log_archive_dest_state_21='DEFER'
*.log_archive_dest_state_22='DEFER'
*.log_archive_dest_state_23='DEFER'
*.log_archive_dest_state_24='DEFER'
*.log_archive_dest_state_25='DEFER'
*.log_archive_dest_state_26='DEFER'
*.log_archive_dest_state_27='DEFER'
*.log_archive_dest_state_28='DEFER'
*.log_archive_dest_state_29='DEFER'
*.log_archive_dest_state_3='DEFER'
*.log_archive_dest_state_30='DEFER'
*.log_archive_dest_state_31='DEFER'
*.log_archive_dest_state_4='DEFER'
*.log_archive_dest_state_5='DEFER'
*.log_archive_dest_state_6='DEFER'
*.log_archive_dest_state_7='DEFER'
*.log_archive_dest_state_8='DEFER'
*.log_archive_dest_state_9='DEFER'
*.log_archive_format='ukomsp1_%t_%s_%r.arc'
*.memory_target=8G
*.open_cursors=300
*.processes=1500
*.remote_listener='ndc-uk-scan-gg-pr2.prod.walmart.com:61290'
*.remote_login_passwordfile='exclusive'
*.sessions=1655
*.sga_target=6G
*.standby_file_management='AUTO'
ukomsp12.thread=2
ukomsp13.thread=3
ukomsp11.thread=1
ukomsp12.undo_tablespace='UNDOTBS3'
ukomsp13.undo_tablespace='UNDOTBS2'
ukomsp11.undo_tablespace='UNDOTBS1'
---rman backup
run
{
allocate channel c1 device typedisk format '/tmp/fred/%d_c1_INCR_0_s%s_p%p';
allocate channel c2 device type diskformat '/tmp/fred/%d_c2_INCR_0_s%s_p%p';
allocate channel c3 device type diskformat '/tmp/fred/%d_c3_INCR_0_s%s_p%p';
allocate channel c4 device type diskformat '/tmp/fred/%d_c4_INCR_0_s%s_p%p';
backup incremental level 0 database;
backup current controlfile forstandby format '/tmp/fred/ctl_%d';
sql 'alter system archive logcurrent';
backup archivelog from time'SYSDATE-1/24' format '/tmp/fred/%d_c1_INCR_0_s%s_p%p_seq%e';
}
cd /tmp/fred
scp *ndc-epod008-dl580-5.prod.walmart.com:/u01/app/ggtrail
---operate on standby
#echo node:
mkdir -p/u01/app/oracle/dbbase/admin/ggukomsp1/adump
export ORACLE_SID=ukomsp1
startup nomount pfile='/tmp/ukomsp1.ora';
rman target /
restore standby controlfile from'/u01/app/ggtrail/ctl_UKOMSP1';
shutdown immediate;
add controlfile to /tmp/ukomsp1.ora
*.control_files='+DATA01/ggukomsp1/controlfile/current.277.820490473','+FRA01/ggukomsp1/controlfile/current.575.820490475'
createspfile='+DATA01/ggukomsp1/spfileukomsp1.ora' from pfile='/tmp/ukomsp1.ora';
alter database mount;
catalog start with '/u01/app/ggtrail';
connect target
run
{
allocate channel c1 device typedisk format '/u01/app/ggtrail/%d_c1_INCR_0_s%s_p%p';
allocate channel c2 device type diskformat '/u01/app/ggtrail/%d_c2_INCR_0_s%s_p%p';
allocate channel c3 device type diskformat '/u01/app/ggtrail/%d_c3_INCR_0_s%s_p%p';
allocate channel c4 device type diskformat '/u01/app/ggtrail/%d_c4_INCR_0_s%s_p%p';
restore database;
}
nohup rman cmdfile=restore.cmdlog=restore.20130711.log &
#drop online and standby redo log and addagain or clear logfile ( rename all the redo log ).
select GROUP#,MEMBER from v$logfile; --nowall are ndcukomsp1.
--online redo log
select group#,thread#,bytes/1024/1024 fromv$log order by 2,1;
alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;
alter database clear logfile group 8;
alter database clear logfile group 9;
alter database clear logfile group 10;
alter database clear logfile group 11;
alter database clear logfile group 12;
--standy redo log
SELECT GROUP#,thread#, BYTES/1024/1024 FROMV$STANDBY_LOG order by 2,1;
alter database clear logfile group 111;
alter database clear logfile group 112;
alter database clear logfile group 113;
alter database clear logfile group 114;
alter database clear logfile group 115;
alter database clear logfile group 116;
alter database clear logfile group 117;
alter database clear logfile group 118;
alter database clear logfile group 119;
alter database clear logfile group 120;
alter database clear logfile group 121;
alter database clear logfile group 122;
alter database clear logfile group 123;
alter database clear logfile group 124;
alter database clear logfile group 125;
meet erorr.
SQL>
alter database clear logfile group 125
*
ERROR at line 1:
ORA-19527: physical standby redo log mustbe renamed
ORA-00312: online log 125 thread 3:'+DATA01/ndcukomsp1/onlinelog/group_125'
#just drop and re-create standy logfile:
alter database drop logfile group 111;
alter database drop logfile group 112;
alter database drop logfile group 113;
alter database drop logfile group 114;
alter database drop logfile group 115;
alter database drop logfile group 116;
alter database drop logfile group 117;
alter database drop logfile group 118;
alter database drop logfile group 119;
alter database drop logfile group 120;
alter database drop logfile group 121;
alter database drop logfile group 122;
alter database drop logfile group 123;
alter database drop logfile group 124;
alter database drop logfile group 125;
----thread 1
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1group 111 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1group 112 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1group 113 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1group 114 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1group 115 SIZE 512M;
----thread 2
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2group 116 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2group 117 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2group 118 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2group 119 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2group 120 SIZE 512M;
----thread 3
ALTER DATABASE ADD STANDBY LOGFILE THREAD 3group 121 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 3group 122 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 3group 123 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 3group 124 SIZE 512M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 3group 125 SIZE 512M;
select GROUP#,MEMBER from v$logfile; --nowall are ggukomsp1.
or use this:alter database rename file'+DATA01/ndcuspricp1/onlinelog/group_1.404.785820897' to'+DATA01/edcuspricp1/onlinelog/group_1_1.log';
#alter database rename tempfile
SQL> select name from v$tempfile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA01/ndcukomsp1/tempfile/temp.620.818304017
alter system setstandby_file_management=manual;
alter database rename file'+DATA01/ndcukomsp1/tempfile/temp.620.818304017' to'+DATA01/ggukomsp1/tempfile/temp_01.dbf';
alter system setstandby_file_management=auto;
srvctl add database -d ggukomsp1 -o/u01/app/oracle/product/11203 -c RAC -r physical_standby -s MOUNT
srvctl add instance -d ggukomsp1 -iukomsp11 -n ndc-epod008-dl580-5
srvctl add instance -d ggukomsp1 -iukomsp12 -n ndc-epod009-dl580-5
srvctl add instance -d ggukomsp1 -iukomsp13 -n ndc-epod010-dl580-5
#shutdown immediate node 1;
#change$ORACLE_HOME/dbs/initukomsp11.ora and other nodes.
spfile='+data01/GGUKOMSP1/spfileukomsp1.ora'
srvctl start database -d ggukomsp1
ALTER DATABASE RECOVER MANAGED STANDBYDATABASE cancel;
ALTER DATABASE RECOVER MANAGED STANDBYDATABASE USING CURRENT LOGFILE disconnect;
ALTER DATABASE RECOVER MANAGED STANDBYDATABASE disconnect from session;
select process,status,sequence#,thread#from v$managed_standby;