How to drop a database from SQL*PLUS

We need to remove database created only fr test purpose using RMAN duplicate command.



SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.

ORACLE instance shut down.
SQL>
SQL>


SQL> startup mount exclusive restrict;
ORACLE instance started.

Total System Global Area 7348420608 bytes
Fixed Size                  2150272 bytes
Variable Size            1201614976 bytes
Database Buffers         6006243328 bytes
Redo Buffers              138412032 bytes
Database mounted.


SQL>
SQL> drop database;

Database dropped.

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

Drop database command remove data files and control files from OS level.

 

ORA-19573: cannot obtain exclusive enqueue for datafile 8

$ ./rman target / nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Oct 06 13:55:43 2013

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

connected to target database: SSAERP (DBID=3389811866, not open)
using target database control file instead of recovery catalog

RMAN>  RECOVER DATABASE NOREDO;

Starting recover at 06-Oct-13
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=1982 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2007072323)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=982 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00008: /ora_dat/drssaerp/xdb_SSAERP02.dbf
destination for restore of datafile 00019: /ora_idx/drssaerp/idb_SSAERP02.dbf
channel ORA_DISK_1: reading from backup piece /ora_idx/drssaerp/bk/forstandby_l ptes4o_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/10/2013 13:57:11
ORA-19870: error reading backup piece /ora_idx/drssaerp/bk/forstandby_lvptes4o_1 _1
ORA-19573: cannot obtain exclusive enqueue for datafile 8


Solution:

It seems database is open mode for which performing restore
Shutdown database and put the mount stage and start recovery.

Restore Database from Incremental Backup

$ ./rman target / nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Jan 25 10:43:52 2015

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

connected to target database: SSAERP (DBID=3389811866, not open)
using target database control file instead of recovery catalog

RMAN> catalog start with '/ora_idx/drssaerp/bk';

searching for all files that match the pattern /ora_idx/drssaerp/bk

List of Files Unknown to the Database
=====================================
File Name: /ora_idx/drssaerp/bk/forstandby_lvptes4o_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m0ptesea_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m1ptesnr_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m2ptet1n_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m3ptetb8_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m4ptetl3_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m5ptetuv_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m6pteu8q_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m7pteuja_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m8pteuun_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m9ptevap_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_maptevo3_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_mbptf028_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_mcptf0b6_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_mdptf0ir_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_meptf0me_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_mfptf0q2_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_lupterr7_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /ora_idx/drssaerp/bk/forstandby_lvptes4o_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m0ptesea_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m1ptesnr_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m2ptet1n_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m3ptetb8_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m4ptetl3_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m5ptetuv_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m6pteu8q_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m7pteuja_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m8pteuun_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_m9ptevap_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_maptevo3_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_mbptf028_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_mcptf0b6_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_mdptf0ir_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_meptf0me_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_mfptf0q2_1_1
File Name: /ora_idx/drssaerp/bk/forstandby_lupterr7_1_1

RMAN>

ORA-19506: failed to create sequential file name="/ora_idx/ssaerp/backup/forstandby_ltpteqk7_1_1", parms=""

RMAN> backup incremental from scn 95670 database FORMAT '/ora_idx/ssaerp/backup/forstandby_%U' tag 'FORSTANDBY';

Starting backup at 01/23/2013
using channel ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: starting full datafile backupset
channel ORA_SBT_TAPE_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/ora_dat/ssaerp/xdb_SSAERP01.dbf
input datafile fno=00007 name=/ora_idx/ssaerp/xdb_SSAERP01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 01/23/2013
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_SBT_TAPE_1 channel at 01/23/2013 14:45:42
ORA-19506: failed to create sequential file, name="/ora_idx/ssaerp/backup/forstandby_ltpteqk7_1_1", parms=""
ORA-27028: skgfqcre: sbtbackup returned error
ORA-19511: Error received from media manager layer, error text:
   VxBSACreateObject: Failed with error:
   Server Status:  the specified policy is not of the correct client type

RMAN> run {
2> allocate channel t1 type disk;
3> backup incremental from scn 95670 database FORMAT '/ora_idx/ssaerp/backup/forstandby_%U' tag 'FORSTANDBY';
4> release channel t1;
5> }

Starting backup at 01/23/2013
channel t1: starting full datafile backupset
channel t1: specifying datafile(s) in backupset
input datafile fno=00006 name=/ora_dat/ssaerp/xdb_SSAERP01.dbf
input datafile fno=00007 name=/ora_idx/ssaerp/xdb_SSAERP01.dbf
channel t1: starting piece 1 at 01/23/2013
channel t1: finished piece 1 at 01/23/2013
piece handle=/ora_idx/ssaerp/backup/forstandby_lupterr7_1_1 tag=FORSTANDBY comment=NONE
channel t1: backup set complete, elapsed time: 00:05:05
channel t1: starting full datafile backupset
channel t1: specifying datafile(s) in backupset
input datafile fno=00008 name=/ora_dat/ssaerp/xdb_SSAERP02.dbf
input datafile fno=00019 name=/ora_idx/ssaerp/idb_SSAERP02.dbf
channel t1: starting piece 1 at 01/23/2013
channel t1: finished piece 1 at 01/23/2013
piece handle=/ora_idx/ssaerp/backup/forstandby_lvptes4o_1_1 tag=FORSTANDBY comment=NONE
channel t1: backup set complete, elapsed time: 00:05:06
channel t1: starting full datafile backupset
channel t1: specifying datafile(s) in backupset
input datafile fno=00009 name=/ora_dat/ssaerp/xdb_SSAERP03.dbf
input datafile fno=00020 name=/ora_idx/ssaerp/idx_SSAERP03.dbf
channel t1: starting piece 1 at 01/23/2013
channel t1: finished piece 1 at 01/23/2013
piece handle=/ora_idx/ssaerp/backup/forstandby_m0ptesea_1_1 tag=FORSTANDBY comment=NONE
channel t1: backup set complete, elapsed time: 00:05:05
channel t1: starting full datafile backupset
channel t1: specifying datafile(s) in backupset
input datafile fno=00010 name=/ora_dat/ssaerp/xdb_SSAERP04.dbf
input datafile fno=00021 name=/ora_idx/ssaerp/idx_SSAERP04.dbf
channel t1: starting piece 1 at 01/23/2013
channel t1: finished piece 1 at01/23/2013
piece handle=/ora_idx/ssaerp/backup/forstandby_m1ptesnr_1_1 tag=FORSTANDBY comment=NONE
channel t1: backup set complete, elapsed time: 00:05:15
channel t1: starting full datafile backupset
channel t1: specifying datafile(s) in backupset
input datafile fno=00011 name=/ora_dat/ssaerp/xdb_SSAERP05.dbf
input datafile fno=00022 name=/ora_idx/ssaerp/idx_SSAERP05.dbf
channel t1: starting piece 1 at 01/23/2013
channel t1: finished piece 1 at 01/23/2013
piece handle=/ora_idx/ssaerp/backup/forstandby_m2ptet1n_1_1 tag=FORSTANDBY comment=NONE
channel t1: backup set complete, elapsed time: 00:05:05
channel t1: starting full datafile backupset
channel t1: specifying datafile(s) in backupset
input datafile fno=00012 name=/ora_dat/ssaerp/xdb_SSAERP06.dbf
input datafile fno=00023 name=/ora_idx/ssaerp/idx_SSAERP06.dbf
channel t1: starting piece 1 at 01/23/2013
channel t1: finished piece 1 at 01/23/2013
piece handle=/ora_idx/ssaerp/backup/forstandby_m3ptetb8_1_1 tag=FORSTANDBY comment=NONE
channel t1: backup set complete, elapsed time: 00:05:15
channel t1: starting full datafile backupset
channel t1: specifying datafile(s) in backupset
input datafile fno=00013 name=/ora_dat/ssaerp/xdb_SSAERP07.dbf
input datafile fno=00024 name=/ora_idx/ssaerp/idx_SSAERP07.dbf

.........



input datafile fno=00002 name=/ora_dat/ssaerp/undotbs01.dbf
channel t1: starting piece 1 at 01/23/2013
channel t1: finished piece 1 at 01/23/2013
piece handle=/ora_idx/ssaerp/backup/forstandby_meptf0me_1_1 tag=FORSTANDBY comment=NONE
channel t1: backup set complete, elapsed time: 00:01:55
channel t1: starting full datafile backupset
channel t1: specifying datafile(s) in backupset
input datafile fno=00005 name=/ora_dat/ssaerp/system02.dbf
input datafile fno=00004 name=/ora_dat/ssaerp/users01.dbf
input datafile fno=00003 name=/ora_dat/ssaerp/sysaux01.dbf
input datafile fno=00001 name=/ora_dat/ssaerp/system01.dbf
channel t1: starting piece 1 at 01/23/2013
channel t1: finished piece 1 at01/23/2013
piece handle=/ora_idx/ssaerp/backup/forstandby_mfptf0q2_1_1 tag=FORSTANDBY comment=NONE
channel t1: backup set complete, elapsed time: 00:00:55
Finished backup at 01/23/2013

Starting Control File and SPFILE Autobackup at 01/23/2013
piece handle=/ora/app/oracle/bkcont/c-3389811866-20150123-00 comment=NONE
Finished Control File and SPFILE Autobackup at 01/23/2013

released channel: t1

RMAN>

Enable / Disable Flash Back Database Option

Flashback in Oracle Database

Flashback technology is a set of features in Oracle database that make your work easier to view past states of data or to move your database objects to a previous state without using point in time media recovery.

View past states of data or move database objects to previous state means you have performed some operations like  DML + COMMIT and now you want to rollback that operation, this can be done easily through FLASHBACK technology without using point in time media recovery.

[oracle@orapri bin]$ ./sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 6 02:37:02 2014

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

SQL> conn / as sysdba
Connected.
SQL> show recyclebin
SQL> show parameter recyclebin

NAME                         TYPE   VALUE
------------------------------------ ----------- ------------------------------
recyclebin                   string on
SQL> show parameter db_recovery_file_dest

NAME                         TYPE   VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest              string /ora/oracle/app/oracle/flash_r
                                     ecovery_area
db_recovery_file_dest_size         big integer 3882M

SQL> alter system set db_recovery_file_dest_size=2g;

System altered.

SQL> alter system set db_recovery_file_dest='/ora/oracle/flashback';

System altered.

SQL> show parameter db_recovery

NAME                         TYPE   VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest              string /ora/oracle/flashback
db_recovery_file_dest_size         big integer 2G

SQL>

SQL> show parameter undo_management

NAME                         TYPE   VALUE
------------------------------------ ----------- ------------------------------
undo_management              string AUTO
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1152450560 bytes
Fixed Size            2212696 bytes
Variable Size             872418472 bytes
Database Buffers    268435456 bytes
Redo Buffers                9383936 bytes
Database mounted.
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter database open;

Database altered.


 How to disable FLASHBACK in Oracle Database 11G R1 and below versions

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

Total System Global Area 1152450560 bytes
Fixed Size            2212696 bytes
Variable Size             872418472 bytes
Database Buffers    268435456 bytes
Redo Buffers                9383936 bytes
Database mounted.

SQL> alter database flashback off;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
off
SQL> alter database open;

Database altered.