Wednesday, December 21, 2011

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

Friday, December 16, 2011

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

Saturday, December 10, 2011

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.


Thursday, December 8, 2011

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.




Thursday, December 1, 2011

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.



Tuesday, November 15, 2011

9i Export/Import Parameters


USERID:   username/password
BUFFER:   size of data buffer
FILE:   output file (EXPDAT.DMP)
COMPRESS:   import into one extent (Y)
GRANTS:   export grants (Y)
INDEXES:   export indexes(Y)
ROWS:   export data rows (Y)
CONSTRAINTS:   export table constraints (Y)
CONSISTENT:   cross-table consistency (N)
LOG:   log file of screen output (None)
STATISTICS:   analyze objects (ESTIMATE)
DIRECT:   Bypass the SQLcommand processing layer (N) (new in Oracle8)
FEEDBACK:   Show a process meter (a dot) every X rows exported (0 – Xvalue)
HELP:   Shows help listing MLS MLS_LABEL_FORMAT Used with secure Oracle; we won't cover
these.
FULL:   export entire file (N)
OWNER:   list of owner usernames
TABLES:   list of table names
RECORDLENGTH:  length of IO record
INCTYPE:  incremental export type
RECORD:   track incr. export (Y)
PARFILE:   parameter file name

How to get parent/child of a table in Oracle


This query gives list of children tables of a given table:


SELECT p.table_name PARENT_TABLE, c.table_name CHILD_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND p.table_name = UPPER('&tab');


And this one gives list of parent tables of a given table:

SELECT c.table_name CHILD_TABLE, p.table_name PARENT_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND c.table_name = UPPER('&tab');

Saturday, November 12, 2011

Chmod commands


The UNIX chmod command is used to change the execution permissions of a UNIX file. The  chmod stands for change mode. The chmod command is based on the permissions and the chmod permissions can be assigned either by number or by a letter value.

Owner
Group
World
Meaning
7
7
7
Read + Write + execute
6
6
6
Write + execute
5
5
5
Read + execute
4
4
4
read only
2
2
2
write only
1
1
1
Execute only


Owner (u)
Group (g)
World(o)
Meaning
rwx
rwx
rwx
Read + Write + execute
rw
rw
rw
Read + Write
rx
rx
rx
Read + execute
wx
wx
wx
Write + execute
r
r
r
Read only
w
w
w
Write Only
x
x
x
Execute only

                 

Wednesday, November 9, 2011

Suspend & Resume a Database

                The ALTER SYSTEM SUSPEND statement halts all input and output (I/O) to datafiles (file header and file data) and control files. The suspended state lets us back up a database without I/O interference. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state. The suspend command is not specific to an instance. In an Oracle Real Application Clusters environment, when we issue the suspend command on one system, internal locking mechanisms propagate the halt request across instances, thereby quiescing all active instances in a given cluster. However, if someone starts a new instance another instance is being suspended, the new instance will not be suspended.

Using the ALTER SYSTEM RESUME statement to resume normal database operations. The SUSPEND and RESUME commands can be issued from different instances. For example, if instances 1, 2, and 3 are running, and we issue an ALTER SYSTEM SUSPEND statement from instance 1, then we can issue a RESUME statement from instance 1, 2, or 3 with the same effect. The suspend/resume feature is useful in systems that allow us to mirror a disk or file and then split the mirror, providing an alternative backup and restore solution. If we use a system that is unable to split a mirrored disk from an existing database while writes are occurring, then we can use the suspend/resume feature to facilitate the split. 

The suspend/resume feature is not a suitable substitute for normal shutdown operations, because copies of a suspended database can contain uncommitted updates.

The following statements illustrate suspend and resume usage. The V$INSTANCE view is queried to confirm database status.

     SQL>    alter system suspend;
               System altered.

     SQL>   select database_status from V$instance;
  
             DATABASE_STATUS
               ------------------------
                 SUSPENDED

       SQL>  alter system resume ;
                System altered.

      SQL> select database_status from v$instance ;
        
             DATABASE_STATUS
             -------------------------
              ACTIVE


Oracle Release Dates

Version
Release
Date
Oracle 6
1988
Oracle 7
1992
Oracle 8
1997
Oracle 8i
1998
Oracle 9i
2001
Oracle 10gR1
2003
Oracle 10gR2
July 2005
Oracle 11gR1
Aug 2007
Oracle 11gR2
Sep 2009
Auto Scroll Stop Scroll