CONFIGURE OF CATALOG IN ORACLE 11G FOR RMAN

CONFIGURE OF CATALOG IN ORACLE 11G FOR RMAN

Target Database Side(hrms)

Catalog Database Side(catdb)

$export ORACLE_SID=hrms

$sqlplus / as sysdba

sql>startup

sql>archive log list

(should be enabled)

create listener.ora file manually

[oracle@mac1 admin]$ cat listener.ora

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = mac1.oracle.com)(PORT = 7777))

      #(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

   (SID_LIST=

       (SID_DESC=

           (SID_NAME=hrms)

           (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

       )

   )

ADR_BASE_LISTENER = /u01/app/oracle

:wq

$lsnrctl start LISTENER

create password file

$cd $ORACLE_HOME/dbs

$orapwd file=orapwhrms password=sys force=y

Catalog Database Side

=====================

$export ORACLE_SID=catdb

$sqlplus / as sysdba

sql>startup mount;

sql>alter database archivelog;

sql>alter database open;

Create tnsnames.ora file manually

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

[oracle@lnx02 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

TO_HRMS =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01.oracle.com/or ip address of target database)(PORT = 7777))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = hrms)

    )

  )

$tnsping to_hrms

sql>create tablespace rmantbs datafile '/u01/app/oracle/catdb/rmantabs.dbf' size 100m;

sql>create user rmanu identified by rmanu;

sql>alter user rmanu default tablespace rmantbs;

sql>grant connect,resource,recovery_catalog_owner to rmanu;

sql>exit

$rman catalog rmanu/rmanu

rman>create catalog;

rman>exit

sql>conn rmanu/rmanu

sql>select count(*) from tab;

$rman catalog rmanu/rmanu target sys/sys@to_hrms

rman>register database;

rman>backup database;

CONVERTING TO SNAPSHOT DATABASE

• Technical Articles

CONVERTING PHYSICAL STANDBY DATABASE TO SNAPSHOT STANDBY DATABASE
Step 1: Check if Flashback is enabled. If not, enable in mount state in Physical Standby database
i) SQL> Show parameter db_recovery_file_dest
NAME TYPE VALUE
--------------------------------------------------------------- ----------- ------------------------------
db_recovery_file_dest string u03/oradata/ODSP1/flash_recovery_area/
db_recovery_file_dest_size big integer 2G
ii) SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
iii) SQL> Shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
iv) SQL> startup mount;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 788524 bytes
Variable Size 162887636 bytes
Database Buffers 41943040 bytes
Redo Buffers 4096000 bytes
Database mounted.
v) SQL> alter database flashback on;
Database altered.
vi) SQL> select flashback_on from v$database;
FLASHBACK
---------
YES
vii) SQL> alter database open;
Database altered.
viii) SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME OPEN_MODE GUARD_S DATABASE_ROLE
---------------------------------------- ---------- ------- ----------------
OLPTP1 READ ONLY NONE PHYSICAL STANDBY
Step 2 : Cancel recovery from Primary Database to Physical Standby Database
SQL> alter database recover managed standby database cancel;
Database altered.
Step 3 : Converting Physical Standby database to Snapshot Standby database
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.
Step 4: Shutdown the Standby Database Normal
SQL> shut immediate
ORA-01507: Database is niet aangekoppeld.
ORACLE instance shut down.
Step 5: Startup Standby database Normal
SQL> Startup
ORACLE instance started.
Total System Global Area 4175568896 bytes
Fixed Size 2166288 bytes
Variable Size 2852127216 bytes
Database Buffers 1308622848 bytes
Redo Buffers 12652544 bytes
Database mounted.
Database opened.
Step 6: Check Database role
SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME OPEN_MODE GUARD_S DATABASE_ROLE
-------------------------------------- ---------- ------- -------------------------------
OLPTP1 READ WRITE NONE SNAPSHOT STANDBY
CONVERTING SNAPSHOT STANDBY DATABASE TO PHYSICAL STANDBY DATABASE
Step 1: Check for current database role
SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME OPEN_MODE GUARD_S DATABASE_ROLE
----------------------------------------- ---------- ------- ----------------
OLPTP1 READ WRITE NONE SNAPSHOT STANDBY
Step 2 : Shutdown Snapshot Standby Database
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Step 3 : Start in Nomount mode
SQL> Startup nomount pfile='$ORACLE_HOME/dbs/initOLPTP1.ora'
ORACLE instance started.
Total System Global Area 4175568896 bytes
Fixed Size 2166288 bytes
Variable Size 2852127216 bytes
Database Buffers 1308622848 bytes
Redo Buffers 12652544 bytes
Step 4: Mounting Snapshot Standby Database
SQL> alter database mount;
Database altered.
Step 5: Converting Snapshot Standby to Physical Standby Database
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
Step 6: Shutdown the database
SQL> shut immediate
ORA-01507: Database is niet aangekoppeld.
ORACLE instance shut down.
Step 7: Starting database in Nomount mode and mount
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initOLPTP1.ora'
ORACLE instance started.
Total System Global Area 4175568896 bytes
Fixed Size 2166288 bytes
Variable Size 2852127216 bytes
Database Buffers 1308622848 bytes
Redo Buffers 12652544 bytes
SQL> alter database mount standby database;
Database altered.
Step 8 : Cancel recovery from primary database and open database
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;
Database altered.
Step 9 : Check for redo’s are applied in standby database
SQL> select process, status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
RFS IDLE
IF RFS is not there, it indicates that there is no communication between primary and standby
Check below query in primary database
Step 10: Check in primary database
SQL>select error from v$archived_dest;
The output shows "END OF FILE on COMMUNICATION FILE or ORACLE NOT FOUND".
Wait until this error get cleared (it will be cleared automatically after some minutes)
Implies that redos are transferring, but not applied as we issued recover managed standby database cancel (As per Step 8)
Step 11 : Recover Physical Standby database
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> select process, status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_GAP
RFS IDLE
6 rows selected.
Shows that archives apply is under process as we are recovering standby database
(IMPORTANT NOTE : RFS - implies redo transfer from primary to standby is under process.
MRP0 - indicates it is applying archive logs in standby)
Step 12 : Check for database role
SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME OPEN_MODE GUARD_S DATABASE_ROLE
--------------------------------- ---------- ------- ----------------
OLPTP1 READ ONLY NONE PHYSICAL STANDBY

CONFIGURATION OF PHYSICAL STANDBY DATABASE IN ORACLE 11G

configuration of physical standby database in oracle 11g

Pre-requisite

1)create 2 vm machine having OEL5 and oracle 11g install

2)let the machine name,ip and database is like below

Machine name:- lnx01.oracle.com (Primary) IP:- 192.168.100.101

Machine name:- lnx02.oracle.com(Standby) IP:- 192.168.100.102

Database name in primary side:- hrms

Primary side

==========

$export ORACLE_SID=hrms

$sqlplus / as sysdba

sql>select name,open_mode,log_mode,force_logging from v$database;

sql>alter database force logging;

sql>create pfile from spfile;

sql>shut immediate;

sql>exit

$cd $ORACLE_HOME/dbs

$mv spfilehrms.ora spfilehrms.ora.bkp

$cd

$export ORACLE_SID=hrms

$sqlplus  / as sysdba

sql>startup

sql>show parameter spfile

sql>exit

$export ORACLE_SID=hrms

$sqlplus / as sysdba

sql>startup

sql>show parameter spfile

sql>select name from v$datafile;

sql>select name from v$controlfile;

sql>select member from v$logfile;

sql>archive log list

sql>shut immediate

sql>exit

$cd $ORACLE_HOME/dbs

$vi inithrms.ora

go to the last and add the below parameter

###########DATAGURAD PARAMTERS FOR Primary Role Initialization Parameters

log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'

log_archive_dest_2='SERVICE=to_hrms_stdby DB_UNIQUE_NAME=hrms_stdby'

log_archive_config='DG_CONFIG=(hrms,hrms_stdby)'

db_unique_name=hrms

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

########Standby Role Initialization Parameters

fal_server=to_hrms_stdby

standby_file_management=auto

:wq

CONFIGURE THE LISTENER IN PRIMARY SIDE

$cd $ORACLE_HOME/network/admin

$vi listener.ora

[oracle@lnx01 admin]$ cat listener.ora

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01.oracle.com)(PORT = 7777))

      #(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

   (SID_LIST=

       (SID_DESC=

           (SID_NAME=hrms)

           (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

       )

   )

ADR_BASE_LISTENER = /u01/app/oracle

:wq

$vi tnsnames.ora

[oracle@lnx01 admin]$ cat tnsnames.ora

TO_HRMS =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01.oracle.com)(PORT = 7777))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = hrms)

    )

 )

TO_HRMS_STDBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02.oracle.com)(PORT = 7777))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = hrms_stdby)

    )

  )

[oracle@lnx01 admin]$

:wq

CONFIGURE NETWORK SERVICE IN STANDBY SIDE

[oracle@lnx02 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_home/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02.oracle.com)(PORT = 7777))

  )

SID_LIST_LISTENER1 =

  (SID_LIST =

    (SID_DESC =

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = hrms_stdby)

    )

  )

ADR_BASE_LISTENER1 = /u01/app/oracle

[oracle@lnx02 admin]$

:wq

[oracle@lnx02 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_home/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

TO_HRMS =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01.oracle.com)(PORT = 7777))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = hrms)

    )

  )

TO_HRMS_STDBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02.oracle.com)(PORT = 7777))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = hrms_stdby)

    )

  )

[oracle@lnx02 admin]$

:wq

Primary side

======

$cd $ORACLE_HOME/dbs

$scp inithrms.ora lnx02:/home/oracle

password:oracle

Standby side

=========

$cp inithrms.ora inithrms_stdby.ora

$mv inithrms_stdby.ora $ORACLE_HOME/dbs

$cd $ORACLE_HOME/dbs

$vi inithrms_stdby.ora

[oracle@lnx02 dbs]$ cat inithrms_stdby.ora

hrms.__db_cache_size=520093696

hrms.__java_pool_size=16777216

hrms.__large_pool_size=16777216

hrms.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

hrms.__pga_aggregate_target=520093696

hrms.__sga_target=771751936

hrms.__shared_io_pool_size=0

hrms.__shared_pool_size=201326592

hrms.__streams_pool_size=0

*.audit_file_dest='/u02/app/oracle/admin/hrms/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u02/app/oracle/hrms/control01.ctl','/u02/app/oracle/fast_recovery_area/hrms/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='hrms'

*.db_recovery_file_dest='/u02/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4196401152

*.diagnostic_dest='/u02/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=hrmsXDB)'

*.memory_target=1284505600

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

################DATAGUARD PARAMETERS FOR PRIMARY ROLE##############

log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'

log_archive_dest_2='SERVICE=to_hrms_stdby DB_UNIQUE_NAME=hrms_stdby'

log_archive_config='DG_CONFIG=(hrms,hrms_stdby)'

db_unique_name=hrms_stdby

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

###############STANDBY ROLE PARAMETER############################

fal_server=to_hrms_stdby

standby_file_management=auto

db_file_name_convert=('/u01/app/oracle/hrms/','/u02/app/oracle/hrms/')

log_file_name_convert=('/u01/app/oracle/hrms/','/u02/app/oracle/hrms/')

:wq

lnx02# mkdir -p /u02/app/oracle

lnx02# mkdir -p /u02/app/oracle/hrms

lnx02# mkdir -p /u02/app/oracle/fast_recovery_area

lnx02# mkdir -p /u02/app/oracle/fast_recovery_area/hrms

lnx02# chown -R oracle:oinstall /u02/app/oracle/

lnx02# chown -R oracle:oinstall /u02/app/oracle/hrms/

lnx02# chown -R oracle:oinstall /u02/app/oracle/fast_recovery_area/

lnx02# chown -R oracle:oinstall /u02/app/oracle/fast_recovery_area/hrms/

lnx02# chmod -R 775 /u02/app/oracle/

lnx02# chmod -R 775 /u02/app/oracle/hrms/

lnx02# chmod -R 775 /u02/app/oracle/fast_recovery_area/

lnx02# chmod -R 775 /u02/app/oracle/fast_recovery_area/hrms/

lnx02#

/lnx02$ cd /u02/app/oracle/hrms/

lnx02$ mkdir adump

##############CONFIGURE PASSWORD FILE BOTH PRIMARY AND STANDBY

[oracle@lnx02 dbs]$ orapwd file=orapwhrms_stdby password=rac entries=3 force=y

[oracle@lnx02 dbs]$

[root@lnx01 ~]# su - oracle

[oracle@lnx01 ~]$ cd $ORACLE_HOME/dbs

[oracle@lnx01 dbs]$ orapwd file=orapwhrms password=rac entries=3 force=y

[oracle@lnx01 dbs]$

###########START THE PRIMARY DATABASE

[oracle@lnx01 ~]$ export ORACLE_SID=hrms

[oracle@lnx01 ~]$ sqlplus / as sysdba

SYS>startup

##########START THE STANDBY DATABASE

[oracle@lnx02 ~]$ export ORACLE_SID=hrms_stdby

[oracle@lnx02 ~]$ sqlplus / as sysdba

SQL> startup nomount

SYS>select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

---------------- ------------

hrms_stdby       STARTED

[oracle@lnx01 ~]$ export ORACLE_SID=hrms

[oracle@lnx01 ~]$ rman target sys/rac auxiliary sys/rac@to_hrms_stdby

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Dec 19 18:04:35 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HRMS (DBID=850606497)

connected to auxiliary database: HRMS (not mounted)

RMAN>

RMAN> duplicate target database for standby from active database;

CREATION OF STANDBY REDO LOG FILE IN BOTH THE SIDE

PRIMARY SIDE

==========

sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo1.log') size 50m;

sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo2.log') size 50m;

sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo3.log') size 50m;

sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo4.log') size 50m;

sql>select member from v$logfile where type = 'STANDBY';

sql>select member from v$logfile;

STANDBY SIDE

============

sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo1.log') size 50m;

sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo2.log') size 50m;

sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo3.log') size 50m;

sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo4.log') size 50m;

sql>select member from v$logfile where type = 'STANDBY';

sql>select member from v$logfile;

Standby side

sql>select name,open_mode from v$database;

sql>archive log list

sql>alter database recover managed standby database disconnect from session;

sql>select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;

Primary side

sql>select name,open_mode from v$database;

sql>archive log list

sql>select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;

THE SEQUENCE AND APPLIED COLUMN SHOULD BE MATCHED IN BOTH THE SIDE OF THE SERVER.