Hello experts, I'm Avinash and I'm learning Oracle Database Creation using OMF feature. I need your expert help in understanding in what manner one ought to specify the control_files parameter in the pfile.
OS = RHEL 5
Oracle Database 11gr2
Following is my pfile initcatdb.sql which is in $ORACLE_HOME/dbs/.
audit_file_dest='/u01/app/oracle/admin/catdb/adump'
audit_trail='db'
compatible='11.2.0.0.0'
db_block_size=8192
db_create_file_dest='/u02/oradata'
db_create_online_log_dest_1='/u03/logfile_1'
db_create_online_log_dest_2='/u04/logfile_2'
db_domain='linux.in'
db_name='catdb'
db_recovery_file_dest='/u05/fast_recovery_area'
db_recovery_file_dest_size=4194304000
diagnostic_dest='/u01/app/oracle'
log_archive_dest_1='location=/u05/archive_logs_copy/CATDB/'
log_archive_dest_10='location=USE_DB_RECOVERY_FILE_DEST'
log_archive_format='%t_%s_%r.dbf'
memory_target=405m
undo_tablespace='undotbs1'
and the database creation script /u01/app/oracle/admin/catdb/scripts/catdb.sql is as follows
create database catdb
extent management local
undo tablespace undotbs1
default temporary tablespace def_temptbs
default tablespace users;
In the pfile I have not used the control_files parameter because if I don't mention it a control file is by default created in $ORACLE_HOME/dbs/ location and Oracle chooses a name for that control file (from Oracle Docs).
Also as I have mentioned
db_create_online_log_dest_1='/u03/logfile_1'
db_create_online_log_dest_2='/u04/logfile_2', two OMF control files will be created in these directories.
When I run the command @/u01/app/oracle/admin/catdb/scripts/catdb.sql; the database is created.
But when I shut it and try to open it, Oracle generates an error which is as follows:
ORA-00205: error in identifying control file, check alert log for more info
I checked alert log..
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/cntrlcatdb.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
I checked the alert log and got a clue that the control file was not created in the $ORACLE_HOME/dbs/ location but the SHOW PARAMETER CONTROL_FILES gives following output:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/cntrltestdb.dbf
and the control file was not there.
I got the solution for the problem. I checked the locations db_create_online_log_dest_1, and db_create_online_log_dest_2, one control file copy was created in each of the locations. I made a note of the control file names in those locations and updated the pfile with the values in those locations for control_files parameter and Oracle did not complaint further.
But what is confusing me is that, why Oracle does not use the control files created in these db_create_online_log_dest_1 and db_create_online_log_dest_2 locations? Why the control file was not created in the dbs location?
My guess: Because these are multiplexed copies.
The control file was not created in the $ORACLE_HOME/dbs because I used db_create_online_log_dest_1 and db_create_online_log_dest_2 parameters
Also is it must to use the control_files parameter in the pfile?
Oracle Docs mentions that CONTROL_FILES parameter is not mandatory but recommended.
Note from Oracle Docs:
If you are using Oracle Managed Files and your initialization parameter file does not contain the CONTROL_FILES parameter, then you must create a server parameter file now so the database can save the names and location of the control files that it creates during the CREATEDATABASE statement.
Should I create spfile immediately after issuing the STARTUP NOMOUNT PFILE='/pfile location/'; command or when I should I create it to avoid the error above mentioned. I will try this and post the result.
Also please forgive me if it's a stupid question. It is little confusing for me.
Thanking you.