Oracle Database - Controlfiles
CONTROL FILE Step 1 : To view list of control files SQL> select name from v$controlfile; NAME ------------------------------------------------------- /u01/app/oracle/oradata/TESDB/control01.ctl /u01/app/oracle/fast_recovery_area/TESDB/control02.ctlStep 2 : To view list of control files SQL> show parameter control NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /u01/app/oracle/oradata/TESDB/ control01.ctl, /u01/app/oracle /fast_recovery_area/TESDB/cont rol02.ctl control_management_pack_access string DIAGNOSTIC+TUNINGStep 3 : Adding new control file Alter system set control_files= '/home/oracle/dev/oradata/control/control01.ctl', '/home/oracle/dev/oradata/control/control02.ctl', '/home/oracle/dev/oradata/control/control03.ctl' scope=spfileStep 4 : To view list of control files, will not show the newly created control file SQL> show parameter control NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /u01/app/oracle/oradata/TESDB/ control01.ctl, /u01/app/oracle /fast_recovery_area/TESDB/cont rol02.ctl control_management_pack_access string DIAGNOSTIC+TUNINGStep 5 : Close the database SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down.Step 6 : In the OS level of control file, create new one by copying from first control file [oracle@tesdb TESDB]$ cp control01.ctl control03.ctlStep 7 : Start the database SQL> startup ORACLE instance started. Total System Global Area 1157627168 bytes Fixed Size 8895776 bytes Variable Size 301989888 bytes Database Buffers 838860800 bytes Redo Buffers 7880704 bytes Database mounted. Database opened.Step 8 : To view list of control files SQL> show parameter control NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /u01/app/oracle/oradata/TESDB/ control01.ctl, /u01/app/oracle /fast_recovery_area/TESDB/cont rol02.ctl, /u01/app/oracle/ora data/TESDB/control03.ctl control_management_pack_access string DIAGNOSTIC+TUNING MULTIPLEXING USING PFILEStep 1 : Create pfile file from spfile for current update regarding databaseSQL> create pfile from spfile;Step 2 : Close database SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down.Step 3 : Open the pfile in locatin and add the new control file information [oracle@tesdb dbs]$ vi inittesdb.ora Add another control file /u01/app/oracle/oradata/TESDB/control04.ctl control_files='/u01/app/oracle/oradata/TESDB/control01.ctl', '/u01/app/oracle/fast_recovery_area/TESDB/control02.ctl', '/u01/app/oracle/oradata/TESDB/control03.ctl', '/u01/app/oracle/oradata/TESDB/control04.ctl'Step 4 : create new control file [oracle@12cserver control]$ cp control03.ctl control04.ctlStep 5 : Start databse using pfile SQL> startup pfile='$ORACLE_HOME/dbs/inittesdb.ora' ORACLE instance started. Total System Global Area 1157627168 bytes Fixed Size 8895776 bytes Variable Size 301989888 bytes Database Buffers 838860800 bytes Redo Buffers 7880704 bytes Database mounted. Database opened.Step 6 : To view list of control files SQL> show parameter control NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /u01/app/oracle/oradata/TESDB/ control01.ctl, /u01/app/oracle /fast_recovery_area/TESDB/cont rol02.ctl, /u01/app/oracle/ora data/TESDB/control03.ctl, /u01 /app/oracle/oradata/TESDB/cont rol04.ctl control_management_pack_access string DIAGNOSTIC+TUNINGStep 7 : Create spfile file from pfile for current update regarding database SQL> create spfile from pfile; File created.Step 8 : Start the database SQL> startup force; ORACLE instance started. Total System Global Area 734003200 bytes Fixed Size 8625032 bytes Variable Size 327156856 bytes Database Buffers 394264576 bytes Redo Buffers 3956736 bytes Database mounted. Database opened.Step 9 : Change the CONTROL_FILE_RECORD_KEEP_TIME parameter (7 days (default)) SQL> show parameter control NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /u01/app/oracle/oradata/TESDB/ control01.ctl, /u01/app/oracle /fast_recovery_area/TESDB/cont rol02.ctl, /u01/app/oracle/ora data/TESDB/control03.ctl, /u01 /app/oracle/oradata/TESDB/cont rol04.ctl control_management_pack_access string DIAGNOSTIC+TUNING SQL> alter system set control_file_record_keep_time=10; System altered.Step 10 : To view list of control files SQL> show parameter control NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 10 control_files string /u01/app/oracle/oradata/TESDB/ control01.ctl, /u01/app/oracle /fast_recovery_area/TESDB/cont rol02.ctl, /u01/app/oracle/ora data/TESDB/control03.ctl, /u01 /app/oracle/oradata/TESDB/cont rol04.ctl control_management_pack_access string DIAGNOSTIC+TUNINGSTEP 11 : BACKUP control file and this file will be binary format SQL> alter database backup controlfile to trace; Database altered. ls[oracle@12cserver trace]$ pwd /u01/app/oracle/diag/rdbms/dev/dev/trace [oracle@tesdb trace]$ ll -lrt -rw-r-----. 1 oracle oinstall 33838 Oct 27 09:56 tesdb_ora_6897.trcSTEP 12 : BACKUP control file in sql format for reading purpose SQL> alter database backup controlfile to trace as '/home/oracle/control.bkp'; Database altered Show controlfile vi control.bkp text format CONTROL file views show parameter control v$controlfile v$controlfile_record_section v$backup_controlfile_summary v$session_fix_control v$backup_controlfile_details dba_hist_wr_control « Previous Next Topic » (Oracle Database - OFA Structure) |