PHYSICAL STANDBY DATABASE CREATION THROUGH RMAN ON 10g

Physical Standby Database Creation with RMAN
PHYSICAL STANDBY DATABASE CREATION THROUGH RMAN:

Prerequisite:
1. Two Servers with same architecture(like Sun sparc/x86 etc.) and same OS version(like solaris 10 etc.) installed.
2. Same oracle software version(like 10.2.0.4.0 etc.) on both servers.
3. create a database on one server for primary purpose.

Primary Database: COBRA
Standby Database: cobrastd

—Physical Standby creation steps:

–On Primary:

SQL>select name from v$database;

NAME
———
cobra

SQL>show parameters unique

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_unique_name                       string      cobra

–Put database in force logging: which takes precedence and all operations are logged into the redo logs.

SQL>alter database force logging;

–Create a password file on primary database and copy it standby because sys password should be same on both sites
which tends to ship and apply archive logs from primary to standby.

#orapwd file=orapwcobra password=oracle force=y

–Create standby redo log with the same size as online redo logs
which enable Data Guard Maximum Availability and Maximum Protection modes.

SQL>select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                   IS_
———- ——- ——- —————————————- —
         4         ONLINE  /data02/cobra/redo04.log                 NO
         5         ONLINE  /data02/cobra/redo05.log                 NO
         6         ONLINE  /data02/cobra/redo06.log                 NO

SQL>select bytes from v$log;

  BYTES
———-
 314572800
 314572800
 314572800

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 1 '/data02/cobra/redo01std.log' SIZE 300M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 2 '/data02/cobra/redo02std.log' SIZE 300M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 '/data02/cobra/redo03std.log' SIZE 300M;

SQL>select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                   IS_
———- ——- ——- —————————————- —
         1         STANDBY /data02/cobra/redo01std.log              NO
         2         STANDBY /data02/cobra/redo02std.log              NO
         3         STANDBY /data02/cobra/redo03std.log              NO
         4         ONLINE  /data02/cobra/redo04.log                 NO
         5         ONLINE  /data02/cobra/redo05.log                 NO
         6         ONLINE  /data02/cobra/redo06.log                 NO

6 rows selected.

–Enable Archivelog mode if database is not in archive mode:

SQL>shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>startup mount
ORACLE instance started.

Total System Global Area 4194304000 bytes
Fixed Size 2045896 bytes
Variable Size 2600470584 bytes
Database Buffers 1577058304 bytes
Redo Buffers 14729216 bytes

Database mounted.

SQL>alter database archivelog;
Database altered.

SQL>alter system set log_archive_dest_1='LOCATION=/data02/oradata/cobra/archives' scope=both;

alter database open;

SELECT GROUP#,ThREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS
———- ———- ———- — ———-
         1          0          0 YES UNASSIGNED
         2          0          0 YES UNASSIGNED
         3          0          0 YES UNASSIGNED

–Now make necessary changes in pfile:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(cobra,cobrastd)';

System altered.

SQL>alter system set LOG_ARCHIVE_DEST_1='LOCATION=/data02/cobra/archive
 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
 DB_UNIQUE_NAME=cobra';

System altered.

SQL>alter system set LOG_ARCHIVE_DEST_2='SERVICE=cobrastd LGWR ASYNC
 VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)
 DB_UNIQUE_NAME=cobrastd';

System altered.

SQL>alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

SQL>alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;

SQL>alter system set FAL_SERVER='cobrastd';

SQL>alter system set FAL_CLIENT='cobra';

SQL>alter system set DB_FILE_NAME_CONVERT='/data02/cobrastd','/data02/cobra' scope=spfile;

SQL>alter system set LOG_FILE_NAME_CONVERT='/data02/cobrastd','/data02/cobra' scope=spfile;

SQL>alter system set standby_file_management='AUTO';

–Run the backup job at the primary by connecting to target
#export ORACLE_SID=cobra
#rman target /

RMAN>
run
 {
 allocate channel c1 type disk;
 allocate channel c2 type disk;
 backup database format '/data01/rman_bak/cobra/%U';
 backup archivelog all format '/data01/rman_bak/cobra/%U';
 backup current controlfile for standby format '/data01/rman_bak/cobra/%U';
 }

—Create net services on both primary and standby

In tnsnames.ora file add both cobra and cobrastd on primary and standby server both.
Above entry is mainly required for FAL_SERVER and FAL_CLIENT parameters.
 
—Now create pfile for standby database on standby server:

cobrastd.__db_cache_size=5284823040
cobrastd.__java_pool_size=16777216
cobrastd.__large_pool_size=16777216
cobrastd.__shared_pool_size=922746880
cobrastd.__streams_pool_size=33554432
*.audit_file_dest='/export/home/oracle/admin/cobrastd/adump'
*.background_dump_dest='/export/home/oracle/admin/cobrastd/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/data02/cobrastd/control01.ctl','/data02/cobrastd/control02.ctl','/data02/cobrastd/control03.ctl'
*.core_dump_dest='/export/home/oracle/admin/cobrastd/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='/data02/cobra','/data02/cobrastd'
*.db_name='cobra'
*.db_unique_name='cobrastd'
*.db_recovery_file_dest='/export/home/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cobrastdXDB)'
*.fal_client='cobrastd'
*.fal_server='cobra'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(cobrastd,cobra)'
*.log_archive_dest_1='LOCATION=/data02/cobrastd/archive
 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
 DB_UNIQUE_NAME=cobrastd'
*.log_archive_dest_2='SERVICE=cobra LGWR ASYNC
 VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)
 DB_UNIQUE_NAME=cobra'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='/data02/cobra','/data02/cobrastd'
*.open_cursors=500
*.pga_aggregate_target=3145728000
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=6291456000
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/export/home/oracle/admin/cobrastd/udump'

—ON STANDBY DATABASE:
Create same backup location on Standby server as created on Primary server(/data01/rman_bak/cobra) and then
Copy backup from primary database server to standby server.

–create required directories on standby:

#export ORACLE_SID=cobrastd

#sqlplus / as sysdba

SQL>startup nomount

SQL>create spfile from pfile;

SQL>shu immediate

SQL>startup nomount

Use RMAN duplicate for standby on standby
When 'dorecover' is specified in the duplicate for stanby command then
do a archivelog switch on the target database and run the duplicate.

–On primary,

sql> alter system archive log current;

–On standby,

#rman target sys/passwd@cobra auxiliary sys/passwd@cobrastd

rman>duplicate target database for standby dorecover;

–Start the MRP(managed recovery process) on standby

SQL> select name,db_unique_name,database_role from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
——— —————————— —————-
cobra     cobrastd                         PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect;

–Do log switch on primary,

SQL> ALTER SYSTEM SWITCH LOGFILE;

–Check the standby whether it is in SYNC with primary,

SQL> select SEQUENCE#, ARCHIVED, APPLIED, FIRST_TIME, NEXT_TIME from V$ARCHIVED_LOG order by SEQUENCE#;

 

You can skip to the end and leave a response. Pinging is currently not allowed.

One Response to “PHYSICAL STANDBY DATABASE CREATION THROUGH RMAN ON 10g”

  1. It is a typical experience that the issue difficult through the night is resolved in the morning after the committee of sleep has worked on it.

Leave a Reply