RMAN - Summary

RMAN
--------

$ 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.


-----------

-Suresh

Useful Oracle DBA Commands

Sample .profile (.bash_profile for oracle user in /home/oracle)

---------

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

umask 022
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=/u01/app/oracle/oracle/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=MCDB01 export ORACLE_SID


TMP=/u01/oratmp; export TMP
TMPDIR=$TMP; export TMPDIR

PATH=$PATH:$HOME/bin

PATH=$PATH:$ORACLE_HOME/bin

export PATH
unset USERNAME

---------

$>sqlplus / as sysdba

sqlplus>startup;

sqlplus>shutdown;

sqlplus>insert into DEPT values (60, 'Manager', 'Dallas');

sqlplus>commit;

sqlplus>show parameter log

-------------

-Suresh

Stages of Oracle Startup and Shutdown

Instance Startup, DB Mount, DB Open
(DB Close, DB Unmount, Instance Shutdown)

startup
startup restrict
startup nomount
startup mount
startup read only

startup force restrict
startup force mount
startup force nomount

shutdown (shutdown normal)
shutdown immediate
shutdown abort
shutdown transactional

e.g.

$>sqlplus / as sysdba
SQL> startup;

------

$>sqlplus / as sysdba
SQL>startup nomount;

------

$>sqlplus / as sysdba
SQL>starup mount;

------

$>sqlplus / as sysdba
SQL>shutdown immediate;

-------

-Suresh

Few Oracle DB Terms

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.

--------

-Suresh

Oracle 11g RMAN Incremental Backup

RMAN Incremental Backup
-----------------------------

DB should be backed up in ARCHIVELOG mode for Incremental Backup.

1.
ORACLE_HOME=/u01/app/oracle/product/11.0.1/db_1
export ORACLE_HOME

ORACLE_SID=orcl1
export ORACLE_SID

PATH=$PATH:$ORACLE_HOME/bin

2.
$ sqlplus / as sysdba

SQL> Select log_mode
From v$database;

O/P -> Make sure the DB is in ARCHIVELOG mode

SQL> exit

3.
rman target=/

4.
RMAN> Backup incremental level 0 database plus archivelog delete input;

5.
RMAN> exit

6.
SQL to check the size of the Backup

$ sqlplus / as sysdba

SQL> Select a.set_count, a.start_time, a.completion_time, sum(b.blocks)
From v$backup_set a, v$backup_datafile b
Where a.set_count = b.set_countand to_char(a.start_time, 'mm/dd/yyyy hh24:mi:ss') =
(Select to_char(max(start_time), 'mm/dd/yyyy hh24:mi:ss')
From v$backup_set
Where incremental_level = 0)
group by a.set_count, a.start_time, a.completion_time;

---------

To do a incremental level 1 backup

7.
rman target=/
RMAN> Backup incremental level 1 database plus archivelog delete input;

8.
RMAN> exit

Note: To check the size of the incremental level 1 Backup use SQL in step 6 except use incremental_level = 1 in the inner Where clause.

----------------------------------------------------------

- Suresh

11g Oracle RMAN offline Backup

RMAN offline Backup
----------------------

DB can be backed up in NOARCHIVELOG or ARCHIVELOG mode withoffline backups.

1.
ORACLE_HOME=/u01/app/oracle/product/11.0.1/db_1
export ORACLE_HOME

ORACLE_SID=orcl1
export ORACLE_SID

PATH=$PATH:$ORACLE_HOME/bin

$ rman target=/

2.
RMAN> shutdown immediate
RMAN> startup mount

3.
RMAN> Backup database;

O/p -> ....Finished backup at ...
...Finished Control File and SPFILE Autobackup at ...

4.
RMAN> alter database open;

----------------------------------------------------------

- Suresh

Oracle 11g Recovery from loss of current online Redo log file

Recovery from loss of current online Redo log file
--------------------------------------------------

1.
SQL> Select member
From v$logfile;

2.
To simulate loss of online Redo log file rename the fileslisted in step 1 above.

Result -> This may crash the DB -> This simulates loss of online Redo log files

If DB did not crash try
SQL> alter database checkpoint;
SQL> alter database clear logfile;
SQL> shutdown abort;

3.
sqlplus / as sysdba;
SQL> startup

O/P -> ORA-00313
followed by ORA-00312
followed by ORA-27037...

4.
Restore the DB files, control files from the latest backup

5.
sqlplus / as sysdba
SQL> startup mount;

6.
Determine the last log sequence archived

SQL> Select max(sequence#)
From v$archived_log
Where resetlogs_changed# = (Select max(resetlogs_change#)
From v$archived_log);

7.
SQL> recover database using backup controlfile until cancel;
SQL> recover database until cancel using backup controlfile;

O/P -> ORA-00279
followed by ORA-00289
...
followed by ORA-00280
followed by ORA-00278
Specify log: {=suggested filename Auto Cancel}

8.
Type
cancel

O/p -> Media recovery cancelled.

9.
SQL> alter database open resetlogs;

Recovery is done.

----------------------------------------------------------

- Suresh