Control file management and common parameter settings in Oracle Database, oracle File Management

Control file management and common parameter settings in Oracle Database, oracle File Management

I. Control Files
Purpose:

• Binary files
• Records the structure and behavior of the current database instance, data file and log file information, and maintains database consistency.
• The parameter file defines the location and size of the control file.
• A very small binary file, generally no more than 100 MB
• After the mount stage is open
• A set of control files can only connect to one database
• Distributed Storage, at least one copy, up to eight copies

Related views:

• V $ controlfile ---- lists the names and statuses of all control files of an instance.
• V $ parameter ---- list the locations and statuses of all parameters (where name = 'control _ files ')
• V $ controlfile_recode_section ---- provides information about the record part of the control file
• Show parameter control_files ---- list the name, status, and location of the Control File

View control file content:

    strings $ORACLE_BASE/oradata/$ORACLE_SID/control01.ctl    alter database backup controlfile to trace as '<filepath&name>'

Dump control file:

    alter system set events 'immediate trace name controlf level 10'

• Level 1 Header
• Level 2 File Content
• Level 3 1 + 2
• Level 10 all

Manage control files:

Spfile: Modify the spfile parameter control_files (alter ......)

• Disable Database Consistency
• Add or remove control files
• Start the database and use spfile
• Verification Result

Pfile consistency shut down Database

• Modify pfile Parameters
• Add or remove control files
• Start the database and use spfile
• Verification Result

Status query: whether the parameters correspond to the specific file, the disk space is left, and the control file is <100 M

Hot Standby control file:

In database archive mode,

alter database backup controlfile to '<fpath&name>'

In other modes:

alter database backup controlfile to trace as '<fpath&name>'

Get the control file creation script

Rman backup in database archive mode:

        rman target /        >backup current controlfile;    or        >backup database include current controlfile;

Rman automatic backup control file:

> In show all, set configure controlfile autobackup to on.

        >configure controlfile autobackup on

Solution:

Inconsistent control files:
(1) copy the control file of the new version to the old version.
2. Modify the parameter file. Only the new version of the file (alter system set…) is used ......), Restart

Control File loss:
[In the mount phase, you can use archive log list to check whether the database is in archive mode]

1. The control file is not backed up
A. The database is not archived, and the online log file of the data file exists-rebuilding the control file

  >create controlfile reuse database <sid> noarchivelog noresetlogs datafile <'datafiles'> logfile <group X 'logfiles' size Y>

2. The control file is backed up.
A. Database archiving, online data files, and archived log files --- restore control files
First, copy the control file to the path specified by the parameter, and then

>alter database recover database using backup controlfile until change;

Ii. parameter settings

>show parameter <keyword>

Query Parameters

Purpose: record the database configuration. Memory Allocation, process count, session count, etc.

Category:
Pfile type-Text File
$ ORACLE_HOME/dbs/init $ ORACLE_SID.ora (9i, 10g is not used by default)
$ ORACLE_BASE/admin/$ ORACLE_SID/pfile/init. ora. xxxx (used once during 10 Gb initialization)

Spfile type-binary file
$ ORACLE_HOME/dbs/spfile $ ORACLE_SID.ora, server (9i)
It can be backed up by rman (rman can only back up binary files)

When oracle is started, the search order is spfile <sid>. ora ----- spfile. ora ----- init <sid>. ora.

Conversion:
Create pfile [= 'fpath'] from spfile; default value: $ ORACLE_HOME/dbs/init $ ORACLE_SID.ora

Create spfile [= 'fpath'] from pfile; default value: $ ORACLE_HOME/dbs/spfile $ ORACLE_SID.ora

Create spfile [= 'fpath'] from memory; rebuild spfile from memory

10g specify to start with pfile:

>startup pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora';>create spfile from pfile;

In this way, the modified pfile can take effect.

View the currently used parameter file:

>show parameter spfile; 

If the value is null, pfile is used; otherwise, spfile displayed by value is used.

>select distinct ISSPECIFIED from v$spparameter;

The value true indicates spfile, and false indicates pfile.

Modify the spfile file:

Alter system | session set parameter_name = values scope = memory | spfile | both sid = '<sid>' | '*';

The isses_modifiable column in system ---- v $ parameter is a non-flase item and can be modified by alter session.
The issys_modifiable column in session ---- v $ parameter is a non-flase item and can be modified by alter system.
[Select name, isses_modifiable, issys_modifiable from v $ parameter ;]
Three statuses are available:
Immdiate ---- dynamic parameters. The system is directly modified to the memory and parameter files and takes effect immediately.
False ---- static parameter, which cannot be changed directly. system adds scope = spfile to the parameter file.
Deferred ---- session parameter, which can be modified using session, only valid for the current instance

Memory ---- valid only for the currently started instance and invalid for the next start
Spfile ---- modify the spfile only. It does not affect the current instance and will take effect at the next start.
Both ---- both the memory and spfile are modified. The default value is

<Sid> ---- current instance
* ---- All instances, used to have multiple instances in the Cluster Environment

10g common parameters:

Orcl. _ db_cache_size = 889192448 orcl. _ java_pool_size = 16777216orcl. _ large_pool_size = 16777216orcl. _ shared_pool_size = 285212672orcl. _ streams_pool_size = 0 *. audit_file_dest = '/u01/oracle/admin/orcl/adump' ---> warning log *. background_dump_dest = '/u01/oracle/admin/orcl/bdump' ---> background process log *. compatible = '10. 2.0.1.0 '---> Version backward compatible Number *. control_files = '/u01/oracle/oradata/orcl/control01.ctl', '/u01/oracle/oradata/orcl/control02.ctl ', '/u01/oracle/oradata/orcl/control03.ctl' ---> Control File *. core_dump_dest = '/u01/oracle/admin/orcl/cdump' ---> server process log *. db_block_size = 8192 ---> oracle memory block size *. db_domain = ''---> domain name *. db_file_multiblock_read_count = 16 ---> how many pieces of word IO can be read *. db_name = 'orcl '---> and db_domain form globename *. db_recovery_file_dest = '/u01/oracle/flash_recovery_area' ---> backup log, OMF management file storage path *. db_recovery_file_dest_size = 2147483648 ---> flash_recovery_area directory size *. dispatchers = '(PROTOCOL = TCP) (SERVICE = orclXDB)' ---> Shared Server parameter *. job_queue_processes = 10 ---> current job process count *. local_listener = 'listener _ ORCL '---> local listening service *. open_cursors = 300 ---> Number of opened cursors *. pga_aggregate_target = 407896064 ---> pga size *. processes = 150 ---> How many processes can be run currently *. remote_login_passwordfile = 'clusive '---> whether a password file is required for remote logon, none | shared | EXCLUSIVE *. sga_target = 1223688192 ---> sga size (10 Gb )*. undo_management = 'auto' ---> undo tablespace Management Method *. undo_tablespace = 'undotbs1 '---> ndo tablespace *. user_dump_dest = '/u01/oracle/admin/orcl/udump' ---> User process log

Hide parameters:

select ksppinm,ksppstvl from x$ksppi a,x$ksppcv b where a.indx=b.indx

Show Hidden PARAMETERS IN THE SYSTEM

Spfile reset parameter default value:

Alter system reset parameter scope = spfile sid = '<sid>' | '*';

Articles you may be interested in:

• Oracle 11g replicuard Parameters
• Detailed introduction to oracle stored procedures (creation and deletion of stored procedures, parameter transfer, etc)
• How to Use the oracle impdp network_link Parameter
• Oracle STARTUP routine STARTUP parameter description