How to Create Oracle ASM disks in windows

In windows environment ASM disks have to be created using asmtoolg.exe file, before that we have to create the partitions of given RAW disk using DISKPART utility in windows.

Once raw partition added to the target server, check the RAW partition available or not.

1. Connect to the command prompt and use below command to check the disk.

C:\diskpart 

DISKPART> list disk

2. Select the disk which you want to made partitions for ASM disk.

DISKPART> select disk 1

Read More

Top 5 I/O wait events in oracle AWR report

Top 5 I/O wait events in oracle:

“Db file sequential read” wait event occur on I/O operations on datafiles.

This wait event related to single block read, for index data blocks are table data blocks accessed through an index but can also be seen for datafile header blocks.

Troubleshooting:

Find the top sql statements in physical reads from

1. Statspack

2. AWR report under Sql order by reads or

3. From v$sql

Reasons for db file sequential read:

1.       If index range scans are involved, more blocks than necessary could be being visited if the index is unselective. By forcing and enabling the more selective index, we can access the same table data by visiting the fewer index blocks.

2.       If the indexes are fragmented, then again, we have to visit more blocks because there is a less index data per block.

3.       If the index being used has a large clustering factor, then more table data blocks have to be visited in order to get the rows in each index block.

Db file scattered read:

This wait even indicates that we are waiting for the read operation which reads number of blocks from disk. Most of the cases it indicates there is full table scan or index fast full scan.

It occurs when oracle performs multiblock reads from disk into non-contiguous buffers in the buffer cache. Such reads are issues for up to DB_FILE_MULTIBLOCK_READ_COUNT blocks at a time. These typically happen for full table scans and fast full index scans.

Fast full index scans: When the data in the index is not in particular order.

Read More

What are the AWR, ADDM and ASH reports in Oracle? How to Analyse?

AWR, ASH and ADDM reports are tools to analyse the Oracle database performance bottlenecks of intervals.

ADDM Report:

How to generate the ADDM report?

To generate the ADDM report by non-sys users, they need below privileges. As a sys user no privileges are required.

How to grant the ADDM report generation privileges to non-sys users:

1.       Login as a sys user.

2.       Sql> grant advisor to ;

3.       Sql> grant select_catalog_role to ;

4.       Sql> grant execute on dbms_workload_repository to ;

Note: From 12c ADDM report also comes with AWR report.

ADDM generate script is in $ORACLE_HOME/RDBMS/admin/addmrpt.sql

Sql>@addmrpt.sql

ADDM report suggests potential resolution options for many issues. ADDM reports gives recommendations on various areas like CPU load, Memory Usage, I/O usage, Object contention, Application Issues, DB configuration issues, high load sql statements..etc.

Read More

What are the Oracle database and Grid/RAC health check/Monitoring tool’s?

Hang Manger

Quality Of service Management

TAF (Trace file analyser) (1513912.1)

Exacheck

Memory Guard

Cluster Healthcheck advisor

Cluster Health monitor

Cluster Verification utility

ORAchk

DBSAT (Database Security Assessment tool)

OSwatcher (301137.1)

Read More

What are the RAC/Grid specific daemons in Oracle 12c

CRSD (Cluster Ready Service Daemon):

CRSD is primarily responsible for maintaining the availability of the application resources such as DB instances. Main responsibility of the CRSD daemon is starting and stopping of the resources, relocating the resources when required to another node in the event of failure and updating the resource profiles in the OCR file and also responsible for backing up the OCR file.

Read More

Oracle RMAN duplicate database creation scenarios

Duplicating When Datafiles Use Inconsistent Paths

CONNECT TARGET /;

CONNECT CATALOG rman/cat@catdb;

CONNECT AUXILIARY SYS/oracle@dupdb;

# note that a RUN command is necessary because you can only execute SET NEWNAME

# within a RUN command

RUN

{

  # The DUPLICATE command uses an automatic sbt channel.

  # Because the target datafiles are spread across multiple directories,

  # run SET NEWNAME rather than DB_FILE_NAME_CONVERT

  SET NEWNAME FOR DATAFILE 1 TO '/oradata1/system01.dbf';

  SET NEWNAME FOR DATAFILE 2 TO '/oradata2/undotbs01.dbf';

  SET NEWNAME FOR DATAFILE 3 TO '/oradata3/cwmlite01.dbf';

  SET NEWNAME FOR DATAFILE 4 TO '/oradata4/drsys01';

  SET NEWNAME FOR DATAFILE 5 TO '/oradata5/example01.dbf';

  SET NEWNAME FOR DATAFILE 6 TO '/oradata6/indx01.dbf';

  # Do not set a newname for datafile 7, because it is in the tools tablespace,

  # and you are excluding tools from the duplicate database.

  SET NEWNAME FOR DATAFILE 8 TO '/oradata7/users01.dbf';

  DUPLICATE TARGET DATABASE TO dupdb

    SKIP TABLESPACE tools

    LOGFILE

      GROUP 1 ('/duplogs/redo01a.log',

               '/duplogs/redo01b.log') SIZE 200K REUSE,

      GROUP 2 ('/duplogs/redo02a.log',

               '/duplogs/redo02b.log') SIZE 200K REUSE;

}

RMAN DUPLICATE DATABASE From File System to ASM: Example

This duplication example assumes the following:

• You are using recovery catalog database catdb.
• The target database trgt is on host1 and the database files are stored in a non-ASM file system.
• You want to duplicate the target to database dupdb on remote host host2.
• host2 has diskgroup +DISK1.
• You want to store the datafiles for dupdb to +DISK1.
• You want to store two controlfiles in +DISK1.
• The backups and archivelogs created by host1 are accessible by host2.

Create an initialization parameter for auxiliary instance by copying the target database initialization parameter file. Change the parameters as follows:

• Set DB_NAME to the new database name dupdb
• Set CONTROL_FILES to store two copies of the control file in +DISK1
• Make sure DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT are not set
• Set any other initialization parameters that end in _DEST, such as DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_DEST_n, to reference +DISK1

For example:

DB_NAME=dupdb

CONTROL_FILES=+DISK1,+DISK1

Create an SPFILE from the parameter file, and start the auxiliary instance:

SQL> CONNECT AUXILIARY SYS/oracle@dupdb;

SQL> CREATE SPFILE FROM PFILE=auxiliary instance pfile;

SQL> STARTUP NOMOUNT;

Then run the following RMAN commands to duplicate the database:

RMAN> CONNECT TARGET /;

RMAN> CONNECT CATALOG rman/cat@catdb;

RMAN> CONNECT AUXILIARY SYS/oracle@dupdb;

RMAN> DUPLICATE TARGET DATABASE TO dupdb

      LOGFILE GROUP 1 ('+DISK1','+DISK1') SIZE 200K,

              GROUP 2 ('+DISK1','+DISK1') SIZE 200K;

RMAN DUPLICATE DATABASE From ASM to ASM: Example

This duplication example assumes the following:

• You are using recovery catalog database catdb
• The target database trgt is on host1 and contains ASM datafiles and online logs in diskgroup +DISK1
• You want to duplicate the target to database dupdb on remote host host2.
• host2 has diskgroup +DISK2
• You want to store the datafiles for dupdb to +DISK2
• You want to store two controlfiles in +DISK2
• The backups and archivelogs created by host1 are accessible by host2

Create an initialization parameter for auxiliary instance by copying the target database initialization parameter file. Change the parameters as follows:

• Set DB_NAME to the new database name dupdb
• Set CONTROL_FILES to store two copies of the control file in +DISK2
• Set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT to convert the datafile and online log file names from +DISK1 to +DISK2
• Set any other initialization parameters that end in _DEST, such as DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_DEST_n, to reference +DISK2

For example:

DB_NAME=dupdb

CONTROL_FILES=+DISK2,+DISK2

DB_FILE_NAME_CONVERT=+DISK1,+DISK2

LOG_FILE_NAME_CONVERT=+DISK1,DISK2

Create an SPFILE from the parameter file, and start the auxiliary instance:

SQL> CONNECT AUXILIARY SYS/oracle@dupdb;

SQL> CREATE SPFILE FROM PFILE=auxiliary instance pfile;

SQL> STARTUP NOMOUNT;

Then run the following RMAN commands to duplicate the database:

RMAN> CONNECT TARGET /;

RMAN> CONNECT CATALOG rman/cat@catdb;

RMAN> CONNECT AUXILIARY SYS/oracle@dupdb;

RMAN> DUPLICATE TARGET DATABASE TO dupdb;

Steps To Migrate a Database From Non-ASM to ASM

You can use these steps to migrate a NON-ASM database to ASM database in Oracle 11gR2.

Prerequisite - ASM instance should be created and up and running. Please refer my previous article to create a ASM instance

1) Check the database version and status

   SQL> select INSTANCE_NAME,VERSION,DATABASE_STATUS from v$instance;

    INSTANCE_NAME    VERSION           DATABASE_STATUS
    ---------------- ----------------- -----------------
    learndb          11.2.0.3.0        ACTIVE

2) We should be able to check the ASM disk available from the instance which we created earlier

   SQL> select NAME,STATE,TOTAL_MB,PATH from v$asm_disk;
   NAME                           STATE      TOTAL_MB PATH
   ------------------------------ -------- ---------- ----------
   DISK1                          NORMAL         4777 ORCL:DISK1


Migrating the SPFILE from Non-asm to ASM


1) If you are using a spfile, take a backup of spfile using rman to restore it to ASM disk

   RMAN>BACKUP AS BACKUPSET SPFILE;

2) Now shutdown the database and start it in Mount stage
   SQL> Shutdown immediate

   SQL> STARTUP MOUNT;

3) Now restore the spfile from the backup to ASM disk as below

  RMAN>RESTORE SPFILE TO '+DATA/spfilelearndb.ora';

4) You can see, still the parameter "spfile" is pointing to file system instead of ASM disk

   SQL> show parameter spfile

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    spfile                               string      /opt/oracle/product/11.2.0/dbh
                                                        ome_1/dbs/spfilelearndb.ora
5) Shutdowm the instance

    SQL> shutdown immediate

6) Delete "spfile.ora and init.ora from the $ORACLE_HOME/dbs directory and create a new init.ora with the following line of content and start the instance,

    vi initlearndb.ora
   SPFILE='+DATA/spfilelearndb.ora'

   SQL> Startup (  first it will search for spfile.ora which we deleted and next it will look for      init.ora which we have moified with the above content  )

   SQL> show parameter spfile

    NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   spfile                               string      +DATA/spfilelearndb.ora


Migrating Control files from Non-ASM to ASM


1) These are the current control files in non-asm

SQL>show parameter contol_files
         control_files                        string      /u01/data/learndb/control01.ctl,
                                                                 /u01/data/learndb/control02.ctl

2) Start the instance in nomount state

SQL> startup nomount

3) You can move these control files to ASM using RMAN

    [oracle@coltdb04 ~]$ rman target /

    Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 29 03:04:39 2012

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

    connected to target database: LEARNDB (not mounted)


    RMAN>restore controlfile to '+DATA' from '/u01/data/learndb/control01.ctl';


4) You can check whether the control file are created

   ASMCMD> find -t CONTROLFILE +DATA *
   WARNING:option 't' is deprecated for 'find'
   please use 'type'

   +DATA/LEARNDB/CONTROLFILE/current.256.787200593

5) Update the spfile with new control file location

   SQL> alter system set control_files='+DATA/LEARNDB/CONTROLFILE/current.256.787200593' scope=spfile;

6) Now shutdown the instance and startup in mount stage and check for the control file location

   SQL> shutdown immediate

   SQL> startup mount

   SQL> show parameter control_files

       NAME                                 TYPE        VALUE
     ------------------------------------ ----------- ------------------------------
     control_files                        string      +DATA/learndb/controlfile/curr
                                                      ent.256.787200593


Migrating the Datafiles from Non-ASM to ASM

1) Keep the database is in mount stage

2) Use RMAN "BACKUP AS COPY" method to migrate the datafiles from filesystem to ASM

   [oracle@coltdb04 ~]$ rman target /

   Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 29 03:00:40 2012

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

   connected to target database: LEARNDB (DBID=3704858930, not open)
                                         

   RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

3)  Switch the database to the copy created using the following RMAN command

   RMAN> SWITCH DATABASE TO COPY;

   datafile 1 switched to datafile copy "+DATA/learndb/datafile/system.258.787201633"
   datafile 2 switched to datafile copy "+DATA/learndb/datafile/sysaux.257.787201553"
   datafile 3 switched to datafile copy "+DATA/learndb/datafile/undotbs1.259.787201713"
   datafile 4 switched to datafile copy "+DATA/learndb/datafile/users.261.787201725"

4) Migrate the tempfile to ASM using RMAN

   RMAN> run
   2> {
   3> set newname for tempfile '/u01/data/learndb/temp01.dbf' to '+DATA';
   4> switch tempfile all;
   5> }

   executing command: SET NEWNAME

   using target database control file instead of recovery catalog

   renamed tempfile 1 to +DATA in control file

5) Now open the Database

   RMAN> ALTER DATABASE OPEN;

   database opened

6) You can now check the datafiles created in ASM

   SQL> select FILE_NAME from dba_data_files;

   FILE_NAME
   --------------------------------------------------------------------------------
   +DATA/learndb/datafile/users.261.787201725
   +DATA/learndb/datafile/undotbs1.259.787201713
   +DATA/learndb/datafile/sysaux.257.787201553
   +DATA/learndb/datafile/system.258.787201633


Migrating the Redo log files from Non-ASM to ASM

1) Identify the currently available redo log files using the following command

   SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

    GROUP#           MEMBER                STATUS
   ----------       ---------              -----------

        3    /u01/data/learndb/redo03.log INACTIVE

        2    /u01/data/learndb/redo02.log CURRENT

        1    /u01/data/learndb/redo01.log INACTIVE

2) Add the new logfiles to ASM using following command

   ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 1; 
   ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2;
   ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 3;

3) Now drop the old logfiles, A redo log member can only be dropped after being archived and being in INACTIVE mode. If needed, switch logfile multiple times until the logfile is ready for dropping.
   ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo01.log';
   ALTER SYSTEM SWITCH LOGFILE;
   ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo02.log';
   ALTER SYSTEM SWITCH LOGFILE;
   ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo03.log';

4) You can check the logfiles created now in ASM

     GROUP#           MEMBER                STATUS
   ----------       ---------              -----------
         1    +DATA/learndb/onlinelog/group_1.264.787205393   CURRENT

         2    +DATA/learndb/onlinelog/group_2.265.787205405   INACTIVE

         3    +DATA/learndb/onlinelog/group_3.266.787205417   INACTIVE

Read More

Oracle DATAGUARD Broker configuration steps

Prerequisites:

1.       Set the initialization parameter DG_BROKER_START value TRUE on both DB’s, So that DMON process would start automatically.

2.       Create the network configuration files on both primary and stand by sides to incorporate the modification to DB_UNIQUE_NAME.

3.       Add static database registration entry on both database tnsnames.ora configuration file, So that each database could communicate with the Data Guard Broker Management Utility DGMGRL.

Configuration Steps:

Primary] $ dgmgrl

DGMGRL > connect  sys

Password:

Connected.

Create configuration command to initialize the DG broker configuration on primary database side.

DGMGRL> Create configuration ‘PR_ORCL’ as primary database is primary_db 

>connect identifier is orcl;

To check the configuration

DGMGRL> show configuration

Adding the standby database to the DG broker configuration

DGMGRL> Add database ‘ST_ORCL’ As 

>Connect identifier is standby;

Verify the configuration:

DGMGRL> show configuration verbose;

 Configuration

  Name:                PR_ORCL

  Enabled:             NO

  Protection Mode:     MaxPerformance

  Databases:

    primary_db - Primary database

    stdby_db   - Physical standby database

 Fast-Start Failover: DISABLED

 Current status for "PR_ORCL":

DISABLED

 DGMGRL> show database ST_ORCL

 Database

  Name:            ST_ORCL

  Role:            PHYSICAL STANDBY

  Enabled:         NO

  Intended State:  OFFLINE

  Instance(s):

    orcl

 Current status for "ST_ORCL":

DISABLED

Enable the DG Broker configuration:

DGMGRL> enable configuration;

 Enabled.

 DGMGRL> show database PR_ORCL;

 Database

  Name:            PR_ORCL

  Role:            PRIMARY

  Enabled:         YES

  Intended State:  TRANSPORT-ON

  Instance(s):

    orcl

 Current status for "PR_ORCL":

SUCCESS

 DGMGRL> show database ST_ORCL;

 Database

  Name:            ST_ORCL

  Role:            PHYSICAL STANDBY

  Enabled:         YES

  Intended State:  APPLY-ON

  Instance(s):

    orcl

 Current status for "ST_ORCL":

SUCCESS

Performing switchover:

Using Data Guard Broker to Perform a Switchover

DGMGRL> SWITCHOVER TO ST_ORCL;

 Performing switchover NOW, please wait...

New primary database "ST_ORCL" is opening...

Operation requires shutdown of instance "orcl" on database "PR_ORCL"

Shutting down instance "orcl"...

ORA-1109: database not open

Database dismounted.

ORACLE instance shut down.

 Operation requires startup of instance "orcl" on database "ST_ORCL"

Starting instance "orcl"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "ST_ORCL"

Using Data Guard Broker to Perform a Switchback

DGMGRL> SWITCHOVER TO PR_ORCL;

Performing switchover NOW, please wait...

New primary database "ST_ORCL" is opening...

Operation requires shutdown of instance "orcl" on database "ST_ORCL"

Shutting down instance "orcl"...

ORA-1109: database not open

Database dismounted.

ORACLE instance shut down.

 Operation requires startup of instance "orcl" on database "PR_ORCL"

Starting instance "orcl"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "PR_ORCL"

Read More

How to convert physical standby database to Active Dataguard 11g R2

Active Dataguard:

Oracle Active Data Guard is a separately licensed database option for Oracle Database 11g Enterprise Edition. 

It includes the Real-time Query feature which enables a physical standby database to be open in read-only mode while Redo Apply is active(MRP). 

Users who are connected to a physical standby database can query and report against data that is up-to-date with the primary database.

It also enables you to configure RMAN BCT(Block Change Tracking) for a physical standby database. 

With RMAN BCT, you can offload fast incremental backups from the production database to the physical standby database.

 Step 1: You must have physical standby database configured in your environment.

NAME      DB_UNIQUE_NAME         DATABASE_ROLE      OPEN_MODE           CONTROL SWITCHOVER_STATUS

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

DC      DR                 PHYSICAL STANDBY MOUNTED           STANDBY NOT ALLOWED

MAX(SEQUENCE#)

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

       129

SQL> 

SQL> select process,status from v$managed_standby;

PROCESS   STATUS

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

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

MRP0      WAIT_FOR_LOG

Step 2: See your MRP process is active. If you try to open the database, you will get below error. You need to stop the MRP process and then open the database.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-10456: cannot open standby database; media recovery session may be in progress

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select process,status from v$managed_standby;

PROCESS   STATUS

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

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CONNECTED

SQL> alter database open;

Database altered.

SQL> 

NAME      DB_UNIQUE_NAME         DATABASE_ROLE      OPEN_MODE           CONTROL SWITCHOVER_STATUS

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

DC      DR                 PHYSICAL STANDBY READ ONLY           STANDBY NOT ALLOWED

MAX(SEQUENCE#)

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

       129

On Primary:

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> @rd

NAME      DB_UNIQUE_NAME         DATABASE_ROLE      OPEN_MODE           CONTROL SWITCHOVER_STATUS

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

DC      DC                 PRIMARY      READ WRITE           CURRENT TO STANDBY

MAX(SEQUENCE#)

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

       131

Step 3: Now start MRP process and see the DB status.

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

Database altered.

SQL> 

NAME      DB_UNIQUE_NAME         DATABASE_ROLE      OPEN_MODE           CONTROL SWITCHOVER_STATUS

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

DC      DR                 PHYSICAL STANDBY READ ONLY WITH APPLY STANDBY NOT ALLOWED

MAX(SEQUENCE#)

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

       131

Standby Database Alert Logfile:

Thu Aug 03 20:05:37 2017

Media Recovery Log /home/oracle/u01/1_129_947265431.arc

Media Recovery Waiting for thread 1 sequence 130 (in transit)

Archived Log entry 130 added for thread 1 sequence 130 rlc 947265431 ID 0x6308ffab dest 2:

RFS[2]: No standby redo logfiles created

RFS[2]: Opened log for thread 1 sequence 131 dbid 1661358039 branch 947265431

Media Recovery Log /home/oracle/u01/1_130_947265431.arc

Media Recovery Waiting for thread 1 sequence 131 (in transit)

Thu Aug 03 20:05:45 2017

Archived Log entry 131 added for thread 1 sequence 131 rlc 947265431 ID 0x6308ffab dest 2:

RFS[2]: No standby redo logfiles created

RFS[2]: Opened log for thread 1 sequence 132 dbid 1661358039 branch 947265431

Thu Aug 03 20:05:48 2017

Media Recovery Log /home/oracle/u01/1_131_947265431.arc

Media Recovery Waiting for thread 1 sequence 132 (in transit)

 You have successfully converted physical standby database into Active Dataguard.

Also convert back it into physical standby database using below method:

Step 1: You need to stop MRP process before converting back into physical standby database otherwise you will get below error message.

SQL> alter database close;

alter database close

*

ERROR at line 1:

ORA-10457: cannot close standby database due to active media recovery

SQL> alter database recover managed standby database cancel;

Database altered.

Step 2: Close the database instead of restarting it and check the database status.

SQL> alter database close;

Database altered.

SQL> 

NAME      DB_UNIQUE_NAME         DATABASE_ROLE      OPEN_MODE           CONTROL SWITCHOVER_STATUS

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

DC      DR                 PHYSICAL STANDBY MOUNTED           STANDBY NOT ALLOWED

MAX(SEQUENCE#)

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

       131

Step 3: Start the MRP process.

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

Database altered.

Step 4: Switch 2-3 logs from primary database and check logs are getting applied on standby from primary.

On Primary:

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

/

System altered.

SQL> 

System altered.

Standby Alert Log File:

Thu Aug 03 20:17:16 2017

Media Recovery Log /home/oracle/u01/1_132_947265431.arc

Media Recovery Log /home/oracle/u01/1_133_947265431.arc

Media Recovery Log /home/oracle/u01/1_134_947265431.arc

Media Recovery Log /home/oracle/u01/1_135_947265431.arc

Media Recovery Waiting for thread 1 sequence 136 (in transit)

SQL> 

NAME      DB_UNIQUE_NAME         DATABASE_ROLE      OPEN_MODE           CONTROL SWITCHOVER_STATUS

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

DC      DR                 PHYSICAL STANDBY MOUNTED           STANDBY NOT ALLOWED

MAX(SEQUENCE#)

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

       135

Read More

Convert the physical standby to snapshot standby database in oracle dataguard

A Snapshot Standby database still receives redo data from the primary but it does not apply the redo data until after it converted back to a physical standby. Keep in mind that a snapshot standby database cannot be the target of a switchover or failover. A snapshot must be converted back to a physical standby prior to performing a role transition. Flashback Database technology is used in the conversion process so the Fast (Flash) Recovery area must be configured.

This document will detail the steps to manually convert a physical standby to a snapshot standby.

Convert the Physical Standby Database into a Snapshot Standby Database

On the standby database stop redo apply.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> 

Next convert the standby database to a snapshot standby.

SQL> alter database convert to snapshot standby;

Database altered.

SQL> 

Once the conversion is complete all that is left is to open the database.

SQL> alter database open;

Database altered.

SQL> 

You can verify the role change by querying the DATABASE_ROLE from V$DATABASE.

SQL> select database_role from v$database;

DATABASE_ROLE

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

SNAPSHOT STANDBY

SQL> 

While the standby is in snapshot standby mode you are free to run transactions against the snapshot standby.

While the standby is in snapshot mode it still continues to receive redo data from the primary but it does not apply the redo data. You verify the transport by switching logs on the primary and looking at the alert log on the standby.

Thu Jun 17 11:15:41 2010

RFS[6]: Selected log 5 for thread 1 sequence 981 dbid 459961910 branch 719914169

Thu Jun 17 11:15:41 2010

Archived Log entry 1513 added for thread 1 sequence 980 ID 0x1b7c5492 dest 2:

RFS[6]: Selected log 4 for thread 1 sequence 982 dbid 459961910 branch 719914169

Thu Jun 17 11:15:42 2010

When the standby was converted to a snapshot a guaranteed restore point was created. You can see this in the alert log for the standby.

Thu Jun 17 09:44:44 2010

RVWR started with pid=30, OS id=9171

Allocated 3981120 bytes in shared pool for flashback generation buffer

Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_06/17/2010 09:44:44

krsv_proc_kill: Killing 3 processes (all RFS)

Begin: Standby Redo Logfile archival

End: Standby Redo Logfile archival

When the snapshot standby is converted back into a physical standby this restore point will be used to flashback the standby to its original state prior to the conversion. If any operation is performed on the snapshot that cannot be reversed with Flashback Database will prevent the snapshot standby from being converted back to a physical standby.

Convert the Snapshot Standby Database back to a Physical Standby Database

Shutdown the snapshot standby database and bring it back up in the mount state.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount 

ORACLE instance started.

Total System Global Area  830930944 bytes

Fixed Size                  2217912 bytes

Variable Size             603981896 bytes

Database Buffers          222298112 bytes

Redo Buffers                2433024 bytes

Database mounted.

SQL> 

Next convert the snapshot to a physical standby.

SQL> alter database convert to physical standby;

Database altered.

SQL>

In the standby alert log you can see that Flashback restore completed and the restore point was dropped.

Thu Jun 17 11:48:29 2010

alter database convert to physical standby

ALTER DATABASE CONVERT TO PHYSICAL STANDBY (standby)

krsv_proc_kill: Killing 4 processes (all RFS)

Flashback Restore Start

Flashback Restore Complete

Stopping background process RVWR

Deleted Oracle managed file /u01/app/flash_recovery_area/STANDBY/flashback/o1_mf_61nf6w8g_.flb

Deleted Oracle managed file /u01/app/flash_recovery_area/STANDBY/flashback/o1_mf_61ngk05r_.flb

Guaranteed restore point  dropped

Clearing standby activation ID 461943091 (0x1b88b133)

The primary database controlfile was created using the

'MAXLOGFILES 16' clause.

There is space for up to 13 standby redo logfiles

Use the following SQL commands on the standby database to create

standby redo logfiles that match the primary database:

ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;

ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;

Completed: alter database convert to physical standby

Shutdown the database and bring it back to the mount state.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  830930944 bytes

Fixed Size                  2217912 bytes

Variable Size             603981896 bytes

Database Buffers          222298112 bytes

Redo Buffers                2433024 bytes

Database mounted.

SQL> 

If you take a look in the alert log you will see that the archive logs shipped when the standby was a snapshot standby are now applied.

Media Recovery Log /u01/app/oracle/oradata/standby/arch/1_970_719914169.dbf

Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_971_719914169.dbf

Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_972_719914169.dbf

Media Recovery Log /u01/app/oracle/oradata/standby/arch/1_973_719914169.dbf

Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_974_719914169.dbf

Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_975_719914169.dbf

Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_976_719914169.dbf

Media Recovery Log /u01/app/oracle/oradata/standby/arch/1_977_719914169.dbf

Using Snapshot Standby you can leverage your standby for testing or other special purposes temporarily will still protecting your primary database.

Read More