11g R2 (11.2.0.2) -- Script to Auto Start/Shutdown databases during reboots.

I have a standalone 11 R2 (11.2.0.2) non-ASM database. The following is my script which can auto start/stop database and listener during system reboots. Please note that script reads /etc/oratab file to check which database to start. During startups, any database entry which has the "Y" at the end in the /etc/oratab file, will be started and anything which ends with "N", will be skipped.

Step 1:
Login as root and create a file called /etc/init.d/oracle. Add the following lines in the script.

#!/bin/bash

#######################################################################
#
# Run-level Startup script for the Oracle Instance and Listener
#
# chkconfig: 345 98 34
# description: Startup/Shutdown script for 11g Oracle instances
#
#######################################################################

#######################################################################
#
# Note:
# cp $ORACLE_HOME/bin/dbstart $ORACLE_HOME/bin/dbstart.orig
# Make the following changes in $ORACLE_HOME/bin/dbstart
#
# Line#80 has the following:
# ORACLE_HOME_LISTNER=$1
#
# Replace it with the following two lines:
# export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
# ORACLE_HOME_LISTNER=$ORACLE_HOME
#
# cp $ORACLE_HOME/bin/dbshut $ORACLE_HOME/bin/dbshut.orig
# Make the following changes in $ORACLE_HOME/bin/dbshut
#
# Line#50 has the following:
# ORACLE_HOME_LISTNER=$1
#
# Replace it with the following two lines:
# export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
# ORACLE_HOME_LISTNER=$ORACLE_HOME
#
# save the file and proceed with the steps below
#
#######################################################################

#######################################################################
#Instructions to start start isntances manually
#
# To Start --> /etc/init.d/oracle start
# To Stop --> /etc/init.d/oracle stop
# To restrart --> /etc/init.d/oracle restart
#
#######################################################################

ORA_HM="/u01/app/oracle/product/11.2.0/db_1"
ORA_OWNR="oracle"
# if the executables do not exist -- display error
if [ ! -f $ORA_HM/bin/dbstart -o ! -d $ORA_HM ]
then
echo "Oracle startup: cannot start"
exit 1
fi

# depending on parameter -- startup, shutdown, restart
# of the instance and listener or usage display

case "$1" in
start)
# starting Oracle instances
echo -n "starting Oracle Instances"
su - $ORA_OWNR -c $ORA_HM/bin/dbstart
touch /var/lock/subsys/oracle
ps -ef |grep pmon |grep -v grep
ps -ef |grep lsnr |grep -v grep
echo "OK"
;;
stop)

# Oracle listener and instance shutdown
su - $ORA_OWNR -c $ORA_HM/bin/dbshut
rm -f /var/lock/subsys/oracle
ps -ef |grep pmon |grep -v grep
ps -ef |grep lsnr |grep -v grep
echo "OK"
;;

reload|restart)
$0 stop
$0 start
ps -ef |grep pmon |grep -v grep
ps -ef |grep lsnr |grep -v grep
echo "OK"
;;
*)
echo "Usage: $0 start|stop|restart|reload"
exit 1
esac
exit 0
clear
###################### END of SCRIPT ##############################

Step-2:
Change the permission of the script
chmod 750 /etc/init.d/oracle

Step-3:
Execute the following so that runlevel 3,4 & 5 will execute the script during system reboots.
chkconfig --level 345 oracle on

Step-4
Execute the script and verify everything is working as expected. Alternatively, you can download the script directory from here.


Additionally you can turn on and turn on with the following.
[root@bl-mm-db-dev ~]# /sbin/chkconfig oracle off
[root@bl-mm-db-dev ~]# /sbin/chkconfig --levels 345 oracle on

That's it. We are done.


--Moid

MoidIITDocID-538: Information on Oracle Quarterly Patches --Moid

Click here for the document.

Moid

MIITDocID-939: All controlfiles are lost. Now what? Fortunately you have a last full backup plus all archivelogs since last backup....... --Moid

Read the full document from here

--Moid

How to duplex Oracle 10g RMAN backups?

Document can be found here.

--Moid

How to create a new 10g Oracle database using existing 10g database script?

My Google Doc can be found here.

--Moid Muhammad

MIITDocID-864: How to drop Oracle database?

Click here for the document.

Hope this helps.

--Moid Muhammad

Where is my 10g "create database script"? How to recreate db ($ORACLE_SID.sh script) on remote host?

Click here for the document.

Oracle Support Policy and Product Expiration Dates

Details can be accessed from here.

--Moid




http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf

How to mount the CDROM in Linux? --Moid

create an empty directory called /mnt/cdrom

[root@chicago /]# mkdir -p /mnt/cdrom
[root@chicago /]# mount -t iso9660 -r /dev/cdrom /mnt/cdrom
[root@chicago /]# cd /mnt/cdrom
[root@chicago /]# ls -ltr

to unmount

# umount /mnt/cdrom
# cd /
eject

--Moid



My page is here.

MIITDocID-916: How to find the biggest / Largest table (row wise) from a schema (or all schemas)? --Moid

Click here for the query.

--Moid

MIITDocID-919: How to find out the used size of the schema? --Moid

Click here for the SQL.

--Moid

How to check the current size (given and used) of the database?

Click here for the query.

--Moid

MoidIITDocID-637: How to find out the Oracle port numbers (used for isqlplus, DBConsole, Grid Control [OMS], OM agent etc)? --Moid

moid@Linux-223:(PrimeDG)$ cat $ORACLE_HOME/install/portlist.ini
iSQL*Plus HTTP port number =5561
iSQL*Plus HTTP port number =5560
Enterprise Manager Console HTTP Port (FlashDB) = 5501
Enterprise Manager Agent Port (FlashDB) = 1831
Enterprise Manager Console HTTP Port (kaleemdba) = 5502
Enterprise Manager Agent Port (kaleemdba) = 1832
Enterprise Manager Console HTTP Port (RecoCAT) = 5503
Enterprise Manager Agent Port (RecoCAT) = 3938
Enterprise Manager Console HTTP Port (STAR) = 5504
Enterprise Manager Agent Port (STAR) = 1830
Enterprise Manager Console HTTP Port (MarsDB) = 5500
Enterprise Manager Agent Port (MarsDB) = 1830
Enterprise Manager Console HTTP Port (alidb) = 5505
Enterprise Manager Agent Port (alidb) = 1831
Enterprise Manager Console HTTP Port (PrimeDG) = 5506
Enterprise Manager Agent Port (PrimeDG) = 3938
Enterprise Manager Console HTTP Port (TRYDB) = 5507
Enterprise Manager Agent Port (TRYDB) = 1832


Hope this helps.

--Moid

Helpful DBA Commands

Helful DBA Commands (in .pdf) can be accessed from here.

--Moid

MIITDocID-981: How to copy a user’s script (DDL) with all grants and roles?

Go to this link for the answer. Hope this helps someone in need someday.

--Moid

How to write DBID to alert long on a regular basis? --Moid

Every Oracle database has an internal unique DBID that can be queried from V$DATABASE
as follows:

SQL> select dbid from v$database;DBID------------------------------1794272723

RMAN uses the DBID to uniquely identify databases. The DBID helps RMAN identify the
correct RMAN backup piece from which to restore the control file. If you don’t use a flash
recovery area or a recovery catalog, then you should record the DBID in a safe location and
have it available in the event you need to restore your control file.


Writing the DBID to the Alert.log File

Another way of recording the DBID is to make sure that it is written to the alert.log file on a
regular basis using the DBMS_SYSTEM package. For example, you could have this SQL code execute as part of your backup job

COL dbid NEW_VALUE hold_dbidSELECT dbid FROM v$database;exec dbms_system.ksdwrt(2,'DBID: '||TO_CHAR(&hold_dbid));

After running the previous code, you should see a text message in your target database
alert.log file that looks like this:

==> alert_PrimeDG.log <==Wed Nov  3 03:06:47 2010DBID: 1794272723

You can easily put this in a cron job to write on a daily or weekly basis.Extracting of DBID from Redo Log fileAnother way of getting DBID is possible by getting the dump of the any availabe datafile, redolog or archvied log. I chosed to take a dumpfile of the online redolog and following are the steps I have taken.

SQL> ALTER SESSION SET sql_trace = true;Session altered.SQL> ALTER SESSION SET tracefile_identifier=Moid_Logfile_dump;Session altered.SQL> alter system dump logfile '/u15/oradata/Prime/redo01a.rdo';System altered.

At this point, I changed my directory to the user dump destination and dumpfile is available to be skinned.

Linux-223:(PrimeDG)$ pwd/u01/app/oracle/admin/PrimeDG/udumpLinux-223:(PrimeDG)$ ls -ltrhtotal 203M-rw-r-----  1 oracle oinstall  948 Nov  3 03:39 primedg_rfs_8676.trc-rw-r-----  1 oracle oinstall  947 Nov  3 03:44 primedg_rfs_8809.trc-rw-r-----  1 oracle oinstall 135M Nov  3 03:49 primedg_ora_8712.trc-rw-r-----  1 oracle oinstall  947 Nov  3 03:49 primedg_rfs_8979.trc-rw-r-----  1 oracle oinstall  68M Nov  3 03:49 primedg_ora_8712_MOID_LOGFILE_DUMP.trc

A simple cat of the file, provided me what I was looking for, DBID of my database.

Linux-223:(PrimeDG)$ cat primedg_ora_8712_MOID_LOGFILE_DUMP.trc |grep "Db ID"        Db ID=1794272723=0x6af26dd3, Db Name='PRIME'

--Moid

The above instructions can also be found here. 

How to add a number to null in Oracle?

select ename, sal, comm, sal+COALESCE(comm,0) as total_compensation from emp;

Click here for the screenshot.

--Moid

How to Build, Configure, Install 10g ASM database Server with migration steps on Linux? --Moid

Click here for the document.

--Moid

How to Install Oracle 11g R1 Software and configure database on Windows Vista?

Click here for the step-by-step (with screenshots) instructions on how to install Oracle 11g R1 database on Windows Vista.

--Moid

How to Install Oracle 10g database on XP?

Click here for the step-by-step (with screenshots) instructions on how to Install Oracle 10g Software and configure database on Microsoft XP.

--Moid

How to check the user's last login date and time on Linux server?

Document can be accessed from here.

--Moid

MoidIITDocID-509: Famous show_space.sql by Tom Kyte

Script can be found here.

--Moid

MoidIITDocID-580: How to register database on a non-default port / listener? -- Moid

Click here for details.

--Moid

MIITDocID-904: How to check the root blocker (blocking session) and kill it?

Document can be found here.

--Moid

How to display the output of the query vertically?

Create a procedure called Print_Table as sys

It uses authid_current user so you can install it ONCE per database and many people can use it (with roles and all intact):

Procedure and display instructions are here.


Reference: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958
Google Key words: "Tom Kyte Print_Table"

--Moid

How to check the total “Used” and “Free” database space?

Here is a script to check the total "Used" and "Free" space in a database.

--Moid

How to subscribe to free monthly edition of Oracle Magazine? --Moid

To subscribe Oracle Magazine, go here and select "Oracle Magazine" and follow the instructions. Or you can go directly to the subscription page by clicking here.

--Moid

Find the last recovery time/date of the Oracle database when "resetlogs" option was used to open the database (or when incarnation took place)?

Hi,

Have you ever been asked when was the database restored/reopened (with resetlog options)? It is a simple thing but sometimes a time consuming task especially if you are not very familiar with Oracle DBA dictionary or if you are a Jr. DBAs. Well, the answer can be found here. Hope this comes handy to someone in need.

--Moid


Keywords:

Last recovery date
Last restore date
Last recovery time
last restore time

How to Migrate Single Standalone to 2-node RAC Cluster

http://moidmuhammad.blogspot.com/2010/06/how-to-migrate-single-standalone-to-2.html

--Moid

How to get the client's machine name and their IP Address in Oracle 10g?

Salam,

I was recently asked to spool out the client’s machine, their IP addresses and the service_name they are using to connect to RAC DB into a text file. I came up with the following queryand it worked like a charm. Although I tested it on a non-RAC db server, I have no doubt it will work on any RAC cluster.

--Moid

MIITDocID-959: RMAN scripts to schedule a daily full backup using crontab --Moid

Click here for the document.

--Moid Muhammad

MoidIITDocID-598: Script / Procedure / Stored Procedure to collect number of users in a 2 node cluster -- Moid

Click here for the document.

--Moid

Keywords:
Oracle Connection Count

My notes on Oracle Manage filesystem

Click here for my notes on creation and management of OMF.

--Moid Muhammad

My Notes on building ASM server and migrating databases to ASM on VMWare.

My notes on building 10g ASM server on a VMware and migrating non-ASM to ASM database. Click here for the step by step guide.

--Moid Muhammad

Linux script to take a daily Oracle export of a schema --Moid

I was recently asked to schedule a daily export of a schema. Although it is a simple task, I think it might help someone out there some day.

Click here for the link.

--Moid Muhammad

10g Physical Standby DataGuard Installation steps --Moid

Click here for complete Step-by-Step procedure to create Physical Standby in Maximum-Performance mode.

Click here for 12 Data Guard Build by Umer Hussaini

--Moid

RMAN Full, Full level 0, Incremental and cumalative incremental

Question:

Let me give you a brief about the scenario

first a level 0 backup-----full backup

full backup size is 126 M

Now I take a level 1 incremental backup, please notice that the size is 162 M

I again take a level 1 incremental backup, but now the size is 664k

My question at the first instance when I took the incremental backup, it should have only taken affected blocks & not the whole database backup??????????????

Second time it did take only effected blocks size shows it.

Answer is as follows:

First of all, I can only guess what RMAN backups commands are issued to take a backup as I have limited visibility in the question. Secondly, "Full backup" is different than "full level 0 backup". In Oracle words, "A level 0 incremental backup is physically identical to a full backup. The only difference is that the level 0
backup is recorded as an incremental backup in the RMAN repository, so it can be used as the parent for a level 1 backup."

So from the above Oracle's statement, we can say the following:

Full backup ==> A complete backup without the incremental strategy marked in RMAN. Level 0 backup ==> A complete backup WITH the incremental strategy marked in RMAN repository.


So let's try few things out. Also, don't forget to check the information on cumalative backups in section-5 of the document.

SECTION-1:
Example-1

1A) Let's take a full backup using RMAN. This instructs RMAN to take a full backup which will NOT have any incremental
backups following in the future.

RMAN> backup database;

Backupset size after the above command is done in backup location:
Backupset = 126M
controlfile = 7.2M

1B) Let's take a level 1 backup using RMAN. This instructs RMAN to take a incremental backup. If the level 0
backup is not found (which in this case is not found --remember full backup is different than level 0 bakcup), then instruction is to take a
level 0 backup first and followed by incremental backup.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 157M
controlfile = 7.2M


1C) Let's take another level 1 backup using RMAN. This time since the level 0 (from our previous step) is found, it is
simply backing up the changed blocks.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 312K
controlfile = 7.2M


Verification:
Output after backup up with all the above three steps.

production:(clonea)$ ls -ltrh
total 305M
-rw-r----- 1 oracle oinstall 126M Jun 16 11:24 full_CLONEA_20100616_721826592_0rlgccp0_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 11:24 control_n_spfile_c-2785473847-20100616-06
-rw-r----- 1 oracle oinstall 157M Jun 16 11:31 full_CLONEA_20100616_721826784_0tlgccv0_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 11:31 control_n_spfile_c-2785473847-20100616-07
-rw-r----- 1 oracle oinstall 312K Jun 16 11:36 full_CLONEA_20100616_721827183_0vlgcdbf_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 11:36 control_n_spfile_c-2785473847-20100616-08


SECTION-2:
Remove the backups now. I am deleting all the previous backups before proceeding further.

RMAN> delete backup;
RMAN> exit;
production:(clonea)$ pwd
/u99/clonea/backup
production:(clonea)$ ls -ltr
total 0


SECTION-3:

3A) Here, as a first step, I am taking a level 0 backup. Remember level 0 takes a full backup and instructs RMAN to be ready
for incremental changes (level 1 backup) where as a FULL backup just takes a complete backup of the db without instructing
RMAN about any incremental backups.

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 126K
controlfile = 7.2M


3B) Now, I will take a level 1 backup which should only backup any changes made since last level 0 (done in 2A) backups.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 208M
controlfile = 7.2M


3C) Let's throw some activity against the database and take another level 1 incremental backups.

SQL> create table scott.t100 as select * from scott.emp;
SQL> insert into scott.t100 select * from scott.t100; --14 rows
SQL> insert into scott.t100 select * from scott.t100; --28 rows
SQL> insert into scott.t100 select * from scott.t100; --56 rows
SQL> insert into scott.t100 select * from scott.t100; --112 rows
SQL> insert into scott.t100 select * from scott.t100; --224 rows
SQL> insert into scott.t100 select * from scott.t100; --448 rows
SQL> insert into scott.t100 select * from scott.t100; --896 rows
SQL> commit;
SQL> alter system switch logfile;
SQL> alter system switch logfile;


RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 1.4M
controlfile = 7.2M


Verification:
Let's see the sizes of all the backups we have taken so far.

production:(clonea)$ ls -ltrh
total 149M
-rw-r----- 1 oracle oinstall 126M Jun 16 11:49 full_CLONEA_20100616_721828119_11lgce8n_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 11:49 control_n_spfile_c-2785473847-20100616-09
-rw-r----- 1 oracle oinstall 208K Jun 16 11:54 full_CLONEA_20100616_721828292_13lgcee4_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 11:54 control_n_spfile_c-2785473847-20100616-0a
-rw-r----- 1 oracle oinstall 1.4M Jun 16 12:08 full_CLONEA_20100616_721829154_15lgcf92_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 12:09 control_n_spfile_c-2785473847-20100616-0b



SECTION-4:
Remove backups:
Now, I am deleting all the previous backups before proceeding to next step.
RMAN> delete backup;
RMAN> exit;
production:(clonea)$ pwd
/u99/clonea/backup
production:(clonea)$ ls -ltr
total 0



SECTION-5:
In this section, lets work on CUMALATIVE backups. Oracle define cumalative backup as "In a cumulative level 1 backup, RMAN backs
up all the blocks used since the most recent level 0 incremental backup. Cumulative incremental backups reduce the work
needed for a restore by ensuring that you only need one incremental backup from any particular level. Cumulative backups
require more space and time than differential backups, however, because they duplicate the work done by previous backups at
the same level."


Example-3:

5A) Let's take a level 0 backup;

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 126M
controlfile = 7.2M

5B) Lets throw some DML against the db.

SQL> insert into scott.t100 select * from scott.t100; --1,792 rows
SQL> insert into scott.t100 select * from scott.t100; --3,584 rows
SQL> insert into scott.t100 select * from scott.t100; --71,68 rows
SQL> insert into scott.t100 select * from scott.t100; --14,336 rows
SQL> insert into scott.t100 select * from scott.t100; --28,672 rows
SQL> commit;
SQL> alter system switch logfile;

5C) Let's take a level 1 differencial backup.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 1.4M
controlfile = 7.2M

5D) Let's throw some more DML against the db.

SQL> insert into scott.t100 select * from scott.t100; --57,344 rows
SQL> insert into scott.t100 select * from scott.t100; --229,376 rows
SQL> insert into scott.t100 select * from scott.t100; --229,376 rows
SQL> insert into scott.t100 select * from scott.t100; --458,752 rows
SQL> insert into scott.t100 select * from scott.t100; --917,504 rows
SQL> commit;
SQL> alter system switch logfile;



5E) Let's take a level 1 differencial backup before taking to a final cumalative backup.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 12M
controlfile = 7.2M




5F) Let's take a level 1 cumalative backup right away (level 2 backup).

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 12M
controlfile = 7.2M

Now this is confusing, why is it showing 12M. I was expecting it to show me 13.4M i.e, sum of 5C+5E. Hmm, I am suspecting the
RMAN overhead.Let's try the next step.


5G) Let's throw some more DML against the db.

SQL> insert into scott.t100 select * from scott.t100; --1,835,008 rows
SQL> insert into scott.t100 select * from scott.t100; --3,670,016 rows
SQL> insert into scott.t100 select * from scott.t100; --7,340,032 rows
SQL> alter system switch logfile;


5H) Perform another incremental level 1 backup again.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 86M
controlfile = 7.2M


5I) Lets take a level 2 backup AGAIN!!

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 87M
controlfile = 7.2M

This size comes from backupset from (5C+5E+5H-(RMAN overhead)) i.e, (1.4M+12M+86-(RMAN_Overhead)=87M. This shows that RMAN
Overhead=12.4M. I am still not convinced. I was hoping less over head and slightly bigger backupset size. Lets try it again.



5J) Let's throw some more DML against the db.

SQL> insert into scott.t100 select * from scott.t100; --1,468,0064 rows
SQL> insert into scott.t100 select * from scott.t100; --29,360,128 rows
SQL> commit;
SQL> alter system switch logfile;



5K) Perform another incremental level 1 backup again.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 298M
controlfile = 7.2M


5L) Let's take a level 2 backup AGAIN!!

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Backupset size after the above command is done in backup location:
Backupset = 339M
controlfile = 7.2M


The size comes from backupset is from 5I=(5C+5E+5H+ -(RMAN_Overhead)) i.e, (1.4M+12M+86+298M-(RMAN_Overhead)=339M.So, from
this, RMAN_Overhead=58.4M. This is bit convincing that when cumalative backups are taken, rman overhead is reduced.

Verification:
production:(clonea)$ ls -ltrh

total 1016M
-rw-r----- 1 oracle oinstall 126M Jun 16 12:25 full_CLONEA_20100616_721830256_17lgcgbg_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 12:25 control_n_spfile_c-2785473847-20100616-0c
-rw-r----- 1 oracle oinstall 1.4M Jun 16 12:40 full_CLONEA_20100616_721831057_19lgch4h_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 12:40 control_n_spfile_c-2785473847-20100616-0d
-rw-r----- 1 oracle oinstall 12M Jun 16 12:49 full_CLONEA_20100616_721831634_1blgchmi_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 12:50 control_n_spfile_c-2785473847-20100616-0e
-rw-r----- 1 oracle oinstall 12M Jun 16 12:57 full_CLONEA_20100616_721832054_1dlgci3m_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 12:57 control_n_spfile_c-2785473847-20100616-0f
-rw-r----- 1 oracle oinstall 86M Jun 16 13:18 full_CLONEA_20100616_721833187_1flgcj73_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 13:18 control_n_spfile_c-2785473847-20100616-10
-rw-r----- 1 oracle oinstall 87M Jun 16 13:23 full_CLONEA_20100616_721833620_1hlgcjkk_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 13:23 control_n_spfile_c-2785473847-20100616-11
-rw-r----- 1 oracle oinstall 298M Jun 16 14:02 full_CLONEA_20100616_721835842_1jlgclq2_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 14:02 control_n_spfile_c-2785473847-20100616-12
-rw-r----- 1 oracle oinstall 339M Jun 16 14:09 full_CLONEA_20100616_721836281_1llgcm7p_1_1.bak
-rw-r----- 1 oracle oinstall 7.2M Jun 16 14:09 control_n_spfile_c-2785473847-20100616-13

Archivelog verification:

production:(clonea)$ pwd

/u99/clonea/Archive
production:(clonea)$ ls -ltrh
total 2.9G
-rw-r----- 1 oracle oinstall 4.3M Jun 16 12:35 ARC_1_15_720833576.arc
-rw-r----- 1 oracle oinstall 89M Jun 16 12:46 ARC_1_16_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:04 ARC_1_17_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:05 ARC_1_18_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:08 ARC_1_19_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:09 ARC_1_20_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:10 ARC_1_21_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:11 ARC_1_22_720833576.arc
-rw-r----- 1 oracle oinstall 71M Jun 16 13:12 ARC_1_23_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:39 ARC_1_24_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:40 ARC_1_25_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:41 ARC_1_26_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:41 ARC_1_27_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:42 ARC_1_28_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:43 ARC_1_29_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:43 ARC_1_30_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:46 ARC_1_31_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:46 ARC_1_32_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:47 ARC_1_33_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:47 ARC_1_34_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:48 ARC_1_35_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:49 ARC_1_36_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:49 ARC_1_37_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:50 ARC_1_38_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:50 ARC_1_39_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:52 ARC_1_40_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:52 ARC_1_41_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:53 ARC_1_42_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:53 ARC_1_43_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:54 ARC_1_44_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:55 ARC_1_45_720833576.arc
-rw-r----- 1 oracle oinstall 96M Jun 16 13:56 ARC_1_46_720833576.arc

That's it. Hope we have little better understanding of RMAN full, full level 0, differential incremental (level 1) and cumulative incremental (as Oracle calls it level-2 in previous versions) backups after reading this article.

--Moid M

References:
http://youngcow.net/doc/oracle10g/backup.102/b14191/rcmconc1005.htm
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/bkup004.htm
http://www.dba-oracle.com/t_incr_differential_incr_cumulative_backups.htm

How to recover tablespace from previous backup?

Recovery Scenerio is:

A student has dropped a user/tablepspace. Fortunately he has a recent level 0 (full) backup. Now, the task is to restore that (dropped) tablespace. We created the same scenerio below to restore the user/tablespace.


Section-1: Let's gather the the current archived log information.

col name format a50
set linesize 200
set pagesize 200

select name,
        sequence#,
        first_change# as First_SCN_Change,
        to_char(first_time,'HH24:Mi:SS') as First_Time,
        next_change# Next_SCN_Change,
        to_char(next_time,'HH24:Mi:SS') as Next_Time
from
    v$archived_log;

As you can see current
    Last Sequene# --> 28
    Last SCN         --> 478859


Step -2:  Let's create a new tablespace.

CREATE TABLESPACE testing DATAFILE
'/u14/oradata/MarsDB/testing01.dbf' SIZE 20M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Lets verify new tbs and datafile associated are in database.

Section-3: Lets make few log switches and record the latest sequence# and SCN.

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> select
            name,
            sequence#,
            first_change# as First_SCN_Change,
            to_char(first_time,'HH24:Mi:SS') as First_Time,
            next_change# Next_SCN_Change,
            to_char(next_time,'HH24:Mi:SS') as Next_Time
        from
            v$archived_log;

  
As you can see, the new changes after the tbs creation is at:
    Last Sequene# --> 38
    Last SCN         --> 481391



Section-4: Let's create a new user and populate it with few tables from scott.

SQL> CREATE USER testing IDENTIFIED BY "abc123"
 DEFAULT TABLESPACE "TESTING"
 TEMPORARY TABLESPACE "TEMP"
 PROFILE DEFAULT
 QUOTA UNLIMITED ON "TESTING";

 SQL> GRANT "CONNECT" TO testing;
 SQL> GRANT "RESOURCE" TO testing;
 SQL> ALTER USER testing DEFAULT ROLE all; 

User created.
Grant succeeded.
Grant succeeded.
User altered.

SQL> create table testing.emp1 as select * from scott.emp;
SQL> create table testing.emp2 as select * from scott.emp;
SQL> create table testing.emp3 as select * from scott.emp;
SQL> create table testing.emp4 as select * from scott.emp;
SQL> create table testing.emp5 as select * from scott.emp;
SQL> create table testing.emp6 as select * from scott.emp;
SQL> create table testing.emp7 as select * from scott.emp;
SQL> create table testing.emp8 as select * from scott.emp;
SQL> create table testing.emp9 as select * from scott.emp;

Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.

Section-5: Lets make few log switches again and verify the latest sequence# and SCN.

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> select
            name,
            sequence#,
            first_change# as First_SCN_Change,
            to_char(first_time,'HH24:Mi:SS') as First_Time,
            next_change# Next_SCN_Change,
            to_char(next_time,'HH24:Mi:SS') as Next_Time
        from
            v$archived_log;

As you can see, after we make the changes above, new changes are at:
    Last Sequene# --> 48
    Last SCN         --> 481807


Section-6: Lets take a hot full backup of the database using RMAN.

export ORACLE_SID=MarsDB
rman target /

RMAN> backup database plus archivelog;


Starting backup at 13-JUN-10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=149 devtype=DISK
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=721595376
input archive log thread=1 sequence=3 recid=2 stamp=721596450
input archive log thread=1 sequence=4 recid=3 stamp=721596451
input archive log thread=1 sequence=5 recid=4 stamp=721596459
input archive log thread=1 sequence=6 recid=5 stamp=721596459
input archive log thread=1 sequence=7 recid=6 stamp=721596462
input archive log thread=1 sequence=8 recid=7 stamp=721596462
input archive log thread=1 sequence=9 recid=8 stamp=721596462
input archive log thread=1 sequence=10 recid=9 stamp=721596465
input archive log thread=1 sequence=11 recid=10 stamp=721596465
input archive log thread=1 sequence=12 recid=11 stamp=721596471
input archive log thread=1 sequence=13 recid=12 stamp=721596471
input archive log thread=1 sequence=14 recid=13 stamp=721596477
input archive log thread=1 sequence=15 recid=14 stamp=721596477
input archive log thread=1 sequence=16 recid=15 stamp=721596478
input archive log thread=1 sequence=17 recid=16 stamp=721596478
input archive log thread=1 sequence=18 recid=17 stamp=721596481
input archive log thread=1 sequence=19 recid=18 stamp=721596481
input archive log thread=1 sequence=20 recid=19 stamp=721597318
input archive log thread=1 sequence=21 recid=20 stamp=721597320
input archive log thread=1 sequence=22 recid=21 stamp=721597326
input archive log thread=1 sequence=23 recid=22 stamp=721597326
input archive log thread=1 sequence=24 recid=23 stamp=721597872
input archive log thread=1 sequence=25 recid=24 stamp=721597876
input archive log thread=1 sequence=26 recid=25 stamp=721597882
input archive log thread=1 sequence=27 recid=26 stamp=721598275
input archive log thread=1 sequence=28 recid=27 stamp=721598329
input archive log thread=1 sequence=29 recid=28 stamp=721599464
input archive log thread=1 sequence=30 recid=29 stamp=721599466
input archive log thread=1 sequence=31 recid=30 stamp=721599475
input archive log thread=1 sequence=32 recid=31 stamp=721599475
input archive log thread=1 sequence=33 recid=32 stamp=721599480
input archive log thread=1 sequence=34 recid=33 stamp=721599607
input archive log thread=1 sequence=35 recid=34 stamp=721599611
input archive log thread=1 sequence=36 recid=35 stamp=721599616
input archive log thread=1 sequence=37 recid=36 stamp=721599617
input archive log thread=1 sequence=38 recid=37 stamp=721599628
input archive log thread=1 sequence=39 recid=38 stamp=721599820
input archive log thread=1 sequence=40 recid=39 stamp=721599829
input archive log thread=1 sequence=41 recid=40 stamp=721599835
input archive log thread=1 sequence=42 recid=41 stamp=721599841
input archive log thread=1 sequence=43 recid=42 stamp=721599841
input archive log thread=1 sequence=44 recid=43 stamp=721599844
input archive log thread=1 sequence=45 recid=44 stamp=721599844
input archive log thread=1 sequence=46 recid=45 stamp=721599850
input archive log thread=1 sequence=47 recid=46 stamp=721599851
input archive log thread=1 sequence=48 recid=47 stamp=721599856
input archive log thread=1 sequence=49 recid=48 stamp=721599859
input archive log thread=1 sequence=50 recid=49 stamp=721600004
channel ORA_DISK_1: starting piece 1 at 13-JUN-10
channel ORA_DISK_1: finished piece 1 at 13-JUN-10
piece handle=/u99/MarsDB/backup/full_MARSDB_20100613_721600005_07lg5fg5_1_1.bak tag=TAG20100613T202645 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 13-JUN-10

Starting backup at 13-JUN-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u14/oradata/MarsDB/system01.dbf
input datafile fno=00003 name=/u14/oradata/MarsDB/sysaux01.dbf
input datafile fno=00002 name=/u14/oradata/MarsDB/undotbs01.dbf
input datafile fno=00005 name=/u14/oradata/MarsDB/testing01.dbf
input datafile fno=00004 name=/u14/oradata/MarsDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-JUN-10
channel ORA_DISK_1: finished piece 1 at 13-JUN-10
piece handle=/u99/MarsDB/backup/full_MARSDB_20100613_721600014_08lg5fge_1_1.bak tag=TAG20100613T202654 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 13-JUN-10

Starting backup at 13-JUN-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=51 recid=50 stamp=721600070
channel ORA_DISK_1: starting piece 1 at 13-JUN-10
channel ORA_DISK_1: finished piece 1 at 13-JUN-10
piece handle=/u99/MarsDB/backup/full_MARSDB_20100613_721600070_09lg5fi6_1_1.bak tag=TAG20100613T202750 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 13-JUN-10

Starting Control File and SPFILE Autobackup at 13-JUN-10
piece handle=/u99/MarsDB/backup/control_n_spfile_c-1975244670-20100613-02 comment=NONE
Finished Control File and SPFILE Autobackup at 13-JUN-10

Step:7 Now lets check the backup information.

RMAN> list backup;

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    6.80M      DISK        00:00:00     13-JUN-10
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20100613T201730
        Piece Name: /u99/MarsDB/backup/control_n_spfile_c-1975244670-20100613-00
  Control File Included: Ckp SCN: 480717       Ckp time: 13-JUN-10
  SPFILE Included: Modification time: 13-JUN-10

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    7.08M      DISK        00:00:00     13-JUN-10
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20100613T201913
        Piece Name: /u99/MarsDB/backup/control_n_spfile_c-1975244670-20100613-01
  Control File Included: Ckp SCN: 481338       Ckp time: 13-JUN-10
  SPFILE Included: Modification time: 13-JUN-10

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7       11.73M     DISK        00:00:06     13-JUN-10
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20100613T202645
        Piece Name: /u99/MarsDB/backup/full_MARSDB_20100613_721600005_07lg5fg5_1_1.bak

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2       467451     13-JUN-10 473922     13-JUN-10
  1    3       473922     13-JUN-10 476883     13-JUN-10
  1    4       476883     13-JUN-10 476888     13-JUN-10
  1    5       476888     13-JUN-10 476892     13-JUN-10
  1    6       476892     13-JUN-10 476894     13-JUN-10
  1    7       476894     13-JUN-10 476896     13-JUN-10
  1    8       476896     13-JUN-10 476898     13-JUN-10
  1    9       476898     13-JUN-10 476900     13-JUN-10
  1    10      476900     13-JUN-10 476902     13-JUN-10
  1    11      476902     13-JUN-10 476904     13-JUN-10
  1    12      476904     13-JUN-10 476907     13-JUN-10
  1    13      476907     13-JUN-10 476909     13-JUN-10
  1    14      476909     13-JUN-10 476912     13-JUN-10
  1    15      476912     13-JUN-10 476914     13-JUN-10
  1    16      476914     13-JUN-10 476919     13-JUN-10
  1    17      476919     13-JUN-10 476922     13-JUN-10
  1    18      476922     13-JUN-10 476926     13-JUN-10
  1    19      476926     13-JUN-10 476932     13-JUN-10
  1    20      476932     13-JUN-10 477635     13-JUN-10
  1    21      477635     13-JUN-10 477637     13-JUN-10
  1    22      477637     13-JUN-10 477641     13-JUN-10
  1    23      477641     13-JUN-10 477643     13-JUN-10
  1    24      477643     13-JUN-10 478548     13-JUN-10
  1    25      478548     13-JUN-10 478551     13-JUN-10
  1    26      478551     13-JUN-10 478554     13-JUN-10
  1    27      478554     13-JUN-10 478819     13-JUN-10
  1    28      478819     13-JUN-10 478859     13-JUN-10
  1    29      478859     13-JUN-10 480732     13-JUN-10
  1    30      480732     13-JUN-10 480734     13-JUN-10
  1    31      480734     13-JUN-10 480738     13-JUN-10
  1    32      480738     13-JUN-10 480740     13-JUN-10
  1    33      480740     13-JUN-10 480750     13-JUN-10
  1    34      480750     13-JUN-10 481369     13-JUN-10
  1    35      481369     13-JUN-10 481373     13-JUN-10
  1    36      481373     13-JUN-10 481384     13-JUN-10
  1    37      481384     13-JUN-10 481386     13-JUN-10
  1    38      481386     13-JUN-10 481391     13-JUN-10
  1    39      481391     13-JUN-10 481733     13-JUN-10
  1    40      481733     13-JUN-10 481761     13-JUN-10
  1    41      481761     13-JUN-10 481764     13-JUN-10
  1    42      481764     13-JUN-10 481770     13-JUN-10
  1    43      481770     13-JUN-10 481775     13-JUN-10
  1    44      481775     13-JUN-10 481779     13-JUN-10
  1    45      481779     13-JUN-10 481785     13-JUN-10
  1    46      481785     13-JUN-10 481793     13-JUN-10
  1    47      481793     13-JUN-10 481797     13-JUN-10
  1    48      481797     13-JUN-10 481807     13-JUN-10
  1    49      481807     13-JUN-10 481809     13-JUN-10
  1    50      481809     13-JUN-10 481935     13-JUN-10

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    95.71M     DISK        00:00:49     13-JUN-10
        BP Key: 8   Status: AVAILABLE  Compressed: YES  Tag: TAG20100613T202654
        Piece Name: /u99/MarsDB/backup/full_MARSDB_20100613_721600014_08lg5fge_1_1.bak
  List of Datafiles in backup set 8
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 481943     13-JUN-10 /u14/oradata/MarsDB/system01.dbf
  2       Full 481943     13-JUN-10 /u14/oradata/MarsDB/undotbs01.dbf
  3       Full 481943     13-JUN-10 /u14/oradata/MarsDB/sysaux01.dbf
  4       Full 481943     13-JUN-10 /u14/oradata/MarsDB/users01.dbf
  5       Full 481943     13-JUN-10 /u14/oradata/MarsDB/testing01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
9       29.00K     DISK        00:00:01     13-JUN-10
        BP Key: 9   Status: AVAILABLE  Compressed: YES  Tag: TAG20100613T202750
        Piece Name: /u99/MarsDB/backup/full_MARSDB_20100613_721600070_09lg5fi6_1_1.bak

  List of Archived Logs in backup set 9
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    51      481935     13-JUN-10 481991     13-JUN-10

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    7.08M      DISK        00:00:02     13-JUN-10
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20100613T202752
        Piece Name: /u99/MarsDB/backup/control_n_spfile_c-1975244670-20100613-02
  Control File Included: Ckp SCN: 481997       Ckp time: 13-JUN-10
  SPFILE Included: Modification time: 13-JUN-10

Now the backups are done, lets verify the backups in the backup location.

As you can see my backups are at --> /u99/MarsDB/backup/

Section-8: Move backups to SAFE location.

For the purpose of this document, lets called this location as a remote location (although it is on the same server) and the actual path is at /u99/MarsDB/backup_copies

Lets move the backups to a remote location.

cp /u99/MarsDB/backup/* /u99/MarsDB/backup_copies/.

Section-9: Lets drop the User and Tablespace.

Before dropping the tablespace, lets make few log switches and check the seq# and SCN# again.

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> select name,
        sequence#,
        first_change# as First_SCN_Change,
        to_char(first_time,'HH24:Mi:SS') as First_Time,
        next_change# Next_SCN_Change,
        to_char(next_time,'HH24:Mi:SS') as Next_Time
from
    v$archived_log;

As you can see the current SCN before we move to drop the objects is:
    Last Sequene# --> 54
    Last SCN         --> 482647

Lets drop user.

SQL> drop user testing cascade;
User dropped.

SQL> alter system switch logfile;
System altered.

SQL> select
            name,
            sequence#,
            first_change# as First_SCN_Change,
            to_char(first_time,'HH24:Mi:SS') as First_Time,
            next_change# Next_SCN_Change,
            to_char(next_time,'HH24:Mi:SS') as Next_Time
        from
            v$archived_log
    where
            sequence#=(select max(sequence#) from v$archived_log);

As you can see, after dropping the user, the current numbers are at:
    Last Sequene# --> 55
    Last SCN         --> 483069

Let's Drop Tablespace

SQL> drop tablespace testing including contents and datafiles;
Tablespace dropped.

SQL> alter system switch logfile;
System altered.

SQL> select
            name,
            sequence#,
            first_change# as First_SCN_Change,
            to_char(first_time,'HH24:Mi:SS') as First_Time,
            next_change# Next_SCN_Change,
            to_char(next_time,'HH24:Mi:SS') as Next_Time
        from
            v$archived_log
    where
            sequence#=(select max(sequence#) from v$archived_log);

As you can see, after dropping the tablespace, the current changes are at:
    Last Sequene# --> 56
    Last SCN         --> 483562

At this point, we can move on to our recovery process.



Section-10: shutdown database;

SQL> shutdown immediate;

Section-11: Restore database using the backup.

The reason we want to use the backup from remote location (/u99/MarsDB/backup_copies) instead of the actual RMAN backup (/u99/MarsDB/backup) location is because anytime a physical structure of the database is change, backup of control files gets updated. When we dropped the tablespace, physical structure was changed and hence a updated controlfile was triggered to the original backup location. We don't want to use the updated controlfile as it is not the file we want to use to restore the backups from.

For our demonstration, check the disk space used by both location. Original backup location which is known by RMAN. The size is bigger than remote location.

student1.com:(MarsDB)$ cd /u99/MarsDB/backup_copies/
student1.com:(MarsDB)$ du
131724  .
student1.com:(MarsDB)$ cd /u99/MarsDB/backup
student1.com:(MarsDB)$ du
139000  .

Let's check what files we have in these two locations.

student1.com:(MarsDB)$ ls -ltrh/u99/MarsDB/backup_copies/
total 129M
-rw-r-----  1 oracle oinstall  12M Jun 13 20:34 full_MARSDB_20100613_721600005_07lg5fg5_1_1.bak
-rw-r-----  1 oracle oinstall 7.1M Jun 13 20:34 control_n_spfile_c-1975244670-20100613-02
-rw-r-----  1 oracle oinstall 7.1M Jun 13 20:34 control_n_spfile_c-1975244670-20100613-01
-rw-r-----  1 oracle oinstall 6.9M Jun 13 20:34 control_n_spfile_c-1975244670-20100613-00
-rw-r-----  1 oracle oinstall  30K Jun 13 20:34 full_MARSDB_20100613_721600070_09lg5fi6_1_1.bak
-rw-r-----  1 oracle oinstall  96M Jun 13 20:34 full_MARSDB_20100613_721600014_08lg5fge_1_1.bak

student1.com:(MarsDB)$ ls -ltrh cd /u99/MarsDB/backup
total 136M
-rw-r-----  1 oracle oinstall 6.9M Jun 13 20:17 control_n_spfile_c-1975244670-20100613-00
-rw-r-----  1 oracle oinstall 7.1M Jun 13 20:19 control_n_spfile_c-1975244670-20100613-01
-rw-r-----  1 oracle oinstall  12M Jun 13 20:26 full_MARSDB_20100613_721600005_07lg5fg5_1_1.bak
-rw-r-----  1 oracle oinstall  96M Jun 13 20:27 full_MARSDB_20100613_721600014_08lg5fge_1_1.bak
-rw-r-----  1 oracle oinstall  30K Jun 13 20:27 full_MARSDB_20100613_721600070_09lg5fi6_1_1.bak
-rw-r-----  1 oracle oinstall 7.1M Jun 13 20:27 control_n_spfile_c-1975244670-20100613-02
-rw-r-----  1 oracle oinstall 7.1M Jun 13 20:50 control_n_spfile_c-1975244670-20100613-03
student1.com:(MarsDB)$

As you can see, there are three copies of controlfile in remote location and 4 copies of controlfile in rman backup location. Let's start the restore process using backups from the remote location.

student1.com:(none)$ export ORACLE_SID=MarsDB
student1.com:(MarsDB)$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Jun 13 21:14:18 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database (not started)

RMAN> set DBID=1975244670
executing command: SET DBID

RMAN> startup force nomount;
Oracle instance starte
Total System Global Area     167772160 bytes
Fixed Size                     1266368 bytes
Variable Size                100666688 bytes
Database Buffers              58720256 bytes
Redo Buffers                   7118848 bytes

Let's give out the controlfile location to RMAN. In this case, the original control files are at remote which is at /u99/MarsDB/backup_copies

RMAN> set controlfile autobackup format for device type disk to '/u99/MarsDB/backup_copies/control_n_spfile_%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

Let's restore controlfile.

RMAN> restore controlfile from autobackup;

Starting restore at 13-JUN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u05/flash_recovery_area
database name (or database unique name) used for search: MARSDB
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20100613
channel ORA_DISK_1: autobackup found: /u99/MarsDB/backup_copies/control_n_spfile_c-1975244670-20100613-02
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u02/oradata/MarsDB/control01.ctl
output filename=/u03/oradata/MarsDB/control02.ctl
output filename=/u04/oradata/MarsDB/control03.ctl
Finished restore at 13-JUN-10

Let's Mount the database

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

Good. Database is mounted. Let's Restore db from last backup. However before we do that. Lets put the backup from remote backup location to the RMAN's known (original) location. So, lets remove (or copy to some other location known to you) everything from RMAN's known (original) backup location and put back the backup sets from our remote location to the original location.

        rm * /u99/MarsDB/backup

mv * /u99/MarsDB/backup_copies/* /u99/MarsDB/backup/

  

and now lets restore the database.

RMAN> restore database;

Starting restore at 13-JUN-10
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/13/2010 21:35:12
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

I got the following error because we removed the latest backupsets and put back the old backupsets into the RMAN's original location. Performing a crosscheck should overcome this.

RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u99/MarsDB/backup/control_n_spfile_c-1975244670-20100613-00 recid=5 stamp=721599450
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u99/MarsDB/backup/control_n_spfile_c-1975244670-20100613-01 recid=6 stamp=721599553
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u99/MarsDB/backup/full_MARSDB_20100613_721600005_07lg5fg5_1_1.bak recid=7 stamp=721600006
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u99/MarsDB/backup/full_MARSDB_20100613_721600014_08lg5fge_1_1.bak recid=8 stamp=721600014
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u99/MarsDB/backup/full_MARSDB_20100613_721600070_09lg5fi6_1_1.bak recid=9 stamp=721600071
Crosschecked 5 objects

Let's try restoring again.

RMAN> restore database;

Starting restore at 13-JUN-10
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u14/oradata/MarsDB/system01.dbf
restoring datafile 00002 to /u14/oradata/MarsDB/undotbs01.dbf
restoring datafile 00003 to /u14/oradata/MarsDB/sysaux01.dbf
restoring datafile 00004 to /u14/oradata/MarsDB/users01.dbf
restoring datafile 00005 to /u14/oradata/MarsDB/testing01.dbf
channel ORA_DISK_1: reading from backup piece /u99/MarsDB/backup/full_MARSDB_20100613_721600014_08lg5fge_1_1.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u99/MarsDB/backup/full_MARSDB_20100613_721600014_08lg5fge_1_1.bak tag=TAG20100613T202654
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 13-JUN-10

Good, restore is completed. Now let's find out the scn we want to recover upto. Go back few steps (section-9) in the document to find out the SCN right before the user/tablespace was dropped. Then we can use the same scn to recover database upto that point in time. The SCN is 482647


Section-12 Recover Database (Use point in time recovery of the database);

RMAN> recover database until scn 482647;

Starting recover at 13-JUN-10
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 51 is already on disk as file /u99/MarsDB/archive/Arc_1_51_721595136.arc
archive log filename=/u99/MarsDB/archive/Arc_1_51_721595136.arc thread=1 sequence=51
archive log filename=/u99/MarsDB/archive/Arc_1_52_721595136.arc thread=1 sequence=52
archive log filename=/u99/MarsDB/archive/Arc_1_53_721595136.arc thread=1 sequence=53
archive log filename=/u99/MarsDB/archive/Arc_1_54_721595136.arc thread=1 sequence=54
media recovery complete, elapsed time: 00:00:02
Finished recover at 13-JUN-10

Media Recovery is complete. Let's verify if the dropped tablespace is back in the database;

SQL> select name from V$tablespace;
SQL> select name from v$datafile;

and the output I see is,

Section-13: Open database

RMAN> alter database open resetlogs;
database opened

Section-14: Verify tables that were dropped are back into the database;

export ORACLE_SID=MarsDB
sqlplus testing/abc123
SQL> select * from cat;
SQL> set linesize 200
SQL? select * from emp1;

Thats it for now. Hope this guide is helpful to you all.

--Moid M

How to copy DBCA template from one DB Server to other DB Server? --Moid

• Assuming you have two DB servers called Server1 and Server2 and you want to copy a database template called MoidDB from Server1 to Server2
  
  
• Login to Server 1 as oracle
  
  
• cd $ORACLE_HOME/assistants/dbca/templates
  
  
• check the available templates.
  Server1:(PrimeDG)$ ls -tlrh
  total 112M
  -rw-r--r-- 1 oracle oinstall 6.8M Jan 6 17:10 Seed_Database.ctl
  -rw-r--r-- 1 oracle oinstall 5.6K Jan 6 17:10 Data_Warehouse.dbc
  -rw-r--r-- 1 oracle oinstall 5.6K Jan 6 17:10 Transaction_Processing.dbc
  -rw-r--r-- 1 oracle oinstall 92M Jan 6 17:10 Seed_Database.dfb
  -rw-r----- 1 oracle oinstall 6.3K Jan 6 17:10 RecoCAT.dbc
  -rw-r----- 1 oracle oinstall 6.2K Jan 6 17:10 Prime.dbc
  -rw-r--r-- 1 oracle oinstall 12K Jan 6 17:10 New_Database.dbt
  -rw-r----- 1 oracle oinstall 6.3K Jan 6 17:10 MoidDB.dbc
  -rw-r----- 1 oracle oinstall 6.3K Jan 6 17:10 MarsDB.dbc
  -rw-r----- 1 oracle oinstall 6.3K Jan 6 17:10 kaleemdba.dbc
  -rw-r----- 1 oracle oinstall 6.2K Jan 6 17:10 JayzeeDB.dbc
  -rw-r--r-- 1 oracle oinstall 5.5K Jan 6 17:10 General_Purpose.dbc
  -rw-r----- 1 oracle oinstall 6.1K Jan 6 17:10 FlashDB.dbc
  -rw-r--r-- 1 oracle oinstall 1.0M Jan 6 17:10 example.dmp
  -rw-r--r-- 1 oracle oinstall 13M Jan 6 17:10 example01.dfb
  -rw-r----- 1 oracle oinstall 6.2K Jan 6 17:10 alidb.dbc
  -rw-r----- 1 oracle oinstall 6.2K Jan 16 13:08 SamaDB.dbc
  
  
• Now copy MoidDB.dbc template from Server1 to Server2.
  Ex:
  
  scp $ORACLE_HOME/assistants/dbca/templates/MoidDB.dbc Server2:$ORACLE_HOME/assistants/dbca/templates/.
  
  
• Login to Server2 as oracle and verify $ORACLE_HOME/assistants/dbca/templates/MoidDB.dbc availability. Start DBCA and you will have your template.
  
  

References:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/install003.htm#i1008647

MoidIITDocID-934: How to identify Oracle Long Running sessions? --Moid

Click here for the document.

MIITDocID-885: My queries on Oracle Roles, Grant and Privileges --Moid

Click here for the document.

--Moid

How to create or recreate dbconsole on a single standalone server? --Moid

How to create or recreate dbconsole on a standalone (non-RAC) server? --Moid

In this exercise, we will create a dbconsole for a manually created database called demo. This demo DB is hosted on a DB server called “production” and it can be accessed by using the ip address “192.168.0.7”. Pictures worth thousand words, so I had lot of screenshots for this exercise, hope this helps you in creating dbconsole.

Section-1: Prerequisite for DBConsole.

· Login to the database server.

· Make sure DB is up

o If it is not started, use the following steps to start it.

§ export ORACLE_SID=demo

§ sqlplus / as sysdba

§ startup

· Make sure Listener is up.

o If the listener is not up, start it using the following command.

§ lsnrctl start listener

· Make sure tns entry for demo database is added in tnsnames.ora file

o If the tns entry is not, add it using the following method. Figure-1 to Figure-12 displays the steps which can be executed to add the tns entry into the tnsnames.ora file.

Figure-1:

Figure-2.

Figure-3:

Figure-4:

Figure-5:

Figure-6:

Figure-7:

Figure-8:

Figure-9:

Figure-10:

Figure-11:

Figure-12:

Figure-13:

Figure-14:

Section-2: Create dbconsole repository using emca utility.

As shown in Figure-14, enter the following on the $ prompt.

$ $ORACLE_HOME/bin/emca -repos create

STARTED EMCA at Apr 20, 2008 3:44:33 PM

EM Configuration Assistant, Version 10.2.0.1.0 Production

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

Enter the following information:

Database SID: demo

Listener port number: 1521

Password for SYS user:abc123

Password for SYSMAN user:abc123

Do you wish to continue? [yes(Y)/no(N)]: Y

Figure-14:

Figure-15: check the output for any errors. My execution went very smooth and I didn’t see any errors building the repository.

Section-3: Create dbconsole using emca utility.

As shown in Figure-16, enter the following on the $ prompt.

$ $ORACLE_HOME/bin/emca –config dbcontrol db

STARTED EMCA at Apr 20, 2008 3:51:06 PM

EM Configuration Assistant, Version 10.2.0.1.0 Production

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

Enter the following information:

Database SID: demo

Listener port number: 1521

Password for SYS user:abc123

Password for DBSNMP user: abc123

Password for SYSMAN user:abc123

.

..

…

..

.

Do you wish to continue? [yes(Y)/no(N)]: Y

Figure-16:

Figure-17: I see few errors than can also be seen in Figure below.

The emca utility is unhappy about shared_pool_size and job_queue_processes parameters lower than it should be. I increased the value to its minimum required in Figure-18 and restarted the dbconsole creation again in Figure 19. This time it complained again. It turned out to be that I had given “shared_pool_size=82” instead of “shared_pool_size=82M”. I fixed that in step 20 and ran the emca execution again without any errors.

Figure-18:

Figure-19:

.

Figure-20:

Section-4: Check the status of the dbconsole and open it using standard browser.

To check the status, on the $prompt, type

emctl status dbconsole

$ export ORACLE_SID=demo

$ emctl status dbconsole

TZ set to US/Central

Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0

Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.

https://production:5507/em/console/aboutApplication

Oracle Enterprise Manager 10g is running.

------------------------------------------------------------------

Logs are generated in directory /u01/app/oracle/product/10.2.0/db_1/production_demo/sysman/log

As you can see, dbconsole is running on https://production:5507/em/

Please note that if the DB Server host is not configured in DNS, you might need to replace the host with IP address. So for example, you will need to type https://192.168.0.7:5507/em/ instead of https://production:5507/em/. Or add the host entry into the hosts file. In Windows XP, location of hosts file is à C:\WINDOWS\system32\drivers\etc\hosts

For example, add the following line in C:\WINDOWS\system32\drivers\etc\hosts

192.168.0.7 production

Now we are all ready to open the dbconsole.

Figure-21: Open an internet browser, and start the dbconsole as shown in figure below.

Figure-22: Since this is the first time you are login to the database, you will see the Oracle license agreement page. Once you click “I agree” you are all set to use the dbconsole for database maintenance.

To shutdown dbconsole, the command is:

$ export ORACLE_SID=demo

$ emctl stop dbconsole

TZ set to US/Central

Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0

Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.

https://production:5507/em/console/aboutApplication

Stopping Oracle Enterprise Manager 10g Database Control ...

... Stopped.

Hope the document helps you in your DBA work.

--Moid M.