fast-start-checkpointing

Fast-start checkpointing should always be enabled for the following reasons:

• It reduces the time required for cache recovery, and makes instance recovery time-bounded and predictable. This is accomplished by limiting the number of dirty buffers (data blocks which have changes in memory that still need to be written to disk) and the number of redo records (changes in the database) generated between the most recent redo record and the last checkpoint.
• Fast-Start checkpointing eliminates bulk writes and corresponding I/O spikes that occur traditionally with interval- based checkpoints, providing a smoother, more consistent I/O pattern that is more predictable and easier to manage.

Starting in Oracle9i Release 2 (9.2), MTTR advisory is available to help you evaluate the effect of different MTTR settings on system performance in terms of extra physical writes.

When MTTR advisory is ON, it simulates checkpoint queue behavior under five different MTTR settings: Current MTTR, 0.1, 0.5, 1.5, and 2.

Restore Datafile using RMAN backup

Restore Datafile using RMAN backup

When for some reason a datafile is lost and RMAN is in place. You might want to recover the file using the RMAN restore option for restoring datafiles.

Here is a brief overview on how to proceed.

oracle@xxx: $ ls -ltr
total 1656572
-rw-r—– 1 oracle oinstall 20979712 Jul 11 06:00 temp01.dbf
-rw-r—– 1 oracle oinstall 52429312 Jul 12 22:01 redo01.log
-rw-r—– 1 oracle oinstall 262152192 Jul 12 22:02 users01.dbf
-rw-r—– 1 oracle oinstall 52429312 Jul 12 22:02 redo03.log
-rw-r—– 1 oracle oinstall 199237632 Jul 13 08:29 sysaux01.dbf
-rw-r—– 1 oracle oinstall 209723392 Jul 13 08:50 undotbs01.dbf
-rw-r—– 1 oracle oinstall 314580992 Jul 13 08:50 system01.dbf
-rw-r—– 1 oracle oinstall 536879104 Jul 13 08:54 xwiki01.dbf
-rw-r—– 1 oracle oinstall 52429312 Jul 13 08:54 redo02.log
-rw-r—– 1 oracle oinstall 7094272 Jul 13 08:54 control03.ctl
-rw-r—– 1 oracle oinstall 7094272 Jul 13 08:54 control01.ctl

oracle@xxx: $ rm xwiki01.dbf

Here we are 1 datafile short !

oracle@xxx: $ ls -ltr
total 1656572
-rw-r—– 1 oracle oinstall 20979712 Jul 11 06:00 temp01.dbf
-rw-r—– 1 oracle oinstall 52429312 Jul 12 22:01 redo01.log
-rw-r—– 1 oracle oinstall 262152192 Jul 12 22:02 users01.dbf
-rw-r—– 1 oracle oinstall 52429312 Jul 12 22:02 redo03.log
-rw-r—– 1 oracle oinstall 199237632 Jul 13 08:29 sysaux01.dbf
-rw-r—– 1 oracle oinstall 209723392 Jul 13 08:50 undotbs01.dbf
-rw-r—– 1 oracle oinstall 314580992 Jul 13 08:50 system01.dbf
-rw-r—– 1 oracle oinstall 52429312 Jul 13 08:54 redo02.log
-rw-r—– 1 oracle oinstall 7094272 Jul 13 08:54 control03.ctl
-rw-r—– 1 oracle oinstall 7094272 Jul 13 08:54 control01.ctl

First connect to your RMAN “catalog” and list backup to find the datafile number to restore.

oracle@xxx: $ $ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 – Production on Mon Jul 13 08:54:59 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target;

connected to target database: XWIKI (DBID=675473154)

RMAN> list backup;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
82 Full 137.80M DISK 00:00:55 06-JUL-09
BP Key: 82 Status: AVAILABLE Compressed: NO Tag: TAG20090706T220341
Piece Name: /u04/backup/XWIKI/rman/backup_df_88_691538622.bak
List of Datafiles in backup set 82
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 Full 2127473 06-JUL-09 /u02/oradata/xwiki/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
84 Full 76.31M DISK 00:00:59 06-JUL-09
BP Key: 84 Status: AVAILABLE Compressed: NO Tag: TAG20090706T220341
Piece Name: /u04/backup/XWIKI/rman/backup_df_86_691538622.bak
List of Datafiles in backup set 84
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
5 Full 2127471 06-JUL-09 /u02/oradata/xwiki/xwiki01.dbf

..
…
..

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
141 Full 6.83M DISK 00:00:00 12-JUL-09
BP Key: 141 Status: AVAILABLE Compressed: NO Tag: TAG20090712T220211
Piece Name: /u04/backup/XWIKI/rman/c-675473154-20090712-00
Control File Included: Ckp SCN: 2330680 Ckp time: 12-JUL-09
SPFILE Included: Modification time: 08-JUL-09

RMAN>

We know now that datafile 5 is the one to restore. Before we can proceed we need to put it offline.

RMAN> SQL ‘ALTER DATABASE DATAFILE 5 OFFLINE’;

sql statement: ALTER DATABASE DATAFILE 5 OFFLINE

RMAN>

Now that the datafile is offline , we are ready for the RMAN datafile restore.

RMAN> RESTORE DATAFILE 5;

Starting restore at 13-JUL-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=140 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=149 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=139 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /u02/oradata/xwiki/xwiki01.dbf
channel ORA_DISK_1: reading from backup piece /u04/backup/XWIKI/rman/backup_df_142_692056911.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u04/backup/XWIKI/rman/backup_df_142_692056911.bak tag=TAG20090712T220151
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 13-JUL-09

RMAN>

At this point we are not yet ready to put the datafile back online

RMAN> SQL ‘ALTER DATABASE DATAFILE 5 ONLINE’;

sql statement: ALTER DATABASE DATAFILE 5 ONLINE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 07/13/2009 08:53:45
RMAN-11003: failure during parse/execution of SQL statement: ALTER DATABASE DATAFILE 5 ONLINE
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: ‘/u02/oradata/xwiki/xwiki01.dbf’

As this is an “old” datafile and not the up to date one, we need to recover the datafile to match the others. This can easily be done by executing the following.

RMAN> RECOVER DATAFILE 5;

Starting recover at 13-JUL-09
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /u03/oradata/xwiki/archiving/1_6_691685756.arc
archive log thread 1 sequence 7 is already on disk as file /u03/oradata/xwiki/archiving/1_7_691685756.arc
archive log thread 1 sequence 8 is already on disk as file /u03/oradata/xwiki/archiving/1_8_691685756.arc
archive log thread 1 sequence 9 is already on disk as file /u03/oradata/xwiki/archiving/1_9_691685756.arc
archive log thread 1 sequence 10 is already on disk as file /u03/oradata/xwiki/archiving/1_10_691685756.arc
archive log thread 1 sequence 11 is already on disk as file /u03/oradata/xwiki/archiving/1_11_691685756.arc
archive log thread 1 sequence 12 is already on disk as file /u03/oradata/xwiki/archiving/1_12_691685756.arc
archive log filename=/u03/oradata/xwiki/archiving/1_6_691685756.arc thread=1 sequence=6
archive log filename=/u03/oradata/xwiki/archiving/1_7_691685756.arc thread=1 sequence=7
archive log filename=/u03/oradata/xwiki/archiving/1_8_691685756.arc thread=1 sequence=8
media recovery complete, elapsed time: 00:00:03
Finished recover at 13-JUL-09

RMAN>

Now that the datafile is brought up to speed, we can put our datafile online again.

RMAN> SQL ‘ALTER DATABASE DATAFILE 5 ONLINE’;

sql statement: ALTER DATABASE DATAFILE 5 ONLINE

RMAN>

Recovering a Lost Datafile Without a Backup

When a datafile is lost,the possible cases can be classified as possible.

1)The control file knows about the datafile,that is user backed up the control file after the datafile creation,but the datafile itself is not backed up.If the datafile record is in the control file,then the restore creates the datafile in the original location or in the user specifie location.The RECOVER command can then apply the necessary logs to the datafile.

2)The control file does not have the datafile record,that is ,the user didnot take the backup of control file after the creation of a datafile,.During recovery ,the database will detect the missing datafile an report it to RMAN,which will create a new datafile and continues recovery process by applying the remianing logs.

Enter the following commands at the RMAN prompt:

#take the tablespace with missing datafile offline
RMAN>SQL "ALTER TABLESPACE ***** OFFLINE IMMEDIATE;
#resotre the tablespace even though you have no backup
RMAN>RESTORE TABLESPACE *****;
#recover tablespace
RMAN>RECOVER TABLESPACE ******;
RMAN>SQL "ALTER TABLESPACE ***** ONLINE";

rman - noarchivelog db - recover noredo

I encountered few issues while restoring a rman backup when the db is running in noarchivelog mode (i think the online redolog's availability has some influence on the rman backup restore strategy ) . so to be on a safeside I implemented the backup strategy with incremental backup for noarchivelog db. this one I tested on 10.2.0.2

Here is the backup script

============================
script rman_noarch_backup.rcv # ##########
SHUTDOWN IMMEDIATE;
STARTUP FORCE DBA;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
BACKUP
COPIES 1
INCREMENTAL LEVEL 0
MAXSETSIZE 1G
DATABASE
INCLUDE CURRENT CONTROLFILE
TAG 'BACKUP_1'
;
alter database open;
#########################################

and the restore operation is as below

vmtest:/export/home/oracle$ rman target /
RMAN> SET DBID 1177027667
executing command: SET DBID
RMAN> startup nomount;

RMAN> restore controlfile from '/export/home/oracle/flash_recovery_area/ORCL/backupset/2008_03_13/o1_mf_ncnn0_BACKUP_1_3xmdomv3_.bkp';

RMAN> alter database mount;

RMAN> run{
restore database;
recover database noredo;
}

RMAN> alter database open resetlogs;

database opened

Restore and Recovery of NOARCHIVELOG Databases

Restore and Recovery of NOARCHIVELOG Databases

1)Only cold backups can be used in restoring a database in NOARCHIVELOG mode.
2)Media recovery is not possible ,because there are no archived logs

A limited form of restore and recovery is possible for NOARCHIVELOG databases if the backup strategy for the database includes incremental backups.The incremental backups can be applied to a full database backup to apply recent changes to the time of the incremtal backup.

Recovering a Database in NOARCHIVELOG Mode Using Incremtal Backups

Assume the following scenario
1)you run database trgt in NOARCHIVELOG mode.
2)You use a recovery catalog
3)You shutdown the database consistently and make a level 0 backup of database trgt to tape on sunday afternoon.
4)you shutdown the database consistently and make a level 1 differential incremantal backup to tape at 3 am
on wednesday and friday.
5)the database has a media failure on saturday ,destroying half of the datafiles as well as the online redologs.

In this case,you must perform an incomplete media recovery until friday,the date of the most incremental backup.RMAN uses the level 0 sunday backup as well as the wed and friday's level 1 backups.

Because the online redo logs are lost,you must specify the NOREDO option in the recover command.
you must also specify NOREDO if the online logs are available but the redo cannot be applied to the incremantals.

If you don't specify NOREDO option,then RMAN searches for online redo logs after applying the fridays' incremental backup and issues error message when it does not find them.

After connecting to trgt and the catalog database,recover the database with the following command

RMAN>STARTUP FORCE MOUNT;
RMAN>RESTORE CONTROLFILE;
RMAN>ALTER DATABASE MOUNT;
RMAN>RESTORE DATABASE;
RMAN>RECOVER DATABASE NOREDO;
RMAN>ALTER DATABASE OPEN RESETLOGS;

NOTE:If the current online redo logs contains all the changes since the last incremantal backups,then you can run the RECOVER DATABASE with out specifying NOREDO.

orainstRoot and root.sh

Why do you run orainstRoot and ROOT.SH once you finalize the Installation?

orainstRoot.sh needs to be run to change the Permissions and groupname to 770 and to dba.

Root.sh (ORACLE_HOME) location needs to be run to create a ORATAB in /etc/oratab or /opt/var/oratab in Solaris and to copy dbhome, oraenv and coraenv to /usr/local/bin.

orainstRoot.sh

[root@oracle11g ~]# /u01/app/oraInventory/orainstRoot.sh

Changing permissions of /u01/app/oraInventory to 770.

Changing groupname of /u01/app/oraInventory to dba.

The execution of the script is complete

root.sh

[root@oracle11g ~]# /u01/app/oracle/product/11.1.0/db_1/root.sh

Running Oracle 11g root.sh script...

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /u01/app/oracle/product/11.1.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:

Copying dbhome to /usr/local/bin ...

Copying oraenv to /usr/local/bin ...

Copying coraenv to /usr/local/bin ...

Creating /etc/oratab file...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root.sh script.

Now product-specific root actions will be performed.

Finished product-specific root actions.

Top SQL Statements.

Since the buffer gets static is cumulative ,the query with the most buffer gets may not be the worst-performing query in the database;It may have been executed enough times to earn the higest ranking.Compare the cumulative number of buffer gete to the cumulative number of disk reads for the queries.;If the numbers are close ,then you should evaluate the explain plan for the query to find out why it is performing so many disk reads.If the disk reads are not high but the buffer gete are high and the executions are low,then the query is either using a bad index or performing a join in the wrong order.

if the shared pool is flushed between the execution times of the two snapshots,the sql portion of the output report will not necessarily contain the most resource-intensive sql executed during the period.
In 10g,v$SQL now shows SQL for multiple users with same statement and shows child cursors,
V$SQL_PLAN_STATISTICS show the execution stats for each cached cursor,and V$SQL_PLAN_STATISTICS_ALL shows the joins plan and stats and many other performance related statistics.

TOP SQL SECTION

• SQL ordered by Elapsed Time
• SQL ordered by CPU TIme
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Executions
• SQL ordered by parse calls
• SQL ordered by Sharable memory.