Oracle DBA experience

Wednesday, October 19, 2011

RESTORE CONTROLFILE FROM AUTOBACKUP ON MODE (WITH OUT RECOVERY CATALOG )

I have lost my spfile and controlfile which are stored in the ASM disk and now I am trying to restore the spfile & controlfile from the autobackup and it is not enabled with the recovery catalog. This article covers only the controlfile restore and i have updated another article with SPFILE restore.

Note: This is for the RAC database and I am started with one instance till I complete the entire process, i am not going to do anything on the second instance. I am doing the autobackup to the TAPE.

Note: when you are allocating the channel & no of channels, you have specified

Accordingly to system perform. It can be either from disk & tape. In the below example i have used TAPE. so i have used the below option.

RESTORING CONTROLFILE (WITH OUT RECOVERY CATALOG & AUTOBACKUP ON MODE)

bhuora01[BHU_1]>rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Oct 6 17:26:15 2011

connected to target database: BHU (not mounted)

RMAN> set DBID 1726821198;

executing command: SET DBID

RMAN> run

{

allocate channel 'dev_0' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BHU_A,OB2BARLIST= bhuora01_ORA_BHU_ON_Weekly)';

restore controlfile from autobackup;

}

2> 3> 4> 5> 6>

using target database control file instead of recovery catalog

allocated channel: dev_0

channel dev_0: SID=67 instance=BHU_1 device type=SBT_TAPE

channel dev_0: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_

Starting restore at 06-OCT-11

channel dev_0: looking for AUTOBACKUP on day: 20111006

channel dev_0: AUTOBACKUP found: c-1726821198-20111006-00

channel dev_0: restoring control file from AUTOBACKUP c-1726821198-20111006-00

channel dev_0: control file restore from AUTOBACKUP complete

output file name=+BHU_DATA1/BHU_a/controlfile/current.271.763839227

output file name=+BHU_ARCH/BHU_a/controlfile/current.369.763839227

output file name=+BHU_RECO/BHU_a/controlfile/current.281.763839227

Finished restore at 06-OCT-11

released channel: dev_0

RMAN>

Note: Here the controlfile locations are picked up from the SPFILE.

Once you restore the control file, you have to check the control_files values and if it different in the ASM DISKGROUP then you have to modify the control_files values in the spfile. You have to issue a alter system command to update for entire instances with sid='*' and start the RAC database.

RESTORE SPFILE FROM AUTOBACKUP MODE (WITH OUT RECOVERY CATALOG)

I have lost my spfile and controlfile which are stored in the ASM disk and now I am trying to restore the spfile & controlfile from the autobackup and it is not enabled with the recovery catalog. This article cover only the spfile restore and i will update a another article with controlfile restore.

Note: This is for the RAC database and I am started with one instance till I complete the entire process, i am not going to do anything on the second instance. I am doing the autobackup to the TAPE.

Note: when you are allocating the channel & no of channels, you have specified

Accordingly to system perform. It can be either from disk & tape. In the below example i have used TAPE. so i have used the below option.

RESTORING SPFILE (WITH OUT RECOVERY CATALOG & AUTOBACKUP ON MODE)

STEP#1 START THE DB WITH TEMP PFILE(you can get it from alert log, you will get the main parameters)

bhuora01[BHU_1]>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 6 17:08:55 2011

Connected to an idle instance.

SQL> startup nomount pfile='file.ora';

ORACLE instance started.

Total System Global Area 1219334144 bytes

Fixed Size 2225952 bytes

Variable Size 620759264 bytes

Database Buffers 587202560 bytes

Redo Buffers 9146368 bytes

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Note: when you don’t have the recovery catalog, you have to pickup the DBID from your alert log.

STEP#2 START THE RESTORE FROM AUTOBACKUP MODE TO THE ASM

bhuora01[BHU_1]>rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Oct 6 17:12:36 2011

connected to target database: BHU (not mounted)

RMAN> set DBID 1726821198;

executing command: SET DBID

RMAN> run

{

allocate channel 'dev_0' type 'sbt_tape'

parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BHU_A,OB2BARLIST=bhuora01_ORA_BHU_ON_Weekly)';

restore spfile to '+BHU_DATA1' from autobackup;

}2> 3> 4> 5> 6>

using target database control file instead of recovery catalog

allocated channel: dev_0

channel dev_0: SID=80 instance=BHU_1 device type=SBT_TAPE

channel dev_0: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_

Starting restore at 06-OCT-11

channel dev_0: looking for AUTOBACKUP on day: 20111006

channel dev_0: AUTOBACKUP found: c-1726821198-20111006-00

channel dev_0: restoring spfile from AUTOBACKUP c-1726821198-20111006-00

channel dev_0: SPFILE restore from AUTOBACKUP complete

Finished restore at 06-OCT-11

released channel: dev_0

RMAN>

Note: you can restore directly to the ASM instance by specifying the “to” option. If you are not specifying “to” option then it will restore the spfile in the $ORACLE_HOME/dbs folder of the restoring instance.

restore spfile to '+BHU_DATA1' from autobackup;

STEP#3 STOP & START THE DB WITH NEW SPFILE

bhuora01[BHU_1]>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 6 17:20:24 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> exit

Note: since I am running on the RAC environment, I am changing the SPFILE CONFIGURATION DETAILS USING SRVCTL. To change to the new spfile configuration details

bhuora01[BHU_1]> srvctl modify database –d BHU_A –p '+BHU_DATA1/BHU_A/PARAMETERFILE/spfile.263.762593853

you can start the database

bhuora01[BHU_1]> srvctl start database –d BHU_A

you can check the status of the cluster database

bhuora01[BHU_1]> srvctl status database -d BHU_A

Instance BHU_1 is running on node bhuora01

Instance BHU_2 is running on node bhuora02

Changing Apply Instance on Physical standby database in RAC environment

Primary DB è BHU_A

Physical standby DB è BHU_B

TO LIST THE CONFIGURATION OF THE BROKER CONFIGURATION

DGMGRL> show configuration;

Configuration - DG_BHU

Protection Mode: MaxAvailability

Databases:

BHU_A - Primary database

BHU_B - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DISPLAY DB CONFIGURATION DETAILS

DGMGRL> show database 'BHU_B';

Database - BHU_B

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds

Apply Lag: 0 seconds

Real Time Query: OFF

Instance(s):

BHU_1 (apply instance)

BHU_2

Database Status:

SUCCESS

DGMGRL> exit

Note: we have the apply instance on Node-1(BHU_1 instance) and we are moving the apply instance to the node-2(BHU_2 instance) of the standby database

bhuora01[BHU_1]>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Oct 19 13:41:22 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Now Login to Broker and checking the status

bhuora01[BHU_1]>dgmgrl

DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys

Password:

Connected.

DGMGRL> show configuration;

Configuration - DG_BHU

Protection Mode: MaxAvailability

Databases:

BHU_A - Primary database

BHU_B - Physical standby database

Error: ORA-16766: Redo Apply is stopped

Fast-Start Failover: DISABLED

Configuration Status:

ERROR

DGMGRL> show database 'BHU_B';

Database - BHU_B

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds

Apply Lag: 34 seconds

Real Time Query: OFF

Instance(s):

BHU_1 (apply instance)

BHU_2

Database Error(s):

ORA-16766: Redo Apply is stopped

Database Status:

ERROR

DGMGRL>

Now Login in to the second instance(BHU_2) of the standby database

bhuora02[BHU_2]>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Wed Oct 19 13:40:35 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

SQL>

Now it should moved to the second instance of the standby database.

DGMGRL> show database 'BHU_B';

Database - BHU_B

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds

Apply Lag: 0 seconds

Real Time Query: OFF

Instance(s):

BHU_1

BHU_2 (apply instance)

Database Status:

SUCCESS

DGMGRL> show configuration;

Configuration - DG_BHU

Protection Mode: MaxAvailability

Databases:

BHU_A - Primary database

BHU_B - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

Monday, October 17, 2011

RELOCATING SCAN VIP FROM ONE NODE TO ANOTHER NODE

TO CHECK THE SCAN DETAILS

$ srvctl status scan

SCAN VIP scan1 is enabled

SCAN VIP scan1 is running on node bhuora02

SCAN VIP scan2 is enabled

SCAN VIP scan2 is running on node bhuora01

SCAN VIP scan3 is enabled

SCAN VIP scan3 is running on node bhuora01

OPTION FOR RELOCATING THE SCAN

$ srvctl relocate scan -h

Temporarily relocate SCAN VIP from one node of the cluster to another.

Usage: srvctl relocate scan -i <ordinal_number> [-n <node_name>]

-i <scan_ordinal_number> Ordinal number of IP address

-n <node_name> SCAN VIP node name

-h Print usage

I AM RELOCATING SCAN VIP FROM “bhuora01” TO “bhuora02”

$ srvctl relocate scan -i 2 -n bhuora02

TO CHECK THE SCAN DETAILS

$ srvctl status scan

SCAN VIP scan1 is enabled

SCAN VIP scan1 is running on node bhuora02

SCAN VIP scan2 is enabled

SCAN VIP scan2 is running on node bhuora02

SCAN VIP scan3 is enabled

SCAN VIP scan3 is running on node bhuora01

After completing the relocate, check the SCAN LISTENER are running fine. By Default SCAN LISTENER should run with out any issues.

$ srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled

SCAN listener LISTENER_SCAN1 is running on node bhuora02

SCAN Listener LISTENER_SCAN2 is enabled

SCAN listener LISTENER_SCAN2 is running on node bhuora02

SCAN Listener LISTENER_SCAN3 is enabled

SCAN listener LISTENER_SCAN3 is running on node bhuora01

Monday, October 10, 2011

ORA-16820: fast-start failover observer is no longer observing this database & ORA-16819: fast-start failover observer not started

ORA-16820: fast-start failover observer is no longer observing this database

PROBLEM NOTE:

When I try to start the Observer from the Observer server, my configuration output shows that

ORA-16820: fast-start failover observer is no longer observing this database.

DGMGRL> show configuration verbose;

Configuration - DG_Bhuvan

Protection Mode: MaxAvailability

Databases:

BHUVAN_A - Primary database

Error: ORA-16820: fast-start failover observer is no longer observing this database

BHUVAN_B - (*) Physical standby database

Error: ORA-16820: fast-start failover observer is no longer observing this database

(*) Fast-Start Failover target

Properties:

FastStartFailoverThreshold = '30'

OperationTimeout = '30'

FastStartFailoverLagLimit = '30'

CommunicationTimeout = '180'

FastStartFailoverAutoReinstate = 'TRUE'

FastStartFailoverPmyShutdown = 'TRUE'

BystandersFollowRoleChange = 'ALL'

Fast-Start Failover: ENABLED

Threshold: 30 seconds

Target: BHUVAN_B

Observer: bhuoraobs001

Lag Limit: 30 seconds (not in use)

Shutdown Primary: TRUE

Auto-reinstate: TRUE

Configuration Status:

ERROR

DGMGRL>

-- VIEWING CONFIGURATION DETAILS FOR PRIMARY DB ON DG

DGMGRL> show database verbose 'BHUVAN_A';

Database - BHUVAN_A

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

BHUVAN_1

BHUVAN_2

Database Error(s):

ORA-16820: fast-start failover observer is no longer observing this database

Properties:

DGConnectIdentifier = 'BHUVAN_A_DG'

ObserverConnectIdentifier = ''

....

(*) - Please check specific instance for the property value

Database Status:

ERROR

-- VIEWING CONFIGURATION DETAILS FOR STANDBY DB ON DG

DGMGRL> show database verbose 'BHUVAN_B';

Database - BHUVAN_B

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds

Apply Lag: 0 seconds

Real Time Query: OFF

Instance(s):

BHUVAN_1

BHUVAN_2 (apply instance)

Database Error(s):

ORA-16820: fast-start failover observer is no longer observing this database

Properties:

DGConnectIdentifier = 'BHUVAN_B_DG'

ObserverConnectIdentifier = ''

....

(*) - Please check specific instance for the property value

Database Status:

ERROR

SOLUTION:

After doing a lot of research we found that “DGConnectIdentifier" should be used when you try to connect from primary & standby database to start the observer respectively

Ex:

DGConnectIdentifier = 'BHUVAN_A_DG' – PRIMARY DB Configuration

DGConnectIdentifier = 'BHUVAN_B_DG' – STANDBY DB Configuration

STARTING OBSERVER IN BACKGROUND MODE

dgmgrl -logfile $HOME/observer.log sys/oracle@bhuvan_a "start observer" &

BHUVAN_A_DG =

(DESCRIPTION =

(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip)(PORT=1521))

(ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip)(PORT=1521))

(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME= BHUVAN_A))

)

BHUVAN_B_DG =

(DESCRIPTION =

(ADDRESS=(PROTOCOL=TCP)(HOST= node1-vip)(PORT=1521))

(ADDRESS=(PROTOCOL=TCP)(HOST= node2-vip)(PORT=1521))

(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME= BHUVAN_B ))

)

Other wise your observer will come down automatically

DGMGRL> show configuration verbose;

Configuration - DG_Bhuvan

Protection Mode: MaxAvailability

Databases:

BHUVAN_A - Primary database

BHUVAN_B - (*) Physical standby database

(*) Fast-Start Failover target

Properties:

FastStartFailoverThreshold = '30'

OperationTimeout = '30'

FastStartFailoverLagLimit = '30'

CommunicationTimeout = '180'

FastStartFailoverAutoReinstate = 'TRUE'

FastStartFailoverPmyShutdown = 'TRUE'

BystandersFollowRoleChange = 'ALL'

Fast-Start Failover: ENABLED

Threshold: 30 seconds

Target: BHUVAN _B

Observer: bhuoraobs001

Lag Limit: 30 seconds (not in use)

Shutdown Primary: TRUE

Auto-reinstate: TRUE

Configuration Status:

SUCCESS

DGMGRL>

ORA-16819: fast-start failover observer not started

ACTION: you have not started the Observer, so you have to start the observer

dgmgrl -logfile $HOME/observer.log sys/oracle@bhuvan_a "start observer" &

Thursday, October 6, 2011

DIFFERENTIAL & CUMULATIVE INCREMENTAL BACKUP - Understanding

DIFFERENTIAL & CUMULATIVE INCREMENTAL BACKUP - Understanding

The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy. Thus, an incremental level 0 backup is a full backup that happens to be the parent of incremental backups whose level is greater than 0. When you are planning for the Incremental backup in your system. You have start with incremental level 0 then you can proceed with Differential & cumulative incremental backup

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

NOTE: you should consider whether you want to spend more time on backup (or) restore/recovery.

If you are going for the FASTER BACKUP è differential incremental backup, it will do backup quickly. Since it has to take the backup only from last level 1 backup (if no level 1, then it takes a level 0 backup). But when you want to restore then you need to have all the differential level 1 backup and LEVEL 0 backup to restore.

If you are going for the FASTER RESTORE è cumulative incremental backup, it will little time in doing the backup. Since it will be taking the backup from the last level 0 backup(it will ignore even though you have the level 1 backup). During the restore, it requires only one LEVEL 0 backup and last LEVEL 1 cumulative backup.

A level 1 incremental backup can be either of the following types:

èDifferential incremental backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

IF YOU ARE NOT SPECIFYING THE WORD CUMULATIVE, THEN ORACLE WILL TAKE IT AS A DIFFERENTIAL BACKUP

è Cumulative incremental backup, which backs up all blocks changed after the most recent incremental backup at level 0

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Block Change Tracking

The block change tracking feature for incremental backups improves incremental backup performance by recording changed blocks in each data file in a block change tracking file. This file is a small binary file stored in the database area. RMAN tracks changed blocks as redo is generated.

NOTE: This will have slight performance degradation.

Wednesday, October 5, 2011

ORA-12154: TNS:could not resolve the connect identifier specified

DGMGRL> add database 'BHUVAN' as connect identifier is ‘BHUVAN_DG’ maintained as physical;

Error: ORA-12154: TNS:could not resolve the connect identifier specified

when i was trying to add the database to the DG broker, i was getting this issue in oracle 11gR2.

SOLUTION:
1) Check whether you have proper configured on both the nodes

A) LOCAL LISTENER -- it should be your VIP of the node

B) REMOTE_LISTENER -- it should be your SCAN name for the cluster database

C) LISTENER_NETWORK -
A network may contain multiple local and remote listeners. By default, all listeners are cross-registered with each other. By specifying a set of listeners in the LISTENER_NETWORKS initialization parameter, you can designate a subset of local listeners with a subset of remote listeners. Listeners specified by the LISTENER_NETWORKS parameter should not be specified by the LOCAL_LISTENER and REMOTE_LISTENER parameters.
The syntax of LISTENER_NETWORKS is as follows:

LISTENER_NETWORKS = '((NAME=network_name)
                      (LOCAL_LISTENER=["]listener_address[,...]["])
                      [(REMOTE_LISTENER=["]listener_address[,...]["])])'

NOTE: Listeners specified by the LISTENER_NETWORKS parameter should not be used in

the LOCAL_LISTENER and REMOTE_LISTENER parameters. Otherwise, cross registration will happen and connections will be redirected cross networks.

D) Check the listener status.