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