Skip to main content

RMAN - DATABASE RESTORE USING RMAN COLD BACKUP



NOTE: Here I'm doing backup & restore for the database name => ORCL

RMAN COLD BACKUP : We can only take. When the database is in MOUNT state. And the database is in noarchivelog mode.

RMAN HOT BACKUP : We can only take if database running in archivelog mode. Here we can take the backup in 'OPEN' state. No need to mount the database.

Please see below steps:

SQL> select name from v$database;

NAME
---------
ORCL

SQL> select open_mode from v$database;

 OPEN_MODE
------------
READ_WRITE

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     14
Current log sequence           14
SQL> exit

Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

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

NOTE : Here we are trying to take the RMAN cold backup in OPEN state. When database is in noarchivelog mode.

C:\Users>rman target sys/xxxx@ORCL

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 2 10:14:49 2014

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

connected to target database: ORCL (DBID=2746387974)

RMAN> backup database;

Starting backup at 02-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=26 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
RMAN-00471: ===========================================================
RMAN-00469: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00471: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/02/2014 10:14:44
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

Unable to backup. Please see above.

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

Now changing database into noarchivelog mode & taking the backup.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  499784472 bytes
FiORCLd Size                  1288820 bytes
Variable Size             163479276 bytes
Database Buffers          432013312 bytes
Redo Buffers                2904064 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

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

C:\Users>rman target sys/xxxxx@ORCL

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 2 10:18:43 2014

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

connected to target database: ORCL (DBID=2746387974, not open)

RMAN> backup database;

Starting backup at 02-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=34 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=C:\ORACLEORCL\ORADATA\ORCL\DATA1.DBF
input datafile fno=00006 name=C:\ORACLEORCL\ORADATA\ORCL\AED1.DBF
input datafile fno=00003 name=C:\ORACLEORCL\ORADATA\ORCL\SYSAUX.DBF
input datafile fno=00001 name=C:\ORACLEORCL\ORADATA\ORCL\SYSTEM.DBF
input datafile fno=00004 name=C:\ORACLEORCL\ORADATA\ORCL\USERS.DBF
input datafile fno=00002 name=C:\ORACLEORCL\ORADATA\ORCL\UNDO.DBF
input datafile fno=00007 name=C:\ORACLEORCL\ORADATA\ORCL\SEE1.DBF
input datafile fno=00008 name=C:\ORACLEORCL\ORADATA\ORCL\SEE2.DBF
input datafile fno=00009 name=C:\ORACLEORCL\ORADATA\ORCL\AHD2.DBF
input datafile fno=00010 name=C:\ORACLEORCL\ORADATA\ORCL\DATA2.DBF
input datafile fno=00011 name=C:\ORACLEORCL\ORADATA\ORCL\AAA1.DBF
input datafile fno=00012 name=C:\ORACLEORCL\ORADATA\ORCL\AAA2.DBF
channel ORA_DISK_1: starting piece 1 at 02-APR-14
channel ORA_DISK_1: finished piece 1 at 02-APR-14
piece handle=C:\ORACLEORCL\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2014_04_02\O1_MF_NNNDF_TAG20140402T101904_BKSLL4KF_.BKP tag=TAG20140402T101904 co
mment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:28
Finished backup at 02-APR-14

Starting Control File Autobackup at 02-APR-14
piece handle=D:\ORCL_BACKUP\SNAPCF_ORCLC-2746387974-20140402-00 comment=NONE
Finished Control File Autobackup at 02-APR-14

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

Now I'm dropping the database ORCL.


SQL> shut immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

-------------------------------------------------------------------------------------
NOTE: To drop database shut down and open it in restrict mount mode.
-------------------------------------------------------------------------------------

SQL> startup restrict mount;
ORACLE instance started.

Total System Global Area  499784472 bytes
FiORCLd Size                  1288820 bytes
Variable Size             163479276 bytes
Database Buffers          432013312 bytes
Redo Buffers                2904064 bytes
Database mounted.

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> drop database;

Database dropped.

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

Now OPEN the database in NOMOUNT state using the same PFILE.

C:\Users>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 2 10:30:38 2014

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

Enter user-name: sys/xxxxx as sysdba
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  499784472 bytes
FiORCLd Size                  1288820 bytes
Variable Size             163479276 bytes
Database Buffers          432013312 bytes
Redo Buffers                2904064 bytes
SQL> exit


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

- Now connect to RMAN and restore the control file from backup. -

C:\Users>rman target sys/xxxx@ORCL

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 2 10:31:44 2014

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

connected to target database: ORCL (not mounted)


RMAN> RESTORE CONTROLFILE FROM 'D:\ORCL_BACKUP\SNAPCF_ORCLC-2746387974-20140402-01';

Starting restore at 02-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=C:\ORACLEORCL\ORADATA\ORCL\CONTROL.DBF
Finished restore at 02-APR-14

RMAN> exit


Recovery Manager complete.

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

MOUNT the database now.

C:\Users>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 2 10:37:36 2014

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

Enter user-name: sys/xxxx as sysdba

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> alter database mount;

Database altered.

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

C:\Users>rman target sys/xxxx@ORCL

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 2 10:38:39 2014

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

connected to target database: ORCL (DBID=2746387974, not open)

RMAN> restore database;  - It will use most recent backup to restore.

Starting restore at 02-APR-14
Starting implicit crosscheck backup at 02-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=34 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 02-APR-14

Starting implicit crosscheck copy at 02-APR-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 02-APR-14

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLEORCL\ORADATA\ORCL\SYSTEM.DBF
restoring datafile 00002 to C:\ORACLEORCL\ORADATA\ORCL\UNDO.DBF
restoring datafile 00003 to C:\ORACLEORCL\ORADATA\ORCL\SYSAUX.DBF
restoring datafile 00004 to C:\ORACLEORCL\ORADATA\ORCL\USERS.DBF
restoring datafile 00004 to C:\ORACLEORCL\ORADATA\ORCL\DATA1.DBF
restoring datafile 00006 to C:\ORACLEORCL\ORADATA\ORCL\AHD1.DBF
restoring datafile 00007 to C:\ORACLEORCL\ORADATA\ORCL\SEE1.DBF
restoring datafile 00008 to C:\ORACLEORCL\ORADATA\ORCL\SEE2.DBF
restoring datafile 00009 to C:\ORACLEORCL\ORADATA\ORCL\AHD2.DBF
restoring datafile 00010 to C:\ORACLEORCL\ORADATA\ORCL\DATA2.DBF
restoring datafile 00011 to C:\ORACLEORCL\ORADATA\ORCL\AAA1.DBF
restoring datafile 00012 to C:\ORACLEORCL\ORADATA\ORCL\AAA2.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLEORCL\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2014_04_02\O1_MF_NNNDF_TAG20140402T102323_BKSLT7KO_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLEORCL\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2014_04_02\O1_MF_NNNDF_TAG20140402T102323_BKSLT7KO_.BKP tag=TAG20140402T102323
channel ORA_DISK_1: restore complete, elapsed time: 00:01:44
Finished restore at 02-APR-14

RMAN> exit


Recovery Manager complete.

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

- Now OPEN the database with RESETLOGS option. Please see below -

C:\Users>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 2 10:43:20 2014

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

Enter user-name: sys/xxxx as sysdba

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01489: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$database;

NAME
---------
ORCL

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

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

NOTE:  Follow the steps if receive the below error. While restoring the database.

C:\Users>rman target sys/xxxx@ORCL

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 2 10:02:42 2014

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

-------------------------------------------------------------------------------------------------------------------------
SOLUTION:

example:

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.xx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
            (UR = A)  > Add this in TNSNMES.ORA  & Try to connect again.
    )
  )

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


Thank You!
Labels:

Comments

Post a Comment

Popular posts from this blog

ORA-39014: One or more workers have prematurely exited.ORA-00018: maximum number of sessions exceeded

ERROR: I was Performing a full database import and during the import I faced the below error. ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 6 with process name "DW07" prematurely terminated ORA-31672: Worker process DW07 died unexpectedly. Job "SYSTEM"."SYS_IMPORT_FULL_04" stopped due to fatal error at 00:59:40 ORA-39014: One or more workers have prematurely exited. SOLUTION:  Run the import with fewer parallel processes, like PARALLEL=2 instead of 8. I was able to run the import successfully. NOTE 1: This errors occurs when there are less session allocation in the database. check the session,process parameters and increase them accordingly. To avoid such errors again. NOTE 2 : Note: Increasing processes parameter increases the amount of shared memory that needs to be reserved & the OS must be configured to support the larger amount of shared memory. So here we first need to increase the Memory & SG...
Post a Comment
Read more

ORA-01143: cannot disable media recovery - file 1 needs media recovery

I got a request from the client - To flashback the database to the existing restore point & disable flashback and archive log mode for database UATB. Here I came a cross error - ORA-01143. I followed the below steps. 1. SQL> select name from v$database; NAME ------------ UATB 2. SQL> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ORDER BY TIME; NAME --------- UATB_COPY Here I'm going to restore the database to the above restore point. NOTE: The flashback database restore has to be done in MOUNT stage of the database. SQL> select name from v$database; NAME --------- UATB SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area  612368384 bytes Fixed Size                  1250428 bytes Variable Size             167775108 bytes ...
2 comments
Read more