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

Oracle 11g Recovery from Backup Control File

Recovery from Backup Control File due to loss of all Control Files
------------------------------------------------------------------

1.
SQL> Select name
From v$controlfile;

2.
SQL> connect sys as sysdba
SQL> shutdown immediate;

3.
To simulate loss of control file rename the control files listed in step 1 above.

4.
SQL> startup

O/P -> ORA-00205: error in identifying control file, check alert log for more info

5.
Copy the control file from backup location to the location shown in step 1 above
(Where the control file should be)

$cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control01.ctl
$cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control02.ctl
$cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control03.ctl

or

SQL> host cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control01.ctl

SQL> host cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control02.ctl

SQL> host cp /u01/oracle/orabackup/orcl1/hot/control1.bak /u01/app/oracle/oradata/orcl1/controlfile/control03.ctl

6.
SQL> alter database mount;

7.
SQL> recover database using backup controlfile;

O/P -> may have ORA-00279
followed by ORA-00289
followed by ORA-00280...
Specify log: filename Auto Cancel

8.
Type
auto

O/P -> may have ORA-00308
followed by ORA-27037...

9.
Needed if step 8 did not complete with 'Media recovery complete'.

SQL> Select lf.group#, lf.member, l.sequence#
From v$logfile lf, v$log l
Where lf.group# = l.group#;

Note the member and Sequence#

10.
Start recovery again

SQL> recover database using backup controlfile;

O/P -> ORA-00279
followed by ORA-00289
followed by ORA-00280...
Specify log: filename Auto Cancel

11.
Enter the member value of sequence# for which error was given in step 10 above

e.g./u01/app/oracle/oradata/orcl1/onlinelog/redo01.log

O/p -> Media recovery complete.

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

- Suresh

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

Point-in-time recovery (Time based)
----------------------------------------
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

$ 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 '2009-09-26:19:18:12';
or
SQL> recover database until time '09/26/2009 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.

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

- Suresh

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';

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

- Suresh

11g Oracle Hot Backup (Online Backup)

Manual Hot (online) Backup
-----------------------------

1.
mkdir for hot backup and make sure it exists with correct write permissions.
$ pwd -> /u01/oracle/orabackup/orcl1/hot

2.
sqlplus sys as sysdba

SQL> Select log_mode
From v$database;

o/p -> ARCHIVELOG

3.
SQL> Select tablespace_name, file_name
From DBA_DATA_FILES;

4.
SQL> Select group#, sequence#, status
From v$log;

o/p -> Status as CURRENT, ACTIVE, INACTIVE

5.
SQL> alter database begin backup;

6.
Copy all datafiles (Result from step 3 above)

e.g.$ cp /u01/app/oracle/oradata/orcl1/*.dbf /u01/oracle/orabackup/orcl1/hot/*.*
or
SQL> host cp /u01/app/oracle/oradata/orcl1/*.dbf /u01/oracle/orabackup/orcl1/hot/*.*

7.
SQL> Select group#, sequence#, status
From v$log;

o/p -> Status as CURRENT, ACTIVE, INACTIVE

The Sequence that was active in step 4 and upto the sequence that is active from step 7 are needed to restore the backup.

8.
SQL> alter database end backup;

9.
SQL> alter system switch logfile;

If needed run
SQL> alter system archive log all;

10.
Check the archive log dir to make sure log-file sequences that was active in step 4(through)
log-file sequences that was active in step 7 were created.

SQL > show parameterlog_archive_format

SQL > show parameterlog_archive_dest_1

SQL > host ls /u01/oracle/arch/orcl1
(In this e.g. /u01/oracle/arch/orcl1 is archive log dir)

11.
SQL > host cp /u01/oracle/arch/orcl1/arc*.* /u01/oracle/orabackup/orcl1/hot/*.*

12.
SQL > host ls /u01/oracle/orabackup/orcl1/hot/*.*
Ensure the files (data files and Redo log sequence) are copied

13.
SQL> alter database backup controlfile to '/u01/oracle/orabackup/orcl1/hot/control1.bak';

14.
optional -> If needed remove the archive-log files that were backed-up

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

- Suresh

11g Convert to Archive Mode

Archivelog Mode
------------------

1.
mkdir for archive and make sure it exists with correct write permissions.
$ pwd -> /u01/oracle/arch/orcl1

2.
sqlplus sys as sysdba

SQL> Select log_mode
From v$database;

o/p -> NOARCHIVELOG or ARCHIVELOG (If NOARCHIVELOG then proceed as follows;)

3.
SQL> alter system set log_archive_dest_1='location=/u01/oracle/arch/orcl1';

4.
SQL> shutdown immediate;

5.
SQL> startup mount;

SQL> Select open_mode
From v$database;

o/p -> MOUNTED

6.
SQL> alter database archivelog;

7.
SQL> alter database open;

SQL> Select log_mode
From v$database;

o/p -> ARCHIVELOG

8.
SQL> alter system switch logfile;

9.
SQL> host ls /u01/oracle/arch/orcl1

o/p -> The logswitch should have created an archive redo log

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

- Suresh

Oracle 11g Manual Cold Backup

Manual Cold Backup
---------------------

Note: For ASM based backup use RMAN

For Non-ASM

1.
$ cd /u01/oracle
$ mkdir orabackup
$ cd orabackup
$ mkdir orcl1
$ cd orcl1
$ mkdir cold
$ cd cold

$ ls
$ pwd -> /u01/oracle/orabackup/orcl1/cold

2.
sqlplus sys as sysdba
SQL>

3.
SQL> Select tablespace_name, file_name
From DBA_DATA_FILES;

4.
SQL> Select member
From v$logfile;

5.
SQL> Select name
From v$controlfile;

6.
SQL> shutdown immediate;

7.
SQL> exit

8.
Copy all the necessary files from based on o/p from step 3, 4 and 5

e.g.
$ cp /u01/app/oracle/oradata/orcl1/*.* /u01/oracle/orabackup/orcl1/cold

Note: TEMP file need not be copied.
Copy datafiles, logfiles and controlfiles.

9.
sqlplus sys as sysdba

SQL> startup


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

- Suresh