Tag: Oracle Database

Oracle Database Security Checklist

Checklist to keep secure an Oracle Database:

1. Install only what is required
The Oracle Database CD pack contains a host of options and products in addition to the database server. Install additional products and options only as necessary.

2. Lock and Expire Default User Accounts
If a manual (i.e. without using Database Configuration Assistant) installation of Oracle Database is performed, then no default database users are locked upon successful installation of the database server. Left open in their default states, these user accounts can be exploited to gain unauthorized access to data or disrupt database operations.

a) Enterprise Manager Accounts
The preceding list of accounts depends on whether you choose to install Enterprise Manager. If so, SYSMAN and DBSNMP are open as well, unless you configure Enterprise Manager for Central Administration. In this case, the SYSMAN account (if present) will be locked as well.

3. Change Default User Passwords
The most trivial method by which Oracle Database can be compromised is a default database server user account which still has a default password associated with it even after installation.

a) Change default passwords of administrative users
Oracle Database 10g enables you to use the same or different passwords for the SYS, SYSTEM, SYSMAN and DBSNMP administrative accounts. Use different passwords for each: in any Oracle environment (production or test), assign strong, secure, and distinct passwords to these administrative accounts.

b) Change default passwords of all users
Each of the accounts install with a default password that is exactly the same as that user account (For example, user MDSYS installs with the password MDSYS).

c) Enforce password management
Oracle recommends that basic password management rules (such as password length, history, complexity, and so forth) as provided by the database be applied to all user passwords and that all users be required to change their passwords periodically.

Oracle also recommends, if possible, using Oracle Advanced Security (an option to the Enterprise Edition of Oracle Database) with network authentication services (such as Kerberos), token cards, smart cards or X.509 certificates.

4. Enable Data Dictionary Protection
Oracle recommends that customers implement data dictionary protection to prevent users having the ANY system privileges from using such privileges on the data dictionary.

To enable dictionary protection, set the following configuration parameter to FALSE, in the init<sid>.ora control file: O7_DICTIONARY_ACCESSIBILITY = FALSE

5. Practicing the principle of least privilege

a) Grant necessary privileges only
The principle of least privilege is that users be given only those privileges that are actually required to efficiently perform their jobs.

To implement this principle, restrict the following as much as possible:
1) The number of SYSTEM and OBJECT privileges granted to database users, and
2) The number of people who are allowed to make SYS-privileged connections to the database.

b) Revoke unnecessary privileges from the public user group
Revoke all unnecessary privileges and roles from the database server user group PUBLIC. PUBLIC acts as a default role granted to every user in an Oracle database.

The more powerful packages that may potentially be misused are:
1. UTL_SMTP 2. UTL_TCP 3. UTL_HTTP 4. UTL_FILE

c) Grant a role to users only if they need all privileges of the role
Ensure that the roles you define contain only the privileges that reflect job responsibility. If your application users do not need all the privileges encompassed by an existing role, then apply a different set of roles that supply just the right privileges.
Altenatively, create and assign a more restricted role.

d)Restrict permissions on run-time facilities
Do not assign all permissions to any database server run-time facility such as the Oracle Java Virtual Machine (OJVM). Grant specific permissions to the explicit document root file paths for such facilities that may execute files and packages outside the database server.

6. Enforce access controls cffectively and authenticate clients stringently

a) Authenticate client properly
By default, Oracle allows operating-system-authenticated logins only over secure connections, which precludes using Oracle Net and a shared server configuration. This default restriction prevents a remote user from impersonating another operating system user over a network connection.

Setting the initialization parameter REMOTE_OS_AUTHENT to TRUE forces the RDBMS to accept the client operating system user name received over a nonsecure connection and use it for account access. Since clients, such as PCs, are not trusted to perform operating system authentication properly, it is very poor security practice to turn on this feature.

7. Restrict Operating System Access
Limit the number of operating system users. Limit the privileges of the operating system accounts (administrative, root-privileged or DBA) on the Oracle Database host (physical machine) to the least privileges needed for the user’s tasks.

Oracle also recommends:
•     Restricting the ability to modify the default file and directory permissions for the Oracle Database home (installation) directory or its contents. Even privileged operating system users and the Oracle owner should not modify these permissions, unless instructed otherwise by Oracle.
•     Restricting symbolic links.

8. Restrict Network Access

a) Use a firewall
Keep the database server behind a firewall. Oracle Database network infrastructure, Oracle Net (formerly known as Net8 and SQL*Net), offers support for a variety of firewalls from various vendors.

b) Never poke a hole through a firewall
If Oracle Database is behind a firewall, then do not, under any circumstances, poke a hole through the firewall.
For example, do not leave open port 1521 for Oracle Listener to make a connection to the Internet or vice versa.

c) Protect the Oracle listener
Because the listener acts as the database gateway to the network, it is important to limit the consequences of malicious interference:
Restrict the privileges of the listener, so that it cannot read or write files in the database or the Oracle server address space.

Secure administration of the database by doing the following:

1. Prevent online administration by requiring the administrator to have write privileges on the LISTENER.ORA file and the listener password:

Add or alter this line in the LISTENER.ORA file ADMIN_RESTRICTIONS_LISTENER=ON
Then RELOAD the configuration.

2. Use SSL when administering the listener, by making the TCPS protocol the first entry in the address list as follows:

LISTENER=
 (DESCRIPTION=
 (ADDRESS_LIST=
 (ADDRESS=
 (PROTOCOL=tcps)
 (HOST = ed-pdsun1.us.oracle.com)
 (PORT = 8281)))

3. Always establish a secure, well-formed password for the Oracle listener to prevent remote configuration of the Oracle listener.
4. Remove the external procedure configuration from the listener.ora file if you do not intend to use such procedures.
5. Monitor listener activity.

d) Monitor who accesses your systems

Instead of authenticating client computers over the Internet, make use of user authentication which avoids client system issues that include falsified IP addresses, hacked operating systems or applications, and falsified or stolen client system identities.

The following steps improve client computer security:
a) Configure the connection to use SSL.
Using SSL (Secure Sockets Layer) communication makes eavesdropping unfruitful and enables the use of certificates for user and server authentication.
b) Set up certificate authentication for clients and servers such that:

i. The organization is identified by unit and certificate issuer and the user is identified by distinguished name and certificate issuer.
ii. Applications test for expired certificates.
iii. Certificate revocation lists are audited.

e) Check network IP addresses

Use the Oracle Net valid node checking security feature to allow or deny access to Oracle server processes from network clients with specified IP addresses. To use this feature, set the following protocol.ora (Oracle Net configuration file) parameters:

tcp.validnode_checking = YES
tcp.excluded_nodes = {list of IP addresses}
tcp.invited_nodes = {list of IP addresses}

The first parameter turns on the feature whereas the latter parameters respectively deny and allow specific client IP addresses from making connections to the Oracle listener (This helps in preventing potential Denial of Service attacks).

f) Encrypt network traffic

If possible, use Oracle Advanced Security to encrypt network traffic between clients, databases, and application servers.

g) Harden the operating system

Harden the host operating system by disabling all unnecessary operating system services. Both UNIX and Windows platforms provide a variety of operating system services, most of which are not necessary for most deployments. Such services include FTP, TFTP, TELNET, and so forth. Be sure to close both the UDP and TCP ports for each service that is being disabled. Disabling one type of port and not the other does not make the operating system more secure.

9. Apply all security patches

Always apply all relevant and current security patches for both the operating system on which Oracle Database resides and Oracle Database itself, and for all installed Oracle Database options and components.

10. Contact Oracle Security products if you come across a vulnerability in Oracle Database

Source: Based on Oracle Documentaion

Plan Oracle Backup Strategy

This article focus on general guidelines that can help to decide:

  • when to perform database backups
  • which parts of a database you should back up
  • what tools Oracle provides for those backups
  • how to configure your database to improve its robustness and make backup and recovery easier

The specifics of the strategy must balance the needs of your restore strategy with questions of cost, resources, personnel and other factors.

1.Protecting the redundancy set

  •  The redundancy set should contain:
    1.  Last backup of the control file and all the datafiles
    2. All archived redo logs generated after the last backup was taken
    3. Duplicates of the current control file and online redo log files, generated by Oracle database multiplexing, operating system mirroring, or both
    4. Copies of configuration files such as the server parameter file, tnsnames.ora, and listener.ora
  • Do not store the redundancy set on the same disk that contains the datafiles, online logs and control files of the database.
  • Make use of a flash recovery area, on a separate device from the working set files and keep it as the ideal location to store backup files and mirrored files.
  • Multiplex the online redo log files and current control file at the database level and the multiplexed files should be on different disks mounted under different disk controllers. Also mirror the online redo logs and current control file at the operating system or hardware level although this is not a substitute for multiplexing at the database level.
  • Archive the redo logs to multiple locations, ideally on different disks if database is running in Archivelog mode.
  • Use operating system or hardware mirroring for the control file.
  • Use operating system or hardware mirroring for the primary datafiles if possible, to avoid having to perform media recovery for simple disk failures.
  • Keep at least one copy of the entire redundancy set including the most recent backup on disk.
  • If the target database is stored on a RAID device, then store the redundancy set on a set of disks that are not in the same RAID device.
  • If you store the redundancy set on tape, then maintain at least two copies of the data to protect against the risk of tape failure.

2. Flash Recovery Area

  • It is recommended that you take advantage of the flash recovery area to store as many backup and recovery -related files as as possible, including disk backups and archived redo logs.
  • Obsolete files no longer needed to meet the recoverability goals and files backed up to tape or other media become eligible for deletion and are deleted when space is needed.

3. ARCHIVELOG and NOARCHIVELOG Mode

  • Database running in ARCHIVELOG mode is preferable to running in NOARCHIVELOG mode because  it provides more flexible recovery options after a data loss, such as point-in-time recovery of the database or some tablespaces.
  • In NOARCHIVELOG mode, one can’t perform online backups of the database and have to shut database down cleanly before backup is taken. Also any data recovery techniques that require the archived redo logs can’t be used.
  • The stored archived redo logs must be managed such that limited disk space is alloted for archived redo logs and older logs which are no longer needed to meet recoverability goals are deleted.

4. Oracle Flashback Features and Restore Points

  • Using the flashback features of Oracle improves the availability of the database when repairing the effects of unwanted database changes. The logical-level flashback features allow the objects not affected to remain available, and Flashback Database allows for faster rewind of the entire database than point-in-time recovery.
  • Incorporating Flashback Database or guaranteed restore points into the strategy requires to enable a flash recovery area, as well as creating guaranteed restore points at the right points in time, or configuring flashback logging.
5. Backup Retention Policy
  • RMAN automates the implementation of a backup retention policy, using the following commands:
    1. CONFIGURE RETENTION POLICY command lets to set the retention policy that will apply to all of your database files by default.
    2. REPORT OBSOLETE command lets to list backups currently on disk that are obsolete under the retention policy.
    3. DELETE OBSOLETE command deletes the files which REPORT OBSOLETE lists as obsolete.
    4. CHANGE… KEEP lets to set a separate retention policy for specific backups, such as long-term backups kept for archival purposes.
  • Redundancy based retention policy
    1. In a redundancy-based retention policy, you a number n is specified such that you always keep at least n distinct backups of each file in the database.
    2. A redundancy-based backup retention policy determines whether a backup is obsolete based on how many backups of a file are currently on disk.
  • Recovery window-based retention policy
    1. In a recovery window-based retention policy, a time interval is specified in the past and keep all backups required to let you perform point-in-time recovery to any point during that window.
    2. A recovery window-based retention policy lets you guarantee that you can perform point-in-time recovery to any point in the past, up to a number of days that you specify.

6. Archiving Older Backups

  • An older backup of datafiles and archived logs is necessary for performing point-in-time recovery to a time before your most recent backup.
  • If your most recent backup is corrupt, you can still recover your database using an older backup and the complete set of archived logs since that older backup.
  • Keep a copy of the database for archival purposes.

7. Frequency of backups

  • Base the frequency of backups on the rate or frequency of database changes such as:
    1. Addition and deletion of tables
    2. Insertions and deletions of rows in existing tables
    3. Updates to data within tables
  • The more frequently your database is updated, the more often you should perform database backups.
  • If database updates are relatively infrequent, then you can make whole database backups infrequently and supplement them with incremental backups.

8.  Backups before and after structural changes

  • If you make any of the following structural changes, then perform a backup of the appropriate portion of your database immediately before and after completing the following changes:
    1. Create or drop a tablespace.
    2. Add or rename a datafile in an existing tablespace.
    3. Add, rename, or drop an online redo log group or member.
  • If you are in NOARCHIVELOG mode, then you must shut down the database and perform a consistent whole database backup after any such change.
  • If you are running in ARCHIVELOG mode, then you must make a control file backup after any such change, using either RMAN’s BACKUP CONTROLFILE command or the SQL ALTER DATABASE BACKUP CONTROLFILE statement.

9. Scheduling Backups for Frequently-Updated Tablespaces

  • If you run in ARCHIVELOG mode, then you can back up an individual tablespace or even a single datafile. You might want to do this for one or more tablespaces that are updated much more often than the rest of your database, as is sometimes the case for the SYSTEM tablespace and automatic undo tablespaces.

10. Backing Up after NOLOGGING Operations

  • When a direct path load is performed to populate a database, no redo data is logged for those database changes. You cannot recover these changes after a restore from backup using conventional media recovery.
  • Likewise, when tables and indexes are created as NOLOGGING, the database does not log redo data for these objects, which means that you cannot recover these objects from existing backups. Therefore, you should back up your datafiles after operations for which no redo data is logged.

11. Exporting Data for Added Protection and Flexibility

  • Oracle database import and export utilities are used to export database objects (tables, stored procedures, and so forth) from databases to be stored as files, and re-import objects from those files.
  • An export provides a logical-level snapshot of the exported objects at the time of the export, as a binary file that can be imported back into the source database or some other database.

12. Preventing the Backup of Online Redo Logs

  • Online redo logs, unlike archived logs, should never be backed up. The chief danger associated by having backups of online redo logs is that you may accidentally restore those backups without meaning to, and corrupt your database.
  • The best method for protecting the online logs against media failure is to multiplex them, with multiple log members in each group, on different disks attached to different disk controllers.

13. Keeping Records of the Hardware and Software Configuration of the Server

  • You should have the following documentation about the hardware configuration:
    1. The name, make, and model of the machine that hosts the database
    2. The version and patch of the operating system
    3. The number of disks and disk controllers
    4. The disk capacity and free space
    5. The names of all datafiles
    6. The name and version of the media management software (if you use a third-party media manager)
  • You should also keep the following documentation about the software configuration:
  1. The name of the database instance (SID)
  2. The database identifier (DBID)
  3. The version and patch release of the Oracle database server
  4. The version and patch release of the networking software
  5. The method (RMAN or user-managed) and frequency of database backups
  6. The method of restore and recovery (RMAN or user-managed
Article based on the Oracle Doc – Planning Backup Strategy

Point in Time Recovery Using RMAN

Point in time recovery using RMAN – untill a log sequence number

1.Recovery Objective

SQL> conn scott/tiger
 Connected.
 SQL> select count(*) from myobjects;
COUNT(*)
 ----------
 249410

2.Switch a logfile

SQL> conn / as sysdba
 Connected.
 SQL> alter system switch logfile;
System altered.

3.Note the current log sequence number (13)

SQL> archive log list
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination /u02/ORACLE/opsdba/arch
 Oldest online log sequence 12
 Next log sequence to archive 14
 Current log sequence 14

4.Simulate an application failure – WRONG Delete!!

SQL> conn scott/tiger
 Connected.
 SQL> delete myobjects;
249410 rows deleted.
SQL> commit;
Commit complete.

The developer states that the wrong DML statement was made AFTER 8.15 AM and is positive about the same.

We need to determine the log sequence we need to recover until

select sequence#,first_change#, to_char(first_time,'HH24:MI:SS')
from v$log order by 3
 SQL> /
SEQUENCE# FIRST_CHANGE# TO_CHAR(
 ---------- ------------- --------
 13 2760463 07:49:36
 14 2761178 08:12:47
 15 2766622 08:18:49

Log sequence 14 was first written to at 8:12 AM so we should recover to a log sequence before this – i.e sequence# 13

5. Shutdown and mount the database

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

 SQL> startup mount;
 ORACLE instance started.
Total System Global Area 264241152 bytes
 Fixed Size 2070416 bytes
 Variable Size 163580016 bytes
 Database Buffers 92274688 bytes
 Redo Buffers 6316032 bytes
 Database mounted.

 RMAN> run {
 2> set until sequence=14; >>> add one to the sequence number we have to recover until
 3> restore database;
 4> recover database;
 5> }
executing command: SET until clause
Starting restore at 29-JAN-07
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: sid=154 devtype=DISK
 allocated channel: ORA_SBT_TAPE_1
 channel ORA_SBT_TAPE_1: sid=158 devtype=SBT_TAPE
 channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.2.4.0
channel ORA_DISK_1: starting datafile backupset restore
 channel ORA_DISK_1: specifying datafile(s) to restore from backup set
 restoring datafile 00001 to /u02/ORACLE/opsdba/system01.dbf
 restoring datafile 00002 to /u02/ORACLE/opsdba/undotbs01.dbf
 restoring datafile 00003 to /u02/ORACLE/opsdba/sysaux01.dbf
 restoring datafile 00004 to /u02/ORACLE/opsdba/users01.dbf
 restoring datafile 00005 to /u02/ORACLE/opsdba/users02.dbf
 restoring datafile 00006 to /u02/ORACLE/opsdba/users03.dbf
 restoring datafile 00007 to /u02/ORACLE/opsdba/users05.dbf
 restoring datafile 00008 to /u02/ORACLE/opsdba/users06.dbf
 restoring datafile 00009 to /u02/ORACLE/opsdba/users07.dbf
 restoring datafile 00010 to /u02/ORACLE/opsdba/users04.dbf
 restoring datafile 00011 to /u02/ORACLE/opsdba/drtbs1.dbf
 restoring datafile 00012 to /u02/ORACLE/opsdba/drtbs2.dbf

 channel ORA_DISK_1: reading from backup piece
 /opt/oracle/backup/opsdba/OPSDBA.20070129.161.1.1.613122551
 channel ORA_DISK_1: 
 restored backup piece 1
 piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070129.161.1.1.613122551 
 tag=TAG20070129T074911
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
 Finished restore at 29-JAN-07
Starting recover at 29-JAN-07
 using channel ORA_DISK_1
 using channel ORA_SBT_TAPE_1
starting media recovery
archive log thread 1 sequence 13 is already on disk as file 
 /u02/ORACLE/opsdba/arch/arch_1_13_613052894.dbf
 channel ORA_DISK_1: starting archive log restore to default destination
 channel ORA_DISK_1: restoring archive log
 archive log thread=1 sequence=12
 channel ORA_DISK_1: reading from backup piece 
 /opt/oracle/backup/opsdba/OPSDBA.20070129.162.1.1.613122577
 channel ORA_DISK_1: restored backup piece 1
 piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070129.162.1.1.613122577 
 tag=TAG20070129T074937
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
 archive log filename=/u02/ORACLE/opsdba/arch/arch_1_12_613052894.dbf thread=1 sequence=12
 archive log filename=/u02/ORACLE/opsdba/arch/arch_1_13_613052894.dbf thread=1 sequence=13
 media recovery complete, elapsed time: 00:00:01
 Finished recover at 29-JAN-07
RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs

6.Confirm that the recovery has worked

 opsdba:/opt/tivoli/tsm/client/oracle/bin64>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jan 29 09:43:14 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
 Connected to:
 Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
 With the Partitioning, Oracle Label Security, OLAP and Data Mining options
SQL> select count(*) from myobjects;
COUNT(*)
 ----------
 249410

Source

Reference links:

Running Oracle database inside Amazon Cloud

Databases in Cloud  Patrick Schwanke

Lots of people talk about cloud nowadays. Working with Oracle databases for about 9 years, I wanted to see how Oracle database would work inside Amazon Web Services cloud and how to get this running from the scratch.

During the next days I will post some experiences and stumbling blocks and other howto related stuff:

  • How to appropriately set up your AWS account and environment: Step 1
  • Amazon Cloud Basics for Oracle DBAs: Step 2
  • How to create an Oracle instance: Step 3
  • How to access my Oracle instance in the cloud (SSH, RDP, File Transfer, Listener, SQL*Plus): Step 4
  • How to do some very useful automation / scripting
  • How about performance in the cloud?
  • How to do backups inside the cloud or into the cloud

Related Articles:

Monitoring Oracle databases running on Amazon EC2

Monitoring Oracle databases running on Amazon RDS

Source

Links:

Databases in Cloud

Amazon AWS

RMAN – Database Recovery

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_DATADATAFILESORCLUSERS01.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_DATADATAFILESORCLUSERS01.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_ARCHIVEORCL1_4.ARC
 archive log thread 1 sequence 5 is already on disk as file C:ORACLE_ARCHIVEORCL1_5.ARC
 archive log thread 1 sequence 6 is already on disk as file E:ORACLE_ARCHIVEORCL1_6.ARC
 archive log thread 1 sequence 7 is already on disk as file E:ORACLE_ARCHIVEORCL1_7.ARC
 archive log filename=E:ORACLE_ARCHIVEORCL1_4.ARC thread=1 sequence=4
 archive log filename=C:ORACLE_ARCHIVEORCL1_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_DATADATAFILESORCLUSERS01.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_DATALOGSORCLREDO03A.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_DATADATAFILESORCLSYSTEM01.DBF
 restoring datafile 00004 to D:ORACLE_DATADATAFILESORCLUSERS01.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_DATADATAFILESORCLUNDOTBS01.DBF
 restoring datafile 00003 to D:ORACLE_DATADATAFILESORCLTOOLS01.DBF
 channel ORA_DISK_2: restored backup piece 1
 piece handle=E:BACKUP13GB14IB_1_1.BAK tag=TAG20050124T171139 params=NUL
 channel ORA_DISK_2: restore complete
 channel ORA_DISK_1: restored backup piece 1
 piece handle=E:BACKUP14GB14IB_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_ARCHIVEORCL1_9.ARC
 archive log thread 1 sequence 10 is already on disk as file E:ORACLE_ARCHIVEORCL1_10.ARC
 archive log thread 1 sequence 11 is already on disk as file E:ORACLE_ARCHIVEORCL1_11.ARC
 archive log thread 1 sequence 12 is already on disk as file E:ORACLE_ARCHIVEORCL1_12.ARC
 archive log filename=E:ORACLE_ARCHIVEORCL1_9.ARC thread=1 sequence=9
 archive log filename=E:ORACLE_ARCHIVEORCL1_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_destcontrolfileORCLcontrol02.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:backupCTL_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_DATACONTROLFILEORCLCONTROL01.CTL
 output filename=E:ORACLE_DUP_DESTCONTROLFILEORCLCONTROL02.CTL
 output filename=C:ORACLE_DUP_DESTCONTROLFILEORCLCONTROL03.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_DATADATAFILESORCLSYSTEM01.DBF
 restoring datafile 00004 to D:ORACLE_DATADATAFILESORCLUSERS01.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_DATADATAFILESORCLUNDOTBS01.DBF
 restoring datafile 00003 to D:ORACLE_DATADATAFILESORCLTOOLS01.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_ARCHIVEORCL1_2.ARC
 archive log thread 1 sequence 4 is already on disk as file D:ORACLE_DATALOGSORCLREDO02A.LOG
 archive log thread 1 sequence 5 is already on disk as file D:ORACLE_DATALOGSORCLREDO01A.LOG
 archive log thread 1 sequence 6 is already on disk as file D:ORACLE_DATALOGSORCLREDO03A.LOG
 archive log filename=E:ORACLE_ARCHIVEORCL1_2.ARC thread=1 sequence=2
 archive log filename=E:ORACLE_ARCHIVEORCL1_3.ARC thread=1 sequence=3
 archive log filename=E:ORACLE_DATALOGSORCLREDO02A.LOG thread=1 sequence=4
 archive log filename=E:ORACLE_DATALOGSORCLREDO01A.LOG thread=1 sequence=5
 archive log filename=E:ORACLE_DATALOGSORCLREDO03A.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_datadatafilesORCLTEMP01.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: