How to make a cold backup for database running on ASM ( using RMAN )

Many a times, you will come in a situation… Or they may ask you in Interview that how to take a cold backup using RMAN.

You heard Right, Cold backup using RMAN…

Using RMAN Utility, we can perform the backup in MOUNT mode.

Lets see…

Follow the below steps to take cold back of database running on ASM.

1.Start the instances in all the nodes in mount stage.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1.7504E+10 bytes

Fixed Size                  2235448 bytes

Variable Size            4429185992 bytes

Database Buffers         1.3019E+10 bytes

Redo Buffers               53477376 bytes

Database mounted.

2.Connect RMAN from any node and take the backup as below.

[oracle@backupServer ]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon May 2 05:53:43 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SPSHIDMP (DBID=465850406, not open)

RMAN> backup device type disk format '/backupServer/test/rman_backup_43/backup/%U' database plus archivelog;

Starting backup at 02-MAY-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=4566 instance=spshidmp1 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=2 sequence=55 RECID=107 STAMP=783110838

input archived log thread=1 sequence=58 RECID=106 STAMP=783107220

input archived log thread=1 sequence=59 RECID=108 STAMP=783139625

input archived log thread=2 sequence=56 RECID=109 STAMP=783155353

input archived log thread=1 sequence=60 RECID=112 STAMP=783164816

3.Once done, start the instances on all the nodes.

SQL> alter database open;

Database altered

We can take a database Backup using RMAN in Mount, this will be said as COLD-BACKUP

What is "Buffer Busy Wait " - - Tuning

“When several concurrent sessions will read the same blocks/same table or same index block.”

Definition : When two or more session issue the same query/related query (that access the same database blocks), the few sessions will read the data from database buffer cache (based in inittrans and maxtrans in block level) while other sessions are in wait. In that case , Buffer Busy Wait – Event occurs.

How to find Buffer Busy Waits ?

Using the AWR  report top-5 wait-event shows the read by other session or Buffer busy wait event.

Or Using v$segment_statistics or v$system_event also, we can see the buffer busy wait event.

What are Hot Objects/Blocks ?

When Number of concurrent session’s access single block in an object then they are called as hot object.

How to find these Hot Objects ?

Using AWR report “Segment statistics” section shows the HOT objects list.

Or also using below query, we find the hot objects. :

SELECT relative_fno, owner, segment_name, segment_type

FROM dba_extents

WHERE file_id = &file

AND &block BETWEEN block_id AND block_id + blocks - 1;

How we reduce the buffer busy waits?

Method 1:

Increasing INITRANS value method:

First we should know how concurrent sessions accessing a single block in an object?

Each database-block having 3 layers.

1. Cache layer

2. Transaction layer

3. Data layer

Transaction layer is playing vital role for block contention.

Each block will have ITL (INTERESTED TRANSACTION LIST) slots. This ITL slots is required for any sessions that’s need to modify a block in an object.

INITRANS value for table having segment 1 & INITRANS for index segment having 2.

MAXTRANS value default is 255.

If there is no free ITL slot in a blocks, then transaction will waiting for serially for a free ITL slot.

By increasing INITRANS value to avoid the serial transaction waiting. Concurrently number of session will perform the DML operation in single block.

Each ITL requires approximately 23 bytes in the block header.

Method 2 : Increasing PCTFREE method:

Suppose a single 8 KB block contains 1000 rows. We reducing the rows in a block can easily reduce the buffer busy wait.

PCTFREE space is used for future updates only. We have an 8 KB data block. Default PCTFREE value is 10%. If we increased the PCTFREE value is 20% automatically number of rows inserted in a block is automatically reduced.

Method 3:   Reducing database block size method:

It’s similar to PCTFREE method. Suppose a single 8 KB block contains 1000 rows. Using db multiblock size future we used 4 KB data block. Now 1000 rows should be stored two 4 KB blocks.

Our goal is to reduce the number of records stored in a block.

Tune the inefficient queries:

Reduce the number of blocks accessing for an objects in buffer cache. By tuning the query to minimize the number of blocks reads from disk to database buffer cache.

Example: I have a one huge table & it contains 10000 blocks. There is no index for this table.If we doing any operation against this table, it’s going full table scan & accessing all the blocks in a table (server process reads the 10000 blocks from disk to database buffer cache). We can put proper index for this table & avoid the full table scan.

See Also : 

How to Prepare for DBA Interview

General Tips to Prepare for an Oracle DBA Job Interview

For more updates,  Like Us Our Facebook Page Here 

How to recover from the Loss of a Datafile on a Standby Database ?

Use these following steps to recover if you lost a datafile on a standby database :

 1.  Initially Stop the Redo Apply using the ALTER DATABASE command:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 2. Now , Start RMAN and connect both to the standby and recovery catalog using:

RMAN TARGET / CATALOG  rcat/passwd@RCAT

 3. Issue the following commands to restore and recover datafiles on the standby database:

RMAN> RESTORE DATAFILE 5;

RMAN> RECOVER DATAFILE 5;

 4. Now, Restart the SQL Apply :

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

See Also : 

How to Prepare for DBA Interview

General Tips to Prepare for an Oracle DBA Job Interview

For more updates,  Like Us Our Facebook Page Here 

Server Maintenance Tasks for a DBA

To delete files older than 60 days

$  find . -name "*.gz" -depth -mtime +60 -exec rm {} \;

To list files dated May 18

ls -lrt | grep 'May 18' | awk -F' ' '{print$9}'

To delete files dated May 18

rm -rf `ls -lrt | grep ‘May 18' | awk -F' ' '{print$9}'`

To list files of Month May

$  ls -lrt | grep 'May' | awk -F' ' '{print$9}'

$  ls -lrt *.trc|grep 'May'|xargs rm -rf  {}\;

Things need to check during - - Server reboot

Suppose you are working on Dataguard setup or normal Stand-Alone Database,

Following steps mandatory just take backup of following three steps and store it in separate notepad.

Once the server reboot activity completed.. then cross check once which was taken before server reboot.

$  ps -ef|grep pmon

$  ps -ef|grep pmon|wc -l

$  ps -ef|grep inh (OR) ps -ef|grep tns

$  ps -ef|grep inh|wc -l

$  ps -ef |grep d.bin

$  hostname

$  date

$  uname -a

$  cat /etc/oratab  /  cat /var/opt/oracle/oratab

$  uptime

$  who -b

$  df -h  / df –gt

Data Guard Database  Shutdown Procedure

Login as oracle

source the environment

$  sqlplus “/ as sysdba”

SQL >alter database recover managed standby database cancel;

                                                                                ------> to cancel MRM mode

SQL> shutdown immediate;

SQL> exit

Data Guard Db Startup Procedure

$  Sqlplus “/ as sysdba”

SQL> startup nomount;

SQL> alter database mount standby database;

SQL>  recover managed standby database disconnect from session;

                                                                ----------->To put it in MRM MODE

SQL>  exit;

Click here to Join Our Blog and Stay Updated