Oracle Physical Database files-07


Physical Database files:

Password file

It’s a physical file. “ORAPWD” is a command utility to create the file.

To connect the oracle database as DBA, either by password file or OS group which has SYSDBA access.

It assists the DBA with granting SYSDBA and SYSOPER privileges to other users.

The init parameter REMOTE_LOGIN_PASSWORDFILE specifies if a password file is used to authenticate the Oracle DBA or not. If it set either to SHARED or EXCLUSIVE, password file will be used.

Sql> show parameter password

SQL> select * from v$pwfile_users;

The only time the password file is accessed is if someone logs on ‘as sysdba’.

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=secret

Parameter files

It’s a physical file.

Without parameter files, we cannot start the database.

Whenever, you are planning to change the parameter files. First check which one it’s using it and take a copy of it both physical as well as oracle.

Types  1. Pfile 2. SPfile


Pfile Spfile (9i Onwards)
Text file Binary file
We can view Cannot view
Needs a DB reboot No reboot required
Names: init.ora (or) init<SID>.ora Names: spfile.ora (or) spfile<SID>.ora
Location: $ORACLE_HOME/dbs Location: $ORACLE_HOME/dbs
SPFILE can be backed-up with RMAN
Reduce human errors. The SPFILE is maintained by the server.


Any changes to either files will not reflect, we need to manually change to match this, a reboot required. Spile we can reboot later, but pfile needs an immediate shutdown.

The reason for the SPfile features, to minimize the changes and downtime, to make sure all are correct before we reflect with Pfile.

For some parameter does not need a reboot and we can specify the “SCOPE can be set to MEMORY, SPFILE, or BOTH”. Memory – no reboot required.

Memory – is a temporary one, next reboot it will use the old. This is the default behavior if a PFILE was used at STARTUP.

SPfile –SPFILE, the parameter will take effect with next database startup.

Both – affect the current instance and persist to the SPFILE. This is the default behavior if an SPFILE was used at STARTUP.

How to change the parameter values using SPfile.

Show parameter shared_pool_size;

Alter system set  shared_pool_size = 700m scope = spfile;

How to take this changes into  Pfile

Create pfile from spile; — It will create a file in the default location

You can also, create your desired location.

Create pfile =’/opt/init_test1_DB.ora’from spile; — To non-default location

How to  read the file

$ cat OH/ init_test1_DB.ora

What are the main parameters are available here.

There are many parameters are available in the Pfile. You can get that from following command.

Show parameters;

The main parameters to start the database





Undo_managment = ‘auto’


How to change the parameter values using Pfile

$ vi OH/ init_test1_DB.ora


Create spfile from pfile =’ OH/ init_test1_DB.ora’; — This will sent an error, since it’s in use

From defalt  location –Create spfile from pfile

In case, if it uses a spfile:

create pfile from spfile

edit pfile

shutdown instance

startup with pfile

create spfile from pfile

Shutdown & startup


By defaut oracle will start from SPfile.

How to start from pfile

Startup pfile =’/opt/init.ora’

How to know the  server is using SPfile or Pfile

Show parameter spfile; – if the column is emty it’s using pfile.

SELECT DECODE(value, NULL, ‘PFILE’, ‘SPFILE’) “Init File Type”

FROM sys.v_$parameter WHERE name = ‘spfile’;

Best practice , to have more pfiles.

How find the database parameters

Select * from v$parameters;


Control files

It’s a physical file. Also, a binary file. It defines the current state of the physical database..

It’s a heart of the database

A database needs a minimum 1 control file and maximum 8 control files. The information will be same as all the control files. By default two files will be created.

If the database has more than 1 control file called “multiplexing”

How to  recover the control files

— Recover from multiplexing

(Even if you have multiple control files, any of the files corrupted, the database will be terminated)

— From backup

— You can write the script, but need to know of content i.e. data file location etc…

REUSE in CREATE CONTROLFILE is to specify that existing controlfiles are to be overwritten.
SET in CREATE CONTROLFILE is to specify that the DB_NAME is being changed from whatever is the existing name in datafile headers.
They are two different clauses for two different purposes. It is not an “either/or” decision.

From what I understand, alter database resetlogs is always required when:

  • restoring from a backup controlfile (it does not have information about latest SCN)

  • incomplete recovery (you cannot use current redologs or redo logs are unavailable)

  • DB point in time recovery (relies on archivelogs to roll DB forward).


When opening database with resetlogs, you reset the SCN and start a new database incarnation.

Oracle uses noresetlogs when your database crashed and all files are available – this is automatic recover. You can also use noresetlogs when you restore a database using a backup controlfile from trace and all datafiles including online redologs are valid and the DB was shutdown properly (rare case).

In most cases when you restore and recover a databaes you use resetlogs, because the current onlline redo logfiles cannot be part of a backup.


A “SET DATABASE” clause in the CREATE CONTROLFILE statement is a clause used to rename a database. (Even if you have SET it back to the same name !). This requires that the Redo Logs be initialized. That is why the RESETLOGS is mandatory for a CREATE CONTROLFILE SET DATABASE.

(and it follows that the RESETLOGS will be required when doing the subsequent ALTER DATABASE OPEN).

Note : IF you are NOT planning to rename the database, you should not use the SET DATABASE clause in the CREATE CONTROLFILE statement.

How to  control control file

Maxinstance                      1

Maxdatafiles                     1600

Maxlogfiles                        5 – Redo log

Maxlogmember                3 – Member of redo log

Maxloghistory                   65000 – Archive log

What is the content of the control file

The database name

Names and locations of associated datafiles and redo log files

The timestamp of the database creation

The current log sequence number

Checkpoint information

Archive log mode info

SCN info

RMAN backup info


Control file multiplexing using SPfile

Down time needed, You need to mention all existing control file with the new file.

Select name from v$controlfile;

Alter system set control_files = ‘ All existing files’, ‘new location ’ scope = spfile;

Shutdown immediate

$ cd /opt/controlfile

$ Ls

$cp old file new file


Select name from v$controlfile;

You need to  manually copy this files.

Control file multiplexing using Pfile

Create pfile = ‘pfile Existing location’ from spfile;

$vi /opt/inittest.ora

Add the  control file info manually and save it. (,/opt/03.ctl)

Shutdown immediate

$ cd /opt/controlfile

$cp old file new file


Startup pfile =’/opt/init.ora’

Select name from v$controlfile;


Create spfile from pfile ’/opt/init.ora’;

Shutdown immediate


Best practice, always database needs to run using SPfile.

How to view the content of the control file

Select * from v$controlfile_record_section;


Backup the Control files

ALTER DATABASE BACKUP CONTROLFILE TO ‘/tmp/control.bkp’; — binary backup

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ‘/opt/create_control.sql’ — script backup

Redo log files

It’s an online backup of the database. All the changes are stored and written here.

A database needs minimum 2 redo log files. Maximum 5 redo log groups, we can create.

The reason for the two files, in case one full oracle will use next.

Once, the log 1 is full and its move to the next it called “log switch”.

The log switch can happen from log sequence number. Every redo log file has a low LSN and High LSN.

The first number is low LSN and end of the log file last number is high LSN. The log writer will write those info. When it moves to next redo log file it will take a previous high LSN and start the same number from the current redo logs. Like (1 to 10) (10 to 20)

Redo log status






The current redo log used by the LGWR. Its’s a current redo online log, cannont be droped and renamed/relocated.

In case if it gets corrupted, only incomplete recovery is possible. (With data loss).


One, the  LGWR completed the task and moved to the next log file , it will mark as active.

Redo log files that are required for instance recovery are called active redo log files.


Once, the  redo log file data moved to Archive file, it’s marked as inactive.

Redo log files that are no longer required for instance recovery are called inactive redo log files.


A newly created file and LGWR cannot started.


Redo logs multiplexing


A group can have 3 members. In a single group the members are having same data.

Example: G1 (M1 & M2) G2(M3,M4). It will be used for high availabilities.


How to  know the member belongs to, which group and how many

Select * from v$logfile;

To find the redo log file size

Select * from v$log;


Create a new group with file & add a new log file

Alter database add logfile  ‘/opt/redo03.log’ size 100m;

Alter database add logfile group 3 ‘/opt/redo03.log’ size 100m;

group 3 – Optional

Multiplexing redo log file

Alter database add logfile member ‘/opt/redo03_1.log’ to group 3;

5 groups maximum possible.

How to  clear/ flush a member

Alter database clear logfile member ‘/opt/redo03.log’ ;

How to  flush a group

Alter database clear logfile group 3;


The flush will be mostly used for shutdown and startup issues.


How to  drop a member & log file

Alter database drop file member ‘/opt/redo03.log’;

Alter database drop logfile ‘/opt/redo03.log’;

How to  drop a group

Alter database drop logfile group 3;


How to  resize he  redo log file

An existing redo  log file cannot be resized.

An alternate solution: Create a new file with the size and drop the existing one in the inactive state.


How to  change the  redo log file into  inactive

Alter system switch logfile;

How to  find  the  number of LSN

Select * from v$log_history;


Archive log files

It’s an offline backup of redo log files.

For every log switch the archive log will be generated. You can choose automatic or manual archiving.

The used file size will come to archive log location (Example log size 100 g, but used 10g)

It’s an offline copy and you can do anything (copy, cut & delete), but before delete we need to back up the files. Since, it’s used for recovery purpose.

An archived redo log file is a copy of one of the filled members of a redo log group. Since, both has same data.

The default archive has two processes. You can add more LOG_ARCHIVE_MAX_PROCESSES. Oracle will increase it whenever needed, you can also limit this.

You can choose whether to archive redo logs to a single destination or multiplex them.

LOG_ARCHIVE_DEST_n – Local or remote

You can specify the location as local as a directory or service. Service is for standby database.


To specify a maximum of two locations, use the LOG_ARCHIVE_DEST parameter to specify a primary and the LOG_ARCHIVE_DUPLEX_DEST to specify an optional secondary archive destination. All locations must be local.


Mode 1. Archive 2. No Archive

Archive No Archive
Archive files will be generated No files
We can recover data without any loss There will be a data loss


How to  find  the  database archive mode

Select  log_mode from v$database;

Archive log list;


Archive mode                    : no archive

Automatice Archive         : disable

Dest                                     : online_recovery_area – Destination of log

OLN                                     :72 –old LSN

CLN                                      :72 – Current LSN

NLN                                     :72 – Next LSN

We need to enable the archive process to place archive mode.

Archive start/stop;


How to make it permanent – From 10g onwards no need to enable the  Archive process

Alter system set log_archive_start =true/false =scope = spfile;

Change the database into Archive mode:

Shutdown immediate

Startup mount

Alter database archivelog/ no archivelog;

Alter database open;

Where the Archive log will generate

9i Lunix – $ORACLE_HOME/dbs

9i Windows – $ORACLE_HOME%database


10g & 11g – Both Lunix and windows


Change the Archive log location from default to other FS

$ mkdir –p /Archive/testDB

alter system set log_archive_dest_1= ‘location=/opt/Archive/testdb1’ SCOPE=spfile;

scope – optional

Archive Full error: ORA-257. If the archive location is full DB will hang. We need to backup the logs and delete.

For emergency, We can point the archive log to a different location and can free up.

Alter system set log_archive_dest= ’Different location’;

We can change back to the original location. But, always need to backup the  logs whatever location it has been running.

How to get the  number of archive log files

select * from v$archived_log;

Understanding Archive Destination Status

Valid/Invalid  –  indicates whether the disk location or service name information is specified and valid

Enabled/Disabled  – indicates the availability state of the location and whether the database can use the destination

Active/Inactive  –  indicates whether there was a problem accessing the destination


select * from V$ARCHIVE_DEST;


I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: