RE: Do anyone implemented Physical Standby using primary db hot backups?

From: Polarski, Bernard <Bernard.Polarski_at_atosorigin.com>
Date: Mon, 11 Sep 2006 11:49:31 +0200
Message-ID: <25D4919915CCF742A88EE3366D6D913D0D568702@mailserver1>

Here again is a step by step to create a pysical/logical using rman. If you are only interested in physical, then just avoid to convert your physical/logical. If you don't want to use rman, than replace all the rman stuff with you restore backup and perform yourself the roll forward archives. Adapt your path and note that it is shorter in 10g than in 9i:

Step by step to create standby 9i/10g:

look also to Note:211016.1

Create a physical standby :

-Remove the spfile if any exists, you will have to recreate it later. meantime we modify the init.ora

-add these parameter in the init<standby>.ora PRIMARY:
*.db_name='asdb'
*.db_unique_name='asdb' # 10g, 9ir2 is LOCK_NAME_SPACE
*.instance_name='asdb'
*.log_archive_dest_1="location=/home/oracle/arch/asdb

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)"

*.log_archive_dest_state_1=enable
*.log_archive_format="log_%t_%r_%s.arc"

.
.

STANDBY:
*.parallel_max_servers=9
*.db_name='asdb'
*.db_unique_name='POLDEV' # 10g, 9ir2 is
LOCK_NAME_SPACE
*.instance_name='POLDEV'
*.log_archive_dest_1="location=/home/oracle/arch/POLDEV

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)"


*.log_archive_dest_2="location=/home/oracle/arch/POLDEV_2

VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)"


*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable
*.log_archive_format="log_%t_%r_%s.arc"
*.db_file_name_convert=('/oradata/asdb/','/POLDEV/')
*.log_file_name_convert=('/oradata/asdb/','/POLDEV/')
*.standby_file_management=AUTO
*.standby_archive_dest=/home/oracle/arch/POLDEV/standby
*.remote_archive_enable=TRUE
*.FAL_SERVER=asdb
*.FAL_CLIENT=POLDEV
PRIMARY:

Rman back of the primary:
"
connect target /
run {
allocate channel c1 device type disk format "/backup/bk_asdb_%U" ; backup as compressed backupset database ; }
"

<9ir2 && only if you target a logical, not needed for phisical> EXECUTE DBMS_LOGSTDBY.BUILD;
</9ir2>

create a standby controlfile:

..............................

sqlplus '/ as sysdba'
alter database create standby controlfile as '/tmp/stdby.ctl' ; alter system switch logfile;
exit

copy this archive and all archived since the backup into log_archive_dest_1 of the standby.
copy the orapw<primary> onto orapw<standby>

STANDBY:

startup nomount
exit
rman cmdfile=dup_stdby.rm log=dup.log &
"
connect target sys/change_on_install_at_primary connect auxiliary /
run {
allocate auxiliary channel c1 device type disk format "/backup/bk_asdb_%U" ;
allocate auxiliary channel c2 device type disk ; duplicate target database for standby dorecover; }
"

sqlplus '/ as sysdba'
alter database recover managed standby database disconnect ; exit

Check that the physical standby receives some archives.

++++++++++++++++++++++++++++++++++++++++++++++++++

++ END of PHYSICAL STANDBY, rest is for LOGICAL ++

++++++++++++++++++++++++++++++++++++++++++++++++++
..................................................

Convert the physical standby to a logical standby:

..................................................

SECONDARY

sqlplus '/ as sysdba'
shutdown immediate;
exit

PRIMARY

alter database create logical standby controlfile as '/tmp/logical_stdby.ctl' ;

SECONDARY

replace all controfiles with this logical control file:

cp '/tmp/logical_stdby.ctl' $ORADATA/control0[1-3].ctl

sqlplus '/ as sysdba'
alter database recover managed standby database; ALTER DATABASE ACTIVATE STANDBY DATABASE; shutdown immediate;
startup mount
exit

nid target=sys/change_on_install_at_POLDEV DBNAME=POLDEV # this will shutdown the DB
==>[Y]

Edit init<standby.ora> and switch
DB_NAME=<primary> to DB_NAME=<stanby>

rm orapw<standby>
orapwd file=orapw<standby> password=change_on_install

sqlplus '/ as sysdba'
create spfile from pfile
startup mount;
alter database open resetlogs ;
ALTER DATABASE RENAME GLOBAL_NAME TO <standby> ; alter tablespace temp add tempfile '/home/oracle/POLDEV/temp01.dbf' size 20m ;
<9ir2>
ALTER DATABASE REGISTER LOGICAL LOGFILE '/<path>/last logfile> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL; <10g>

ALTER DATABASE START LOGICAL STANDBY APPLY;                 # At least !

<10g>

The SQL apply coordinatory is in initialisation phase :

SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME = 'coordinator state';

PRIMARY

sqlplus '/ as sysdba'
alter system switch logfile;
exit

STANDBY

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#; -----Original Message-----
From: Syed Jaffar Hussain [mailto:sjaffarhussain_at_gmail.com] Sent: Monday, 11 September, 2006 11:30 AM To: Polarski, Bernard
Subject: Re: Do anyone implemented Physical Standby using primary db hot backups?

Thanks for your valuable suggestion.
Can you give it to me in plain english. I mean, step by step proecess. My question is,

When should i copy my redo groups (files) to the standby? Because, hot backup doesn't contains redo files?
When do I create standby control file? and start restroe and recovery?

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 11 2006 - 04:49:31 CDT