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!
Comments
Post a Comment