How an Oracle Instance Is Started

When Oracle starts an instance, it reads the server parameter file (SPFILE) or initialization parameter file to determine the values of initialization parameters. Then, it allocates an SGA, which is a shared area of memory used for database information, and creates background processes. At this point, no database is associated with these memory structures and processes.

Restricted Mode of Instance Startup

You can start an instance in restricted mode (or later alter an existing instance to be in restricted mode). This restricts connections to only those users who have been granted the RESTRICTED SESSION system privilege.

Forced Startup in Abnormal Situations

In unusual circumstances, a previous instance might not have been shut down cleanly. For example, one of the instance's processes might not have terminated properly. In such situations, the database can return an error during normal instance startup. To resolve this problem, you must terminate all remnant Oracle processes of the previous instance before starting the new instance.

How a Database Is Mounted

The instance mounts a database to associate the database with that instance. To mount the database, the instance finds the database control files and opens them. Control files are specified in the CONTROL_FILES initialization parameter in the parameter file used to start the instance. Oracle then reads the control files to get the names of the database's datafiles and redo log files.

At this point, the database is still closed and is accessible only to the database administrator. The database administrator can keep the database closed while completing specific maintenance operations. However, the database is not yet available for normal operations.

How a Database Is Mounted with Real Application Clusters

If Oracle allows multiple instances to mount the same database concurrently, then the database administrator can use the CLUSTER_DATABASE initialization parameter to make the database available to multiple instances. The default value of the CLUSTER_DATABASE parameter is false. Versions of Oracle that do not support Real Application Clusters only allow CLUSTER_DATABASE to be false.

If CLUSTER_DATABASE is false for the first instance that mounts a database, then only that instance can mount the database. If CLUSTER_DATABASE is set to true on the first instance, then other instances can mount the database if their CLUSTER_DATABASE parameters are set to true. The number of instances that can mount the database is subject to a predetermined maximum, which you can specify when creating the database.

Overview of Instance and Database Startup

The three steps to starting an Oracle database and making it available for systemwide use are:

1. Start an instance.

2. Mount the database.

3. Open the database.

A database administrator can perform these steps using the SQL*Plus STARTUP statement or Enterprise Manager.

How Initialization Parameter Values Are Changed

The database administrator can adjust variable parameters to improve the performance of a database system. Exactly which parameters most affect a system depends on numerous database characteristics and variables.

Some parameters can be changed dynamically with the ALTER SESSION or ALTER SYSTEM statement while the instance is running. Unless you are using a server parameter file (SPFILE), changes made using the ALTER SYSTEM statement are only in effect for the current instance. You must manually update the text initialization parameter file for the changes to be known the next time you start up an instance. When you use a SPFILE, you can update the parameters on disk, so that changes persist across database shutdown and startup.

Oracle provides values in the starter initialization parameter file provided with your database software, or as created for you by the Database Configuration Assistant. You can edit these Oracle-supplied initialization parameters and add others, depending upon your configuration and options and how you plan to tune the database. For any relevant initialization parameters not specifically included in the initialization parameter file, Oracle supplies defaults. If you are creating an Oracle database for the first time, it is suggested that you minimize the number of parameter values that you alter.

Initialization Parameter Files and Server Parameter Files

To start an instance, Oracle must read either an initialization parameter file or a server parameter file. These files contain a list of configuration parameters for that instance and database. Oracle traditionally stored initialization parameters in a text initialization parameter file. You can also choose to maintain initialization parameters in a binary server parameter file (SPFILE).

Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup.

Initialization parameters are divided into two groups: basic and advanced. In the majority of cases, it is necessary to set and tune only the basic parameters to get reasonable performance. In rare situations, modification to the advanced parameters may be needed for optimal performance.

Most initialization parameters belong to one of the following groups:

• Parameters that name things, such as files

• Parameters that set limits, such as maximums

• Parameters that affect capacity, such as the size of the SGA, which are called variable parameters

Among other things, the initialization parameters tell Oracle:

• The name of the database for which to start up an instance

• How much memory to use for memory structures in the SGA

• What to do with filled redo log files

• The names and locations of the database control files

• The names of undo tablespaces in the database

The Instance and the Database

After starting an instance, Oracle associates the instance with the specified database. This is a mounted database. The database is then ready to be opened, which makes it accessible to authorized users.

Multiple instances can run concurrently on the same computer, each accessing its own physical database. In large-scale cluster systems, Real Application Clusters enables multiple instances to mount a single database.

Only the database administrator can start up an instance and open the database. If a database is open, then the database administrator can shut down the database so that it is closed. When a database is closed, users cannot access the information that it contains.

Security for database startup and shutdown is controlled through connections to Oracle with administrator privileges. Normal users do not have control over the current status of an Oracle database.

Database and Instance Startup and Shutdown

Every running Oracle database is associated with an Oracle instance. When a database is started on a database server (regardless of the type of computer), Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle Instance. The memory and processes of an instance manage the associated database's data efficiently and serve the one or multiple users of the database.

Managing PFILE and SPFILE

As a DBA the main thing you need to worry about with the SPFILE and PFILES are backing them up. You can use RMAN to backup an SPFILE, or back them up yourself.

Remember that a PFILE is simply a text based file, which means you can copy it to another directory without affecting the Oracle instance. This is the easiest way to backup a PFILE.

To back up an SPFILE, you will first want to convert it to a PFILE. You can do this with the following syntax.

SQL> create pfile from spfile;

This will create a PFILE named initSID.ora in your $ORACLE_HOME/database (Windows) or $ORACLE_HOME/dbs (Linux/Unix) directory.

Note that the SID in initSID.ora will be replaced with the SID of your database as defined during creation.

In addition, you can back up the file directly to the preferred location with the command:

SQL> create pfile=/path/to/backup.ora from spfile;

If the time comes that you must put the SPFILE back into place, you can do so with this command:

SQL> create spfile from pfile=/path/to/backup.ora

If your database is currently running using the SPFILE, be sure to shut down first so Oracle can replace the file. As your SPFILE is in use the entire time your database is running, you should never overwrite it during normal operations

The Oracle SPFILE

The SPFILE is different from the PFILE in that it can not be directly edited. This is because it has a header and footer that contains binary values. Since you can not change a SPFILE directly, Oracle allows you to manage the SPFILE via the alter system command.

That might sound a bit more complex, but it really is no harder than manually changing a PFILE. For using an SPFILE, you can reap great benefits. It can be backed up by RMAN every time a change is made or when the database is backed up, which means it is easier to recover. Also, SPFILES allow you to make dynamic changes to parameters that are persistent. For example, remember that we said this database parameter change was not persistent if we were using PFILES:

Alter system set db_recovery_file_dest_size=10g;

If we were using SPFILES the parameter would keep the same value, even after a database restart. This means you only have to change the parameter value in one place, and that you can forget having to change it in the PFILE of the database.

One of the most important benefits of the SPFILE is that Oracle has introduced many automatic tuning features into the core of the database. Without an SPFILE, Oracle can not autotune your database.

An SPFILE uses the same formatting for its file name as the PFILE, except the word spfile replaces init. For instance, if your ORACLE_SID is testdb, the resulting spfile would be called spfiletestdb.ora.

Oracle Startup and Parameter File

Oracle prefers the use of an SPFILE to a PFILE. When you startup your Oracle database, Oracle will scan the contents of your parameter directory ($ORACLE_HOME/database on Windows or the Linux directory name $ORACLE_HOME/dbs), searching in the following order:

* spfileSID.ora

* spfile.ora

* initSID.ora

* init.ora

If the directory contains none of the above, then the startup will fail.

Alternatively, you can tell Oracle where to find a PFILE if you store it in a different location.

SQL> startup pfile=/path/to/pfile/inittestdb.ora

Furthermore, you can create a PFILE that contains nothing but the following line:

SPFILE=/path/to/spfiletestdb.ora

By doing so, we are able to startup using a PFILE in any location we want, but continue to use an SPFILE that can also be in a different location. This can be very beneficial for those that wish to store their SPFILE in a centralized location, such as a SAN.

Oracle's PFILE

Oracle provides two different types of mutually exclusive parameter files that you can use, PFILE and SPFILE.

The PFILE is a text-based file.
PFILE contains number of database settings called parameters.

You can use the V$PARAMETER dynamic view to see the current setting of the different database parameters.

SQL> desc v$parameter

Name Null? Type

----------------------------------------- -------- -------------

NUM NUMBER

NAME VARCHAR2(80)

TYPE NUMBER

VALUE VARCHAR2(512)

DISPLAY_VALUE VARCHAR2(512)

ISDEFAULT VARCHAR2(9)

ISSES_MODIFIABLE VARCHAR2(5)

ISSYS_MODIFIABLE VARCHAR2(9)

ISINSTANCE_MODIFIABLE VARCHAR2(5)

ISMODIFIED VARCHAR2(10)

ISADJUSTED VARCHAR2(5)

ISDEPRECATED VARCHAR2(5)

DESCRIPTION VARCHAR2(255)

UPDATE_COMMENT VARCHAR2(255)

HASH NUMBER

SQL> select name, value from v$parameter where name = 'control_files';

NAME VALUE

-------------------- -----------------------------------------------

control_files C:\ORACLE\ORADATA\BOOKTST\BOOKTST\CONTROL01.CTL, C:\ORACLE

\ORADATA\BOOKTST\BOOKTST\CONTROL02.CTL, C:\ORACLE\ORADATA\

BOOKTST\BOOKTST\CONTROL03.CTL

SQL> show parameter control_files;

As the PFILE is text based, one can edit it in an editor like vi on UNIX or Notepad on Windows. When you have changed it, you need to make sure you save your changes to disk before you exit the editor. Also, make sure you save it as a plain text file, since some editors (like Microsoft Word) can save documents in special formats that Oracle would not be able to read.

Depending on which operating system you are running on, your PFILE is located by default in the ORACLE_HOME\database (usually the case on Windows) or ORACLE_HOME\dbs directory for most other platforms (we talked about where ORACLE_HOME was earlier in this book).

If you are using a PFILE, it takes on the form of initSID.ora, meaning the file will use the ORACLE_SID you defined when you created the database. If your SID is called testdb, the resulting PFILE should be called inittestdb.ora

Removing a Password File

If you determine that you no longer require a password file to authenticate users, you can delete the password file and then optionally reset the REMOTE_LOGIN_PASSWORDFILE initialization parameter to NONE. After you remove this file, only those users who can be authenticated by the operating system can perform SYSDBA or SYSOPER database administration operations.

Replacing a Password File

Use the following procedure to replace a password file:

1.Identify the users who have SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view.
2.Delete the existing password file.
3.Follow the instructions for creating a new password file using the ORAPWD utility in "Using ORAPWD". Ensure that the ENTRIES parameter is set to a number larger than you think you will ever need.
4.Follow the instructions in "Adding Users to a Password File".

Expanding the number of password file users if the password file becomes full

Expanding the Number of Password File Users

If you receive the file full error (ORA-1996) when you try to grant SYSDBA or SYSOPER system privileges to a user, you must create a larger password file and regrant the privileges to the users.

Viewing Password File Members

Use the V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSOPER system privileges for a database. The columns displayed by this view are as follows:

Column	Description
USERNAME	This column contains the name of the user that is recognized by the password file.
SYSDBA	If the value of this column is TRUE, then the user can log on with SYSDBA system privileges.
SYSOPER	If the value of this column is TRUE, then the user can log on with SYSOPER system privileges.

Granting and Revoking SYSDBA and SYSOPER Privileges to Oracle database user

If your server is using an EXCLUSIVE password file, use the GRANT statement to grant the SYSDBA or SYSOPER system privilege to a user, as shown in the following example:

GRANT SYSDBA TO oe;

Use the REVOKE statement to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example:

REVOKE SYSDBA FROM oe;

Because SYSDBA and SYSOPER are the most powerful database privileges, the WITH ADMIN OPTION is not used in the GRANT statement. That is, the grantee cannot in turn grant the SYSDBA or SYSOPER privilege to another user. Only a user currently connected as SYSDBA can grant or revoke another user's SYSDBA or SYSOPER system privileges. These privileges cannot be granted to roles, because roles are available only after database startup. Do not confuse the SYSDBA and SYSOPER database privileges with operating system roles.

Creating a Password File and Adding New Users to It

Use the following procedure to create a password and add new users to it:

1. Follow the instructions for creating a password file as explained in "Using ORAPWD".
2.Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default.)

Note:
REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without restarting the database.
3. Connect with SYSDBA privileges as shown in the following example, and enter the SYS password when prompted:

CONNECT SYS AS SYSDBA

4. Start up the instance and create the database if necessary, or mount and open an existing database.
5. Create users as necessary. Grant SYSDBA or SYSOPER privileges to yourself and other users as appropriate. See "Granting and Revoking SYSDBA and SYSOPER Privileges", later in this section.

Setting REMOTE_LOGIN_ PASSWORDFILE

When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information are added to the password file. If the server does not have an EXCLUSIVE password file (that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, or the password file is missing), Oracle Database issues an error if you attempt to grant these privileges.

A user's name remains in the password file only as long as that user has at least one of these two privileges. If you revoke both of these privileges, Oracle Database removes the user from the password file.

Setting REMOTE_LOGIN_ PASSWORDFILE

In addition to creating the password file, you must also set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to the appropriate value. The values recognized are:

• NONE: Setting this parameter to NONE causes Oracle Database to behave as if the password file does not exist. That is, no privileged connections are allowed over nonsecure connections.

• EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.

• SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (RAC) database. A SHARED password file cannot be modified. This means that you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.

  This option is useful if you are administering multiple databases or a RAC database.

If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.

Note:

You cannot change the password for SYS if REMOTE_LOGIN_PASSWORDFILE is set to SHARED. An error message is issued if you attempt to do so.

Using ORAPWD

The syntax of the ORAPWD command is as follows:

ORAPWD FILE=filename [ENTRIES=numusers]
  [FORCE={Y|N}] [IGNORECASE={Y|N}] [NOSYSDBA={Y|N}]

Command arguments are summarized in the following table.

Argument	Description
FILE	Name to assign to the password file. See your operating system documentation for name requirements. You must supply a complete path. If you supply only a file name, the file is written to the current directory.
ENTRIES	(Optional) Maximum number of entries (user accounts) to permit in the file.
FORCE	(Optional) If y, permits overwriting an existing password file.
IGNORECASE	(Optional) If y, passwords are treated as case-insensitive.
NOSYSDBA	(Optional) For Data Vault installations. See the Data Vault installation guide for your platform for more information.

There are no spaces permitted around the equal-to (=) character.

The command prompts for the SYS password and stores the password in the created password file.

Example

The following command creates a password file named orapworcl that allows up to 30 privileged users with different passwords.

orapwd FILE=orapworcl ENTRIES=30

ORAPWD Command Line Argument Descriptions

The following sections describe the ORAPWD command line arguments.

FILE

This argument sets the name of the password file being created. You must specify the full path name for the file. If you supply only a file name, the file is written to the current directory. The contents of this file are encrypted, and the file cannot be read directly. This argument is mandatory.

The types of filenames allowed for the password file are operating system specific. Some operating systems require the password file to adhere to a specific format and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file. For name and location information for the Unix and Linux operating systems, see Administrator's Reference for UNIX-Based Operating Systems. For Windows, see Platform Guide for Microsoft Windows. For other operating systems, see your operating system documentation.

If you are running multiple instances of Oracle Database using Oracle Real Application Clusters, the environment variable for each instance should point to the same password file.

Caution:

It is critically important to the security of your system that you protect your password file and the environment variables that identify the location of the password file. Any user with access to these could potentially compromise the security of the connection.

ENTRIES

This argument specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.

Entries can be reused as users are added to and removed from the password file. If you intend to specify REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, and to allow the granting of SYSDBA and SYSOPER privileges to users, this argument is required.

Caution:

When you exceed the allocated number of password entries, you must create a new password file. To avoid this necessity, allocate a number of entries that is larger than you think you will ever need.

FORCE

This argument, if set to Y, enables you to overwrite an existing password file. An error is returned if a password file of the same name already exists and this argument is omitted or set to N.

IGNORECASE

If this argument is set to y, passwords are case-insensitive. That is, case is ignored when comparing the password that the user supplies during login with the password in the password file.

Oracle Password File

You can create a password file using the password file creation utility, ORAPWD. For some operating systems, you can create this file as part of your standard installation.

To enable authentication of an administrative user using password file authentication you must do the following:

1. If not already created, create the password file using the ORAPWD utility:

   ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users
   
   

   Note:

   REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without restarting the database.

2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default).

Connect to the database as user SYS (or as another user with the administrative privileges).

1. If the user does not already exist in the database, create the user.

2. Grant the SYSDBA or SYSOPER system privilege to the user:

   GRANT SYSDBA to oe;
   This statement adds the user to the password file, thereby enabling connection AS SYSDBA.

Dropping Control Files

You can drop control files from the database. For example, you might want to do so if the location of a control file is no longer appropriate. Remember that the database must have at least two control files at all times.

1. Shut down the database.
2. Edit the CONTROL_FILES parameter in the database's initialization parameter file to delete the old control file's name.
3. Restart the database.

Please note that This operation does not physically delete the unwanted control file from the disk. Use operating system commands to delete the unnecessary file after you have dropped the control file from the database.

Control File V$ Views

V$DATABASE - Displays database information from the control file
V$CONTROLFILE - Lists the names of control files
V$CONTROLFILE_RECORD_SECTION - Displays information about control file record sections
V$PARAMETER - Can be used to display the names of control files as specified in the CONTROL_FILES initialization parameter

SQL> SELECT NAME FROM V$CONTROLFILE;

NAME
-------------------------------------
/u01/oracle/prod/control01.ctl
/u02/oracle/prod/control02.ctl
/u03/oracle/prod/control03.ctl

Recovering a Control File

Recovering from Permanent Media Failure Using a Control File Copy

This procedure assumes that one of the control files specified in the CONTROL_FILES parameter is inaccessible due to a permanent media failure, and you have a multiplexed copy of the control file.

1. With the instance shut down, use an operating system command to copy the current copy of the control file to a new, accessible location:

   % cp /u01/oracle/prod/control01.ctl  /u04/oracle/prod/control03.ctl
   
   

2. Edit the CONTROL_FILES parameter in the initialization parameter file to replace the bad location with the new location:

   CONTROL_FILES = (/u01/oracle/prod/control01.ctl,
                    /u02/oracle/prod/control02.ctl,
                    /u04/oracle/prod/control03.ctl)
   
   

3. Start SQL*Plus and open the database:

   SQL> STARTUP
   
   

In any case where you have multiplexed control files, and you must get the database up in minimum time, you can do so by editing the CONTROL_FILES initialization parameter to remove the bad control file and restarting the database immediately. Then you can perform the reconstruction of the bad control file and at some later time shut down and restart the database after editing the CONTROL_FILES initialization parameter to include the recovered control file.

Recovering a Control File

Recovering from Control File Corruption Using a Control File Copy

This procedure assumes that one of the control files specified in the CONTROL_FILES parameter is corrupted, the control file directory is still accessible, and you have a multiplexed copy of the control file.

1. With the instance shut down, use an operating system command to overwrite the bad control file with a good copy:

   % cp /u01/oracle/prod/control03.ctl  /u01/oracle/prod/control02.ctl
   
   

2. Start SQL*Plus and open the database:

   SQL> STARTUP
   

Backing Up Control Files

Use the ALTER DATABASE BACKUP CONTROLFILE statement to back up your control files. You have two options:

1. Back up the control file to a binary file (duplicate of existing control file) using the following statement:

   ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';
   
   

2. Produce SQL statements that can later be used to re-create your control file:

   ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
   
   

   This command writes a SQL script to the database's trace file where it can be captured and edited to reproduce the control file.

Troubleshooting After Creating Control Files

After issuing the CREATE CONTROLFILE statement, you may encounter some common errors.

Checking for Missing or Extra Files

After creating a new control file and using it to open the database, check the alert file to see if Oracle has detected inconsistencies between the data dictionary and the control file, such as a datafile that the data dictionary includes but the control file does not list.

If a datafile exists in the data dictionary but not in the new control file, Oracle creates a placeholder entry in the control file under the name MISSINGnnnn (where nnnn is the file number in decimal). MISSINGnnnn is flagged in the control file as being offline and requiring media recovery.

The actual datafile corresponding to MISSINGnnnn can be made accessible by renaming MISSINGnnnn so that it points to the datafile only if the datafile was read-only or offline normal. If, on the other hand, MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, then the rename operation cannot be used to make the datafile accessible, because the datafile requires media recovery that is precluded by the results of RESETLOGS. In this case, you must drop the tablespace containing the datafile.

In contrast, if a datafile indicated in the control file is not present in the data dictionary, Oracle removes references to it from the new control file. In both cases, Oracle includes an explanatory message in the alert.log file to let you know what was found.

Handling Errors During CREATE CONTROLFILE

If Oracle sends you an error (usually error ORA-01173, ORA-01176, ORA-01177, ORA-01215, or ORA-01216) when you attempt to mount and open the database after creating a new control file, the most likely cause is that you omitted a file from the CREATE CONTROLFILE statement or included one that should not have been listed. In this case, you should restore the files you backed up in Step 3 and repeat the procedure from Step 4, using the correct filenames.

Steps for Creating New Control Files

Complete the following steps to create a new control file.

1. Make a list of all datafiles and online redo log files of the database.

If you follow recommendations for control file backups as discussed in "Backing Up Control Files" , you will already have a list of datafiles and online redo log files that reflect the current structure of the database. However, if you have no such list, executing the following statements will produce one.

SELECT MEMBER FROM V$LOGFILE;
SELECT NAME FROM V$DATAFILE;
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'CONTROL_FILES';

If you have no such lists and your control file has been damaged so that the database cannot be opened, try to locate all of the datafiles and online redo log files that constitute the database. Any files not specified in Step 5 are not recoverable once a new control file has been created. Moreover, if you omit any of the files that make up the SYSTEM tablespace, you might not be able to recover the database.

2. Shut down the database.

If the database is open, shut down the database normally if possible. Use the IMMEDIATE or ABORT options only as a last resort.

3. Back up all datafiles and online redo log files of the database.

4. Start up a new instance, but do not mount or open the database:

STARTUP NOMOUNT

5. Create a new control file for the database using the CREATE CONTROLFILE statement.

When creating a new control file, select the RESETLOGS option if you have lost any online redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs (Step 8). You must also specify the RESETLOGS option if you have renamed the database. Otherwise, select the NORESETLOGS option.

6. Store a backup of the new control file on an offline storage device. See "Backing Up Control Files" for instructions for creating a backup.

7. Edit the CONTROL_FILES initialization parameter for the database to indicate all of the control files now part of your database as created in Step 5 (not including the backup control file). If you are renaming the database, edit the DB_NAME parameter to specify the new name.


8. Recover the database if necessary. If you are not recovering the database, skip to Step 9.

If you are creating the control file as part of recovery, recover the database. If the new control file was created using the NORESETLOGS option (Step 5), you can recover the database with complete, closed database recovery.

If the new control file was created using the RESETLOGS option, you must specify USING BACKUP CONTROL FILE. If you have lost online or archived redo logs or datafiles, use the procedures for recovering those files.

9. Open the database using one of the following methods:
* If you did not perform recovery, or you performed complete, closed database recovery in Step 8, open the database normally.

ALTER DATABASE OPEN;

* If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement, indicating RESETLOGS.

ALTER DATABASE OPEN RESETLOGS;

The database is now open and available for use.

CREATE CONTROLFILE Statement

You can create a new control file for a database using the CREATE CONTROLFILE statement. The following statement creates a new control file for the prod database (formerly a database that used a different database name):

CREATE CONTROLFILE
   SET DATABASE prod
   LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',
                    '/u01/oracle/prod/redo01_02.log'),
           GROUP 2 ('/u01/oracle/prod/redo02_01.log',
                    '/u01/oracle/prod/redo02_02.log'),
           GROUP 3 ('/u01/oracle/prod/redo03_01.log',
                    '/u01/oracle/prod/redo03_02.log')
   NORESETLOGS
   DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
            '/u01/oracle/prod/rbs01.dbs' SIZE 5M,
            '/u01/oracle/prod/users01.dbs' SIZE 5M,
            '/u01/oracle/prod/temp01.dbs' SIZE 5M
   MAXLOGFILES 50
   MAXLOGMEMBERS 3
   MAXLOGHISTORY 400
   MAXDATAFILES 200
   MAXINSTANCES 6
   ARCHIVELOG;

When to Create New Control Files

It is necessary for you to create new control files in the following situations:

• All control files for the database have been permanently damaged and you do not have a control file backup.
• You want to change one of the permanent database parameter settings originally specified in the CREATE DATABASE statement. These settings include the database's name and the following parameters: MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES.

  For example, you would change a database's name if it conflicted with another database's name in a distributed environment. Or, as another example, you can change the value of MAXLOGFILES if the original setting is too low.

Creating Additional Copies of Control Files

You can create an an additional control file copy by copying an existing control file to a new location and adding the file's name to the list of control files.

To Multiplex or Move Additional Copies of the Current Control Files

1. Shut down the database.
2. Copy an existing control file to a different location, using operating system commands.
3. Edit the CONTROL_FILES parameter in the database's initialization parameter file to add the new control file's name, or to change the existing control filename.
4. Restart the database.

Creating Control Files

The initial control files of an Oracle database are created when you issue the CREATE DATABASE statement. The names of the control files are specified by the CONTROL_FILES parameter in the initialization parameter file used during database creation. The filenames specified in CONTROL_FILES should be fully specified and are operating system specific. The following is an example of a CONTROL_FILES initialization parameter:

CONTROL_FILES = (/u01/oracle/prod/control01.ctl,
                 /u02/oracle/prod/control02.ctl,
                 /u03/oracle/prod/control03.ctl)

If files with the specified names currently exist at the time of database creation, you must specify the CONTROLFILE REUSE clause in the CREATE DATABASE statement, or else an error occurs. Also, if the size of the old control file differs from the SIZE parameter of the new one, you cannot use the REUSE option.

The size of the control file changes between some releases of Oracle, as well as when the number of files specified in the control file changes. Configuration parameters such as MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES affect control file size.

You can subsequently change the value of the CONTROL_FILES initialization parameter to add more control files or to change the names or locations of existing control files.

Size of Control Files

The main determinants of a control file's size are the values set for the MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCESCREATE DATABASE statement that created the associated database.

Increasing the values of these parameters increases the size of a control file of the associated database.

Multiplexed control files

Every Oracle database should have at least two control files, each stored on a different disk. If a control file is damaged due to a disk failure, the associated instance must be shut down. Once the disk drive is repaired, the damaged control file can be restored using the intact copy of the control file from the other disk and the instance can be restarted. In this case, no media recovery is required.

The following describes the behavior of multiplexed control files:

• Oracle writes to all filenames listed for the initialization parameter CONTROL_FILES in the database's initialization parameter file.
• The first file listed in the CONTROL_FILES parameter is the only file read by the Oracle database server during database operation.
• If any of the control files become unavailable during database operation, the instance becomes inoperable and should be aborted.
  
  

Control Files Init.ora parameter

You specify control file names using the CONTROL_FILES initialization parameter in the database's initialization parameter file.

The instance startup procedure recognizes and opens all the listed files. The instance writes to and maintains all listed control files during database operation.

If you do not specify files for CONTROL_FILES before database creation, and you are not using the Oracle Managed Files feature, Oracle creates a control file and uses a default filename. The default name is operating system specific.

Oracle Control file

A control file is a small binary file that records the physical structure of the database and includes:

• The database name
• Names and locations of associated datafiles and online redo log files
• The timestamp of the database creation
• The current log sequence number
• Checkpoint information

The control file must be available for writing by the Oracle database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

The control file of an Oracle database is created at the same time as the database. By default, at least one copy of the control file is created during database creation. On some operating systems the default is to create multiple copies. You should create two or more copies of the control file during database creation. You might also need to create control files later, if you lose control files or want to change particular settings in the control files.

Datafile Backups

Datafile Backups
A datafile backup is a backup of a single datafile. Datafile backups, which are not as common as tablespace backups and are only valid if the database is run in ARCHIVELOG mode. The only time a datafile backup is valid for a database running in NOARCHIVELOG mode is if that datafile is the only file in a tablespace. For example, the backup is a tablespace backup, but the tablespace only contains one file and is read-only or offline-normal.

Oracle Datafile backup - part1

KEY DATA STRUCTURES FOR BACKUP AND RECOVERY
Before users begin to think seriously about backup and recovery strategy, the physical data structures relevant for backup and recovery operations must be identified. This section discusses the following physical data structures:

Datafiles
Control Files
Online Redo Log Files
Archived Redo Log Files
Automatic Managed Undo

DATAFILES
Every Oracle database has one or more physical datafiles that belong to logical structures called tablespaces. The datafile is divided into smaller units called data blocks. The data of logical database structures, such as tables and indexes, is physically located in the blocks of the datafiles allocated for a database. Datafiles hold the following characteristics:

• User-defined characteristics allow datafiles to automatically extend when the database runs out of space.
• One or more physical datafiles form a logical database storage unit called a tablespace.

The first block of every datafile is the header. The header includes important information such as file size, block size, tablespace, and creation timestamp. Whenever the database is opened, Oracle checks to see that the datafile header information matches the information stored in the control file. If it does not, then recovery is necessary. Oracle reads the data in a datafile during normal operation and stores it in the buffer cache. For example, assume that a user wants to access some data in a table. If the requested information is not already in the buffer cache, Oracle reads it from the appropriate datafiles and stores it in memory.

v$datafile_header view

This view displays datafile information from the datafile headers.

Column	Datatype	Description
FILE#	NUMBER	Datafile number (from control file)
STATUS	VARCHAR2(7)	ONLINE | OFFLINE (from control file)
ERROR	VARCHAR2(18)	NULL if the datafile header read and validation were successful. If the read failed then the rest of the columns are NULL. If the validation failed then the rest of columns may display invalid data. If there is an error then usually the datafile must be restored from a backup before it can be recovered or used.
FORMAT	NUMBER	Indicates the format for the header block. The possible values are 6, 7, 8, or 0. 6 - indicates Oracle Version 6 7 - indicates Oracle Version 7 8 - indicates Oracle Version 8 0 - indicates the format could not be determined (for example, the header could not be read)
RECOVER	VARCHAR2(3)	File needs media recovery (YES | NO)
FUZZY	VARCHAR2(3)	File is fuzzy (YES | NO)
CREATION_CHANGE#	NUMBER	Datafile creation change#
CREATION_TIME	DATE	Datafile creation timestamp
TABLESPACE_NAME	VARCHAR2(30)	Tablespace name
TS#	NUMBER	Tablespace number
RFILE#	NUMBER	Tablespace relative datafile number
RESETLOGS_CHANGE#	NUMBER	Resetlogs change#
RESETLOGS_TIME	DATE	Resetlogs timestamp
CHECKPOINT_CHANGE#	NUMBER	Datafile checkpoint change#
CHECKPOINT_TIME	DATE	Datafile checkpoint timestamp
CHECKPOINT_COUNT	NUMBER	Datafile checkpoint count
BYTES	NUMBER	Current datafile size in bytes
BLOCKS	NUMBER	Current datafile size in blocks
NAME	VARCHAR2(513)	Datafile name
SPACE_HEADER	VARCHAR2(40)	The amount of space currently being used and the amount that is free, as identified in the space header
LAST_DEALLOC_SCN	VARCHAR2(16)	Last deallocated SCN

v$datafile view

This view contains datafile information from the control file.

FILE#	NUMBER	File identification number
CREATION_CHANGE#	NUMBER	Change number at which the datafile was created
CREATION_TIME	DATE	Timestamp of the datafile creation
TS#	NUMBER	Tablespace number
RFILE#	NUMBER	Tablespace relative datafile number
STATUS	VARCHAR2(7)	Type of file (system or user) and its status. Values: OFFLINE, ONLINE, SYSTEM, RECOVER, SYSOFF (an offline file from the SYSTEM tablespace)
ENABLED	VARCHAR2(10)	Describes how accessible the file is from SQL: DISABLED - No SQL access allowed READ ONLY - No SQL updates allowed READ WRITE - Full access allowed UNKNOWN - should not occur unless the control file is corrupted
CHECKPOINT_CHANGE#	NUMBER	SCN at last checkpoint
CHECKPOINT_TIME	DATE	Timestamp of the checkpoint#
UNRECOVERABLE_CHANGE#	NUMBER	Last unrecoverable change number made to this datafile. If the database is in ARCHIVELOG mode, then this column is updated when an unrecoverable operation completes. If the database is not in ARCHIVELOG mode, this column does not get updated.
UNRECOVERABLE_TIME	DATE	Timestamp of the last unrecoverable change. This column is updated only if the database is in ARCHIVELOG mode.
LAST_CHANGE#	NUMBER	Last change number made to this datafile (null if the datafile is being changed)
LAST_TIME	DATE	Timestamp of the last change
OFFLINE_CHANGE#	NUMBER	Offline change number of the last offline range. This column is updated only when the datafile is brought online.
ONLINE_CHANGE#	NUMBER	Online change number of the last offline range
ONLINE_TIME	DATE	Online timestamp of the last offline range
BYTES	NUMBER	Current datafile size (in bytes); 0 if inaccessible
BLOCKS	NUMBER	Current datafile size (in blocks); 0 if inaccessible
CREATE_BYTES	NUMBER	Size when created (in bytes)
BLOCK_SIZE	NUMBER	Block size of the datafile
NAME	VARCHAR2(513)	Name of the datafile
PLUGGED_IN	NUMBER	Describes whether the tablespace is plugged in. The value is 1 if the tablespace is plugged in and has not been made read/write, 0 if not.
BLOCK1_OFFSET	NUMBER	Offset from the beginning of the file to where the Oracle generic information begins. The exact length of the file can be computed as follows: BYTES + BLOCK1_OFFSET.
AUX_NAME	VARCHAR2(513)	Auxiliary name that has been set for this file via CONFIGURE AUXNAME
FIRST_NONLOGGED_SCN	NUMBER	First nonlogged SCN
FIRST_NONLOGGED_TIME	DATE	First nonlogged time

Renaming and Relocating Datafiles for Multiple Tablespaces

You can rename and relocate datafiles of one or more tablespaces using ALTER DATABASE statement with the RENAME FILE clause. This option is the only choice if you want to rename or relocate datafiles of several tablespaces in one operation, or rename or relocate datafiles of the SYSTEM tablespace. If the database must remain open, consider instead the procedure outlined in the previous section.

To rename datafiles of several tablespaces in one operation or to rename datafiles of the SYSTEM tablespace, you must have the ALTER DATABASE system privilege.

To rename datafiles in multiple tablespaces, follow these steps.

1. Ensure that the database is mounted but closed.
2. Copy the datafiles to be renamed to their new locations and new names, using the operating system.
3. Use ALTER DATABASE to rename the file pointers in the database's control file.

   For example, the following statement renames the datafiles/u02/oracle/rbdb1/sort01.dbf and /u02/oracle/rbdb1/user3.dbf to /u02/oracle/rbdb1/temp01.dbf and /u02/oracle/rbdb1/users03.dbf, respectively:

   ALTER DATABASE
       RENAME FILE '/u02/oracle/rbdb1/sort01.dbf',
                   '/u02/oracle/rbdb1/user3.dbf'
                TO '/u02/oracle/rbdb1/temp01.dbf',
                   '/u02/oracle/rbdb1/users03.dbf;
   
   

   The new files must already exist; this statement does not create the files. Also, always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile name exactly as it appears in the DBA_DATA_FILES view of the data dictionary.

4. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Relocating and Renaming Datafiles in a Single Tablespace

Here is an example that illustrates the steps involved for relocating a datafile.

Assume the following conditions:

• An open database has a tablespace named users that is made up of datafiles all located on the same disk.
• The datafiles of the users tablespace are to be relocated to different and separate disk drives.
• You are currently connected with administrator privileges to the open database.
• You have a current backup of the database.

Complete the following steps:

1. Identify the datafile names of interest.

   The following query of the data dictionary view DBA_DATA_FILES lists the datafile names and respective sizes (in bytes) of the users tablespace:

   SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES
   WHERE TABLESPACE_NAME = 'USERS';
   
   FILE_NAME                                  BYTES
   ------------------------------------------ ----------------
   /U02/ORACLE/RBDB1/USERS01.DBF              102400000
   /U02/ORACLE/RBDB1/USERS02.DBF              102400000
   
   

2. Take the tablespace containing the datafiles offline, or shut down the database and restart and mount it, leaving it closed. Either option closes the datafiles of the tablespace.
3. Copy the datafiles to their new locations and rename them using the operating system.
3. You can execute an operating system command to copy a file by using the SQL*Plus HOST command.
4. Rename the datafiles within Oracle.

   The datafile pointers for the files that make up the users tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.

   If the tablespace is offline but the database is open, use the ALTER TABLESPACE ... RENAME DATAFILE statement. If the database is mounted but closed, use the ALTER DATABASE ... RENAME FILE statement.

   ALTER TABLESPACE users
       RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf',
                       '/u02/oracle/rbdb1/users02.dbf'
                    TO '/u03/oracle/rbdb1/users01.dbf',
                       '/u04/oracle/rbdb1/users02.dbf';
   
   

5. Bring the tablespace online, or open the database.

   If the users tablespace is offline and the database is open, bring the tablespace back online. If the database is mounted but closed, open the database.

6. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Renaming and Relocating Datafiles for a Single Tablespace

Renaming Datafiles in a Single Tablespace

To rename datafiles from a single tablespace, complete the following steps:

1. Take the non-SYSTEM tablespace that contains the datafiles offline.

   For example:

   ALTER TABLESPACE users OFFLINE NORMAL;
   
   

2. Rename the datafiles using the operating system.
3. Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.

   For example, the following statement renames the datafiles /u02/oracle/rbdb1/user1.dbf and /u02/oracle/rbdb1/user2.dbf to/u02/oracle/rbdb1/users01.dbf and /u02/oracle/rbdb1/users02.dbf, respectively:

   ALTER TABLESPACE users
       RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
                       '/u02/oracle/rbdb1/user2.dbf'
                    TO '/u02/oracle/rbdb1/users01.dbf',
                       '/u02/oracle/rbdb1/users02.dbf';
   
   

   The new files must already exist; this statement does not create the files. Also, always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile name exactly as it appears in the DBA_DATA_FILES view of the data dictionary.

4. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Determine the Number of Datafiles

When starting an Oracle instance, the DB_FILES initialization parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance. This limit applies for the life of the instance. You can change the value of DB_FILES (by changing the initialization parameter setting), but the new value does not take effect until you shut down and restart the instance.

When determining a value for DB_FILES, take the following into consideration:

• If the value of DB_FILES is too low, you cannot add datafiles beyond the DB_FILES limit without first shutting down the database.
• If the value of DB_FILES is too high, memory is unnecessarily consumed.

Limitations When Adding Datafiles to a Tablespace

You can add datafiles to tablespaces, subject to the following limitations:

• Operating systems often impose a limit on the number of files a process can open simultaneously. More datafiles cannot be created when the operating system limit of open files is reached.
• Operating systems impose limits on the number and size of datafiles.
• Oracle imposes a maximum limit on the number of datafiles for any Oracle database opened by any instance. This limit is operating system specific.
• You cannot exceed the number of datafiles specified by the DB_FILES initialization parameter.
• When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. However, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.

Oracle datafiles

Oracle Datafiles are physical files of the operating system that store the data of all logical structures in the database. They must be explicitly created for each tablespace. Oracle assigns each datafile two associated file numbers, an absolute file number and a relative file number, that are used to uniquely identify it.

Absolute

Uniquely identifies a datafile in the database. In earlier releases of Oracle, the absolute file number may have been referred to as simply, the "file number."

Relative

Uniquely identifies a datafile within a tablespace. For small and medium size databases, relative file numbers usually have the same value as the absolute file number. However, when the number of datafiles in a database exceeds a threshold (typically 1023), the relative file number differs from the absolute file number.

Oracle Startup - Third Stage - Open Database

Open Database

The third and last startup step for an Oracle database is the open stage. When Oracle opens the database, it accesses all of the datafiles associated with the database. Once it has accessed the database datafiles, Oracle makes sure that all of the database datafiles are consistent.

Oracle Startup - Second Stage

Oracle Startup - Second Stage

Mount the database

When the startup command enters the mount stage, it opens and reads the control file. The control file is a binary file that tracks important database information, such as the location of the database datafiles.

In the mount stage, Oracle determines the location of the datafiles, but does not yet open them. Once the datafile locations have been identified, the database is ready to be opened.

Oracle Startup - First Stage

When you issue the startup command, the first thing the database will do is enter the nomount stage. During the nomount stage, Oracle first opens and reads the initialization parameter file (spfile/pfile init.ora) to see how the database is configured. For example, the sizes of all of the memory areas in Oracle are defined within the parameter file.

After the parameter file is accessed, the memory areas associated with the database instance are allocated. Also, during the nomount stage, the Oracle background processes are started. Together, we call these processes and the associated allocated memory the Oracle instance. Once the instance has started successfully, the database is considered to be in the nomount stage. If you issue the startup command, then Oracle will automatically move onto the next stage of the startup, the mount stage.

Oracle Startup

In order to start an oracle database you must first be logged into an account that has sysdba or sysoper privileges such as the SYS account.

When Oracle is trying to open your database, it goes through three distinct stages.

* Startup (nomount)

* Mount

* Open

SQL> startup

ORACLE instance started.

Total System Global Area 251658240 bytes

Fixed Size 788368 bytes

Variable Size 145750128 bytes

Database Buffers 104857600 bytes

Redo Buffers 262144 bytes

Database mounted.

Database opened.