Archive

Archive for October, 2011

SQL*Loader

October 31, 2011 Leave a comment

SQL*Loader is a tool used by DBAs and developers to populate Oracle tables with data from flat files. It is possible to selectivly exclude records and transform records while they’re loaded. The load produces a log file, bad files and discard files. The control file specifies the behaviour of the SQL*Loader.

A control file contains three sections:
1st section: The first section contains global options: bindsize, rows, records to skip etc. Also, the INFILE clause determines the location of the input data.
2st section: The second section contains one or more INTO TABLE blocks.
3st section: The (optional) third section contains input data.

Image Source

INFILE

The INFILE statement specifies the file to be loaded. It also indicates whether the records are fixed length, variable length or stream format.

INTO TABLE

The into table statement defines the relationship between the fields in an Oracle table and the records in the datafile to be loaded.

APPEND

One of the options to be used if the table is non empty.

INSERT

FIELDS TERMINIATED BY

If the fields of the data that is being loaded are not fixed width, it must be specified how they’re seperated.

WHEN

When is used to selectively load data (according to a criteria stated after the when) into the table to be loaded.

BEGINDATA

If the data to be loaded is in the control file itself, the start of the data is marked with a BEGINDATA statement.

SQL*Loader in action consist of several additional items. If, in the course of performing data loads, SQL*Loader encounters records it cannot load, the record is rejected and the SQL*Loader puts it in special file called bad file. Additionally, SQL*Loader gives the user options to reject data based on special criteria. These criteria are defined in the control file as a part of the when clause. Is the SQL*Loader encounters a record that fails a specified when clause, the record is placed in a special file called discard file.

Examples:
A. Insert records into an empty table.

1.Create an empty table:

create table loader_1 (
load_time            date,
field_1              varchar2(10),
field_2              varchar2(10)
) ;

2. Create Control file:

load data
infile ‘loader_1.dat’ “str ‘\r\n'”
insert
into table loader_1
(
load_time    sysdate,
field_2      position( 1:10),
field_1      position(11:15)
)

Data file: loader_1.dat

0123456789abcde
test line next
another test line

3. Start SQL*Loader with sqlldr and load data file

C:\>sqlldr control=loader_1.ctl userid=test/test

SQL*Loader: Release 10.1.0.2.0 – Production on Mon Oct 31 10:50:48 2011

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

Commit point reached – logical record count 2
Commit point reached – logical record count 3

4. Verify the table

SQL> select * from loader_1;

LOAD_TIME FIELD_1    FIELD_2
——— ———- ———-
31-OCT-11 abcde      0123456789
31-OCT-11 next       test line
31-OCT-11 st li      another te

B. Insert records that are seperated by a comma.

1.Create a table:

create table loader_2 (
field_1       varchar2(10),
field_2       number
);

2. Create Control file:

load data
infile ‘loader_2.dat’ “str ‘\r\n'”
insert
into table loader_2
fields terminated by ‘,’
(
field_1      char,
field_2      integer external
)

‘integer external’ for field_2 means, that the numbers in the dat file are human readable rather than “real” bytes

Data file: loader_2.dat

one,1
two,2
three, 3
fifty,50
forty, 40
eighty-eight,88
one hundred,100
hundred,100
fourteen,14

3. Start SQL*Loader with sqlldr and load data file

C:\>sqlldr control=loader_2.ctl userid=test/test

SQL*Loader: Release 10.1.0.2.0 – Production on Mon Oct 31 11:19:18 2011

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

Commit point reached – logical record count 8
Commit point reached – logical record count 9

4. Verify the table:

SQL> select * from loader_2;

FIELD_1       FIELD_2
———- ———-
one                 1
two                 2
three               3
fifty              50
forty              40
hundred           100
fourteen           14

7 rows selected.

Two records that could not be loaded are found in the bad file.

Bad file: loader_2.bad

eighty-eight,88
one hundred,100

C. Selectively insert records according to a criteria.

1.Create a table:

create table loader_3 (
field_1       varchar2(10),
field_2       varchar2(10)
);

2. Create Control file:

Suppose only those records whose second field equals Employee is to be loaded, then

load data
infile ‘loader_3.dat’ “str ‘\r\n'”
discardfile ‘loader_3.dsc’
insert
into table loader_3
when field_2 = ‘Employee’
fields terminated by ‘;’
(
field_1      char,
field_2      char
)

Data file: loader_3.dat

Name1;Employee;
Name2;Employee;
Name3;Employee;
Name4;Employee;
Name5;Admin;
Name6;Employee;
Name7;Employee;
Name8;Admin;

3. Start SQL*Loader with sqlldr and load data file

C:\>sqlldr control=loader_3.ctl userid=test/test

SQL*Loader: Release 10.1.0.2.0 – Production on Mon Oct 31 11:36:58 2011

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

Commit point reached – logical record count 7

4. Verify the table:

SQL> select * from loader_3;

FIELD_1    FIELD_2
———- ———-
Name1      Employee
Name2      Employee
Name3      Employee
Name4      Employee
Name6      Employee
Name7      Employee

6 rows selected.

Records that didn’t meet the criteria are found in the discard file:

Discard file: loader_3.dsc

Name5;Admin;
Name8;Admin;

D. Selectively insert records into two different tables.

1.Create two tables:

create table loader_4_1 (
field_1       varchar2(10),
field_2       varchar2(10)
);

create table loader_4_2 (
field_1       varchar2(10),
field_2       varchar2(10)
);

2. Create Control file:

load data
infile ‘loader_4.dat’ “str ‘\r\n'”
discardfile ‘loader_4.dsc’
insert
into table loader_4_1
when field_2 = ‘Employee’
(
field_1      position(1) char(5),
field_2      position(7) char(8)
)
into table loader_4_2
when field_2 = ‘Admin’
(
field_1      position(1) char(5),
field_2      position(7) char(5)
)

Data file: loader_4.dat

Name1    Employee
Name2    Employee
Name3    Employee
Name4    Employee
Name5    Admin
Name6    Employee
Name7    Employee
Name8    Admin

3. Start SQL*Loader with sqlldr and load data file

C:\>sqlldr control=loader_4.ctl userid=test/test

SQL*Loader: Release 10.1.0.2.0 – Production on Mon Oct 31 11:56:27 2011

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

Commit point reached – logical record count 7
Commit point reached – logical record count 8

4. Verify tables:

SQL> select * from loader_4_1;

FIELD_1    FIELD_2
———- ———-
Name1      Employee
Name2      Employee
Name3      Employee
Name4      Employee
Name6      Employee
Name7      Employee

6 rows selected.

SQL> select * from loader_4_2;

FIELD_1    FIELD_2
———- ———-
Name5      Admin
Name8      Admin

Reference Links:

http://orafaq.com/wiki/SQL*Loader

http://www.adp-gmbh.ch/ora/tools/sql_loader/index.html

http://oreilly.com/catalog/orsqlloader/chapter/ch01.html

Advertisements
Categories: Oracle Utilities

Performance Tuning Areas

October 20, 2011 Leave a comment

Just picking those area that require tuning! Source – OraFAQ

  •     Database Design (if it’s not too late):

Poor system performance usually results from a poor database design. One should generally normalize to the 3NF. Selective denormalization can provide valuable performance improvements. When designing, always keep the “data access path” in mind. Also look at proper data partitioning, data replication, aggregation tables for decision support systems, etc.

  •     Application Tuning:

Experience shows that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.

  •     Memory Tuning:

Properly size your database buffers (shared_pool, buffer cache, log buffer, etc) by looking at your wait events, buffer hit ratios, system swapping and paging, etc. You may also want to pin large objects into memory to prevent frequent reloads.

  •     Disk I/O Tuning:

Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc.

  •     Eliminate Database Contention:

Study database locks, latches and wait events carefully and eliminate where possible.

  •     Tune the Operating System:

Monitor and tune operating system CPU, I/O and memory utilization. For more information, read the related Oracle FAQ dealing with your specific operating system.

Oracle Cloning

October 20, 2011 Leave a comment

About Cloning
(From Oracle.com)
Cloning is the process of copying an existing Oracle installation to a different location and updating the copied bits to work in the new environment. The changes made by applying one-off patches on the source Oracle home are also present after the clone operation. The source and the destination path (host to be cloned) need not be the same. During cloning, OUI replays the actions that were run to install the home. Cloning is similar to installation, except that OUI runs the actions in a special mode referred to as clone mode. Some situations in which cloning is useful are:

Creating an installation that is a copy of a production, test, or development installation. Cloning enables you to create a new installation with all patches applied to it in a single step. This contrasts with going through the installation process by performing separate steps to install, configure, and patch the installation.

Rapidly deploying an instance and the applications that it hosts.

Preparing an Oracle home and deploying it to many hosts.

The cloned installation behaves the same as the source installation. For example, the cloned Oracle home can be removed using OUI or patched using OPatch. You can also use the cloned Oracle home as the source for another cloning operation. You can create a cloned copy of a test, development, or production installation by using the command-line cloning scripts. The default cloning procedure is adequate for most usage cases. However, you can also customize various aspects of cloning, for example, to specify custom port assignments, or to preserve custom settings.

The cloning process copies all of the files from the source Oracle home to the destination Oracle home. Thus, any files used by the source instance located outside the source Oracle home’s directory structure are not copied to the destination location.

The size of the binaries at the source and the destination may differ because these are relinked as part of the clone operation, and the operating system patch levels may also differ between these two locations. Additionally, the number of files in the cloned home would increase because several files copied from the source, specifically those being instantiated, are backed up as part of the clone operation.

OUI cloning is more beneficial than using the tarball approach, because cloning configures the Central Inventory and the Oracle home inventory in the cloned home. Cloning also makes the home manageable and allows the paths in the cloned home and the target home to be different.

1. Sign in as SYSDBA on the source system through SQL Plus and issue:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Create database syntax will be now placed in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

Trace file:

— The following are current System-scope REDO Log Archival related
— parameters and can be included in the database initialization file.

— LOG_ARCHIVE_DEST=”
— LOG_ARCHIVE_DUPLEX_DEST=”

— LOG_ARCHIVE_FORMAT=ARC%S_%R.%T

— DB_UNIQUE_NAME=”TEST”

— LOG_ARCHIVE_CONFIG=’SEND, RECEIVE, NODG_CONFIG’
— LOG_ARCHIVE_MAX_PROCESSES=2
— STANDBY_FILE_MANAGEMENT=MANUAL
— STANDBY_ARCHIVE_DEST=%ORACLE_HOME%\RDBMS
— FAL_CLIENT=”
— FAL_SERVER=”

— LOG_ARCHIVE_DEST_10=’LOCATION=USE_DB_RECOVERY_FILE_DEST’
— LOG_ARCHIVE_DEST_10=’OPTIONAL REOPEN=300 NODELAY’
— LOG_ARCHIVE_DEST_10=’ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC’
— LOG_ARCHIVE_DEST_10=’REGISTER NOALTERNATE NODEPENDENCY’
— LOG_ARCHIVE_DEST_10=’NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME’
— LOG_ARCHIVE_DEST_10=’VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)’
— LOG_ARCHIVE_DEST_STATE_10=ENABLE


— Below are two sets of SQL statements, each of which creates a new
— control file and uses it to open the database. The first set opens
— the database with the NORESETLOGS option and should be used only if
— the current versions of all online logs are available. The second
— set opens the database with the RESETLOGS option and should be used
— if online logs are unavailable.
— The appropriate set of statements can be copied from the trace into
— a script file, edited as necessary, and executed when there is a
— need to re-create the control file.

— Set #1. NORESETLOGS case

— The following commands will create a new control file and use it
— to open the database.
— Data used by Recovery Manager will be lost.
— Additional logs may be required for media recovery of offline
— Use this only if the current versions of all online logs are
— available.

— After mounting the created controlfile, the following SQL
— statement will place the database in the appropriate
— protection mode:
— ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “TEST” NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO01.LOG’ SIZE 50M,
GROUP 2 ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO02.LOG’ SIZE 50M,
GROUP 3 ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO03.LOG’ SIZE 50M
— STANDBY LOGFILE

DATAFILE
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEST.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS02.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE2.DBF’
CHARACTER SET WE8MSWIN1252
;

— Commands to re-create incarnation table
— Below log names MUST be changed to existing filenames on
— disk. Any one log file from each branch can be used to
— re-create incarnation records.
— ALTER DATABASE REGISTER LOGFILE ‘C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2011_10_17\O1_MF_1_1_%U_.ARC’;
— ALTER DATABASE REGISTER LOGFILE ‘C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2011_10_17\O1_MF_1_1_%U_.ARC’;
— Recovery is required if any of the datafiles are restored backups,
— or if the last shutdown was not normal or immediate.
RECOVER DATABASE

— All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

— Database can now be opened normally.
ALTER DATABASE OPEN;

— Commands to add tempfiles to temporary tablespaces.
— Online tempfiles have complete space information.
— Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEMP01.DBF’
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
— End of tempfile additions.

— Set #2. RESETLOGS case

— The following commands will create a new control file and use it
— to open the database.
— Data used by Recovery Manager will be lost.
— The contents of online logs will be lost and all backups will
— be invalidated. Use this only if online logs are damaged.

— After mounting the created controlfile, the following SQL
— statement will place the database in the appropriate
— protection mode:
— ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “TEST” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO01.LOG’ SIZE 50M,
GROUP 2 ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO02.LOG’ SIZE 50M,
GROUP 3 ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO03.LOG’ SIZE 50M
— STANDBY LOGFILE

DATAFILE
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEST.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS02.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE2.DBF’
CHARACTER SET WE8MSWIN1252
;

— Commands to re-create incarnation table
— Below log names MUST be changed to existing filenames on
— disk. Any one log file from each branch can be used to
— re-create incarnation records.
— ALTER DATABASE REGISTER LOGFILE ‘C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2011_10_17\O1_MF_1_1_%U_.ARC’;
— ALTER DATABASE REGISTER LOGFILE ‘C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2011_10_17\O1_MF_1_1_%U_.ARC’;
— Recovery is required if any of the datafiles are restored backups,
— or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

— Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

— Commands to add tempfiles to temporary tablespaces.
— Online tempfiles have complete space information.
— Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEMP01.DBF’
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
— End of tempfile additions.

2. Shutdown source database.

3. Install Oracle server on new machine. Create directories ( bdump, udump and cdump and other directories as in source database system ) for the copy database on target system.

4. Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

5. Add database name to tnsnames.ora, listener.ora on target system.

6. Create database service with ORADIM ( If OS is Windows )

oradim -new -sid TEST -INTPWD mypassword -STARTMODE AUTO

7. On target system, set SID and use sqlplus to connect as SYSDBA

8. Delete the control files if any already copied over using OS commands

9. Rename trace file to ‘ DB_CREATE_CONTROLFILE.SQL ‘, edited, contents are as follows:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “TEST” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO01.LOG’ SIZE 50M,
GROUP 2 ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO02.LOG’ SIZE 50M,
GROUP 3 ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO03.LOG’ SIZE 50M

DATAFILE
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE01.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEST.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS02.DBF’,
‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE2.DBF’
CHARACTER SET WE8MSWIN1252
;

RECOVER DATABASE USING BACKUP CONTROLFILE

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEMP01.DBF’
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

10. Run the create control file script issuing:

@DB_CREATE_CONTROLFILE.SQL

If everything went OK and you see no errors, then execute:

SHUTDOWN IMMEDIATE;

11. Startup new database and verify it work fine.

Categories: Cloning Tags: , , ,

Long Running Queries

October 19, 2011 Leave a comment

Below query displays the status of various operations that run for longer than 6 seconds:

select * from V$SESSION_LONGOPS

Draft Article*

Start Oracle service on Windows using ORADIM utility

October 19, 2011 Leave a comment

To start a new service on Windows, Oracle provides a command-line utility, ORADIM. It can also be used to remove, edit and stop a service.

Create:

C:\oracle\product\10.1.0\db_2>oradim -new -sid %ORACLE_SID% -intpwd password -startmode A
Instance created.

Startup:

C:\oracle\product\10.1.0\db_2>oradim –startup –sid %ORACLE_SID%

Shutdown:

C:\oracle\product\10.1.0\db_2>oradim –shutdown –sid %ORACLE_SID%

Delete:

C:\oracle\product\10.1.0\db_2>oradim –delete –sid %ORACLE_SID%

  • startup – Indicates that the specified instance should be started.
  • shutdown – Indicates that the specified instance should be shutdown.
  • sid – The SID of the database to start.
  • intpwd – The password for the database user.
  • startmode – It could be A(utomatic) or M(anual), this refers to whether or not starting the service automatically opens the database.

To verify whether the service is created or not, check Windows services by typing services.msc into the console. A service named OracleServiceDBName (DBName = %ORACLE_SID%) will be found.

Oracle also created a password file under %ORACLE_HOME%\database.

C:\oracle\product\10.1.0\db_2\database\PWDDBName.ORA

Registry Settings:

Check registry editor by typing regedit into the console.

HKLM\Software\Oracle\Key_<Oracle Home Name>\ORA<SID>_AUTOSTART
Start the database when the service starts

HKLM\Software\Oracle\Key_<Oracle Home Name>\ORA<SID>_PFILE
Location of the Configuration File

HKLM\Software\Oracle\Key_<Oracle Home Name>\ORA<SID>_SHUTDOWN
Shutdown the database when the service stops

HKLM\Software\Oracle\Key_<Oracle Home Name>\ORA<SID>_SHUTDOWNTYPE
Type of shutdown to do when stopping the server

RMAN – Disaster Recovery using RMAN

October 17, 2011 Leave a comment

Oracle Backup and Recovery on Windows, Part III – Disaster Recovery using RMAN:

(Article  extracted from  Orafusion.com)

1. Introduction:

This is the final article of a three part series, introducing Oracle’s RMAN (Recovery MANager) backup and recovery tool to novice DBAs. The first article dealt with taking RMAN backups and the second one covered some recovery scenarios. This article discusses disaster recovery – i.e. a situation in which your database server has been destroyed and has taken all your database files (control files, logs and data files) with it. Obviously, recovery from a disaster of this nature is dependent on what you have in terms of backups and hardware resources. We assume you have the following available after the disaster:

  • A server with the same disk layout as the original.
  • The last full hot backup on tape.

With the above items at hand, it is possible to recover all data up to the last full backup. One can do better if subsequent archive logs (after the last backup) are available. In our case these aren’t available, since our only archive destination was on the destroyed server (see Part I ). Oracle provides methods to achieve better data protection. We will discuss some of these towards the end of the article.

Now on with the task at hand. The high-level steps involved in disaster recovery are:

  • Build replacement server.
  • Restore backup from tape.
  • Install database software.
  • Create Oracle service.
  • Restore and recover database.

It sounds quite straightforward and it is reasonably so. However, there are some details that are often missed in books and documentation. The devil, as always, is in these details. Here we hope to provide you with enough detail to plan and practice disaster recovery in your test environment.

2. Build the server

You need a server to host the database, so the first step is to acquire or build the new machine. This is not strictly a DBA task, so we won’t delve into details here. The main point to keep in mind is that the replacement server should, as far as possible, be identical to the old one. In particular, pay attention to the following areas:

Disk layout and capacity: Ideally the server should have the same number of disks as the original. This avoids messy renaming of files during recovery. Obviously, the new disks should also have enough space to hold all software and data that was on the original server.
Operating system, service pack and patches: The operating system environment should be the same as the original, right up to service pack and patch level.
Memory: The new server must have enough memory to cater to Oracle and operating system / other software requirements. Oracle memory structures (Shared pool, db buffer caches etc) will be sized identically to the original database instance. Use of the backup server parameter file will ensure this.

Although you probably won’t build and configure the machine yourself. It is important to work with your systems people so that the above items are built to the recovery server specs.

3. Restore backup from tape

The next step is to get your backup from tape on to disk. In our example from Part I, the directory to be restored is e:\backup. The details of this depend on the backup product used, so we can’t go into it any further. This task would normally be performed your local sysadmin.

4. Install Oracle Software

Now we get to the meat of the database recovery process. The next step is to install Oracle software on the machine. The following points should be kept in mind when installing the software:

  • Install the same version of Oracle as was on the destroyed server. The version number should match right down to the patch level, so this may be a multi-step process involving installation followed by the application of one or more patchsets and patches.
  • Do not create a new database at this stage.
  • Create a listener using the Network Configuration Assistant. Ensure that it has the same name and listening ports as the original listener. Relevant listener configuration information can be found in the backed up listener.ora file.

4. Create directory structure for database files

After software installation is completed, create all directories required for datafiles, (online and archived) logs, control files and backups. All directory paths should match those on the original server. This, though not mandatory, saves additional steps associated with renaming files during recovery.

Don’t worry if you do not know where the database files should be located. You can obtain the required information from the backup spfile and control file at a later stage. Continue reading – we’ll come back to this later.

5. Create Oracle service

As described in section 2 of Part II, an Oracle service must be exist before a database is created. The service is created using the oradim utility, which must be run from the command line. The following commands show how to create and modify a service (comments in italics, typed commands in bold):

–create a new service with manual startup

C:\>oradim -new -sid ORCL -startmode m

–modify service to startup automatically

C:\>oradim -edit -sid ORCL -startmode a

Unfortunately oradim does not give any feedback, but you can check that the service exists via the Services administrative panel. The service has been configured to start automatically when the computer is powered up. Note that oradim offers options to delete, startup and shutdown a service. See the documentation for details.

6. Restore and recover database

Now it is time to get down to the nuts and bolts of database recovery. There are several steps, so we’ll list them in order:

  • Copy password and tnsnames file from backup: The backed up password file and tnsnames.ora files should be copied from the backup directory (e:\backup, in our example) to the proper locations. Default location for password and tnsnames files are ORACLE_HOME\database ORACLE_HOME\network\admin respectively.
  • Set ORACLE_SID environment variable: ORACLE_SID should be set to the proper SID name (ORCL in our case). This can be set either in the registry (registry key: HKLM\Software\Oracle\HOME\ORACLE_SID) or from the system applet in the control panel.
  • Invoke RMAN and set the DBID: We invoke rman and connect to the target database as usual. No login credentials are required since we connect from an OS account belonging to ORA_DBA. Note that RMAN accepts a connection to the database although the database is yet to be recovered. RMAN doesn’t as yet “know” which database we intend to connect to. We therefore need to identify the (to be restored) database to RMAN. This is done through the database identifier (DBID). The DBID can be figured out from the name of the controlfile backup. Example: if you use the controlfile backup format suggested in Part I, your controlfile backup name will be something like “CTL_SP_BAK_C-1507972899-20050228-00”. In this case the DBID is 1507972899. Here’s a transcript illustrating the process of setting the DBID:
C:\>rman
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
RMAN> connect target /
connected to target database (not started)
RMAN> set dbid 1507972899
executing command: SET DBID
RMAN>
  • Restore spfile from backup: To restore the spfile, you first need to startup the database in the nomount state. This starts up the database using a dummy parameter file. After that you can restore the spfile from the backup (which has been restored from tape in Section 3). Finally you restart the database in nomount state. The restart is required in in order to start the instance using the restored parameter file. Here is an example RMAN transcript for the foregoing procedure. Note the difference in SGA size and components between the two startups:
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
 LRM-00109: could not open parameter file 'C:\ORACLE\ORA92\DATABASE\INITORCL.ORA'
trying to start the Oracle instance without parameter files ...
 Oracle instance started
Total System Global Area 97590928 bytes
Fixed Size 454288 bytes
 Variable Size 46137344 bytes
 Database Buffers 50331648 bytes
 Redo Buffers 667648 bytes
RMAN> restore spfile from 'e:\backup\CTL_SP_BAK_C-1507972899-20050228-00';
Starting restore at 01/MAR/05
using target database controlfile instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: sid=9 devtype=DISK
 channel ORA_DISK_1: autobackup found: e:\backup\CTL_SP_BAK_C-1507972899-20050228-00
 channel ORA_DISK_1: SPFILE restore from autobackup complete
 Finished restore at 01/MAR/05
RMAN> startup force nomount
Oracle instance started
Total System Global Area 1520937712 bytes
Fixed Size 457456 bytes
 Variable Size 763363328 bytes
 Database Buffers 754974720 bytes
 Redo Buffers 2142208 bytes
RMAN>
The instance is now started up with the correct initialisation parameters.

We are now in a position to determine the locations of control file and archive destination, as this information sits in the spfile. This is done via SQL Plus as follows:

C:\>sqlplus /nolog
....output not shown
SQL>connect / as sysdba
 Connected.
 SQL> show parameter control_file
....output not shown
SQL> show parameter log_archive_dest
 ....output not shown

The directories listed in the CONTROL_FILES and LOG_ARCHIVE_DEST_N parameters should be created at this stage if they haven’t been created earlier.

  • Restore control file from backup: The instance now “knows” where the control files should be restored, as this is listed in the CONTROL_FILES initialisation parameter. Therefore, the next step is to restore these files from backup. Once the control files are restored, the instance should be restarted in mount mode. A restart is required because the instance must read the initialisation parameter file in order to determine the control file locations. At the end of this step RMAN also has its proper configuration parameters, as these are stored in the control file.

Here is a RMAN session transcript showing the steps detailed here:

RMAN> restore controlfile from 'e:\backup\CTL_SP_BAK_C-1507972899-20050228-00';
Starting restore at 01/MAR/05
allocated channel: ORA_DISK_1
 hannel ORA_DISK_1: sid=13 devtype=DISK
 channel ORA_DISK_1: restoring controlfile
 channel ORA_DISK_1: restore complete
 replicating controlfile
 input filename=D:\ORACLE_DATA\CONTROLFILE\ORCL\CONTROL01.CTL
 output filename=E:\ORACLE_DATA\CONTROLFILE\ORCL\CONTROL02.CTL
 output filename=C:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\CONTROL03.CTL
 Finished restore at 01/MAR/05
RMAN> shutdown
Oracle instance shut down
RMAN> exit
Recovery Manager complete.
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
 database mounted
Total System Global Area 1520937712 bytes
Fixed Size 457456 bytes
 Variable Size 763363328 bytes
 Database Buffers 754974720 bytes
 Redo Buffers 2142208 bytes
RMAN> show all;
using target database controlfile instead of recovery catalog
 RMAN configuration parameters are:
 CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
 CONFIGURE BACKUP OPTIMIZATION OFF; # default
 CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
 CONFIGURE CONTROLFILE AUTOBACKUP ON;
 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'e:\backup\ctl_sp_bak_%F';
 CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
 CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
 CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
 CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'e:\backup\%U.bak' MAXPIECESIZE 4G;
 CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'e:\backup\%U.bak' MAXPIECESIZE 4G;
 CONFIGURE MAXSETSIZE TO UNLIMITED; # default
 CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\ORA92\DATABASE\SNCFORCL.ORA'; # default
RMAN>

At this stage we can determine the locations of data files and redo logs if we don’t know where they should go. This is done from SQL Plus as follows:

C:\>sqlplus /nolog
...output not shown
SQL> connect / as sysdba
 Connected.
 SQL> select name from v$datafile;
...output not shown
SQL> select member from v$logfile;
...output not shown
SQL>

The directories shown in the output should be created manually if this hasn’t been done earlier.

  • Restore all datafiles: This is easy. Simply issue a “restore database” command from RMAN, and it will do all the rest for you:
RMAN> restore database;
Starting restore at 01/MAR/05
using target database controlfile instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: sid=11 devtype=DISK
 allocated channel: ORA_DISK_2
 channel ORA_DISK_2: sid=8 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 D:\ORACLE_DATA\DATAFILES\ORCL\SYSTEM01.DBF
 restoring datafile 00003 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF
 restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS02.DBF
 channel ORA_DISK_2: starting datafile backupset restore
 channel ORA_DISK_2: specifying datafile(s) to restore from backup set
 restoring datafile 00002 to D:\ORACLE_DATA\DATAFILES\ORCL\UNDOTBS01.DBF
 restoring datafile 00005 to D:\ORACLE_DATA\DATAFILES\ORCL\TOOLS01.DBF
 restoring datafile 00006 to D:\ORACLE_DATA\DATAFILES\ORCL\TOOLS02.DBF
 channel ORA_DISK_2: restored backup piece 1
 piece handle=E:\BACKUP\80G6E1TT_1_1.BAK tag=TAG20041130T222501 params=NULL
 channel ORA_DISK_1: restored backup piece 1
 piece handle=E:\BACKUP\81G6E1TU_1_1.BAK tag=TAG20041130T222501 params=NULL
 channel ORA_DISK_2: restored backup piece 2
 piece handle=E:\BACKUP\80G6E1TT_2_1.BAK tag=TAG20041130T222501 params=NULL
 channel ORA_DISK_1: restored backup piece 2
 piece handle=E:\BACKUP\81G6E1TU_2_1.BAK tag=TAG20041130T222501 params=NULL
 channel ORA_DISK_1: restored backup piece 3
 piece handle=E:\BACKUP\81G6E1TU_3_1.BAK tag=TAG20041130T222501 params=NULL
 channel ORA_DISK_1: restore complete
 channel ORA_DISK_2: restored backup piece 3
 piece handle=E:\BACKUP\80G6E1TT_3_1.BAK tag=TAG20041130T222501 params=NULL
 channel ORA_DISK_2: restore complete
 Finished restore at 01/MAR/05
RMAN>
  • Recover database: The final step is to recover the database. Obviously recovery is dependent on the available archived (and online) redo logs. Since we have lost our database server and have no remote archive destination, we can recover only up to the time of the backup. Further, since this is an incomplete recovery, we will have to open the database with resetlogs. Here’s a sample RMAN session illustrating this:
RMAN> recover database;
Starting recover at 01/MAR/05
 using channel ORA_DISK_1
 using channel ORA_DISK_2
starting media recovery
unable to find archive log archive log thread=1 sequence=1388
RMAN-00571: ==============================
 RMAN-00569: =ERROR MESSAGE STACK FOLLOWS =
 RMAN-00571: ===============================
 RMAN-03002: failure of recover command at 04/01/2005 14:14:43
 RMAN-06054: media recovery requesting unknown log: thread 1 scn 32230460
RMAN> alter database open resetlogs;
database opened
RMAN>

Note that RMAN automatically applies all available archive logs. It first applies the backed up log and then searches for subsequent logs in the archive destination. This opens the door for further recovery if the necessary logs are available. In our case, however, we have no more redo so we open the database with resetlogs. The error message above simply indicates that RMAN has searched, unsuccessfully, for subsequent logs.

That’s it. The database has been recovered, from scratch, to the last available backup. Now having done this, it is worth spending some time in discussing how one can do better – i.e. recover up to a point beyond the backup. We do this in the next section.

7. Options for better recovery

The above recovery leaves one with a sense of dissatisfaction: one could have done much better had the necessary logs been available. Clearly, one would have to copy the logs to a remote machine in order to guarantee access in a disaster situation. A couple of ways to do this include:

Copy archive logs to a remote destination using OS scripts: This is achieved simply by a script scheduled to run every hour or so. The script copies, to a remote network computer, all the archive logs generated since the script last ran. This achieves better recoverability than before. However, it does not achieve up to the minute recovery. Further, one has to ensure that the a log switch is performed before the logs are copied, so as to ensure that redo associated with recent transactions (within the last hour) is copied to the remote destination.The log switch can be performed using the “alter system archive log current” command.

Configure the Oracle ARC process to copy logs to the remote destination: This is done by defining a secondary archive destination via one of the LOG_ARCHIVE_DEST_N initialisation parameters. This method is not recommended because it is somewhat fragile – see this discussion on Tom Kyte’s site, for example. Be sure to use a mapped network drive as the archive destination if you choose to go down this path. Oracle 9i will not recognise archive log destinations specified using UNC (Universal Naming Convention).

Finally, any article on disaster recovery should mention Oracle Data Guard – which is Oracle Corporation’s recommended disaster recovery solution for mission critical systems. This is essentially a standby database that is kept synchronised with the primary through the continuous application of redo. There are different levels of synchronisation depending on required availability, performance and (most important!) the acceptable data loss. There are two types of standby databases depending on how redo is applied: 1) Physical standby – which is an identical, block for block, copy of the primary, and 2) Logical standby – which is kept synchronised by applying SQL mined from redo logs. Interested readers are referred to the Oracle documentation on Data Guard for further details, as it is a vast subject, appropriate for a book rather than an article this size. A good starting point is Oracle Data Guard Concepts and Administration guide.

8. Concluding Remarks

This brings me to the end of the three part series on RMAN. I hope the material covered helps you plan your backup and recovery strategy. Remember this: your backup strategy should be dictated by business requirements rather than the latest and greatest technology. It is technically not hard to implement up-to-the-minute recovery, given the appropriate hardware and network bandwidth. However, unless you work for a bank or similar business, your end-users will likely accept some data loss once they hear the costs involved in up-to-the-minute recovery. If your requirements do allow for some data loss, it should be possible to implement a robust recovery strategy using the concepts and procedures discussed in this series of articles.

Source

Reference Links:

RMAN – Database Recovery

October 17, 2011 Leave a comment

Oracle Backup and Recovery on Windows, Part II – Database Recovery using RMAN:

(Article  extracted from  Orafusion.com)

1. Introduction:

This article is the second in a series of three, introducing Oracle’s RMAN (Recovery MANager) backup and recovery tool to novice DBAs. The first article dealt with taking RMAN backups. The aim in the present piece is to cover some recovery scenarios. In all the scenarios it is assumed that:

  • The database host server is still up and running. Recovery in a situation where the server is unavailable will be the subject of the next article.
  • The last full backup is available on disk.
  • All archived logs since the last backup are available on disk.

Most of the examples here deal with complete recovery, where all committed transactions up to the point of failure are recovered. We also discuss some cases of incomplete recovery, where the database is recovered to a time prior to failure. Generally one would perform an incomplete recovery only when some of the required logs (archived or online) are missing. One of the examples below deals with just this situation. There are also other valid reasons for performing incomplete recovery – example: to recover a table that has been accidentally dropped. We will not go into these incomplete recovery situations in this article.

The following scenarios are discussed:

  • Recovery from corrupted or missing datafile(s).
  • Recovery from corrupted or missing online redo logs.
  • Recovery from corrupted or missing control files.

In each of the examples discussed it is assumed that RMAN is invoked on the database host machine using an OS account belonging to the ORA_DBA group. This permits connections to the database without a password, i.e. using “/ as sysdba”, as discussed in the first article of this series.

2. Instance and database

In order to understand some of the recovery procedures below, it is necessary to appreciate the difference between an instance and a database.

An instance is made up of Oracle process(es) and associated memory. On Windows operating systems there is a single, multithreaded Oracle process which is associated with a Windows service. The service is normally created when the database is first created, so, as far as this discussion is concerned, the service already exists (for completeness we point out that the service is created and manipulated using the oradim utility – check the Oracle Administrator’s Guide for details on oradim). For our purposes then, an Oracle instance refers to the memory associated with the pre-existing Oracle process. The instance is created when a startup command is issued from the command line (SQL Plus or RMAN for example) or via a GUI tool such as Oracle Enterprise Manager. More on this below. A database , on the other hand, refers to the files comprising the database. These files exist independent of any instance. An instance requires that the computer be powered on and also requires that the Oracle service exists. The database, in contrast, exists even if the computer is powered down.

An instance can be started up in various modes from SQL Plus or RMAN using the startup command. Three variants of the startup command, relevant to the present discussion are:

  • startup nomount: The instance is started up – i.e. the required memory structures are associated with the pre-existing Oracle process. At this point the instance is not associated with any database.
  • startup mount: The instance is started up and the database control file is read. At this point Oracle knows the locations of all files that make up the database. However, the database is not open. It is possible to go from the nomount state to the mount state using the SQL command “alter database mount”.
  • startup: The instance is started up, the control file is read and the database opened for general use. At this point the instance is fully associated with the database. It is possible to go from nomount / mount to the open state using the SQL command “alter database open”.

There are other options to the startup command which we will not go into here. See the Oracle Administrator’s Guide for further details. Note that startup is not a standard SQL command; it can only be executed from an Oracle tool such as SQL Plus or RMAN.

3. Recovery from missing or corrupted datafile(s):

Case 1: Recovery from corrupted or missing datafile

This scenario deals with a situation where a datafile has gone missing, or is corrupted beyond repair. For concreteness, we look at a case where a datafile is missing. Below is a transcript of an SQL Plus session that attempts to open a database with a missing datafile (typed commands in bold, lines in italics are my comments, all other lines are feedback from SQL Plus):

--open SQL Plus from the command line without
 --logging on to database
C:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jan 25 14:52:41 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
--Connect to the idle Oracle process as a privileged user and start up instance
SQL> connect / as sysdba
 Connected to an idle instance.
 SQL> startup
 ORACLE instance started.
Total System Global Area 131555128 bytes
 Fixed Size 454456 bytes
 Variable Size 88080384 bytes
 Database Buffers 41943040 bytes
 Redo Buffers 1077248 bytes
 Database mounted.
 ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
 ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'
SQL>

The error message tells us that file# 4 is missing. Note that although the startup command has failed, the database is in the mount state. Thus, the database control file, which is also the RMAN repository can be accessed by the instance and by RMAN. We now recover the missing file using RMAN. The transcript of the recovery session is reproduced below (bold lines are typed commands, comments in italics, the rest is feedback from RMAN):

--logon to RMAN
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--restore missing datafile
RMAN> restore datafile 4;
Starting restore at 26/JAN/05
using target database controlfile instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: sid=14 devtype=DISK
 allocated channel: ORA_DISK_2
 channel ORA_DISK_2: 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 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF
 channel ORA_DISK_1: restored backup piece 1
 piece handle=D:\BACKUPQGB0UEC_1_1.BAK tag=TAG20050124T152708 params=NULL
 channel ORA_DISK_1: restore complete
 Finished restore at 26/JAN/05
--recover restored datafile - RMAN applies all logs automatically
RMAN> recover datafile 4;
Starting recover at 26/JAN/05 using channel ORA_DISK_1
 using channel ORA_DISK_2
starting media recovery
archive log thread 1 sequence 4 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_4.ARC
 archive log thread 1 sequence 5 is already on disk as file C:\ORACLE_ARCHIVE\ORCL\1_5.ARC
 archive log thread 1 sequence 6 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_6.ARC
 archive log thread 1 sequence 7 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_7.ARC
 archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_4.ARC thread=1 sequence=4
 archive log filename=C:\ORACLE_ARCHIVE\ORCL\1_5.ARC thread=1 sequence=5
 media recovery complete
 Finished recover at 26/JAN/05
--open database for general use
RMAN> alter database open;
database opened
RMAN>

In the above scenario, the database is already in the mount state before the RMAN session is initiated. If the database is not mounted, you should issue a “startup mount” command before attempting to restore the missing datafile. The database must be mounted before any datafile recovery can be done.

If the database is already open when datafile corruption is detected, you can recover the datafile without shutting down the database. The only additional step is to take the relevant tablespace offline before starting recovery. In this case you would perform recovery at the tablespace level. The commands are:

C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--offline affected tablespace
RMAN> sql 'alter tablespace USERS offline immediate';
using target database controlfile instead of recovery catalog
 sql statement: alter tablespace USERS offline immediate
--recover offlined tablespace
RMAN> recover tablespace USERS;
Starting recover at 26/JAN/05
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: sid=14 devtype=DISK
 allocated channel: ORA_DISK_2
 channel ORA_DISK_2: sid=12 devtype=DISK
starting media recovery
 media recovery complete
Finished recover at 26/JAN/05
--online recovered tablespace
RMAN> sql 'alter tablespace USERS online';
sql statement: alter tablespace USERS online
RMAN>

Here we have used the SQL command, which allows us to execute arbitrary SQL from within RMAN.

Case 2: Recovery from block corruption

It is possible to recover corrupted blocks using RMAN backups. This is a somewhat exotic scenario, but it can be useful in certain circumstances, as illustrated by the following example. Here’s the situation: a user connected to SQLPlus gets a data block corruption error when she queries a table. Here’s a part of the session transcript:

SQL> connect testuser/testpassword
 Connected.
 SQL> select count(*) from test_table;
 select count(*) from test_table
 *
 ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 2015)
 ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'
Since we know the file and block number, we can perform block level recovery using RMAN. This is best illustrated by example:
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--restore AND recover specific block
RMAN> blockrecover datafile 4 block 2015;
Starting blockrecover at 26/JAN/05
 using target database controlfile instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: sid=19 devtype=DISK
 allocated channel: ORA_DISK_2
 channel ORA_DISK_2: sid=20 devtype=DISK
channel ORA_DISK_1: restoring block(s)
 channel ORA_DISK_1: specifying block(s) to restore from backup set
 restoring blocks of datafile 00004
 channel ORA_DISK_1: restored block(s) from backup piece 1
 piece handle=E:\BACKUPQGB0UEC_1_1.BAK tag=TAG20050124T152708 params=NULL
 channel ORA_DISK_1: block restore complete
starting media recovery
 media recovery complete
Finished blockrecover at 26/JAN/05
RMAN>
Now our user should be able to query the table from her SQLPlus session. Here's her session transcript after block recovery.
SQL> select count(*) from test_table;
COUNT(*)
 ----------
 217001
SQL>

A couple of important points regarding block recovery:

  • Block recovery can only be done using RMAN.
  • The entire database can be open while performing block recovery.
  • Check all database files for corruption. This is important – there could be other corrupted blocks. Verification of database files can be done using RMAN or the dbverify utility. To verify using RMAN simply do a complete database backup with default settings. If RMAN detects block corruption, it will exit with an error message pointing out the guilty file/block.

4. Recovery from missing or corrupted redo log group:

Case 1: A multiplexed copy of the missing log is available.

If a redo log is missing, it should be restored from a multiplexed copy, if possible. This is the only way to recover without any losses. Here’s an example, where I attempt to startup from SQLPlus when a redo log is missing:

SQL> startup
 ORACLE instance started.
Total System Global Area 131555128 bytes
 Fixed Size 454456 bytes
 Variable Size 88080384 bytes
 Database Buffers 41943040 bytes
 Redo Buffers 1077248 bytes
 Database mounted.
 ORA-00313: open failed for members of log group 3 of thread 1
 ORA-00312: online log 3 thread 1: 'D:\ORACLE_DATA\LOGS\ORCL\REDO03A.LOG'
SQL>

To fix this we simply copy REDO03A.LOG from its multiplexed location on E: to the above location on E:. After copying the file, we issue an “alter database open” from the above SQLPlus session:

SQL> alter database open;
Database altered.
SQL>

That’s it – the database is open for use.

Case 2: All members of a log group lost.

In this case an incomplete recovery is the best we can do. We will lose all transactions from the missing log and all subsequent logs. We illustrate using the same example as above. The error message indicates that members of log group 3 are missing. We don’t have a copy of this file, so we know that an incomplete recovery is required. The first step is to determine how much can be recovered. In order to do this, we query the V$LOG view (when in the mount state) to find the system change number (SCN) that we can recover to (Reminder: the SCN is a monotonically increasing number that is incremented whenever a commit is issued):

--The database should be in the mount state for v$log access
SQL> select first_change# from v$log where group#=3 ;
FIRST_CHANGE#
 -------------
 370255
SQL>

The FIRST_CHANGE# is the first SCN stamped in the missing log. This implies that the last SCN stamped in the previous log is 370254 (FIRST_CHANGE#-1). This is the highest SCN that we can recover to. In order to do the recovery we must first restore ALL datafiles to this SCN, followed by recovery (also up to this SCN). This is an incomplete recovery, so we must open the database resetlogs after we’re done. Here’s a transcript of the recovery session (typed commands in bold, comments in italics, all other lines are RMAN feedback):

C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--Restore ENTIRE database to determined SCN
RMAN> restore database until scn 370254;
Starting restore at 26/JAN/05
using channel ORA_DISK_1
 using channel ORA_DISK_2
 channel ORA_DISK_1: starting datafile backupset restore
 channel ORA_DISK_1: specifying datafile(s) to restore from backup set
 restoring datafile 00001 to D:\ORACLE_DATA\DATAFILES\ORCL\SYSTEM01.DBF
 restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF
 channel ORA_DISK_2: starting datafile backupset restore
 channel ORA_DISK_2: specifying datafile(s) to restore from backup set
 restoring datafile 00002 to D:\ORACLE_DATA\DATAFILES\ORCL\UNDOTBS01.DBF
 restoring datafile 00003 to D:\ORACLE_DATA\DATAFILES\ORCL\TOOLS01.DBF
 channel ORA_DISK_2: restored backup piece 1
 piece handle=E:\BACKUP\13GB14IB_1_1.BAK tag=TAG20050124T171139 params=NUL
 channel ORA_DISK_2: restore complete
 channel ORA_DISK_1: restored backup piece 1
 piece handle=E:\BACKUP\14GB14IB_1_1.BAK tag=TAG20050124T171139 params=NUL
 channel ORA_DISK_1: restore complete
 Finished restore at 26/JAN/05
--Recover database
RMAN> recover database until scn 370254;
Starting recover at 26/JAN/05
 using channel ORA_DISK_1
 using channel ORA_DISK_2
starting media recovery
archive log thread 1 sequence 9 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_9.ARC
 archive log thread 1 sequence 10 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_10.ARC
 archive log thread 1 sequence 11 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_11.ARC
 archive log thread 1 sequence 12 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_12.ARC
 archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_9.ARC thread=1 sequence=9
 archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_10.ARC thread=1 sequence=10
 media recovery complete
 Finished recover at 26/JAN/05
--open database with RESETLOGS (see comments below)
RMAN> alter database open resetlogs;
database opened
RMAN>

The following points should be noted:

  • The entire database must be restored to the SCN that has been determined by querying v$log.
  • All changes beyond that SCN are lost. This method of recovery should be used only if you are sure that you cannot do better. Be sure to multiplex your redo logs, and (space permitting) your archived logs!
  • The database must be opened with RESETLOGS, as a required log has not been applied. This resets the log sequence to zero, thereby rendering all prior backups worthless. Therefore, the first step after opening a database RESETLOGS is to take a fresh backup. Note that the RESETLOGS option must be used for any incomplete recovery.

5. Recovery from missing or corrupted control file:

Case 1: A multiplexed copy of the control file is available.

On startup Oracle must read the control file in order to find out where the datafiles and online logs are located. Oracle expects to find control files at locations specified in the CONTROL_FILE initialisation parameter. The instance will fail to mount the database if any one of the control files are missing or corrupt. A brief error message will be displayed, with further details recorded in the alert log. The exact error message will vary depending on what has gone wrong. Here’s an example:

SQL> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
 Fixed Size 453492 bytes
 Variable Size 109051904 bytes
 Database Buffers 25165824 bytes
 Redo Buffers 667648 bytes
 ORA-00205: error in identifying controlfile, check alert log for more info
SQL>

On checking the alert log, as suggested, we find the following:

ORA-00202: controlfile: 'e:\oracle_dup_dest\controlfile\ORCL\control02.ctl'
 ORA-27046: file size is not a multiple of logical block size
 OSD-04012: file size mismatch (OS 5447783)

The above corruption was introduced by manually editing the control file when the database was closed.

The solution is simple, provided you have at least one uncorrupted control file – replace the corrupted control file with a copy using operating system commands. Remember to rename the copied file. The database should now start up without any problems.

Case 2: All control files lost

What if you lose all your control files? In that case you have no option but to use a backup control file. The recovery needs to be performed from within RMAN, and requires that all logs (archived and current online logs) since the last backup are available. The logs are required because all datafiles must also be restored from backup. The database will then have to be recovered up to the time the control files went missing. This can only be done if all intervening logs are available. Here’s an annotated transcript of a recovery session (as usual, lines in bold are commands to be typed, lines in italics are explanatory comments, other lines are RMAN feedback):

-- Connect to RMAN
 C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (not mounted)
-- set DBID - get this from the name of the controlfile autobackup.
 -- For example, if autobackup name is
 -- CTL_SP_BAK_C-1507972899-20050124-00 the the DBID is
 -- 1507972899. This step will not be required if the instance is
 -- started up from RMAN
RMAN> set dbid 1507972899
executing command: SET DBID
--restore controlfile from autobackup. The backup is not at the default
 --location so the path must be specified
RMAN> restore controlfile from 'e:\backup\CTL_SP_BAK_C-1507972899-20050124-00';
Starting restore at 26/JAN/05
using channel ORA_DISK_1
 channel ORA_DISK_1: restoring controlfile
 channel ORA_DISK_1: restore complete
 replicating controlfile
 input filename=D:\ORACLE_DATA\CONTROLFILE\ORCL\CONTROL01.CTL
 output filename=E:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\CONTROL02.CTL
 output filename=C:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\CONTROL03.CTL
 Finished restore at 26/JAN/05
-- Now that control files have been restored, the instance can mount the
 -- database.
RMAN> mount database;
database mounted
-- All datafiles must be restored, since the controlfile is older than the current
 -- datafiles. Datafile restore must be followed by recovery up to the current log.
RMAN> restore database;
Starting restore at 26/JAN/05
using channel ORA_DISK_1
 channel ORA_DISK_1: starting datafile backupset restore
 channel ORA_DISK_1: specifying datafile(s) to restore from backup set
 restoring datafile 00001 to D:\ORACLE_DATA\DATAFILES\ORCL\SYSTEM01.DBF
 restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF
 channel ORA_DISK_1: restored backup piece 1
 piece handle=E:\BACKUPDGB0I79_1_1.BAK tag=TAG20050124T115832 params=NULL
 channel ORA_DISK_1: restore complete
 channel ORA_DISK_1: starting datafile backupset restore
 channel ORA_DISK_1: specifying datafile(s) to restore from backup set
 restoring datafile 00002 to D:\ORACLE_DATA\DATAFILES\ORCL\UNDOTBS01.DBF
 restoring datafile 00003 to D:\ORACLE_DATA\DATAFILES\ORCL\TOOLS01.DBF
 channel ORA_DISK_1: restored backup piece 1
 piece handle=E:\BACKUPCGB0I78_1_1.BAK tag=TAG20050124T115832 params=NULL
 channel ORA_DISK_1: restore complete
 Finished restore at 26/JAN/05
--Database must be recovered because all datafiles have been restored from
 -- backup
RMAN> recover database;
Starting recover at 26/JAN/05
 using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_2.ARC
 archive log thread 1 sequence 4 is already on disk as file D:\ORACLE_DATA\LOGS\ORCL\REDO02A.LOG
 archive log thread 1 sequence 5 is already on disk as file D:\ORACLE_DATA\LOGS\ORCL\REDO01A.LOG
 archive log thread 1 sequence 6 is already on disk as file D:\ORACLE_DATA\LOGS\ORCL\REDO03A.LOG
 archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_2.ARC thread=1 sequence=2
 archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_3.ARC thread=1 sequence=3
 archive log filename=E:\ORACLE_DATA\LOGS\ORCL\REDO02A.LOG thread=1 sequence=4
 archive log filename=E:\ORACLE_DATA\LOGS\ORCL\REDO01A.LOG thread=1 sequence=5
 archive log filename=E:\ORACLE_DATA\LOGS\ORCL\REDO03A.LOG thread=1 sequence=6
 media recovery complete
 Finished recover at 26/JAN/05
-- Recovery completed. The database must be opened with RESETLOGS
 -- because a backup control file was used. Can also use
 -- "alter database open resetlogs" instead.
RMAN> open resetlogs database;
database opened

Several points are worth emphasising.

  • Recovery using a backup controlfile should be done only if a current control file is unavailable.
  • All datafiles must be restored from backup. This means the database will need to be recovered using archived and online redo logs. These MUST be available for recovery until the time of failure.
  • As with any database recovery involving RESETLOGS, take a fresh backup immediately.

Technically the above is an example of complete recovery – since all committed transactions were recovered. However, some references consider this to be incomplete recovery because the database log sequence had to be reset.

After recovery using a backup controlfile, all temporary files associated with locally-managed tablespaces are no longer available. You can check that this is so by querying the view V$TEMPFILE – no rows will be returned. Therefore tempfiles must be added (or recreated) before the database is made available for general use. In the case at hand, the tempfile already exists so we merely add it to the temporary tablespace. This can be done using SQLPlus or any tool of your choice:

SQL> alter tablespace temp add tempfile
 'D:\oracle_data\datafiles\ORCL\TEMP01.DBF';
Tablespace altered.
SQL>

Check that the file is available by querying v$TEMPFILE.

6. Wrap up:

In an article this size it is impossible to cover all possible recovery scenarios that one might encounter in real life. The above examples will, I hope, provide you with some concrete situations to try out on your test box. The best preparation for real-life recovery is practice. Simulate as many variations of the above situations, and others, as you can think up. Then try recovering from them. The exercise will improve your recovery skills, clarify conceptual issues and highlight deficiencies in your backup strategy.

Source

Reference Links: