Convert Physical Standby database into snapshot standby database .

We can open  standby database in read-write mode .When switched back into standby mode, all changes made whilst in read-write mode are lost is know as Snapshot standby database .

Priversly This is achieved using flashback database, but from 11g standby database does not need to have flashback database explicitly enabled to take advantage

of this feature, thought it works just the same if it is.

How To Set Up Physical Standby Database You Can Check Here 

                     http://adminoracle10g.blogspot.in/2014/08/physical-standby-database-implementation.html        

Steps

            1)      Bring database in mount state

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

2)      Disable  recovery  on standby

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3)      Convert standby database to flashback

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

4)      Open database

ALTER DATABASE OPEN;

5)      Check  database status

Select NAME, OPEN_MODE, DATABASE_ROLE from v$database;

NAME          OPEN_MODE            DATABASE_ROLE

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

ORCL_STBY        READ WRITE    SNAPSHOT STANDBY

SELECT flashback_on FROM v$database;

FLASHBACK_ON

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

RESTORE POINT ONLY

6)      To convert it back to the physical standby, losing all the changes made since the conversion to snapshot standby, issue the following commands.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

SHUTDOWN IMMEDIATE;

STARTUP NOMOUNT;

ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

SELECT flashback_on FROM v$database;

Select NAME, OPEN_MODE, DATABASE_ROLE from v$database;

NAME                  OPEN_MODE            DATABASE_ROLE

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

ORCL_STBY        READ WRITE           PYSICAL STANDBY

FLASHBACK_ON

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

NO

Active Data Guard :  While the standby is open read-only  on the same time the physical standby database can be  in recovery mode  is called  ACTIVE  DATA GUARD.

Advantage

This allows you to use this standby as a real-time reporting database or even to backup the primary data, also as a result it does not have any impact on RTO or RPO.

The following operations are disallowed

•          Any Data Manipulation Language (DML) except for select statements
•            Any Data Definition Language (DDL)
•            Access of local sequences
•            DMLs on local temporary tables

·    

Note :-However, this benefit is offset to a certain extent by the fact that Active Data Guard is available on Enterprise Edition only and is cost option which must be licensed on both the primary and standby database.

Steps To create Active DATA Guard .

    1) Check the status of the Primary database and  Physical standby database and the latest sequence generated in the primary database.

Primary

select status,instance_name,database_role from v$instance,v$database;

STATUS       INSTANCE_NAME    DATABASE_ROLE

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

OPEN         ORCL           PRIMARY

 select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

3333

Standby

select status,instance_name,database_role from v$database,v$instance;

STATUS   INSTANCE_NAME DATABASE_ROLE

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

MOUNTED  ORCL_STBY         PHYSICAL STANDBY

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)

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

3333

)     Check if the Managed Recovery Process (MRP) is active on the physcial standby database.

select process,status,sequence# from v$managed_standby;

2)     Cancel the MRP on the physical standby database and open the standby database  in            READ-ONLY mode

alter database recover managed standby database cancel;

alter database open read only;

select status,instance_name,database_role,open_mode from v$database,v$instance;

STATUS INSTANCE_NAME  DATABASE_ROLE    OPEN_MODE

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

OPEN   ORCL_STBY            PHYSICAL STANDBY READ ONLY

   3)    start the MRP on the physical standby database.

alter database recover managed standby database disconnectfrom session;

                                                                                                                                 

4)       Database is now Active Data Guard

 SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE    OPEN_MODE

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

PHYSICAL STANDBY READ ONLY WITH APPLY

How To Set Up Physical Standby Database You Can Check Here 

                     http://adminoracle10g.blogspot.in/2014/08/physical-standby-database-implementation.html                                                                                                            

Physical Standby Database Implementation

Database	DB_NAME	DB_UNIQUE_NAME	Oracle Net Service Name
Primary	ORCL	ORCL	ORCL
Physical standby	ORCL	ORCL_STBY	ORCL_STBY

Implementation

1)       Check that the primary database is in archivelog mode.

SELECT log_mode FROM v$database;

LOG_MODE

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

NOARCHIVELOG

2)       If it is noarchivelog mode, switch is to archivelog mode.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

3)       Enabled forced logging by issuing the following command.

ALTER DATABASE FORCE LOGGING;

4)      Set Initialization Parameters

Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to "ORCL" on the primary database.

SQL> show parameter db_name

NAME                                                                                      TYPE       VALUE

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

db_name                                                         string     ORCL

SQL> show parameter db_unique_name

NAME                                                                                      TYPE       VALUE

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

db_unique_name                                                               string     ORCL

The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value "ORCL_STBY".

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL_STBY)';

LOG_ARCHIVE_DEST_1='LOCATION=/backup/archs VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL_STBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STBY';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

ALTER SYSTEM SET FAL_SERVER=ORCL_STBY;

ALTER SYSTEM SET FAL_CLIENT=ORCL;

--ALTER SYSTEM SET DB_FILE_NAME_CONVERT='ORCL_STBY','ORCL' SCOPE=SPFILE;

--ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='ORCL_STBY','ORCL'  SCOPE=SPFILE;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

5)       Create a Backup Copy of the Primary Database Datafiles Use can use any backup method . We prefer RMAN

RMAN>backup database plus archivelog;

6)       Create Standby Controlfile and PFILE

Create a controlfile for the standby database by issuing the following command on the primary database.

STARTUP MOUNT;

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/orcl_stby.ctl';

ALTER DATABASE OPEN:

7)       Create a parameter file for the standby database.

CREATE PFILE='/tmp/initorcl_stby.ora' FROM SPFILE;

8)    Modifying Initialization Parameters for a Physical Standby Database

Amend the PFILE making the entries relevant for the standby database. I'm making a replica of the original server, so in my case I only had to amend the following parameters.

*.db_unique_name='ORCL_STBY'

*.fal_server='ORCL'

*.log_archive_dest_2='SERVICE=ORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'

*.STANDBY_FILE_MANAGEMENT='AUTO'

9)    Create a window based service 

 Oradim –new  -sid  orcl_stby –inpwsd  pass

for unix export ORACLE_SID

10)     CP password file from primary to  standby an rename as  per standby database name

11)    Configure listener on standby

12)    Add TNS entry on both primary and standby
13)   stratstandby using pfile '/tmp/initorcl_stby.ora'

   

   stratup nomount pfile =’/tmp/initorcl_stby.ora’;

14)    Create a server parameter file for standby database

   Create spfile from pfile =’/tmp/initorcl_stby.ora’;

  Shu immediate;

15)    Start physical standby database

Startup nomount

16)    Restore database from  primary database backup

RMAN> restore database;

17)    Create Redo Logs

Create online redo logs for the standby. It's a good idea to match the configuration of the primary server.
You should create standby redo logs on both the standby and the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following is standby redo logs must be created on both servers.

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo03.log') 

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL /standby_redo01.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL /standby_redo02.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL /standby_redo03.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL /standby_redo04.log') SIZE 50M;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

18)    Startup redo apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECTS FROM SESSION;

19)    Test archival operations to the physical standby database. Swich logfile on primary database;

        ALTER SYSTEM SWITCH LOGFILE;

20)     Verify the new redo data was archived on the standby database.

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME

   FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME         NEXT_TIME

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

         8 11-JUL-14 17:50:45 11-JUL-14 17:50:53

         9 11-JUL-14 17:50:53 11-JUL-14 17:50:58

        10 11-JUL-14 17:50:58 11-JUL-14 17:51:03