Recreating the Controlfile in RAC
PURPOSE
This article describes how to recreate the control file in RAC.
WARNING:
The control file should only be rebuilt under very special circumstances:
- All current copies of the control file have been lost or are corrupted.
- It is required to change a "hard" database parameter that was set when the database was first created, such as MAXDATAFILES, MAXLOGFILES, MAXLOGHISTORY, etc.
- Restoring a backup in which the control file is corrupted or missing.
- Oracle Customer Support advises to do so.
SCOPE
For DBA's requiring to recreate the controlfile.
DETAILS
If recreating the controlfile from an existing one, use the following steps to recreate the control file. If all copies of the controlfile are lost, a new one will need to be generated using SQL. The syntax is available in the SQL Reference manual for all versions but consideration for Step 4 onward must be taken into account.
1. Connected to an open or mounted RAC instance via sqlplus, issue the following command to dump a trace file that contains a create controlfile script. The file will be generated in the trace directory on the local instance:
alter database backup controlfile to trace noresetlogs;
2. Find the trace file using "ls -ltr" in the trace directory, it will probably be the last or one of the last files listed as it will be very recent. Rename the file to an easy to remember name. In the example the name is create_control.sql.
3. Once the file is opened, remove all of the header information up to the "STARTUP NOMOUNT" command.
At this point the controlfile should look something like this:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/ocfs01/rac/redo01.log' SIZE 50M,
GROUP 2 '/ocfs01/rac/redo02.log' SIZE 50M,
GROUP 3 '/ocfs01/rac/redo03.log' SIZE 50M,
GROUP 4 '/ocfs01/rac/redo04.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/ocfs01/rac/system01.dbf',
'/ocfs01/rac/undotbs01.dbf',
'/ocfs01/rac/sysaux01.dbf',
'/ocfs01/rac/undotbs02.dbf',
'/ocfs01/rac/users01.dbf'
CHARACTER SET WE8ISO8859P1
RECOVER DATABASE
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/ocfs01/rac/temp01.dbf'
SIZE 167772160 REUSE AUTOEXTEND OFF;
RECOVER DATABASE
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/ocfs01/rac/temp01.dbf'
SIZE 524288000 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- The next step is optional if gv$thread does not show all the threads:
-- ALTER DATABASE ENABLE PUBLIC THREAD 2;
-- repeat for other threads if applicable
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/ocfs01/rac/redo01.log' SIZE 50M,
GROUP 2 '/ocfs01/rac/redo02.log' SIZE 50M,
GROUP 3 '/ocfs01/rac/redo03.log' SIZE 50M,
GROUP 4 '/ocfs01/rac/redo04.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/ocfs01/rac/system01.dbf',
'/ocfs01/rac/undotbs01.dbf',
'/ocfs01/rac/sysaux01.dbf',
'/ocfs01/rac/undotbs02.dbf',
'/ocfs01/rac/users01.dbf'
CHARACTER SET WE8ISO8859P1
RECOVER DATABASE
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/ocfs01/rac/temp01.dbf'
SIZE 167772160 REUSE AUTOEXTEND OFF;
RECOVER DATABASE
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/ocfs01/rac/temp01.dbf'
SIZE 524288000 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- The next step is optional if gv$thread does not show all the threads:
-- ALTER DATABASE ENABLE PUBLIC THREAD 2;
-- repeat for other threads if applicable
Change NOARCHIVELOG to ARCHIVELOG if the database is in ARCHIVELOG mode; note that this will be done automatically by the "alter database backup controlfile to trace" command.
IF THE ONLINE REDO LOGS HAVE BEEN REMOVED OR NEED TO BE RECREATED (RESETLOGS) will have to be used. It is highly recommended to take a full backup of the database after completing this procedure.
4. Shutdown all instances cleanly with shutdown immediate, shutdown transactional, or shutdown normal:
srvctl stop database -d orcl -o immediate
5. Take backup copies of the current controlfiles.
6. Confirm that the instance designated is for thread 1. This is because any redo log files designated in the create controlfile statement will go into thread 1. Errors can be produced if running from another instance. Make sure that the local init/spfile file states:
instance=1
thread=1
thread=1
7. Make sure the cluster_database=false parameter is set in the init/spfile to re-create the controlfile:
from spfile:
startup nomount
alter system set cluster_database=false scope=spfile;
shutdown
alter system set cluster_database=false scope=spfile;
shutdown
8. Now we are ready to run the script. Connect to SQL*Plus on thread 1 and execute the script:
sqlplus / as sysdba
SQL> @create_control.sql
SQL> @create_control.sql
9. Set the cluster_database=true parameter in the init/spfile:
alter system set cluster_database=true scope=spfile;
10. Shutdown the database:
SQL> shutdown immediate
11. Start all instances:
$ srvctl start database -d orcl
$ srvctl status database -d orcl
$ srvctl status database -d orcl
12. At the earliest convenience, take a full backup of the database.
=================================================================
Note--> This information collected from Oracle and all rights reserved for Oracle. knowledge purpose we are using this note not for commercial use.
Comments
Post a Comment
Oracle DBA Information