Hi,
Here I'm descrbing RMAN backups Types,Commands and Usage in Brief in Oracle 11g Database.Hope it will help one and all.
RMAN BACKUPS:
----------------
1)RMAN HOT BACKUP =>database up & running
2)RMAN COLD BACKUP =>database is not up(shutdown =>mount(now take backup).
1)RMAN HOT BACKUP: OPEN STATE (ARCHIVE_LOG MODE)
2)RMAN COLD BACKUP:MOUNT STATE (NO ARCHIVE LOG MODE)
INCREMENTAL BACKUP:
1)DIFFERENTIAL BACKUP =>BY DEFAULT INCREMENTAL BACKUP IS DIFFERENTIAL.
=>BACKUP FROM SAME OR LOWER LEVEL.
=>LEVEL 0,LEVEL 1,LEVEL 2.....
2)CUMMULATIVE BACKUP =>BACKUP FROM LEVEL 0(LOWEST LEVEL)
=>LEVEL 0,LEVEL 1,LEVEL 2.....
WHERE:
LEVEL 0=FULL BACKUP
LEVEL 1=CHANGE DATA FROM LEVEL 0
LEVEL 2=CHANGE DATA FROM LEVEL 1...
BACKUP STRATERGY:
--------------------
SUNDAY =LEVEL 0 =>INCREMENTAL (DIFFERENTIAL)=>BACKUP LEVEL 0(LOWEST LEVEL)
MONDAY =LEVEL 1 =>INCREMENTAL
TUESDAY=LEVEL 2 =>INCREMENTAL
WED =LEVEL 0 =>INCREMENTAL (CUMMULATIVE) =>BACKUP LEVEL 0(LOWEST LEVEL)
THUR =LEVEL 1 =>INCREMENTAL
FRI =LEVEL 2 =>INCREMENTAL
SAT= =LEVEL 1 =>CUMMULATIVE =>BACKUP LEVEL 1(CHANGE DATA FROM LEVEL 0)
TIMESTAMP SYMBOLS:
-------------------
%U=UNIQUE BACKUPSET NAME
%T= TIMESTAMP OF TIME OF BACKUPSET
RMAN COMMANDS:
----------------
INCREMENTAL BACKUP:
---------------------
1)DIFFERENTIAL BACKUP
2)CUMULATIVE BACKUP
1)DIFFERENTIAL BACKUP:
-- INCREMENTAL LEVEL 0
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=0 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}
-- INCREMENTAL LEVEL 1
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=1 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}
-- INCREMENTAL LEVEL 2
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
Backup incremental level=2 database tag='complete_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}
2)CUMULATIVE BACKUP:
----------------------
-- CUMMULATIVE BACKUP
RMAN> run{
2> Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
3> Backup incremental level=0 CUMULATIVE database tag='complete_backup';
4> Release channel ch1;
5> allocate channel c1 type disk;
6> copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
7> Release channel c1;
8> }
released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=73 device type=DISK
Starting backup at 24-JUL-11
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1HMI7EPO_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1IMI7EPP_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:15
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1JMI7ES5_1_1 tag=COMPLETE_BACKUP comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11
released channel: ch1
-- CUMULATIVE BACKUP LEVEL LOWEST(0)
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
BACKUP INCREMENTAL LEVEL=0 CUMULATIVE DATABASE FILESPERSET 4 tag='cumulative';
tag='complete_cummulative_backup';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}
-- CUMULATIVE BACKUP LEVEL LOWEST(1)
run{
Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
BACKUP INCREMENTAL LEVEL=1 CUMULATIVE DATABASE tag='cumulative_LEVEL1';
Release channel ch1;
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
Release channel c1;
}
RMAN> run{
2> Allocate channel ch1 type disk format 'D:\RMAN_BACKUP\ORCL_data_BACKUP2_%T_%U';
3> BACKUP INCREMENTAL LEVEL=1 CUMULATIVE DATABASE tag='cumulative_LEVEL1';
4> Release channel ch1;
5> allocate channel c1 type disk;
6> copy current controlfile to 'D:\RMAN_BACKUP\ORCL_ctrl_BACKUP2_%U_%T';
7> Release channel c1;
8> }
released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=73 device type=DISK
Starting backup at 24-JUL-11
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1NMI7F9M_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:25
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1OMI7FCC_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:35
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 24-JUL-11
channel ch1: finished piece 1 at 24-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1PMI7FDF_1_1 tag=CUMULATIVE_LEVEL1 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11
released channel: ch1
allocated channel: c1
channel c1: SID=73 device type=DISK
Starting backup at 24-JUL-11
channel c1: starting datafile copy
copying current control file
output file name=D:\RMAN_BACKUP\ORCL_CTRL_BACKUP2_CF_D-ORCL_ID-1280115002_1QMI7FDK_20110724 tag=TAG20110724T055044 R
D=31 STAMP=757317045
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 24-JUL-11
released channel: c1
ARCHIVE LOG DELETION POLICY FOR A DATABASE:
----------------------------------------------
run {
allocate channel for maintenance device type disk;
delete archivelog until time 'sysdate -5';
}
I do backups from the primary database to a local drive and have been puzzled
how to delete the standby archive logs after they ship.
The ‘obvious’ solution is the documented feature in RMAN:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
COMPRESSION LEVEL ORACLE 11g:
----------------------------------
basic =DEFAULT
none =Not recomended
medium = license
high =license
Drawbacks:
------------
medium =>consume hight system resource
high =>consume high system resource
II)RMAN COLD BACKUP:
----------------------
=>NOARCHIVE LOG MODE
=>MOUNT STATE
=> DEVELOPMENT => MUCH DISK SPACE IS NOT THERE
=>DOWNTIME TOLERABLE..
run_orcl.txt:
-----------------------------------------------------
*SNAPSHOT CONTROLFILE:RMAN USES FOR BACKUP OF CONTROL FILE.
Configure setting in RMAN :
----------------------------
We can use configure command to change any setting in RMAN.I would definetly like to outside my script like below.
$rman target / catalog rman/rman@catdb
RMAN>CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
rman_cold.txt:
--------------
run{
shutdown immediate;
startup mount;
allocate channel ch1 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET1_%U_%T' maxpiecesize 5G;
allocate channel ch2 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET2_%U_%T';
backup database TAG='ORCL_BACKUP_WEEKLY';
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\TESTDB_ctrl_%U_%T';
Release channel c1;
configure retention policy to recovery window of 7 days;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
CROSSCHECK BACKUP;
release channel ch1;
release channel ch2;
alter database open;
}
rman_orcl.bat:
---------------
-- orcl DB RMAN COLD BACKUP
set ORACLE_SID=orcl
set ORACLE_HOME=D:\app\RafiAlvi\product\11.2.0\dbhome_1
set ORACLE_BASE=D:\app\RafiAlvi\
rman target sys/orcldba @D:\RMAN_BACKUP_SCRIPTS\run_ORCL.txt log=D:\RMAN_BACKUP_SCRIPTS\LOGS\rman_cold_orcl_24july_cum.log
Note: In Unix environment use export for setting the enviromnmet and Write shell script 'rman_orcl.sh',rest all script is almost same and can fit as per our path in Unix environment.
RMAN BACKUP DETAILS Views:
---------------------------------
We can use Dictionary Views for checking the status of rman backups.
1)USE AFTER SCHEDULING RMAN BACKUP:
SELECT START_TIME||' '||END_TIME||' '||STATUS FROM V$RMAN_BACKUP_JOB_DETAILS;
2)USE TO CHECK BYYES PROCESSED IN RMAN BACKUP:
SELECT SID||' '||STATUS||' '||MBYTES_PROCESSED||' '||START_TIME||' '||END_TIME
FROM V$RMAN_STATUS;
3)USE TO CHECK SESSION AND RMAN BACKUP OUTPUT:
SELECT SID||' '||RECID||' '||OUTPUT||' '||SESSION_STAMP
FROM V$RMAN_OUTPUT;
Note:On Unix terminal,It will be very much useful to check the process in order to check the status by using 'ps' command.
$ps -eaf|grep rman
Hope it helps...
Enjoy Oracle learning.
Best regards,
Rafi.
Thursday, July 28, 2011
Monday, July 25, 2011
RMAN Recovery Catalog Database Creation and Configuration
Hi,
RMAN Recovery Catalog is an excellent way of keeping our backup safe.Since backup is the medicine of Database in case of failure or Data loss,so we don't want to keep the medicine and poison in the same bottle(which will be the case when target Database 'Controlfile' will be using for keeping the information of Backup.If the 'Controlfile' is corrupted or deleted(usually it is multiplexed),but still 'Controlfile' will be acting as poison and medicine.Hence Best option will be to use the 'Recovery Catalog'(Other Database to keep the RMAN metadata) where our backup can be safe.
Below are some steps for Creating and Configuring Recovery Catalog for our Database which might be production,test or Development:
Step 1:Create a new database for RMAN – Recovery catalog database->'CATDB' Database created using dbca.
Note: We can create a small database with minimal sizes of tablespaces and others, and we can name the database as CATDB for naming convention and to avoid the confusion between our production and rman databases.
Step 2:Create a new tablespace in the new database (CATDB)
$ sqlplus /nolog
CONNECT SYS/welcome@catdb AS SYSDBA;
CREATE TABLESPACE rman
DATAFILE 'D:\rafi\catdb\rman\rman01.dbf' size 200m;
Step 3:Create the Recovery Catalog Owner in the new database (CATDB)
CREATE USER rman IDENTIFIED BY rman
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;
Step 4:Grant the necessary privileges to the schema owner
SQL> GRANT recovery_catalog_owner TO rman;
Here the role "RECOVERY_CATALOG_OWNER" provides the user with all privileges required to maintain and query the recovery catalog.RECOVERY_CATALOG_OWNER role has the CREATE_SESSION privileges so need to grant separately.
Step 5:Creating the Recovery Catalog
Connect to the database which will contain the catalog as the catalog owner.
For example:'rman' user is catalog owner in our example.
On Linux(UNIX):
------------------
Run the 'CREATE CATALOG' command to create the catalog
$ rman target / catalog rman/rman@catdb
RMAN> CREATE CATALOG;
recovery catalog created
On Windows:
--------------------
C:\Windows\system32>rman target / catalog rman/rman@catdb
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 21 00:44:02 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> CREATE CATALOG;
recovery catalog created
Step 6:Registering a Database in the Recovery Catalog
Connect to the target database and recovery catalog database.
$ export ORACLE_SID=ORCL
$ rman target / catalog rman/rman@catdb
Recovery Manager: Release 11.2.0.1.0 - Production on Sun July 1 14:25:30 2007
Copyright (c) 1982, 2010, Oracle. All rights reserved.
connected to target database: ORCL (DBID=3677528376)
connected to recovery catalog database
windows:
-------------
C:\Windows\system32>set ORACLE_SID=ORCL
C:\Windows\system32> rman target / catalog rman/rman@catdb
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 21 01:03:19 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1280115002)
connected to recovery catalog database
Step 7:Register and Verify after connection
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
On Windows (OR) Linux Environment:
-------------------------------
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Register the target Database using 'REGISTER DATABASE' command as seen above.
Make sure that the registration is successful by running REPORT SCHEMA:
RMAN> REPORT SCHEMA;
Report of database schema
RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 690 SYSTEM YES D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
2 570 SYSAUX NO D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
3 55 UNDOTBS1 YES D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
4 6144 USERS NO D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
5 100 EXAMPLE NO D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 D:\APP\RAFIALVI\ORADATA\ORCL\TEMP01.DBF
Note:
In Windows:
---------------
For Connecting to recovery catalog Database 'CATDB' with rman for Target Database 'ORCL':
C:\Windows\system32>set ORACLE_SID=ORCL
C:\Windows\system32>rman target / CATALOG rman/rman@CATDB
In Linux:
-----------------
For Connecting to recovery catalog Database 'CATDB' with rman for Target Database 'ORCL':
$export ORACLE_SID=ORCL
$rman target / CATALOG rman/rman@CATDB
Rest all steps are prety much similar in both environment.....
Hope it helps.
Best regards,
Rafi.
RMAN Recovery Catalog is an excellent way of keeping our backup safe.Since backup is the medicine of Database in case of failure or Data loss,so we don't want to keep the medicine and poison in the same bottle(which will be the case when target Database 'Controlfile' will be using for keeping the information of Backup.If the 'Controlfile' is corrupted or deleted(usually it is multiplexed),but still 'Controlfile' will be acting as poison and medicine.Hence Best option will be to use the 'Recovery Catalog'(Other Database to keep the RMAN metadata) where our backup can be safe.
Below are some steps for Creating and Configuring Recovery Catalog for our Database which might be production,test or Development:
Step 1:Create a new database for RMAN – Recovery catalog database->'CATDB' Database created using dbca.
Note: We can create a small database with minimal sizes of tablespaces and others, and we can name the database as CATDB for naming convention and to avoid the confusion between our production and rman databases.
Step 2:Create a new tablespace in the new database (CATDB)
$ sqlplus /nolog
CONNECT SYS/welcome@catdb AS SYSDBA;
CREATE TABLESPACE rman
DATAFILE 'D:\rafi\catdb\rman\rman01.dbf' size 200m;
Step 3:Create the Recovery Catalog Owner in the new database (CATDB)
CREATE USER rman IDENTIFIED BY rman
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;
Step 4:Grant the necessary privileges to the schema owner
SQL> GRANT recovery_catalog_owner TO rman;
Here the role "RECOVERY_CATALOG_OWNER" provides the user with all privileges required to maintain and query the recovery catalog.RECOVERY_CATALOG_OWNER role has the CREATE_SESSION privileges so need to grant separately.
Step 5:Creating the Recovery Catalog
Connect to the database which will contain the catalog as the catalog owner.
For example:'rman' user is catalog owner in our example.
On Linux(UNIX):
------------------
Run the 'CREATE CATALOG' command to create the catalog
$ rman target / catalog rman/rman@catdb
RMAN> CREATE CATALOG;
recovery catalog created
On Windows:
--------------------
C:\Windows\system32>rman target / catalog rman/rman@catdb
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 21 00:44:02 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> CREATE CATALOG;
recovery catalog created
Step 6:Registering a Database in the Recovery Catalog
Connect to the target database and recovery catalog database.
$ export ORACLE_SID=ORCL
$ rman target / catalog rman/rman@catdb
Recovery Manager: Release 11.2.0.1.0 - Production on Sun July 1 14:25:30 2007
Copyright (c) 1982, 2010, Oracle. All rights reserved.
connected to target database: ORCL (DBID=3677528376)
connected to recovery catalog database
windows:
-------------
C:\Windows\system32>set ORACLE_SID=ORCL
C:\Windows\system32> rman target / catalog rman/rman@catdb
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 21 01:03:19 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1280115002)
connected to recovery catalog database
Step 7:Register and Verify after connection
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
On Windows (OR) Linux Environment:
-------------------------------
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Register the target Database using 'REGISTER DATABASE' command as seen above.
Make sure that the registration is successful by running REPORT SCHEMA:
RMAN> REPORT SCHEMA;
Report of database schema
RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 690 SYSTEM YES D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
2 570 SYSAUX NO D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
3 55 UNDOTBS1 YES D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
4 6144 USERS NO D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
5 100 EXAMPLE NO D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 D:\APP\RAFIALVI\ORADATA\ORCL\TEMP01.DBF
Note:
In Windows:
---------------
For Connecting to recovery catalog Database 'CATDB' with rman for Target Database 'ORCL':
C:\Windows\system32>set ORACLE_SID=ORCL
C:\Windows\system32>rman target / CATALOG rman/rman@CATDB
In Linux:
-----------------
For Connecting to recovery catalog Database 'CATDB' with rman for Target Database 'ORCL':
$export ORACLE_SID=ORCL
$rman target / CATALOG rman/rman@CATDB
Rest all steps are prety much similar in both environment.....
Hope it helps.
Best regards,
Rafi.
Recover Database from rman cold backup
Hi,
If we have the rman cold backup we can recover the Database upto that point we have the backup.Below is our experiment to prove this point.
Recovery of Database from complete loss of all files:
---------------------------------------------------------
Our Database is in noarchive log mode as verified below:
SQL> ARCHIVE LOG LIST
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination D:\app\RafiAlvi\product\11.2.0\dbhome_1\RDBMS
Oldest online log sequence 1
Current log sequence 2
k
I'm having rman cold backup of all database files and image copy of controlfile.
Below are our scripts which I used for taking cold backup:
run_ORCL.txt:
-----------------
run{
shutdown immediate;
startup mount;
allocate channel ch1 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET1_%U_%T' maxpiecesize 5G;
allocate channel ch2 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET2_%U_%T';
backup database TAG='ORCL_BACKUP_WEEKLY';
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\orcl_ctrl_%U_%T';
Release channel c1;
configure retention policy to recovery window of 7 days;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
CROSSCHECK BACKUP;
release channel ch1;
release channel ch2;
alter database open;
}
rman_coldb_backup.bat:
------------------------
-- orcl DB RMAN COLD BACKUP
set ORACLE_SID=orcl
set ORACLE_HOME=D:\app\RafiAlvi\product\11.2.0\dbhome_1
set ORACLE_BASE=D:\app\RafiAlvi\
rman target sys/orcldba @D:\RMAN_BACKUP_SCRIPTS\run_ORCL.txt log=D:\RMAN_BACKUP_SCRIPTS\LOGS\rman_cold_orcl_25july_cold.log
rman_cold_orcl_25july_cold.log:
--------------------------------
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 25 09:04:41 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1280115002, not open)
RMAN> run{
2> shutdown immediate;
3> startup mount;
4> allocate channel ch1 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET1_%U_%T' maxpiecesize 5G;
5> allocate channel ch2 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET2_%U_%T';
6> backup database TAG='ORCL_BACKUP_WEEKLY';
7> allocate channel c1 type disk;
8> copy current controlfile to 'D:\RMAN_BACKUP\orcl_ctrl_%U_%T';
9> Release channel c1;
10> configure retention policy to recovery window of 7 days;
11> CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
12> CROSSCHECK BACKUP;
13> release channel ch1;
14> release channel ch2;
15> alter database open;
16> }
17>
using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
allocated channel: ch1
channel ch1: SID=134 device type=DISK
allocated channel: ch2
channel ch2: SID=5 device type=DISK
Starting backup at 25-JUL-11
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
input datafile file number=00005 name=D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ch1: starting piece 1 at 25-JUL-11
channel ch2: starting full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
channel ch2: starting piece 1 at 25-JUL-11
channel ch1: finished piece 1 at 25-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_21MIAF62_1_1_20110725 tag=ORCL_BACKUP_WEEKLY comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:11
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 25-JUL-11
channel ch1: finished piece 1 at 25-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_23MIAF6E_1_1_20110725 tag=ORCL_BACKUP_WEEKLY comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:08
channel ch2: finished piece 1 at 25-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_22MIAF62_1_1_20110725 tag=ORCL_BACKUP_WEEKLY comment=NONE
channel ch2: backup set complete, elapsed time: 00:01:21
Finished backup at 25-JUL-11
allocated channel: c1
channel c1: SID=48 device type=DISK
Starting backup at 25-JUL-11
channel ch1: starting datafile copy
copying current control file
output file name=D:\RMAN_BACKUP\ORCL_CTRL_CF_D-ORCL_ID-1280115002_24MIAF8O_20110725 tag=TAG20110725T090632 RECID=32 STAMP=757415193
channel ch1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 25-JUL-11
released channel: c1
old RMAN configuration parameters:
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
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\SNAPCF_ORCL.F';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
new RMAN configuration parameters are successfully stored
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_04\O1_MF_NCSNF_TAG20110604T133433_6YO5PVD8_.BKP RECID=1 STAMP=752938475
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NNNDF_TAG20110621T011243_700NMVS9_.BKP RECID=8 STAMP=754362763
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NCSNF_TAG20110621T011243_700NO02P_.BKP RECID=9 STAMP=754362800
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NNND0_TAG20110621T013343_700OV861_.BKP RECID=10 STAMP=754364024
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NCSN0_TAG20110621T013343_700OYC20_.BKP RECID=11 STAMP=754364123
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NNND1_TAG20110621T013544_700OZ19R_.BKP RECID=12 STAMP=754364145
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NCSN1_TAG20110621T013544_700P3CKP_.BKP RECID=13 STAMP=754364283
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1HMI7EPO_1_1 RECID=20 STAMP=757316409
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1IMI7EPP_1_1 RECID=21 STAMP=757316410
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1JMI7ES5_1_1 RECID=22 STAMP=757316486
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1NMI7F9M_1_1 RECID=23 STAMP=757316919
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1OMI7FCC_1_1 RECID=24 STAMP=757317004
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1PMI7FDF_1_1 RECID=25 STAMP=757317040
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_1RMI7GM4_1_1_20110724 RECID=26 STAMP=757318341
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_1TMI7GMD_1_1_20110724 RECID=27 STAMP=757318368
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_1UMI7GN7_1_1_20110724 RECID=28 STAMP=757318377
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_1SMI7GM5_1_1_20110724 RECID=29 STAMP=757318347
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_21MIAF62_1_1_20110725 RECID=30 STAMP=757415106
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_23MIAF6E_1_1_20110725 RECID=31 STAMP=757415136
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_22MIAF62_1_1_20110725 RECID=32 STAMP=757415110
Crosschecked 20 objects
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_20110618_04MF7PSK_1_1 RECID=2 STAMP=754182037
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_20110618_05MF7PVA_1_1 RECID=3 STAMP=754182123
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_07MF7TM1_1_1_20110619 RECID=4 STAMP=754185922
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_09MF7TM4_1_1_20110619 RECID=5 STAMP=754185933
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_0AMF7TML_1_1_20110619 RECID=6 STAMP=754185943
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_08MF7TM2_1_1_20110619 RECID=7 STAMP=754185924
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0LMI4OJ5_1_1 RECID=14 STAMP=757228134
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0MMI4OM5_1_1 RECID=15 STAMP=757228232
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0OMI4PES_1_1 RECID=16 STAMP=757229020
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0PMI4PJ3_1_1 RECID=17 STAMP=757229156
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0RMI4QET_1_1 RECID=18 STAMP=757230046
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0SMI4QJ5_1_1 RECID=19 STAMP=757230182
Crosschecked 12 objects
released channel: ch1
released channel: ch2
database opened
Recovery Manager complete.
We are dropping our Database and assuming that all our Database files are lost
or corrupted including the controlfile.I'm including that step also.
Dropping Database:
---------------------
We have to drop Database in mount restrict mode as given below:
To make sure we are dropping the correct Database,please make sure we do the below steps:
C:\Users\RafiAlvi>set ORACLE_SID=ORCL
C:\Users\RafiAlvi>sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 25 09:14:41 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
ORCL
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount restrict
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
Database mounted.
SQL> drop database;
Database dropped.
Now I will try to connect the Database,
C:\Users\RafiAlvi>set ORACLE_SID=ORCL
C:\Users\RafiAlvi>sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 25 09:18:12 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
Now,Restore and recovery of Database can be done by following below steps:
Step 1: Startup Database with pfile:
Create one pfile in location where previous pfile was existing:
INITORCL.ora:
db_name=ORCL
Step 2:Restore the controlfile with 'RMAN':
RMAN> restore controlfile;
Starting restore at 24-JUL-11
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: copied control file copy
input file name=D:\RMAN_BACKUP\ORCL_CTRL_CF_D-ORCL_ID-1280115002_1VMI7GP2_20110724
output file name=D:\APP\RAFIALVI\PRODUCT\11.2.0\DBHOME_1\DATABASE\CTL1ORCL.ORA
Finished restore at 24-JUL-11
Step 3: Mount the Database.
C:\Users\RafiAlvi>set ORACLE_SID=ORCL
C:\Users\RafiAlvi>sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 24 08:31:24 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='D:\APP\RAFIALVI\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITORCL.ORA'
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> alter database mount;
Database altered.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
Step 4: Restore ,recover and open the Database.
RMAN> restore Database;
Starting restore at 24-JUL-11
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece D:\RMAN_BACKUP\ORCL_BK_SET1_1RMI7GM4_1_1_20110724
channel ORA_DISK_1: piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_1RMI7GM4_1_1_20110724 tag=ORCL_BACKUP_WEEKLY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: reading from backup piece D:\RMAN_BACKUP\ORCL_BK_SET2_1SMI7GM5_1_1_20110724
channel ORA_DISK_1: piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_1SMI7GM5_1_1_20110724 tag=ORCL_BACKUP_WEEKLY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 24-JUL-11
RMAN> recover database;
Starting recover at 24-JUL-11
using channel ORA_DISK_1
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/24/2011 08:55:16
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database
Step 5:0RA-38760 is because flashback Database is turned ON.
We have to turn OFF and try now,It will work.
CASE 1:IN ARCHIVE LOG MODE:
------------------------------
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
SQL> alter database flashback off;
Database altered.
SQL> alter database open;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
CASE 2:IN NOARCHIVELOG MODE
-------------------------------
RMAN> recover database;
Starting recover at 25-JUL-11
using channel ORA_DISK_1
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/25/2011 10:29:40
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-00275: media recovery has already been started
(OR)
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00275: media recovery has already been started
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select name from v$database;
NAME
---------
ORCL
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
Oracle is amazing :) BECAUSE my Database was in NOARCHIVELOG mode after recovery as I resetlogs it is converted to ARCHIVELOG MODE.
Hope we enjoyed recovery.
Best regards,
Rafi.
If we have the rman cold backup we can recover the Database upto that point we have the backup.Below is our experiment to prove this point.
Recovery of Database from complete loss of all files:
---------------------------------------------------------
Our Database is in noarchive log mode as verified below:
SQL> ARCHIVE LOG LIST
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination D:\app\RafiAlvi\product\11.2.0\dbhome_1\RDBMS
Oldest online log sequence 1
Current log sequence 2
k
I'm having rman cold backup of all database files and image copy of controlfile.
Below are our scripts which I used for taking cold backup:
run_ORCL.txt:
-----------------
run{
shutdown immediate;
startup mount;
allocate channel ch1 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET1_%U_%T' maxpiecesize 5G;
allocate channel ch2 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET2_%U_%T';
backup database TAG='ORCL_BACKUP_WEEKLY';
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\orcl_ctrl_%U_%T';
Release channel c1;
configure retention policy to recovery window of 7 days;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
CROSSCHECK BACKUP;
release channel ch1;
release channel ch2;
alter database open;
}
rman_coldb_backup.bat:
------------------------
-- orcl DB RMAN COLD BACKUP
set ORACLE_SID=orcl
set ORACLE_HOME=D:\app\RafiAlvi\product\11.2.0\dbhome_1
set ORACLE_BASE=D:\app\RafiAlvi\
rman target sys/orcldba @D:\RMAN_BACKUP_SCRIPTS\run_ORCL.txt log=D:\RMAN_BACKUP_SCRIPTS\LOGS\rman_cold_orcl_25july_cold.log
rman_cold_orcl_25july_cold.log:
--------------------------------
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 25 09:04:41 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1280115002, not open)
RMAN> run{
2> shutdown immediate;
3> startup mount;
4> allocate channel ch1 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET1_%U_%T' maxpiecesize 5G;
5> allocate channel ch2 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET2_%U_%T';
6> backup database TAG='ORCL_BACKUP_WEEKLY';
7> allocate channel c1 type disk;
8> copy current controlfile to 'D:\RMAN_BACKUP\orcl_ctrl_%U_%T';
9> Release channel c1;
10> configure retention policy to recovery window of 7 days;
11> CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
12> CROSSCHECK BACKUP;
13> release channel ch1;
14> release channel ch2;
15> alter database open;
16> }
17>
using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
allocated channel: ch1
channel ch1: SID=134 device type=DISK
allocated channel: ch2
channel ch2: SID=5 device type=DISK
Starting backup at 25-JUL-11
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
input datafile file number=00005 name=D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ch1: starting piece 1 at 25-JUL-11
channel ch2: starting full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
channel ch2: starting piece 1 at 25-JUL-11
channel ch1: finished piece 1 at 25-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_21MIAF62_1_1_20110725 tag=ORCL_BACKUP_WEEKLY comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:11
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 25-JUL-11
channel ch1: finished piece 1 at 25-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_23MIAF6E_1_1_20110725 tag=ORCL_BACKUP_WEEKLY comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:08
channel ch2: finished piece 1 at 25-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_22MIAF62_1_1_20110725 tag=ORCL_BACKUP_WEEKLY comment=NONE
channel ch2: backup set complete, elapsed time: 00:01:21
Finished backup at 25-JUL-11
allocated channel: c1
channel c1: SID=48 device type=DISK
Starting backup at 25-JUL-11
channel ch1: starting datafile copy
copying current control file
output file name=D:\RMAN_BACKUP\ORCL_CTRL_CF_D-ORCL_ID-1280115002_24MIAF8O_20110725 tag=TAG20110725T090632 RECID=32 STAMP=757415193
channel ch1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 25-JUL-11
released channel: c1
old RMAN configuration parameters:
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
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\SNAPCF_ORCL.F';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
new RMAN configuration parameters are successfully stored
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_04\O1_MF_NCSNF_TAG20110604T133433_6YO5PVD8_.BKP RECID=1 STAMP=752938475
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NNNDF_TAG20110621T011243_700NMVS9_.BKP RECID=8 STAMP=754362763
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NCSNF_TAG20110621T011243_700NO02P_.BKP RECID=9 STAMP=754362800
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NNND0_TAG20110621T013343_700OV861_.BKP RECID=10 STAMP=754364024
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NCSN0_TAG20110621T013343_700OYC20_.BKP RECID=11 STAMP=754364123
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NNND1_TAG20110621T013544_700OZ19R_.BKP RECID=12 STAMP=754364145
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NCSN1_TAG20110621T013544_700P3CKP_.BKP RECID=13 STAMP=754364283
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1HMI7EPO_1_1 RECID=20 STAMP=757316409
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1IMI7EPP_1_1 RECID=21 STAMP=757316410
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1JMI7ES5_1_1 RECID=22 STAMP=757316486
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1NMI7F9M_1_1 RECID=23 STAMP=757316919
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1OMI7FCC_1_1 RECID=24 STAMP=757317004
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1PMI7FDF_1_1 RECID=25 STAMP=757317040
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_1RMI7GM4_1_1_20110724 RECID=26 STAMP=757318341
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_1TMI7GMD_1_1_20110724 RECID=27 STAMP=757318368
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_1UMI7GN7_1_1_20110724 RECID=28 STAMP=757318377
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_1SMI7GM5_1_1_20110724 RECID=29 STAMP=757318347
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_21MIAF62_1_1_20110725 RECID=30 STAMP=757415106
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_23MIAF6E_1_1_20110725 RECID=31 STAMP=757415136
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_22MIAF62_1_1_20110725 RECID=32 STAMP=757415110
Crosschecked 20 objects
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_20110618_04MF7PSK_1_1 RECID=2 STAMP=754182037
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_20110618_05MF7PVA_1_1 RECID=3 STAMP=754182123
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_07MF7TM1_1_1_20110619 RECID=4 STAMP=754185922
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_09MF7TM4_1_1_20110619 RECID=5 STAMP=754185933
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_0AMF7TML_1_1_20110619 RECID=6 STAMP=754185943
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_08MF7TM2_1_1_20110619 RECID=7 STAMP=754185924
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0LMI4OJ5_1_1 RECID=14 STAMP=757228134
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0MMI4OM5_1_1 RECID=15 STAMP=757228232
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0OMI4PES_1_1 RECID=16 STAMP=757229020
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0PMI4PJ3_1_1 RECID=17 STAMP=757229156
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0RMI4QET_1_1 RECID=18 STAMP=757230046
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0SMI4QJ5_1_1 RECID=19 STAMP=757230182
Crosschecked 12 objects
released channel: ch1
released channel: ch2
database opened
Recovery Manager complete.
We are dropping our Database and assuming that all our Database files are lost
or corrupted including the controlfile.I'm including that step also.
Dropping Database:
---------------------
We have to drop Database in mount restrict mode as given below:
To make sure we are dropping the correct Database,please make sure we do the below steps:
C:\Users\RafiAlvi>set ORACLE_SID=ORCL
C:\Users\RafiAlvi>sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 25 09:14:41 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
ORCL
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount restrict
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
Database mounted.
SQL> drop database;
Database dropped.
Now I will try to connect the Database,
C:\Users\RafiAlvi>set ORACLE_SID=ORCL
C:\Users\RafiAlvi>sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 25 09:18:12 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
Now,Restore and recovery of Database can be done by following below steps:
Step 1: Startup Database with pfile:
Create one pfile in location where previous pfile was existing:
INITORCL.ora:
db_name=ORCL
Step 2:Restore the controlfile with 'RMAN':
RMAN> restore controlfile;
Starting restore at 24-JUL-11
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: copied control file copy
input file name=D:\RMAN_BACKUP\ORCL_CTRL_CF_D-ORCL_ID-1280115002_1VMI7GP2_20110724
output file name=D:\APP\RAFIALVI\PRODUCT\11.2.0\DBHOME_1\DATABASE\CTL1ORCL.ORA
Finished restore at 24-JUL-11
Step 3: Mount the Database.
C:\Users\RafiAlvi>set ORACLE_SID=ORCL
C:\Users\RafiAlvi>sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 24 08:31:24 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='D:\APP\RAFIALVI\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITORCL.ORA'
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> alter database mount;
Database altered.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
Step 4: Restore ,recover and open the Database.
RMAN> restore Database;
Starting restore at 24-JUL-11
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece D:\RMAN_BACKUP\ORCL_BK_SET1_1RMI7GM4_1_1_20110724
channel ORA_DISK_1: piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_1RMI7GM4_1_1_20110724 tag=ORCL_BACKUP_WEEKLY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: reading from backup piece D:\RMAN_BACKUP\ORCL_BK_SET2_1SMI7GM5_1_1_20110724
channel ORA_DISK_1: piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_1SMI7GM5_1_1_20110724 tag=ORCL_BACKUP_WEEKLY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 24-JUL-11
RMAN> recover database;
Starting recover at 24-JUL-11
using channel ORA_DISK_1
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/24/2011 08:55:16
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database
Step 5:0RA-38760 is because flashback Database is turned ON.
We have to turn OFF and try now,It will work.
CASE 1:IN ARCHIVE LOG MODE:
------------------------------
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
SQL> alter database flashback off;
Database altered.
SQL> alter database open;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
CASE 2:IN NOARCHIVELOG MODE
-------------------------------
RMAN> recover database;
Starting recover at 25-JUL-11
using channel ORA_DISK_1
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/25/2011 10:29:40
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-00275: media recovery has already been started
(OR)
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00275: media recovery has already been started
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select name from v$database;
NAME
---------
ORCL
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
Oracle is amazing :) BECAUSE my Database was in NOARCHIVELOG mode after recovery as I resetlogs it is converted to ARCHIVELOG MODE.
Hope we enjoyed recovery.
Best regards,
Rafi.
Sunday, July 17, 2011
Resolving ORA-31626,ORA-31633 while running Datapump job
Resolving ORA-31626,ORA-31633 while running Datapump job:
-------------------------------------------------------------
When I check my expdp logs(Datapump backup logs) and backup for one of my development Database I found both the logs and backup for the previous day missing,than I checked the cron job log and found the job actually ran the previous day.After some thinking I decided to run the expdp job(Datapump job) once again manually with the help of one my favourite command 'nohup' as follows:
$nohup sh expdp_FULL_DB_DEVDB.sh>a.out&
In the a.out(log file for nohup job used for running expdp job) I found the below error:
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.FULL_EXPDP_DEVDB"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-00955: name is already used by an existing object
After some research,I found the cause of this error as given below:
Cause: This error occured because Job creation failed because a Master Table(A table created when a Datapump job is started and dropped when a Datapump job is completed) and its indexes could not be created,most commonly due to the pre-existance of a table with the same name (job name) in the user schema
Solution: I decided to Change the JOB_NAME in the expdp script and run the expdp script once again with the help of the 'nohup' command again as given below:
$vi expdp_FULL_DB_DEVDB.sh
JOB_NAME=EXPD_JOB_NEW_NAME
:wq
$nohup sh expdp_FULL_DB_DEVDB.sh>a.out&
This time the 'nohup' command ran successfully,I checked the 'expdp' job it started successfully and the log file also looks fine.
$ps -eaf|grep expdp
=>Datapump(expdp) job found running
$tail -f expdp-18072011_095042.log
=>show running log file and I've verified here that expdp job is running fine and completed successfully.
After some time expdp job completed successfully,My only concern is next time this issue should not occur for this I'm not getting any clue if you have any please reply to this post.
Hope it helps...
Best regards,
Rafi.
-------------------------------------------------------------
When I check my expdp logs(Datapump backup logs) and backup for one of my development Database I found both the logs and backup for the previous day missing,than I checked the cron job log and found the job actually ran the previous day.After some thinking I decided to run the expdp job(Datapump job) once again manually with the help of one my favourite command 'nohup' as follows:
$nohup sh expdp_FULL_DB_DEVDB.sh>a.out&
In the a.out(log file for nohup job used for running expdp job) I found the below error:
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.FULL_EXPDP_DEVDB"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-00955: name is already used by an existing object
After some research,I found the cause of this error as given below:
Cause: This error occured because Job creation failed because a Master Table(A table created when a Datapump job is started and dropped when a Datapump job is completed) and its indexes could not be created,most commonly due to the pre-existance of a table with the same name (job name) in the user schema
Solution: I decided to Change the JOB_NAME in the expdp script and run the expdp script once again with the help of the 'nohup' command again as given below:
$vi expdp_FULL_DB_DEVDB.sh
JOB_NAME=EXPD_JOB_NEW_NAME
:wq
$nohup sh expdp_FULL_DB_DEVDB.sh>a.out&
This time the 'nohup' command ran successfully,I checked the 'expdp' job it started successfully and the log file also looks fine.
$ps -eaf|grep expdp
=>Datapump(expdp) job found running
$tail -f expdp-18072011_095042.log
=>show running log file and I've verified here that expdp job is running fine and completed successfully.
After some time expdp job completed successfully,My only concern is next time this issue should not occur for this I'm not getting any clue if you have any please reply to this post.
Hope it helps...
Best regards,
Rafi.
Wednesday, July 13, 2011
Resolving DRG-10502 and ORA-06512
Hi,
When I checked my Datapump export backup(logical backup) log file,I saw the below error message,In the below post I'm explaining the cause and the posible solution to get rid of this error in our next Datapump export backup.Remember,we need
to check our log files regularly for resolving such errors and to make sure our backup is complete error free as this is very much vital for DBA to avoid any future issues.
Error message:
ORA-39127: unexpected error from call to local_str := SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('WWV_FLOW_OH_IDX','APEX_040000','TEXTINDEXMETHODS','CTXSYS',11.02.00.00.00,newblock,0)
ORA-20000: Oracle Text error:
DRG-10502: index APEX_040000"."WWV_FLOW_OH_IDX does not exist
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 270
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 6300
Cause:
We got this error because,In my 11g rel2 database Because of the below reason:
The errors are thrown because the domain index has status FAILED, visible in USER_/DBA_INDEXES view, and no entry exists in Text data dictionary, ctxsys schema.Only metadata of valid domain indexes are exported.
I tried to rebuild the index but still the same error thrown.So finally decided to
drop it.
SQL> select name from v$database;
NAME
---------
TESTDB
SQL> select index_name, status, domidx_status, domidx_opstatus
from dba_indexes
where index_type = 'DOMAIN'
and domidx_opstatus = 'FAILED'; 2 3 4
INDEX_NAME STATUS DOMIDX_STATU DOMIDX
------------------------------ -------- ------------ ------
WWV_FLOW_OH_IDX VALID VALID FAILED
SQL> ALTER INDEX APEX_040000.WWV_FLOW_OH_IDX REBUILD ONLINE;
ALTER INDEX APEX_040000.WWV_FLOW_OH_IDX REBUILD ONLINE
*
ERROR at line 1:
ORA-29858: error occurred in the execution of ODCIINDEXALTER routine
ORA-20000: Oracle Text error:
DRG-10561: index WWV_FLOW_OH_IDX is not valid for requested operation
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 614
Solution:
To implement the solution, please execute the following steps:
* if the indexes are required in your application drop and re-create them
* if the indexes are not used by your application then drop them
Resolution:
I have drop the index as I know it is not used by our application and one valid reason i.e ctxsys:The owner of Oracle text (formerly: interMedia text) and not used by our application.
Note: Before dropping WWV_FLOW_OH_IDX,please make sure it is not used by your application or else drop and recreate.
SQL> DROP INDEX APEX_040000.WWV_FLOW_OH_IDX;
Index dropped.
SQL> select index_name, status, domidx_status, domidx_opstatus
from dba_indexes
where index_type = 'DOMAIN'
and domidx_opstatus = 'FAILED'; 2 3 4
no rows selected
References:MY ORACLE SUPPORT,ORACLE FORUM
Hope it helps...
Best regards,
Rafi.
When I checked my Datapump export backup(logical backup) log file,I saw the below error message,In the below post I'm explaining the cause and the posible solution to get rid of this error in our next Datapump export backup.Remember,we need
to check our log files regularly for resolving such errors and to make sure our backup is complete error free as this is very much vital for DBA to avoid any future issues.
Error message:
ORA-39127: unexpected error from call to local_str := SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('WWV_FLOW_OH_IDX','APEX_040000','TEXTINDEXMETHODS','CTXSYS',11.02.00.00.00,newblock,0)
ORA-20000: Oracle Text error:
DRG-10502: index APEX_040000"."WWV_FLOW_OH_IDX does not exist
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 270
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 6300
Cause:
We got this error because,In my 11g rel2 database Because of the below reason:
The errors are thrown because the domain index has status FAILED, visible in USER_/DBA_INDEXES view, and no entry exists in Text data dictionary, ctxsys schema.Only metadata of valid domain indexes are exported.
I tried to rebuild the index but still the same error thrown.So finally decided to
drop it.
SQL> select name from v$database;
NAME
---------
TESTDB
SQL> select index_name, status, domidx_status, domidx_opstatus
from dba_indexes
where index_type = 'DOMAIN'
and domidx_opstatus = 'FAILED'; 2 3 4
INDEX_NAME STATUS DOMIDX_STATU DOMIDX
------------------------------ -------- ------------ ------
WWV_FLOW_OH_IDX VALID VALID FAILED
SQL> ALTER INDEX APEX_040000.WWV_FLOW_OH_IDX REBUILD ONLINE;
ALTER INDEX APEX_040000.WWV_FLOW_OH_IDX REBUILD ONLINE
*
ERROR at line 1:
ORA-29858: error occurred in the execution of ODCIINDEXALTER routine
ORA-20000: Oracle Text error:
DRG-10561: index WWV_FLOW_OH_IDX is not valid for requested operation
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 614
Solution:
To implement the solution, please execute the following steps:
* if the indexes are required in your application drop and re-create them
* if the indexes are not used by your application then drop them
Resolution:
I have drop the index as I know it is not used by our application and one valid reason i.e ctxsys:The owner of Oracle text (formerly: interMedia text) and not used by our application.
Note: Before dropping WWV_FLOW_OH_IDX,please make sure it is not used by your application or else drop and recreate.
SQL> DROP INDEX APEX_040000.WWV_FLOW_OH_IDX;
Index dropped.
SQL> select index_name, status, domidx_status, domidx_opstatus
from dba_indexes
where index_type = 'DOMAIN'
and domidx_opstatus = 'FAILED'; 2 3 4
no rows selected
References:MY ORACLE SUPPORT,ORACLE FORUM
Hope it helps...
Best regards,
Rafi.
Wednesday, July 6, 2011
Resolving UDE-12545 and ORA-12545 error
Hi,
Oracle networking errors can test our patience some times.Below is one such error.Changing the host or domain name of a server can lead to UDE-12545 and
ORA-12545 error.Below I'm discussing the cause of this error,possible ways to diagnose and resolve this error in a very simple manner.
Error message:
UDE-12545: operation generated ORACLE error 12545
ORA-12545: Connect failed because target host or object does not exist
Diagnosis:
We can solve this error as follows:
1)Check the listener is up & running
[oracle@node1 Backup_Scipts]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-JUL-2011 10:55:48
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1.in.company.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 02-JUL-2011 11:00:36
Uptime 3 days 23 hr. 55 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle11g/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/oracle11g/diag/tnslsnr/node1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.in.company.com)(PORT=1521)))
Services Summary...
Service "TESTDB" has 1 instance(s).
Instance "TESTDB", status UNKNOWN, has 1 handler(s) for this service...
Service "TESTDB2" has 1 instance(s).
Instance "TESTDB2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
2)Use the tnsping to check the connection string(TNS entry) for Database is working fine or not,for checking network connectivity:
Here we will get the error as given below:
[oracle@node1 Backup_Scipts]$ tnsping TESTDB
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 06-JUL-2011 10:55:56
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/oracle11g/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node1.apac.company.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDB)))
TNS-12545: Connect failed because target host or object does not exist
Note:Here,TESTDB is connection string(tns entry) present in tnsnames.ora file.
Resolving:
Now,We have to check our hostname and verify it with the connection string present in the tnsnames.ora file.
Verify the host details and connection string details and validate:
[oracle@node1 Backup_Scipts]$ hostname
node1.in.company.com
As we noticed we need to change the domain name from 'apac' to 'in' in the connetion string present
in tnsnames.ora file and save it and than try 'tnsping'
[oracle@node1 admin]$ tnsping TESTDB
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 06-JUL-2011 10:57:58
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/oracle11g/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node1.in.company.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDB)))
OK (10 msec)
Now,we can perform the tasks related to rman,datapump or other tasks dealing with the connection string it will work fine.
Hope it helps.
Best regards,
Rafi.
Oracle networking errors can test our patience some times.Below is one such error.Changing the host or domain name of a server can lead to UDE-12545 and
ORA-12545 error.Below I'm discussing the cause of this error,possible ways to diagnose and resolve this error in a very simple manner.
Error message:
UDE-12545: operation generated ORACLE error 12545
ORA-12545: Connect failed because target host or object does not exist
Diagnosis:
We can solve this error as follows:
1)Check the listener is up & running
[oracle@node1 Backup_Scipts]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-JUL-2011 10:55:48
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1.in.company.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 02-JUL-2011 11:00:36
Uptime 3 days 23 hr. 55 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle11g/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/oracle11g/diag/tnslsnr/node1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.in.company.com)(PORT=1521)))
Services Summary...
Service "TESTDB" has 1 instance(s).
Instance "TESTDB", status UNKNOWN, has 1 handler(s) for this service...
Service "TESTDB2" has 1 instance(s).
Instance "TESTDB2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
2)Use the tnsping to check the connection string(TNS entry) for Database is working fine or not,for checking network connectivity:
Here we will get the error as given below:
[oracle@node1 Backup_Scipts]$ tnsping TESTDB
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 06-JUL-2011 10:55:56
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/oracle11g/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node1.apac.company.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDB)))
TNS-12545: Connect failed because target host or object does not exist
Note:Here,TESTDB is connection string(tns entry) present in tnsnames.ora file.
Resolving:
Now,We have to check our hostname and verify it with the connection string present in the tnsnames.ora file.
Verify the host details and connection string details and validate:
[oracle@node1 Backup_Scipts]$ hostname
node1.in.company.com
As we noticed we need to change the domain name from 'apac' to 'in' in the connetion string present
in tnsnames.ora file and save it and than try 'tnsping'
[oracle@node1 admin]$ tnsping TESTDB
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 06-JUL-2011 10:57:58
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/oracle11g/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node1.in.company.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDB)))
OK (10 msec)
Now,we can perform the tasks related to rman,datapump or other tasks dealing with the connection string it will work fine.
Hope it helps.
Best regards,
Rafi.
Subscribe to:
Posts (Atom)