DROP DATABASE VIA RMAN

If drop database command is run under RMAN then it will drop datafiles, online redo logs ,controlfiles , backups and also unregister database from recovery catalog. Only thing I noticed that was left unclean was flashback logs that has to be deleted manually.

In order to drop database within RMAN, make sure that database is in mount state and in restrictive mode.

RMAN> CONNECT TARGET /


connected to target database: TESTDB1 (DBID=39523451)

RMAN> STARTUP FORCE MOUNT
RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;


NOPROMPT will not ask for confirmation so if you want to be sure then use
 RMAN> DROP DATABASE INCLUDING BACKUPS;

Above command will use controlfile of the database so in order to user catalog use

RMAN > CONNECT TARGET SYS@TESTDB CATALOG RMAN@RMANCAT


 

Calculate how much time/work is left to finish by RMAN

This command is specific to monitor RMAN activity but can be modified easily to monitor activity by other sessions

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%'
/

　
or use  this

select OPERATION,MBYTES_PROCESSED from v$rman_status where status='RUNNING';

RMAN backup details require to restore a database

 When you have to restore a database from RMAN backup, last thing you want to do is to identify which backups, tapes and dates you need to use for database restore. There are few sql commands that can be very handy in order to extract those details.


column handle format a60
column media format a10
column tag format a20
column start_time format a20
column name format a10
set lines 200

select d.name,    p.tag,   p.handle , p.media ,    s.incremental_level "LEVEL",
 to_char(s.start_time, 'DD-MON-YY HH24:MI') start_time,
 s.elapsed_seconds/60 "MINUTES",   p.backup_type
  from  RC_DATABASE d, RC_BACKUP_PIECE p, RC_BACKUP_SET s  
  where
    d.name='ELPM'  and
    d.db_key = p.db_key  and
    s.db_key = p.db_key     and
    p.bs_key = s.bs_key      and
   s.start_time between to_date('08-SEP-11 00:00','dd-MON-yy hh24:mi')
   and to_date('10-SEP-11 00:00','dd-MON-yy hh24:mi')

Above script can give you backup details by providing database name and dates after connecting to RMAN repository.

Another method is to connect to RMAN prompt using control file

$>rman connect target / nocatalog

RMAN> restore database preview summary;

RESTORING A PHYSICAL STANDBY TO DIFFERENT LOCATION

Restoring a PHYSICAL STANDBY database to a different location …

First make memory usage of your system
You do this, in the case we need to remove shared memory or semaphore.
# ipcs -b

Assuming that you have already use RMAN to backup your standby database into flashback recovery area (db_recovery_file_dest).
                             
So, if your  db_recovery_file_dest=’/recovery_area’, then your backup will be at the following location by default. On the new server create the

recovery area and restore the backup.

Default:
/recovery_area//backupset
/recovery_area//autobackup

On the empty server, we assume you have already cloned the Oracle binaries and set your global variables such as ORACLE_HOME, ORACLE_SID, etc.

Create external password:
# orapwd file=@ORACLE_HOME/dbs/orapw password=your-sys-pass entries=3

Run the RMAN tool and login as sys. Then set your DBID to restore spfile and controlfiles.  If you don’t know where your controlfiles are located

first restore spfile then create pfile and check the location and then build it if they are not exist.

# rman target /
RMAN> SET DBID xxxxxxxxxxxx;
RMAN> STARTUP FORCE NOMOUNT;
RMAN> show all;    --  to display default rman configurations.

To restore spfile:
RMAN> restore spfile from autobackup
            db_recovery_file_dest=’/recovery_area’ db_unique_name=’xxxx’_stdby;
--or—
RMAN> restore spfile from autobackup
            db_recovery_file_dest=’/recovery_area’ db_name=’xxxx’;
Now, you can goto SQLPLUS and create your parameter file.
--or—
If your spfile and controlfile were backup on $ORACLE_HOME/dbs, then do the following:
RMAN> restore spfile from autobackup;
SQL> create pfile from spfile;


To restore controlfiles:
Shutdown, make sure all the directories including audit file and then startup with no mount.
RMAN> shutdown immediate;
--Make sure all directories exist including “adump.”
RMAN> startup nomount;
RMAN> restore controlfile from autobackup
           db_recovery_file_dest=’/recovery_area’ db_unique_name=’xxxx_stdby’;
--or--
RMAN> restore controlfile from autobackup
          db_recovery_file_dest=’/recovery_area’ db_name=’xxxx’;
--or--
If your spfile and controlfile were backup on $ORACLE_HOME/dbs, then do the following:
RMAN> restore controlfile from autobackup;

Mout the instance:
SQL> shutdown immediate;
SQL> startup mount;

Locate the database files destinations:
Note: if your controlfiles’ destination were not set, rman will copy one to $ORACLE_HOME/dbs/cntrl.dbf.
Now, from parameter file, check the diagnostic_dest, audit_file_dest, db_recovery_file_dest, etc.
Use the trace file by backing controlfile to trace to locate other data files destinations.
SQL> ALTER DATABASE BACKUP controlfile TO TRACE; -- located at udump.

Restore the database:
If the database is not mounted:
SQL> alter database mount;
RMAN> restore database;

If you have missing logs do the following:
Now go to your archivelog location and check your last archivelog sequence number. It should be more than value from the following query.
SQL> SELELCT * FROM v$log;

Then recover until the last sequence number;
RMAN> recover database until sequence your-number;

If you have get flashback recovery on/off error message, try to turn it off.
SQL> ALTER DATABASE flashback OFF;
SQL> shutdown immediate;
SQL> startup mount;
--or--
SQL> recover database;  --if you have all logs
--or--
SQL> recover database until sequence 52;
--or--
SQL> recover database using backup controlfile until sequence 52;
--or--
SQL> recover database using backup controlfile until cancel;

Important:
After the recover make sure that you have all your redo logs plus standby.
SQL> SELECT * FROM gv$logfile;
To drop it we you are on the mount mode:
SQL>ALTER DATABASE DROP LOGFILE group nnn;

Add them if there are not there in directory. You may see them on query but not physically on disk.
Example:
SQL> alter database add standby logfile
  2  '/oradata/your-db_name/stndby_redo/stndby04.log' size 50m reuse;


SQL> ALTER DATABASE OPEN resetlogs;

If you hav all your archivelogs:
Go to primary database and switch logfile and copy all archivelogs to the new server. Then create trace file with noresetlogs option.
# scp arch*.log oracle@yournew-server:$DB_RECOVERY_FILE_AREA//archivelog/.

On SQLPLUS:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE as '/home/oracle/myprim-cntl.sql' NORESETLOGS;

Copy the myprim-cntl.sql file to your new server. And edit it to replace the existing controlfiles.

Shutdown the database.
SQL> shutdown immediate;
SQL> @myprim-cntl.sql

Notice:
If you get hange, make sure you check the memory usage:
# ipcs –b

Use the following command to remove them from shared memory or semaphores if needed.
# ipcrm –m
# ipcrm –s

Creating a Duplicate Database on a New Host

Creating a Duplicate Database on a New Host. [Oracle Document ID 388431.1]
		Modified 01-FEB-2011     Type HOWTO     Status PUBLISHED

In this Document
  Goal
  Solution
     Duplicate database from server A to server B (Non ASM)
     1. Backup of the primary database.
     2. Determine how much disk space will be required.
     3. Ensuring you have enough space on your target server.
     4. Making the backup available for the duplicate process.
     5. Creating the init.ora & administration directories for the duplicate database.
     6. Ensuring SQL*NET connections to primary database and RMAN catalog are working.
     7. Prepare RMAN duplicate script.
     8. Execute the RMAN script.
  References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 9.0.1.4 to 11.2.0.0 - Release: 9.0.1 to 11.2
Information in this document applies to any platform.
***Checked for relevance on 06-Aug-2009***
***Checked for relevance on 01-Feb-2011***

Goal

The following note will guide a user in creating a duplicate database on a new host (non ASM).  The scenario refers to a recovery catalog however a catalog is not mandatory. The following example has been completed on a linux operating system.

The note is applicable for 9i, 10g and 11g. However 11g has new features related to the DUPLICATE.
See for more details on the 11g DUPLICATE new features : Note 452868.1 RMAN 'Duplicate Database' Feature in 11G

Solution

Duplicate database from server A to server B (Non ASM)

Assumed database names:
Primary Database SID:       PROD
Duplicate Database SID:    AUX
RMAN Catalog SID:          RMAN

====================================================================================
Steps

1.  Backup the primary database.

2.  Determine how much disk space will be required.

3.  Ensuring you have enough space on your target server.

4.  Making the backup available for the duplicate process.

5.  Creating the init.ora & administration directories for the duplicate database.

6.  Ensuring SQL*NET connections to primary database and RMAN catalog are working.

7.  Prepare RMAN duplicate script.

8.  Execute the RMAN script.

=====================================================================================

1. Backup of the primary database.

Host A (Target)

Prior to creating a duplicate database you must have a backup of the target database. In this example we assume the backup media is disk. Find appropriate disk space on host A and take a full backup including archive logs and current controlfile (Figure 1a). If you are planning to duplicate a subset of the database refer to (Figure 1b) this illustrates the RMAN command to backing up certain tablespaces.




[oracle@linux] export ORACLE_SID=PROD

[oracle@linux] rman target=/ catalog=rman/rman@RMAN

RMAN> run {
        allocate channel d1 type disk;
        backup format '/backups/PROD/df_t%t_s%s_p%p' database;
        sql 'alter system archive log current';
        backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
        release channel d1;
      }

       Figure 1a - This command will perform a full database backup including archivelogs and the current controlfile.

[oracle@linux] export ORACLE_SID=PROD

[oracle@linux] rman target=/ catalog=rman/rman@RMAN

RMAN> run {
        allocate channel d1 type disk;
        backup format '/backups/PROD/df_t%t_s%s_p%p'
               tablespace SYSTEM, SYSAUX, UNDO, USERS;
        sql 'alter system archive log current';
        backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
        release channel d1;
     }


    Figure 1b- This command will perform a tablespace backup ( SYSTEM, SYSAUX, UNDO & USERS) including archive logs and the current controlfile.

2. Determine how much disk space will be required.

Host A(Target) -

After deciding what you will be duplicating, calculate the entire space this will require on the new host. The full database calculation (Figure 2a) will calculate the entire space required whereas (figure 2b) allows you to enter the tablespace names in order to calculate the space required. Figure 2c provides a sample output.

Note: sql valid for version 10g only.

SQL> select DF.TOTAL/1048576 "DataFile Size Mb",
            LOG.TOTAL/1048576 "Redo Log Size Mb",
            CONTROL.TOTAL/1048576 "Control File Size Mb",
            (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb"
     from dual,
    (select sum(a.bytes) TOTAL from dba_data_files a) DF,
    (select sum(b.bytes) TOTAL from v$log b) LOG,
    (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

     Figure 2a - Calculate total space for all datafiles within database.

SQL> select DF.TOTAL/1048576 "DataFile Size Mb",
            LOG.TOTAL/1048576 "Redo Log Size Mb",
            CONTROL.TOTAL/1048576 "Control File Size Mb",
            (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,
    (select sum(a.bytes) TOTAL from dba_data_files a
     where tablespace_name in('SYSTEM','UNDOTBS1', 'SYSAUX', 'USERS')) DF,
    (select sum(b.bytes) TOTAL from v$log b) LOG,
    (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

     Figure 2b - Calculate space for list of tablespaces within primary database.

DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb
---------------- ---------------- -------------------- -------------
             900              150             20.34375    1070.34375

     Figure 2c - Sample output of space calculation.

3. Ensuring you have enough space on your target server.

Host B (Aux)

Prior to starting the duplicate process you must ensure you have enough disk space within file system to hold the database.

[oracle@linux] df -kh
Filesystem         Size     Used     Avail     Use%     Mounted on
/dev/vg01/root     9.9G     2.8G      6.6G      30%              /
/dev/sda1          145M      16M      122M      12%          /boot
none               999M        0      999M       0%       /dev/shm
/dev/vg01/tmp      2.0G     383M      1.5G      20%           /tmp
/dev/vg01/u01       20G      12G      7.0G      62%           /u01
/dev/vg01/u02      4.9G    1010M      3.6G      22%           /u02
/dev/vg01/backups  5.9G     1.2G      4.4G      22%       /backups
/dev/vg01/oradata   15G      13G      2.0G      87%       /oradata

     Figure 3a - Sample output showing the space available on your filesystem.

Compare the results received from this query with the output from 2a or 2b and ensure you have enough diskspace for your duplicate database.

4. Making the backup available for the duplicate process.

If your backup resides on disk you will need to copy this back up from host A to host B. Ensure you place it in the same directory as where it was created. In the example below (figure 4a) the backup piece resides in ‘/backups/PROD’ these files need to be copied into the same directory on host B.

Furthermore also ensure than all archive log backups have also been moved and any archive logs in the archive log directory that may be require for the recovery. The archive logs required will depend on the point in time of the recovery.

RMAN> list backup;

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
22 Full 529M DISK 00:00:51 2006/05/16 11:12:54
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20060516T111203
Piece Name: /backups/PROD/df_t590584323_s23_p1
List of Datafiles in backup set 22
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/system01.dbf
2 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/undotbs01.dbf
3 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/sysaux01.dbf
4 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/users01.dbf


BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
24 48M DISK 00:00:06 2006/05/16 11:13:07
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20060516T111301
Piece Name: /backups/PROD/al_t590584381_s25_p1

List of Archived Logs in backup set 24
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 78 1344750 2006/05/15 04:55:34 1353208 2006/05/15 10:00:19
1 79 1353208 2006/05/15 10:00:19 1353213 2006/05/15 10:00:20
1 80 1353213 2006/05/15 10:00:20 1371564 2006/05/15 22:00:11
1 81 1371564 2006/05/15 22:00:11 1373291 2006/05/15 22:00:59
1 82 1373291 2006/05/15 22:00:59 1381066 2006/05/16 03:00:05
1 83 1381066 2006/05/16 03:00:05 1390685 2006/05/16 09:03:00
1 84 1390685 2006/05/16 09:03:00 1393870 2006/05/16 11:13:00
1 85 1393870 2006/05/16 11:13:00 1393929 2006/05/16 11:13:00

     Figure 4a - A list backup showing the backup pieces that need to be copied across Host B

5. Creating the init.ora & administration directories for the duplicate database.

Host B(AUX)

Create the pfile [initAUX.ora] parameter file in the $ORACLE_HOME/dbs directory for the auxiliary database. An example is shown below (figure 5a) with bare minimum settings. Note you may require to set parameters which are the same as your production database (refer to you primary init.ora)

# +----------------------------------------+
# | FILE : initAUX.ora                     |
# | DATABASE NAME : AUX                    |
# +----------------------------------------+
# Set the below to location of the clone Duplicate database / name of
# clone database.

audit_file_dest =/oradata/AUX/adump
background_dump_dest =/oradata/AUX/bdump
core_dump_dest =/oradata/AUX/cdump
user_dump_dest =/oradata/AUX/udump
db_name ="AUX"
instance_name =AUX
cluster_database=false (in case the production is a rac environment)


# Set the below to the location of the duplicate clone control file.

control_files =('/oradata/AUX/control01.ctl','/oradata/AUX/control02.ctl','/oradata/AUX/control03.ctl')



# Set the below for the from and to location for all data files / redo
# logs to be cloned.

db_file_name_convert =("/oradata/PROD/", "/oradata/AUX/")
log_file_name_convert =("/oradata/PROD/", "/oradata/AUX/")
#Set the below to the same as the production target

undo_management =AUTO
undo_retention =10800
undo_tablespace =UNDOTBS1
db_block_size = 8192
compatible = 10.2.0.1.0

     Figure 5a - Sample initAUX.ora with minimal settings

Following the creation of the initAUX.ora startup nomount the auxiliary instance.

[oracle@linux]export ORACLE_SID=AUX

[oracle@linux] sqlplus '/as sysdba'

SQLPLUS> startup nomount;

Figure 5b - startup nomount the AUX instance.

6. Ensuring SQL*NET connections to primary database and RMAN catalog are working.

Host B(AUX)

Ensure the production target database is open or at least mounted. If using a catalog database this must also be open. Test your SQL*NET connections: From Host B you must be able to connect to the primary DB as sysdba and make an rman connection to RMAN. Perform a basic test using sqlplus. When you have successfully connected exit sqlplus and move onto the next step.

[oracle@linux]% sqlplus ‘sys/oracle@PROD as sysdba’

[oracle@linux]% sqlplus rman/rman@RMAN   (not mandatory)

     Figure 6a - SQL*NET connections

7. Prepare RMAN duplicate script.

In a working directory on Host B create an RMAN script file duplicate.rcv. The example below (figure 7a) shows the command for a complete duplicate (figure 7b) skips the tablespaces which are not required in the duplicate and (figure 7b) provide the syntax required for a point in time duplicate.

run {
  allocate auxiliary channel C1 device type disk;
  duplicate target database to AUX;
}

     Figure 7a - Sample duplicate command.

run {
  allocate auxiliary channel C1 device type disk;
  duplicate target database to AUX skip tablespace ABC, XYZ;
}

     Figure 7b- Sample duplicate script omitting optional tablespaces;

run {
  set until time "to_date('Jan 01 2000 12:00:00','Mon DD YYYY HH24:MI:SS')";
  allocate auxiliary channel C1 device type disk;
  duplicate target database to AUX;
}

      Figure 7c- Sample duplicate script to a point in time.

8. Execute the RMAN script.

Start RMAN, connect to the production target, the catalog instance and also the auxiliary clone. Run the RMAN duplicate script as shown below. Before doing this ensure that the Oracle SID environment variable is set to the duplicate clone database.

[oracle@linux] export ORACLE_SID=AUX

[oracle@linux] rman target sys/sys@PROD catalog rman/rman@rman auxiliary /

RMAN> @duplicate.rcv

    Figure 8a - Execute the RMAN duplicate script.

References

NOTE:452868.1 - RMAN 'Duplicate Database' Feature in 11G