Location of Database Files Configred with ASM and Data Guard

For Primary DB
SID=TESTDB

DB_UNIQUE_NAME=TESTDB_PRIMARY

For Standby DB
SID=TESTDB

DB_UNIQUE_NAME=TESTDB_STANDBY

ORACLE_BASE

/u01/app/oracle

ORALCE DATABASE HOME

$ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

$ORACLE_SID=TESTDB

ORACLE GRID HOME (Oracle Grid Infrastructure--including ASM and Oracle Restart)

$ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid

$ORACLE_SID=+ASM

Listener Home

The default home for all listeners is the grid home

/u01/app/oracle/product/11.2.0/grid/network/admin

Alert and trace files

$ORACLE_BASE/diag/rdbms/

Data Guard Broker Configuration Datafile

$ORACLE_HOME/dbs

Data Guard Broker Configuration log

$ORACLE_BASE/diag/rdbms/trace

ASM log and trace file

/u01/app/oracle/diag/asm/+asm/+ASM

Oracle Sql Scripts

/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin

FAST RECOVERY AREA

/u01/app/oracle/oradata/TESTDB/archivelog

TESTDB_PRIMARY DATABASE FILES

Control File -- in TESTDB_PRIMARY (DB_UNIQUE_NAME) direcotry

select * from v$controlfile;

+DATA/testdb_primary/controlfile/current.256.747592231

/u01/app/oracle/oradata/TESTDB/archivelog/TESTDB_PRIMARY/controlfile/o1_mf_6sng3qps_.ctl

(oracle save a copy of control file in FAST RECOVERY AREA defined by DB_RECOVERY_FILE_DEST

init parameter)

Parameter File -- in TESTDB_PRIMARY (DB_UNIQUE_NAME) directory

The initSID.ora is in $ORACLE_HOME/dbs

initTESTDB.ora, inside the file it is the pointer to the spfile.

SPFILE='+DATA/TESTDB_PRIMARY/spfileTESTDB_PRIMARY.ora'

select * from V$PARAMETER where name = 'spfile'

Passowrd File

$ORACLE_HOME/dbs

Datafile -- in TESTDB_PRIMARY (DB_UNIQUE_NAME) directory

SELECT NAME FROM V$DATAFILE;

+DATA/TESTDB_PRIMARY/DATAFILE/

Online Redo Log Files --TESTDB_PRIMARY (DB_UNIQUE_NAME) Directory

select * from v$logfile where type='ONLINE'

+DATA/testdb_primary/onlinelog/

/u01/app/oracle/oradata/TESTDB/archivelog/TESTDB_PRIMARY/onlinelog/

oracle maintains a 2nd copy at Flash Recovery Destination

If a new online redo log is created, a correspoindg standby redo log must be created on standby.

ALTER DATABASE ADD STANDBY LOGFILE Group 17 SIZE 256000K;

Standby Redo Log Files -- TESTDB_PRIMARY (DB_UNIQUE_NAME) Directory

select * from v$logfile where type='STANDBY'

+DATA/testdb_primary/onlinelog/

/u01/app/oracle/oradata/TESTDB/archivelog/TESTDB_PRIMARY/onlinelog/

oracle maintains a 2nd copy at Flash Recovery Destination

Archive Log Files -- in TESTDB, the default DB_RECOVERY_FILE_DEST

The archive log file is saved in /u01/app/oracle/oradata/TESTDB/archivelog

show parameter DB_RECOVERY_FILE_DEST;

/u01/app/oracle/oradata/TESTDB/archivelog

show parameter log_archive_dest_1;

LOCATION=/u01/app/oracle/oradata/TESTDB/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TESTDB_PRIMARY

TESTDB_STANDBY DATABASE FILES

Control File -- in TESTDB_STANDBY (DB_UNIQUE_NAME) direcotry

select * from v$controlfile;

+DATA/testdb_standby/controlfile/current.257.757684131

/u01/app/oracle/oradata/TESTDB/archivelog/TESTDB/controlfile/o1_mf_6qf8lbgc_.ctl

Parameter File -- in TESTDB_STANDBY(DB_UNIQUE_NAME) directory

The initSID.ora is in $ORACLE_HOME/dbs

initTESTDB.ora, inside the file it is the pointer to the spfile.

SPFILE='+DATA/TESTDB_STANDBY/spfileTESTDB.ora'

select * from V$PARAMETER where name = 'spfile'

Passowrd File

$ORACLE_HOME/dbs

Datafile -- in TESTDB_STANDBY (DB_UNIQUE_NAME) directory

SELECT NAME FROM V$DATAFILE;

+DATA/TESTDB_STANDBY/DATAFILE

Online Redo Log Files --TESTDB_STANDBY (DB_UNIQUE_NAME) Directory

select * from v$logfile where type='ONLINE'

+DATA/testdb_standby/onlinelog/

/u01/app/oracle/oradata/TESTDB/archivelog/TESTDB_STANDBY/

oracle maintains a 2nd copy at Flash Recovery Destination

If a new online redo log is created on primary, a correspoindg standby redo log must be created on standby.

ALTER DATABASE ADD STANDBY LOGFILE Group 17 SIZE 256000K;

Standby Redo Log Files -- TESTDB_STANDBY(DB_UNIQUE_NAME) Directory

select * from v$logfile where type='STANDBY'

+DATA/testdb_standby/onlinelog/

/u01/app/oracle/oradata/TESTDB/archivelog/TESTDB_STANDBY/

oracle maintains a 2nd copy at Flash Recovery Destination

Archive Log Files -- shipped archives are in TESTDB folder, standby archives are in TESTDB_STANDBY

The archive log file is saved in /u01/app/oracle/oradata/TESTDB/archivelog

show parameter DB_RECOVERY_FILE_DEST;

/u01/app/oracle/oradata/TESTDB/archivelog

show parameter log_archive_dest_1;

LOCATION=/u01/app/oracle/oradata/TESTDB/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TESTDB_STANDBY

when it is in standby role, this destination stores the archive shipped from primary

select * from V$PARAMETER where name = 'log_archive_dest_2'

LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)

This uses DB_RECOVERY_FILE_DEST which is

/u01/app/oracle/oradata/TESTDB/archivelog

when it is in standby role with real time log apply, the primary online log is applied to standby redo log and then is archved to this destination.

Backupset and Autobackups

When fast recovery area is defined, RMAN automatically saves backupset and autobackups in the fast recovery area, when the fast recovery area is full, RMAN auto deletes obseletes,expired to make room available.