DBA 101

Alert log
---------
In BACKGROUND_DUMP_DEST - Name: alert.log

Archived redo logs
--------------------
In ARCHIVELOG mode, generated when Oracle switches online redo logs - LGWR process
Can be up tp 10 destinations - LOG_ARCHIVE_DEST_n

Backup control file
-------------------
Generated by:
alter database backup controlfile to 'file_name';
or
alter database backup controlfile to trace;

Checkpoint
-------------
DB event that causes dirty buffers from memory to be written to disk.

DB Parameter file
-------------------
Has DB and instance configuration information.
spfile.ora - Binary file altered using alter system command
init.ora - text file

Flash Recovery Area
-----------------------
Optional area to store recovery related files like
RMAN backups,
archived redo logs,
online redo logs,
control files

Instance
---------
A unique set of Oracle Processes and memory from a machine that manipulates a given database (db files, control files, online redo log files, archived redo log files, Flashback logs).
An instance is associated with only one database at a given time.

The basic processes that make up an instance are:
PMON
SMON
DBWn
LGWR
CKPT

The basic RAM memory that are associated with an instance are:

SGA(System Global Area - SGA_TARGET and SGA_MAX_SIZE):

DB buffer cache - DB_CACHE_SIZE and DB_nk_CACHE_SIZE
Shared pool - SHARED_POOL_SIZE
Redo log buffer - LOG_BUFFER
Large pool - LARGE_POOL_SIZE
Java pool - JAVA_POOL_SIZE
Streams pool - STREAMS_POOL_SIZ

PGA(Program Global Area - PGA_AGGREGATE_TARGET):

---------

Online redo logs
------------------
Min - 2 online redo logs
log switch occurs when the current redo log file fills up or can be forced (by a DBA/privileged user) using SQL>alter system switch logfile; .
Each online redo log has a unique log sequence number - This is also associated with its
corresponding Archived redo log file.
Log sequence number of a log file is entered in V$LOG view.
Log sequence number associated with an Archived Redo log is entered in V$ARCHIVED_LOG view.

An online Redo log has a range of DB System Change Numbers (SCNs).

System Change Number (SCN)
--------------------------------
A number (a counter) to represent the state of a DB at a given point in time.

Trace files
------------
Files that has the list of commands/operations executed by the DB or a process or a session or a program etc that can be used for debugging purposes.

RMAN - Summary

$ rman
RMAN> connect target sys/pwd

connected to target database:MCDB01 (DBID=2882204056)
RMAN>

------
Possible error without sysdba privilege

ORA-01031: insufficient privileges

------

To connect to RMAN using a remote connection make the following entry in the tnsnames.ora
(This is not needed to connect locally from) so that it is a dedicated connection.

MCDB01RMAN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = yemsuresh01.maricorp.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MCDB01)
)
)

-----------

When you use RMAN do not use
alter database backup control file to trace;
use
alter database backup control file to 'filename'
e.g.
alter database backup control file to '/u01/oracle/orabackup/orcl1/manual/control.ctl'

-----------

If you have a recovery catalog DB then you connect from RMAN to the target DB and then
to the recovery catalog (so there are 2 connections from RMAN)

$ rman
RMAN> connect target /
RMAN> connect catalog rman/pwd@reccatdb

-----------

For connecting to Auxiliary DB (that is used for Tablespace point-in-time recovery - TSPITR) or for creating standby DB using RMAN)

$ rman
RMAN> connect target /
RMAN> connect auxiliary sys/pwd@auxdb

Configure one of the DB with a password file to connect remotely and the other can DB can be connected locally.

-----------

A simple backup with the proper configurations in place

$ rman
RMAN> connect target /
RMAN> backup database;RMAN> exit;

-----------

Minimum Parameters to set

log_archive_dest_1='location=/u01/app/oracle/arch/msdb01'
log_archive_min_succeed_dest=1
log_archive_format="%t_%s_%r.arc"
log_archive_dest_state_1='enable'

Other useful parameters

log_checkpoint_timeout
log_checkpoints_to_alert

control_file_record_keep_time (default is 7 - days)
This should be set to a value equal to or greater than db backup retention period.

-----------

Flash Recovery Area for Backups

Files stored in Flash Recovery Area

Archived redo logs
Control File
Control File AutoBackUps
Flashback logs
Redo logs
RMAN data files
RMAN back upset files

-----------

To change the size of the Flash recovery area the parameter DB_RECOVERY_FILE_DEST_SIZE can be changed using

SQL> alter system set db_recovery_file_dest_size=20G scope=both;
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' scope=both;

SQL> alter system set log_archive_dest_10='location=USE_DB_RECOVERY_FILE_DEST';

Not to use FRA

SQL> alter system set db_recovery_file_dest=' ' scope=both;

Using RMAN to increase the size of Flash recovery area:

RMAN>Backup recovery area

Views related to Flash recovery area

DBA_OUTSTANDING_ALERTS
v$RECOVERY_FILE_DEST
v$FLASH_RECOVERY_AREA_USAGE

v$controlfile
v$logfile
v$archived_log
v$datafile_copy
v$datafile
v$backup_piece

IS_RECOVERY_DEST_FILE is a Column with Boolean value in the above 6 views.

-----------

SQL> create user rman_admin identified by pwd default tablespace users;
SQL> grant sysdba to rman_admin;

$ orapwd file=dbapwdfile password=pwd entries=# force=y/n nosysdba=y/n
e.g.
$ orapwd file=dbapwdfile password=oracle1 entries=15 force=n

SQL> alter system set REMOTE_LOGIN_PASSWORD=EXCLUSIVE scope=spfile;
This is a static parameter so cannot be set when DB is running.

11g Summary of Oracle Hot Backup (Online Backup)

Summary of Backup Steps
----------------------------

1. Put the database in hot backup mode.
2. Copy all database datafiles to a backup location.
3. Take the database out of hot backup mode.
4. Force a log switch.
5. Backup the archived redo logs.

sqlplus / as sysdba

SQL> alter database begin backup;
SQL> host cp /u01/app/oracle/oradata/orcl1/*.dbf /u01/oracle/orabackup/orcl1/hot/*.*
SQL> alter database end backup;
SQL> alter system switch logfile;
optional -> SQL> alter system archive log all;
SQL > host cp /u01/oracle/arch/orcl1/* /u01/oracle/orabackup/orcl1/hot/*
SQL> alter database backup controlfile to trace;
SQL> alter database backup controlfile to '/u01/oracle/orabackup/orcl1/hot/control1.bak';

11g Oracle Point-in-time recovery (Time based)

Point-in-time recovery (Time based)
----------------------------------------
1.
ORACLE_HOME=/u01/app/oracle/product/11.0.2db_1
export ORACLE_HOME

ORACLE_SID=orcl1
export ORACLE_SID

PATH=$PATH:$ORACLE_HOME/bin

$ sqlplus / as sysdba
or
SQL> connect / as sysdba;
or
SQL> connect sys as sysdba;

SQL> Shutdown immediate;

2.
Restore DB datafiles from backup location to the location where DB files are.

$ cd /u01/oracle/orabackup/orcl1/hot/
$ pwd
$ cp *.dbf /u01/app/oracle/oradata/orcl1/*

3. sqlplus / as sysdba

SQL> startup mount

SQL> alter session set nls_date_format='mm/dd/yyyy hh24:mi:ss';

4.
SQL> recover database until time 'yyyy-mm-dd:hh:mm:ss';

e.g.
SQL> recover database until time '2011-08-26:19:18:12';
or
SQL> recover database until time '08/26/2011 19:18:12';
(based on nls_date_format)

Enter auto when prompted for archived redo log to apply

5.
alter database open resetlogs;

Note: For this to work you should have all of your datafile backup and archived redo log
upto the point-in-time specified for the recovery and the correct control file.