standby 创建_alter database add standby logfile-CSDN博客

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