The manual is quite good on that and include a step by step :
Here are my notes for a logical, you will just have to replace the word 'logical' by physical'
ie ) alter database create physical standby controlfile as <file> ;
Step by step to create standby 10g:
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
*.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 "/home/oracle/mtier_10_1_2/backup/bk_asdb_%U" ;
backup as compressed backupset database ;
}
"
<9ir2>
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@primary
connect auxiliary /
run {
allocate auxiliary channel c1 device type disk format "/home/oracle/mtier_10_1_2/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.
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@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#;