Monday 6 August 2012

11g R2 - DETERMINE AND SET SIZING PARAMETERS FOR DATABASE STRUCTURES (Part 1)


Hi,

My name is Mark Tiger, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g).

Gathering information for some DBA tasks can be time-consuming, even although the commands that you need to issue eventually can be over quite quickly.  I have gone through this process over and over again, and have decided to help other Oracle DBA’s in the community.

In this blog, I will give you the details of how to carry out those tasks, that typically need a lot of research, before you can do them.  I will try to present the information in an easy to understand way.  My hope is that this will save you lots of time in research, and help to make you more productive as an Oracle DBA.  The illustrations are primarily meant for Linux, since this is a good choice of platform.  However they are easily adaptable for versions of UNIX/AIX and windows etc.

DETERMINE AND SET SIZING PARAMETERS FOR DATABASE STRUCTURES  (Part 1)

Specifying a Fast Recovery Area

A fast recovery area is a location in which Oracle can store and manage files related to backup and recovery.  This area is different to the Database Area, which is where the Data Files, Control files, and online Redo Logs are kept.

There are two parameters that are needed in order to define this area.
DB_RECOVERY_FILE_DEST:  This is the location of the fast recovery area.  This can point to a Directory, or a file system, or an ASM(Automatic Storage Management)disk group.  This parameter may not point to a RAW file system.

In a RAC(Real Application Clusters) environment; this location must be a cluster file system, or an ASM disk group, or a shared directory configured through NFS.

DB_RECOVERY_FILE_DEST_SIZE: This specifies the maximum  bytes to be used by the Fast Recovery Area.  This parameter must be configured beore DB_RECOVERY_FILE_DEST is configured.

In a RAC environment, the settings for these two variables, must be the same on all instances.  If LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST have values, they must first be disabled, beore you can set up the Fast Recovery Area.  You can use LOG_ARCHIVE_DEST_n to define your archiving destinations.

If a local archiving location has not been  configured and LOG_ARCHIVE_DEST_1 has not been set; then LOG_ARCHIVE_DEST_1 is implicitly set to point to the fast recovery area.

The Fast Recovery Area can simplify backup and recovery operations for your database.

Specifying Control Files

What is a Control File

A control file is a small binary file that records the physical structure of the database.  The following information is included in the control file:
·         The Database Name
·         Names and locations of all the datafiles and redo log files
·         The timestamp of the database creation
·         The current log sequence number
·         Checkpoint information

The control file must be available for writing to by the Oracle Database Server, whenever the database is open.  Without the control file the database cannot be mounted, and recovery operations are compromised.

The control file is created at the same time that the database is created.  There should be at least two copies of the control; ideally three copies, on different file systems or disks.  It is possible to create the control files later, or to re-create them, in order to change certain settings.

Guidelines for control files

Provide Filenames for the Control Files

You specify the names, in the parameter file, using the parameter CONTROL_FILES.  The instance will open all the listed control files during startup, and keep them updated during database operation.

If you do not specify the names for the control files before database creation:
·         If you are not using Oracle Managed Files, then the database creates a control file with a default name.
·         If you are using Oracle Managed Files, then the parameters you use to enable Oracle Managed files, also determine the names and locations of the control files.
·         If you are using ASM, then you can define incomplete filenames in the DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST parameters.  ASM, will then create control files in the appropriate places.

Multiplex Control Files on Different Disks

Every Oracle Database should have at least two control files, each stored on a different physical disk.  Best practice would be three control files.  If one of the control files is damaged due to media failure, then the instance must be shutdown.  The good control file, can be copied to where the control file was corrupted.  Then the instance can be re-started, without needing any Media recovery.

Multiplexed control files have these behavioural properties.
·         The database writes to all the files listed in the CONTROL_FILES parameter.
·         During normal database operation, only the first file listed in the CONTROL_FILES parameter is read from.
·         If any of the control files becomes unavailable during database operation, the instance is not able to continue, and should be shutdown or aborted.

Oracle also recommends that there should be at least two copies of the redo log files on separate physical disk drives if possible.  It makes sense to store the control files with the redo log files.  This will improve the chances of having a good set of redo log files, plus a control file, in the event of media failure.

Back Up Control Files

You should start off by making a backup of your control files, and then every time you make any structural changes you should backup the control files again.  Every time you make a backup of the database, you should backup the control files at the same time.

Structural changes that warrant an immediate backup of the control files are:
·         Adding, dropping or renaming datafiles.
·         Altering a tablespace from read/write to read only, or vice-versa or adding or dropping a tablespace.
·         Adding or dropping Redo log files or Redo Log file Groups.





Sizing Control Files

On a small system, the default for control files is typically 50MB, and the sizing for best practicies should be between 100MB and up to a few Gigabytes.  Also verify the values for the following parameters, which can affect the default size of the control files.
MAXDATAFILES
MAXLOGFILES
MAXLOGMEMBERS
MAXLOGHISTORY
MAXINSTANCES

Initial Control files

The initial control file is created, when the database is created.  Either with DBCA(Database Configuration Assistant), or by issuing the CREATE DATABASE statement.  If there is no control file defined with database creation, then you will get an error.

The control file names are specified in the CONTROL_FILES parameter in the pfile or the spfile.  If you try to create a control file with the same name, as an existing one, but with a different size, then you can’t use the REUSE clause in the statement.

The default sizes of control files vary between different releases of the Oracle Database Server; and can also vary depending on the values of the following parameters:  MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXINSTANCES

Renaming, Relocating, and creating additional copies of the Control Files 

It is a matter of copying an existing control file to a new location, and adding the name into the CONTROL_FILES parameter, in order to create an additional copy of a control file.

To change the name or location of an existing control file, you copy the control file to the new name/and or location and update the CONTROL_FILES parameter.  You will have to shut down the database in order to run this process, to make sure, that a control file does not change, while you are busy.  For example if the system change number s updated, then the new control file will no longer be in sync.

Follow these steps:
1.       Shut down the database
2.       Copy a control file to a new location or file name, or to its duplexed file name.  This operation is carried out on the Operating system, and not within Oracle.
3.       Edit the CONTROL_FILES parameter in the initSID.ora(pfile), in order to add the new control file, or change the existing file.  Startup pfile=<fully qualified pfile name>
4.       Create or re-create the spfile, connected as sysdba:  SQL> create spfile from pfile;
5.       Restart the database, and it will automatically start from the spfile, your new CONTROL_FILE values will be permanently part of the database.

When and how to create new control files

These are some scenarios that will require you to create new control files
·         You have lost all your control files for the database; and you don’t have a backup of the control file.
·         You want to change the database name.  Oracle recommends using the DBNEWID utility for changing the database name and the DBID.
·         The compatibility level is set to a level earlier than 10.2.0, and you need to make a change to one of the following parameters:  MAXLOGFILES, MAXLOGMENBERS, MAXLOGHISTORY, MAXINSTANCES.  For example if you wanted to change MAXLOGMEMBERS from 3 to 10.

The Create Control file Statement

This statement can potentially damage your data files, redo log files.  Leaving a file out can result in loss of data to that file, or loss of access to the database.

If you want Forced Logging to continue in the new database, then you must use the FORCE LOGGING clause in the control file creation statement.

For example:
CREATE CONTROLFILE
   SET DATABASE TEST
   LOGFILE GROUP 1 (‘/u01/app/test/redo01_01.log’,
                                      ‘/u02/app/test/redo01_02.log),
                    GROUP 2 (‘/u01/app/test/redo02_01.log’,
                                      ‘/u02/app/test/redo02_02.log),
                    GROUP 3 (‘/u01/app/test/redo03_01.log’,
                                      ‘/u02/app/test/redo03_02.log)
   RESETLOGS
   DATAFILE ‘/u01/app/test/datafiles/system01.dbf’  SIZE 300M,
                      ‘/u01/app/test/datafiles/sysaux01.dbf’  SIZE 300M,
                      ‘/u01/app/test/datafiles/user01.dbf’  SIZE 300MB,
                      ‘/u01/app/test/datafiles/temp01.dbf’  SIZE 300M
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXLOGHISTORY 4000
MAXDATAFILES 2000
MAXINSTANCES 12
ARCHIVELOG;

Follow these steps to create a new control file:
1.       Make an accurate list of all the datafiles and redo log files in the database.
SQL> select name from v$datafile;
SQL> select member from v$logfile;
Verify this with:
SQL> select value from v$parameter where name = ‘control_files’;

If you can’t open the database anymore, then you can get a list of the datafiles and redo log files by searching through the operating system.  Keep in mind that any datafiles that are not included in the CREATE CONTROLFILE statement, will no longer be recoverable.  Also if you miss out any of the system tablespace datafiles, then you may not be able to recover the database.
2.       Shutdown the database normally if possible.
3.       Make operating system backups of all the datafiles and redo log files for the database.
4.       Start up the instance in nomount mode
SQL> startup nomount
5.       Issue the CREATE CONTROLFILE statement.  Specify the NORESETLOGS clause.  If you have lost any REDO LOG GROUPS in addition to the control files, or if you have renamed the database, then you will need to specify the RESETLOGS clause.
6.       Store a backup copy of the new control file in a safe place, preferably on an offline storage device.
7.       In the parameter file, edit the CONTROL_FILES parameter to include all the copies of the control file.  Change the DB_NAME parameter to reflect the new name if required.
8.       Recover the database if necessary.
If the control file was created using NORESETLOGS, then you can do a complete recovery; closed database recovery.
If the control file was created using RESETLOGS, then you must specify USING BACKUP CONTROL FILE.
9.       If you did not perform recovery, or performed complete closed database recovery, then you can open the database normally.
SQL> alter database open;
If you specified RESETLOGS when creating the control file, then you will need to open the database with RESETLOGS
SQL> alter database open resetlogs;

Checking for missing or extra files after creating a new controlfile

The first step is to check the alert log for any errors or messages relating to datafiles and redo log files.  If the database has detected inconsistencies between the actual datafiles, and the datafiles in the data dictionary.

If the data file exists in the data dictionary, but not in the control file.  In this case the database creates a placeholder entry in the control file in this format:  MISSINGnnnn  (nnnn being a decimal number).  MISSINGnnnn is flagged as being offline and needing recovery.

If MISSINGnnnn corresponds to a datafile that was read-only or “offline normal”.  Then you can make the datafile accessible by renaming MISSINGnnn to the actual name of the datafile.

If MISSINGnnnn corresponds to a datafile that was not in “offline normal” mode, or was not in read-only mode:  Then you cannot use the rename  operation to make the datafile accessible, because the datafile will require media recovery.  Media recovery is not possible because of the having executed the RESETLOGS option.  In this case you will have to drop the tablespace containing the datafile.

If the data file exists in the control file, but not in the data dictionary.  Then the database will remove references to this file from the new control file.

In both cases, whether there are extra files or missing files in the control file, an explanatory message will be written to the alert log; with the details of what was found.

Handing errors during CREATE CONTROLFILE

If you get certain errors, when trying to mount or open the database, after running “create controlfile”.  These are the common errors: ORA-01173, ORA-01176, ORA-01177, ORA-01215, ORA-01216.
The most likely cause for this is that you omitted a file from the CREATE CONTROLFILE statement, or included one that was extra, or could not be mapped to a physical file.

To remedy this start the process of creating the controlfile from step 3 above again.  That is to say:
3.) Make operating system backups of all the datafiles and redo log files for the database
4.) Start up the database in nomount mode etc.
This time make sure that you use the correct and fully qualified filenames.
 



Backing up Control Files

This is apart from backing them up in a RMAN backup, or as part of a cold backup of a database.           

You back then up using: ALTER DATABASE BACKUP CONTROLFILE
·         You can make a duplicate or a binary file copy of the current controlfile.
SQL> alter database backup controlfile to ‘/u01/app/backups/current_control.bkp’;

·         You can also create the SQL script to a trace file.  These captured statements, can be used to re-create your control-file.  The details of the directory and file name of the trace file will be captured in the alert log.  Otherwise you can look in the default trace directory, and more than likely the newest file, will be the one you are looking for.
SQL> alter database backup controlfile to trace;

Recovering a Control file using a current copy

Recovering from control file corruption using a control file copy

Assuming that you have more than one control file listed in the CONTROL_FILES parameter, and one of these control files is corrupt.  The corrupt one will be detailed in the alert log.  Assuming that the directory with the duplexed copy is still accessible, and the duplexed control file is still available.
  • Shut the instance down, and overwrite the bad copy with the duplexed good copy of the controlfile.
$ cp /u01/app/datafiles/test/control01.ctl /u04/app/datafiles/test/control03.ctl

  • If the location has changed, then edit the values in the initialization file to replace the bad location with the new location:
CONTROL_FILES=(/u01/app/datafiles/test/control01.ctl,
                                    /u02/app/datafiles/test/control02.ctl,
                                   /u04/app/datafiles/test/control03.ctl)

  • Login to SQL*Plus as sysdba
SQL> startup

Recovering from a corrupted control file with multiplexed control files is quick.  Without a multiplexed control file, you would have had to re-create the control file manually, which could take some time, and also result in other failures.

Dropping Control Files

You can also recover from the loss of a control file by dropping one of the multiplexed control files.  There should be two copies of the control files at all times, so you will need at least three defined control files, in order to drop one.

1.)     Shut down the database
2.)     Edit the CONTROL_FILES parameter in the initialization parameter file to exclude or delete the old control file
3.)     Restart thedatabase
The above operation does not remove the old control file from the OS, you can remove the physical file from the OS with OS commands, after you have removed it from the database configuration.

Control files data dictionary views

The following four dynamic views will display information about the control files:
V$DATABASE        - Display database information from the control file
V$CONTROLFILE – Lists the names of the control files
V$CONTROLFILE_RECORD_SECTION – Displays information about control file record sections
V$PARAMETER – Display the names of the control files as specified in the CONTROL_FILES parameter in the initialization parameter file, as well as the values of the other parameters



Mark Tiger,
Need a database health check, remote monitoring, support & maintenance, or a security Audit?

P.S. I am busy preparing Nine books on preparing for the Oracle 11g R2 OCM DBA exam.  Watch this spot for details on the books, as they start becoming available.

No comments:

Post a Comment