Control file management and common parameter settings in Oracle database _oracle

First, the control document
Role:

• binary files
• The structure and behavior of the current instance of the database, the information of the data file log file, and the maintenance of database consistency are recorded.
• The location and size of the control file are defined in the parameter file
• Very small binary files, generally no more than 100m
• After the Mount stage open, it has been used
• A set of control files can only be connected to one database
• Distribute, at least one copy, up to eight copies

Related views:

• V$controlfile----Lists the names and states of all control files for the instance
• V$parameter----Lists the position and status of all parameters (where name= ' control_files ')
• V$controlfile_recode_section----Provide information about the records section of the control file
• Show parameter control_files----lists the name, status, and location of the control file

To view the contents of the control file:

    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.
• Level 2 File Content
• Level 3 1+2
• Level 10 All

Management control Files:

SPFile Modify SPFile parameter Control_files (Alter ...). ）

• Consistency Close Database
• Increase or decrease control files
• Start the database using SPFile
• Validation results

Pfile Consistency Close Database

• modifying pfile parameters
• Increase or decrease control files
• Start the database using SPFile
• Validation results

Status query: Whether the parameters and specific files correspond to the disk space remaining, control file <100m

Hot Standby Control File:

In the database archive mode,

ALTER DATABASE backup Controlfile to ' <fpath&name> '

In other modes:

ALTER DATABASE backup Controlfile to trace as ' <fpath&name> '

Get set up control file script

Rman Backup in database archive mode:

        Rman Target/
        >backup current controlfile;    or
        >backup database include current controlfile;

Rman automatically backs up control files:

Configure Controlfile Autobackup set to ON in >show all

        >configure Controlfile Autobackup on

Problem solving:

Control file Inconsistencies:
(1). Copy new version of control file to old version file
2. Modify the parameter file, using only the new version of the file (alter system set ...). ), restart

Control file Loss:
"Mount phase, use the archive log list to see if the database is in archive mode"

1. control File No Backup
A. Database is not archived, data file online log file exists---rebuild control file

  >create controlfile Reuse Database <sid> noarchivelog noresetlogs datafile < ' datafiles ' > LogFile < Group X ' logfiles ' size y>

2. Control files are backed up
A. Database archiving, data file online and archive log files exist---restore control files
First complete, then copy the control file to the path specified by the parameter, and then

>alter database recover database using Backup controlfile until change;

Second, parameter setting

>show parameter <keyword>

Query parameters

Role: Records the configuration of the database. Memory allocation, number of processes, number of sessions, etc.

Classification:
pfile Type---text file
$ORACLE _home/dbs/init$oracle_sid.ora (9i,10g not used by default)
$ORACLE _base/admin/$ORACLE _sid/pfile/init.ora.xxxx (used once for 10g initialization)

SPFile Type---binary file
$ORACLE _home/dbs/spfile$oracle_sid.ora, server-side (9i started to use)
Can be backed up by Rman (Rman can only back up binary files)

When Oracle starts, the lookup order is spfile<sid>.ora-----Spfile.ora-----Init<sid>.ora

Transformation:
create pfile[= ' Fpath '] from SPFile; Default in $oracle_home/dbs/init$oracle_sid.ora

Create spfile[= ' Fpath '] from pfile; Default in $oracle_home/dbs/spfile$oracle_sid.ora

Create spfile[= ' Fpath '] from memory; Rebuilding SPFile from memory

10g specifies to start with pfile:

>startup pfile= ' $ORACLE _home/dbs/init$oracle_sid.ora ';
>create SPFile from Pfile;

This will take effect on the pfile that you just modified.

To view the currently used parameter file:

If the value is NULL, the description is using Pfile, otherwise the value displayed is used SPFile

>select distinct isspecified from V$spparameter;

A value of True is Spfile,false is Pfile

Modification of SPFile file:

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

The isses_modifiable v$parameter column in system----is not flase can be modified by alter session
The issys_modifiable v$parameter column in session----is not flase can be modified by alter system
"Select name,isses_modifiable,issys_modifiable from V$parameter;"
The state of the query includes three types:
Immdiate----Dynamic parameters, system directly modifies to memory and parameter files, takes effect immediately
False----static parameters, can not be directly changed, System plus scope=spfile modified to the parameter file
Deferred----session parameters, which can be modified by using sessions only valid for the current instance

Memory----is valid only for the currently started instance and is not valid the next time it starts
SPFile----Only modifies spfile, does not affect the current instance, and the next time it is active
Both----memory and SPFile are modified, default values

<sid>----Current Instance
*----All instances, for clustered environments with multiple instances

Common parameters in 10g:

orcl.__db_cache_size=889192448 orcl.__java_pool_size=16777216 orcl.__large_pool_size=16777216 Orcl.__shared_pool_ size=285212672 orcl.__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 backwards compatibility # *. 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 *.db_file_multiblock_read_count=16---> Word io How many blocks can be read *.db_ Name= ' ORCL '---> and db_domain constitute 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 parameters *.job_queue_processes=10---> Current number of job processes *.local_listener= ' Listener_ORCL '---> Local listening service *.open_cursors=300---> Number of open cursors *.pga_aggregate_target=407896064--->PGA size *.processes=150-- -> How many processes can currently be run *.remote_login_passwordfile= ' EXCLUSIVE '---> Remote logins need a password file, none|shared|exclusive *.sga_target= 1223688192--->sga size (10g) *.undo_management= ' AUTO '--->undo table space management *.undo_tablespace= ' UNDOTBS1 '--->

 Ndo table Space *.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

To display hidden parameters in the system

SPFile Reset parameter Default value:

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