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 

PARTITION in Oracle

What is a PARTITION in Oracle?

With the help of Partitioning, It allows DBAs to split large tables into more manageable "sub-tables",  to improve database performance, manageability and availability.

What actually happens in partitioning ?

With table partitioning, a table can be physically divided into multiple smaller tables, called partitions, while logically it stays one table. This means your code stays the same, but full partition scans will be executed instead of a full table scan.

When to Partition a Table ?

- If Tables size greater than 2 GB , then it always considered for partitioning.

- Tables containing historical data, in which new data is added into the newest partition.

Example :

If you have a historical table where only the current month's data is updatable and the other 11 months are read only, then that table will be partitioned.

-When the contents of a table need to be distributed across different types of storage devices.

Benefits of partitioning

Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.

Partitioning Reduces the downtime in case of data failure. And a failure of a particular partition will no way affect other partitions.

Maintenance operations can be carried out on selected partitions, while other partitions are available to users, So this will also reduce the downtime.

What is the advantage of partitions, by storing them in different Tablespaces??

1     Reduces the possibility of data corruption in multiple partitions.

2     Back-up and recovery of each partition can be done independently.

What are the Types of partitioning ?

1     Range partitions

2     List partitions

3     Hash partitions

4     Sub partitions

1     Range partitions

The table is divided in ranges, typically used for ‘date’ ranges  

It is partitioned in such a way that each partition contains rows for which the partitioning expression value lies within a given range.

2     List partitions

Here, a limited set of possible values is given ; such that the rows containing the same value are grouped.

Enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition.

3     Hash partitions

Happens when a random distribution occurs.

Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify.

4     Sub partitions

INDEX Partitioning

•              Non partitioned:              No partitioning applied

•              Globally partitioned:     The index is not partitioned on the same key as the table, offer                                                                higher flexibility.

•              Locally partitioned:       The index is partitioned on the same key as the partitioned table,

See Also

Most asked Interview Questions for 2+ Candidates

 

Frequently asked Datapump Scenarios

Database life of DBA 

 

Click here to Join Our Blog and Stay Updated

For more updates,  Like Us Our Facebook Page Here