AbdulWasiq: January 2014

Thursday 23 January 2014

How to drop the database in oracle?

SQL> --How to drop the database?--
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict mount;
ORACLE instance started.

Total System Global Area 1824624640 bytes
Fixed Size 1337184 bytes
Variable Size 1090521248 bytes
Database Buffers 721420288 bytes
Redo Buffers 11345920 bytes
Database mounted.
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Monday 13 January 2014

How to deletes the backup which are no longer needed?

DELETE Commands :
=================
Delete commands delete/remove specific items from the server, repository catalog.

$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 27 22:07:16 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 1 27-DEC-13
Backup Piece 1 27-DEC-13 /d01/oracle/flash_recovery_area/PROD/backupset/2013_12_27/o1_mf_ncnnf_TAG20131227T220955_9cvcbwhr_.bkp
Backup Set 2 27-DEC-13
Backup Piece 2 27-DEC-13 /backup/rman/prod/c-246965557-20131227-00

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/d01/oracle/flash_recovery_area/PROD/backupset/2013_12_27/o1_mf_ncnnf_TAG20131227T220955_9cvcbwhr_.bkp RECID=1 STAMP=835308596
deleted backup piece
backup piece handle=/backup/rman/prod/c-246965557-20131227-00 RECID=2 STAMP=835308597
Deleted 2 objects

RMAN>

Note: -
The above command deletes all the backups that are older based on the retention policy setup.

How to find which backup files can be deleted or not need anymore?

$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 27 22:07:16 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 1 27-DEC-13
Backup Piece 1 27-DEC-13 /d01/oracle/flash_recovery_area/PROD/backupset/2013_12_27/o1_mf_ncnnf_TAG20131227T220955_9cvcbwhr_.bkp
Backup Set 2 27-DEC-13
Backup Piece 2 27-DEC-13 /backup/rman/prod/c-246965557-20131227-00

RMAN>

How to find which files need to be backup?

++++++++++++++++++
REPORT Commands :
++++++++++++++++++
Report commands list/report specific information.

++++++++++++++++++++++++++++++++++++++++++
How to find which files need to backup?
+++++++++++++++++++++++++++++++++++++++++++
rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 27 22:07:16 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of files with less than 2 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
2 0 /d01/oracle/oradata/prod/sysaux01.dbf
3 0 /d01/oracle/oradata/prod/undotbs01.dbf
4 0 /d01/oracle/oradata/prod/users01.dbf

Friday 10 January 2014

HOW TO CONFIGURE SNAPSHOT CONTROLFILE NAME ?

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/d02/oracle/snapcf_%d.f';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/d02/oracle/snapcf_%d.f';
new RMAN configuration parameters are successfully stored

RMAN>

How to find which backup files can be deleted in rman?

+++++++++++++++++++++++++++++++++++++++++++++++++++++
How to find which backup files can be deleted in rman?
++++++++++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 22:33:22 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN>

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 4 27-DEC-13
Backup Piece 4 27-DEC-13 /backup/rman/prod/c-246965557-20131227-01
Backup Set 6 27-DEC-13
Backup Piece 6 27-DEC-13 /backup/rman/prod/c-246965557-20131227-02
Backup Set 8 27-DEC-13
Backup Piece 8 27-DEC-13 /backup/rman/prod/c-246965557-20131227-03
Backup Set 10 27-DEC-13
Backup Piece 10 27-DEC-13 /backup/rman/prod/c-246965557-20131227-04
Backup Set 12 27-DEC-13
Backup Piece 12 27-DEC-13 /backup/rman/prod/c-246965557-20131227-05
Backup Set 14 27-DEC-13
Backup Piece 14 27-DEC-13 /backup/rman/prod/c-246965557-20131227-06
Backup Set 15 27-DEC-13
Backup Piece 15 27-DEC-13 /backup/rman/prod/PROD_DB_0fosjonf_15_1
Backup Set 16 27-DEC-13
Backup Piece 16 27-DEC-13 /backup/rman/prod/PROD_DB_0gosjoni_16_1
Backup Set 17 27-DEC-13
Backup Piece 17 27-DEC-13 /backup/rman/prod/PROD_DB_0hosjooc_17_1
Backup Set 18 27-DEC-13
Backup Piece 18 27-DEC-13 /backup/rman/prod/c-246965557-20131227-07
Backup Set 19 28-DEC-13
Backup Piece 19 28-DEC-13 /backup/rman/prod/28dec2013_fullbkp/PROD_db_u0josm4s5_s19_p1_t835392389_db
Backup Set 22 28-DEC-13
Backup Piece 22 28-DEC-13 /backup/rman/prod/28dec2013_fullbkp/PROD_db_u0kosm4s6_s20_p1_t835392390_db
Backup Set 23 28-DEC-13
Backup Piece 23 28-DEC-13 /backup/rman/prod/28dec2013_fullbkp/PROD_db_u0losm4s6_s21_p1_t835392390_db
Backup Set 21 28-DEC-13
Backup Piece 21 28-DEC-13 /backup/rman/prod/28dec2013_fullbkp/PROD_db_u0mosm4s6_s22_p1_t835392390_db
Backup Set 20 28-DEC-13
Backup Piece 20 28-DEC-13 /backup/rman/prod/28dec2013_fullbkp/PROD_db_u0nosm4s6_s23_p1_t835392390_db
Backup Set 24 28-DEC-13
Backup Piece 24 28-DEC-13 /backup/rman/prod/28dec2013_fullbkp/PROD_db_u0oosm4tk_s24_p1_t835392436_db
Backup Set 25 28-DEC-13
Backup Piece 25 28-DEC-13 /backup/rman/prod/c-246965557-20131228-00
Backup Set 26 28-DEC-13
Backup Piece 26 28-DEC-13 /backup/rman/prod/28dec2013_fullbkp/Control_bk_u0qosm4tn_s26_p1_t835392439_bk
Backup Set 27 28-DEC-13
Backup Piece 27 28-DEC-13 /backup/rman/prod/c-246965557-20131228-01
Backup Set 29 30-DEC-13
Backup Piece 29 30-DEC-13 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20131230-00
Backup Set 31 30-DEC-13
Backup Piece 31 30-DEC-13 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20131230-01
Backup Set 34 30-DEC-13
Backup Piece 34 30-DEC-13 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20131230-02
Backup Set 38 30-DEC-13
Backup Piece 38 30-DEC-13 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20131230-03
Backup Set 40 30-DEC-13
Backup Piece 40 30-DEC-13 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20131230-04
Backup Set 41 30-DEC-13
Backup Piece 41 30-DEC-13 /d04/oracle/db_1dosr957_1_1.bck
Backup Set 42 30-DEC-13
Backup Piece 42 30-DEC-13 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20131230-05
Backup Set 44 30-DEC-13
Backup Piece 44 30-DEC-13 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20131230-06
Backup Set 45 30-DEC-13
Backup Piece 45 30-DEC-13 /d01/oracle/flash_recovery_area/PROD/backupset/2013_12_30/o1_mf_annnn_TAG20131230T205254_9d33ygf8_.bkp
Backup Set 46 30-DEC-13
Backup Piece 46 30-DEC-13 /d01/oracle/flash_recovery_area/PROD/backupset/2013_12_30/o1_mf_nnndf_TAG20131230T205255_9d33yhoz_.bkp
Archive Log 12 30-DEC-13 /d01/oracle/flash_recovery_area/PROD/archivelog/2013_12_30/o1_mf_1_14_9d33yjqw_.arc
Backup Set 47 30-DEC-13
Backup Piece 47 30-DEC-13 /d01/oracle/flash_recovery_area/PROD/backupset/2013_12_30/o1_mf_annnn_TAG20131230T205256_9d33yjvs_.bkp
Datafile Copy 2 30-DEC-13 /d01/oracle/flash_recovery_area/PROD/datafile/o1_mf_sysaux_9d34c0wd_.dbf
Backup Set 48 30-DEC-13
Backup Piece 48 30-DEC-13 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20131230-07
Backup Set 49 30-DEC-13
Backup Piece 49 30-DEC-13 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20131230-08
Backup Set 51 01-JAN-14
Backup Piece 51 01-JAN-14 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20140101-00
Backup Set 53 01-JAN-14
Backup Piece 53 01-JAN-14 /d01/oracle/flash_recovery_area/PROD/backupset/2014_01_01/o1_mf_nnndf_TAG20140101T092010_9d743lot_.bkp
Backup Set 55 01-JAN-14
Backup Piece 55 01-JAN-14 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20140101-01
Backup Set 57 01-JAN-14
Backup Piece 57 01-JAN-14 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20140101-02
Backup Set 58 05-JAN-14
Backup Piece 58 05-JAN-14 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20140105-00
Backup Set 59 10-JAN-14
Backup Piece 59 10-JAN-14 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20140110-00
Backup Set 60 10-JAN-14
Backup Piece 60 10-JAN-14 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20140110-01
Backup Set 61 10-JAN-14
Backup Piece 61 10-JAN-14 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20140110-02
Backup Set 62 10-JAN-14
Backup Piece 62 10-JAN-14 /d01/oracle/flash_recovery_area/PROD/backupset/2014_01_10/o1_mf_nnsnf_TAG20140110T211622_9f05ggz1_.bkp
Backup Set 63 10-JAN-14
Backup Piece 63 10-JAN-14 /d01/oracle/product/11.2.0/dbs/cfc-246965557-20140110-03

RMAN>

How to validate the database backup?

++++++++++++++++++++++++++++++++++++++++++++++++
How to validate the database backup?
++++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 22:30:18 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> BACKUP VALIDATE DATABASE;

Starting backup at 10-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=131 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/d01/oracle/oradata/prod/system01.dbf
input datafile file number=00002 name=/d01/oracle/oradata/prod/sysaux01.dbf
input datafile file number=00005 name=/d01/oracle/oradata/prod/users02.dbf
input datafile file number=00006 name=/d01/oracle/oradata/prod/example01.dbf
input datafile file number=00009 name=/d01/oracle/oradata/prod/abdulwasiq01.dbf
input datafile file number=00003 name=/d01/oracle/oradata/prod/undotbs01.dbf
input datafile file number=00007 name=/d01/oracle/oradata/prod/abdulwasiq.dbf
input datafile file number=00008 name=/d01/oracle/oradata/prod/tbs.dbf
input datafile file number=00004 name=/d01/oracle/oradata/prod/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 13489 87043 948309
File Name: /d01/oracle/oradata/prod/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 58556
Index 0 12305
Other 0 2690

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 17824 62731 948307
File Name: /d01/oracle/oradata/prod/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 11877
Index 0 8385
Other 0 24634

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 1 5760 948309
File Name: /d01/oracle/oradata/prod/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 5759

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 466 640 901698
File Name: /d01/oracle/oradata/prod/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 20
Index 0 3
Other 0 151

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 25473 25600 821002
File Name: /d01/oracle/oradata/prod/users02.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 127

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 25469 25600 904394
File Name: /d01/oracle/oradata/prod/example01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1
Index 0 0
Other 0 130

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 1149 1280 932979
File Name: /d01/oracle/oradata/prod/abdulwasiq.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1
Index 0 0
Other 0 130

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8 OK 0 1153 1280 931032
File Name: /d01/oracle/oradata/prod/tbs.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 127

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 OK 0 12673 12800 934939
File Name: /d01/oracle/oradata/prod/abdulwasiq01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 127

channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 594
Finished backup at 10-JAN-14

RMAN>

How to backup controlfile with the TAG?

RMAN> BACKUP CURRENT CONTROLFILE TAG = mondaypmbackup;

Starting backup at 10-JAN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 10-JAN-14
channel ORA_DISK_1: finished piece 1 at 10-JAN-14
piece handle=/d01/oracle/flash_recovery_area/PROD/backupset/2014_01_10/o1_mf_ncnnf_MONDAYPMBACKUP_9f09og16_.bkp tag=MONDAYPMBACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-JAN-14

RMAN>

How to backup all archivelog ?

[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 22:26:10 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> backup archivelog all;

Starting backup at 10-JAN-14
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
skipping archived log file /d01/oracle/flash_recovery_area/PROD/archivelog/2013_12_30/o1_mf_1_14_9d33yjqw_.arc; already backed up 1 time(s)
skipping archived log file /d01/oracle/flash_recovery_area/PROD/archivelog/2014_01_01/o1_mf_1_15_9d71k9d8_.arc; already backed up 1 time(s)
skipping archived log file /d01/oracle/flash_recovery_area/PROD/archivelog/2014_01_01/o1_mf_1_16_9d743k8w_.arc; already backed up 1 time(s)
skipping archived log file /d01/oracle/flash_recovery_area/PROD/archivelog/2014_01_01/o1_mf_1_17_9d744cr5_.arc; already backed up 1 time(s)
skipping archived log file /d01/oracle/flash_recovery_area/PROD/archivelog/2014_01_01/o1_mf_1_18_9d7454yp_.arc; already backed up 1 time(s)
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=19 RECID=17 STAMP=836063753
input archived log thread=1 sequence=20 RECID=18 STAMP=836066791
input archived log thread=1 sequence=21 RECID=19 STAMP=836319224
input archived log thread=1 sequence=22 RECID=20 STAMP=836517626
input archived log thread=1 sequence=23 RECID=21 STAMP=836519184
channel ORA_DISK_1: starting piece 1 at 10-JAN-14
channel ORA_DISK_1: finished piece 1 at 10-JAN-14
piece handle=/d01/oracle/flash_recovery_area/PROD/backupset/2014_01_10/o1_mf_annnn_TAG20140110T222624_9f09krqr_.bkp tag=TAG20140110T222624 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 10-JAN-14

RMAN>

How to execute multiple commands at a time in rman?

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
How to execute multiple commands at a time in rman?
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
NOTE * : -Use "run" block to execute multiple commands

EXAMPLE * ;_
run
{
backup current controlfile;
backup datafile 1;
}

[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jan 1 12:11:13 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: ORCL (DBID=1362912542)

RMAN> run
{
backup current controlfile;
backup datafile 1;
}2> 3> 4> 5>

Starting backup at 01-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=5 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=70 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=135 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 01-JAN-14
channel ORA_DISK_1: finished piece 1 at 01-JAN-14
piece handle=/d02/oracle/flash_recovery_area/ORCL/backupset/2014_01_01/o1_mf_ncnnf_TAG20140101T121121_9d7g4lvb_.bkp tag=TAG20140101T121121 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-JAN-14

Starting backup at 01-JAN-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/d02/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 01-JAN-14
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_3: starting piece 1 at 01-JAN-14
including current control file in backup set
channel ORA_DISK_2: starting piece 1 at 01-JAN-14
channel ORA_DISK_3: finished piece 1 at 01-JAN-14
piece handle=/d02/oracle/flash_recovery_area/ORCL/backupset/2014_01_01/o1_mf_nnsnf_TAG20140101T121123_9d7g4n8p_.bkp tag=TAG20140101T121123 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_2: finished piece 1 at 01-JAN-14
piece handle=/d02/oracle/flash_recovery_area/ORCL/backupset/2014_01_01/o1_mf_ncnnf_TAG20140101T121123_9d7g4o5l_.bkp tag=TAG20140101T121123 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: finished piece 1 at 01-JAN-14
piece handle=/d02/oracle/flash_recovery_area/ORCL/backupset/2014_01_01/o1_mf_nnndf_TAG20140101T121123_9d7g4n8d_.bkp tag=TAG20140101T121123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
Finished backup at 01-JAN-14

RMAN>

Take the backup datafiles which are not backed up?

++++++++++++++++++++++++++++++++++++++++++
Take the backup datafiles which are not backed up?
+++++++++++++++++++++++++++++++++++++++++
$rman

Recovery Manager: Release 11.2.0.1.0 - Production on Mon 30 DEC 21:23:22 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> BACKUP DATABASE NOT BACKED UP;

Starting backup at 30-DEC-13
using channel ORA_DISK_1
skipping datafile 1; already backed up on 30-DEC-13
skipping datafile 2; already backed up on 30-DEC-13
skipping datafile 3; already backed up on 30-DEC-13
skipping datafile 4; already backed up on 30-DEC-13
Finished backup at 30-DEC-13

RMAN>

let me add a datafile to the tablespace
+++++++++++++++++++++++++++++++++++++++
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/d01/oracle/oradata/prod/users01.dbf
/d01/oracle/oradata/prod/undotbs01.dbf
/d01/oracle/oradata/prod/sysaux01.dbf
/d01/oracle/oradata/prod/system01.dbf

SQL> alter tablespace users add datafile '/d01/oracle/oradata/prod/users02.dbf' size 200m;

Tablespace altered.

SQL>

RMAN> BACKUP DATABASE NOT BACKED UP;

Starting backup at 30-DEC-13
using channel ORA_DISK_1
skipping datafile 1; already backed up on 30-DEC-13
skipping datafile 2; already backed up on 30-DEC-13
skipping datafile 3; already backed up on 30-DEC-13
skipping datafile 4; already backed up on 30-DEC-13
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/d01/oracle/oradata/prod/users02.dbf
channel ORA_DISK_1: starting piece 1 at 30-DEC-13
channel ORA_DISK_1: finished piece 1 at 30-DEC-13
piece handle=/d01/oracle/flash_recovery_area/PROD/backupset/2013_12_30/o1_mf_nnndf_TAG20131230T200659_9d318d1j_.bkp tag=TAG20131230T200659 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30-DEC-13

Starting Control File and SPFILE Autobackup at 30-DEC-13
piece handle=/d01/oracle/product/11.2.0/dbs/cfc-246965557-20131230-04 comment=NONE
Finished Control File and SPFILE Autobackup at 30-DEC-13

RMAN>

How to take the backup of single datafile along with archivelogs?

[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Mon 30 DEC 21:23:22 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> BACKUP DATAFILE 1 PLUS ARCHIVELOG;

Starting backup at 30-DEC-13
current log archived
using channel ORA_DISK_1
skipping archived log file /d01/oracle/flash_recovery_area/PROD/archivelog/2013_12_27/o1_mf_1_4_9cvdkyy7_.arc; already backed up 1 time(s)
skipping archived log file /d01/oracle/flash_recovery_area/PROD/archivelog/2013_12_27/o1_mf_1_5_9cvk0qcf_.arc; already backed up 1 time(s)
skipping archived log file /d01/oracle/flash_recovery_area/PROD/archivelog/2013_12_27/o1_mf_1_6_9cvk1n6q_.arc; already backed up 1 time(s)
skipping archived log file /d01/oracle/flash_recovery_area/PROD/archivelog/2013_12_28/o1_mf_1_7_9cxx5dp7_.arc; already backed up 1 time(s)
skipping archived log file /d01/oracle/flash_recovery_area/PROD/archivelog/2013_12_28/o1_mf_1_8_9cxx6wsq_.arc; already backed up 1 time(s)
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=7 STAMP=835392439
input archived log thread=1 sequence=10 RECID=8 STAMP=835556826
input archived log thread=1 sequence=11 RECID=9 STAMP=835559895
channel ORA_DISK_1: starting piece 1 at 30-DEC-13
channel ORA_DISK_1: finished piece 1 at 30-DEC-13
piece handle=/d01/oracle/flash_recovery_area/PROD/backupset/2013_12_30/o1_mf_annnn_TAG20131230T195815_9d30qznf_.bkp tag=TAG20131230T195815 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30-DEC-13

Starting backup at 30-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/d01/oracle/oradata/prod/system01.dbf
channel ORA_DISK_1: starting piece 1 at 30-DEC-13
channel ORA_DISK_1: finished piece 1 at 30-DEC-13
piece handle=/d01/oracle/flash_recovery_area/PROD/backupset/2013_12_30/o1_mf_nnndf_TAG20131230T195816_9d30r0rt_.bkp tag=TAG20131230T195816 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 30-DEC-13

Starting backup at 30-DEC-13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=10 STAMP=835559911
channel ORA_DISK_1: starting piece 1 at 30-DEC-13
channel ORA_DISK_1: finished piece 1 at 30-DEC-13
piece handle=/d01/oracle/flash_recovery_area/PROD/backupset/2013_12_30/o1_mf_annnn_TAG20131230T195831_9d30rj04_.bkp tag=TAG20131230T195831 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 30-DEC-13

Starting Control File and SPFILE Autobackup at 30-DEC-13
piece handle=/d01/oracle/product/11.2.0/dbs/cfc-246965557-20131230-03 comment=NONE
Finished Control File and SPFILE Autobackup at 30-DEC-13

RMAN>

How to take backup of the database with TAG?

++++++++++++++++++++++++++++++++++++++++++++++
How to take backup of the database with TAG
++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Mon 30 DEC 21:23:22 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> BACKUP DATABASE TAG='TEST BACKUP';

Starting backup at 30-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/d01/oracle/oradata/prod/system01.dbf
input datafile file number=00002 name=/d01/oracle/oradata/prod/sysaux01.dbf
input datafile file number=00003 name=/d01/oracle/oradata/prod/undotbs01.dbf
input datafile file number=00004 name=/d01/oracle/oradata/prod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 30-DEC-13
channel ORA_DISK_1: finished piece 1 at 30-DEC-13
piece handle=/d01/oracle/flash_recovery_area/PROD/backupset/2013_12_30/o1_mf_nnndf_TEST_BACKUP_9d307to7_.bkp tag=TEST BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 30-DEC-13

Starting Control File and SPFILE Autobackup at 30-DEC-13
piece handle=/d01/oracle/product/11.2.0/dbs/cfc-246965557-20131230-02 comment=NONE
Finished Control File and SPFILE Autobackup at 30-DEC-13

RMAN>

How to take multiple tablespace backup using rman?

[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 21:23:22 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> backup tablespace system,sysaux;

Starting backup at 10-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/d01/oracle/oradata/prod/system01.dbf
input datafile file number=00002 name=/d01/oracle/oradata/prod/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 10-JAN-14
channel ORA_DISK_1: finished piece 1 at 10-JAN-14
piece handle=/d01/oracle/flash_recovery_area/PROD/backupset/2014_01_10/o1_mf_nnndf_TAG20140110T212344_9f05w88v_.bkp tag=TAG20140110T212344 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 10-JAN-14
channel ORA_DISK_1: finished piece 1 at 10-JAN-14
piece handle=/d01/oracle/flash_recovery_area/PROD/backupset/2014_01_10/o1_mf_ncsnf_TAG20140110T212344_9f05x2f2_.bkp tag=TAG20140110T212344 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-JAN-14

RMAN>

How to backup particular tablespace in rman?

[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 21:21:53 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> backup tablespace users;

Starting backup at 10-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=197 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/d01/oracle/oradata/prod/users02.dbf
input datafile file number=00004 name=/d01/oracle/oradata/prod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 10-JAN-14
channel ORA_DISK_1: finished piece 1 at 10-JAN-14
piece handle=/d01/oracle/flash_recovery_area/PROD/backupset/2014_01_10/o1_mf_nnndf_TAG20140110T212207_9f05s767_.bkp tag=TAG20140110T212207 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-JAN-14

RMAN>

How to backup multiple datafiles using rman?

+++++++++++++++++++++++++++++++
How to backup multiple datafiles using rman?
++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 21:20:20 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> backup datafile 2,3;

Starting backup at 10-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/d01/oracle/oradata/prod/sysaux01.dbf
input datafile file number=00003 name=/d01/oracle/oradata/prod/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 10-JAN-14
channel ORA_DISK_1: finished piece 1 at 10-JAN-14
piece handle=/d01/oracle/flash_recovery_area/PROD/backupset/2014_01_10/o1_mf_nnndf_TAG20140110T212037_9f05pfnp_.bkp tag=TAG20140110T212037 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 10-JAN-14

RMAN>

How to backup a datafile using rman?

+++++++++++++++++++++++++++++++++++++++
How to backup a datafile using rman?
++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 21:18:03 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> configure controlfile autobackup off;

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored

RMAN> backup datafile 1;

Starting backup at 10-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/d01/oracle/oradata/prod/system01.dbf
channel ORA_DISK_1: starting piece 1 at 10-JAN-14
channel ORA_DISK_1: finished piece 1 at 10-JAN-14
piece handle=/d01/oracle/flash_recovery_area/PROD/backupset/2014_01_10/o1_mf_nnndf_TAG20140110T211828_9f05ldfw_.bkp tag=TAG20140110T211828 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 10-JAN-14
channel ORA_DISK_1: finished piece 1 at 10-JAN-14
piece handle=/d01/oracle/flash_recovery_area/PROD/backupset/2014_01_10/o1_mf_ncsnf_TAG20140110T211828_9f05m6l7_.bkp tag=TAG20140110T211828 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-JAN-14

RMAN>

How to backup of spfile?

++++++++++++++++++++++++++++++++++++++
How to backup of spfile?
++++++++++++++++++++++++++++++++++++++

[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 21:16:13 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> backup spfile;

Starting backup at 10-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 10-JAN-14
channel ORA_DISK_1: finished piece 1 at 10-JAN-14
piece handle=/d01/oracle/flash_recovery_area/PROD/backupset/2014_01_10/o1_mf_nnsnf_TAG20140110T211622_9f05ggz1_.bkp tag=TAG20140110T211622 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-JAN-14

Starting Control File and SPFILE Autobackup at 10-JAN-14
piece handle=/d01/oracle/product/11.2.0/dbs/cfc-246965557-20140110-03 comment=NONE
Finished Control File and SPFILE Autobackup at 10-JAN-14

RMAN>

How to enable controlfile autobackup on in rman?

[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 21:07:53 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> configure controlfile autobackup on;

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN>

How to set retention policy to 2?

[oracle@wasiq 2013_12_27]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 27 22:21:28 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters are successfully stored

RMAN> show retention policy;

RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

The above commands indicates how many days the backup copies need to be retained. Default is 1 day.

How to backup current controlfile using rman?

=============================================
How to backup current controlfile using rman?
==============================================
[oracle@wasiq prod]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 27 22:07:16 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> backup current controlfile;

Starting backup at 27-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 27-DEC-13
channel ORA_DISK_1: finished piece 1 at 27-DEC-13
piece handle=/d01/oracle/flash_recovery_area/PROD/backupset/2013_12_27/o1_mf_ncnnf_TAG20131227T220955_9cvcbwhr_.bkp tag=TAG20131227T220955 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-DEC-13

Starting Control File and SPFILE Autobackup at 27-DEC-13
piece handle=/backup/rman/prod/c-246965557-20131227-00 comment=NONE
Finished Control File and SPFILE Autobackup at 27-DEC-13

RMAN>

How to change controlfile autobackup format in rman?

==================================================
How to change controlfile autobackup format in rman?
=====================================================
[oracle@wasiq prod]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 27 22:07:16 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> configure controlfile autobackup format for device type disk to '/backup/rman/prod/%F';

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/rman/prod/%F';
new RMAN configuration parameters are successfully stored

RMAN> show controlfile autobackup format;

RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/rman/prod/%F';

RMAN>

How to change rman setting using "CONFIGURE" command?

Configure * : - To change or set the configuration of rman will use configure command.

++++++++++++++++++++++++++++++++++++++++++++++
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT
++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/rman/prod/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';
new RMAN configuration parameters are successfully stored

++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN>

++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;
++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored

RMAN>

++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
new RMAN configuration parameters are successfully stored

RMAN>

+++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE RETENTION POLICY CLEAR;
++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE RETENTION POLICY CLEAR;

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
RMAN configuration parameters are successfully reset to default value

RMAN>

++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';
++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';
new RMAN configuration parameters are successfully stored

RMAN> show CONTROLFILE AUTOBACKUP FORMAT;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';

RMAN>

++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';
RMAN configuration parameters are successfully reset to default value

RMAN>

+++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;
+++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'cf%F';
RMAN configuration parameters are successfully reset to default value

RMAN>

+++++++++++++++++++++++++++++++++++++++++++++
CONFIGURE DEFAULT DEVICE TYPE TO disk;
+++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO disk;

new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1

RMAN>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored

RMAN>

++++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
+++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
new RMAN configuration parameters are successfully stored

RMAN>

++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN>

+++++++++++++++++++++++++++++++++++++++++++++++
RMAN> CONFIGURE BACKUP OPTIMIZATION OFF;
++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> CONFIGURE BACKUP OPTIMIZATION OFF;

old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION OFF;
new RMAN configuration parameters are successfully stored

RMAN>

++++++++++++++++++++++++++++++++++
CONFIGURING MAXSETSIZE
++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
RMAN> CONFIGURE MAXSETSIZE TO 100M;

new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 100 M;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE MAXSETSIZE TO UNLIMITED;

old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 100 M;
new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO UNLIMITED;
new RMAN configuration parameters are successfully stored

RMAN>

+++++++++++++++++++++++++++++++++++++++++++++++++++++++
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/d02/oracle/snapcf_%d.f';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
[oracle@wasiq ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 10 20:57:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/d02/oracle/snapcf_%d.f';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/d02/oracle/snapcf_%d.f';
new RMAN configuration parameters are successfully stored

RMAN>

How to use show command in rman?

Show * :- use to display the current configuration of rman.

[oracle@wasiq d01]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 27 21:43:39 2013

RMAN> connect target /;

connected to target database: PROD (DBID=246965557)

using target database control file instead of recovery catalog

RMAN> SHOW RETENTION POLICY;

RMAN configuration parameters for database with db_unique_name PROD are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

RMAN> SHOW DEVICE TYPE;

RMAN configuration parameters for database with db_unique_name PROD are:

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;

RMAN> SHOW CHANNEL;

RMAN configuration parameters for database with db_unique_name PROD are:

RMAN configuration has no stored or default parameters

RMAN> SHOW MAXSETSIZE;

RMAN configuration parameters for database with db_unique_name PROD are:

CONFIGURE MAXSETSIZE TO UNLIMITED;

RMAN> CONFIGURE MAXSETSIZE TO 500m;

old RMAN configuration parameters:

CONFIGURE MAXSETSIZE TO UNLIMITED;

new RMAN configuration parameters:

CONFIGURE MAXSETSIZE TO 500 M;

new RMAN configuration parameters are successfully stored

RMAN> SHOW MAXSETSIZE;

RMAN configuration parameters for database with db_unique_name PROD are:

CONFIGURE MAXSETSIZE TO 500 M;

RMAN> SHOW BACKUP OPTIMIZATION;

RMAN configuration parameters for database with db_unique_name PROD are:

CONFIGURE BACKUP OPTIMIZATION ON;

RMAN> SHOW SNAPSHOT CONTROLFILE NAME;

RMAN configuration parameters for database with db_unique_name PROD are:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/d01/oracle/product/11.2.0/dbs/snapcf_prod.f';

RMAN> SHOW CONTROLFILE AUTOBACKUP;

RMAN configuration parameters for database with db_unique_name PROD are:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> SHOW COMPRESSION ALGORITHM;

RMAN configuration parameters for database with db_unique_name PROD are:

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

RMAN> SHOW ENCRYPTION ALGORITHM;

RMAN configuration parameters for database with db_unique_name PROD are:

CONFIGURE ENCRYPTION ALGORITHM 'AES128';

RMAN>

How to check the configuration of rman?

By default the RMAN configuration is stored in the control file. You can see the default values
for the configuration parameters by using RMAN to connect to the database and issuing the SHOW ALL command.
==============================
Check RMAN configuration
==============================
[oracle@wasiq d01]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 27 21:43:39 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN>
RMAN> connect target /;

connected to target database: PROD (DBID=246965557)
using target database control file instead of recovery catalog

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/d01/oracle/product/11.2.0/dbs/snapcf_prod.f'; # default

RMAN>

ALL ABOUT RMAN

rman
++++++++
rman stands for recovery manager .
It is utility/tool to take backup,perform restore and recovery.
And also known as rman client.
rman executable is located at $ORACLE_HOME/bin.
when we type rman and hit enter it will internally call recover.bsq file.
Brain behain rman is recover.bsq which is located at $ORACLE_HOME/rdbms/admin.

Target Database:
+++++++
The database which is going to backup(source).

Backup Set
+++++++++
It is a logical structure that contain one or more backup pieces.
Default one backup piece for one backup set.

Backup Piece
+++++++++++
It is a physical file or operating system file which containing the backup of datafile, archive redolog file and control file.

No Catalog
++++++++++
Default RMAN is configured in NO CATALOG Mode.
RMAN backup information will be stored in Controlfile.

Catalog
++++++
Backup information stored in CATALOG that is seperate from TARGET database in the form of tables and views.Backup information also stored in controlfile.

Catalog Database
++++++++++++++
In which catalog object will store.
Catalog objects are tables and views.

Catalog Owner
++++++++++++
Who owns the catalog object.

Degree of Paralism
++++++++++++++
The number of channel that rman can open during backup or recovery.
Default is 1 Channel.

Backup Optimization
++++++++++++++++
rman doesnt perform a file backup if it has already backed up identical version of the file.

Image Copy
+++++++++
Image copies are similar to the copies of operating system files.
We can make image copies of datafile, controlfile and archive redolog file.
Rman image copies can be made only to disk.
They can't be made to tape.

Proxy Copy
++++++++++
rman can alos perform a special kind of backup called the proxy backup.
proxy copies cant be used to disks.

How to clone a table?

SQL> ---How to clone a table--
SQL> show user
USER is "SYS"
SQL> create table cloneof_emp as select * from scott.emp;
Table created.

SQL> select * from cloneof_emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7369 SMITH CLERK 7902 17-DEC-80 800

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

7521 WARD SALESMAN 7698 22-FEB-81 1250 500

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7566 JONES MANAGER 7839 02-APR-81 2975

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400

7698 BLAKE MANAGER 7839 01-MAY-81 2850

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7782 CLARK MANAGER 7839 09-JUN-81 2450

7788 SCOTT ANALYST 7566 19-APR-87 3000

7839 KING PRESIDENT 17-NOV-81 5000

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0

7876 ADAMS CLERK 7788 23-MAY-87 1100

7900 JAMES CLERK 7698 03-DEC-81 950

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7902 FORD ANALYST 7566 03-DEC-81 3000

7934 MILLER CLERK 7782 23-JAN-82 1300

14 rows selected.

SQL>

How to Remove Data Files before Opening a Database?

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1824624640 bytes
Fixed Size 1337184 bytes
Variable Size 1090521248 bytes
Database Buffers 721420288 bytes
Redo Buffers 11345920 bytes
Database mounted.
SQL> alter database datafile '/d01/oracle/oradata/prod/abdulwasiq01.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

How To Add Another Datafile to a Tablespace?

SQL> -- This is comment on sql--before adding a datafile in a tablespace check mount point space --
SQL> !df -h /d01/oracle/oradata/prod
Filesystem Size Used Avail Use% Mounted on
/dev/sda9 15G 11G 2.6G 82% /d01

SQL> -- To see datafiles in a tablespace use below command ---
SQL> select file_name from dba_data_files where file_name like '%abdulwasiq%';

FILE_NAME
--------------------------------------------------------------------------------
/d01/oracle/oradata/prod/abdulwasiq.dbf

SQL> ---Adding a datafile in a tablespace ---
SQL> alter tablespace abdulwasiq add datafile '/d01/oracle/oradata/prod/abdulwasiq01.dbf' size 100m;

Tablespace altered.

SQL> --Now check whether the datafiles is added or not-----
SQL> select file_name from dba_data_files where file_name like '%abdulwasiq%';

FILE_NAME
--------------------------------------------------------------------------------
/d01/oracle/oradata/prod/abdulwasiq.dbf
/d01/oracle/oradata/prod/abdulwasiq01.dbf

SQL>

How To Bring a Tablespace Online?

SQL> alter tablespace abdulwasiq online;

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
ABDULWASIQ ONLINE
MYTBS ONLINE

8 rows selected.

SQL>

How To Bring a Tablespace Offline?

If you want to stop users using a tablespace, you can bring it offline using the ALTER TABLESPACE ... OFFLINE statement as shown in the following script:
SQL> alter tablespace abdulwasiq offline normal;

Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
ABDULWASIQ OFFLINE
MYTBS ONLINE

8 rows selected.

SQL>

How To See Free Space of Each Tablespace?

SQL> conn scott/tiger
Connected.
SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES FROM USER_FREE_SPACE WHERE TABLESPACE_NAME IN ('USERS', 'ABDULWASIQ');

TABLESPACE_NAME FILE_ID BYTES
------------------------- ---------- ----------
ABDULWASIQ 7 9371648
USERS 5 208666624
USERS 4 3866624

SQL>

How To Create a Table in a Specific Tablespace?

SQL> alter user scott account unlock;

User altered.

SQL> alter user scott identified by tiger;

User altered.

SQL> conn scott/tiger
Connected.
SQL> select count(*) from emp;

COUNT(*)
----------
14

SQL> CREATE TABLE image_emp TABLESPACE abdulwasiq as select * from emp;

Table created.

SQL> SELECT table_name,tablespace_name, num_rows FROM USER_TABLES WHERE tablespace_name in ('USERS', 'ABDULWASIQ');

TABLE_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------- ----------
SALGRADE USERS 5
BONUS USERS 0
EMP USERS 14
DEPT USERS 4
IMAGE_EMP ABDULWASIQ

SQL>

What Happens to Data Files If a Tablespace Is Dropped?

If a tablespace is dropped then datafiles are remain in operating system .
The logical path is broken between datafile and tablespace.
If you want to delete the tablespace along with the datafiles use this clause
INCLUDING CONTENTS AND DATAFILES.

SQL> CREATE TABLESPACE IMMU DATAFILE '/d01/oracle/oradata/prod/IMMU.dbf' size 100m;

Tablespace created.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
------------------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USERS /d01/oracle/oradata/prod/users01.dbf
UNDOTBS1 /d01/oracle/oradata/prod/undotbs01.dbf
SYSAUX /d01/oracle/oradata/prod/sysaux01.dbf
SYSTEM /d01/oracle/oradata/prod/system01.dbf
USERS /d01/oracle/oradata/prod/users02.dbf
EXAMPLE /d01/oracle/oradata/prod/example01.dbf
ABDULWASIQ /d01/oracle/oradata/prod/abdulwasiq.dbf
MYTBS /d01/oracle/oradata/prod/tbs.dbf
IMMU /d01/oracle/oradata/prod/IMMU.dbf

9 rows selected.

SQL> drop tablespace IMMU including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
------------------------- -----------------------------------------------------------------------------------
USERS /d01/oracle/oradata/prod/users01.dbf
UNDOTBS1 /d01/oracle/oradata/prod/undotbs01.dbf
SYSAUX /d01/oracle/oradata/prod/sysaux01.dbf
SYSTEM /d01/oracle/oradata/prod/system01.dbf
USERS /d01/oracle/oradata/prod/users02.dbf
EXAMPLE /d01/oracle/oradata/prod/example01.dbf
ABDULWASIQ /d01/oracle/oradata/prod/abdulwasiq.dbf
MYTBS /d01/oracle/oradata/prod/tbs.dbf

8 rows selected.

SQL>

Tablespace is dropped along with datafile's.

How To Drop a Tablespace?

If you have an existing tablespace and you don't want it anymore.
You can delete a tablespace by using the DROP TABLESPACE statement as shown in the example below.

SQL> CREATE TABLESPACE my_space DATAFILE '/d01/oracle/oradata/prod/myspace.dbf' SIZE 100M;

Tablespace created.

SQL> drop tablespace my_space;

Tablespace dropped.

SQL>

How To Rename a Tablespace?

SQL> CREATE TABLESPACE tbs DATAFILE '/d01/oracle/oradata/prod/tbs.dbf' SIZE 10M;

Tablespace created.

SQL> alter tablespace tbs rename to mytbs;

Tablespace altered.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;

TABLESPACE_NAME STATUS CONTENTS
------------------------- --------- ---------
SYSTEM ONLINE PERMANENT
SYSAUX ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
EXAMPLE ONLINE PERMANENT
ABDULWASIQ ONLINE PERMANENT
MYTBS ONLINE PERMANENT

8 rows selected.

SQL>

Pages

Thursday 23 January 2014

Monday 13 January 2014

Friday 10 January 2014