Forgot your password?    
+ Reply to Thread
Results 1 to 5 of 5

Thread: How to Multiplex Controlfiles for an Oracle Database

  1. #1
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427

    How to Multiplex Controlfiles for an Oracle Database

    Add the new controlfile
    (/u01/accounts/b13hema/myora/oradata/flashrecovery/devdb/control03.ctl) to the control_files parameter with scope as spfile.

    If you are using an init/pfile , the change needs to be made manually in the pfile.


    SQL> alter system set control_files='/u01/accounts/b13hema/myora/oradata/flashrecovery/devdb/control03.ctl','/u01/accounts/b13hema/myora/oradata/devdb/control01.ctl','/u01/accounts/b13hema/myora/flashrecovery/devdb/control02.ctl' scope=spfile;



    System altered.

    SQL> shut immediate;

    cp /u01/accounts/b13hema/myora/oradata/devdb/control01.ctl /u01/accounts/b13hema/myora/oradata/flashrecovery/devdb/control03.ctl

    SQL> startup;
    ORACLE instance started.

    Total System Global Area 263049216 bytes
    Fixed Size 2212448 bytes
    Variable Size 205524384 bytes
    Database Buffers 50331648 bytes
    Redo Buffers 4980736 bytes
    Database mounted.
    Database opened.
    SQL>

  2. #2
    About Control File and its Multiplexing

    The control file is relatively small binary files that contains the datafiles and redo log files locations. The control file is created when the database is created and is updated with the physical changes, for example, whenever a datafile is added or renamed. The control file is updated continuously and should be available at all times. Don’t edit the contents of the control file as it is binary; only Oracle processes should update its contents. When the database is started, Oracle uses the control file at mount mode to identify the datafiles and redo log files and opens them. Control files play a major role when recovering a database. If one control file is lost, the database can be restarted after copying one of the other control files or after changing the CONTROL_FILES parameter in the initialization file. When multiplexing control files, Oracle updates all the control files at the same time, but uses only the first control file listed in the CONTROL_FILES parameter for reading. When creating a database, control file names can be listed in the CONTROL_FILES parameter, and Oracle creates as many control files as are listed. A database can have maximum of eight multiplexed control file copies.

    Contents of the control file include:

    The database name to which the control file belongs. A control file can belong to only one database.
    The database creation time stamp.
    The name, location, and online/offline status information of the data files.
    The name and location of the redo log files.
    Redo log archive information.
    Tablespace names.
    The current log sequence number, which is a unique identifier that is incremented and recorded when an online redo log file is switched.
    The most recent checkpoint information.
    The beginning and ending of undo segments.
    Recovery Manager’s backup information.

    Multiplexing can be done when the database is usimg Pfile or SPfile

    Adding additional control file in PFILE:

    SQL> SHUTDOWN NORMAL

    Copy the control file to more locations by using an operating system command:
    $ cp /u02/oradata/pathdb/control01.ctl /u05/oradata/pathdb/ control04.ctl

    Change the initialization parameter file to include the new control file name(s) in the parameter CONTROL_FILES changing this:

    CONTROL_FILES='/u02/oradata/pathdb/control01.ctl', '/u03/oradata/pathdb/control02.ctl', '/u04/oradata/pathdb/control03.ctl',
    '/u05/oradata/pathdb/control04.ctl'

    SQL> STARTUP

    Multiplexing Control Files Using an SPFILE

    Multiplexing using a binary SPFILE is similar to multiplexing using init.ora. The major difference is in how the CONTROL_FILES parameter is changed.

    Alter the SPFILE while the database is still open:
    SQL> ALTER SYSTEM SET CONTROL_FILES = ‘/ora01/oradata/PATHDB/ctrlPATHDB01.ctl’, ‘/ora02/oradata/PATHDB/ctrlPATHDB02.ctl’,
    ‘/ora03/oradata/PATHDB/ctrlPATHDB03.ctl’, ‘/ora04/oradata/PATHDB/ctrlPATHDB04.ctl’ SCOPE=SPFILE;

    This parameter change takes effect only after the next instance restart by using the SCOPE=SPFILE qualifier. The contents of the binary SPFILE are changed immediately, but the old specification of CONTROL_FILES is used until the instance is restarted.

    SQL> SHUTDOWN NORMAL

    Copy an existing control file to the new location:
    $ cp /ora01/oradata/PATHDB/ctrlPATHDB01.ctl /ora04/oradata/PATHDB/ctrlPATHDB04.ctl

    SQL> STARTUP

  3. #3
    Oracle Administrator
    Join Date
    Apr 2012
    Posts
    53
    how to know the control file size?..datafile and redolog file sizes?

  4. #4
    Oracle Administrator
    Join Date
    Apr 2012
    Posts
    53
    how to know that much of sorting is happening in the database.?and which user is performing much of sorting?sorting first uses pga?and later temporarory tablespace?give me steps

    tnx

  5. #5
    Oracle Administrator
    Join Date
    Apr 2012
    Posts
    53
    What is local=no?its a connection from remote pc?then even if we connect from directly into the server also we are getting local=no?then whats exactly local=no process and how to know the user id and name ?

+ Reply to Thread

Similar Threads

  1. About Controlfiles
    By shoaibansari in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 02-14-2012, 07:59 PM
  2. Loss of all controlfiles of the Database (User Managed Recovery)
    By ajaysingh in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 11-01-2011, 11:41 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics