Sunday, September 26, 2010
Saturday, September 25, 2010
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
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
Thursday, September 23, 2010
How to subscribe to free monthly edition of Oracle Magazine? --Moid
Monday, September 20, 2010
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
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
Sunday, September 19, 2010
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
--Moid
Friday, September 10, 2010
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
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
Monday, August 23, 2010
Saturday, July 31, 2010
Tuesday, July 13, 2010
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
--Moid Muhammad
Tuesday, July 6, 2010
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
Click here for the link.
--Moid Muhammad
Sunday, June 27, 2010
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
Click here for 12 Data Guard Build by Umer Hussaini
--Moid
Wednesday, June 16, 2010
RMAN Full, Full level 0, Incremental and cumalative incremental
Question:
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
production:(clonea)$ pwd
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
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:
-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
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
Sunday, June 13, 2010
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.
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;
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;
'/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;
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;
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.
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;
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.
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;
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 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
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
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;
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);
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);
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);
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
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 .
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)$
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
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
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog
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
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
database mounted
released channel: ORA_DISK_1
rm * /u99/MarsDB/backup
mv * /u99/MarsDB/backup_copies/* /u99/MarsDB/backup/
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.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
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.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
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
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
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
SQL> select name from V$tablespace;
SQL> select name from v$datafile;
and the output I see is, SQL> select name from v$datafile;
Section-13: Open database
RMAN> alter database open resetlogs;
database opened
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
Wednesday, June 9, 2010
Sunday, June 6, 2010
Tuesday, June 1, 2010
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-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.
Subscribe to:
Posts (Atom)