BNFD - Your DBA NoteBook On The Net!!!

Wednesday, February 27, 2008

Recovery Catalog

Configure the Recovery Catalog:

Lets assume that 'MARS' is the Catalog Database and the recovery Database is 'EARTH'


1. First create a user to hold the recovery catalog:

CONNECT sys/sys123 @ mars AS SYSDBA

2. Create tablepsace to hold repository :


CREATE TABLESPACE "RMAN"
DATAFILE 'C:\ORCL\RMAN\RMAN01.DBF' SIZE 6208K REUSE
AUTOEXTEND ON NEXT 64K MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

3. Create rman schema owner


CREATE USER rman IDENTIFIED BY rman123
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;

GRANT connect, resource, recovery_catalog_owner TO rman;


4. Then create the recovery catalog:

C:>rman catalog = rman/rman123 @ mars

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to recovery catalog database
recovery catalog is not installed

RMAN> create catalog tablespace "RMAN";

recovery catalog created

RMAN> exit

Recovery Manager complete.

C:>
5.Register Database


Each database to be backed up by RMAN must be registered:

C:>rman catalog=rman/rman@ mars target=sys/password@ earth

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database:earth (DBID=1371963417)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

Tuesday, February 26, 2008

TNS:12541 ERROR

TNS:12541 error: No Linstner



Solution:



1.Check the target Db's listener.ora , check for HOST and PORT ensure that both are correct.



2. Bounce the LNSRCTL (STOP and then start it)



3. Now ping the target database again.



EXAMPLE:

C:\orcl>tnsping earth


TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 26-FEB-2008 16:28:11
Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:C:\orcl\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.16.12.35)(PORT = 1522))) (CONNECT_DATA = (SID = EARTH) (SERVER = DEDICATED) (SERVICE_NAME = EARTH)))

TNS-12541: TNS:no listener

C:\myorcl>lsnrctl

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 26-FEB-2008 16:24:19
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Welcome to LSNRCTL, type "help" for information.


LSNRCTL> stop



Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))The command completed successfully



LSNRCTL> start



Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - ProductionSystem parameter file is C:\myorcl\network\admin\listener.oraLog messages written to C:\myorcl\network\log\listener.logListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.16.12.35)(PORT=1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - ProductionStart Date 26-FEB-2008 16:24:33

Uptime 0 days 0 hr. 0 min. 2 sec

Trace Level off

Security OFF

SNMP OFF

Listener Parameter File C:\myorcl\network\admin\listener.oraListener

Log File C:\myorcl\network\log\listener.logListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.16.12.35)(PORT=1522)))Services Summary...Service "EARTH" has 1 instance(s). Instance "EARTH", status UNKNOWN, has 1 handler(s) for this service...Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully



C:\orcl>tnsping earth

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 26-FEB-2008 16:35:18
Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:C:\orcl\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.16.12.35)(PORT = 1522))) (CONNECT_DATA = (SID = EARTH) (SERVER = DEDICATED) (SERVICE_NAME = EARTH)))

OK (20 msec)

Sunday, February 24, 2008

Database House Keeping Activities

Thursday, February 14, 2008

Priority Checking - For database Hanging

Assumptions :The database is running in one of the unix platform.

Check for locks using standrad scripts as described below:

1. Check if numerous locks exist in the database(You can use some scripts _ I will try to put in this blog soon)
2. Look for TX locks/ Kill the processes which are form / CM long running sessions/ Run -away processes.
3. If SS- Enqueue/ ST - Enqueue , then we need to dig further , which process is creating
4. If oracle background processes , then try to find whcih one.
2. Check for the services running in that node and may consider moving some of the services from that node to other nodes which are a bit lesser burdened.

3. Do a top -h for looking which process (oracle/ user) is taking much cpu.
$ top -h
or
$ glance

4. Open a SR with oracle and upload the following files:
1. alertlog
2.Hang Analyze (means at node level)
3.CRS oswatcher files
4. Systemstate Dumps
5.Processstack
6.Errorstack at level 3 / or at level 1
7.Hang analyze Global
8.Processstack for the background processes for oracle background processes.
9.AWR report ( Execute $ORACLE_HOME/rdbms/admin/awrrpt.sql) -In 10g its awr , in 9i - it was statspack)

I will discuss more on each with individual sessions in coming days.

Wednesday, February 13, 2008

Advantage of RAC

Oracle Real Application clusters allows multiple instances to access a single database. Instance running on multiple server nodes access the common set of datafiles compromising a single database.

Advantages:

1. High availability—Provide continuous uptime for database applications
2. On-demand scalability—Expand capacity by simply adding servers to your cluster
3. Lower computing costs—Use low-cost commodity hardware and reduce cost of downtime
4. High performance—Runs faster than the fastest mainframe
5. Grid computing—Oracle RAC is the foundation for grid computing

RAID

RAID is an acronym for Redundant Arrays of Inexpensive Disks. These provide a mechanism for load balancing and securing your data across multiple disks.

There are three terminology related to RAID, that is

1. Striping : It is the process of breaking data into pieces and distributing across multiple disk that supports a logical volume. It yeilds better Performance.

2. Mirroring : Its the process of writting the same data simultaniously to another member of the same volume.Mirroring provides protection for data by writting exactly same data to other members in the same volume.

3. Parity : Its the error checking.

Types of RAID

1.Host Based RAID: RAID can be software based.its imposes a small overhead as it consumes memory, I/O bandwidth, and CPU space on the host where it is implemented.

2.Embedded Controller based RAID : RAId implemented by hardware is in the form of micro code present in dedicated disk controler module that connect to the host this type of RAID is know as Embedded Controller based RAID .

3.Bridge based RAID : RAID can also be implemented using controllers that are external to the host where it is implemented. This type of RAID are known as bridge based RAID.

RAID Levels:
Initially RAID was a simple method of logically joining two or more disks.RAID levels usually range from 0 to 7.

RAID 0: Plain striping

RAID 1: plain mirroring

RAID 0+1 / 1+0 : combination of 0 and 1- striping and then mirroring or vice versa

RAID 2: Early implementation of striping with parity

RAID 3 : Striping with bit/byte level parity,dedicated parity disk

RAID 4: Striping with block level parity

RAID 5: Striping with block level parity, distributed parity across the number of disks in volume

RAID 6: Striping with block level multidimensional parity

RAID 7: Same as RAID 3 but with better asynchronous capability for read and write.

RAID s : EMC's implementation of RAID 3/5

Auto RAID : HP's automatic RAID technilogy.

Tuesday, February 12, 2008

Pros and Cons of Indexing


Pros


  • Its easy to use/implement and provides immediate value
  • It can be used to speed up existing applications without changing any of their logic or queries. Many orders of magnitude query improvement may be observed.
  • It can be used to precompute complex values without using a trigger
  • Can be created either as B*Tree or bitmap index
  • Index can be built on an arithmetic expression or expression containing PL/SQL, package functions, C callout or SQL built-in functions
  • Optimizer can estimate selectivity more accurately if the expressions are materialized in a function-based index. Range scan can be used for queries with expression in where clause and has index build on the expression used.
  • Provides efficient linguistic collation to use NLS sort index
  • Indexes can be created on object columns and REF columns by using methods defined for the object.
Cons

  • You cannot direct path load the table with a function based index if that function was user written and requires the SQL ENGIRE. That means you cannot direct path load into a table indexed using my_soundex(x), but you could if it had indexed upper(x).
  • It will affect the performance of inserts and updates. (Remember, you insert a row once, you query it thousands of times.)
  • If the function you are indexing is a user written function and it returns a string, you may have to expose a view for the end users to use.





























Monday, February 11, 2008

Difference Between Export and Import






Ten Best Things About Indexing

To day I wanna share all that I know about indexing for beginners.

1. An index is a performance-tuning method of allowing faster retrieval of records.

2. It's easy and provides immediate value, because it's just a CREATE INDEX statement

3.It can be used to speed up existing applications without changing any of their logic or queries

4. It can be used to supply additional functionality to applications with very little cost

5. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

6.Because I/Os are expensive operations on a computer system, table scans are very expensive. Reducing the amount of data that is read from the disk is desirable. By reducing the amount of data that is read, you reduce system overhead. An index improves your performance by knowing exactly where the data is on disk and avoiding costly table scans, thus reducing I/O overhead.

6. Indexes are transparent to the end-user application.

7.Oracle automatically maintains the indexes during a DML operation on the table. Oracle Optimizer chooses the suitable index during a SQL SELECT operation.

8.Oracle manages the index for you, there are no additional commands you need to use to keep the index sync'd with the table.

9. It can allow the SQL optimizer to avoid large chunks of data blocks and narrow down the candidate blocks very quickly taking a query that takes minutes to run to less than a second to run.

10. an index is one of the best features of an RDBMS and will solve so many of your performance and scalability issues

Table & Index Partition

Partition:
Decompose a table or index into smaller, more manageable pieces, called partitions.

Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.

Partitioning is useful for many different types of applications, particularly applications that manage large volumes of data. OLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from performance and manageability.
Partition Key

Each row in a partitioned table is unambiguously assigned to a single partition. The partition key is a set of one or more columns that determines the partition for each row. Oracle9i automatically directs insert, update, and delete operations to the appropriate partition through the use of the partition key.
A partition key:
Consists of an ordered list of 1 to 16 columns
Cannot contain a LEVEL, ROWID, or MLSLABEL pseudocolumn or a column of type ROWID
Can contain columns that are NULLable

Partitioning Methods
There are mainly four types of partitioning :

1.Range Partitioning
2.List Partitioning
3.Hash Partitioning
4.Composite Partitioning

You can get the partition related information form the views:
dba_tables
dba_tab_partitions

Task: Partition the Scott.emp table using SAL (range partitioning method ):

SQL> SELECT partitioned FROM user_tables WHERE table_name = 'EMP';
PAR
----
NO

SQL> CREATE TABLE emp_range
(EMPNO NUMBER(4) NOT NULL,
ENAME vARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2))
PARTITION BY RANGE (SAL)
(PARTITION emp_1000 VALUES LESS THAN('1000'),
PARTITION emp_2000 VALUES LESS THAN('2000'),
PARTITION emp_6000 VALUES LESS THAN('6000'));

SQL> insert into emp_range (select * from emp) ;
14 rows created;

SQL> drop table emp;
Table dropped.

SQL> alter table emp_range rename to emp ;
Table altered.

SQL> SELECT partitioned FROM user_tables WHERE table_name = 'EMP';
PAR
----
YES

SQL> SELECT * FROM emp;

SQl> SELECT * FROM emp PARTITION(emp_1000);

SQL> SELECT partition_name FROM dba_tab_partitions WHERE table_name = 'EMP';
PARTITION_NAME
------------------------------
EMP_1000
EMP_2000
EMP_6000

SQL> ALTER TABLE "SCOTT"."EMP" DROP PARTITION "EMP_6000";

SQL> ALTER TABLE "SCOTT"."EMP" ADD PARTITION "EMP_MAX" VALUES LESS THAN (MAXVALUE) TABLESPACE "SYSTEM" ;

SQL> SELECT partition_name FROM dba_tab_partitions WHERE table_name = 'EMP';
PARTITION_NAME
------------------------------
EMP_1000
EMP_2000
EMP_MAX

SQL> ALTER TABLE "DIP"."FOOD_RANGE" RENAME PARTITION "FOOD_4" TO "FOOD_3"



Range partition Example:
CREATE TABLE sales_range (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE)PARTITION BY RANGE(sales_date) (PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')));
List partition:
CREATE TABLE sales_list(salesman_id NUMBER(5), salesman_name VARCHAR2(30),sales_state VARCHAR2(20),sales_amount NUMBER(10), sales_date DATE)PARTITION BY LIST(sales_state)(PARTITION sales_west VALUES('California', 'Hawaii'),PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),PARTITION sales_central VALUES('Texas', 'Illinois')PARTITION sales_other VALUES(DEFAULT));

Hash partition Example:
CREATE TABLE sales_hash(salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), week_no NUMBER(2)) PARTITION BY HASH(salesman_id) PARTITIONS 4 STORE IN (data1, data2, data3, data4);

Composite Partitioning Range-Hash Example
CREATE TABLE sales_composite (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE)PARTITION BY RANGE(sales_date) SUBPARTITION BY HASH(salesman_id)SUBPARTITION TEMPLATE(SUBPARTITION sp1 TABLESPACE data1,SUBPARTITION sp2 TABLESPACE data2,SUBPARTITION sp3 TABLESPACE data3,SUBPARTITION sp4 TABLESPACE data4)(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')) PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')) PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')) PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')) PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','DD/MM/YYYY')));

Composite Partitioning Range-List Example
CREATE TABLE bimonthly_regional_sales(deptno NUMBER, item_no VARCHAR2(20), txn_date DATE, txn_amount NUMBER, state VARCHAR2(2))PARTITION BY RANGE (txn_date)SUBPARTITION BY LIST (state)SUBPARTITION TEMPLATE(
SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE ts1,SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2,SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE ts3)
( PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('1-MAR-2000','DD-MON-YYYY')), PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-MAY-2000','DD-MON-YYYY')), PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('1-JUL-2000','DD-MON-YYYY')) );

Partitioned Indexes

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability.
They can either be partitioned independently (global indexes)
or
automatically linked to a table's partitioning method (local indexes).

Example of a Local Index Creation
CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL;

Example of a Global Index Creation
CREATE INDEX employees_global_idx ON employees(employee_id);

Example of a Global Partitioned Index Creation
CREATE INDEX employees_global_part_idx ON employees(employee_id)
GLOBAL PARTITION BY RANGE(employee_id)
(PARTITION p1 VALUES LESS THAN(5000),
PARTITION p2 VALUES LESS THAN(MAXVALUE));

How to partition a non-partitioned table

You can partition a non-partitioned table three different ways:

A. Export/Import method
B. Partition exchange method
C. Insert with a sub-query method

Any of these 3 methods will create a partitioned table from

an existing non-partitioned table. Here we are considering that EMP is a non-partitioned table.

A. Export/import method

1) Export non-partitioned EMP table:
exp username/password tables=emp file=emp_exp.dmp log=emp_exp.log

2) Drop the table:
drop table emp;

3) Recreate EMP table with partitions:
create table EMP (empo number(3), name varchar2(15)) partition by range (qty) (partition p1 values less than (501), partition p2 values less than (maxvalue));

4) Import the table with ignore=y:
imp usr/pswd file=exp.dmp ignore=y The ignore=y causes the import to skip the table creation and continues to load all rows.

B. Insert with a subquery method

1) Create a partitioned table:
create table EMP_PART (empo number(3), name varchar2(15)) partition by range (qty) (partition p1 values less than (501), partition p2 values less than (maxvalue));

2) Insert into the partitioned table with a subquery from the non-partitioned table:
insert into EMP_PRAT (qty, name) select * from EMP Note: EMP is a non-partition table.

3) If you want the partitioned table to have the same name as the original table, then drop the original table and rename the new table:
drop table EMP; alter table EMP_PART rename to EMP;

C. Partition Exchange method
ALTER TABLE EXCHANGE PARTITION can be used to convert a partition (or subpartition) into a non-partitioned table and a non-partitioned table into a partition (or subpartition) of a partitioned table by exchanging their data and index segments.

1) Create table dummy_t as select with the required partitions
2) Alter table EXCHANGE partition with ;

Example
-------
SQL> CREATE TABLE p_emp 2 (sal NUMBER(7,2)) 3 PARTITION BY RANGE(sal) 4 (partition emp_p1 VALUES LESS THAN (2000), 5 partition emp_p2 VALUES LESS THAN (4000));
Table created.

SQL> SELECT * FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL --------- ---------- --------- --------- --------- --------- 7369 SMITH CLERK 7902 17-DEC-80 800 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 7521 WARD SALESMAN 7698 22-FEB-81 1250 7566 JONES MANAGER 7839 02-APR-81 2975 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 7698 BLAKE MANAGER 7839 01-MAY-81 2850 7782 CLARK MANAGER 7839 09-JUN-81 2450 7788 SCOTT ANALYST 7566 19-APR-87 3000 7839 KING PRESIDENT 17-NOV-81 5000 7844 TURNER SALESMAN 7698 08-SEP-81 1500 7876 ADAMS CLERK 7788 23-MAY-87 1100 7900 JAMES CLERK 7698 03-DEC-81 950 7902 FORD ANALYST 7566 03-DEC-81 3000 7934 MILLER CLERK 7782 23-JAN-82 1300
14 rows selected.

SQL> CREATE TABLE dummy_y as SELECT sal FROM emp WHERE sal<2000;> CREATE TABLE dummy_z as SELECT sal FROM emp WHERE sal BETWEEN 2000 AND 3999;
Table created.

SQL> alter table p_emp exchange partition emp_p1 with table dummy_y;
Table altered.

SQL> alter table p_emp exchange partition emp_p2 with table dummy_z;
Table altered

Saturday, February 9, 2008

What Backup policies your Company Need?

A brief overview of Backup policy Matrics and how you can take advantage of latest Tools and Technologies for safeguarding your data

Ten Best Recovery Scenarios I have ever faced!!!!

Today I am sharing the most difficult and challenging assignments I have experienced in my past jobs - The data recovery scenarios.

You know what , these are the best times in my job profile I would like to tell you today.

Data , the most important and valuable data of any organization when need to be required from your back up sets , then even experienced dbas find it most difficult to get what the companies want.

Friday, February 8, 2008

RMAN

Recovery Manager is Oracle’s utility to manage the backup, and more importantly the recovery, of the database. It eliminates operational complexity while providing superior performance and availability of the database. Recovery Manager debuted with Oracle8 to provide DBAs an integrated backup and recovery solution. Recovery Manager determines the most efficient method of executing the requested backup, restore, or recovery operation and then executes these operations in concert with the Oracle database server. Recovery Manager and the server automatically identify modifications to the structure of the database and dynamically adjust the required operation to adapt to the changes.

RMAN Configuration commands :::

RMAN> connect target sys/justchill

connected to target database: MARS (DBID=617151761)
using target database controlfile instead of recovery catalog

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;

new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\orcl\rman\ora_cf%F'
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'c:\orcl\rman\ora_df%t_s%s_s%p';
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'c:\orcl\rman\snp123.ora' ;

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\orcl\rman\ora_cf%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'c:\orcl\rman\ora_df%t_s%s_s%p';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORCL\RMAN\SNP123.ORA';


RMAN BACKUP Commands :------------

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE; ////Incremental Backups
RMAN> BACKUP DATABASE PLUS ARCHIVELOG; ////Backup the Database
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL; /// Testing Backups
RMAN> report schema; ///show all schemas
RMAN> BACKUP TABLESPACE EXAMPLE; /// backup particular tablespace
RMAN> BACKUP DATAFILE 5; /// backup particular datafile
RMAN> BACKUP DATABASE NOT BACKED UP SINCE TIME 'SYSDATE-1'; /// upto time

Example:
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE; ////Incremental Backups Starting backup at 08-FEB-08 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=15 devtype=DISK channel ORA_DISK_1: starting incremental level 0 datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001 name=C:\ORCL\P123\VITALDATA\SYSTEM01.DBF input datafile fno=00002 name=C:\ORCL\P123\VITALDATA\UNDOTBS01.DBF input datafile fno=00004 name=C:\ORCL\P123\VITALDATA\EXAMPLE01.DBF input datafile fno=00009 name=C:\ORCL\P123\VITALDATA\XDB01.DBF input datafile fno=00005 name=C:\ORCL\P123\VITALDATA\INDX01.DBF input datafile fno=00008 name=C:\ORCL\P123\VITALDATA\USERS01.DBF input datafile fno=00003 name=C:\ORCL\P123\VITALDATA\DRSYS01.DBF input datafile fno=00006 name=C:\ORCL\P123\VITALDATA\ODM01.DBF input datafile fno=00007 name=C:\ORCL\P123\VITALDATA\TOOLS01.DBF input datafile fno=00010 name=C:\ORCL\P123\VITALDATA\DATA.DBF input datafile fno=00011 name=C:\ORCL\P123\VITALDATA\TS_STH.DBF channel ORA_DISK_1: starting piece 1 at 08-FEB-08 channel ORA_DISK_1: finished piece 1 at 08-FEB-08 piece handle=C:\ORCL\RMAN\ORA_DF646157625_S2_S1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55 Finished backup at 08-FEB-08 Starting Control File and SPFILE Autobackup at 08-FEB-08 piece handle=C:\ORCL\RMAN\ORA_CFC-617151761-20080208-00 comment=NONE Finished Control File and SPFILE Autobackup at 08-FEB-08

RMAN Administrative Commands:-------

RMAN> REPORT NEED BACKUP; ///files need to be backed up based on the configured retention policy

RMAN> REPORT OBSOLETE; /// to view what backups are obsolete and delete them.

RMAN> DELETE OBSOLETE; //// to view what backups are obsolete and delete them.

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL; //to check for corruption or verify that database files are in the correct location

RMAN> REPORT UNRECOVERABLE; //report any files that have not been backed up since the last nolog operation

RMAN> DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1'; //run this command to delete the archive logs that are older then yesterday

RMAN> CROSSCHECK BACKUP OF DATABASE;

RMAN> LIST BACKUP OF DATAFILE 4; /// view the backups for datafile 4

RMAN> LIST BACKUP OF CONTROLFILE; /// view the backups for control file

RMAN Complete Recovery :------
When you perform complete recovery, you recover the backups to the current SCN. You can either recover the whole database at once or recover individual tablespaces or datafiles. Because you do not have to open the database with the RESETLOGS option after complete recovery as you do after incomplete recovery, you have the option of recovering some datafiles at one time and the remaining datafiles later

1.Recovering Datafiles :::
If u know the file corrupted then use the command below..
for example - DATAFILE 5 C:\orcl\p123\vitaldata\EXAMPLE01.BDF corrupted

RMAN> CONNECT TARGET SYS/justchill
RMAN> RESTORE DATAFILE 5;
RMAN> RECOVER DATAFILE 5;
RMAN> SQL 'alter tablespace example online';

2. Recovering Database :::
The database must be in MOUNT mode to restore and recover with RMAN.

RMAN> CONNECT TARGET SYS/justchill
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

3. Recovering Tablespaces :::

RMAN> CONNECT TARGET SYS/justchill
RMAN> RESTORE TABLESPACE EXAMPLE;
RMAN> RECOVER TABLESPACE EXAMPLE;
RMAN> SQL 'ALTER TABLESPACE EXAMPLE ONLINE';


RMAN InComplete Recovery :------
Incomplete recovery uses a backup to produce a noncurrent version of the database. In other words, you do not apply all of the redo records generated after the most recent backup. You usually perform incomplete recovery of the whole database in the following situations:

1.Media failure destroys some or all of the online redo logs.

2. A user error causes data loss, for example, a user inadvertently drops a table.

3.You cannot perform complete recovery because an archived redo log is missing.

4.You lose your current control file and must use a backup control file to open the database.

To perform incomplete media recovery, you must restore all datafiles from backups created prior to the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes. The RESETLOGS operation creates a new incarnation of the database. in other words, a database with a new stream of log sequence numbers starting with log sequence 1.

The following commands are issued within RMAN, using the database id(DBID) returned from
the query. You will receive an error message at the end of recover due to the loss of the
online redo logs

RMAN> SET DBID 1003121815;
RMAN> CONNECT TARGET SYS/ORACLE@ORCL.WORLD;
RMAN> STARTUP NOMOUNT;
RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'C:\ORCl\RMAN\ora_cf%F';
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;


Since the online logs were lost, complete recovery is not possible. Open the database
with resetlogs to continue.

RMAN> ALTER DATABASE OPEN RESETLOGS;


To monitor the RMAN @monitorjob type this in SQL promt.

Thursday, February 7, 2008

Tablespaces

Tablespaces

A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace.

Each table, index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table's or index' data is stored.

There are three types of tablespaces in Oracle:
A.Permanent tablespaces
B.Undo tablespaces
C.Temporary tablespaces

Creating a tablespace
A tablespace is created with the create tablespace sql command.

----Permanent tablespace
create tablespace ts_something logging datafile '/dbf1/ts_sth.dbf' size 32m
autoextend on next 32m maxsize 2048m extent management local;

create tablespace data datafile '/home/oracle/databases/ora10/data.dbf'size 10Mautoextend on maxsize 200M extent management local uniform size 64K;

----Temporary tablespace
create temporary tablespace temp_mtr tempfile '/dbf1/mtr_temp01.dbf' size 32m
autoextend on next 32m maxsize 2048m extent management local;
--- Undo tablespace
create undotablespace ts_undo datafile '/dbf/undo.dbf' size 100M;

Dropping a tablespace
Dropping a tablespace is a structural change to the database that requires a backup.
The extent size of the objects in the tablespace are subject to the tablespace's minimum extent size.

Quotas on tablespaces
Users can have (space) quotas on tablespaces. This is a means to limit how much space a user uses on a tablespace. This quota can be set using alter user quota.

Tablespace groups
This is a feature that is available with Oracle 10g.

Assigning a tablespace to a tablespace group:

SQL>alter tablespace ts_user tablespace group ts_grp_user;


Renaming Tablespaces
This is a feature that is available with Oracle 10g and can be useful in transportable tablespace scenarios.

SQL>alter tablespace ts_user rename to ts_user_01;

The system and sysaux tablespace cannot be renamed, though. But that will not be much of a problem, However, read only tablespaces can be renamed.
After renaming a tablespace, the controlfiles should immediatly be backed up.

The system tablespace

Every Oracle database contains a tablespace named SYSTEM that Oracle creates automatically when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.
A small database might need only the SYSTEM tablespace; however, it is recommended that you create at least one additional tablespace to store user data separate from data dictionary information. This allows you more flexibility in various database administration operations and can reduce contention among dictionary objects and schema objects for the same datafiles.

Note: The SYSTEM tablespace must always be kept online

The sysaux tablespace
The sysaux tablespace is new with Oracle 10g . It is used to store database components that were stored in the system tablespace in prior releases of the database. Also, the tablespaces that were needed for RMAN's recovery catalog, for Ultra Search, for Data Mining, for XDP and for OLAP are going to sysaux with 10g. Additionally, it is the place where automatic workload repository stores its information.

occupants
An occupant is a set of (application-) tables within the sysaux tablespace. The occupants can be viewed with the v$sysaux_occupants

Temporary tablespace
A temporary tablespace is a tablespace that can only be used for sort segments. No permanent objects can reside in a temporary tablespace. Sort segments are used when a segment is shared by multiple sort operations. One sort segment exists in every instance that performs a sort operation in a given tablespace.
Temporary tablespaces provide performance improvements when you have multiple sorts that are too large to fit into memory. The sort segment of a given temporary tablespace is created at the time of the first sort operation. The sort segment grows by allocating extents until the segment size is equal to or greater than the total storage demands of all of the active sorts running on that instance

Transportable tablespaces
Transportable tablespaces can quickly be moved across different platforms (with Oracle 10g)
This allows databases to be migrated at almost the speed of a file transfer.
For example, data providers can deploy their data to custumers that run Oracle on different platforms more easily. Also, usually, data marts are on different platforms as the data warehouse. With transportable tablespaces, the data can be 'copied' from the data warehouse to the data mart more efficiently.

Transportable tablespaces require the export transportable tablespaces option.

RMAN> convert tablespace ts_data, ts_idx to platform 'Microsoft Windows NT' format '/temp/%U';

Bigfile tablespaces
This is a feature of Oracle 10g. A bigfile tablespace contains only one datafile (or tempfile) which can be as big as 2^32 (=4GB) blocks
create bigfile tablespace beeeg_ts data file '/o1/dat/beeeg.dbf' size 2T
Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management (which is the default setting since Oracle 9i). There are two exceptions: locally managed undo and temporary tablespaces can be bigfile tablespaces, even though their segments are manually managed.
The system and sysauxtablespace cannot be created as bigfile tablespace.
Bigfile tablespaces should be used with automatic storage managemet, or other logical volume managers that support dynamically extensible logical volumes, striping and RAID.

Smallfile tablespaces
A smallfile tablespace is a traditional tablespace that can contain up to 1022 datafiles.

Default tablespaces
An Oracle database can be configured with a default tablespace and a default temporary tablespace. These are used for users who are not configured with default tablespaces or default temporary tablespaces .
Displaying tablespaces
The dba_tablespaces view displays all tablespaces along with the respective relevant information.
Space management
Oracle maintains extents for a tablespace. There are two different methods for Oracle to keep track of free and used (occupied) extents:
Dictionary managed tablespace
Locally managed tablespace
A 'bitmap' is stored within the tablespace. Each bit within this bitmap determines if a corresponding extent in the tablespace is free or used. The extent sizes are either uniform or autoallocate.

resulting in the following benefits:

1.Improved concurrency and speed of space operations, because space allocations and deallocations predominantly modify locally managed resources (bitmaps stored in header files) rather than requiring centrally managed resources such as enqueues

2.Improved performance, because recursive operations that are sometimes required during dictionary-managed space allocation are eliminated
Readable standby databases are allowed, because locally managed temporary tablespaces (used, for example, for sorts) are locally managed and thus do not generate any undo or redo.

3.Simplified space allocation--when the AUTOALLOCATE clause is specified, appropriate extent size is automatically selected

4.Reduced user reliance on the data dictionary because necessary information is stored in file headers and bitmap blocks

EXTENT MANAGEMENT LOCAL AUTOALLOCATE
EXTENT MANAGEMENT LOCAL UNIFORM

Segment Space Management in Locally Managed Tablespaces

A.MANUAL

Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows.

MANUAL is the default.

B. AUTO

This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Dictionary managed tablespaces
For dictionary-managed tablespaces, Oracle updates the appropriate tables in the data dictionary whenever an extent is allocated, or freed for reuse. Extents are allocated according to the following storage parameters initial next pctincrease .The information about used and free extents is stored in the dictionary.

CREATE TABLESPACE tbsb DATAFILE '/u02/oracle/data/tbsa01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

State of tablespaces
A tablespace is either online (ready to be accessed) or offline (not accessible).
A tablespace becomes offline either
when the DBA explicitely makes the tablespace offline, or
when an error occures while Oracle accesses a datafile within the tablespace.
Offline tablespaces cannot be transported to other databases.

Read Only Tablespaces

The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Oracle never updates the files of a read-only tablespace, and therefore the files can reside on read-only media, such as CD ROMs or WORM drives.

Whenever you create a new tablespace, it is always created as read-write. The READ ONLY option of the ALTER TABLESPACE command allows you to change the tablespace to read-only, making all of its associated datafiles read-only as well. You can then use the READ WRITE option to make a read-only tablespace writeable again.
Read-only tablespaces cannot be modified. Therefore, they do not need repeated backup. Also, should you need to recover your database, you do not need to recover any read-only tablespaces, because they could not have been modified.
You can drop items, such as tables and indexes, from a read-only tablespace, just as you can drop items from an offline tablespace. However, you cannot create or alter objects in a read-only tablespace.

About RESETLOGS Option

The RESETLOGS option is always required after incomplete media recovery or recovery using a backup control file. Resetting the redo log does the following:


1. Archives the current online redo logs (if they are accessible) and then erases the contents of the online redo logs and resets the log sequence number to 1. For example, if the current online redo logs are sequence 1000 and 1001 when you open RESETLOGS, then the database archives logs 1000 and 1001 and then resets the online logs to sequence 1 and 2.


2.Creates the online redo log files if they do not currently exist.


3.Reinitializes the control file metadata about online redo logs and redo threads.
Updates all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.


Because the database will not apply an archived log to a datafile unless the RESETLOGS SCN and time stamps match, the RESETLOGS prevents you from corrupting datafiles with archived logs that are not from direct parent incarnations of the current incarnation.

In prior releases, it was recommended that you back up the database immediately after the RESETLOGS. Because you can now easily recover a pre-RESETLOGS backup like any other backup, making a new database backup is optional. In order to perform recovery through resetlogs you must have all archived logs generated since the last backup and at least one control file (current, backup, or created).

Tuesday, February 5, 2008

Roles and Priveleges in Oracle Database!!!

Creating a Role
To create a role, you must have CREATE ROLE system privileges.

The syntax for creating a role is:
CREATE ROLE role_name[ NOT IDENTIFIED IDENTIFIED {BY password USING [schema.] package EXTERNALLY GLOBALLY } ;

For example:
CREATE ROLE test_role;

This first example creates a role called test_role.

CREATE ROLE test_roleIDENTIFIED BY test123;

This second example creates the same role called test_role, but now it is password protected with the password of test123.

Grant Privileges (on Tables) to Roles

Privilege Description
Select Ability to query the table with a select statement.
Insert Ability to add new rows to the table with the insert statement.
Update Ability to update rows in the table with the update statement.
Delete Ability to delete rows from the table with the delete statement.
References Ability to create a constraint that refers to the table.
Alter Ability to change the table definition with the alter table statement.
Index Ability to create an index on the table with the create index statement.

The syntax for granting privileges on a table is:
grant privileges on object to role_name

For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a role named test_role, you would execute the following statement:

grant select, insert, update, delete on suppliers to test_role;

You can also use the all keyword to indicate that you wish all permissions to be granted. For example:

grant all on suppliers to test_role;

Revoke Privileges (on Tables) to Roles
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.

The syntax for revoking privileges on a table is:

revoke privileges on object from role_name;

Dropping a Role
It is also possible to drop a role. The syntax for dropping a role is:

DROP ROLE role_name;

For example:
DROP ROLE test_role;

Myths and Realities about Recovery

Last septmber I was asked if we a complete recovery is possible if we donot have a pcf(present control file) and on lin redo log file. - Its a myth - We cannot do a complete recovery with out a pcf or olrdf(online redo log file)

Why ?

Sunday, February 3, 2008

EXP & IMP Excersise

Here we ll see some examples about Export and Import .exp and imp are the executables that allow to make exports and imports of data objects (such as tables). With the exp and imp we used to take backup of data structures and data from where logical backups can be made.Its a light weight backup process.

Before taking the export first check below :

1.Select count(1) from dba_objects where owner='&OWNERNAME';

2.select object_type,object_name from dba_objects where owner=upper('&OWNERNAME');

3.select the users credential if the necessary priveleges are there to export and impot
EXP_FULL_DATABASE
IMP_FULL_DATABASE


4.Go to the Source Directory where you will put the export dump

EXP example:
C:\orcl\myExportDump>exp system/budhiya buffer=200000 file=export_scott.dmp compress=n owner=SCOTT log=scott.log

IMP example:
C:\orcl\myExportDump>imp system/budhiya buffer=200000 commit=y file=export_scott.dmp ignore=y fromuser=SCOTT touser=RAMA log=imp.log rows=y grants=Y INDEXES=y

EXP& IMP only tables example
C:\orcl\myExportDump>exp system/budhiya tables=dip.food file=export_scott.dmp compress=n

C:\orcl\myExportDump>imp system/budhiya buffer=200000 commit=y file=export_scott.dmp ignore=y fromuser=RAMA touser=DIP log=imp.log rows=y grants=Y INDEXES=y


For more info refer :
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch01.htm#1006128

Friday, February 1, 2008

Change the Database Name

In situations we need to change the database name , for example when we do a clone from production server to a development server , we rename the datbase name at the development site.

Let me give an example to you : My company's prod server is BNFD. At the develoment site I have restore a copy of the database and it is BNFD.But we want to rename it to a development server say new name would be MARS.

Theoritical Steps:

1. Check the database Name.

2. Take a Trace of the control file.

3.Edit the trace and change the database Name. - Create the controlfile script

4.Change the db_name and instance_name in the init.ora file.

5.Move or remove the binary copies from multiplexed Locations.

6. At NOMOUNT state create the control file.

7.Open the Database - And Check the database name to confirm.

SQL> select name from v$database;

NAME
---------
BNFD

SQL> show parameters dump_dest ;

NAME TYPE VALUE
--------- -------- -----------
background_dump_dest string C:\orcl\admin\BNFD\bdump
core_dump_dest string C:\orcl\admin\BNFD\cdump
user_dump_dest string C:\orcl\admin\BNFD\udump


Step 1: Take a Backup trace of Control file
SQL> alter database backup controlfile to trace;
This ll create the trace file of a control file in UDUMP


The instance needs to be shut down. SYSDBA privileges will be needed.
SQL> shutdown immediate ;


STEP :2Modify (and optionally rename) the created trace file:
1.Find the line reading # Set #2. RESETLOGS case
2.Remove all lines above this line.
3.Change the line containing the database name from CREATE CONTROLFILE REUSE DATABASE "ORA9" RESETLOGS NOARCHIVELOG
toCREATE CONTROLFILE SET DATABASE "CAT" RESETLOGS NOARCHIVELOG
Note, in my case, the database is running in noarchivelog mode.
The corresponding line reads ARCHIVELOG otherwise.
4.Remove the line reading RECOVER DATABASE USING BACKUP CONTROLFILE.
5.Remove lines starting with #.

It looks then something like:
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CAT" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 454
LOGFILE
GROUP 1 '/home/oracle/databases/cat/redo1.ora' SIZE 100M,
GROUP 2 '/home/oracle/databases/cat/redo2.ora' SIZE 100M,
GROUP 3 '/home/oracle/databases/cat/redo3.ora' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/home/oracle/databases/cat/system.dbf',
'/home/oracle/databases/cat/undo.dbf',
'/home/oracle/databases/cat/data.dbf'
CHARACTER SET WE8ISO8859P1
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/OraHome1/databases/ora9/temp.dbf'
SIZE 104857600 REUSE AUTOEXTEND OFF;

in my case I renamed the file to newcontrol123.sql

STEP3: Move the all the previous Control file to otherlocation
Move the controlfiles away so that they can be re-created..
$ mv ctl_1.ora ctl_1.ora.moved
$ mv ctl_2.ora ctl_2.ora.moved
$ mv ctl_3.ora ctl_3.ora.moved

STEP 4:Change the Database name in Init.ora File
The database name must be entered (changed) in the initSID.ora:
initcat.ora
db_name = CAT

Step 5 : Connect as sysdba and RUN the new controlfile

sqlplus "/ as sysdba"

SQL> @/tmp/newcontrol

SQL> startup open resetlogs ;

SQL> select name from v$database;
NAME
---------
CAT