Rman backups

Database Recovery and Consistent VS Inconsistent Backups

Consistent Backups


Inconsistent Backups
If you shutdown your database using shutdown norml, shutdown immediate and shutdown transactional, you’ll have a consistent backup. Shutdown abort or shutdown force or if there is an instance failure , you’ll end up with an inconsistent database.
All uncommitted changes are rolled back Any committed changes are not rolled back automatticaly
The contents of the database buffer cache written to the datafiles on disk. Changes made to the database buffer cache aren’t written to the datafiles on disk.
All resources such as lock and latches are released. No need for instance recovery. All resources such as locks and latches are still held and aren’t released

Crash Recovery :

1. The Oracle server will perform an automatic crash recovery when you restart the instance.
2. Don’t need to restore or recovery tasks.
3. The server will use the information in the undo tablespace to perform automatic instance recovery by rolling back uncommitted transactions in the database.

Rolling Forward


Rolling Back


The Oracle server will update all data files with the information from the redo log file. The log files are always written to before the data is recorded in the data file. thus ,an instance recovery may usually leave the online log files “ahead” of the data files.


Uncommitted changes that were added to the data files during the roll forward operation are rolled back. Undo tablespace contents uncommitted changes to their Original states. At the end of rollback stage, only committed data at the time of the instance failure is retained in the data files.

FAST_START_MTTR_TARGET

1. The database server must apply all transactions b/w the last checkpoint and end of the redo log to the data files.
2. Number of seconds you want the crash recovery to take.
3. Oracle will try to recover the instance as close as possible to the time that you specify for the FAST_START_MTTR_TARGET
4. MAX value is 3,600 seconds (1 hour).

Media Recovery

1. The server won’t be able to automatically recover from such a catastrophe
2. Must provide the lost datafiles from backup.
3. If error occured, Find V$RECOVER_FILE view, which lists all files that need media recovery
4. You use two basic commands RERTORE OR RECOVER

You must do the following as part of a Media Recovery operation

1. Restore the necessary data files from backup ,either to the old or to an alternative location
2. Rename the data files , if necessary, so the database will know about their new location
3. Recover the data files (bring them up to date), if necessary, by applying redo information to them

To open the database after a Successful Restore and Recovery

1. You must have synchronized copies of all the control files
2. you must have synchronized online data files.
3. you must have at least one member of each redo log group

Deciding on the Appropriate Recovery Technique

1. If you run into logical errors, perform a TSPITR or consider using an appropriate flashback technique to make a point in time recovery.
2. If you have data corruption in a few blocks in a data files or a set of data files, use block media recovery.
3. If a user error affects a large set of tables or the entire database ,use the flashback feature to revert the database to a previous “good” time by undoing all the changes since that point in time.

RMAN performance problem

Known RMAN Performance Problems - Metalink Note : 247611.1

This morning, I have come across of a recently revised Metalink Note : 247611.1 (revised on 27-02.2007) which addressed several known performance problems with RMAN backup and recovery.

The note has workaround for the following bugs:

Bug 5219484 - CATALOG RESYNCS ARE VERY SLOW - ROUT TABLE HAS 6 MILLION ROWS +
Bug 5047758 - RMAN SLOW PERFORMANCE ON EXIT (UPDATING STATUS STATISTICS)
Bug 5247609 RMAN SLOW PERFORMANCE DURING REGISTER DATABASE/OPEN RESETLOGS
Bug 1551773 RMAN RESTORE RUNS VERY SLOWLY WHEN THERE ARE MANY ROWS IN THE CKP TABLE
Bug 2803823 RESYNC CATALOG AND MAINTENANCE COMMANDS ARE SLOW
Bug 4013855 - RMAN CROSSCHECK IS TAKING A LONG TIME TO COMPLETE
Note 339964.1 RMAN 8.1.7 Crosscheck Command Is Very Slow
Bug 4548861 RMAN RESTORE ARCHIVELOG UNTIL SEQUENCE TAKES A LONG TIME TO COMPLETE
Note 342999.1 First Resync on Production Host After Running RMAN on
Bug 2876717 RMAN backup starts SLOW if READ-ONLY device is used
Bug 1887868 RMAN RESYNC takes a long time with many tablespaces
Internal Bug 1712720 RMAN MAINTENANCE COMMANDS ARE VERY SLOW
Bug 2174697 RMAN incremental backup performance degrades over time in NOCATALOG mode
Bug 2385857 RMAN backup performance degrades over time for large DB_FILES
Bug 2484250 RMAN CUMULATIVE BACKUP / MAINTENENCE may appear to hang (spin)
Bug 2610596 Huge CPU usage from RMAN server when TAPE not mounted with BACKUP_TAPE_IO_SLAVE=TRUE
Bug 2968394 RESYNC TAKES A LONG TIME TO COMPLETE : LARGE BP TABLE
Bug 1407413 NO INDEX TO ON RI CONSTRAINT BETWEEN BS AND BDF
Bug 4110489 RMAN SKIP READ ONLY TABLESPACE CLAUSE TAKING TOO MUCH TIME TO SKIP
Bug 3966722 CONCURRENT RMAN BACKUPS WAIT ON TM ENQUEUE ON CKP TABLE
Bug 2710321 X$KCCFN / V$DATAFILE much slower in 9i than 8i with many datafiles

RMAN block change tracking

Bug 4200702 - BLOCK CHANGE TRACKING WHEN USING DIRECT LOAD

According to Metalink Note : 300989.1, when block change tracking enabled and using direct load operations, it is like to get an ORA-19694 on Oracle 10.0.1.x version specific to all the platforms due to a bug 4200702.

Possible Symptoms
~~~~~~~~~~~~~~~~~
RMAN-571: ==========================================================
RMAN-569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-571: ========================================================== RMAN-3009: failure of backup command on ORA_DISK_1 channel at 02/14/2005 ORA-19694: some changed blocks were not found in the change tracking file

Workaround would be disabling change tracking feature.

According to the note, this is fixed in a patchset 10.1.0.5 and one-off patches are available for 10.1.0.4 and 10.1.0.3.

I guess, who are on 10gR2 and enabled the BCT feature doesn’t have this bug.

Controlfile autobackup on

CONTROLFILE AUTOBACKUP should be TURNED ON??

RMAN is one of the very useful utility provided by Oracle for backup and recovery Purpose. Oracle online backups were introduced with Version 6, where tablespace must be kept in backup mode in order to take online backups.

RMAN was first introduced with Version 8 through which database server database can be backup. Since then, Oracle has enhanced RMAN features drastically by every release/version. I am not going to discuss neither the RMAN history nor the backup.

I would like to mention here, how enabling few default parameters of RMAN can save the life of dba significantly.

RMAN has following default parameters and its default values:

RMAN> show all;

using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO /app/oracle/dbs/snapcf_EHEALTH.f'; # default
I would be talking the benefits of CONTROLFILE AUTOBACKUP.

By default CONTROLFILE AUTOBACKUP is OFF. I would strongly recommend enabling CONTROLFILE AUTOBACKUP ON.

I remember in my early days of DBA job, a lot of notes/articles and many people use to suggest backup the controlfile immediately after any maintenance on the tablespaces, which was a good suggestion.

RMAN just takes away this head ache of backing controlfile after any maintenance on tablespace, when you turn ON CONTROLFILE AUTOBACKUP feature.

Benefits:

With a control file autobackup, RMAN can recover the database even if the current control file, recovery catalog, and server parameter file are inaccessible.

A control file autobackup lets you restore the RMAN repository contained in the control file when the control file is lost and you have no recovery catalog. You do not need a recovery catalog or target database control file to restore the control file autobackup.

Whenever you add a datafile/s, resize, increase/decrease the size of datafile/s or etc, controlfile is automatically backed up.

If CONFIGURE CONTROLFILE AUTOBACKUP is ON, then RMAN automatically backs up the control file and the current server parameter file (if used to start up the database) in one of two circumstances: when a successful backup must be recorded in the RMAN repository, and when a structural change to the database affects the contents of the control file which therefore must be backed up.


Conclusion:
You can turn the autobackup feature on or off by running the following commands through RMAN utility:CONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE CONTROLFILE AUTOBACKUP OFF;

block contension

Tips to avoid hot block contention for small look-up tables.

MINIMIZE RECORD_PER_BLOCK

The basic idea of this article is to store a single row into a single block.
This could be useful to avoid the hot block contention for the small look up tables, which access heavily.

The MINIMIZE RECORDS_PER_BLOCK features comes from Oracle 8i, which gives the
facility to almost store a single record into a single block.

Test case:

Normal Heap Table (without MINIMIZE RECORDS_PER_BLOCK)

17:26:39 jaffar@PRIMEDB> create table nomin1 as select * from user_objects where rownum = 1;

Table created.

Elapsed: 00:00:00.01
17:27:18 jaffar@PRIMEDB> analyze table nomin1 compute statistics;

Table analyzed.

Elapsed: 00:00:00.00
17:27:33 jaffar@PRIMEDB> select table_name,num_rows,blocks from user_tables where table_name = 'NOMIN1';

TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
NOMIN1 1 4

17:28:22 jaffar@PRIMEDB> insert into nomin1 select * from user_objects;

242 rows created.

Elapsed: 00:00:00.00
17:28:34 jaffar@PRIMEDB> commit;

Commit complete.

Elapsed: 00:00:00.00
17:28:35 jaffar@PRIMEDB> analyze table nomin1 compute statistics;

Table analyzed.

Elapsed: 00:00:00.00
17:28:39 jaffar@PRIMEDB> select table_name,num_rows,blocks from user_tables where table_name = 'NOM
IN1';

TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
NOMIN1 243 8


Note : Total rows comes in 8 blocks.


Test case with MINIMIZE RECORD_PER_BLOCK

17:28:47 jaffar@PRIMEDB> create table min01 as select * from user_objects where rownum = 1;

Table created.

Elapsed: 00:00:00.00
17:29:12 jaffar@PRIMEDB> analyze table min01 compute statistics;

Table analyzed.

Elapsed: 00:00:00.00
17:29:22 jaffar@PRIMEDB> select table_name,num_rows,blocks from user_tables where table_name = 'MIN01';

TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
MIN01 1 4


17:32:26 jaffar@PRIMEDB> alter table min01 minimize records_per_block;

Table altered.


17:32:53 jaffar@PRIMEDB> insert into min01 select * from user_objects;

243 rows created.

Elapsed: 00:00:00.09
17:33:12 jaffar@PRIMEDB> commit;

Commit complete.

Elapsed: 00:00:00.00
17:33:12 jaffar@PRIMEDB> analyze table min01 compute statistics;

Table analyzed.

Elapsed: 00:00:00.00
17:33:14 jaffar@PRIMEDB>
17:33:14 jaffar@PRIMEDB> select table_name,num_rows,blocks from user_tables where table_name = 'MIN0
1';

TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
MIN01 244 180

Note : Total rows 243 comes in 180 blocks.


References:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76962/ch2.htm#103385

Sources Used in the test cases:

Orcle Version : 9.2.0.7
db_block_size : 8194
Tablespace : LMT + Auto Extent Size.

Oracle MS Sql connection

Oracle MS Sql connection

Using Oracle's heterogenous services also known as HS, we can make a connection to non-Oracle databases and access the data remotely. I used MS SQL server as the target non-Oracle database on Windows and used HS to access from Oracle database residing on Linux. Here, we'll see step by step instructions on how to set up a connection from Oracle database to Microsoft SQL server.

In this example, I've used the following:
Source Oracle database on Linux:test-oralin
Target SQL server on Windows:test-mssqlwin
DSN name:MSORA

Our objective is to make a connection from test-oralin to test-mssqlwin and access the SQL server database from Oracle. Here are the steps:

1. On test-mssqlwin, install Oracle Home. Example: E:\OraHome
Note: Only Oracle binaries are needed here so you need not create a database.

2. On test-mssqlwin, install ODBC drivers so that Oracle can access SQL server

3. On test-mssqlwin, configure ODBC by setting up a system DSN(Data Source Name)
Typically you can find this at Start->Settings->Control Panel->Administrative Tools->Data Sources(ODBC) however depending on the Windows version, this may differ slightly.
On System DSN tab, click Add button and then choose the driver you want to use to connect to SQL server. Continue with the ODBC configuration by entering the details of the SQL server you wish to connect to along with necessary credentials. For this test, I've chosen "MSORA" as my DSN name.

4. On test-mssqlwin, setup the heterogenous services init file.
Look for inithsodbc.ora in $ORACLE_HOME/hs/admin/ which in our case would be E:\OraHome\hs\admin. Make a copy of it and rename it to initMSORA.ora. Remember we had chosen "MSORA" as our DSN name. Open the file and enter the DSN name that you just created recently i.e "MSORA". This is how E:\OraHome\hs\admin\initMSORA.ora should look like:
# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MSORA
HS_FDS_TRACE_LEVEL = OFF

5. On test-mssqlwin, configure listener.ora. Take a sample copy from E:\OraHome\hs\admin\listener.ora.sample and copy it to E:\OraHome\network\admin\listener.ora
This is how your E:\OraHome\network\admin\listener.ora should look like:

LISTENER_MSORA =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=test-mssqlwin)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENER_MSORA=
(SID_LIST=
(SID_DESC=
(SID_NAME=MSORA)
(ORACLE_HOME = E:\OraHome)
(PROGRAM=hsodbc)
)
)

Note: SID_NAME should be your DSN name. PROGRAM=hsodbc tells Oracle to use heterogenous services. Restart your listener to make sure the settings are in effect.

We have completed our configuration on the target MS SQL server i.e test-mssqlwin. Now let us move to our source Oracle database i.e test-oralin from where we wish to access test-mssqlwin

6. On test-oralin, configure your $ORACLE_HOME/network/admin/tnsnames.ora
MSORA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test-mssqlwin)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = MSORA)
)
(HS=OK)
)
Note:SID should be the DSN name created on test-mssqlwin. PORT should be same as what was configured for the listener on Oracle Home that was installed on test-mssqlwin. HS=OK means Oracle will use heterogenous services.

7. Verify the connection from test-oralin to test-mssqlwin by doing a tnsping MSORA from test-oralin.

8. We are all set now so let us access the SQL server database now from Oracle.
On test-oralin, connect to the Oracle database from where you want to access SQL server database located on test-mssqlwin
Make sure that global_names parameter is set to false
SQL> sho parameter global_names

NAME TYPE VALUE
------------------ ---------------- -------
global_names boolean FALSE

Create a database link:
CREATE DATABASE LINK <> CONNECT TO <> IDENTIFIED BY <> USING '<>';

Example:
SQL> CREATE DATABASE LINK MSORA CONNECT TO REPL IDENTIFIED BY REPL USING 'MSORA';

Database link created.

Note: REPL is a user on SQL server database i.e test-mssqlwin with REPL as it's password. 'MSORA' was given as a connection string in tnsnames.ora. Here, we have chosen MSORA as dblinkname, you could have any name.

We have completed our configuration. You may start accessing the data now.
Example:
SQL> desc emp@msora
Name Null? Type
------------- -------- --------------
EMPID NOT NULL NUMBER(1)
EMPNAME VARCHAR2(60)
CREATED DATE