Wednesday, June 16, 2010

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

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.

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

Wednesday, June 9, 2010

Identify Oracle Long Running sessions --Moid

My notes on long running sessions


1) The following query will identify any session which are running for more than 10 seconds.


select
s.username,
s.sid,
s.serial#,
s.last_call_et seconds_running,
q.sql_text
from
v$session s join v$sqltext_with_newlines q on s.sql_address = q.address
where
status='ACTIVE'
and
type <>'BACKGROUND'
and
last_call_et> 10 --time in seconds
order by
sid,
serial#,
q.piece;




2) The following query will identify any session which are running for more than 10 seconds and the SQL statement to kill that session.

select
s.username,
s.sid,
s.serial#,
s.last_call_et seconds_running,
q.sql_text,
'Alter system kill session '''||s.sid||','||serial#||''' immediate;' as SQL_to_kill_long_running_sqls
from
v$session s join v$sqltext_with_newlines q on s.sql_address = q.address
where
status='ACTIVE'
and
type <>'BACKGROUND'
and
last_call_et> 10 --time in seconds
order by
sid,
serial#,
q.piece;



More to come..





Reference:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm#i1414383
http://stackoverflow.com/questions/622289/how-to-check-oracle-database-for-long-running-queries

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-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.

How to Migrate Single Standalone to 2-node RAC Cluster? --Moid

How to copy controlfile from ASM diskgroup to a cooked filesystem --Moid

How to copy controlfile from ASM diskgroup to disk in 10g?  

One of the 11g new ASM feature is the ability to use the "cp" command in ASM using "asmcmd" which 10g lacks. The following steps will help one understand  how to copy controlfile from ASM to disk.


10.2.0.3 databases on the server called MoidDBServer are:

    MoidDBServer > ps -ef |grep pmon |grep -v 'grep'
    oracle    9305     1  0 Jun03 ?        00:00:05 asm_pmon_+ASM
    oracle    6818     1  0 Jun03 ?        00:00:14 ora_pmon_QADB
    oracle   21648     1  0 16:13 ?        00:00:00 ora_pmon_DEVDB

Database called DEVDB has only two copies of controlfile and both of them are on ASM. My task was to add a copy of controlfile to disk.

These are the steps I did to copy the controlfile from ASM to disk.

First, I found the the current controlfiles locations of DEVDB.

    MoidDBServer > export ORACLE_SID=DEVDB
    MoidDBServer > sqlplus / as sysdba
    SQL> show parameter control;

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    control_file_record_keep_time        integer     7
    control_files                        string      +DG1/DEVDB/controlfile/curre
nt.257.686842909,  +DG2/DEVDB/controlfile/current.256.686842909
                            
                            
                           
Now I updated the control_files parameter in spfile to reflect the new controlfile I will be creating next.

    SQL> alter system set control_files = '+DG1/DEVDB/controlfile/current.257.686842909', '+DG2/DEVDB/controlfile/current.256.686842909', '/backup/DEVDB/CONTROLFILE/control03.ctl' scope=spfile;
  


Then I created a pfile in case we need it later:
    SQL> create pfile='/tmp/initDEVDB.ora' from spfile;



Then database was brought down cleanly:
    SQL> shutdown immediate;



Now, to copy controlfile from asm to disk, I logged in as sysdba to any other database (in this case, it will be database called QADB) which is up. Then I created the following temporary directories:

    MoidDBServer > export ORACLE_SID=QADB
    MoidDBServer > sqlplus / as sysdba
  
    SQL> create or replace directory AA as '+DG1/DEVDB/controlfile';
    SQL> create or replace directory BB as '/backup/DEVDB/CONTROLFILE';
  


Then I issued the following to copy the controlfile from ASM to DISK:


    SQL>    BEGIN
              DBMS_FILE_TRANSFER.COPY_FILE(
              source_directory_object => 'AA',
              source_file_name => 'current.257.686842909',
              destination_directory_object => 'BB',
              destination_file_name => 'control03.ctl');
        END;
        /
    SQL> exit;


AT this point, I went back to database I stopped earlier and started it. Since the database is started with spfile, third control file came in full use.

    MoidDBServer > export ORACLE_SID=DEVDB
    MoidDBServer > sqlplus / as sysdba
      
    SQL> startup
    ORACLE instance started.
  
    Total System Global Area  536870912 bytes
    Fixed Size                  1262812 bytes
    Variable Size             260049700 bytes
    Database Buffers          272629760 bytes
    Redo Buffers                2928640 bytes
    Database mounted.
    Database opened.
    SQL> show parameter control;
  
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    control_file_record_keep_time        integer     7
    control_files                        string      +DG1/DEVDB/controlfile/curre
                                                     nt.257.686842909, +DG2/DEVDB
                                                     /controlfile/current.256.68684
                                                     2909, /backup/DEVDB/CONTROLF
                                                     ILE/control03.ctl


Lastly, I cleaned up the temporary directory AA and BB from the second database we used earlier.


Moid

Followers