STOP Rman backup job

If the Rman job process is running in the foreground on your screen then type ‘ctl+C’, but if its running in the background then you can kill either manually by finding sid/spid or using OEM

To know the rman sid and spid of your running job use the below script

SQL >   SELECT p.SPID, s.sid, s.serial#, sw.EVENT, sw.SECONDS_IN_WAIT AS  SEC_WAIT, sw.STATE, CLIENT_INFO 
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p

WHERE s.client_info LIKE 'rman%'

AND s.SID=sw.SID

AND s.PADDR=p.ADDR;

SPID      SID      SERIAL#    EVENT                                     SEC_WAIT     STATE        CLIENT_INFO

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

1234   5678     98765      control file sequential read                         0     WAITING     rman channel=ORA_DISK_1

SQL >   alter system kill session ’SID, SERIAL#′;

To know more about killing a session :   find and kill oracle session

11g RMAN Cloning using Duplicate Command

Today I was given a task to perform Rman cloning from server prod to Dev.

NOTE: Here i'm doing backup based Duplication instead of Active duplication (NO backup method)

Here my DEV server is already identical to prod except with a data in it Since my both prod and dev server are 11gr2 I decided to go with Rman Duplicate Command, even though  we can go either for Data Pump  or Rman or Manual Copy(cp) methods but my prod size is around 2TB so Rman is a better solution for it(since its fast and recommended)

SQL> select banner from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 – Production

$ cat    /etc/redhat-release

Red Hat Enterprise Linux Server release 5.4 (Tikanga)

Step 1:   Take the backup of the prob server either HOT or COLD (I did cold) depending upon your situation

For a cold backup

SQL>   shutdown Immediate;

SQL>   Startup mount;

And connect to RMAN (I have no catalog)

Rman target / nocatalog

Rman > configure controlfile autobackup on;

Allocate more channels depending upon number of CPU’s available on your database, here I used 12 channels

RMAN >   configure device type disk parallelism 12 backup type to backupset;

RMAN>   show all ;

RMAN configuration parameters for database with db_unique_name PROD are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 12 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/11.2.0.2/dbs/snapcf_PROD.f'; # default

Rman >  run {

2>  allocate channel d1 type disk;

3> Backup full tag full_offline_bkup

4> Format ‘/var/backup/corppsdb/rman/db_t%t_s%s_p%p’

5> Database plus archivelog;

6> Release channel d1;

}

If you write a script like above then Rman uses only 1 channel even though you set it for 12 paths by default.

Rman >  run {

2>Backup full tag full_offline_bkup

3> Format ‘/var/backup/corppsdb/rman/db_t%t_s%s_p%p’

4> Database plus archivelog;

}

Now it uses 12 channels…….

It took me around 12 hrs. to complete backup to the file location specified (12 backup sets created)

STEP 2 :  Move all the backup sets to the DEV server using either SCP or SFTP or FTP Or whatever you are convenient to a specific location

STEP 3 :   Restore all the data into the DEV server using RMAN Duplicate Command

Before Doing RMAN Duplication makes sure you have TNS entry of prod in DEV server

Go to the $ORACLE_HOME/network/admin location and open tnsnames.ora file and see if not there copy from prod and put in dev  tnsnames.ora file

I already have both Parameter file and Controlfile in DEV server but if you don’t have you need to create pfile and controlfile which is same as like PROD pfile and controlfile, for this you need to copy pfile and controlfile from prod and change all parameters according to the DEV

The two main mandatory parameters we need to change in DEV are

db_file_name_convert=(<source_db_path>,<target_db_path>)

log_file_name_convert=(<source_db_path>,<target_db_path>)

Password file also should create in $ORACLE_HOME/dbs

orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=<your password>

Then shut down and startup dev in NOMOUNT again…..

SQL >   shut immediate;

SQL >   startup nomount;

$ RMAN    target      sys/passwd@prod

RMAN >   connect auxiliary /                              --->  connecting to target

RMAN>   show   all;                    (just to check everything)

RMAN>   run {

2>   set until time =’ sysdate  –  1’;

3>   Duplicate target database to aux_database_name nofilenamecheck;

}

...........

.......

database opened
Finished Duplicate Db at 09-DEC-11

Now my dev database is exact copy of prod database.

If you want to see the Converter parameters in pfile or spfile

SQL> show parameter convert

NAME                                         TYPE           VALUE

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

_convert_set_to_join                 boolean     FALSE

db_file_name_convert                 string      +DATA/PROD/DATAFILE/, +DATA/DEV/DATAFILE/

log_file_name_convert                string      +DATA/PROD/ONLINELOG/, +DATA/DEV/ONLINELOG/             

  See: RMAN 'Duplicate From Active Database' Feature in 11G (Doc ID 452868.1)               


See also :
                     Rman cloning 
                     Rman Disaster recovery
                     Rman Data Recovery Advisor(DRA)
                     Rman FRACTURED BLOCK
                     Rman Catalog Views

ORA-00059: maximum no. of DB_FILES exceeded

ORA-00059:	maximum number of DB_FILES exceeded
Cause:	The value of the DB_FILES initialization parameter was exceeded.
Action:	Increase the value of the DB_FILES parameter and warm start.

Today I came across ORA00059: maximum number of DB_FILES exceeded, while adding a tablespace to my database then I checked db_files parameter

This will give the MAXDATAFILES specified at control file level while creating the database

Select   value  from  v$parameter   where  name = ‘db_files’ ;

value

-------

500

Also I checked  v$control_record_section to see MAXDATAFILES 

select records_total from v$controlfile_record_section where type = ‘DATAFILE’;

records_total

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

700

Then I increased the db_files parameter

SQL> alter system set db_files=700 scope=spfile;

System altered.

And bounce the database to see the new value

select   value  from  v$parameter   where  name = ‘db_files’;

value

-------

700

You can still increase db_files to morethan control_record_section parameter

EX:  db_files= 800

      Control_record_section=700

But when you reach datafiles to near 700 then control_record_section automatically increases its value

Ex: select count(*) from dba_data_files;

Count(*)

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

698

And still if you add one more to it

Ex: select count(*) from dba_data_files;

Count(*)

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

699

Then you can check the control_record_section  parameter automatically increases its value

select records_total from v$controlfile_record_section where type = ‘DATAFILE’ ;

records_total

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

800

NOTE:

When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. However,if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.

DB_FILES specifies the maximum number of database files that can be opened for this database.

Avoid Inactive PUTTY session

I’m taking Cold backup of database which is of 2TB size with Rman  using putty  for that I connected to rman(database in mount stage)  and written the backup script with 1 channel to disk and left  to my home in the evening so rman created a backup piece after it has reached around 160gb it has to create another backup piece but due to putty which is in idle for more than 1 hour it became inactive and unable to create another backup piece this results in rman to stop backing up my database

    So in order to avoid this kind of situation here is the solution

STEP 1 :Go to PuTTY Configuration –> Connection and set the "seconds between keepalives" to a non-zero value. This ensures that your PuTTY client sends NULL packets to the server regularly. If you do not know the idle session timeout defined on your server, then choose a small value for the "seconds between keepalives". The value you select must be less than the idle session timeout of your server for PuTTY to keep your connection active. The default value is 0 which tells PuTTY not to bother about keeping the connection alive. Refer the picture below:

STEP 2 : Save Your PUTTY Configuration

So, after completing STEP 1, you must go to the session window and click the “SAVE” button.

Then your Putty session doesn’t go to inactive state even though you leave that for a long time.

Bitmap & B-Tree indexes

What is Index?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

Oracle's two major index types are Bitmap indexes and B-Tree indexes. B-Tree indexes are the regular type that OLTP systems make much use of, and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.

 

What is Bitmap Index?

A bitmap index is an indexing method that can provide both performance benefits and storage savings. Bitmap indexes are particularly useful for data warehousing environments because data is usually updated less frequently and ad hoc queries are more common.

Bit-mapped indexes are best for “low-cardinality” data (such as a column to indicate a person’s gender, which contains only two possible values: MALE and FEMALE).

 

Characteristic of Bitmap Indexes:

        1.  For columns with very few unique values (low cardinality)

        2.  Tables that have no or little insert/update are good candidates (static data in warehouse)

        3.  Stream of bits: each bit relates to a column value in a single row of table.

 

What is B-Tree Index?

B-tree indexes are usually created on columns containing the most unique values. It is an ordered set of entries in which each entry a search key value and a pointer to a specific row with that value. When a server finds a search key value matching a constraint, the pointer is used to fetch the row.

Referrence:  http://tamzidul-amin.blogspot.com/2010/03/index-bitmap-index-b-tree-index.html