Archive for May, 2012

rman catalog in Oracle Database

Let’s start with Catalog Database, connect to your desired catalog database as sysdba. Create Tablespace, user and grant appropriate privelleges as shown.

SQL> create tablespace cat_tablespace datafile 'G:\oracle\product\10.2.0\oradata\catdb\cat_datafile.dbf' size 200M autoextend on;

Tablespace created.

SQL> create user rcat identified by rcat default tablespace cat_tablespace temporary tablespace temp quota unlimited on cat_tablespace;

User created.

SQL> grant connect, create session, resource, recovery_catalog_owner to rcat;

Grant succeeded.

SQL> conn rcat/rcat@catdb;

When we use rman without catalog Database, it uses target Database’s Control File as rman repository. But if we use rman with catalog Database, it uses catalog database along with target database control file as rman repository. Those following step by step command shows how to register a target Database in catalog. Continue reading

Oracle External Table

This is the simplest demonstration of External Table in ORACLE Database.

SQL> select tablespace_name from dba_tablespaces;


SQL> create user ext_user identified by ext default tablespace users temporary tablespace temp quota unlimited on users;

User created.

SQL> grant connect,resource,create session  to ext_user;

Grant succeeded.

SQL> create or replace directory external as 'F:\khanExternal';

Directory created.

SQL> grant read,write on directory external to exp_full_database,imp_full_database,system;

Grant succeeded.

Continue reading

Oracle Database Link

Oracle Database Links are of two types, Private or Public. Private is the default one. let’s start with connecting with database, in this Example, My two Database are on same Machine. For creating Database link, you need to have TNS entry for remote database.

C:\Documents and Settings\A Rahim Khan>set oracle_sid = catdb;

C:\Documents and Settings\A Rahim Khan>sqlplus / as sysdba;

SQL*Plus: Release - Production on Wed May 2 10:52:28 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release - Production
With the Partitioning, OLAP and Data Mining options

SQL> select instance_name from v$instance;


SQL> create user hr_link identified by hr;

User created.

SQL> grant connect,resource,CREATE DATABASE LINK hr_link;

Grant succeeded.

Continue reading

Control File Backup Script to Trace

To generate SQL Script for Control Files, use this SQL Command.

SQL> alter database backup controlfile to trace;

Database altered.

This will be written in Trace file, Default Location is given below. Find out the latest trace file and copy it to some where else then open it using Notepad or Wordpad.


Continue reading

Oracle Database Control File Multiplexing

First of all, keep a backup of your existing Control File on other disk. If, you don’t know location of existing Control File then use this following query.

SQL> select name from v$controlfile;


Continue reading

Oracle Redo Log Files Multiplexing

First of all, let’s see output from v$log and v$logfile.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                                   IS_
    ---------------------------------------------------------------------------------------------------------- ---------
         3 STALE   ONLINE  G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG                                         NO
         2         ONLINE  G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG					     NO
         1         ONLINE  G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG                                         NO

SQL> select * from v$log;

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         11   52428800          1 YES INACTIVE                799976 01-MAY-12
         2          1         12   52428800          1 NO  CURRENT                 800427 01-MAY-12
         3          1         10   52428800          1 YES INACTIVE                779491 29-APR-12

As shown above, Group 2 was the CURRENT one and Group 3 becomes CURRENT one after switching log. Continue reading