Notes for Oracle control file multiplexing

Notes for Oracle control file multiplexing
Because the control file is very important to the database, the control file is usually reused after the dbca database is created. For example, in Oracle10g, three control files are generated by default and the content is exactly the same. Normally, the files are located under $ ORACLE_BASE/oradata/SID/and are named control01.ctl, control02.ctl, and control03.ctl, by default, there are only two copies at 11 GB. One is under $ ORACLE_BASE/oradata/SID/and the other is under $ ORACLE_BASE/fast_recovery_area/SID, the name is control02.ctl. Of course, I am talking about the default installation. You can customize different paths in pfile. Sometimes, there may be only one control file in some databases, and there is no image, that is, there is no multiplexing of control files. Obviously this is not good. You need to add an image file for the control file for the database, let's perform a test.

---------------------------- Lili split line ----------------------------

Multiplexing of Oracle Control Files

Migration from 32-bit to 64-bit for a single Oracle instance

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Oracle Import and Export expdp IMPDP details

Solution to Oracle 10g expdp export error ORA-4031

---------------------------- Lili split line ---------------------------- -- start the database and view the current control file information SQL> startupORACLE instance started. total System Global Area 835104768 bytesFixed Size 2232960 bytesVariable Size 507514240 bytesDatabase Buffers 322961408 bytesRedo Buffers 2396160 bytesDatabase mounted. database opened. SQL> show parameter control_file NAME TYPE VALUE ------------------------------------------------ When successfully integer 7control_files string/u01/app/oracle/oradata/zlm11g/control01.ctl,/u01/app/oracl e/fast_recovery_area/zlm11g/co ntrol02.ctl is installed by default, there are already two control files, of which control02.ctl is an image -- check whether the control_file parameter can be modified online SQL> col name for a20SQL> select name, issys_modifiable from v $ parameter where name = 'control _ files'; NAME ISSYS_MOD --------------------------- -- Control_files FALSE: Obviously, FALSE indicates that the parameter cannot be modified online, so the Multiplexing Control File means that the database is restarted-create pfileSQL> create pfile from spfile; File created. -- close the database and modify pfile. Add the control file image (add the Image Location and file name after the control_files parameter) SQL> shutdown immediateDatabase closed. database dismounted. ORACLE instance shut down. SQL>! Zlm11g. _ db_cache_size = 322961408zlm11g. _ java_pool_size = 4192134zlm11g. _ large_pool_size = 4192134zlm11g. _ oracle_base = '/u01/app/oracle' # ORACLE_BASE set from environmentzlm11g. _ pga_aggregate_target = 335544320zlm11g. _ sga_target = 503316480zlm11g. _ shared_io_pool_size = 0zlm11g. _ shared_pool_size = 163577856zlm11g. _ streams_pool_size = 0 *. audit_file_dest = '/u01/app/oracle/admin/zlm11g/adump '*. audit_trail = 'db '*. Compatible = '11. 2.0.0.0 '*. control_files = '/u01/app/oracle/oradata/zlm11g/control01.ctl', '/u01/app/oracle/fast_recovery_area/zlm11g/control02.ctl ', '/u01/control03.ctl' -- the red part is the newly added control file image *. db_block_size = 8192zlm11g. db_create_file_dest = '/u01/app/oracle/oradata/zlm11g /'*. db_domain = ''*. db_name = 'zlm11g '*. db_recovery_file_dest = '/u01/app/oracle/fast_recovery_area '*. db_recovery_file_dest_size = 4322230272 *. diagno Stic_dest = '/u01/app/oracle '*. dispatchers = '(PROTOCOL = TCP) (SERVICE = zlm11gXDB )'*. log_archive_format = '% t _ % s _ % r. dbf '*. memory_target = 838860800 *. memory_target = 838860800 *. open_cursors = 300 "/u01/app/oracle/product/11.2.0/db_1/dbs/initzlm11g. ora "27L, 1048C written -- copy a control file image at the OS level [oracle @ zlm ~] $ Cp $ ORACLE_BASE/oradata/zlm11g/control01.ctl/u01/control03.ctl
[Oracle @ zlm ~] $ Cd/u01 [oracle @ zlm u01] $ lsapp bak control03.ctl dave. trc expdp rman. log rman. trc [oracle @ zlm u01] $ exitexit -- start Database SQL with pfile> startup pfile = $ ORACLE_HOME/dbs/initzlm11g. oraORACLE instance started. total System Global Area 835104768 bytesFixed Size 2232960 bytesVariable Size 507514240 bytesDatabase Buffers 322961408 bytesRedo Buffers 2396160 bytesDatabase mounted. database opened. SQL> show parameter Control_files name type value extract ----------- extract control_files string/u01/app/oracle/oradata/zlm11g/control01.ctl,/u01/app/oracl e/fast_recovery_area/zlm11g/co ntrol02.ctl, /u01/control03.ct the new image control file has taken effect. Now there are three control files, one original file and two multiplexing methods -- create a new spfile lSQL> create spfile from pfile; file created. -- shut down the database and restart SQL with spfile> shutdown immediateDatabase cl Osed. database dismounted. ORACLE instance shut down. SQL> startupORACLE instance started. total System Global Area 835104768 bytesFixed Size 2232960 bytesVariable Size 507514240 bytesDatabase Buffers 322961408 bytesRedo Buffers 2396160 bytesDatabase mounted. database opened. SQL> show parameter control_files NAME TYPE VALUE ----------------------------------------------------------------------------- Control_files string/u01/app/oracle/oradata/zlm11g/control01.ctl,/u01/app/oracl e/fast_recovery_area/zlm11g/co ntrol02.ctl,/u01/control03.ct so far, the control file has been reused, from two to three. Note that the database must be stopped. Next we will try to add a control file image in another way, and use the spfile dynamic modification + backup control file method to see if it is feasible-Modify the spfile, add 4th control files SQL> alter system set control_files = '/u01/app/oracle/oradata/zlm11g/control01.ctl,/u01/oracle/fast_recovery_area/zlm11g/control02.ctl, /u01/control03.ctl,/u01/control04.ctl 'scope = spfile; System altered. -- backup the current control file and generate the image file SQL> alter database backup controlfile to '/u01/control04.ctl'; Database altered. SQL> alter database backup controlfile to trace as '/u01/control. bak '; Database altered. note the differences between the two commands. The preceding statements generate binary control files, while the subsequent statements generate readable trace files (usually used to recreate control files)

 

For more details, please continue to read the highlights on the next page:

1 2 3 Next Page