External Tables - 10g

External Tables - 10g

Until 9i external tables could only be used for read purposes.But 10g allows you to populate external tables.here is how it does:

Step 1: - Create a directory

SQL> create directory my_dir as '/usr/test';
--- Make sure Oracle OS user hasprivilege to write on this directory.

Step 2: - Grant permission to user SCOTT

SQL> grant read,write on directory my_dir to scott;

Step 3: - Create the External Table: -

SQL> CREATE TABLE scott.external_emp_dept

ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY my_dir
LOCATION ('emp_dept.exp'))
reject limit unlimited
AS select e.ename,d.dname from emp e, dept d where e.deptno=d.deptno;

SQL> select * from scott.external_emp_dept;

ENAME DNAME---------- --------------SMITH RESEARCHALLEN SALESWARD SALESJONES RESEARCHMARTIN SALESBLAKE SALESCLARK ACCOUNTINGSCOTT RESEARCHKING ACCOUNTINGTURNER SALESADAMS RESEARCHENAME DNAME---------- --------------JAMES SALESFORD RESEARCHMILLER ACCOUNTING

And you will find a file generated i.e. 'emp_dept.exp' in /usr/test directory.Now you can take this file to the target database and create an external table and associate the file with that table.

Step 4: - to be executed on the source database

SQL> set long 2000
SQL> Select dbms_metadata.get_ddl('TABLE','EXTERNAL_EMP_DEPT') from dual;
The above command will give you the DDL command to create the table at target database.

10g new background processes

10g new background processes

With 10g many new background processes were introduced.This note highlights those.

MMANMemory Manager (MMAN) coordinates the sizing of different memory components within SGA. MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage. This process is used only when you enable Automatic Shared Memory Management.


RVWRProcess responsible for writing flashback logs which stores pre-image of data-blocks.
These logs can be used to flash back database to past point in time. It proves very handy specially in situation of logical data corruptions like an accidental drop/truncate of a table or commit of an unwanted transaction.

CTWR
Change Tracking Writer (CTWR) Process introduced in 10g to track changes to block to assist fast RMAN incremental backups.

MMNL
The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed. It works with Automatic Workload Repository (AWR)

MMON
The memory monitor (MMON) process was introduced in 10g is responsible for gathering statistics for Automatic Workload Repository. This helps Oracle doing automatic problem detection and self tuning.M000
MMON background slave (m000) processes.

RBALRBAL (Re-balancer) is the ASM related process that performs rebalancing of disk resources controlled by ASM.ARBxThese processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.ASMBThe ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.T

ORA-14097 - when using materialized view and partition table

ORA-14097 - when using materialized view and partition table
This one was an interesting issue which came up few days back.

I spent quite sometime before I solved it.

Issue was - A developer came upto me and told that he is getting "ORA-14097 - : column type or size mismatch in ALTER TABLE EXCHANGE PARTITION" while exchanging partitions.This guy is working on a warehouse design in which huge data loads will happen on base tables through out the day and there are mviews based on which it mines and these mviews' data should be moved fact tables at midnight 12.Now we strategize in a way that at midnight when base tables will be truncated for fresh load of next day, we exchange the partitions of these mviews with temporary partitioned tables and use "Insert /*+ append */" to move it to final fact tables. We could not have directly exchanged partitions of fact table as they might not be partitioned by day.Now the above is all background. While investigating the issue, I was perplexed for some time when he showed me the issue. He created a temporary partitioned table using "create table as select .. the mview where 1=2" and while doing exchange partition he was getting ORA-14097.Let me give you a simple test case:SQL> create table test(a number);Table created.SQL> create materialized view log on test with rowid including new values;Materialized view log created.SQL> create materialized view test_mv refresh fast on demand with rowid as select * from test;Materialized view created.SQL> insert into test values(1);1 row created.SQL> commit;Commit complete.SQL> exec dbms_mview.refresh('test_mv','F')PL/SQL procedure successfully completed.Now both table and mview have on erow each.Let's try and exchange partition of this mview with another table.SQL> select partition_name from user_tab_partitions where table_name='TEST_PART';PARTITION_NAME------------------------------SYS_P3446

SQL> alter table test_part exchange partition SYS_P3446 with table test_mv;

alter table test_part exchange partition SYS_P3446 with table test_mv*ERROR at line 1:ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITIONI used CTAS, there is no question abt data type and column order.

SQL> desc test_partName Null? Type----------------------------------------- -------- ----------------------------A NUMBERSQL> desc test_mvName Null? Type----------------------------------------- -------- ----------------------------A NUMBERAfter doing some research, i got through this metalink article: 72332.1According to that : "If a table has a FUNCTIONAL index on it there is an extra hidden column in COL$ which will cause an EXCHANGE to fail. Comparing USER_TAB_COLUMNS will not show this, nor will USER_UNUSED_COL_TABS but COL$ will show the difference.

"SQL> select col#, name2 from sys.col$3 where obj# in4 (select object_id from user_objects where object_name = 'TEST_MV');COL# NAME---------- ------------------------------0 M_ROW$$1 ANow there you go - this M_ROW$$ was creating the problem for me.Old saying - identifying is problem is 80% of tak that entails solving it.Now - i created test_mv using primary key (not using rowid) and the whole exchange process worked fine!For those who donot have PK in their tables can consider having a separate column which can be seeded using a sequence and treat that as PK to combat this issue.

How can you perform Fragmentation in tables?

Table fragmentation –
when?

If a table is only subject to inserts, there will not be any fragmentation.Fragmentation comes with when we update/delete data in table.The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.To understand it more clearly, we need to be clear on how oracle manages space for tables.“High water mark” of table actually defines the border line between (ever) used and unused (never) space. While performing full table scan, Oracle will always read the data up to HWM. And if there is lot of free space with-in HWM, that is read too, and hence degrading the performance of FTS.Now lets see how to identify HWM, unused (never used) space and free space (used but deleted/updated) and then take a call whether the concerned table is candidate for a reorganization or not.

SQL> create table test as select * from dba_tables;

-- Create a tableTable created.

SQL> analyze table test compute statistics;

-- Analyze itTable analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"2 from user_tables where table_name='TEST'; -- The number of blocks used/freeEver Used Never Used Total rows---------- ---------- ----------49 6 1680
SQL> delete from test where owner='SYS';
--- Im deleting almost half the number of rows.764 rows deleted.
SQL> commit;
Commit complete.

SQL> analyze table test compute statistics; -- Analyze it againTable analyzed.SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"2 from user_tables where table_name='TEST'; -- No difference in blocks usageEver Used Never Used Total rows---------- ---------- ----------49 6 916PL/SQL procedure successfully completed.Even though you deleted almost half the rows, the above shows that table HWM is up to 49 blocks, and to perform any FTS, Oracle will go up to 49 blocks to search the data. If your application is so-written that there are many FTS on this table, you may consider, reorganizing this table.Reasons to reorganizationa) Slower response time (from that table)b) High number of chained (actually migrated) rows. c) Table has grown many folds and the old space is not getting reused.Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.How to reorganize? Before Oracle 10g, there were mainly 2 ways to do the reorganization of the tablea) Export, drop, and import.b) Alter table move (to another tablespace, or same tablespace).Oracle 10g provides us a new way of reorganizing the data.Shrink command: This command is only applicable for tables which are tablespace with auto segment space management.Before using this command, you should have row movement enabled.

SQL> alter table test enable row movement;Table altered.There are 2 ways of using this command.1. Break in two parts: In first part rearrange rows and in second part reset the HWM.Part 1: Rearrange (All DML's can happen during this time)

SQL> alter table test shrink space compact;Table altered.Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)

SQL> alter table sa shrink space;Table altered.2. Do it in one go:

SQL> alter table sa shrink space; (Both rearrange and restting HWM happens in one statement)Table altered.Few advantages over the conventional methods1. Unlike "alter table move ..", indexes are not in UNUSABLE state. After shrink command, indexes are updated also.2. Its an online operation, So you dont need downtime to do this reorg.3. It doesnot require any extra space for the process to complete.ConclusionIts a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management

What is the Difference between a schema and a user?

What is the Difference between a schema and a user?

A schema is collection of database objects, including logical structures such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.
A user owns a schema.
A user and a schema have the same name.
The CREATE USER command creates a user. It also automatically creates a schema for that user.
The CREATE SCHEMA command does not create a "schema" as it implies, it just allows you to create multiple tables and views and perform multiple grants in your own schema in a single transaction.
For all intents and purposes you can consider a user to be a schema and a schema to be a user.

What is HOT / COLD backup?

What is HOT / COLD backup?
There is two types of backup we can take for Oracle Database.
1. COLD/OFFLINE/CONSISTENT backup
What is cold backup and why we say "cold" backup?
When database is DOWN, no activity running on database, no one accessing the database. that time taken database backup called "COLD BACKUP". We can also say "OFFLINE" database backup.
In short:
COLD backup equal to OFFINE backup
COLD backup equal to CONSISTENT backup
For COLD/OFFLINE/COSISTENT database backup we must need to SHUTDOWN Oracle Database with the following option.
1. SQL>shutdown normal
2. SQL>shutdown immediate
3. SQL>shutdown transactional;
4. SQL>shutdown abort;
5. CMD>net stop OracleService
For Example of cold backup on any OS platforms.
1. conn with sysdba user.
2. shutdown oracle database
3. copy init.ora, all datafiles, all redologs files, all controlfile to backup location
4. startup oracle database
NOTE: Database doesn't require ARCHIVELOG mode for COLD backup.
2. What is HOT/ONLINE/INCONSISTENT backup?
When database is open, user accessing the database that time we taken backup is called "HOT,ONLINE, inconsistent" backup.
NOTE: Database must require ARCHIVELOG mode for HOT backup.
For HOT backup we have two options
1. RMAN Recovery Manager (Server Managed Backup)
2. User Managed Backup (User Managed backup)

Data Pump 10g

Data Pump -CONN sys/password@db10g AS SYSDBAALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;GRANT CREATE ANY DIRECTORY TO scott; -CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
Table export /import-expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log -impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
Schema Exports/Imports
The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax:expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log
Database Exports/Imports
The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax:expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.logimpdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

Parallel Full Export and Import: Example
$ expdp system/manager full = y
parallel = 4
dumpfile = DATADIR1:full1%U.dat,
DATADIR2:full2%U.dat,
DATADIR3:full3%U.dat,
DATADIR4:full4%U.dat
filesize = 2G
$ impdp system/manager
directory = NET_STORAGE_1
parallel = 4
dumpfile = full1%U.dat,full2%U.dat,
full3%U.dat,full4%U.dat

Limited Schema Export: Example
$ expdp system/manager schemas = hr,oe
directory = USR_DATA
dumpfile = schema_hr_oe.dat
parfile = exp_par.txt
include = function
include = procedure
include = package
include = type
include = view:"like ’PRODUCT%’"
$ impdp system/manager directory = USR_DATA
dumpfile = schema_hr_oe.dat
sqlfile = schema_hr_oe.sql

Network Mode Import: Example
$ impdp system/manager
schemas = hr,sh,payroll
parfile = imp_par.txt
network_link = finance.hq.com
flashback_time = 2003-09-08 09:00
remap_schema = payroll:finance

Oracle Flashback Transaction Query 10g

Oracle Flashback Transaction Query
Through Flashback Transaction query we can obtain transaction information, including SQL code.Flashback Transaction query queries the static data dictionary view FLASHBACK_TRANSACTION_QUERYWe get following information from FLASHBACK_TRANSACTION_QUERY view1. XID 2. STAR_SCN 3. START_TIMESTAMP 4. COMMIT_SCN 5. UNDO_SQLXID is represent transaction ID,
we can get XID from FLASHBACK VERSION QUERYFor example:Scott user by mistake delete one row from TAJ table and now system user want to know exact TRANSACTION (sql code) for deleted row.We can query in FLASHBACK_TRANSACTION_QUERY view and get exact transaction (sql code)---First check what TRASACTION_ID is for deleted row through FLASHBACK VERSION QUERYselect versions_xid from scott.taj versions between timestampto_timestamp ( '2008-05-17 09:18:00','YYYY-MM-DD HH24:MI:SS') andto_timestamp ( '2008-05-17 09:22:00','YYYY-MM-DD HH24:MI:SS');VERSIONS_XID ---------------- 050017007C070000SQL> select logon_user,operation,undo_sql2 from flashback_transaction_query3 where xid = HEXTORAW ('050017007C070000');LOGON_USER OPERATION UNDO_SQL---------- ---------- ----------------------------------------------SCOTT DELETE insert into "SCOTT"."Shahid"("NAME","NO") values ('Shahid','4');So above is DELETE operation performed by SCOTT user and above UNDO_SQL is generated.Suppose we don’t know about exact timing then we can use SCN (system change number) when last commit is performed.We can get SCN number through ORA_ROWSCN pseudo column, ORA_ROWSCN is represents SCN of the most recent change to given row, that is latest COMMIT operation for the row.Suppose with above example if we don’t know exact timing then row deleted in Shahid table. So we can get exact timing from ORA_ROWSCN pseudo column.SQL> select ORA_ROWSCN, taj.* from scott.taj;ORA_ROWSCN NAME NO---------- -------------------- ----------2407151 Shahid 12407151 Shahid 3Latest COMMIT is performed on Shahid table is 2407151.Now change SCN to TIMESTAMP to get exact timing when last commit was performed.
SQL> select scn_to_timestamp('2407151') from dual;SCN_TO_TIMESTAMP('2407151')---------------------------------------------------------------------------17-MAY-08 09.18.20.000000000 AM
Last COMMIT is performed 17-may-08 09:18 AM.
select undo_sqlfrom flashback_transaction_querywhereCOMMIT_TIMESTAMP >= to_timestamp('17-MAY-05 09:18:00','DD-MON-RR HH:MI:SS')and TABLE_NAME = 'Shahid';
UNDO_SQL--------------------------------------------------------------------------------insert into "SCOTT"."Shahid"("NAME","NO") values ('Shahid','4');

Oracle Flashback Query 10g

Oracle Flashback QueryWe can recover lost records which done by DML statement not DDL.Through this feature we can able to perform below task1. Recovering lost data or undoing incorrect, commit changes.2. Comparing current data with the corresponding data at some time in the past3. Checking the state of transactional data at a particular time4. Simplifying application design by removing the need to store some kinds of temporal dataExample:User Scott delete empcopy table by mistake at 7:53 pm and also delete one record from emp table at 7:25 pm and commit.After he found his doing incorrect operation on empcopy or emp table now he want to recover lost records without performing incomplete recovery or export/import method.Through Flashback query we can recover lost transaction, we can query past data with SELECT … AS OF TIMESTAMP or SCN (system change number)SQL> select empno,sal,ename from emp2 AS OF TIMESTAMP to_timestamp('2008-05-14 19:24:00','YYYY-MM-DD HH24:MI:SS')3 where empno = 7934;
EMPNO SAL ENAME---------- ---------- ----------7934 1300 MILLERInsert this record again in EMP tableSQL> insert into emp ( select * from emp AS OF TIMESTAMP to_timestamp('2008-05-14 19:24:00','YYYY-MM-DD HH24:MI:SS') where empno = 7934);1 row created. SQL> commit; Commit complete.Now recover empcopy tableSQL> insert into empcopy ( select * from empcopyAS OF TIMESTAMP to_timestamp('2008-05-14 19:54:00','YYYY-MM-DD HH24:MI:SS') );
13 rows created.
SQL> commit;
Commit complete.

Oracle Flashback Table 10g

Through flashback table we can rewind table in past stage.
Flashback table uses information in the undo tablespace rather than restored backups.
When flashback table operation occurs, rows are deleted and reinserted. The rest of database remain available which the flashback of the table is being performed.
Prerequisites for flashback table1. Must have FLASHBACK ANY TABLE or FLAHSBACK object privileges
2. Must have SELECT, INSERT, UPDATE , DELETE and ALTER privilege on the table
3. Must have SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY system privileges.
Following objects are eligible for flashback table option1. The object must not be included the following categories: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions2. The structure of the table must not have been changed between the current time and the target flash back time.The following DDL operations change the structure of a table: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (with the exception of adding a range partition).
3. Row movement must be enabled on the table, which indicates that rowids will change after the flashback occurs.4. The undo data in the undo tablespace must extend far enough back in time to satisfy the flashback target time or SCN.Example:
18:30:32 SQL> delete scott.emp;13 rows deleted.18:30:38 SQL> commit;Commit complete.
After commit user found his made delete operation on wrong table now he wants to back all deleted records.
The perform a flashback of SCOTT.EMP
1. obtain SCN information about deleted records and commit;SQL> select current_scn from v$database;CURRENT_SCN-----------2456211
2. Ensure that enough undo data exists to rewind the table to the specified target.SQL> SELECT NAME, VALUE/60 MINUTES_RETAINED2 FROM V$PARAMETER3 WHERE NAME = 'undo_retention';NAME MINUTES_RETAINED------------------------------ ----------------undo_retention 603. Ensure that row movement is enabled for all objects that you are rewinding with Flashback Table.SQL> alter table SCOTT.EMP enable row movement;Table altered.
4. Determine whether the table that you intend to flash back has dependencies on other tables. If dependencies exist, then decide whether to flash back these tables as well.SQL> SELECT other.owner, other.table_name2 FROM sys.all_constraints this, sys.all_constraints other3 WHERE this.owner = 'SCOTT'4 AND this.table_name = 'EMP'5 AND this.r_owner = other.owner6 AND this.r_constraint_name = other.constraint_name7 AND this.constraint_type='R';OWNER TABLE_NAME------------------------------ ------------------------------SCOTT DEPT
5. Execute a FLASHBACK TABLE statement for the objects that you want to flash back.SQL> flashback table SCOTT.EMP2 to timestamp to_timestamp ('2008-05-17 18:30:31','YYYY-MM-DD HH24:MI:SS');Flashback complete.
NOTE: User performed delete operation at 18:30:31 so he flashback table with TIMESTAMP.
6. Optionally, query the table to check the data.SQL> select count(*) from scott.emp;COUNT(*)----------13
NOTE: Flashback table operation is complete and lost data is recovered.
Keeping Triggers Enabled During Flashback Table
It is recommended to add ENABLE TRIGGER keyword with FLASHBACK TABLE statement. For enable database trigger which create on flashback table.
For example:
SQL> Flashback table SCOTT.EMP to timestamp to_timestamp (‘2008-05-17 18:30:31’,’YYYY-MM-DD HH24:MI:SS’) ENABLE TRIGGERS;

Oracle Flashback Troubleshooting 10g

1- Oracle flashback troubleshooting
SQL> alter database flashback on;
alter database flashback on ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.Cause: Flashback log file are created only in FLASH

RECOVERY AREA then must be DB_RECOVERY_FILE_DEST parameter for flashback recovery areaSolution:
SQL> alter system set db_recovery_file_dest='location';SQL> alter database flashback on;

2. SQL> alter database flashback on; --alter database flashback on

ERROR at line 1: ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38714:

Instance recovery required.Cluase: Database is not cleaning shutdown, database shutdown must be NORMAL, IMMEDIATE or TRANSACTIONAL;Solution: SQL> alter database open; (becuase currently database is in mount stage)

SQL>SHUTDOWN NORMAL SQL>STARTUP MOUNT SQL>ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE OPEN;3. SQL> startup ORACLE instance started.[output cut] Database mounted.

ORA-38760: This database instance failed to turn on flashback databaseClause: Flashback file is not accessiable or missing or lost
NOTE: check alertlog file for more info

Solution:
SQL> alter database flashback off;
SQL> alter database flashback on;
SQL> alter database open;

EXPORT AND IMPORT UTILITIES

EXPORT AND IMPORT UTILITIES

CH # 18

Export Full Schema
C:\Documents and Settings\Shahid ul ghani>exp
Username: scott
Password: tiger
Enter array fetch buffer size: 4096 > 4096
Export file: EXPDAT.DMP > c:\bac.dmp
(2)U(sers), or (3)T(ables): (2)U > u (if we want to export table then use T)
Export grants (yes/no): yes > y
Export table data (yes/no): yes > y (if No then only definition will be exported)
Compress extents (yes/no): yes > y
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exporte
. . exporting table DEPT 4 rows exporte
. . exporting table EMP 14 rows exporte
. . exporting table SALGRADE 5 rows exporte
.
Export terminated successfully without warnings.
Export a specific Table or tables
C:\> exp scott/tiger tables=emp,dept
Export: Release 10.2.0.1.0 - Production on Sat Nov 8 17:19:23 2008
About to export specified tables via Conventional Path ...
. . exporting table EMP 14 rows exported
. . exporting table DEPT 4 rows exported
Export terminated successfully without warnings.
Export a Particular Tablespace

SQL>Alter tablespace users Read Only
C:\> exp userid=’sys/oracle as sysdba’ tablespaces=users file=c:\users.dmp
(if we want to export only single datafile then use the following )
Datafile=’c:\oracle\oradata\orcl\users01.dbf’
SQL> Alter tablespace users Read Write
Export Full database by SYS
C:\Documents and Settings\Shahid ul ghani>exp ‘sys/oracle as sysdba’ full=y
file=c:\fulldb.dmp rows=y (if no then tables structure is export)
INCREMENTAL EXPORT
There are two major types of incremental export .
1- Incremental
2- Full / Non- Incremental
There are three types of incremental export
1- Complete
2- Incremental
3- Cumulative
C:\> EXP system/manager FILE=C:\complete.dmp FULL=y INCTYPE=complete
C:\> EXP system/manager FILE=c:\increment.dmp FULL=y INCTYPE=incremental
C:\> EXP system/manager FILE=c:\cumulative.dmp full=y INCTYPE=cumulative
IMPORT UTILITY
C:> imp scott/tiger tables=emp rows=y file=c:\imp.dmp
C:\> imp system/manager FROMUSER=scott file=c:\abc.dmp TOUSER=hr
C:\> imp userid=’sys/oracle as sysdba’ tabelspace=users file=c:\users.dmp

Recovery Catalog Creation

Recovery Catalog Creation

CH # 17

What is recovery catalog?
Recovery catalog is external database which use to store RMAN
repository
What is RMAN repository?
RMAN repository is collection of target database which store
information about RMAN backup, recovery and maintenance.
When recovery catalog is not in used then where RMAN
repository stored?
When recovery catalog is not in used then RMAN repository store in
CONTROLFILE.
Where we create recovery catalog?
We have two option for it.
1. We can create recovery catalog on target database through create
separate tablespace for recovery catalog.
Or

2. We can create separate database for recovery catalog.
NOTE: if we have more than one database then separate database for
recovery catalog is recommended instead of separate tablespace.
How recovery catalog store information about RMAN
repository?
After creation of recovery catalog we have to manually register each
target database to recovery catalog.
Should we need to take backup of recovery catalog?
Yes, We must take backup of recovery catalog because recovery
catalog store most important information about RMAN backup,
recovery and RMAN configuration if we lost recovery catalog then we
can’t get those information so it is always recommended to take
recovery catalog backup.
How take recovery catalog backup?
1. If recovery catalog created as separate tablespace on target
database
Then just export recovery catalog tablespace and store in backup
drive.
2. If recovery catalog created as separate database.
Then we can use any backup method which we use for target database
for instance we can use user managed backup or we can also use
RMAN method.
Is there any view for query information about recovery
catalog?
Yes, actually for recovery catalog we need to create separate user.
And after user and recovery catalog creation there is some view
created in recovery catalog users with RC_ prefix. For instance:
RC_DATABASE
How to configure recovery catalog for target database?
There are three steps for recovery catalog configuration.
1. Configure the database that will content the recovery catalog,
2. Create owner for recovery catalog
3. Create recovery catalog

SQL> conn sys/oracle as sysdba
Connected.
SQL> create tablespace CATDB
2 datafile 'c:\oracle\catdb01.dbf' size 80m;
Tablespace created.
NOTE: Here I am create separate tablespace for recovery catalog on
target database.
SQL>conn sys/oracle as sysdba
Connected.
SQL> create user CATDB
2 identified by CATDB
3 default tablespace CATDB;
NOTE: Owner for recovery catalog
SQL> grant connect,resource to catdb;
Grant succeeded.
SQL> grant recovery_catalog_owner to CATDB;
Grant succeeded.
NOTE: Grant the RECOVERY_CATALOG_OWNER role to the schema
owner. This role provides the user with all privileges required to
maintain and query the recovery catalog.
C:\>rman
Recovery Manager: Release 10.1.0.5.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
RMAN> connect catalog catdb/catdb
connected to recovery catalog database
recovery catalog is not installed

RMAN> create catalog tablespace CATDB;
recovery catalog created
NOTE: now recovery catalog is created.
SQL> conn catdb/catdb
Connected.
SQL> select table_name from user_tables where rownum =1;
TABLE_NAME
------------------------------
RCVER
NOTE: after recovery catalog creation we can check all recovery
catalog views is created in recovery catalog owner.
What we do after create recovery catalog?
Now just register target database to recovery catalog and store RMAN
repository in recovery catalog.
C:\>rman target sys/oracle catalog catdb/catdb
connected to target database: DEV (DBID=3718891706)
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Viewing the Recovery Catalog

1- RC_DATABASE (List of Registered database with Recovery Catalog)
2- RC_DATAFILE
3- RC_STORED_SCRIPT
4- RC_STORED_SCRIPT_LINE (display that what is inside the script)
5- RC_TABLESPACE
Just connect with user CATDB and perform the following queries
SQL> desc RC_Database
SQL>Desc RC_Datafile
Etc..
Stored Script in Recovery catalog
RMAN> Create script abc {
Backup database;}
Execute script
RMAN> Run {
Execute script abc;}
Replace Script
RMAN> Replace script abc
Backup database
Filesperset 2;}
Delete Script
RMAN> Delete script abc;
Print Script on screen
RMAN> Print script abc

Using RMAN to Recover a Database in ARCHIVELOG mode

CH # 13

Note: You must have backup of database by RMAN.
Scenario: Delete two or three datafiles from database and then do the following:
RMAN> Startup Mount
RMAN> Restore Database;
RMAN> Recover Database
RMAN>Alter database open
Using RMAN to Restore Datafiles to a New Location
RMAN> Run {
Set newname from datafile 1 to ‘c:\newlocation\system01.dbf’;
Restore database;
Switch datafile all (entry will be recorded in controlfile)
Recover database;
Alter database open;}

Using RMAN to Recover a Tablespace
RMAN> Run {
Sql ‘alter tablespace users offline immediate ‘;
Restore tablespace users;
Recover tabelspace users;
Sql ‘ alter tablespace users online’; }
Using RMAN to Relocate a Tablespace
RMAN > Run {
Sql ‘ alter tablespace users offline immediate ‘;
Set newname for datafile ‘c:\oracle\oradata\orcl\users01.dbf’ to
‘c:\newlocation\users01.dbf’;
Restore tablespace users;
Switch datafile 3; (update the control file and recovery catalog)
Recover tablespace users;
Sql ‘ alter tablespace users online;
}

RMAN InComplete Recovery Until Time

CH # 15

Note: Suppose a table is drop and you have to recover that table then you must have
to perform incomplete recovery.
Set the following environmental variables in registry for this practical
1- NLS_LANG=American
2- NLS_DATE_FORMAT =’YYY’-MM-DD:HH24:MI:SS’
RMAN > Run {
Allocate Channel c1 type disk;
Allocate Channel c2 type disk ;
Set until time=’2001-12-08:13:54:00’
Restore database;
Recover database;
Alter database open resetlogs;
}

RMAN MAINTENANCE COMMANDS
CH # 16

CROSSCHECK COMMAND
RMAN> Crosscheck backupset of database;
RMAN> Crosscheck Copy ;
This command compare the recovery catalog to Hard Disk for confirmation that
the above backupset is available or expired.
DELETE COMMAND
RMAN> Delete backupset 102
If you want to delete without confirmation the use the following command.
RMAN> Delete noprompt expired backup of tablespace users;
If you want to delete backups,copies and archived redo log files based on the configured
retention policy then use the following command .
RMAN> Delete Obsolete

RMAN BACKUPS

RMAN BACKUPS

CH # 11

Backup of Control file
RMAN> Backup current controlfile tag=Sunday
RMAN> Backup device type disk tablespace users include current controlfile;
RMAN> Backup datafile 2 include current controlfile;
RMAN> Backup current control file;
Backup Piece Size
RMAN> Run {
Allocate channel c1 type disk
Maxpiecesize= 4G;
Backup
Format =’c:\backup\df_%t_%s_%p’ filesperset 2
(tablespace users );}
The Backup Command
RMAN> Backup
Format =’c:\backup\df_%d_%s_%p.bus’
Database filesperset = 3;
Parallelization of Backup Sets
(Multiple channels working for multi files)
RMAN> Run {
Allocate channel c1 type disk;
Allocate channel c2 type disk;
Allocate channel c3 type disk;
Backup database
Format =’c:\backup\df_%d_%s_p.bak’
(datafile 1,2,3 channel c1 tag=DF1)
(datafile 4,5,6 channel c2 tag=DF1)
(datafile 7,8,9 channel c3 tag=DF1)
Sql ‘ alter system archive log current’; }

Duplexed Backup Sets
RMAN> Backup copies 2 datafile 1,2
Format =’c:\backup\%U’,’d:\backup\%U’;
Backup of Backupset
RMAN> Backup backupset 1
Archived Redo Log File Backup
RMAN> Run {
Allocate channel c1 device type disk ;
Backup archivelog
Sequence between 31 and 40 thread 1
Delete input ;}
First select sequence number from v$archived_log view;
RMAN> Backup archivelog all
RMAN> Backup archivelog all not backed up 2 times
RMAN> Backup archivelog all from scn=1 until scn=20;
RMAN> Backup
Format =’c:\backup\ar_%t_%s_%p’
Archivelog all delete all input;
Image Copy (1 by 1 relationship means each file will be copied separatly )
RMAN> Copy
Datafile ‘c:\oracle\oradata\orcl\users01.dbf’ to ‘c:\backup\users01.dbf’
Archivelog ‘c:\oracle\ora92\rdbms\arch_001.arc’ to ‘c:\backup\arch_01.arc’
RMAN> Copy
Datafile 1 to ‘c:\backup\file1.dbf’,
Datafile 2 to ‘c:\backup\file2.dbf’
Image Copy Parallelization (Multiple server process will write multifiles at same time)
RMAN> Configure device type disk parallelism 4 ; (if not configure )
RMAN> Copy (3 files copied in parallel means 3 server process write parallel)
Datafile 1 to ‘c:\backup\df1.dbf’,
Datafile 2 to ‘c:\backup\df2.dbf’,
Datafile 3 to ‘c:\backup\df3.dbf’;
RMAN> Copy (second copy command will write by 4th server process in single form)
Datafile 4 to ‘c:\backup\df4.dbf’;

Copying the whole database
RMAN> Startup mount
RMAN> Report schema;
RMAN> Copy datafile1 to ‘c:\backup\df1.cpy’,
Datafile 2 to ‘c:\backup\df2.cpy’, etc…
RMAN> List copy
Incremental Backups (according to company policy of backup)
RMAN> Backup incremental level 0 database;
RMAN> Backup incremental level 3 database;
RMAN> Backup incremental level 3 database;
RMAN> Backup incremental level 3 database;
RMAN> Backup incremental level 3 database;
RMAN> Backup incremental level 3 database;
RMAN> Backup incremental level 2 database;
Cumulative backup
RMAN> Backup incremental level 2 cumulative database;
Backing up the SPfile
RMAN> Backup copies 2 device type disk spfile;

BACKUP USING RMAN

BACKUP USING RMAN

CH # 9

To connect RMAN type c:\rman target sys/oracle
Automatic channel allocation
RMAN> Configure default device type to disk or sbt;
RMAN> Configure device type to disk parallelism 3 (3 server process will be started)
RMAN> Configure channel device type disk
Format =’c:\backup\%U’; (Backup destination is set now)
RMAN> Configure channel device type disk
Maxpiecesize 2G; ( the size of a piece is 2G in a set)
Manual Channel Allocation
RMAN> Run {
2> Allocate channel c1 type disk
3> Format =’c:\backup\users01.bak’
4> Backup datafile ‘c:\oracle\oradata\orcl\users01.dbf’;}

Backing up a tablespace

RMAN> Backup tablespace system ;
Backing up Archived logs
RMAN> Backup archivelog all
RMAN> Backup archivelog all delete input( Current destination archive file will be
backed up and delete)
RMAN> Backup archivelog all delete all input(All archivefiles backed up and delete)
RMAN> List backup (To check backup detail)
RMAN> Restore database validate (to check that backup is valid or not)
Additional RMAN Command Line Arguments
First Create a notepad file like:
Backup database;
Save this file with script.rcv .
Now run this file on command line like:
C:\ rman target sys/oracle@’c:\backup\script.rcv log=logfile.log
JOB COMMAND
RMAN> RUN {
Backup
Incremental level 0
Format =’c:\backup\%d_%s_%p’
Filesperset 2
(database include current controlfile);
Sql ‘ alter database archive log current’;}
SIMPLE backup command
RMAN> Backup database
RMAN> Backup datafile
Use LIST command to check database and datafiles .
RMAN> List backup of database;
RMAN> List backup of datafile 4;
RMAN> List copy of tablespace “SYSTEM”
RMAN> List expired backup
RMAN> List backup summary
RMAN> List backup by File
RMAN> List backupset 1
REPORT COMMAND
RMAN> Report schema
RMAN> Report obsolete (which backup can be deleted)
RMAN> Report need backup incremental 3 database ;
RMAN> Report need backup days 3 tablespace system;(what system files have not been
backed up for three days)
Mirror backup in RMAN
RMAN> RUN {
Set backup copies 2;
Backup database
Format =’c:\backup\%U’,’d:\backup\%U’;}

Cancel Based Recovery

Cancel Based Recovery

Scenario:
One of the online redo log is missed. Then missing redo log is not archived.
You can recover the database as follows:-
1- Shutdown the database
2- Restore all datafiles rom the most recent backup.
3- You already have a valid backup so mount the database
4- Recover the database until cancel
5- Open the database by using the resetlogs option

Using a Backup Control File During Recovery

1- Drop tablespace users including contents;
2- Alter system enable restricted session;
3- Select * from v$log;
4- Confirm the time of error by checking the alert log file in bdump folder.
5- Shutdown the database(restore all datafiles and controlfile from backup)
6- If any file is offline then first become this file online
Alter database datafile 4 online;
7- recover database until time ‘2008-03-11:11:45:00’;
8- alter database open resetlogs;
9- Make a whole backup now.

USER MANAGED INCOMPLETE RECOVERY

USER MANAGED INCOMPLETE RECOVERY

CH# 14

RECOVERY

1-Physical Recovery 2-Logical Recovery
No-Archive
Full Offline Database Recovery
Archive
Full Offline Database Recovery
Partial Offline Database Recovery
Partial Online Database Recovery
Complete Database Recovery
In-Complete Database Recovery

1. Time Based
2. Cancel Based
3. SCN Based

Time Based Incomplete Recovery
Scenario: Suppose a user delete records and commit or drop the table.
The DBA don’t know the situation till 4:00. When a user try to access that table then he
complained to DBA that table is not exist.
Then DBA will back the database before the Drop command is executed.
Note: We must have a backup of database .
1- Startup
2- Alter system switch logfile
3- Alter system switch logfile
4- Create a table now (insert record and commit it)
5- Alter system switch logfile
6- Alter system switch logfile
7- Alter system switch logfile
8- Create another table
9- Alter system switch logfile
10- Create another table and switch the logfile
11-Drop a table now and note the time when the table is dropped .
12- Create another table
13-Alter system switch logfile(2 times)
14- Shutdown the database
15- Restore all datafiles (only) from the backup.
16- Startup mount
17- Set autorecovery on
18- Recover database until time ‘2005-10-21:16:12:00’
19-Alter database open resetlogs;
20-Now take a new backup of database
Created by: SHAHID UL GHANI (OCP 9i, 10g, 11g)

DBVERIFY COMMAND LINE INTERFACE

FAILURE DURING ONLINE TABLESPACE BACKUP

Error: ORA-01113 :- File 1 need media recovery
1- Mount the database (startup mount)
2- Alter database end backup
3- Alter database open

DBVERIFY COMMAND LINE INTERFACE

DBverify is a command line utility used to verify that a backup database or datafile is
valid before it is restored.
Syntax:
C:\dbv file=c:\backup\system01.dbf (press enter)
Then you will see an error DBV-00103: Specified blocksize (4096) is dffers from
actual (8192). Then use like :
C:\dbv file=c:\backup\system01.dbf blocksize= 8192 logfile=dbv.log feedback=100
start=1 end=1000 ;
Feedback means One page = 1 block is checked.

DBVERIFY USING PARFILE

Note: Create a file in notepad like
File=c:\backup\tools01.dbf blocksize=8192
Now save this file with dbv.txt
Now on C prompt type c:\dbv parfile=c:\dbv.txt (Now the verification is started)

OPEN DATABASE BACKUP BY SCRIPT

OPEN DATABASE BACKUP BY SCRIPT

Step 1
Note: Create a sample.sql file in Notepad and write the following:.
1- Alter tablespace users begin backup;
2- Host copy c:\oracle\oradata\orcl\users01.dbf c:\backup\users01.dbf;
3- Alter tablespace users end backup;
4- Alter system switch logfile ;
5- Exit ;
Step 2

Create another file in notepad with script.bat name and write the following:

1- sqlplus system/manager@orcl @c:\sample.sql
Now double click this file then backup will be started.

USER MANAGED OFFLINE TABLESPACE AND DATAFILES BACKUP

1- Compilation
2- Alter tablespace users offline normal
3- Copy the files of users tablespace and put into the backup folder
4- Alter tablespace users online
5- Alter system archive log current
Manual Control File Backup (Control file is online)
1- Alter database backup controlfile to ‘c:\backup\control1.bkp’;(this is Binary File)

Creating a Trace File

1- Alter database backup contrlfile to trace; (it’ll copy the controlfile in UDump
folder and this file is a text file)

PARALLEL OR MULTI USER MANAGED BACKUP

1- compilation
2- Alter tablespace system begin backup
3- Alter tablespace users begin backup
4- Alter tablespace tools begin backup
5- Copy all tablespaces files and put into backup folder
6- Alter tablespace system end backup
7- Alter tablespace users end backup
8- Alter tablespace tools end backup
9- Alter system Archive log Current;

USER MANAGED whole database BACKUP

USER MANAGED whole database BACKUP

1- Compilation (check the location of datafiles,controlfiles and log files)
2- Shutdown normal / immediate / transactional
3- Copy all required files to backup location
4- Open the database (Startup)
OPEN DATABASE BACKUP
1- Compilation
2- ALTER TABLESPACE SYSTEM BEGIN BACKUP
3- Copy all system tablespace related files to Backup Folder
4- ALTER TABLESPACE SYSTEM END BACKUP
5- ALTER SYSTEM SWITCH LOGFILE

BACKUP AND RECOVERY SECTION

BACKUP AND RECOVERY SECTION
Ch 10

There are two major types to take backup & recovery in oracle :
1- USER MANAGED BACKUP & RECOVERY
2- RMAN (RECOVERY MANAGER)
USER MANAGED BACKUP
Physical Backup Logical Backp
(datafile,controlfile,logfiles) (Imp & Exp)
(This Backup is
Takeout from
No-Archive Archive instance/SGA)
Full Offline Full Offline Database Backup
Database Full Online Database Backup
Backup Partial Offline Database Backup
Partial Online Database Backup
Note:
In No-Archive Mode just Shutdown the database and take Backup of (Physical Files)

Backup & Recovery /Changing the Archiving Mode

Changing the Archiving Mode

Ch # 8

If database is in NOARCHIVE mode then the following steps are used to change then
mode into ARCHIVE mode.

1-Shutdown Normal/Immediate/Transactional
2-Startup Mount
3-Alter database archivelog
4-Alter database open
5-Full database backup

Note: Same steps are used From Archive to No-Archive just change the step 3 and write
Noarchivelog instead of Archivelog.

Automating Archiving

1- Just write the LOG_ARCHIVE_START=TRUE in parameter file .whenever the
instance will start the archive process will be enabled.
2- Or ALTER SYSTEM SET ARCHIVE LOG START or STOP
3- Or ALTER SYSTEM SET ARCHIVE LOG START TO
‘c:\archivefiles\arc001.log’ ; (to change archive file location)

Stop or Start additional Archive Processes

1- in parameter file just write LOG_ARCHIVE_MAX_PROCESSES=VALUE(4 )etc.
2- or in open database just write ALTER SYSTEM SET
LOG_ARCHIVE_MAX_PROCESSES=4 ;

Manually Archiving Online Redo Log Files

1 – ALTER SYSTEM ARCHIVE LOG CURRENT;
SPECIFYING MULTIPLE ARCHIVE LOG DESTINATIONS
1-log_archive_dest_1=”location=c:\archivefile\arc.log”
2- log_archive_dest_2=”location=c:\archivefile\arc.log”
3- log_archive_dest_3=”location=c:\archivefile\arc.log” etc. Or
1- log_archive_dest_1=”location=c:\archivefile\arc.log MANDATORY REOPEN”
(Archive file must be created in this location)
2- log_archive_dest_1=”location=c:\archivefile\arc.log MANDATORY
REOPEN=600” (seconds)
(Archive file must be created in this location if no space then archive process
attempt to write after 600 seconds)
3- log_archive_dest_1=”location=c:\archivefile\arc.log OPTIONAL”
4- Log_archive_dest_state_2 =Defer (Archive file will not generate in this location)
To check files situation then use ARCHIVE LOG LIST

Sql Loader

SQL Loader

Step-1. Write these statement in NOTEPAD and save as “ sample.ctl “
LOAD DATA
INFILE ‘sample.dat’
BADFILE ‘sample.bad’
DISCARDFILE ‘sample.dis’
APPEND
INTO TABLE test
( sno POSITION(1:10) , name POSITION(11:30) , class POSITION(31:40))
Step-2. Create a file named as “sample.dat” using notepad having following data.
1 ALI ONE
2 KAMRAN ONE
3 BILAL THREE
4 SAAD FIVE
5 ALI SIX
6 FAHAD NINE
7 ALI MATRIC
8 HARIS TWO
Step-2. Create a table named as test.
SQL> CREATE TABLE test
(SNO NUMBER, NAME VARCHAR2(15), CLASS VARCHAR2(10)) ;
Step-3. Now COMMAND prompt and run the following statement.
C:\ > SQLLDR scott/tiger control=sample.ctl direct=Y

Creating a database Manually

Creating a database Manually

Step-1. Create a folder as ORCL2 (name other then the existing database)
And then create 5 more folders in this folder name and give them the following names
1. create
2. udump
3. bdump
4. cdump
5. Pfile

Step-2. Copy original “init.ora” from Oracle → admin → ORCL → Pfile and past it in new
Pfile folder created in 1st step.

Step-3. Change these parameters in new init.ora file
1. Db_name = ORCL2
2. background_dump_dest = c:\ORCL2\bdump
core_dump_dest = c:\ORCL2\cdump
user_dump_dest = c:\ORCL2\udump
3. control_files = ("C:\oracle\oradata\ORCL2\CONTROL01.CTL", "C:\oracle\oradata\ORCL2\
CONTROL02.CTL", "C:\oracle\oradata\ORCL2\CONTROL03.CTL")
4. Remote_login_password file = SHARED
5. # undo_managemnt = Auto
# undo_tablespace = undoTBS1

Step-4. Now startup the database with Pfile in no mount mode and create a database
SQL > Startup nomount pfile=’c:\orcl2\pfile\init.ora’
SQL > Create database orcl2
2. datafile ‘c\orcl2\create\system01.dbf’ size 200M
3. Log file
4. Group 1 (‘c:\orcl\create\g1m1.log’) size 5M ,
5. Group 2 (‘c\ orcl\create\g2m2.log’) size 5M ;
Now an empty database is created

Step-5. To copy default tables we have to run these scripts
SQL> @c:\oracle\ora92\RDBMS\admin\catalog.sql
SQL> @c:\oracle\ora92\RDBMS\admin\catproc.sql
Step-6. Now we can create additional table spaces as needed

Creating Roles

Creating Roles

Use the following command to create a role:

SQL> CREATE ROLE role_name [ NOT IDENTIFIED | IDENTIFIED
{ BY password | EXTERNALLY | GLOBALLY | USING package }]
where :
NOT IDENTIFIED = Indicates that no verification is required when enabling the role.
IDENTIFIED = Indicates that verification is required when enabling the role.
BY PASSWORD = Provides the password that the user required when enabling the role.
EXTERNALLY = Indicates that user must be authorized by an external service such as operating
system service.
USING package = Creates an application role, which is a role that can be enabled only by
application using as authorized package.
GLOBALLY = Indicates that a user must be authorized to use the role by the enterprise
directory service before the role is enabled with the SET ROLE statement.
For Example :
SQL> CREATE ROLE oe_clerk ;
SQL> CREATE ROLE hr_clerk IDENTIFIED BY bonus ;
SQL> CREATE ROLE hr_clerk IDENTIFIED EXTERNALLY ;
Modifying Roles
Use the following command to modify a role:
SQL> ALTER ROLE role_name [ NOT IDENTIFIED | IDENTIFIED
{ BY password | EXTERNALLY | GLOBALLY | USING package }]
For Example :
SQL> ALTER ROLE hr_clerk IDENTIFIED BY order ;
SQL> ALTER ROLE hr_clerk NOT IDENTIFIED ;
SQL> ALTER ROLE hr_clerk IDENTIFIED EXTERNALLY ;
Assigning Roles
To grant a role to user, following syntax is used:
SQL> GRANT [role1,role2,role_n] TO {user | role | PUBLIC }
[ WITH ADMIN OPTION ] ;
where :

role = Is a collection of role to be granted.
PUBLIC = Grants the role to all users.
WITH ADMIN OPTION = Enables the grantee to grant the role to other users or role.
For Example :
SQL> GRANT oe_clerk TO scott ;
SQL> GRANT hr_clerk TO hr_manager ;
SQL> GRANT hr_manager TO scott WITH GRANT OPTION ;
NOTE:- The maximum number of database roles that users can enable is set by the
MAX_ENABLED_ROLES initialization parameter.
Establishing Default Roles
SQL> ALTER USER scott DEFAULT ROLE hr_clerk , oe_clerk ;
SQL> ALTER USER scott DEFAULT ROLE ALL ;
SQL> ALTER USER scott DEFAULT ROLE NONE ;
SQL> ALTER USER scott DEFAULT ROLE ALL EXCEPT hr_clerk ;
Revoking Roles
SQL> REVOKE hr_clerk FROM scott ;
SQL> REVOKE hr_clerk FROM PUBLIC ;
PUBLIC keyword revokes the privileges or roles from all users;
Dropping Roles
SQL> DROP ROLE role_name ;

Creating Profile and priviliges

Creating Profile

SQL> CREATE PROFILE profile_name LIMIT
[parameter_1] [parameter_2] [parameter_3] [parameter_n] ;

For Example:
SQL> CREATE PROFILE prfl LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 30
PASSWORD_REUSE_TIME 30
PASSWORD_CERIFY_FUNCTION verify_func
CPU_PER_SESSION 1000
IDEL_TIME 60 ;
Profile Parameters
Password Settings:
PARAMETERS DESCRIPTION
FAILED_LOGIN_ATTEMPTS Number of failed login attempts before lockout of the
account.
PASSWORD_LOCK_TIME Number of days the account is locked after the specified
number of failed login attempts.
PASSWORD_LIFE_TIME Lifetime of the password in days after which the
password expires.
PASSWORD_GRACE_TIME Grace period in days for changing the password after the
first successful login after the password has expired.
PASSWORD_REUSE_TIME Number of days before a password can be reused.
PASSWORD_REUSE_MAX Maximum number of changes required before a
password can be reused.
PASSWORD_VERIFY_FUNCTION PL / SQL function that perform a password complexity
check before a password is assigned.
Resource Limit:
PARAMETERS DESCRIPTION
CPU_PER_SESSION Total CPU time measured in hundredths of second.
SESSIONS_PER_USER Number of concurrent sessions allowed fro each
username.
CONNECT_TIME Elapsed connect time measured in minutes.

IDLE_TIME Periods of inactive time measured in minutes.
LOGICAL_READS_PER_SESSION Number of data blocks ( physical and logical reads)
PRIVATE_SGA Private space in the SGA measured in bytes ( for Shared
server only )
CPU_PER_CALL CPU time per call in hundredths of seconds.
LOGICAL_READS_PER_CALL Number of data blocks that can be read per call.
Enabling Resource Limits
Enable or disable the enforcement of resource limit by setting the initialization parameters
RESOURCE_LIMIT = TRUE , or by using the ALTER SYSTEM command…
SQL> ALTER SYSTEM SET RESOURCE_LIMIT = TRUE ;
Altering Profile
Use ALTER PROFILE command to change password limits.
SQL> ALTER PROFILE profile_name LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60 ;
Dropping Profile
SQL> DROP PROFILE profile_name ;
SQL> DROP PROFILE profile_name CASCADE ;
CASCADE revokes the profile from the users to whom it was assigned.
Changing Users Quota
SQL> ALTER USER abc QUOTA 10M ON data ;
SQL> ALTER USER abc QUOTA 0M ON data ;
After a quota 0 is assigned, the objects owned by the user remain in the revoked tablespace, but they
cannot be allocated a new space.

Dropping User
SQL> DROP USER user_name ;
SQL> DROP USER user_name CASCADE ;
The CASCADE option drop all objects in the schema before dropping the user. This must be
specified if the schema contains any objects. Users who are currently connected to Oracle server
cannot be dropped.
Granting Privileges
“System Privileges”
SQL> GRANT CREATE SESSION TO abc ;
SQL> GRANT CREATE SESSION TO abc WITH ADMIN OPTION ;
WITH ADMIN OPTION enables the grantee to further grant the privileges or role to other users.
“Object Privileges”
SQL> GRANT SELECT ON emp TO hr ;
SQL> GRANT SELECT ON emp TO hr WITH GRANT OPTION ;
WITH GRANT OPTION enables the grantee to grant object privileges or role to other users or
role.
Revoking Privileges
“System Privileges”
SQL> REVOKE CREATE TABLE FROM abc ;
“Object Privileges”
SQL> REVOKE SELECT ON emp FROM hr ;
Grantors can revoke object privileges from only those users to whom they have granted privileges.

tables and indexes

Creating Temporary Tables

SQL> CREATE GLOBAL TEMPORARY TABLE
hr.employees_temp
AS SELECT * FROM hr.employees ;
Altering Table
“Changing Storage And Block Utilization Parameters”

Some of the storage parameters and any of the block utilization parameters can be modified by using
the ALTER TABLE command.
SQL> ALTER TABLE hr.employees
PCTFREE 30
PCTUSED 50
STORAGE ( NEXT 500K MINEXTENTS 2 MAXEXTENTES 100 ) ;

“Manually Allocating Extents”

Use the following command to allocate an extent to a table.
SQL> ALTER TABLE hr.employees
ALLOCATE EXTENTS ( SIZE 500K
DATAFILE ‘ C:\ORACLE\ORADATA\ORCL\USER.DBF ’ );

“Nonpartitioned Table Reorganization”

SQL> ALTER TABLE hr.employees MOVE TABLESPACE data01;
“Dropping a Column”
SQL> ALTER TABLE hr.employees DROP COLUMN comments
CASCADE CONSTRAINTS CHECKPOINT 1000 ;

In the example, checkpoint occurs every 1000 rows. The table is marked invalid until operation
completed.

“Renaming Column”
SQL> ALTER TABLE hr.employees
RENAME COLUMN hire_date TO start_date ;

“Mark Column as Unused”

SQL> ALTER TABLE hr.employees
SET UNUSED COLUMN comments CASCADE CONSTRAINTS ;

“Drop Unused Column”

SQL> ALTER TABLE hr.employees
DROP UNUSED COLUMNS ;

Creating B-Tree Indexes

SQL> CREATE INDEX hr.employees_indx
ON hr.employees ( last_name )
PCTFREE 30
STORAGE ( INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50 )
TABLESPACE indx ;
Creating Bitmap Indexes
SQL> CREATE BITMAP INDEX orders_id_indx
ON orders ( region_id )
PCTFREE 30
STORAGE ( INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50 )
TABLESPACE indx ;
Altering Indexes

“Changing Storage Parameters”

SQL> ALTER INDEX employees_name_indx
STORAGE ( NEXT 400K MAXEXTENTS 100 ) ;

“Allocating Index Space”

SQL> ALTER INDEX employees_name_indx
ALLOCATE EXTENTS ( SIZE 200K
DATAFILE ‘C:\ORACLE\ORADATA\ORCL\indx.dbf ’) ;

“Deallocating Index Space”

SQL> ALTER INDEX employees_name_indx
DEALLOCATE UNUSED ;

“Rebuilding Indexes”

SQL> ALTER INDEX employees_name_indx REBUILT ;

“Rebuilding Indexes Online”

SQL> ALTER INDEX employees_name_indx REBUILT ONLINE ;

“Coalesing Indexes”

SQL> ALTER INDEX employees_name_indx COALESCE ;

“Checking Index Validity”

SQL> ALTER INDEX employees_name_indx VALIDATE STRUCTURE ;

“Start Usage Monitoring on Indexes”

SQL> ALTER INDEX employees_name_indx MONITORING USAGE ;
“Stop Usage Monitoring on Indexes”
SQL> ALTER INDEX employees_name_indx NOMONITORING USAGE ;
“Dropping Indexes”
SQL> DROP INDEX index_name ;

Creating Tablespaces

Creating Tablespaces

A tablespace can be created using the following commands…
SQL> CREATE TABLESPACE userdata
DATAFILE ‘C:\ORACLE\ORADATA\ORCL\USERDATA01.dbf’ SIZE 5M;

“ Locally Managed Tablespases”

SQL> CREATE TABLESPACE userdata
DATAFILE ‘C:\ORACLE\ORADATA\ORCL\USERDATA01.dbf’ SIZE 5M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

“ Dictionary Managed Tablespases”

SQL> CREATE TABLESPACE userdata
DATAFILE ‘C:\ORACLE\ORADATA\ORCL\USERDATA01.dbf’ SIZE 5M
EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE
( INITIAL 1M NEXT 1M PCTINCREASE 0 ) ;

“ Undo Tablespases”

SQL> CREATE UNDO TABLESPACE undo1
DATAFILE ‘C:\ORACLE\ORADATA\ORCL\UNDO01.dbf’ SIZE 20M;

“ Temporary Tablespases”

SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘C:\ORACLE\ORADATA\ORCL\TEMP01.dbf’ SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;

Creating tablespace using Oracle Enterprise Manager

From OEM Console:

1- Navigate to storage > tablespace
2- Select Create form the right mouse menu.
3- Enter detail in the General tabbed page.
4- Select the type like Temporary or Undo.
5- Click the storage tabbed page and enter the storage information.
6- Click Create.
Migrating a Dictionary Managed tablespace into
Locally managed

To migrate any dictionary managed system tablespace into locally managed tablespace, following
package is used…
SQL> DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL( ‘name of
tablespace’ );
Altering Tablespaces

“Read-Only”
To make tablespace Read-only following command is used.
SQL> ALTER TABLESPACE userdata READ ONLY;

“Offline / Online”

To make tablespace Offline following command is used.
SQL> ALTER TABLESPACE userdata OFFLINE;


To make tablespace Online following command is used.
SQL> ALTER TABLESPACE userdata ONLINE;
NOTE:- SYSTEM , Active UNDO segments and default TEMPORARY tablespace cannot be
taken offline.

“Changing Storage Setting”

Use Alter tablespace commands to change storage setting.
1- SQL> ALTER TABLESPACE userdata MINIMUM EXTENT 2M;
2- SQL> ALTER TABLESPACE userdata
DEFAULT STORAGE ( INITIAL 2M NEXT 2M MAXEXTENT 100 ) ;
NOTE:- Storage settings for locally managed tablespaces cannot be altered.

“Adding Datafile”

To add datafile in tablespace, following command is used.
SQL> ALTER TABLESPACE userdata
ADD DATAFILE ‘C:\ORACLE\ORADATA\ORCL\user_data.dbf ’
SIZE 100M ;

“Resizing Datafile”

To resize data file, following command is used.
SQL> ALTER DATABASE
DATAFILE ‘C:\ORACLE\ORADATA\ORCL\user_data.dbf ’
RESIZE 200M ;
“Moving Datafile”

To move data file from one location to another, following command is used.
1- Through Alter Tablespace, Tablespace must be offline, And target file must exist.
SQL> ALTER TABLESPACE userdata RENAME
DATAFILE ‘C:\ORACLE\ORADATA\ORCL\user_data.dbf ’
TO ‘C:\user_data.dbf ’ ;
2- Through Alter Database, Database must be mounted, And target file must exist.

SQL> ALTER DATABASE RENAME
FILE ‘C:\ORACLE\ORADATA\ORCL\user_data.dbf ’
TO ‘C:\user_data.dbf ’ ;

Dropping Tablespaces

To drop any tablespace, following command is used.
SQL> DROP TABLESPACE tablespace_name
[ INCLUDING CONTENTS [ AND DATAFILES ]
[ CASECADE CONSTRAINTS] ]
where :
INCLUDING CONTENTS = Drops all the segments in the tablespace
AND DATAFILE = Deletes the associated operating system datafiles.
CASECADE CONSTRAINTS = Drops referential constraints outside the tablespace.
For Example:
SQL> DROP TABLESPACE user_data
INCLUDING CONSTENTS AND DATAFILES CASECADE CONSTRAINTS;
NOTE:- The SYSTEM tablespace and the tablespace having active segments can not be drop.

Managing Tablespaces Using OMF
“Creating an OMF tablespace”

SQL> CREATE TABLESPACE ts_name DATAFILE SIZE 20M ;
“Adding datafile to an existing tablespace”
SQL> ALTER TABLESPACE ts_name ADD DATAFILE ;

“Changing default file location”

SQL> ALTER SYSTEM SET
db_create_file_dest = ‘C:\new_location’ ;

Adding Online Redo Log File Group

Adding Online Redo Log File Groups

To create a new group of online redo log files, following SQL statement is used:
SQL> ALTER DATABASE ADD LOGFILE GROUP 3
( ‘ C:\ORACLE\ORADATA\ORCL\log3a.rdo ’ ,
‘ C:\ORACLE\ORADATA\ORCL\log3b.rdo ’ ) SIZE 1M ;
Adding Online Redo Log File Members
We can add new members to existing online redo log file group using the following commands.
SQL> ALTER DATABASE ADD LOGFILE MEMBER
‘ C:\ORACLE\ORADATA\ORCL\log1c.rdo ’ TO GROUP 1 ,
‘ C:\ORACLE\ORADATA\ORCL\log2c.rdo ’ TO GROUP 2 ,
‘ C:\ORACLE\ORADATA\ORCL\log3c.rdo ’ TO GROUP 3 ;
Adding Online Redo Log File Groups and Members
(Using Oracle Enterprise Managers)
To open Oracle Enterprise Manager
Start → Programs → Oracle-OraHome92 → Enterprise Manager Console
To add group or member
1- Navigate to Stroage
2- Click the Redo Log Groups folder
3- Select Create from right mouse menu
4- Select the General tabbed page
5- Complete the information to create the online redo log file group and members
6- Click Create
Dropping Online Redo Log File Groups
To drop group of online redo log files, following SQL statement is used:
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Dropping Online Redo Log File Members
To drop member of online redo log group, following SQL statement is used:
SQL> ALTER DATABASE DROP LOGFILE MEMBER
‘ C:\ORACLE\ORADATA\ORCL\log1c.rdo ’;
Dropping Online Redo Log File Groups and Members
(Using Oracle Enterprise Managers)
To remove group.
1- Navigate to Stroage
2- Click the Redo Log Groups folder
3- Select the online redo log file group we want to remove
4- Select Remove from right – mouse menu
5- Confirm remove.
To remove member.
1- Navigate to storage
2- Expand the redo log group folder and navigate to the group containing the member we want
to remove
3- Select the General tab page
4- Highlight the member
5- Select Remove from the right mouse menu
6- Confirm Remove.
Relocating Or Renaming Redo log files
Step-1. Shutdown the database.
SQL> SHUTDOWN
Step-2. Copy the online redolog file to new location.
Step-3. Startup database in mount mode
SQL> STARTUP MOUNT
Step-4. Rename the online redo log member using
SQL> ALTER DATABSE RENAME FILE
‘C:\ORACLE\ORADATA\ORCL\LOG2A.rdo’ TO

‘C:\ORACLE\LOG2A.rdo’;
Step-5. Open the database.
SQL> ALTER DATABSE OPEN;
Managing Redo log file with OMF
To create online redo log files to be managed by OMF, following parameters must be defined in
“init.ora” file……
DB_CREATE_ONLINE_LOG_DEST_1
DB_CREATE_ONLINE_LOG_DEST_2
To create a new group of online redo log file, the DBA use the the following SQL command….
SQL> ALTER DATABASE ADD LOGFILE;
A group can be added with no file specification.
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
The above SQL statement drops the logfile group 3 and its operating system files associated with
OMF log file member in group 3.

Multiplexing the Control File

Multiplexing the Control File

Note: A control file can be multiplexed (copied) maximum 8 times
“ USING SPFILE “
Step-1. Alter the spfile using :
SQL> ALTER SYSTEM SET control_files =
2. ‘C:\ORACLE\ORADATA\ORCL\CTRL01.CTL’,
3. ‘C:\ORACLE\ORADATA\ORCL\CTRL02.CTL’ SCOPE = SPFILE;
Step-2. Shutdown the database
SQL> SHUTDOWN IMMEDIATE
Step-3. Copy control file at some other location using Operating system → Rename the file → again
paste the file into previous folder from where we have coped it .
Step-4. Start the database
SQL> STARTUP
“ USING PFILE “
Step-1. Shutdown the database
SQL> SHUTDOWN IMMEDIATE
Step-2. Copy control file at some other location using Operating system → Rename the file → again
paste the file into previous folder from where we have coped it
Step-3. Add control files name in pfile(init.ora)
CONTROL_FILES = (‘C:\ORACLE\ORADATA\ORCL\CTRL01.CTL’,
‘C:\ORACLE\ORADATA\ORCL\CTRL02.CTL’)
Step-4. Start the database
SQL> STARTUP