Reduce Recovery Time By Using Datafile Backup Copy

One of the strategy to reduce the recovery time or downtime of database is to use backup copy of datafile. Backup copy helps by eliminating the process of restoring datafile from backup set, thus no restore time. The recovery is done by switching the database to use the backup copy.

Here are the general steps of restoring & recovering datafile using backupset:

1. Making the datafile offline
2. Restore datafile from backup set
3. Applying incremental backup
4. Recover datafile using the archived log
5. Make datafile online

And here are the steps using the switch approach (using the backup copy):

1. Making datafile offline
2. Switch to copy
3. Recover datafile
4. Making Datafile online

The reduction of downtime is achieved by skipping the datafile restoration, which could take a very long time depending on the datafile size.

The following example simulate the recovery of datafile 6.

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name LASTEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1500 SYSTEM *** /oradata/LASTEST/dbf/dbfsystem01.dbf
2 1500 SYSAUX *** /oradata/LASTEST/dbf/dbfsysaux01.dbf
3 365 UNDOTBS1 *** /oradata/LASTEST/dbf/dbfundotbs01.dbf
4 480 USERS *** /oradata/LASTEST/dbf/dbfusers01.dbf
5 20 DRSYS *** /oradata/LASTEST/dbf/drsys01.dbf
6 0 TEST *** /oradata/LASTEST/dbf/test.dbf
8 10 TOOLS *** /oradata/LASTEST/dbf/tools01.dbf
9 70 XDB *** /oradata/LASTEST/dbf/xdb01.dbf
19 250 LOGMNRTS *** /oradata/LASTEST/dbf/logmnrts_01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 328 TEMP 32767 /oradata/LASTEST/dbf/dbftemp01.dbf

RMAN> list copy of datafile 6;

List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
82 6 A 26-AUG-10 23408928202 26-AUG-10
Name: /orabackup/flash_recovery_area/LASTEST/datafile/o1_mf_test_67cjwvcc_.dbf
Tag: TAG20100826T093002


RMAN> sql 'alter database datafile 6 offline';

sql statement: alter database datafile 6 offline

RMAN> switch datafile 6 to copy;

datafile 6 switched to datafile copy "/orabackup/flash_recovery_area/LASTEST/datafile/o1_mf_test_67cjwvcc_.dbf"

RMAN> recover datafile 6;

Starting recover at 26-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 26-AUG-10

RMAN> sql 'alter database datafile 6 online';

sql statement: alter database datafile 6 online

RMAN> sql 'alter database open';

sql statement: alter database open

RMAN> exit

Now that we have quickly recover datafile 6, we could find a suitable time to switch the datafile back to the original location.

r-bash-3.2$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Aug 26 09:38:28 2010

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

connected to target database: LASTEST (DBID=2460905176)

RMAN> backup as copy datafile 6 format ='/oradata/LASTEST/dbf/test01.dbf';

Starting backup at 26-AUG-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/orabackup/flash_recovery_area/LASTEST/datafile/o1_mf_test_67cjwvcc_.dbf
output file name=/oradata/LASTEST/dbf/test01.dbf tag=TAG20100826T093907 RECID=83 STAMP=728041148
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 26-AUG-10

Starting Control File and SPFILE Autobackup at 26-AUG-10
piece handle=/orabackup/flash_recovery_area/LASTEST/autobackup/2010_08_26/o1_mf_s_728041149_67ckfy19_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-AUG-10

RMAN>

RMAN>

RMAN> sql 'alter database datafile 6 offline';

sql statement: alter database datafile 6 offline

RMAN> switch datafile 6 to copy;

datafile 6 switched to datafile copy "/oradata/LASTEST/dbf/test01.dbf"

RMAN> recover datafile 6;

Starting recover at 26-AUG-10
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 26-AUG-10

RMAN> sql 'alter database datafile 6 online';

sql statement: alter database datafile 6 online

RMAN>

Selecting Column from Oracle User Defined Type (Nested Table)

this is how to select a single column from a multi-column nested table.

CREATE OR REPLACE TYPE question_obj AS OBJECT
(QUESTIONSEQ VARCHAR2(10),
QUESTION_LABEL VARCHAR2 (200 BYTE),
TYPESEQ NUMBER (2),
COMPULSORY NUMBER (1),
MAX_ANSWER NUMBER (2),
GROUPSEQ NUMBER (2),
ORDER_NO NUMBER (2))
/


CREATE OR REPLACE TYPE question_tbl is table of question_obj;
/



DECLARE
l_questions   question_tbl;
BEGIN
-- this following statement would populate the nested table with data
-- from a table
SELECT question_obj (q.questionseq,
                   q.question_label,
                   q.typeseq,
                   q.compulsory,
                   q.max_answer,
                   q.groupseq,
                   q.order_no)
BULK COLLECT
INTO l_questions
FROM QUESTION q
WHERE q.surveyseq = '2010050195';

-- now let's demonstrate how to select from a nested table
FOR r IN (SELECT q.questionseq
               FROM TABLE (l_questions) q
              WHERE q.questionseq IS NOT NULL)
LOOP
 DBMS_OUTPUT.put_line (r.questionseq);
END LOOP;
END;

Note: You can NOT use sql againts PLSQL table, that is you declare the type in PLSQL declaration or package specification

It's been a while...

It's been a long time since i write in this blog.

When i was helping my colleague with PLSQL, he suggested me to keep a blog of things that I have done or learned so that i could always easily refer back to it. What he said reminded me that i have this blog, waiting for me to write again.

I think it'd be useful to keep a blog about this than relying on search engine and trying to find article that suits your use case. Human brain is fantastic, you would easily remember in the back of your head about things you had done before though you may have forgotten how you did it.
That's where the blog will come in and help you.

Database Control Shows - Java.Lang.Exception: No Such Metric after login

Applies to:

Enterprise Manager for RDBMS - Version: 10.2.0.1.0
This problem can occur on any platform.

Symptoms

ERROR
java.lang.Exception: no such metric

is displayed when logging into database control

emoms.log file shows the error -

ORA-28000: the account is locked (DBD ERROR: OCISessionBegin)

Also HOST CPU shows no data

Cause

This is due to the dbsnmp account being locked.

Solution

To implement the solution, please execute the following steps:

1. Unlock dbsnmp account.
2. Restart dbconsole


Taken directly from metalink Note:333490.1

Configure Oracle 10g Database Control

Database Control is basically a web based Monitoring tools for Oracle 10g Architecture. It is provided to ease DBA's job.
The different between Database Control and Grid Control is you can only use Database Control to manage single instance. So, if you have 10 instance you will need 10 database control. In contrast, with Grid Control everything is centralised.

Grid Control is supported with Enterprise Edition & Personal Edition, if you are using Standard Edition then you can use Database Control.

You can configure Database Control during database creation with DBCA, or you can configure it after the database creation using command line.

Use 'emca' binary to configure database control.

Configure DB Control
$ cd $ORACLE_HOME/bin
$ ./emca -config dbcontrol db

STARTED EMCA at 16/08/2006 18:04:35
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: SNZDB
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /app/oracle/product/10.2.0

Database hostname ................ OLSSWIFTCS0001
Listener port number ................ 1521
Database SID ................ SNZDB
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
16/08/2006 18:08:34 oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /app/oracle/product/10.2.0/cfgtoollogs/emca/SNZDB/emca_2006-08-16_06-04-35-PM.log.
16/08/2006 18:08:42 oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
16/08/2006 18:10:24 oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
16/08/2006 18:10:24 oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://OLSSWIFTCS0001:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Aug 16, 2006 6:10:24 PM

RMAN : Restore SPFILE from autobackup

SPFILE is included in the controlfile autobackup. Therefore, If you loose it you can use RMAN to restore it from the autobackup.

Use below command :

RMAN> restore SPFILE from autobackup db_recovery_file_dest='/app/oracle/flash_recovery_area' db_name = 'orcl';

if you don't specify db_recovery_file_dest, by default RMAN will try to look the autobackup in $ORACLE_HOME/dbs (UNIX).

Before you can execute above command, you need to start the instance.
Use STARTUP FORCE NOMOUNT to start the instance without initialization parameter file.

RMAN : Improving Incremental Backup Performance: Change Tracking

RMAN's change tracking feature for incremental backups improves incremental backup performance by recording changed blocks in each datafile in a change tracking file. If change tracking is enabled, RMAN uses the change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan every block in the datafile.

After enabling change tracking, the first level 0 incremental backup still has to scan the entire datafile, as the change tracking file does not yet reflect the status of the blocks. Subsequent incremental backup that use this level 0 as parent will take advantage of the change tracking file.

Using change tracking in no way changes the commands used to perform incremental backups, and the change tracking files themselves generally require little maintenance after initial configuration.

Change tracking is disabled by default, because it does introduce some minimal performance overhead on your database during normal operations. However, the benefits of avoiding full datafile scans during backup are considerable, especially if only a small percentage of data blocks are changed between backups. If your backup strategy involves incremental backups, then you should enable change tracking.

One block change tracking file is created for the whole database. By default, the block change tracking file is created as an Oracle managed file in DB_CREATE_FILE_DEST. You can also specify the name of the block change tracking file, placing it in any location you choose.

How to enable Change Tracking:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/mydir/rman_change_track.f' REUSE;

You can view the status,filename,filesize
of Change Tracking in V$BLOCK_CHANGE_TRACKING view

How to Change the DBID and the DBNAME by using NID

The NID (New Database ID) is a new utility introduced with Oracle 9.2. The NID utility allows you to change only the DBNAME, or only the DBID or both DBNAME and DBID in the same command.

Notes:

1. If you change the DBID you must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1.
2. If you change the DBNAME without changing the DBID then this does not require you to open with the RESETLOGS option, so database backups and archived logs are not invalidated. You must change the DB_NAME initialization parameter after a database name change to reflect the new name. Also, you may have to re-create the Oracle password file. If you restore an old backup of the control file (before the name change, then you should use the initialization parameter file and password file from before the database name change.

Steps to use NID :
=============

1. Backup the database
2. SHUTDOWN IMMEDIATE of the database
3. STARTUP MOUNT
4. Open one session and run NID with sysdba privileges
5. Shutdown IMMEDIATE of the database
   
6. Set the DB_NAME initialization parameter in the initialization parameter file to the new database name
7. Create a new password file
8. Startup of the database with open resetlogs/no resetlogs

NID Usage:
========

TARGET --> Username/Password
DBNAME --> New database name
LOGFILE --> Output Log
REVERT --> Revert failed change (NO)
SETNAME --> Set a new database name only (NO)
APPEND --> Append to output log (NO)
HELP --> Displays these messages (NO)