Remove Oracle Clusterware Software

Perform the following steps on each server to Remove Oracle Clusterware Software
rm -rvf /etc/oracle/*
rm -vf /etc/init.d/init.crs
rm -vf /etc/init.d/init.crsd
rm -vf /etc/init.d/init.cssd
rm -vf /etc/init.d/init.evmd
rm -vf /etc/rc2.d/*.crs
rm -vf /etc/rc3.d/*.crs
rm -vf /etc/rc5.d/*.crs
rm -vf /etc/inittab.crs
cp inittab inittab.old
cp inittab.orig inittab
ls -l inittab*
cd /crs/app/oracle/product/
rm -rf crs/*

Bonding

Bonding is a method of aggregating multiple network interfaces into a single logical bonded interface.

Oracle best practice is to use bonding using round-robin and load balancing. The change is to improve High Availability.

Oracle does not recommend setting the CLUSTER_INTERCONNECTS parameter, which overrides the default interconnect settings at the operating system level. Instead, the best practice is to use operating system bonding techniques (also referred to as NIC (network interface card) bonding). Platform-specific Oracle RAC installation guide need to verified for information about setting up NIC bonding at the operating system level.

Check speed of interconnect (bonding).

# cd /proc/net/bonding/

# cat bond0

Related articles

• Network in RAC (oramasters.com)

Configure RAC Nodes for Remote Access

Perform the following configuration procedures on both Oracle RAC nodes in the cluster

Before installation and use of Oracle Real Application clusters, secure shell (SSH) must be configured for the "oracle" UNIX user account on all cluster nodes. User equivalence enables the "oracle" UNIX user account to access all other nodes in the cluster (running commands and copying files) without the need for a password. This can be configured using SSH.

This section describes how to configure SSH.

To determine if SSH is installed and running, enter the following command on both Oracle RAC nodes:

# pgrep sshd

(If SSH is running, then the response to this command is a list of process ID number(s)). 

Creating RSA and DSA Keys on Both Oracle RAC Nodes

The first step in configuring SSH is to create RSA and DSA key pairs on both Oracle RAC nodes in the cluster. The command to do this will create a public and private key for both RSA and DSA (for a total of four keys per node). The content of the RSA and DSA public keys will then need to be copied into an authorized key file which is then distributed to both Oracle RAC nodes in the cluster.

Use the following steps to create the RSA and DSA key pairs. Please note that these steps will need to be completed on both Oracle RAC nodes in the cluster:

Logon as the "oracle" UNIX user account.

# su - oracle

If necessary, create the .ssh directory in the "oracle" user's home directory and set the correct permissions on it:

$ mkdir -p ~/.ssh

$ chmod 700 ~/.ssh

Enter the following command to generate an RSA key pair (public and private key) for version 3 of the SSH protocol:

$ /usr/bin/ssh-keygen -t rsa

At the prompts:

• Accept the default location for the key files.
• Enter and confirm a pass phrase. This should be different from the "oracle" UNIX user account password however it is not a requirement.
• This command will write the public key to the ~/.ssh/id_rsa.pub file and the private key to the ~/.ssh/id_rsa file. Note that you should never distribute the private key to anyone!

Enter the following command to generate a DSA key pair (public and private key) for version 3 of the SSH protocol:

$ /usr/bin/ssh-keygen -t dsa

At the prompts:

• Accept the default location for the key files.
• Enter and confirm a pass phrase. This should be different from the "oracle" UNIX user account password however it is not a requirement.
• This command will write the public key to the ~/.ssh/id_dsa.pub file and the private key to the ~/.ssh/id_dsa file. Note that you should never distribute the private key to anyone!

Repeat the above steps for both Oracle RAC nodes in the cluster.

Now that both Oracle RAC nodes contain a public and private key for both RSA and DSA, you will need to create an authorized key file on one of the nodes. An authorized key file is nothing more than a single file that contains a copy of everyone's (every node's) RSA and DSA public key. Once the authorized key file contains all of the public keys, it is then distributed to all other nodes in the cluster.

Complete the following steps on one of the nodes in the cluster to create and then distribute the authorized key file:

First, determine if an authorized key file already exists on the node (~/.ssh/authorized_keys). In most cases this will not exist since this a new install. If the file doesn't exist, create it now:

$ touch ~/.ssh/authorized_keys

$ cd ~/.ssh

$ ls -l *.pub

-rw-r--r--  1 oracle oinstall 603 Aug 31 23:40 id_dsa.pub

-rw-r--r--  1 oracle oinstall 223 Aug 31 23:36 id_rsa.pub

The listing above should show the id_rsa.pub and id_dsa.pub public keys created in the previous section.

In this step, use SSH to copy the content of the ~/.ssh/id_rsa.pub and ~/.ssh/id_dsa.pub public key from both Oracle RAC nodes in the cluster to the authorized key file just created (~/.ssh/authorized_keys). Again, this will be done from another node. It will prompt for the "oracle" UNIX user account password for both Oracle RAC nodes accessed. Notice that when using SSH to access the node, the first time prompts for the "oracle" UNIX user account password. The second attempt at accessing this node will prompt for the pass phrase used to unlock the private key. For any of the remaining nodes, it will always ask for the "oracle" UNIX user account password.

The following example is being executed from xxxx1.com and assumes a two-node cluster, with nodes xxxx1.com and xxxx2.com:

$ cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

$ cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

Note: The first time you use SSH to connect to a node from a particular system, you may see a message similar to the following:

The authenticity of host ‘xxxx1.com’can't be established.

RSA key fingerprint is 61:8a:f9:9e:28:a2:b7:d3:70:8d:dc:76:ca:d9:23:43.

Are you sure you want to continue connecting (yes/no)? yes

Enter yes at the prompt to continue. You should not see this message again when you connect from this system to the same node.

At this point, we have the content of the RSA and DSA public keys from every node in the cluster in the authorized key file (~/.ssh/authorized_keys) on xxxx1.com. We now need to copy it to the remaining nodes in the cluster. Use the scp command to copy the authorized key file to all remaining nodes in the cluster:

$ scp ~/.ssh/authorized_keys xxxx2.com:.ssh/authorized_keys

oracle@ xxxx2.com’s password: xxxxx

authorized_keys                          100% 1652 1.6KB/s 00:00

Change the permission of the authorized key file for both Oracle RAC nodes in the cluster by logging into the node and running the following:

$ chmod 600 ~/.ssh/authorized_keys

At this point, if you use SSH to log in to or run a command on another node, you are prompted for the pass phrase that you specified when you created the DSA key. For example, test the following from any on of the node:

$ ssh xxxx1.com hostname

    xxxx1.com

$ ssh xxxx2.com hostname

  xxxx2.com

At this point, if you use SSH to log in to or run a command on another node, you are prompted.

Allow root to login using SSH keys. For that PermitRootLogin should be set to without-password or not set in sshd_config. Test the setting as shown below.

# cat /etc/ssh/sshd_config | grep PermitRootLogin

PermitRootLogin without-password 

Related articles

• Configuring Basic Security | Knowledge Center | Rackspace Hosting (rackspace.com)
• SSH Keys (wiki.archlinux.org)

RAC

--Display cluster status
crs_stat –t

--Start all offline services
crs_start –all

--See the crs configuration
crs_start –p

--Start nodeapps (listener, VIP, ONS and GSD)
srvctl start nodeapps -n

--Stop nodeapps (listener, VIP, ONS and GSD)
srvctl stop nodeapps -n

--Start ASM
srvctl start asm -n

--Stop ASM
srvctl stop asm -n

--Start instance
srvctl start instance -d-i

--Stop instance
srvctl stop instance -d-i >

--Start the cluster on a node
sudo su -
/etc/init.crs start 

--Stop the cluster on a node
sudo su -
/etc/init.crs stop 

--Disable the cluster from starting after reboot
sudo su -
/etc/init.crs disable

--Enable the cluster to automatically start after reboot
sudo su -
/etc/init.crs enable

--Get session details from SID
set linesize 130
set pages 1000
col sid format 999999
col username format a20
col osuser format a15
select a.sid, a.serial#,a.username, a.inst_id, a.osuser, b.spid
from gv$session a, gv$process b
where a.paddr= b.addr
and a.sid='&sid'
order by a.inst_id;

--Get session details from OS process id
set linesize 130
set pages 1000
col sid format 999999
col username format a20
col osuser format a15
select a.sid, a.serial#,a.username, a.inst_id, a.osuser, b.spid
from gv$session a, gv$process b
where a.paddr= b.addr
and b.spid='&spid'
order by a.inst_id;

--Check session status and count in all nodes in RAC
set linesize 130
set pages 1000
select gvinst.INSTANCE_NAME,gvsess.TYPE,gvsess.STATUS,COUNT(*) from gv$session gvsess,gv$instance gvinst where gvsess.INST_ID=gvinst.INSTANCE_NUMBER group by gvinst.INSTANCE_NAME,gvsess.TYPE,gvsess.STATUS order by gvinst.INSTANCE_NAME;

Statistics

Statistics is a meta-data that contains information about database and database object. This meta-data is used by query optimizer to generate best execution plan. 

To gather statistics for a table

EXEC dbms_stats.gather_table_stats(‘SCOTT’,’EMP’,cascade=>TRUE);

Scott – schema
EMP – table
cascade => true will collect statistics for all related objects for the table like index.

To gather statistics for a schema

EXEC dbms_stats.gather_schema_stats(’SCOTT’, cascade=>TRUE);

scott – schema

To see the statistics for table

Use DBA_TABLES to view complete statistics for a table. Else at user level use the view “all_tab_statistics” to view the table statistics.

Example

select * from dba_tables where table_name= upper(‘mscompare_input_temp’);
select * from all_tab_statistics where table_name = upper(‘mscompare_input_temp’);

Query Redo Log Switch Rate

SET LINES 100
SET PAGESIZE 9999
SET VERIFY    OFF
COL DAY FORMAT a15;
COL HOUR FORMAT a4;
COL TOTAL FORMAT 999;

SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,
TO_CHAR(FIRST_TIME,'HH24') HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')
ORDER BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')
ASC;

GOLDEN GATE CONCEPT AND ARCHITECTURE

INTRODUCTION

- Oracle Golden Gate is a tool provided by oracle for transactional data replication among oracle databases and other RDBMS tools (SQL SERVER, DB2.Etc).
- Golden Gate provides High Availability, Data Integration, Zero downtime upgrade and migration, Live reporting database etc
- Oracle Golden Gate server runs on both source and target server.
- Oracle Golden Gate is installed as an external component to the database and it won’t uses database resource, in turn it won’t affect database performance. Where as Oracle streams which uses built-in packages which are provided by oracle, which uses most of the database resources and there are chances of performance slow down in both source and target databases.

Golden Gate process architecture is given below.

EXTRACT

- Extract runs on the source system and it  capture the changes which happens at the source database.
- The Extract process extracts the necessary data from the database transaction logs ( REDO logs). Unlike streams which runs in the oracle database itself and needs the access to the database. Oracle Golden Gate does not needs access to the oracle database and also it will extract only the committed transaction from the online redo log file.
- When ever there is a long running transaction which generates more number of redo data will force to switch the redo log file and in turn more number of archive logs will be generated. In these cases the extract process need to read the archive log files to get the data.
- Extract process captures all the changes that are made to objects that are configured for synchronization.  Multiple Extract processes can operate on different objects at the same time. For example once process could continuously extract transactional data changes and stream them to a decision support database  while another process performs batch extracts for periodic reporting or, two extract processes could extract and transmit in parallel to two replicat processes ( with two trails) to minimize target latency when the databases are large.

DATAPUMP

 - Datapump is the secondary extract process with in source Oracle Golden Gate configuration. You can have the source Oracle Golden Gate configured without Datapump process also, but in this case Extract process has to send the data to trail file at the target. If the Datapump is configured the primary extract process writes the data to the source trail file and Datapump will read this trail file and propagate the data over the network to target trail file. The Datapump adds the storage flexibility and it isolates the primary extract process from TCP/IP activity.
 - You can configure the primary extract process and Datapump extract to extract online or extract during batch processing.

REPLICAT

 - Replicat process runs on the target system. Replicat reads extracted transactional data changes and DDL changes that are specified in the Replicat configuration, and then it replicates them to the target database.

TRAILS OR EXTRACTS

 - To support the continuous extraction and replication of source database changes, Oracle Golden Gate stores the captured changes temporarily on the disk in a series of files call a TRAIL. A trail can exist on the source or target system and even it can be in an intermediate system, depending on how the configuration is done. On the local system it is known as an EXTRACT TRAIL and on the remote system it is known as REMOTE TRAIL.
- The use of a trail also allows extraction and replication activities to occur independently of each other. Since these two ( source trail and target trail) are independent you have more choices for how data is delivered.

CHECKPOINT

- Checkpoints stores the current read and write positions of a process to disk for recovery purposes. These checkpoints ensure that data changes that are marked for synchronization are extracted by extract and replicated by replicat.
- Checkpoint work with inter process acknowledgments to prevent messages from being lost in the network.  Oracle  Golden Gate has a proprietary guaranteed-message delivery technology.
- Checkpoint information is maintained in checkpoint files within the dirchk sub-directory of the Oracle Golden Gate directory. Optionally, Replicat checkpoints can be maintained in a checkpoint table within the target database, apart from standard checkpoint file.

MANAGER

- The Manager process runs on both source and target systems and it is the heart or control process of Oracle Golden Gate. Manager must be up and running before you create EXTRACT or REPLICAT process. Manager performs Monitoring, restarting oracle golden gate process, report errors, report events, maintains trail files and logs etc.

COLLECTOR

- Collector is a process that runs in the background on the target system. Collector receives extracted database changes that are sent across the TCP/IP network and it writes them to a trail or extract file.

Related articles

• Zero Downtime Migration (slideshare.net)

ORACLE DATABASE CLONING

Below are the are steps to clone a oracle database

Connect to target system and execute

sqlplus / as sysdba

alter database backup controlfile to trace;

Get file from /u01/oracle/admin/oldlsq/udump/xxxx.sql

It will look something like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
 

Shutdown the target database

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.

cp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
cp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
cp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
cp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq
 

Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

Old:

CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

New:

CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS

Remove the “recover database” and “alter database open” syntax

# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN; 

Re-names of the data files names that have changed.

Old:

DATAFILE'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'

New:

DATAFILE'/u01/oradata/newlsq/system01.dbf',
'/u01/oradata/newlsq/mydatabase.dbf'

Create the bdump, udump and cdump ,adump,pfile,dpdump directories

Copy-over the old init.ora file

cp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile

Modify the init.ora with the new locations and db name

Run the modified control file script.

sqlplus / as sysdba

startup pfile=’loaction of your pfile’ nomount;

Run the modified script to create control file

@db_create_controlfile.sql

alter database mount;

alter database open resetlogs;

Oracle Processes

Oracle background process (Photo credit: Wikipedia)

Server Processes

Oracle creates server processes to handle the requests of user processes connected to the instance. In some situations when the application and Oracle operate on the same machine, it is possible to combine the user process and corresponding server process into a single process to reduce system overhead. However, when the application and Oracle operate on different machines, a user process always communicates with Oracle through a separate server process.

Server processes (or the server portion of combined user/server processes) created on behalf of each user’s application can perform one or more of the following:

• Parse and run SQL statements issued through the application
• Read necessary data blocks from datafiles on disk into the shared database buffers of the SGA, if the blocks are not already present in the SGA
• Return results in such a way that the application can process the information

Background Processes

To maximize performance and accommodate many users, a multiprocess Oracle system uses some additional Oracle processes called background processes.

An Oracle instance can have many background processes; not all are always present. The background processes in an Oracle instance include the following:

• Database Writer (DBW0 or DBWn)
• Log Writer (LGWR)
• Checkpoint (CKPT)
• System Monitor (SMON)
• Process Monitor (PMON)
• Archiver (ARCn)
• Recoverer (RECO)
• Lock Manager Server (LMS) – Real Application Clusters only
• Queue Monitor (QMNn)
• Dispatcher (Dnnn)
• Server (Snnn)

On many operating systems, background processes are created automatically during instance startup.

Semaphore

A semaphore can be thought of as a counter that is used to control access to a shared resource. Semaphores provide low level synchronization between processes (or threads within a process) so that only one process (or thread) has access to the shared segment, thereby ensuring the integrity of the shared resource.

Oracle recommeds setting some of the semaphore parameters to the largest PROCESS instance parameter setting in the database parameter files for all database hosted in the Linux system plus 10.

In Linux environments semaphore is controlled by parameter kernel.sem. This parameter is set along with other parameters in the configuration file /etc/sysctl.conf.

Following commands helps to check the current setting of semaphore parameter

# cat /etc/sysctl.conf | grep sem

# sysctl -a | grep kernel.sem

# ipcs -l

Example

# cat /etc/sysctl.conf | grep sem

kernel.sem = 250 32000 100 128

# sysctl -a | grep kernel.sem

kernel.sem = 250 32000 100 128

# ipcs -l

------ Shared Memory Limits --------

max number of segments = 4096

max seg size (kbytes) = 67108864

max total shared memory (kbytes) = 100663296

min seg size (bytes) = 1

------ Semaphore Limits --------

max number of arrays = 1000

max semaphores per array = 1000

max semaphores system wide = 32000

max ops per semop call = 1000

semaphore max value = 32767

------ Messages: Limits --------

max queues system wide = 16

max size of message (bytes) = 65536

default max size of queue (bytes) = 65536

To change the semaphore settings, the "kernel.sem" parameter value need to be changed in the /etc/sysctl.conf. This change wont take effect until server is rebooted. In order to make the change to take effect immediately the following command need to be executed.

# sysctl -p

USER MANAGED RECOVERY

About User-Managed Restore Operations

Backup and Restore (Photo credit: Wikipedia)

To restore a file is to replace it with a backup file. Typically, you restore a file when a media failure or user error has damaged or deleted the original file. The following files are candidates for restore operations:

·         Datafiles and control files

·         Archived redo logs

·         Server parameter file

In each case, the loss of a primary file and the restore of a backup has the following implications for media recovery.

If you lose . . .	Then . . .
One or more datafiles	You must restore them from a backup and perform media recovery. Recovery is required whenever the checkpoint SCN in the datafile header does not match the checkpoint SCN for the datafile that is recorded in the control file.
All copies of the current control file	You must restore a backup control file and then open the database with the RESETLOGS option. If you do not have a backup, then you can attempt to re-create the control file. If possible, use the script included in the ALTER DATABASE BACKUP CONTROLFILE TO TRACE output. Additional work may be required to match the control file structure with the current database structure.
One copy of a multiplexed control file	Copy one of the intact multiplexed control files into the location of the damaged or missing control file and open the database. If you cannot copy the control file to its original location, then edit the initialization parameter file to reflect a new location or remove the damaged control file. Then, open the database.
One or more archived logs required for media recovery	You must restore backups of these archived logs for recovery to proceed. You can restore either to the default or nondefault location. If you do not have backups, then you must performing incomplete recovery up to an SCN before the first missing redo log and open RESETLOGS.
The server parameter file	If you have a backup of the server parameter file, then restore it. Alternatively, if you have a backup of the client-side initialization parameter file, then you can restore a backup of this file, start the instance, and then re-create the server parameter file.

Note:

Restore and recovery of Oracle-managed files is no different from restore and recovery of user-named files.

Determining Which Datafiles Require Recovery

Use the following SQL*Plus commands to format the output of the query:

COL DF# FORMAT 999

COL DF_NAME FORMAT A35

COL TBSP_NAME FORMAT A7

COL STATUS FORMAT A7

COL ERROR FORMAT A10

COL CHANGE# FORMAT 99999999

SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,

       d.STATUS, r.ERROR, r.CHANGE#, r.TIME

FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t

WHERE t.TS# = d.TS#

AND d.FILE# = r.FILE#

;

The ERROR column identifies the problem for each file requiring recovery.

Restoring Datafiles and Archived Redo Logs

SQL>ALTER TABLESPACE users OFFLINE IMMEDIATE;

Copy backups of the damaged datafiles to their default location using operating system commands.

% cp /disk2/backup/users01.dbf $ORACLE_HOME/oradata/trgt/users01.dbf

Recover the affected tablespace.

SQL>RECOVER TABLESPACE users

Bring the recovered tablespace online.

SQL>ALTER TABLESPACE users ONLINE;

Restoring Archived Redo Logs with Operating System Utilities

The database locates the correct log automatically when required during media recovery.

% cp /disk2/arch/* $ORACLE_HOME/oradata/trgt/arch

If sufficient space is not available at the location indicated by the archiving destination initialization parameter, restore some or all of the required archived redo log files to an alternate location. Specify the location before or during media recovery using the LOGSOURCE parameter of the SET statement in SQL*Plus or the RECOVER ... FROM parameter of the ALTER DATABASE statement in SQL. For example, enter:

SET LOGSOURCE /tmp   # set location using SET statement

  DATABASE RECOVER FROM '/tmp';  # set location in RECOVER statement

After an archived log is applied, and after making sure that a copy of each archived log group still exists in offline storage, delete the restored copy of the archived redo log file to free disk space. For example:

% rm /tmp/*.dbf

Restoring Control Files

Restore Lost Copy of a Multiplexed Control File

Use the following procedures to recover a database if a permanent media failure has damaged one or more control files of a database and at least one control file has not been damaged by the media failure.

Copying a Multiplexed Control File to a Default Location

shut down

SQL>SHUTDOWN

Use an intact multiplexed copy of the database's current control file to copy over the damaged control files. For example, to replace bad_cf.f with good_cf.f, you might enter:

% cp /oracle/good_cf.f /oracle/dbs/bad_cf.f

Start a new instance and mount and open the database.

SQL>STARTUP

Copying a Multiplexed Control File to a Nondefault Location

shut down

SQL>SHUTDOWN

% cp $ORACLE_HOME/oradata/trgt/control01.dbf /new_disk/control01.dbf

Edit the parameter file of the database so that the CONTROL_FILES parameter

CONTROL_FILES='/oracle/oradata/trgt/control01.dbf','/bad_disk/control02.dbf'

Then, you can edit it as follows:

CONTROL_FILES='/oracle/oradata/trgt/control01.dbf','/new_disk/control02.dbf'

Start a new instance and mount and open the database.

SQL>STARTUP

Restore Control File from Backup After Loss of All Current Control Files

Status of Online Logs	Status of Datafiles	Restore Procedure
Available	Current	If the online logs contain redo necessary for recovery, then restore a backup control file and apply the logs during recovery. You must specify the filename of the online logs containing the changes in order to open the database. After recovery, open RESETLOGS.
Unavailable	Current	If the online logs contain redo necessary for recovery, then re-create the control file. Because the online redo logs are inaccessible, open RESETLOGS (when the online logs are accessible it is not necessary to OPEN RESETLOGS after recovery with a created control file).
Available	Backup	Restore a backup control file, perform complete recovery, and then open RESETLOGS.
Unavailable	Backup	Restore a backup control file, perform incomplete recovery, and then open RESETLOGS.

Restoring a Backup Control File to the Default Location

shut down

SQL>SHUTDOWN

% cp /backup/control01.dbf ORACLE_HOME/oradata/trgt/control01.dbf

% cp /backup/control02.dbf ORACLE_HOME/oradata/trgt/control02.dbf

Start a new instance and mount the database.

SQL>STARTUP MOUNT

SQL>RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL

Apply the prompted archived logs. If you then receive another message saying that the required archived log is missing, it probably means that a necessary redo record is located in the online redo logs. This situation can occur when unarchived changes were located in the online logs when the instance crashed.

For example, assume that you see the following:

ORA-00279: change 55636 generated at 11/08/2002 16:59:47 needed for thread 1

ORA-00289: suggestion : /oracle/work/arc_dest/arcr_1_111.arc

ORA-00280: change 55636 for thread 1 is in sequence #111

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

You can specify the name of an online redo log and press Enter (you may have to try this a few times until you find the correct log):

ORACLE_HOME/oradata/redo01.dbf

Log applied.

Media recovery complete.

If the online logs are inaccessible, then you can cancel recovery without applying them. If all datafiles are current, and if redo in the online logs is required for recovery, then you cannot open the database without applying the online logsOpen the database with the RESETLOGS option after finishing recovery:

SQL>ALTER DATABASE OPEN RESETLOGS;

Restoring a Backup Control File to a Nondefault Location

Edit all locations specified in the CONTROL_FILES initialization parameter to reflect the new control file locations. For example, if the control file locations listed in the server parameter file are as follows, and both locations are inaccessible:

CONTROL_FILES='/oracle/oradata/trgt/control01.dbf',

              '/oracle/oradata/trgt/control01.dbf'

Then, you can edit the initialization parameter file as follows:

CONTROL_FILES='/good_disk/control01.dbf','/good_disk/control02.dbf'

Create New Control File After Losing All Current and Backup Control Files

If you . . .	Then . . .
Executed ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS after you made the last structural change to the database, and if you have saved the SQL command trace output	Use the CREATE CONTROLFILE statement from the trace output as-is.
Performed your most recent execution of ALTER DATABASE BACKUP CONTROLFILE TO TRACE before you made a structural change to the database	Edit the output of ALTER DATABASE BACKUP CONTROLFILE TO TRACE to reflect the change. For example, if you recently added a datafile to the database, then add this datafile to the DATAFILE clause of the CREATE CONTROLFILE statement.
Backed up the control file with the ALTER DATABASE BACKUP CONTROLFILE TO filename statement (not the TO TRACE option)	Use the control file copy to obtain SQL output. Create a temporary database instance, mount the backup control file, and then run ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS. If the control file copy predated a recent structural change, then edit the trace to reflect the change.
Do not have a control file backup in either TO TRACE format or TO filename format	Execute the CREATE CONTROLFILE statement manually (refer to Oracle Database SQL Reference).

Note:

If your character set is not the default US7ASCII, then you must specify the character set as an argument to the CREATE CONTROLFILE statement. The database character set is written to the alert log at startup. The character set information is also recorded in the BACKUP CONTROLFILE TO TRACE output.

To create a new control file:

Start the database in NOMOUNT mode. For example, enter:

SQL>STARTUP NOMOUNT

Create the control file with the CREATE CONTROLFILE statement, specifying the NORESETLOGS option. The following example assumes that the character set is the default US7ASCII:

CREATE CONTROLFILE REUSE DATABASE SALES NORESETLOGS ARCHIVELOG

     MAXLOGFILES 32

     MAXLOGMEMBERS 2

     MAXDATAFILES 32

     MAXINSTANCES 16

     MAXLOGHISTORY 1600

LOGFILE

     GROUP 1 (

       '/diska/prod/sales/db/log1t1.dbf',

       '/diskb/prod/sales/db/log1t2.dbf'

     )  SIZE 100K

     GROUP 2 (

       '/diska/prod/sales/db/log2t1.dbf',

       '/diskb/prod/sales/db/log2t2.dbf'

     )  SIZE 100K,

DATAFILE

     '/diska/prod/sales/db/database1.dbf',

     '/diskb/prod/sales/db/filea.dbf';

After creating the control file, the instance mounts the database.

Recover the database as normal (without specifying the USING BACKUP CONTROLFILE clause):

SQL>RECOVER DATABASE

Open the database after recovery completes (RESETLOGS option not required):

SQL>ALTER DATABASE OPEN;

Immediately back up the control file. The following SQL statement backs up a database's control file to /backup/control01.dbf:

SQL>ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control01.dbf' REUSE;

Automating Recovery with SET AUTORECOVERY

Run the SET AUTORECOVERY ON command to enable on automatic recovery.

To automate the recovery using SET AUTORECOVERY:

Restore a backup of the offline datafiles. This example restores an inconsistent backup of all datafiles with an operating system utility:

% cp /backup/datafiles/*.dbf $ORACLE_HOME/oradata/trgt/

Ensure the database is mounted. For example, if the database is shut down, run:

SQL>STARTUP MOUNT

Enable automatic recovery.

SET AUTORECOVERY ON

Recover the desired datafiles.

SQL>RECOVER DATABASE

Open the database.

SQL>ALTER DATABASE OPEN;

Recovering When Archived Logs Are in the Default Location

Recovering when the archived logs are in their default location is the simplest case. As a log is needed, the database suggests the filename. If you are running nonautomatic media recovery with SQL*Plus, then the output is displayed in this format:

ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for thread#

ORA-00289: Suggestion : logfile

ORA-00280: Change #### for thread # is in sequence #

Specify log: [<RET> for suggested | AUTO | FROM logsource | CANCEL ]

For example, SQL*Plus displays output similar to the following:

ORA-00279: change 53577 generated at 11/26/02 19:20:58 needed for thread 1

ORA-00289: suggestion : /oracle/oradata/trgt/arch/arcr_1_802.arc

ORA-00280: change 53577 for thread 1 is in sequence #802

Specify log: [<RET> for suggested | AUTO | FROM logsource | CANCEL ]

Similar messages are returned when you use an ALTER DATABASE ... RECOVER statement. However, no prompt is displayed.

The database constructs suggested archived log filenames by concatenating the current values of the initialization parameters LOG_ARCHIVE_DEST_n (where n is the highest value among all enabled, local destinations) and LOG_ARCHIVE_FORMAT and using log history data from the control file. The following are possible settings:

LOG_ARCHIVE_DEST_1 = 'LOCATION = /oracle/oradata/trgt/arch/'

LOG_ARCHIVE_FORMAT = arcr_%t_%s.arc

SQL>SELECT NAME FROM V$ARCHIVED_LOG;

Thus, if all the required archived log files are mounted at the LOG_ARCHIVE_DEST_1 destination, and if the value for LOG_ARCHIVE_FORMAT is never altered, then the database can suggest and apply log files to complete media recovery automatically.

Resetting the Archived Log Destination

You can edit the initialization parameter file or issue ALTER SYSTEM statements to change the default location of the archived redo logs.

To change the default archived log location before recovery:

Use an operating system utility to restore the archived logs to a nondefault location. For example, enter:

% cp /backup/arch/* /tmp/

Change the value for the archive log parameter to the nondefault location. You can issue ALTER SYSTEM statements while the instance is started, or edit the initialization parameter file and then start the database instance. For example, while the instance is shut down edit the parameter file as follows:

LOG_ARCHIVE_DEST_1 = 'LOCATION=/tmp/'

LOG_ARCHIVE_FORMAT = arcr_%t_%s.arc

Using SQL*Plus, start a new instance by specifying the edited initialization parameter file, and then mount the database. For example, enter:

SQL>STARTUP MOUNT

Begin media recovery as usual. For example, enter:

SQL>RECOVER DATABASE

Responding to Unsuccessful Application of Redo Logs

Error	Possible Cause	Solution
ORA-27037: unable to obtain file status	Entered wrong filename. Log is missing.	Reenter correct filename. Restore backup archived redo log.
ORA-27047: unable to read the header block of file	The log may have been partially written or become corrupted.	If you can locate an uncorrupted or complete log copy, then apply the intact copy and continue recovery. If no copy of the log exists and you know the time of the last valid redo entry, then you use incomplete recovery. Restore backups and restart recovery.

Restoring Backups of the Damaged or Missing Files

In this stage, you restore all necessary backups.

To restore the necessary files:

Use the following guidelines when determining where to restore datafile backups.

If . . .	Then . . .
The hardware problem is repaired and you can restore the datafiles to their default locations	Restore the datafiles to their default locations and begin media recovery.
The hardware problem persists and you cannot restore datafiles to their original locations	Restore the datafiles to an alternative storage device. Indicate the new location of these files in the control file with ALTER DATABASE RENAME FILE.

Losing Datafiles in ARCHIVELOG Mode

If either a permanent or temporary media failure affects the datafiles of a database operating in ARCHIVELOG mode, then the following scenarios can occur.

Damaged Datafiles	Database Status	Solution
Datafiles in the SYSTEM tablespace or datafiles with active undo segments.	Database shuts down.	If the hardware problem is temporary, then fix it and restart the database. Usually, crash recovery recovers lost transactions.
Datafiles not in the SYSTEM tablespace or datafiles that do not contain active rollback or undo segments.	Affected datafiles are taken offline, but the database stays open.	If the unaffected portions of the database must remain available, then do not shut down the database. Take tablespaces containing problem datafiles offline using the temporary option, then recover them.

Recovering Through an Added Datafile with a Backup Control File:

If database recovery with a backup control file rolls forward through a CREATE TABLESPACE or an ALTER TABLESPACE ADD DATAFILE operation, then the database stops recovery when applying the redo record for the added files and lets you confirm the filenames.

For example, suppose the following sequence of events occurs:

You back up the database.You create a new tablespace containing two datafiles: /oracle/oradata/trgt/test01.dbf and /oracle/oradata/trgt/test02.dbf.

You later restore a backup control file and perform media recovery through the CREATE TABLESPACE operation.

You may see the following error when applying the CREATE TABLESPACE redo data:

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile(s) added to control file by media recovery

ORA-01110: data file 11: '/oracle/oradata/trgt/test02.dbf'

ORA-01110: data file 10: '/oracle/oradata/trgt/test01.dbf'

To recover through an ADD DATAFILE operation, use the following procedure:

View the files added by selecting from V$DATAFILE. For example:

SQL>SELECT FILE#,NAME

FROM V$DATAFILE;

If multiple unnamed files exist, then determine which unnamed file corresponds to which datafile by using one of these methods:

Open the alert_SID.log, which contains messages about the original file location for each unnamed file.

Derive the original file location of each unnamed file from the error message and V$DATAFILE: each unnamed file corresponds to the file in the error message with the same file number.

Issue the ALTER DATABASE RENAME FILE statement to rename the datafiles. For example, enter:

SQL>ALTER DATABASE RENAME FILE '/db/UNNAMED00001' TO

                           '/oracle/oradata/trgt/test01.dbf';

SQL>ALTER DATABASE RENAME FILE '/db/UNNAMED00002' TO

                           '/oracle/oradata/trgt/test02.dbf';

Continue recovery by issuing the previous recovery statement. For example:

RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL

Re-Creating Datafiles When Backups Are Unavailable

If a datafile is damaged and no backup of the file is available, then you can still recover the datafile if:

·         All archived log files written after the creation of the original datafile are available

·         The control file contains the name of the damaged file (that is, the control file is current, or is a backup taken after the damaged datafile was added to the database)

Note:

You cannot re-create any of the datafiles for the SYSTEM tablespace by using the CREATE DATAFILE clause of the ALTER DATABASE statement because the necessary redo is not available.

To re-create a datafile for recovery:

Create a new, empty datafile to replace a damaged datafile that has no corresponding backup. For example, assume that the datafile ?/oradata/trgt/users01.dbf has been damaged, and no backup is available. The following statement re-creates the original datafile (same size) on disk2:

SQL>ALTER DATABASE CREATE DATAFILE '?/oradata/trgt/users01.dbf' AS ‘/disk2/users01.dbf';

This statement creates an empty file that is the same size as the lost file. The database looks at information in the control file and the data dictionary to obtain size information. The old datafile is renamed as the new datafile.

Perform media recovery on the empty datafile. For example, enter:

SQL>RECOVER DATAFILE '/disk2/users01.dbf'

All archived logs written after the original datafile was created must be applied to the new, empty version of the lost datafile during recovery.

Recovering Through RESETLOGS with Created Control File: Scenario

You can recover backups through an OPEN RESETLOGS so long as:

·         You have a current, backup, or created control file that knows about the prior incarnations

·         You have all available archived redo logs

If you need to re-create the control file, the trace file generated by ALTER DATABASE BACKUP CONTROLFILE TO TRACE will contain the necessary commands to re-construct the complete incarnation history. The V$DATABASE_INCARNATION view displays the RESETLOGS history known to the control file, while the V$LOG_HISTORY view displays the archived log history.

It is possible for the incarnation history to be incomplete in the in re-created control file. For example, archived logs necessary for recovery may be missing. In this case, it is possible to create incarnation records explicitly with the ALTER DATABASE REGISTER LOGFILE statement.

In the following example, you register four logs that are necessary for recovery but are not recorded in the re-created control file, and then recover the database:

ALTER DATABASE REGISTER LOGFILE '?/oradata/trgt/arch/arcr_1_1_42343523.arc';

ALTER DATABASE REGISTER LOGFILE '?/oradata/trgt/arch/arcr_1_1_34546466.arc';

ALTER DATABASE REGISTER LOGFILE '?/oradata/trgt/arch/arcr_1_1_23435466.arc';

ALTER DATABASE REGISTER LOGFILE '?/oradata/trgt/arch/arcr_1_1_12343533.arc';

RECOVER AUTOMATIC DATABASE;

Media Recovery of Transportable Tablespaces

To recover a transportable tablespace, use the following procedure:

If the database is open, then take the transported tablespace offline. For example, if you want to recover the users tablespace, then issue:

SQL>ALTER TABLESPACE users OFFLINE IMMEDIATE;

Restore a backup of the transported datafiles with an operating system utility. The backup can be the initial version of the transported datafiles or any backup taken after the tablespace is transported. For example, enter:

% cp /backup/users.dbf $ORACLE_HOME/oradata/trgt/users01.dbf

Recover the tablespace as normal. For example, enter:

SQL>RECOVER TABLESPACE users

Recovering After the Loss of Online Redo Log Files

If a media failure has affected the online redo logs of a database, then the appropriate

Status	Description
UNUSED	The online redo log has never been written to.
CURRENT	The online redo log is active, that is, needed for instance recovery, and it is the log to which the database is currently writing. The redo log can be open or closed.
ACTIVE	The online redo log is active, that is, needed for instance recovery, but is not the log to which the database is currently writing.It may be in use for block recovery, and may or may not be archived.
CLEARING	The log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, then the status changes to UNUSED.
CLEARING_CURRENT	The current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
INACTIVE	The log is no longer needed for instance recovery. It may be in use for media recovery, and may or may not be archived.

Recovering After Losing a Member of a Multiplexed Online Redo Log Group

If the online redo log of a database is multiplexed, and if at least one member of each online redo log group is not affected by the media failure, then the database continues functioning as normal, but error messages are written to the log writer trace file and the alert_SID.log of the database.

Solve the problem by taking one of the following actions:

·         If the hardware problem is temporary, then correct it. The log writer process accesses the previously unavailable online redo log files as if the problem never existed.

·         If the hardware problem is permanent, then drop the damaged member and add a new member by using the following procedure.

Note:

The newly added member provides no redundancy until the log group is reused.

Locate the filename of the damaged member in V$LOGFILE. The status is INVALID if the file is inaccessible:

SQL>SELECT GROUP#, STATUS, MEMBER

FROM V$LOGFILE

WHERE STATUS='INVALID';

Drop the damaged member. For example, to drop member redo01.log from group 2, issue:

SQL>ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/trgt/redo02.log';

Add a new member to the group. For example, to add redo02.log to group 2, issue:

SQL>ALTER DATABASE ADD LOGFILE MEMBER '/oracle/oradata/trgt/redo02b.log'

  TO GROUP 2;

If the file you want to add already exists, then it must be the same size as the other group members, and you must specify REUSE. For example:

SQL>ALTER DATABASE ADD LOGFILE MEMBER '/oracle/oradata/trgt/redo02b.log'

  REUSE TO GROUP 2;

Recovering After the Loss of All Members of an Online Redo Log Group

If a media failure damages all members of an online redo log group, then different scenarios can occur depending on the type of online redo log group affected by the failure and the archiving mode of the database.

If the damaged log group is active, then it is needed for crash recovery; otherwise, it is not.

If the group is . . .	Then . . .	And you should . . .
Inactive	It is not needed for crash recovery	Clear the archived or unarchived group.
Active	It is needed for crash recovery	Attempt to issue a checkpoint and clear the log; if impossible, then you must restore a backup and perform incomplete recovery up to the most recent available redo log.
Current	It is the log that the database is currently writing to	Attempt to clear the log; if impossible, then you must restore a backup and perform incomplete recovery up to the most recent available redo log.

Your first task is to determine whether the damaged group is active or inactive.

Locate the filename of the lost redo log in V$LOGFILE and then look for the group number corresponding to it. For example, enter:

SQL>SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;

Determine which groups are active. For example, enter:

SQL>SELECT GROUP#, MEMBERS, STATUS, ARCHIVED

FROM V$LOG;

Losing an Inactive Online Redo Log Group

If all members of an online redo log group with INACTIVE status are damaged, then the procedure depends on whether you can fix the media problem that damaged the inactive redo log group.

If the failure is . . .	Then . . .
Temporary	Fix the problem. LGWR can reuse the redo log group when required.
Permanent	The damaged inactive online redo log group eventually halts normal database operation. Reinitialize the damaged group manually by issuing the ALTER DATABASE CLEAR LOGFILE statement as described in this section.

Clearing Inactive, Archived Redo

You can clear an inactive redo log group when the database is open or closed. The procedure depends on whether the damaged group has been archived.

To clear an inactive, online redo log group that has been archived, use the following procedure:

If the database is shut down, then start a new instance and mount the database:

SQL>STARTUP MOUNT

Reinitialize the damaged log group. For example, to clear redo log group 2, issue the following statement:

SQL>ALTER DATABASE CLEAR LOGFILE GROUP 2;