Problem Description
In RAC environment alert log of the database instance intermittently reports following message for a brief period of time.
ORA-00202: controlfile: '+DATA/database/control01.ctl'
ORA-17510: Attempt to do i/o beyond file size
However, after sometime errors are go away. Database functions normally since the errors are reported and no problem is seen from the front end.
Cause of the Errors Reported
If you have close look of the alert log file of the other instance in the database, you will notice following message.
kccrsz: expanded controlfile section < > from < > to < > records
requested to grow by < > record(s); added < > block(s) of records
The above message indicates that there was an expansion in the size of the control file observed on the other node at around the time the errors were reported.
The scenario here is that there is a small window where the resize of a control file took place on one node and before the new control file size got updated/refreshed on the other node, a select was issued against one of the fixed views that accessed the control file without this control file transaction.
If you look for the trace files generated due to the these errors, you will notice the v$ fixed views being accessed in the failing sql captured.
The errors ultimately being disappeared automatically because the new control file size would get refreshed across all the nodes.
Solution
Since errors are harmless so you can simply ignore the error message.
Showing posts with label Controlfiles. Show all posts
Showing posts with label Controlfiles. Show all posts
Sunday, September 19, 2010
Wednesday, January 20, 2010
RMAN-00571, RMAN-00569, RMAN-00571, RMAN-03002, RMAN-05021
Problem Description
In the standby database rman configuration of configure retention policy fails with error message RMAN-05021 as below.
RMAN> configure retention policy to redundancy 1;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 01/19/2010 04:00:23
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file
Cause of the Problem:
It is attempted to modify the configuration which cannot be changed for a BACKUP or STANDBY control file while the mounted control file was either BACKUP or STANDBY.
As it is standby database and database is mounted using standby control file so we can't change the retention policy using standby controlfile.
The following configurations can be changed only when connected to primary database instance that has CURRENT/CREATED control file type mounted:
1) CONFIGURE RETENTION POLICY
2) CONFIGURE EXCLUDE
3) CONFIGURE ARCHIVELOG DELETION POLICY
Solution of the Problem:
In order to change retention policy, configure exclude and archivelog deletion policy you must connect to primary database instance and execute the command.
Related Documents:
In the standby database rman configuration of configure retention policy fails with error message RMAN-05021 as below.
RMAN> configure retention policy to redundancy 1;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 01/19/2010 04:00:23
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file
Cause of the Problem:
It is attempted to modify the configuration which cannot be changed for a BACKUP or STANDBY control file while the mounted control file was either BACKUP or STANDBY.
As it is standby database and database is mounted using standby control file so we can't change the retention policy using standby controlfile.
The following configurations can be changed only when connected to primary database instance that has CURRENT/CREATED control file type mounted:
1) CONFIGURE RETENTION POLICY
2) CONFIGURE EXCLUDE
3) CONFIGURE ARCHIVELOG DELETION POLICY
Solution of the Problem:
In order to change retention policy, configure exclude and archivelog deletion policy you must connect to primary database instance and execute the command.
Related Documents:
How to Restore the Controlfile from Backup.
ORA-00214: Controlfile Version Inconsistent on Startup or Shutdown
Controlfile in Oracle Database.
New Feature of 10.2g: Eliminate Control File Re-Creation
Creating controlfile fails with ORA-01503, ORA-01161
Backup Database control file -User Managed
Recover database after only lose of all controlfiles
Tuesday, January 12, 2010
ORA-00205: error in identifying control file
Problem Description
Whenever you try to start your oracle database instance or mount your database it fails with ORA-00205 error message as below.
SQL> startup
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 306185108 bytes
Database Buffers 222298112 bytes
Redo Buffers 5844992 bytes
ORA-00205: error in identifying control file, check alert log for more info
Error Investigation
As error message "ORA-00205: error in identifying control file, check alert log for more info" suggests to look for alert log messages so immediately after getting this error message you should look for alert log message.
Based on this error message you might get various types of alert long entries which would really help you to solve your problem. Following is the several versions of error messages.
Error Message Version 01:
ORA-00202: control file: '/oracle/controlfile/contro101.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Fri Jan 8 11:19:42 2010
ORA-205 signalled during: ALTER DATABASE MOUNT...
Fri Jan 8 11:19:50 2010
Shutting down instance (abort)
Error Message Version 02:
ORA-00202: controlfile: '/oracle/controlfile/CONTROL01.CTL'
ORA-27086: skgfglk: unable to lock file - already in use
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.
Error Message Version 03:
ORA-00202: control file: 'F:\ORACLE\CONTROL.CTL'
ORA-27047: unable to read the header block of file
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 38) Reached the end of the file.
Error Message Version 04:
Errors in file /oracle/9.2.0/admin/bdump/abc_ckpt_3117.trc:
ORA-00206: error in writing (block 3, # blocks 1) of controlfile
ORA-00202: controlfile: '/oracle/data/ctl03.dbf'
ORA-27072: skgfdisp: I/O error
Linux Error: 30: Read-only file system
Additional information: 2
General Cause And Solution of ORA-00205 Problem:
The ORA-00205 problem is reported because the system could not find a control file of the specified name and size. The name of control file is specified by the CONTROL_FILES initialization parameter within spfile/pfile. While mounting oracle database oracle finds that in the disk all those files are not there or size is unmatched as it is specified within spfile/pfile.
If you see ORA-00205 is reported while starting up/mount the oracle database then check that the proper control filename is referenced in the CONTROL_FILES initialization parameter in the initialization parameter. If not check your correct spfile/pfile or fix location of CONTROL_FILES parameter within spfile/pfile and try again to mount the database.
Whenever you are using mirrored control files, that is, more than one control file is referenced in the initialization parameter file, remove the control filename listed in the message from the initialization parameter file and restart the instance. If the message does not recur, remove the problem control file from the initialization parameter file and create another copy of the control file with a new filename in the initialization parameter file.
Step by step Solution
Step 01: After you see ORA-00205 immediately look for alert log files for further investigation.
Step 02: Check the control_files parameter in your initialization parameter. You can simply check by ,
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string D:\APP\ARJU\ORADATA\A\CONTROL0
1.CTL, D:\APP\ARJU\ORADATA\A\C
ONTROL02.CTL, D:\APP\ARJU\ORAD
ATA\A\CONTROL03.CTL
Now compare this location with the location of controlfile in your disk system.
From the alert log if you see error message like version 1 i.e you have noticed "ORA-00202: control file: '/oracle/controlfile/contro101.ctl'" then from your CONTROL_FILES initialization parameter remove the entry /oracle/controlfile/contro101.ctl if you have multiple version of controlfiles and start the database. If you fail then again check for alert logs. If it is same error message like points to another controlfile then try to remove that control file too if you have at least three versions of controlfiles. If you fail then possibly all of your controlfiles are lost. In that case you need to restore controlfile from previous backup or create a new one if you don't have any previous backup of controlfile. In the post Recover database after only lose of all controlfiles it is discussed how you can recover controlfile. In the post Restore the Controlfile from Backup using RMAN it is discussed how you can restore controlfile from previous RMAN backup using RMAN. In the post How to create controlfile it is discussed how to create a controlfile whenever you are lost all controlfile and you don't have any backups.
If you see other version of error versions then take necessary actions as alert log suggests. For example if you see error message like version 2 that is
"ORA-00202: controlfile: '/oracle/controlfile/CONTROL01.CTL'
ORA-27086: skgfglk: unable to lock file - already in use"
then ensure that oracle database is shutdown cleanly and no other process is holding lock of the control file. After you ensure that try starting database again.
If you see error message like version 3 that is
"ORA-27047: unable to read the header block of file
OSD-04006: ReadFile() failure, unable to read from file"
then possibly your control file got corrupted and follow the same steps as it is demonstrated in step 2 for error version 01.
If you see error message like version 4 that is
"ORA-00202: controlfile: '/oracle/data/ctl03.dbf'
ORA-27072: skgfdisp: I/O error
Linux Error: 30: Read-only file system"
then ensure that your control file has proper permission set from operating system. That means it must have read, write permission to oracle owner user.
Step 03:
After you identify and solve the problem ensure that you have multiple copies of your CONTROL_FILES parameter onwards. If you use spfile in your database startup then after your nomount state of database and physically copied controlfiles to all locations as you want, you can point multiple copies of controlfile by issuing "ALTER SYSTEM SET CONTROL_FILES=..., ..." command from database. A similar example is demonstrated in the topic ORA-00214: Controlfile Version Inconsistent on Startup or Shutdown
Related Documents:
Whenever you try to start your oracle database instance or mount your database it fails with ORA-00205 error message as below.
SQL> startup
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 306185108 bytes
Database Buffers 222298112 bytes
Redo Buffers 5844992 bytes
ORA-00205: error in identifying control file, check alert log for more info
Error Investigation
As error message "ORA-00205: error in identifying control file, check alert log for more info" suggests to look for alert log messages so immediately after getting this error message you should look for alert log message.
Based on this error message you might get various types of alert long entries which would really help you to solve your problem. Following is the several versions of error messages.
Error Message Version 01:
ORA-00202: control file: '/oracle/controlfile/contro101.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Fri Jan 8 11:19:42 2010
ORA-205 signalled during: ALTER DATABASE MOUNT...
Fri Jan 8 11:19:50 2010
Shutting down instance (abort)
Error Message Version 02:
ORA-00202: controlfile: '/oracle/controlfile/CONTROL01.CTL'
ORA-27086: skgfglk: unable to lock file - already in use
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.
Error Message Version 03:
ORA-00202: control file: 'F:\ORACLE\CONTROL.CTL'
ORA-27047: unable to read the header block of file
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 38) Reached the end of the file.
Error Message Version 04:
Errors in file /oracle/9.2.0/admin/bdump/abc_ckpt_3117.trc:
ORA-00206: error in writing (block 3, # blocks 1) of controlfile
ORA-00202: controlfile: '/oracle/data/ctl03.dbf'
ORA-27072: skgfdisp: I/O error
Linux Error: 30: Read-only file system
Additional information: 2
General Cause And Solution of ORA-00205 Problem:
The ORA-00205 problem is reported because the system could not find a control file of the specified name and size. The name of control file is specified by the CONTROL_FILES initialization parameter within spfile/pfile. While mounting oracle database oracle finds that in the disk all those files are not there or size is unmatched as it is specified within spfile/pfile.
If you see ORA-00205 is reported while starting up/mount the oracle database then check that the proper control filename is referenced in the CONTROL_FILES initialization parameter in the initialization parameter. If not check your correct spfile/pfile or fix location of CONTROL_FILES parameter within spfile/pfile and try again to mount the database.
Whenever you are using mirrored control files, that is, more than one control file is referenced in the initialization parameter file, remove the control filename listed in the message from the initialization parameter file and restart the instance. If the message does not recur, remove the problem control file from the initialization parameter file and create another copy of the control file with a new filename in the initialization parameter file.
Step by step Solution
Step 01: After you see ORA-00205 immediately look for alert log files for further investigation.
Step 02: Check the control_files parameter in your initialization parameter. You can simply check by ,
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string D:\APP\ARJU\ORADATA\A\CONTROL0
1.CTL, D:\APP\ARJU\ORADATA\A\C
ONTROL02.CTL, D:\APP\ARJU\ORAD
ATA\A\CONTROL03.CTL
Now compare this location with the location of controlfile in your disk system.
From the alert log if you see error message like version 1 i.e you have noticed "ORA-00202: control file: '/oracle/controlfile/contro101.ctl'" then from your CONTROL_FILES initialization parameter remove the entry /oracle/controlfile/contro101.ctl if you have multiple version of controlfiles and start the database. If you fail then again check for alert logs. If it is same error message like points to another controlfile then try to remove that control file too if you have at least three versions of controlfiles. If you fail then possibly all of your controlfiles are lost. In that case you need to restore controlfile from previous backup or create a new one if you don't have any previous backup of controlfile. In the post Recover database after only lose of all controlfiles it is discussed how you can recover controlfile. In the post Restore the Controlfile from Backup using RMAN it is discussed how you can restore controlfile from previous RMAN backup using RMAN. In the post How to create controlfile it is discussed how to create a controlfile whenever you are lost all controlfile and you don't have any backups.
If you see other version of error versions then take necessary actions as alert log suggests. For example if you see error message like version 2 that is
"ORA-00202: controlfile: '/oracle/controlfile/CONTROL01.CTL'
ORA-27086: skgfglk: unable to lock file - already in use"
then ensure that oracle database is shutdown cleanly and no other process is holding lock of the control file. After you ensure that try starting database again.
If you see error message like version 3 that is
"ORA-27047: unable to read the header block of file
OSD-04006: ReadFile() failure, unable to read from file"
then possibly your control file got corrupted and follow the same steps as it is demonstrated in step 2 for error version 01.
If you see error message like version 4 that is
"ORA-00202: controlfile: '/oracle/data/ctl03.dbf'
ORA-27072: skgfdisp: I/O error
Linux Error: 30: Read-only file system"
then ensure that your control file has proper permission set from operating system. That means it must have read, write permission to oracle owner user.
Step 03:
After you identify and solve the problem ensure that you have multiple copies of your CONTROL_FILES parameter onwards. If you use spfile in your database startup then after your nomount state of database and physically copied controlfiles to all locations as you want, you can point multiple copies of controlfile by issuing "ALTER SYSTEM SET CONTROL_FILES=..., ..." command from database. A similar example is demonstrated in the topic ORA-00214: Controlfile Version Inconsistent on Startup or Shutdown
Related Documents:
How to Restore the Controlfile from Backup.
Wednesday, December 16, 2009
ORA-00600: internal error code, arguments: [2141]
Problem Description
While starting up or shutting down the database it fails with oracle error message ORA-00600: with the first argument 2141. A sample message from my screen,
SQL> shutdown immediate;
ORA-00600: internal error code, arguments: [2141], [4192191924], [0], [], [], [], [], []
Cause of the Problem
The problem happened due to incorrect control files. If you have different versions of controlfile or if you have wrong controlfile pointed inside spfile/pfile then while startup the database above bug will fire. Similarly after starting up the database if you replace your controlfile by wrong one then while shutting down the database above bug will fire.
Solution of the Problem
The solution of this problem is to correct control_files parameter settings within spfile/pfile whenever you start up the database. Below procedures will help you.
1) Whenever you start up the database make sure whether you start the database by pfile or spfile and also be sure about their location.
2) If you are not sure which file is used then issue "startup nomount" command after connecting as sqlplus / as sysdba to the database.
3) If you start your database with spfile then issuing "show parameter spfile" will locate the location of your spfile location. So simply create a pfile by issuing "create pfile='new_pfile' from spfile" will give you pfile and you can work with that.
4) Whether you start your database with spfile or pfile if you issue "show parameter control_files" it will list the control_files that is denoted by the control_files initialization parameter inside your spfile/pfile (by which one you started your database).
5) Note those locations and check whether the locations are correct. If those are not correct and if you use spfile you can change the locations by,
SQL> alter system set control_files='/oradata1/arju/newcontrol01.ctl', '/oradata1/arju/newcontrol02.ctl' scope=spfile;
and then "startup force". If you use pfile then you need to change manually after opening the pfile with a text editor.
6)If you get error still then check one by one control files as described in the post http://arjudba.blogspot.com/2008/04/ora-00214-controlfile-version.html
If you get this error message while you shutting down your database, then do a shutdown abort and follow the same steps as it is written for startup the database procedures.
Related Documents
http://arjudba.blogspot.com/2009/12/ora-01665-control-file-is-not-standby.html
http://arjudba.blogspot.com/2009/01/ora-00600-arguments-keltnfy-ldminit-46.html
http://arjudba.blogspot.com/2008/12/ora-07445-exception-encountered-core.html
http://arjudba.blogspot.com/2008/11/what-is-ora-00600-internal-error-code.html
http://arjudba.blogspot.com/2008/10/ora-00600-internal-error-code.html
http://arjudba.blogspot.com/2008/09/ora-07445-exception-encountered-core.html
While starting up or shutting down the database it fails with oracle error message ORA-00600: with the first argument 2141. A sample message from my screen,
SQL> shutdown immediate;
ORA-00600: internal error code, arguments: [2141], [4192191924], [0], [], [], [], [], []
Cause of the Problem
The problem happened due to incorrect control files. If you have different versions of controlfile or if you have wrong controlfile pointed inside spfile/pfile then while startup the database above bug will fire. Similarly after starting up the database if you replace your controlfile by wrong one then while shutting down the database above bug will fire.
Solution of the Problem
The solution of this problem is to correct control_files parameter settings within spfile/pfile whenever you start up the database. Below procedures will help you.
1) Whenever you start up the database make sure whether you start the database by pfile or spfile and also be sure about their location.
2) If you are not sure which file is used then issue "startup nomount" command after connecting as sqlplus / as sysdba to the database.
3) If you start your database with spfile then issuing "show parameter spfile" will locate the location of your spfile location. So simply create a pfile by issuing "create pfile='new_pfile' from spfile" will give you pfile and you can work with that.
4) Whether you start your database with spfile or pfile if you issue "show parameter control_files" it will list the control_files that is denoted by the control_files initialization parameter inside your spfile/pfile (by which one you started your database).
5) Note those locations and check whether the locations are correct. If those are not correct and if you use spfile you can change the locations by,
SQL> alter system set control_files='/oradata1/arju/newcontrol01.ctl', '/oradata1/arju/newcontrol02.ctl' scope=spfile;
and then "startup force". If you use pfile then you need to change manually after opening the pfile with a text editor.
6)If you get error still then check one by one control files as described in the post http://arjudba.blogspot.com/2008/04/ora-00214-controlfile-version.html
If you get this error message while you shutting down your database, then do a shutdown abort and follow the same steps as it is written for startup the database procedures.
Related Documents
http://arjudba.blogspot.com/2009/12/ora-01665-control-file-is-not-standby.html
http://arjudba.blogspot.com/2009/01/ora-00600-arguments-keltnfy-ldminit-46.html
http://arjudba.blogspot.com/2008/12/ora-07445-exception-encountered-core.html
http://arjudba.blogspot.com/2008/11/what-is-ora-00600-internal-error-code.html
http://arjudba.blogspot.com/2008/10/ora-00600-internal-error-code.html
http://arjudba.blogspot.com/2008/09/ora-07445-exception-encountered-core.html
ORA-01665: control file is not a standby control file
Problem Description
While recovering standby database, using command alter database recover managed standby database disconnect from session it fails with message ORA-01665: control file is not a standby control file like below.
SQL> alter database recover managed standby database disconnect from session;
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01665: control file is not a standby control file
Cause of the Problem
The error occurred because there was an attempt to mount, recover, or activate a standby database without a standby controlfile. In order to recover standby database you need standby controlfile. If you don't have standby controlfile and use normal controlfile then above error will occurred.
Solution of the Problem
The solution is to create a standby controlfile before attempting to use the database as a standby database.
1)So if you don't have a standby controlfile for your standby first create a standby controlfile from source database using command,
SQL>alter database create standby controlfile as 'standbyctlfile.ctl';
2)Transfer this standby controlfile into standby database.
3)Edit the standby database pfile control_files parameter in order to effect the changes.
4)Startup the database in nomount stage.
SQL> startup nomount pfile='your_pfile_location';
5)Create spfile from the pfile.
SQL> create spfile from pfile='your_pfile_location';
6)Shutdown the database.
SQL>shutdown immediate;
7)Start the database in mount state.
SQL>startup mount;
8)On the standby database, start redo apply by following command.
SQL>alter database recover managed standby database disconnect from session;
Note that only step 1) need to be performed in primary database only. All other steps will be done in standby database.
Related Documents
http://arjudba.blogspot.com/2009/04/different-types-of-standby-database-in.html
http://arjudba.blogspot.com/2009/04/what-is-oracle-data-guard.html
While recovering standby database, using command alter database recover managed standby database disconnect from session it fails with message ORA-01665: control file is not a standby control file like below.
SQL> alter database recover managed standby database disconnect from session;
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01665: control file is not a standby control file
Cause of the Problem
The error occurred because there was an attempt to mount, recover, or activate a standby database without a standby controlfile. In order to recover standby database you need standby controlfile. If you don't have standby controlfile and use normal controlfile then above error will occurred.
Solution of the Problem
The solution is to create a standby controlfile before attempting to use the database as a standby database.
1)So if you don't have a standby controlfile for your standby first create a standby controlfile from source database using command,
SQL>alter database create standby controlfile as 'standbyctlfile.ctl';
2)Transfer this standby controlfile into standby database.
3)Edit the standby database pfile control_files parameter in order to effect the changes.
4)Startup the database in nomount stage.
SQL> startup nomount pfile='your_pfile_location';
5)Create spfile from the pfile.
SQL> create spfile from pfile='your_pfile_location';
6)Shutdown the database.
SQL>shutdown immediate;
7)Start the database in mount state.
SQL>startup mount;
8)On the standby database, start redo apply by following command.
SQL>alter database recover managed standby database disconnect from session;
Note that only step 1) need to be performed in primary database only. All other steps will be done in standby database.
Related Documents
http://arjudba.blogspot.com/2009/04/different-types-of-standby-database-in.html
http://arjudba.blogspot.com/2009/04/what-is-oracle-data-guard.html
Saturday, December 5, 2009
Database Architecture Exercises - Control file Redo logfile Examples
The Oracle database uses two very important files for database support, stability, and recovery. The Control file is one of the most important files in the database, as it is the file that keeps track of where everything is and where it should be at any given time. Since the database itself only needs one Control File to operate with, multiplexing the Control File is a very important part of the Oracle Architecture. In this lab, you will create a third Control File for the database.
Another essential file structure for successful operation of an Oracle database is the Redo Log file. This file contains listings of every transaction that takes place in the database, and it is essential in database recovery and restoration. Log Files exist in Log Groups, and each Log Group must have at least one file--although it is recommended that there be a minimum of two per group and that they exist on separate disks. In this lab, you will also create a new Log Group containing two files.
The following steps will outline the processes needed to complete this lab. Keep in mind that the file paths shown in the lab specs may vary slightly from your file paths, depending on the database instance you have. The expected results examples given in each step are provided as a guide only. The specific results of your work may vary slightly, but should be similar enough to determine whether or not you have successfully completed the step.
VERY IMPORTANT – Setting up for your lab
Creating a SPOOL session: Be sure that you start a spool session before you start executing your SQL code when working on the lab. Your SPOOL file name should be DATABASE_lab3. If you do the lab in several sessions, be sure that you use a different file name so as not to overwrite the existing file (for example DATABASE_lab3, DATABASE_lab3a, DATABASE_lab3b etc.)
 | L A B S T E P S |  |
| Part 1 -- The Control File: Adding a new Control File Step 1: Locating the existing control files |
Where is the existing control file located and what is the name?
Hint: Query the dynamic performance view V$CONTROLFILE or V$PARAMETER, or execute the SHOW PARAMETER command to display the name and location of the control file.Your response will look similar to this:
NAME
------------------------------------------------------------ -------------------
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK1\ctl1001. ctl
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\ctl1002. ctl
2 rows selected.
| Step 2: Add a new control file |
Hints:
- In your current SQL*Plus session, issue the command to shut down the database.
- On your mapped network drive, go to the DISK1 directory and physically copy the existing control file in that directory to the directory DISK3. Rename the file to CTLtest3.ctl
- Next, go to your initol###.ora file found in the PFILE directory of your mapped drive. Modify the “Control files” parameter to include the new file name, so that your parameter listing looks similar to the one below. (You will be adding a third control file path in the parameter listing for the control file). Your new listing should look similar to the one below.
Control_files=(‘F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\ DISK1\ctl1001.ctl’,
‘F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\ctl1002. ctl',
‘F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ ORADATA\DISK3\ctltest3.ctl’)
- Save the control file.
- Now go back to yrou SQL*Plus session and issue the STARTUP command to restart the database, using your init.ora parameter file with the new setting. Your startup command should look similar to the following:
startup pfile= O:\oracle\admin\pfile\init###.ora
| Step 3: Verify the new control file |
Your response will look similar to this:
NAME
------------------------------------------------------------ -----------
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK1\ctl1001. ctl
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\ctl1002. ctl
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK3\ctltest3. ctl
2 rows selected.
| Step 4: Recovering from a corrupted Control file |
- To begin, issue a SHUTDOWN IMMEDIATE (make sure you are logged in as the SYS user).
- Next, go into your mapped directory structure and find the ctltest3.ctl file in the Disk3 folder. Right-click on the file and select Open With => Notepad from the options list. When the file opens, press the space bar once and then close and save the file. You have just corrupted your database!
- Now, once again, start up your database using the init.ora file for your instance, as you did in step 2. What happened? Did the database start? Go find your alert log (Hint: bdump folder in your OFA structure), copy the part of the entry that references the problem (look down at the bottom of the file), and save it to a text file to be turned in with the lab work.
- Ok, so now we need to fix this problem. Execute the following steps to recover your database from this problem.
- First, shut down the database by issuing a SHUTDOWN IMMEDIATE.
- Next, go back into your init.ora file in your mapped directories and remove the line that you just added that has the path to the ctltest3.ctl file. Make sure that you have both original files in single quotes with a comma separating them, and that there is not a comma at the end before the closing parenthesis. Close the file and save it.
- Now go back to the SQL*Plus editor and start up your database using the init.ora file, as you have done before.
| Part 2 -- The Redo Logs: Working with Redo Logs Step 5: Find the location of existing Redo Log files and Groups |
List the number and location of existing log files and display the number of redo log groups and members your database has:
Hints:Your response will look similar to this for A.
- Query the dynamic performance views V$LOGFILE to display the location.
- Use the dynamic performance view V$LOG to display the number of redo log file groups and members.
MEMBERYour response will look similar to this for B.
------------------------------------------------------------ --------
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\LOG1A01. RDO
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK3\LOG2A01. RDO
2 rows selected.
GROUP# MEMBERS
---------- ------------
1 1
2 1
2 rows selected.
| Step 6: Determine current Archive Mode |
Hint: Query the dynamic performance views V$DATABASE to show the database mode.Your response should look similar to this:
LOG_MODE
---------------
NOARCHIVELOG
1 row selected.
| Step7: Determine if Archiving is enabled |
Hint: Query the dynamic performance views V$INSTANCE to verify if the archiving is enabled.Your response should look similar to this:
ARCHIVE
---------
STOPPED
1 row selected.
| Step 8: Adding Redo Log Members |
Hint: Execute the ALTER DATABASE ADD LOGFILE MEMBER command to add a redo log member to each group. Then query the dynamic performance views V$LOGFILE to verify your result.Your response should look similar to this:
Database altered.
Database altered.
GROUP# STATUS MEMBER
--------- -------- ------------------------------------------------------------ ----
1 STALE F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\LOG1A01. RDO
2 F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK3\LOG2A01. RDO
1 INVALID F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK3\LOG1B01. RDO
2 INVALID F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\LOG2B01. RDO
4 rows selected.
| Step 9: Add a new Log Group |
Your response should look similar to this:
Hint: Execute the ALTER DATABASE ADD LOGFILE GROUP command to create a new group. Remember that you can add both files to the new group at one time. To verify your work, query the performance view V$LOG.
Database altered.Now, to verify your work, query the performance view V$LOG.
Your response should look similar to this:
GROUP# MEMBERSThis concludes the Lab for Week 3. Be sure to enter a SPOOL OFF command to end your spool session, to save your output file.
---------- ----------
1 2
2 2
3 2
3 rows selected.
Sunday, August 31, 2008
Recover database after only lose of all controlfiles
This example is based on,
You have lost all your current and backup of controlfile.
You have avaiable your current data files.
You have available your online redo log files.
In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and you don't have any backup of your controlfile.
Let's start by deleting controlfile of my running database.
1.In order to know the controlfiles of my database issue,
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/control01.ctl
/oradata2/arjudba/arjudba/control02.ctl
/oradata2/arjudba/arjudba/control03.ctl
2.Delete all copies of controlfile. Here with one command I have deleted. But before deleting with one such command be conscious that this command will not delete other important files. You can delete files one by one.
SQL> !rm /oradata2/arjudba/arjudba/control0*
3.Now let's see whether controlfile is available or not by issuing following command.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2020416 bytes
Variable Size 121637824 bytes
Database Buffers 184549376 bytes
Redo Buffers 6365184 bytes
ORA-00205: error in identifying control file, check alert log for more info
As it can't read controlfile so ORA-00205 arises.
4.From this state is your situation what you need to do. If you have previously issued ALTER DATABASE BACKUP CONTROLFILE TO TRACE then use that file and modify. If you don't have any backup of controlfile then you have to create a new one as below. Note that you have to remember the name of datafile and online redo log file.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
5.Save the script and run it inside SQL*plus.
SQL> @/oradata2/ctl.ctl
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
6. At this stage your database is mounted. You need to recover it.
SQL> recover database;
Media recovery complete.
7.Open the database after recovery completes.
SQL> alter database open;
Database altered.
8.As Create controlfile statement does not include Temp tablespace you may need to add it.
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata2/arjudba/arjudba/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Related Documents
Recover database after missing online redo logs and all controlfiles.
Purpose and Restriction of Recover Command in Oracle
Restore and Recover database in Noarchivelog Mode
You have lost all your current and backup of controlfile.
You have avaiable your current data files.
You have available your online redo log files.
In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and you don't have any backup of your controlfile.
Let's start by deleting controlfile of my running database.
1.In order to know the controlfiles of my database issue,
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/control01.ctl
/oradata2/arjudba/arjudba/control02.ctl
/oradata2/arjudba/arjudba/control03.ctl
2.Delete all copies of controlfile. Here with one command I have deleted. But before deleting with one such command be conscious that this command will not delete other important files. You can delete files one by one.
SQL> !rm /oradata2/arjudba/arjudba/control0*
3.Now let's see whether controlfile is available or not by issuing following command.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2020416 bytes
Variable Size 121637824 bytes
Database Buffers 184549376 bytes
Redo Buffers 6365184 bytes
ORA-00205: error in identifying control file, check alert log for more info
As it can't read controlfile so ORA-00205 arises.
4.From this state is your situation what you need to do. If you have previously issued ALTER DATABASE BACKUP CONTROLFILE TO TRACE then use that file and modify. If you don't have any backup of controlfile then you have to create a new one as below. Note that you have to remember the name of datafile and online redo log file.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
5.Save the script and run it inside SQL*plus.
SQL> @/oradata2/ctl.ctl
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
6. At this stage your database is mounted. You need to recover it.
SQL> recover database;
Media recovery complete.
7.Open the database after recovery completes.
SQL> alter database open;
Database altered.
8.As Create controlfile statement does not include Temp tablespace you may need to add it.
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata2/arjudba/arjudba/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Related Documents
Recover database after missing online redo logs and all controlfiles.
Purpose and Restriction of Recover Command in Oracle
Restore and Recover database in Noarchivelog Mode
Monday, August 18, 2008
MAXDATAFILES , DB_FILES parameters and ORA-00059
Problem Description
Whenever I try to create tablespace it fails with error message, ORA-00059.
SQL> create tablespace test_tbs;
create tablespace test_tbs
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
Cause of The Problem
You have reached the limit of DB_FILES parameter. Before entering into solution part let's have an idea about DB_FILES and MAXDATAFILES parameter.
The DB_FILES parameter limits the maximum number of datafile can exist in oracle database. We can't change this parameter dynamically. We have to change it spfile by using ALTER SYSTEM .... SCOPE=SPFILE or in the pfile.
And the MAXDATAFILES parameter you can find with the CREATE DATABASE command or in CREATE CONTROLFILE command. It is also a limitation of maximum number of datafiles can be in the datafile. But starting from oracle 8 this hard limit parameter can be easily expanded up to DB_FILES parameter. So, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
So, if you are after oracle 8i then we should just forget about MAXDATAFILES parameter. We should rather think about DB_FILES parameter.
Solution of The Problem
If you use pfile to startup the database then edit pfile and add/modify the DB_FILES parameter to a greater value so that it can accommodate higher number of datafiles. Then start the database using that pfile.
If you use spfile to startup the database then issue,
SQL> alter system set db_files=300 scope=spfile;
System altered.
Then start your database and either create tablespace or add datafile to an existing tablespace. Hopefully it will work.
SQL> conn / as sysdba
SQL> startup force
SQL> CREATE TABLESPACE or ALTER TABLESPACE command to add datafile.
Related Documents
http://arjudba.blogspot.com/2008/07/ora-01667-cannot-add-any-more.html
http://arjudba.blogspot.com/2008/09/list-of-parameters-that-must-have.html
http://arjudba.blogspot.com/2008/08/possible-limitations-when-adding.html
Whenever I try to create tablespace it fails with error message, ORA-00059.
SQL> create tablespace test_tbs;
create tablespace test_tbs
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
Cause of The Problem
You have reached the limit of DB_FILES parameter. Before entering into solution part let's have an idea about DB_FILES and MAXDATAFILES parameter.
The DB_FILES parameter limits the maximum number of datafile can exist in oracle database. We can't change this parameter dynamically. We have to change it spfile by using ALTER SYSTEM .... SCOPE=SPFILE or in the pfile.
And the MAXDATAFILES parameter you can find with the CREATE DATABASE command or in CREATE CONTROLFILE command. It is also a limitation of maximum number of datafiles can be in the datafile. But starting from oracle 8 this hard limit parameter can be easily expanded up to DB_FILES parameter. So, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
So, if you are after oracle 8i then we should just forget about MAXDATAFILES parameter. We should rather think about DB_FILES parameter.
Solution of The Problem
If you use pfile to startup the database then edit pfile and add/modify the DB_FILES parameter to a greater value so that it can accommodate higher number of datafiles. Then start the database using that pfile.
If you use spfile to startup the database then issue,
SQL> alter system set db_files=300 scope=spfile;
System altered.
Then start your database and either create tablespace or add datafile to an existing tablespace. Hopefully it will work.
SQL> conn / as sysdba
SQL> startup force
SQL> CREATE TABLESPACE or ALTER TABLESPACE command to add datafile.
Related Documents
http://arjudba.blogspot.com/2008/07/ora-01667-cannot-add-any-more.html
http://arjudba.blogspot.com/2008/09/list-of-parameters-that-must-have.html
http://arjudba.blogspot.com/2008/08/possible-limitations-when-adding.html
Wednesday, July 30, 2008
ORA-01667: cannot add any more tablespaces: limit of exceeded
If your database version is higher than 8 suppose 10.2g then when MAXDATAFILES of your control file reached then MAXDATAFILES parameter would expand automatically unless you hit bug. You can see the post here,
New Feature of 10.2g Eliminate Control file Recreation
However if you are before 8 then while creating any tablespace and while adding a datafile to a tablespace you may get ORA-01667: cannot add any more tablespaces: limit of exceeded
How to Solve the Problem
Whenever you get ORA-01667 then your DB_FILES parameter have good settings and as you have older version of oracle , so your controlfile could not automatically expanded. So the solution is to either recreate the controlfile or recreate the database.
There is MAXDATAFILES parameter in the control file. Your total number of database datafiles exceed MAXDATAFILES limit. You have to enlarge it. Here is the procedure.
1)Take a text backup of the controlfile.
SQL> alter database backup controlfile to trace as '/oradata2/1.ctl'
2)Open the backup controlfile and edit it
$ vi /oradata2/1.ctl
MAXDATAFILES 100 --Edit it
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
Delete all lines after this. You can press dG in vi editor to delete rest of lines.
In the text backup of controlfile you can see two versions of the controlfile. NORESETLOGS version and RESETLOGS version. Remove RESETLOGS version.
3)Save it and shutdown database.
SQL> shutdown abort;
4)Execute the saved controlfile script.
@/oradata2/1.ctl
Now you can add more datafile to your database up to MAXDATAFILES.
Related Documents
MAXDATAFILES , DB_FILES parameters and ORA-00059
How to Resize a Datafile
ORA-01667: cannot add any more tablespaces: limit of exceeded
New Feature of 10.2g: Eliminate Control File Re-Creation
When and How to Recreate the Controlfile
New Feature of 10.2g Eliminate Control file Recreation
However if you are before 8 then while creating any tablespace and while adding a datafile to a tablespace you may get ORA-01667: cannot add any more tablespaces: limit of exceeded
How to Solve the Problem
Whenever you get ORA-01667 then your DB_FILES parameter have good settings and as you have older version of oracle , so your controlfile could not automatically expanded. So the solution is to either recreate the controlfile or recreate the database.
There is MAXDATAFILES parameter in the control file. Your total number of database datafiles exceed MAXDATAFILES limit. You have to enlarge it. Here is the procedure.
1)Take a text backup of the controlfile.
SQL> alter database backup controlfile to trace as '/oradata2/1.ctl'
2)Open the backup controlfile and edit it
$ vi /oradata2/1.ctl
MAXDATAFILES 100 --Edit it
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
Delete all lines after this. You can press dG in vi editor to delete rest of lines.
In the text backup of controlfile you can see two versions of the controlfile. NORESETLOGS version and RESETLOGS version. Remove RESETLOGS version.
3)Save it and shutdown database.
SQL> shutdown abort;
4)Execute the saved controlfile script.
@/oradata2/1.ctl
Now you can add more datafile to your database up to MAXDATAFILES.
Related Documents
MAXDATAFILES , DB_FILES parameters and ORA-00059
How to Resize a Datafile
ORA-01667: cannot add any more tablespaces: limit of exceeded
New Feature of 10.2g: Eliminate Control File Re-Creation
When and How to Recreate the Controlfile
Monday, May 19, 2008
What you do if all copies of your control files are lost
What you will do if you lost of your all copies of control file? Will you re-create a new one or will you restore a controlfile from backup? What will be your backup command? In the following section I have depicted in which scenario what we need to do.
1)Both Data files and Online Redo logs are in tact: In this case you have only lost your control file and every other things are ok. 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 the database with RESETLOGS.
2)Data files are intact but Online Redo logs are lost: In this case you have lost your online redo log file also. If the online logs contain redo necessary for recovery, then re-create the control file. Because the online redo logs are inaccessible, open the database with RESETLOGS options. But when the online logs are accessible it is not necessary to OPEN RESETLOGS after recovery with a created control file.
3)Data files are lost but online redo logs are available: In this restore a backup control file , perform complete recovery and open that database with resetlogs option.
4)Both Data files and redo logs file are lost: In this scenario restore a backup control file , perform incomplete recovery and open the database with resetlogs option.
Related Documents:
-------------------------
User Managed Restore Operation
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
1)Both Data files and Online Redo logs are in tact: In this case you have only lost your control file and every other things are ok. 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 the database with RESETLOGS.
2)Data files are intact but Online Redo logs are lost: In this case you have lost your online redo log file also. If the online logs contain redo necessary for recovery, then re-create the control file. Because the online redo logs are inaccessible, open the database with RESETLOGS options. But when the online logs are accessible it is not necessary to OPEN RESETLOGS after recovery with a created control file.
3)Data files are lost but online redo logs are available: In this restore a backup control file , perform complete recovery and open that database with resetlogs option.
4)Both Data files and redo logs file are lost: In this scenario restore a backup control file , perform incomplete recovery and open the database with resetlogs option.
Related Documents:
-------------------------
User Managed Restore Operation
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
Wednesday, May 14, 2008
File size is not a multiple of logical block size ORA-00202,ORA-27046
Error Description:
-------------------------
Whenever you tried to start the database the database failed to mount. It retuen the following error message.
ORA-00202: controlfile: '/oradata2/data1/dbase/control01.ctl'
ORA-27046: file size is not a multiple of logical block size
Cause of the Problem:
------------------------
All of the control files were corrupted.
Solution of the Problem:
----------------------------
A)If you have backup of control file then restore the control file. How to restore control file from backup is discussed on Restore Control File from backup
B)If you don't have control file then re-create it.
How to create control file is discussed on, How to create control file
-------------------------
Whenever you tried to start the database the database failed to mount. It retuen the following error message.
ORA-00202: controlfile: '/oradata2/data1/dbase/control01.ctl'
ORA-27046: file size is not a multiple of logical block size
Cause of the Problem:
------------------------
All of the control files were corrupted.
Solution of the Problem:
----------------------------
A)If you have backup of control file then restore the control file. How to restore control file from backup is discussed on Restore Control File from backup
B)If you don't have control file then re-create it.
How to create control file is discussed on, How to create control file
Monday, May 5, 2008
How to Restore the Controlfile from Backup.
If you loss or if your all copies of control file is corrupted and if you have backup of your control file then it is required to restore your control file from your backup.
Restore control file to default location:
----------------------------------------------
The default location is defined by CONTROL_FILES parameter of pfile/spfile. If you don't specify any location while restoring your control file then the control file will be restored to the location set by CONTROL_FILES parameter. TO determine DBID check http://arjudba.blogspot.com/2008/05/how-to-discover-find-dbid.html
RMAN>SET DBID 3386862614
RMAN> RUN {
RESTORE CONTROLFILE FROM AUTOBACKUP;
}
Restore of the Control File from Control File Autobackup
-------------------------------------------------------------
If you are not using a recovery catalog, you must restore your control file from an autobackup. The database must be in a NOMOUNT state. And you have to set DBID. RMAN uses the autobackup format and DBID to determine where to find for the control file autobackup.
RMAN>SET DBID 3386862614
RMAN> RUN {
SET CONTROLFILE AUTOBACKUP FORMAT
FOR DEVICE TYPE DISK TO 'autobackup_format';
RESTORE CONTROLFILE FROM AUTOBACKUP;
}
Restore of the Control File When Using a Flash Recovery Area
---------------------------------------------------------------------
Suppose you restored a backup of the control file. Now in that control file the backup information may not updated/full. May be it contains only current backup information of that session while taking backup. If you use flash recovery area then RMAN automatically catalog the backups in the flash recovery area. As a result the restored control file has a complete and accurate record of all backups in your flash recovery area and any other backups that were known to the control file at the time of the backup.
Restoring a Control File When Using a Recovery Catalog
------------------------------------------------------------------
The recovery catalog contains a complete record of your backups, including backups of the control
file. Therefore, you do not have to specify your DBID or control file autobackup format.
Just use,
$rman TARGET / CATALOG catdb/catdb
RMAN> RESTORE CONTROLFILE;
Restore of the Control File From a Known Location
-----------------------------------------------------
If you know the backuppiece of controlfile or any copy then simply you can use,
RMAN> RESTORE CONTROLFILE from 'filename';
Restore of the Control File to a New Location
---------------------------------------------------
In prior cases RMAN restore the control file to the location specified by CONTROL_FILES parameter of the spfile or pfile.
If you want to restore the control file to another location use,
RMAN>RESTORE CONTROLFILE TO 'give_here_new_location';
You can also change CONTROL_FILES parameter and then perform RESTORE CONTROLFILE to change location.
Limitations When Using a Backup Control File
------------------------------------------------
After you restore your database using a backup control file, you must run RECOVER DATABASE and perform an OPEN RESETLOGS on the database.
Related Documents:
--------------------------------------
How to change DBID
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
Restore control file to default location:
----------------------------------------------
The default location is defined by CONTROL_FILES parameter of pfile/spfile. If you don't specify any location while restoring your control file then the control file will be restored to the location set by CONTROL_FILES parameter. TO determine DBID check http://arjudba.blogspot.com/2008/05/how-to-discover-find-dbid.html
RMAN>SET DBID 3386862614
RMAN> RUN {
RESTORE CONTROLFILE FROM AUTOBACKUP;
}
Restore of the Control File from Control File Autobackup
-------------------------------------------------------------
If you are not using a recovery catalog, you must restore your control file from an autobackup. The database must be in a NOMOUNT state. And you have to set DBID. RMAN uses the autobackup format and DBID to determine where to find for the control file autobackup.
RMAN>SET DBID 3386862614
RMAN> RUN {
SET CONTROLFILE AUTOBACKUP FORMAT
FOR DEVICE TYPE DISK TO 'autobackup_format';
RESTORE CONTROLFILE FROM AUTOBACKUP;
}
Restore of the Control File When Using a Flash Recovery Area
---------------------------------------------------------------------
Suppose you restored a backup of the control file. Now in that control file the backup information may not updated/full. May be it contains only current backup information of that session while taking backup. If you use flash recovery area then RMAN automatically catalog the backups in the flash recovery area. As a result the restored control file has a complete and accurate record of all backups in your flash recovery area and any other backups that were known to the control file at the time of the backup.
Restoring a Control File When Using a Recovery Catalog
------------------------------------------------------------------
The recovery catalog contains a complete record of your backups, including backups of the control
file. Therefore, you do not have to specify your DBID or control file autobackup format.
Just use,
$rman TARGET / CATALOG catdb/catdb
RMAN> RESTORE CONTROLFILE;
Restore of the Control File From a Known Location
-----------------------------------------------------
If you know the backuppiece of controlfile or any copy then simply you can use,
RMAN> RESTORE CONTROLFILE from 'filename';
Restore of the Control File to a New Location
---------------------------------------------------
In prior cases RMAN restore the control file to the location specified by CONTROL_FILES parameter of the spfile or pfile.
If you want to restore the control file to another location use,
RMAN>RESTORE CONTROLFILE TO 'give_here_new_location';
You can also change CONTROL_FILES parameter and then perform RESTORE CONTROLFILE to change location.
Limitations When Using a Backup Control File
------------------------------------------------
After you restore your database using a backup control file, you must run RECOVER DATABASE and perform an OPEN RESETLOGS on the database.
Related Documents:
--------------------------------------
How to change DBID
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
Tuesday, April 22, 2008
New Feature of 10.2g: Eliminate Control File Re-Creation
Before Oracle 10.2g if we need to change the limit of MAXLOGFILE, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES then the possible solutions is either RE-create new controlfile or create a new database.
But from Oracle 10.2g all sections of the control file are now automatically extended when they run out of space. This means that there is no longer a requirement to re-create the control file when changes in the configuration parameter the MAXLOGFILE, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES are needed.
Two different Sections of Control file:
--------------------------------------------
1)Circularly reusable Sections:
The CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused. Example of circularly reusable records are archive log records and various backup records.
2)Not circularly reusable Sections:
Records such as datafile, tablespace, and redo thread records, which are never reused unless the corresponding object is dropped from the tablespace.
For the circularly reusable sections the behaviour remain same with previous version.
The new feature in Oracle 10.2g is that for the non-reusable records we now also extend the control file size if we go over the previous limit.
Though the values for MAXLOGFILE, MAXLOGMEMBERS,MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES are still useful since they initialize the control file at a certain size, but they no longer set a hard limit for the number of records in the control file.
Therefore 10.2g onwards, we can get rid of re-creating controlfile whenever we need change parameter like MAXLOGFILE, MAXLOGMEMBERS,MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES and keep the database alive.
Related Documents:
----------------------
How and When to Re-create Controlfile
But from Oracle 10.2g all sections of the control file are now automatically extended when they run out of space. This means that there is no longer a requirement to re-create the control file when changes in the configuration parameter the MAXLOGFILE, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES are needed.
Two different Sections of Control file:
--------------------------------------------
1)Circularly reusable Sections:
The CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused. Example of circularly reusable records are archive log records and various backup records.
2)Not circularly reusable Sections:
Records such as datafile, tablespace, and redo thread records, which are never reused unless the corresponding object is dropped from the tablespace.
For the circularly reusable sections the behaviour remain same with previous version.
The new feature in Oracle 10.2g is that for the non-reusable records we now also extend the control file size if we go over the previous limit.
Though the values for MAXLOGFILE, MAXLOGMEMBERS,MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES are still useful since they initialize the control file at a certain size, but they no longer set a hard limit for the number of records in the control file.
Therefore 10.2g onwards, we can get rid of re-creating controlfile whenever we need change parameter like MAXLOGFILE, MAXLOGMEMBERS,MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES and keep the database alive.
Related Documents:
----------------------
How and When to Re-create Controlfile
Thursday, April 17, 2008
kccrsz: denied expansion of controlfile section 9 by 65535 record(s)
Problem Description:
-------------------------
The machine did not respond properly and in the alert log you the got the following.
kccrsz: denied expansion of controlfile section 9 by 65535 record(s)
the number of records is already at maximum value (65535)
krcpwnc: following controlfile record written over:
RECID #528019 Recno 3739 Record timestamp
04/13/08 16:26:16
Thread=1 Seq#=528019 Link-Recid=528018
Error Description:
----------------------
1)Check the default value of control file record keep time.
SQL> show parameter control_file_record_keep_time
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
2)Check the MAXLOGHISTORY database parameter.
SQL>alter database backup controlfile to trace as '/oradata/1.txt'
Check MAXLOGHISTORY value from /oradata/1.txt
Or, you can check RECORDS_TOTAL value from v$controlfile_record_section
SQL> select * from v$controlfile_record_section where type='LOG HISTORY' ;
Now the problem is occurred due to control_file_record_keep_time and MAXLOGHISTORY settings.
The MAXLOGHISTORY increases dynamically when the CONTROL_FILE_RECORD_KEEP_TIME is set to a value different from 0, but does not exceed MAXLOGHISTORY (65535).Once reached, the message appears in the alert log.
From above error it is seen the MAXLOGHISTORY parameter has already reached the maximum of 65535 and it cannot be increased anymore.
Solution of The problem:
---------------------------
1)Set the CONTROL_FILE_RECOED_KEEP_TIME to zero.
alter system set control_file_record_keep_time=0;
or.
2)Change the MAXLOGHISTORY parameter.
The compatibility level is set to a value that is earlier than 10.2.0, and you must create new new control file by CREATE CONTROLFILE command. If compatibility is 10.2.0 or later, you do not have to create new control files when you make such a change; the control files automatically expand, if necessary, to accommodate the new configuration information.
Caution:
-----------
Setting control_file_record_keep_time to zero is dangerous for making RMAN backups. In that case use recovery catalog as repository for RMAN backup instead of control file.
Related Link:
-------------
Create Controlfile
Controlfile Information
-------------------------
The machine did not respond properly and in the alert log you the got the following.
kccrsz: denied expansion of controlfile section 9 by 65535 record(s)
the number of records is already at maximum value (65535)
krcpwnc: following controlfile record written over:
RECID #528019 Recno 3739 Record timestamp
04/13/08 16:26:16
Thread=1 Seq#=528019 Link-Recid=528018
Error Description:
----------------------
1)Check the default value of control file record keep time.
SQL> show parameter control_file_record_keep_time
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
2)Check the MAXLOGHISTORY database parameter.
SQL>alter database backup controlfile to trace as '/oradata/1.txt'
Check MAXLOGHISTORY value from /oradata/1.txt
Or, you can check RECORDS_TOTAL value from v$controlfile_record_section
SQL> select * from v$controlfile_record_section where type='LOG HISTORY' ;
Now the problem is occurred due to control_file_record_keep_time and MAXLOGHISTORY settings.
The MAXLOGHISTORY increases dynamically when the CONTROL_FILE_RECORD_KEEP_TIME is set to a value different from 0, but does not exceed MAXLOGHISTORY (65535).Once reached, the message appears in the alert log.
From above error it is seen the MAXLOGHISTORY parameter has already reached the maximum of 65535 and it cannot be increased anymore.
Solution of The problem:
---------------------------
1)Set the CONTROL_FILE_RECOED_KEEP_TIME to zero.
alter system set control_file_record_keep_time=0;
or.
2)Change the MAXLOGHISTORY parameter.
The compatibility level is set to a value that is earlier than 10.2.0, and you must create new new control file by CREATE CONTROLFILE command. If compatibility is 10.2.0 or later, you do not have to create new control files when you make such a change; the control files automatically expand, if necessary, to accommodate the new configuration information.
Caution:
-----------
Setting control_file_record_keep_time to zero is dangerous for making RMAN backups. In that case use recovery catalog as repository for RMAN backup instead of control file.
Related Link:
-------------
Create Controlfile
Controlfile Information
Wednesday, April 16, 2008
Controlfile in Oracle Database.
Every database has a control file which is a binary file that records the physical structure of the database. Control file is called the heart of the database.
The control file includes:
• The database name.
• Names and locations of associated datafiles and redo log files.
• The timestamp of the database creation.
• The current log sequence number.
• Checkpoint information.
• Information of the tablespaces.
• Datafile offline ranges.
• The log history.
• Archived log information.
• Database backup information taken through RMAN.
• The current log sequence number.
• Datafile copy information.
How to see the control file Contents:
--------------------------------------
Control file creation sql command/syntax can be seen by,
alter database backup controlfile to trace as '/oradata/1.txt'
After issuing the command if you open /oradata/1.txt you will see the sql script of creation controlfile.
If you don't use trace suppose you use,
alter database backup controlfile as '/oradata/1.txt' then you will get a duplicate binary copy of the current control file.
The control file includes:
• The database name.
• Names and locations of associated datafiles and redo log files.
• The timestamp of the database creation.
• The current log sequence number.
• Checkpoint information.
• Information of the tablespaces.
• Datafile offline ranges.
• The log history.
• Archived log information.
• Database backup information taken through RMAN.
• The current log sequence number.
• Datafile copy information.
How to see the control file Contents:
--------------------------------------
Control file creation sql command/syntax can be seen by,
alter database backup controlfile to trace as '/oradata/1.txt'
After issuing the command if you open /oradata/1.txt you will see the sql script of creation controlfile.
If you don't use trace suppose you use,
alter database backup controlfile as '/oradata/1.txt' then you will get a duplicate binary copy of the current control file.
Subscribe to:
Posts (Atom)