Here a some explanation of several years of punctual intervention on Oracle Databases. The meaning of this page is to regroup all informations needed to check health of an Oracle system.

PFILE / SPFILE

What is it ?

This both files are files that store initialization parameters of the database. Oracle process reads this file during the startup. In them we can find informations related to memory allocation (SGA and PGA), NLS language parameters, backgroup processes, ...

Oracle recommand to use spfile, as this one prevent typo error because it can only be altered with "ALTER SYSTEM" commands. And also because RMAN can take care of the backup of this file.

General

Default location is under $ORACLE_HOME/dbs

pfile: (Parameter File): client-side text file, can be edit with an editor (vim, emacs, ...). Usually init.ora file

spfile (Server Parameter File): server-side binary file, can be only edit with "alter system set ...". No longer needs a pfile to start a database. As this is a binary file, do not edit it because you cannot start your database anymore

Currently using pfile or spfile ?

If you want to know if you are currently using a pfile or a spfile, type the following command:

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v\_$parameter WHERE name = 'spfile';


Oracle startup process regarding spfile/pfile

On the startup of the database, if nothing has been specified, Oracle will look into the $ORACLE_HOME/dbs folder, and proceed like this: * look for spfile${ORACLE_SID}.ora * look for spfile.ora * look for init${ORACLE_SID}.ora <- our case initSTORE.ora * (All of this can be override by using:
-> SQL> STARTUP PFILE='/YOUR_PATH/spfileYOUWANT.ora';

If I don't have spfile, I need to create one to have the ability to use "alter system set ...". Then, convert pfile from/to spfile with one of the following

SQL> create pfile from spfile;
SQL> create spfile from pfile;

Control files

What is it ?

Binary file that save physical structure of the db: * db name * name & location of associated datafiles and online redo logs * general timestamp * log sequence number in progress * checkpoint informations

Check current configuration

Recommanded usage is to have several control files on several physical disks. Check current configuration with

SQL> show parameter control_files;
#or
SQL> select * from v$controlfile_record_section;

Control file backup

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;    (put file in udump in text format)
ALTER DATABASE BACKUP CONTROLFILE TO '/tmp/control.bkp';  (binary version)
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/create_control.sql' (generate a sql file, to be able to manually create it later)

Redo Logs

What is it ?

Redo logs contain history of all transaction & changes made to the running database. When user commit an update, Oracle will generate another record and will assign a "System Change Number" to it. With this method, Database can in case of crash redo all transactions made to the specified number the DBA will point to.

Informations about redo logs

status, sequence number, members, location

SQL> select * from v$log;
SQL> select * from v$logfile;

Show redo log max size

SQL> set linesize 300
column REDOLOG_FILE_NAME format a50
SELECT
    a.GROUP#,
    a.THREAD#,
    a.SEQUENCE#,
    a.ARCHIVED,
    a.STATUS,
    b.MEMBER    AS REDOLOG_FILE_NAME,
    (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group# 
ORDER BY a.GROUP# ASC;



Add a redo log

SQL> alter database add logfile group 4 '/opt/oracle/app/oradata/orcl/redo04.log' size 50M;
SQL> alter database add logfile group 5 '/opt/oracle/app/oradata/orcl/redo05.log' size 50M;

Flash Recovery Area (FRA)

What is it ?

Became "Fast Recovery Area" in Oracle 11g.
It is the unified storage location for all recovery files and activities of the Database.

Content


Since Oracle 10g, this is a storage zone that contains: * redo logs * control files * archived logs * backup pieces * copies * flashback logs * foreign archived logs 11g



Check used space

SQL> select * from v$recovery_area_usage;



Increase FRA size

SQL> alter system set db_recovery_file_dest_size=120g scope=both;

Archivelogs

What is it ?

Archivelog are the files that store for a long time redo log. It is highly recommanded to have them enabled.



Archivelog enabled ?

SQL> select NAME,LOG_MODE from v$database;
#or
SQL> archive log list;



Pass DB in archive log mode

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> select NAME,LOG_MODE from v$database;



Archivelog location

SQL> show parameter recovery_file_dest;



Deletion

# Delete archivelogs (here date-3)
RMAN>DELETE ARCHIVELOG ALL COMPLETED BEFORE 'sysdate-3';

# Delete archivelog backups (here date-7)
RMAN>DELETE BACKUP OF archivelog UNTIL TIME=’sysdate-7′;

Tablespace check usage

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ; 

Tables

List table for specific user (scott)

SQL> SELECT owner, table_name FROM dba_tables WHERE OWNER='SCOTT';



Get informations from a table

SQL> describe <TABLE_NAME>;

Language

# NLS Modification (language & charset)
ALTER SYSTEM SET NLS_TERRITORY ='FRANCE' SCOPE=SPFILE;
ALTER SYSTEM SET NLS_LANGUAGE='FRENCH' SCOPE=SPFILE;
ALTER SYSTEM SET NLS_ISO_CURRENCY='FRANCE' SCOPE=SPFILE;
ALTER SYSTEM SET NLS_CURRENCY='#' SCOPE=SPFILE;

# Oracle charset
SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;
WE8ISO8859P15

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;
  PARAMETER            VALUE
  ------------------------------ ----------------------------------------
  NLS_LANGUAGE             AMERICAN
  NLS_TERRITORY            AMERICA
  NLS_CURRENCY             $
  NLS_ISO_CURRENCY         AMERICA
  NLS_NUMERIC_CHARACTERS           .,
  NLS_CHARACTERSET         WE8ISO8859P15
  NLS_CALENDAR             GREGORIAN
  NLS_DATE_FORMAT          DD-MON-RR
  NLS_DATE_LANGUAGE        AMERICAN
  NLS_SORT             BINARY
  NLS_TIME_FORMAT          HH.MI.SSXFF AM

  PARAMETER            VALUE
  ------------------------------ ----------------------------------------
  NLS_TIMESTAMP_FORMAT         DD-MON-RR HH.MI.SSXFF AM
  NLS_TIME_TZ_FORMAT           HH.MI.SSXFF AM TZR
  NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
  NLS_DUAL_CURRENCY        $
  NLS_COMP             BINARY
  NLS_LENGTH_SEMANTICS         BYTE
  NLS_NCHAR_CONV_EXCP          FALSE
  NLS_NCHAR_CHARACTERSET           AL16UTF16
  NLS_RDBMS_VERSION        11.2.0.1.0

Blocks

Get the block size of data

select value from v$parameter where name = 'db_block_size';

Session & processes

Check value


Check actual value of sessions & processes

SQL> select name, value from v$parameter where name like 'sessions' or name like 'processes' or name like 'open_cursors';

Check the number of max sessions the database allows

SQL> SELECT name, value FROM v$parameter WHERE name = 'sessions';
  --------------------------------------------------------------------------------
  VALUE
  --------------------------------------------------------------------------------
  sessions
  776



Change value (scope)


Once running with a spfile, modify the value:

SQL> alter system set sessions=1500 scope=spfile;

scope can have 3 values: * memory: for the current instance only * spfile: update the spfile binary file, this will be applied on the next database startup * both: ...


Oracle options

Listing des options en cours d’utilisation sur la base existante (Enterprise 11.2.0.1)

SQL> select * from dba_feature_usage_statistics where CURRENTLY_USED='TRUE';

Backup

Configuration

rman target /
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name STORE are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backup/rman/full_%u_%s_%p';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_STORE.f'; # default

# Change directory where backup will be stored (directory must be manually create)
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/rman/full_%u_%s_%p';

# retention (rman will automatically delete old backup older than the specified period)
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;

# set a specific parameter to default value (here retention)
CONFIGURE RETENTION POLICY CLEAR;

# Take a backup of Database + archivelogs with compression
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

# Take a backup of a specific tablespace
BACKUP AS BACKUPSET TABLESPACE STORE_DATA;


# Add tag to a backup (add date variable)
BACKUP AS COMPRESSED BACKUPSET TAG 'prodim_full_03mar2017_12h06' DATABASE PLUS ARCHIVELOG;

# List backups
list backup summary;