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