Category Archives: RMAN

Oracle, Oracle 10g, oracle 9i, RMAN

ORACLE RMAN Full Database Backup

Leave a comment

Since the release of Oracle 8, Oracle has provided the Recovery Manger utility (RMAN) to perform database backup and recovery operations for Oracle databases. The utility has provided vast improvements over previous backup procedures. While there have been third party products to backup Oracle and other database systems, RMAN is a proprietary utility provided by Oracle and is freely available with your standard Oracle license. In this article we will review the procedures to perform a full database backup with the RMAN utility.

NOTE: This article assumes you have setup a recovery catalog. See article ‘Oracle 10g RMAN database Setup’ at http:/www.relidb.com/?p=80

1. Logon to your Oracle database server as the Oracle software owner.
2. Set your oracle SID to the database you wish to backup in this example the database is orcl10g.

mylinux: >set ORACLE_SID=orcl10g
mylinux: >

3. Execute the utility command RMAN.

Mylinux:> rman

Recovery Manager: Release 10.2.0.1.0 – Production on Mon Aug 24 19:16:23 2009

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

RMAN>

4. Connect to your target database and recovery catalog.

RMAN> connect target system/oracle
connected to target database: ORCL10G (DBID=879290681)
RMAN> connect catalog rman/rman@rman
connected to recovery catalog database
RMAN>

5. Create your catalog with ‘CREATE CATALOG’ command.

RMAN> create catalog
recovery catalog created
RMAN>

6. Register the database with the ‘REGISTER DATABASE’ command.

RMAN> register database
2> ;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

7. Begin the backup of your Oracle database with the ‘BACKUP FULL’ command.

RMAN> backup full tag BACKUP_DB_ORCL format ‘/U01/ORACLE/ORADATA/ORCL10G/%d_%t_%p_%s_%c_%u.dbf’ d
atabase;

Starting backup at 24-AUG-09
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/U01/ORACLE/ORADATA/ORCL10G/SYSTEM01.
DBF
input datafile fno=00003 name=/U01/ORACLE/ORADATA/ORCL10G/SYSAUX01.
DBF
input datafile fno=00005 name=/U01/ORACLE/ORADATA/ORCL10G/EXAMPLE01
.DBF
input datafile fno=00002 name=/U01/ORACLE/ORADATA/ORCL10G/UNDOTBS01
.DBF
input datafile fno=00006 name=/U01/ORACLE/ORADATA/ORCL10G/USERDATA1
.DBF
input datafile fno=00004 name=/U01/ORACLE/ORADATA/ORCL10G/USERS01.D
BF
input datafile fno=00007 name=/U01/ORACLE/ORADATA/ORCL10G/TEST_DATA
01.DBF
input datafile fno=00008 name=/U01/ORACLE/ORADATA/ORCL10G/DATA02.DB
F
channel ORA_DISK_1: starting piece 1 at 24-AUG-09
channel ORA_DISK_1: finished piece 1 at 24-AUG-09
piece handle=/U01/ORACLE/ORADATA/ORCL10G/ORCL10G_695765204_1_3_1_03KNH26K.DBF tag=BACKUP_DB_ORCL c
omment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 24-AUG-09
channel ORA_DISK_1: finished piece 1 at 24-AUG-09
piece handle=/U01/ORACLE/ORADATA/ORCL10G/ORCL10G_695765270_1_4_1_04KNH28M.DBF tag=BACKUP_DB_ORCL c
omment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-AUG-09

RMAN>

8. Begin the backup of your controlfiles with the ‘BACKUP CURRENT CONTROLFILE’ command.

RMAN> backup current controlfile format ‘/U01/ORACLE/ORADATA/ORCL10G/%d_%t_%p_%s_%c_%u.ctl’;

Starting backup at 24-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 24-AUG-09
channel ORA_DISK_1: finished piece 1 at 24-AUG-09
piece handle=/U01/ORACLE/ORADATA/ORCL10G/ORCL10G_695765322_1_5_1_05KNH2AA.CTL tag=TAG20090824T2008
42 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 24-AUG-09

RMAN>

9. Crosscheck the archivelogs, alter the archivelogs to current, and backup archivelogs

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
specification does not match any archive log in the recovery catalog

RMAN> sql ‘alter system archive log current’;

sql statement: alter system archive log current

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
validation succeeded for archived log
archive log filename=/U01/ORACLE/FLASH_RECOVERY_AREA/ORCL10G/ARCHIV
ELOG/2009_08_24/O1_MF_1_15_596BXB7T_.ARC recid=1 stamp=695765354
Crosschecked 1 objects

RMAN> backup format ‘/U01/ORACLE/ORADATA/ORCL10G/%d_S%s_P%p_t%t_arch’ ( archivelog all delete inpu
t );

Starting backup at 24-AUG-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=15 recid=1 stamp=695765354
input archive log thread=1 sequence=16 recid=2 stamp=695765359
channel ORA_DISK_1: starting piece 1 at 24-AUG-09
channel ORA_DISK_1: finished piece 1 at 24-AUG-09
piece handle=/U01/ORACLE/ORADATA/ORCL10G/ORCL10G_S6_P1_T695765362_ARCH tag=TAG20090824T200921 comm
ent=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/U01/ORACLE/FLASH_RECOVERY_AREA/ORCL10G/ARCHIV
ELOG/2009_08_24/O1_MF_1_15_596BXB7T_.ARC recid=1 stamp=695765354
archive log filename=/U01/ORACLE/FLASH_RECOVERY_AREA/ORCL10G/ARCHIV
ELOG/2009_08_24/O1_MF_1_16_596BXGWB_.ARC recid=2 stamp=695765359
Finished backup at 24-AUG-09

RMAN>

10. That completes taking a complete database backup with the RMAN utility.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, RMAN

Recreate Oracle RDBMS User accounts

Leave a comment

With the advent of Oracle 10g Import and Export data pump utility, the need to recreate a user account from scratch has been greatly reduced. However, there are still situation when you may need the SQL to recreate a specific user within another Oracle environment or just desire to see the creation statements. The script below will generate the statements to create the user defined in the CURR variable from the current Oracle RDBMS environment.

1. First we will create a user named LJCATT with various roles and privileges.

SQL> create user LJCATT identified by LJCATT;
User created.
SQL> alter user LJCATT default tablespace USERS;
User altered.
SQL> alter user LJCATT temporary tablespace TEMP;
User altered.
SQL> alter user LJCATT profile DEFAULT;
User altered.
SQL> grant DBA to LJCATT;
Grant succeeded.
SQL> grant CREATE TABLE to LJCATT with admin option;
Grant succeeded.
SQL> grant UNLIMITED TABLESPACE to LJCATT;
Grant succeeded.
SQL> grant EXECUTE on SYS.UTL_FILE to LJCATT with grant option;
Grant succeeded.
SQL>

2. Now we will execute the following PL/SQL block to regenerate the statements to recreate the user from the data dictionary.

set serveroutput on
spool ./create_user_account.sql

declare

curr varchar2(30):= ‘LJCATT’;
v_ext varchar2(3);

begin

— Create original user definition
for user in(select * from dba_users where username = curr)
loop
dbms_output.put_line(‘create user ‘||user.username||’ identified by
‘||user.username||’;’);
dbms_output.put_line(‘alter user ‘||user.username||’ default tablespace
‘||user.default_tablespace||’;’);
dbms_output.put_line(‘alter user ‘||user.username||’ temporary tablespace
‘||user.temporary_tablespace||’;’);
dbms_output.put_line(‘alter user ‘||user.username||’ profile
‘||user.profile||’;’);
if user.account_status<>‘OPEN’
then
dbms_output.put_line(‘alter user ‘||user.username||’ account lock;’);
end if;
end loop;

— Grant all roles defined for the user.

for role in(select * from dba_role_privs where grantee=curr)
loop
if role.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ with
admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’);
end if;
end loop;

— Grant all system privileges for the user.

for sys_priv in(select * from dba_sys_privs where grantee=curr)
loop
if sys_priv.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee||’
with admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
‘||sys_priv.grantee||’;’);
end if;
end loop;

— Grant all object privileges for the user.

for tab_priv in(select * from dba_tab_privs where grantee=curr)
loop
if tab_priv.grantable = ‘YES’
then
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with
grant option;’);
else
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
end if;
end loop;
end;
/

spool off

Sample Output results:

47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
create user LJCATT identified by LJCATT;
alter user LJCATT default tablespace USERS;
alter user LJCATT temporary tablespace TEMP;
alter user LJCATT profile DEFAULT;
grant DBA to LJCATT;
grant CREATE TABLE to LJCATT with admin option;
grant UNLIMITED TABLESPACE to LJCATT;
grant EXECUTE on SYS.UTL_FILE to LJCATT with grant option;

PL/SQL procedure successfully completed.

SQL>

3. The above SQL statements can now be used against any other Oracle database to recreate the user account with the same rights which exist in this database. NOTE: There exist several dependencies in the above statements, such as: tablespaces, profiles, roles, and object privileges. For example, we need to take care that a default tablespace defined in the alter user statement truly exist in the destination database.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, RMAN

Oracle RMAN – Recovery of entire Oracle database

Leave a comment

Oracle’s Recovery Manger (RMAN), originally released in Oracle 8, is a proprietary utility used for the backup and recovery of Oracle databases. Given a good Oracle RMAN backup, A DBA can recover from any disaster which may occur to their database, to include: loss of controlfiles, loss of datafiles, corruption of datafiles, loss of the entire database and even provides the ability to clone a database. In this article we give detailed instructions on the recovery of an entire Oracle database in Oracle 9i, however these instructions will work in versions 10g and 11g.

1. Logon to the Oracle server as the Oracle software owner and set the ORACLE_SID.

[oracle@testdb02 ORCL]$ su – oracle
Password:
[oracle@testdb02 ~]$ export ORACLE_SID=ORCL
[oracle@testdb02 ~]$

2. Logon to SQL*PLUS and obtain location of all datafiles from dba_data_files and location of all control files by the show parameter control_file command.

[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:45:27 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> select file_name from dba_data_files;

FILE_NAME
——————————————————————————–
/u01/oradata/ORCL/system01.dbf
/u04/oradata/ORCL/undo01.dbf
/u05/oradata/ORCL/users01.dbf
/u05/oradata/ORCL/tools01.dbf

SQL> select file_name from dba_temp_files;

FILE_NAME
——————————————————————————–
/u05/oradata/ORCL/temp02.dbf

SQL> show parameter control_files

NAME TYPE VALUE
———————————— ———– ——————————
control_files string /u01/oradata/ORCL/control01.c
tl, /u03/oradata/ORCL/control
02.ctl, /u06/oradata/ORCL/con
trol03.ctl
SQL>

3. Navigate to each of the files listed and add the extension of _bak to the files.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
[oracle@testdb02 ORCL]$ cd /u01/oradata/ORCL/
[oracle@testdb02 ORCL]$ ls
archive control01.ctl redolog1a.dbf redolog2b.dbf system01.dbf
[oracle@testdb02 ORCL]$ mv control01.ctl control01.ctl_bak
[oracle@testdb02 ORCL]$ mv system01.dbf system01.dbf_bak
[oracle@testdb02 ORCL]$ cd /u03/oradata/ORCL/
[oracle@testdb02 ORCL]$ ls
archive control02.ctl redolog1b.dbf
[oracle@testdb02 ORCL]$ mv control02.ctl control02.ctl_bak
[oracle@testdb02 ORCL]$ cd /u04/oradata/ORCL/
[oracle@testdb02 ORCL]$ ls
undo01.dbf
[oracle@testdb02 ORCL]$ mv undo01.dbf undo01.dbf_bak
[oracle@testdb02 ORCL]$ cd /u05/oradata/ORCL/
[oracle@testdb02 ORCL]$ ls
sqlnet.log temp02.dbf tools01.dbf users01.dbf
[oracle@testdb02 ORCL]$ mv temp02.dbf temp02.dbf_bak
[oracle@testdb02 ORCL]$ mv tools01.dbf tools01.dbf_bak
[oracle@testdb02 ORCL]$ mv users01.dbf users01.dbf_bak
[oracle@testdb02 ORCL]$ cd /u06/oradata/ORCL/
[oracle@testdb02 ORCL]$ ls
archive control03.ctl redolog2a.dbf
[oracle@testdb02 ORCL]$ mv control03.ctl control03.ctl_bak
[oracle@testdb02 ORCL]$ ls
archive control03.ctl_bak redolog2a.dbf
[oracle@testdb02 ORCL]$

4. Stop the Oracle database by killing the active SMON process of the ORCL instance.

[oracle@testdb02 ORCL]$ ps -ef|grep smon
oracle 14206 1 0 21:37 ? 00:00:00 ora_smon_ORCL
oracle 15121 13009 0 21:56 pts/4 00:00:00 grep smon
[oracle@testdb02 ORCL]$ kill -9 14206
[oracle@testdb02 ORCL]$ ps -ef|grep ORCL
oracle 12023 11973 0 20:51 pts/6 00:00:00 vim ORCL_RMAN.sh
oracle 15139 13009 0 21:56 pts/4 00:00:00 grep ORCL
[oracle@testdb02 ORCL]$

5. Enter SQL*PLUS and startup the database in nomount state. You have to startup the database with nomount in order to recovery the controlfiles.

[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:58:01 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 144670496 bytes
Fixed Size 741152 bytes
Variable Size 109051904 bytes
Database Buffers 33554432 bytes
Redo Buffers 1323008 bytes
SQL>

6. Exit SQL*PLUS, enter RMAN and connect to the target database and recovery catalog.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
[oracle@testdb02 ORCL]$ RMAN

Recovery Manager: Release 9.2.0.8.0 – 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target sys/h1g0alz

connected to target database: ORCL (not mounted)

RMAN> connect catalog RMAN/RMAN@RMANDB

connected to recovery catalog database

7. Issue the restore database command, which replaces all database objects with the backup set copies.

RMAN> restore database;

Starting restore at 12-JUN-08

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/ORCL/system01.dbf
restoring datafile 00002 to /u04/oradata/ORCL/undo01.dbf
restoring datafile 00003 to /u05/oradata/ORCL/users01.dbf
restoring datafile 00004 to /u05/oradata/ORCL/tools01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u07/RMAN_BACKUP/ORCL/ORCL_657220129_1_26_1_0qjiooh1.dbf tag=BACKUP_FULL_ORCL params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 12-JUN-08

8. Issue the restore controlfile command, which replaces all controlfiles with the backup set copies.

RMAN> restore controlfile;

Starting restore at 12-JUN-08

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring controlfile
output filename=/u01/oradata/ORCL/control01.ctl
channel ORA_DISK_1: restored backup piece 1
piece handle=/u07/RMAN_BACKUP/ORCL/ORCL_657220219_1_27_1_0rjioojr.ctl tag=TAG20080612T171019 params=NULL
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/u01/oradata/ORCL/control01.ctl
output filename=/u03/oradata/ORCL/control02.ctl
output filename=/u06/oradata/ORCL/control03.ctl
Finished restore at 12-JUN-08

RMAN>

9. Exit RMAN and enter SQL*PLUS, alter the database to a mount state. The mount state will allow you to restore the database using the archive redo logs.

RMAN> exit

Recovery Manager complete.
[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 22:06:33 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> alter database mount;

10. Exit SQL*PLUS and enter RMAN utility, connect to the target database and catalog.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
[oracle@testdb02 ORCL]$ RMAN

Recovery Manager: Release 9.2.0.8.0 – 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target sys/h1g0alz

connected to target database: ORCL (DBID=1373796530)

RMAN> connect catalog RMAN/RMAN@RMANDB

connected to recovery catalog database

11. Issue the recovery database command. This will apply all archive redo logs to the datafiles and controlfiles.

RMAN> recover database;

Starting recover at 12-JUN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK

starting media recovery

archive log thread 1 sequence 28 is already on disk as file /u01/oradata/ORCL/archive/ORCL_ARCH28.arc
archive log thread 1 sequence 29 is already on disk as file /u06/oradata/ORCL/archive/ORCL_ARCH29.arc
archive log thread 1 sequence 30 is already on disk as file /u06/oradata/ORCL/redolog2a.dbf
archive log thread 1 sequence 31 is already on disk as file /u03/oradata/ORCL/redolog1b.dbf
archive log filename=/u01/oradata/ORCL/archive/ORCL_ARCH28.arc thread=1 sequence=28
archive log filename=/u06/oradata/ORCL/archive/ORCL_ARCH29.arc thread=1 sequence=29
archive log filename=/u06/oradata/ORCL/redolog2a.dbf thread=1 sequence=30
archive log filename=/u03/oradata/ORCL/redolog1b.dbf thread=1 sequence=31
media recovery complete
Finished recover at 12-JUN-08

RMAN>

12. Exit RMAN, enter SQL*PLUS and alter the database open with reset logs command. NOTE: you must perform new full database RMAN backup after you reset the logs. ANY backup taken prior will now be useless.

RMAN> exit

Recovery Manager complete.
[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 22:10:06 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> alter database open resetlogs;

Database altered.

SQL>

This completes the recovery of full database.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, RMAN

Oracle RMAN recovery of files on a lost mount point

Leave a comment

Oracle’s Recovery Manger (RMAN), originally released in Oracle 8, is a proprietary utility used for the backup and recovery of Oracle databases. Given a good Oracle RMAN backup, A DBA can recover from any disaster which may occur to their database, to include: loss of controlfiles, loss of datafiles, corruption of datafiles, loss of the entire database and even provides the ability to clone a database. In this article we give detailed instructions on the recovery of a lost mount point in Oracle 9i, however these instructions will work in versions 10g and 11g.

1. Logon to the Oracle server as the Oracle software owner and set the ORACLE_SID.

[oracle@testdb02 ORCL]$ su – oracle
Password:
[oracle@testdb02 ~]$ export ORACLE_SID=ORCL
[oracle@testdb02 ~]$

2. Logon to SQL*PLUS and obtain the location of all datafiles from the table dba_data_files. Note: There exist two data files on /u05 that can be used for this test.

[oracle@testdb02 ~]$ SQL*PLUS ‘/ as sysdba’
SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 20:03:06 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL>

SQL> select tablespace_name, file_name from dba_data_files order by tablespace_name;
TABLESPACE_NA FILE_NAME
————- ————————————————–
SYSTEM /u01/oradata/ORCL/system01.dbf
TOOLS /u05/oradata/ORCL/tools01.dbf
UNDOTBS1 /u04/oradata/ORCL/undo01.dbf
USERS /u05/oradata/ORCL/users01.dbf

SQL>

3. Exit SQL*PLUS and navigate to /u05/oradata/ORCL, create temp directory and move all files located on the mount point /u05 to the new directory. This will simulate the loss of all Oracle related files on /u05. In a real situation, you would have your SA recreate the /u05 mount point.

[oracle@testdb02 ~]$ cd /u05/oradata/ORCL
[oracle@testdb02 ORCL]$ ls
sqlnet.log temp02.dbf tools01.dbf users01.dbf
[oracle@testdb02 ORCL]$ mkdir temp
[oracle@testdb02 ORCL]$ mv tools01.dbf temp/
[oracle@testdb02 ORCL]$ mv users01.dbf temp/
[oracle@testdb02 ORCL]$ ls
sqlnet.log temp temp02.dbf
[oracle@testdb02 ORCL]$

4. Log back into SQL*PLUS and attempt to shutdown the database. You will receive an indicating that the files on mount point /u05 cannot be located.

[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:27:47 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> shutdown
ORA-01116: error in opening database file 3
ORA-01110: data file 3: ‘/u05/oradata/ORCL/users01.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>

5. Exit SQL*PLUS and force a shutdown of the database by killing the smon process.

[oracle@testdb02 ORCL]$ ps -ef|grep smon
oracle 12677 1 0 21:04 ? 00:00:00 ora_smon_ORCL
oracle 14115 13009 0 21:35 pts/4 00:00:00 grep smon
[oracle@testdb02 ORCL]$ kill -9 12677
[oracle@testdb02 ORCL]$ ps -ef|grep ORCL
oracle 12023 11973 0 20:51 pts/6 00:00:00 vim ORCL_RMAN.sh
oracle 14137 13009 0 21:36 pts/4 00:00:00 grep ORCL
[oracle@testdb02 ORCL]$

6. Logon to SQL*PLUS and perform open mount operation, so the recovery operation can be performed.

[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:37:20 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 144670496 bytes
Fixed Size 741152 bytes
Variable Size 109051904 bytes
Database Buffers 33554432 bytes
Redo Buffers 1323008 bytes
Database mounted.
SQL>

7. Exit SQL*PLUS and enter the RMAN utility connecting to the target database and recovery catalog.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
[oracle@testdb02 ORCL]$ RMAN

Recovery Manager: Release 9.2.0.8.0 – 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
RMAN> connect target sys/h1g0alz

connected to target database: ORCL (DBID=1373796530)

RMAN> connect catalog RMAN/RMAN@RMANDB

connected to recovery catalog database

RMAN>

8. Issue the restore database command, which will restore all database components.

RMAN> restore database;

Starting restore at 12-JUN-08

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/ORCL/system01.dbf
restoring datafile 00002 to /u04/oradata/ORCL/undo01.dbf
restoring datafile 00003 to /u05/oradata/ORCL/users01.dbf
restoring datafile 00004 to /u05/oradata/ORCL/tools01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u07/RMAN_BACKUP/ORCL/ORCL_657220129_1_26_1_0qjiooh1.dbf tag=BACKUP_FULL_ORCL params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 12-JUN-08

RMAN>

9. Issue the recover database command, which will apply all archive redo logs since the last backup set.

RMAN> recover database;

Starting recover at 12-JUN-08
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 28 is already on disk as file /u01/oradata/ORCL/archive/ORCL_ARCH28.arc
archive log thread 1 sequence 29 is already on disk as file /u06/oradata/ORCL/archive/ORCL_ARCH29.arc
archive log filename=/u01/oradata/ORCL/archive/ORCL_ARCH28.arc thread=1 sequence=28
media recovery complete
Finished recover at 12-JUN-08

RMAN>

10. Exit out of RMAN, enter into SQL*PLUS and open the database. Once the database has successfully opened, you know that all database files are in a consistent state.

RMAN> exit

Recovery Manager complete.
[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:43:14 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> alter database open;

Database altered.

SQL>

This completes recovery of all files on a single mount point or directory.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, RMAN

RMAN recovery of lost or corrupted tablespace datafile

Leave a comment

Oracle’s Recovery Manger (RMAN), originally released in Oracle 8, is a proprietary utility used for the backup and recovery of Oracle databases. Given a good Oracle RMAN backup, A DBA can recover from any disaster which may occur to their database, to include: loss of controlfiles, loss of datafiles, corruption of datafiles, loss of the entire database and even provides the ability to clone a database. In this article we give detailed instructions on the recovery of a lost or corrupted tablespace datafile in Oracle 9i, however these instructions will work in versions 10g and 11g.

1. Logon to the oracle server as the oracle software owner.

2. Set the ORACLE_SID and logon to sqlplus as sysdba.

[oracle@testdb02 scripts]$ su – oracle
Password:
[oracle@testdb02 ~]$ export ORACLE_SID=ORCL
[oracle@testdb02 ~]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 20:03:06 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL>

3. Query the dba_data_files table for tablespaces and datafiles. We are looking for a datafile which can be moved out of the location specified in the controlfile, resulting in tablespace corruption

SQL> select tablespace_name, file_name from dba_data_files order by tablespace_name;
TABLESPACE_NA FILE_NAME
————- ————————————————–
SYSTEM /u01/oradata/ORCL/system01.dbf
TOOLS /u05/oradata/ORCL/tools01.dbf
UNDOTBS1 /u04/oradata/ORCL/undo01.dbf
USERS /u05/oradata/ORCL/users01.dbf

SQL>

4. Shutdown the database with immediate.

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

5. Exit SQL*PLUS and move to directory /u05/oradata/ORCL. Create a temp directory and move the datafile users01.dbf into the new temp directory.

[oracle@testdb02 ~]$ cd /u05/oradata/ORCL
[oracle@testdb02 ORCL]$ ls
temp02.dbf tools01.dbf users01.dbf
[oracle@testdb02 ORCL]$
[oracle@testdb02 ORCL]$ mkdir temp
[oracle@testdb02 ORCL]$
[oracle@testdb02 ORCL]$ mv users01.dbf temp/
[oracle@testdb02 ORCL]$ ls
temp temp02.dbf tools01.dbf
[oracle@testdb02 ORCL]$

6. Log back into sqlplus and startup your Oracle instance. Note: the error received referring to datafile users01.dbf, there exists a reference to this datafile in the controlfile, however Oracle could not locate the file. This results in the tablespace USERS being in an unusable inconsistent state.

[oracle@testdb02 ORCL]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 20:48:14 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 144670496 bytes
Fixed Size 741152 bytes
Variable Size 109051904 bytes
Database Buffers 33554432 bytes
Redo Buffers 1323008 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 – see DBWR trace file
ORA-01110: data file 3: ‘/u05/oradata/ORCL/users01.dbf’

SQL>

7. To resolve the inconsistence, we must startup the RMAN utility and connect to both the target database and recovery catalog.

[oracle@testdb02 ORCL]$ rman

Recovery Manager: Release 9.2.0.8.0 – 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target sys/h1g0alz

connected to target database: ORCL (DBID=1373796530)

RMAN> connect catalog rman/rman@RMANDB

connected to recovery catalog database

RMAN>

8. From within the RMAN utility, issue the command to ‘restore tablespace ’. In this example the tablespace was USERS. The restore command replaces the tablespace’s datafiles in your database with the copy in your backup set.

RMAN> restore tablespace users;

Starting restore at 12-JUN-08

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=15 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u05/oradata/ORCL/users01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u07/RMAN_BACKUP/ORCL/ORCL_657220129_1_26_1_0qjiooh1.dbf tag=BACKUP_FULL_ORCL params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 12-JUN-08

RMAN>

9. From within the RMAN utility, issue the command ‘recover tablespace ’. The recover command applies all archive redo logs which have occurred since the last backup set.

RMAN> recover tablespace users;

Starting recover at 12-JUN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK

starting media recovery

archive log thread 1 sequence 28 is already on disk as file /u01/oradata/ORCL/archive/ORCL_ARCH28.arc
archive log thread 1 sequence 29 is already on disk as file /u06/oradata/ORCL/archive/ORCL_ARCH29.arc
archive log filename=/u01/oradata/ORCL/archive/ORCL_ARCH28.arc thread=1 sequence=28
media recovery complete
Finished recover at 12-JUN-08

RMAN>

10. Exit the RMAN utility and enter SQL*PLUS, alter the database to an open status. You know that the tablespace USERS is now in a consistent state with the controlfiles when the database opens successfully

RMAN> exit

Recovery Manager complete.
[oracle@testdb02 ORCL]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:03:11 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> alter database open;

Database altered.

SQL>

This completes successful recovery of a tablespace.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, RMAN

Oracle 10g RMAN database Setup

Leave a comment

Oracle Recovery Manager is an excellent tool for the backup and recovery of Oracle databases. It allows you to easily backup your information on scheduled bases to ensure against all forms of data loss. Additionally, it makes the process of recovering from data corruption to disk loss in a very expedient manor. The following instruction set covers the setup of an oracle database for storage of your recovery catalog and the cataloging of your first target database. This instruction set was written from a Linux installed Oracle system; however it will work on most any flavor of UNIX.

1. Logon to your linux server as the Oracle software owner.

2. Use the ./dbca command as follows to open up the Oracle Database Creation Assistant or create a database named RMAN manually. Note: This oracle database is only used to store the catalog information of backed-up databases, thus the storage and memory requirements are very low. I would not allocate more than 200MB of space and 100MB of memory to this database.

linux1# cd $ORACLE_HOME/bin
linux1#
linux1# ./dbca

3. If using dbca, follow the prompted screens to create a general purpose database.

4. If the database is created using manual methods ensure that the following Oracle scripts are executed after build.

connect sys/oracle@RMAN as sysdba
set echo off
@?/rdbms/admin/catalog
@?/rdbms/admin/catproc
@?/rdbms/admin/catrep
@?/rdbms/admin/catexp
@?/rdbms/admin/catldr
@?/rdbms/admin/dbmsutil
@?/rdbms/admin/prvtutil.plb
@?/rdbms/admin/dbmssql
@?/rdbms/admin/prvtsql.plb
@?/rdbms/admin/dbmsdefr
@?/rdbms/admin/prvtdefr.plb
@?/rdbms/admin/catqueue
@?/rdbms/admin/dbmsaqad
@?/rdbms/admin/prvtaqad.plb
@?/rdbms/admin/catcr
@?/rdbms/admin/prvtcr.plb
connect system/oracle@RMAN
@?/rdbms/admin/catdbsyn
@?/sqlplus/admin/pupbld.sql
conn sys/oracle@RMAN as sysdba
@?/rdbms/admin/utlrp

5. Logon to your newly created database and create a tablespace for the RMAN user with the following command.

create tablespace rman
datafile ‘/u05/oradata/RMAN/rman01.dbf’ size 200m
extent management local autoallocate
segment space management auto;

6. Logon to your newly created database and create the RMAN User with the following command. Then grant the RMAN user the privileges: recovery_catalog_owner, connect, and resource.

create user rman identified by rman
temporary tablespace temp
default tablespace rman quota unlimited on rman;

grant recovery_catalog_owner to rman;

grant connect,resource to rman;

7. Add the following text to the listener.ora file on the server hosting the RMAN database and ensure that an entry is listed on all database servers to be backed-up in their individual tnsnames.ora files for the newly created RMAN database.

(SID_DESC =
(ORACLE_HOME = /u01/opt/app/oracle/product/10.2.0/Db_1)
(SID_NAME = RMAN1)
)

8. Issue a stop and restart of the oracle listener for changes to take affect.

linux1# lsnrctl stop

linux1# lsnrctl start

9. Register the target database in the catalog.

10. Logon to your server and set the ORACLE_SID parameter to your target database.

linux1# echo $ORACLE_SID

linux1# ORACLE_SID=orcl
linux1# export ORACLE_SID
linux1# echo $ORACLE_SID
orcl
linux1#

11. Execute the command , to logon to the RMAN utility.

linux1# rman target / catalog rman/rman@RMAN

Recovery Manager: Release 10.2.0.2.0 – Production on Wed Sep 24 15:42:29 2008

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

connected to target database: ORCL (DBID=3051662701)
connected to recovery catalog database

RMAN>

12. Issue the command in the RMAN utility.

RMAN> create catalog

recovery catalog created

RMAN>

13. Issue the command in the RMAN utility.

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

14. NOTE: By default RMAN will attempt to register a TAPE drive in the system. If you do not have a tape drive available specify.

RMAN> CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ clear;

old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ MAXPIECESIZE 10 G;
old RMAN configuration parameters are successfully deleted
starting full resync of recovery catalog
full resync complete

This completes the creation of RMAN recovery catalog and registration of your target database.

Larry Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com