Big N Fat DBA

ASM Remote Copy

2010-05-26T22:07:00.001-07:00

ASM Duplicate Active in 11g

2010-05-26T22:06:00.005-07:00

ASM KFED

2010-05-26T22:06:00.001-07:00

Create Multiple Instance on A Single Database

2010-04-09T21:18:00.000-07:00

TO CRAETE A NEW ORACLE INSTANCE (DB_NAME=bnfd INSTANCE_NAME=bnfd1)
-----------------------------------
1.CREATE A NEW DIRECTORY AS THE GLOBAL DATABASE NAME (DB_NAME:bnfd) IN THE PARAMETER FILE
AND EDIT THE PFILE WITH THE NEW INSTANCE NAME AND NEW DATABASE NAME.
example:create a directory as E:\oracle\admin\bnfd and edit the parameter
file after copying.(CREATE THE NEW PARAMETER FILE)
2.CREATE THE NEW PASSWORD FILE IN THE DIRECTORY
E:\>oracle\ora92\database>orapwd FILE=pwdbnfd1.ora PASSWORD=sys ENTRIES=5;
3.If we are using Windows NT then add the service with oradim program
C:\>oradim -new -sid bnfd1 -startmode a -pfile E:\oracle\admin\bnfd\pfile\bnfd.ora
4.Add in the listener.ora the global database name and the sid and restart the lis
tener.
5.Add this in tnsnames.ora with the new service name(SERVICE_NAME=bnfd1).
6.connect sys/sys@bnfd as sysdba
7C:\>oradim -delete -sid bnfd1(To delete a SID)
8C:\>oradim -edit -sid bnfd1 -startmode a -pfile E:\oracle\admin\bnfd\pfile\bnfd.ora;(To edit a sid)

TO CREATE A PARALLEL SERVER(instance=11,12 db_name:hello1)
-------------------------------------------------------------------
1.Do not change the DB_NAME to hello2(keep it hello1)
but change the instance name to 12.
2.do the same steps as that of creating a new instance.
3.In the tnsnames.ora have two entries to connect to two instances, but they will
connect to the same database so that if one instance is down the other instance
can go up and take its place.

copy the folder structure as it is in case of other databases.
TO CREATE A DATABASE FOR THE INSTANCE (11 and 12)(DB_NAME:hello1)
Either give the UNDO tablespace or comment the undo parameters
---------------------------------------------------------------------------
1.Comment all the undo related parameters in the init.ora file for 12 instance.
2.Startup nomount using pfile for 12 instance
3. startup nomount pfile="C:\oracle\admin\catalgdb\scripts\init.ora";
CREATE DATABASE targetdb
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE 'C:\oracle\oradata\targetdb\system01.dbf' SIZE 250M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'C:\oracle\oradata\targetdb\temp01.dbf' SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE 'C:\oracle\oradata\targetdb\undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('C:\oracle\oradata\targetdb\redo01.log') SIZE 102400K,
GROUP 2 ('C:\oracle\oradata\targetdb\redo02.log') SIZE 102400K,
GROUP 3 ('C:\oracle\oradata\targetdb\redo03.log') SIZE 102400K;
spool off
exit;

Database created.

4.alter database mount;
5.alter database archivelog;
6.alter database open;

Run catalog.sql from e:\oracle\ora92\rdbms\admin
Eun catproc.sql from e:\oracle\ora92\rdbms\admin

Go to the e:\oracle\ora92\sqlplus\admin directory, connect as SYSTEM and run @PUPBLD.SQL from the sql prompt.

open the database and create temp and undo tablespace.
make them at least 600mb each
undo tablespace should be named as undo01
edit the init.ora file where all "UNDO" parameters are there and make them uncommented.

It will be possible to start 11/12 in nomount mode.
But if we mount the database hello1 using 11 instance
then if we try to mount the database using 12 instance
then we will get an error
ORA-01102: cannot mount database in EXCLUSIVE mode.
(Any one of the instances 11 or 12 can be used to open the database).

THE STEPS IS THEORITICALLY CORRECT BUT PRACTICALLY THERE SEEMS TO BE SOME PROBLEM
(To solve this problem we have to mount and open the database in shared mode and
this is done using the statement

SQL>ALTER DATABASE MOUNT SHARED;(Parallel server mode)

(This will ensure that whether we can connect the database using SID 11 or 12
but it will open the same database -hello1).)

STEPS TO PREPARE AN AUXILLARY INSTANCE FOR TSPITR
-----------------------------------------------------
1.db_file_name_convert=('e:\oracle\oradata\hello1','e:\oracle\oradata\hello2')
log_file_name_convert=('e:\oracle\oradata\hello1','e:\oracle\oradata\hello2')
lock_name_space=hello2
db_name=hello1 instance_name=12
2.Start the auxiliary instance in nomount mode.

TSPITR-Tablespace Point In Time Recovery
----------------------------------------------------------
Before TSPITR can continue ensure that the database backup is taken and target
database is open and auxillary database is in nomount mode and follow the rules
of tspitr.It should be noted that immediately after TSPITR is done backup
should be taken, otherwise next time TSPITR will give an error.

Shutdown the auxiliary instance immediately after performing tspitr.

RMAN> connect target backup_admin/backup_admin@hello1

connected to target database: hello1 (DBID=3960930000)

RMAN> connect catalog rman_bnfd/rman_bnfd@kodiak

connected to recovery catalog database

RMAN> connect auxiliary sys/sys@hello2

connected to auxiliary database: hello1 (not mounted)

RMAN> RECOVER tablespace users UNTIL TIME "to_date('14-10-2004 15:30:00','dd-mm-yyyy hh24:mi:ss')"
2> ;

Starting recover at 14-OCT-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=20 devtype=DISK

printing stored script: Memory Script
{
# set the until clause
set until time "to_date('14-10-2004 15:30:00','dd-mm-yyyy hh24:mi:ss')";
# restore the controlfile
restore clone controlfile to clone_cf;
# replicate the controlfile
replicate clone controlfile from clone_cf;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog after controlfile restore
resync catalog;
}
executing script: Memory Script

executing command: SET until clause

Starting restore at 14-OCT-04

allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=12 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring controlfile
output filename=E:\ORACLE\ORADATA\hello2\CONTROL01.CTL
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\BACKUP\CONTROLFILE\%ORACLE_SID_CTRL_DATE_C-3960930000-20041014-0
5.BAK tag=null params=NULL
channel ORA_AUX_DISK_1: restore complete
Finished restore at 14-OCT-04

replicating controlfile
input filename=E:\ORACLE\ORADATA\hello2\CONTROL01.CTL
output filename=E:\ORACLE\ORADATA\hello2\CONTROL02.CTL
output filename=E:\ORACLE\ORADATA\hello2\CONTROL03.CTL

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
full resync complete

printing stored script: Memory Script
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "to_date('14-10-2004 15:30:00','dd-mm-yyyy hh24:mi:ss')";
plsql <<<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'USERS' ||' offline for recover';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed then
null;
end; >>>;
# set a destination filename for restore
set newname for datafile 1 to
"E:\ORACLE\ORADATA\hello2\SYSTEM01.DBF";
# set a destination filename for restore
set newname for datafile 2 to
"E:\ORACLE\ORADATA\hello2\UNDOTBS01.DBF";
# set a destination filename for restore
set newname for datafile 9 to
"E:\ORACLE\ORADATA\hello1\USERS01.DBF";
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 9;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 9 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1";
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing script: Memory Script

executing command: SET until clause

sql statement: alter tablespace USERS offline for recover

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 14-OCT-04

using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to E:\ORACLE\ORADATA\hello2\SYSTEM01.DBF
restoring datafile 00002 to E:\ORACLE\ORADATA\hello2\UNDOTBS01.DBF
restoring datafile 00009 to E:\ORACLE\ORADATA\hello1\USERS01.DBF
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\BACKUP\DATABASETEST\TEST_0AG2HB1G_1_1.BAK tag=TAG20041014T151351
params=NULL
channel ORA_AUX_DISK_1: restore complete
Finished restore at 14-OCT-04

datafile 9 switched to datafile copy
input datafilecopy recid=4 stamp=539537696 filename=E:\ORACLE\ORADATA\hello1\US
ERS01.DBF

sql statement: alter database datafile 1 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 9 online

Starting recover at 14-OCT-04
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 8 is already on disk as file E:\ORACLE\ORA92\RDBMS
\ARC00008.001
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=7
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\BACKUP\ARCHIVELOG\%ORACLE_SID_DATA_DATA.BAK tag=TAG20041014T1515
34 params=NULL
channel ORA_AUX_DISK_1: restore complete
archive log filename=E:\ORACLE\ORA92\RDBMS\ARC00007.001 thread=1 sequence=7
archive log filename=E:\ORACLE\ORA92\RDBMS\ARC00008.001 thread=1 sequence=8
media recovery complete
Finished recover at 14-OCT-04

database opened

printing stored script: Memory Script
{
# export the tablespaces in the recovery set
host 'exp userid =\"sys/sys@hello2 as sysdba\" point_in_time_recover=y tablespa
ces=
USERS file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"backup_admin/backup_admin@hello1 as sysdba\" point_in_time_
recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace USERS online";
sql "alter tablespace USERS offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog after tspitr finished
resync catalog;
}
executing script: Memory Script

Export: Release 9.2.0.1.0 - Production on Thu Oct 14 15:35:22 2004

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

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete

database closed
database dismounted
Oracle instance shut down

Import: Release 9.2.0.1.0 - Production on Thu Oct 14 15:35:41 2004

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

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace USERS online

sql statement: alter tablespace USERS offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog
full resync complete
Finished recover at 14-OCT-04

ANOTHER EXAMPLE OF TSPITR RECOVERY
-----------------------------------
RMAN> connect target backup_admin/backup_admin@hello1

connected to target database: hello1 (DBID=3960930000)

RMAN> connect catalog rman_bnfd/rman_bnfd@kodiak

connected to recovery catalog database

RMAN> connect auxiliary sys/sys@hello2

connected to auxiliary database: hello1 (not mounted)

RMAN> RECOVER tablespace testbnfd UNTIL TIME "to_date('14-10-2004 16:20:00','dd
-mm-yyyy hh24:mi:ss')"
2> ;

Starting recover at 14-OCT-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=8 devtype=DISK

printing stored script: Memory Script
{
# set the until clause
set until time "to_date('14-10-2004 16:20:00','dd-mm-yyyy hh24:mi:ss')";
# restore the controlfile
restore clone controlfile to clone_cf;
# replicate the controlfile
replicate clone controlfile from clone_cf;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog after controlfile restore
resync catalog;
}
executing script: Memory Script

executing command: SET until clause

Starting restore at 14-OCT-04

allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=12 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring controlfile
output filename=E:\ORACLE\ORADATA\hello2\CONTROL01.CTL
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\BACKUP\CONTROLFILE\%ORACLE_SID_CTRL_DATE_C-3960930000-20041014-0
B.BAK tag=null params=NULL
channel ORA_AUX_DISK_1: restore complete
Finished restore at 14-OCT-04

replicating controlfile
input filename=E:\ORACLE\ORADATA\hello2\CONTROL01.CTL
output filename=E:\ORACLE\ORADATA\hello2\CONTROL02.CTL
output filename=E:\ORACLE\ORADATA\hello2\CONTROL03.CTL

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
full resync complete

printing stored script: Memory Script
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "to_date('14-10-2004 16:20:00','dd-mm-yyyy hh24:mi:ss')";
plsql <<<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'TESTbnfd' ||' offline for recover';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed then
null;
end; >>>;
# set a destination filename for restore
set newname for datafile 1 to
"E:\ORACLE\ORADATA\hello2\SYSTEM01.DBF";
# set a destination filename for restore
set newname for datafile 2 to
"E:\ORACLE\ORADATA\hello2\UNDOTBS01.DBF";
# set a destination filename for restore
set newname for datafile 11 to
"E:\ORACLE\ORADATA\hello1\TESTbnfd.ORA";
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 11;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 11 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "TESTbnfd", "SYSTEM", "UNDOTBS1";
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing script: Memory Script

executing command: SET until clause

sql statement: alter tablespace TESTbnfd offline for recover

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 14-OCT-04

using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to E:\ORACLE\ORADATA\hello2\SYSTEM01.DBF
restoring datafile 00002 to E:\ORACLE\ORADATA\hello2\UNDOTBS01.DBF
restoring datafile 00011 to E:\ORACLE\ORADATA\hello1\TESTbnfd.ORA
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\BACKUP\DATABASETEST\TEST_0JG2HEPM_1_1.BAK tag=TAG20041014T161758
params=NULL
channel ORA_AUX_DISK_1: restore complete
Finished restore at 14-OCT-04

datafile 11 switched to datafile copy
input datafilecopy recid=6 stamp=539547280 filename=E:\ORACLE\ORADATA\hello1\TE
STbnfd.ORA

sql statement: alter database datafile 1 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 11 online

Starting recover at 14-OCT-04
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 14 is already on disk as file E:\ORACLE\ORA92\RDBM
S\ARC00014.001
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=13
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\BACKUP\DATABASETEST\TEST_0NG2HEST_1_1.BAK tag=TAG20041014T161938
params=NULL
channel ORA_AUX_DISK_1: restore complete
archive log filename=E:\ORACLE\ORA92\RDBMS\ARC00013.001 thread=1 sequence=13
archive log filename=E:\ORACLE\ORA92\RDBMS\ARC00014.001 thread=1 sequence=14
media recovery complete
Finished recover at 14-OCT-04

database opened

printing stored script: Memory Script
{
# export the tablespaces in the recovery set
host 'exp userid =\"sys/sys@hello2 as sysdba\" point_in_time_recover=y tablespa
ces=
TESTbnfd file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"backup_admin/backup_admin@hello1 as sysdba\" point_in_time_
recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace TESTbnfd online";
sql "alter tablespace TESTbnfd offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog after tspitr finished
resync catalog;
}
executing script: Memory Script

Export: Release 9.2.0.1.0 - Production on Thu Oct 14 18:15:12 2004

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

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
For tablespace TESTbnfd ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TEST1
. . exporting table TEST2
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete

database closed
database dismounted
Oracle instance shut down

Import: Release 9.2.0.1.0 - Production on Thu Oct 14 18:15:36 2004

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

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing bnfd's objects into bnfd
. . importing table "TEST1"
. . importing table "TEST2"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace TESTbnfd online

sql statement: alter tablespace TESTbnfd offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog
full resync complete
Finished recover at 14-OCT-04

RMAN>

RMAN>

AFTER THE TABLESPACE POINT IN TIME RECOVERY IS OVER THE TABLESPACE SHOULD BE MADE ONLINE FOR
FURTHUR TRANSACTIONS.
2

CREATING A CLONE DATABASE(hello1 database being duplicated to hello3).BEFORE CLONING THE DATABASE
IT SHOULD BE SEEN THAT THE SCN IS SET TILL THE LAST ARCHIVELOG SCN.THIS INFORMATION CAN BE OBTAINED
BY ISSUING THE SQL STATEMENT SELECT * FROM V$DATABASE;)
connect to auxiliary database before starting the database cloning
----------------------------
RMAN> RUN
2> {
3> set until scn 2338189;
4> duplicate target database to hello3;
5> }

executing command: SET until clause

Starting Duplicate Db at 04-DEC-04
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=12 devtype=DISK

printing stored script: Memory Script
{
set until scn 2338189;
set newname for datafile 1 to
"E:\ORACLE\ORADATA\hello3\SYSTEM01.DBF";
set newname for datafile 2 to
"E:\ORACLE\ORADATA\hello3\UNDO01";
set newname for datafile 3 to
"E:\ORACLE\ORADATA\hello3\TEMP01";
set newname for datafile 4 to
"E:\ORACLE\ORADATA\hello3\bnfdTEST";
restore
check readonly
clone database
;
}
executing script: Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 04-DEC-04

using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to E:\ORACLE\ORADATA\hello3\SYSTEM01.DBF
restoring datafile 00002 to E:\ORACLE\ORADATA\hello3\UNDO01
restoring datafile 00003 to E:\ORACLE\ORADATA\hello3\TEMP01
restoring datafile 00004 to E:\ORACLE\ORADATA\hello3\bnfdTEST
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\BACKUP\DATABASETEST\TEST_37G6ON27_1_1.BAK tag=TAG20041203T232702
params=NULL
channel ORA_AUX_DISK_1: restore complete
Finished restore at 04-DEC-04
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "hello3" RESETLOGS ARCHIVE
LOG
MAXLOGFILES 40
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 16
MAXLOGHISTORY 1815
LOGFILE
GROUP 1 ( 'E:\ORACLE\ORADATA\hello3\REDO01.LOG' ) SIZE 5242880 REUSE,
GROUP 2 ( 'E:\ORACLE\ORADATA\hello3\REDO02.LOG' ) SIZE 5242880 REUSE,
GROUP 3 ( 'E:\ORACLE\ORADATA\hello3\REDO03.LOG' ) SIZE 5242880 REUSE
DATAFILE
'E:\ORACLE\ORADATA\hello3\SYSTEM01.DBF'
CHARACTER SET WE8ISO8859P1

printing stored script: Memory Script
{
switch clone datafile all;
}
executing script: Memory Script

datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=543977044 filename=E:\ORACLE\ORADATA\hello3\UN
DO01
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=543977044 filename=E:\ORACLE\ORADATA\hello3\TE
MP01
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=543977044 filename=E:\ORACLE\ORADATA\hello3\SU
MANTEST

printing stored script: Memory Script
{
set until scn 2338189;
recover
clone database
delete archivelog
;
}
executing script: Memory Script

executing command: SET until clause

Starting recover at 04-DEC-04
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 4 is already on disk as file E:\ORACLE\ORA92\RDBMS
\ARC00004.001
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=3
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=C:\BACKUP\ARCHIVELOG\%ORACLE_SID_DATA_DATA_U%.BAK tag=TAG20041203T2
32756 params=NULL
channel ORA_AUX_DISK_1: restore complete
archive log filename=E:\ORACLE\ORA92\DATABASE\hello3ARC00003.001 thread=1 seque
nce=3
channel clone_default: deleting archive log(s)
archive log filename=E:\ORACLE\ORA92\DATABASE\hello3ARC00003.001 recid=1 stamp=
543977050
archive log filename=E:\ORACLE\ORA92\RDBMS\ARC00004.001 thread=1 sequence=4
media recovery complete
Finished recover at 04-DEC-04

printing stored script: Memory Script
{
shutdown clone;
startup clone nomount ;
}
executing script: Memory Script

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 185670316 bytes

Fixed Size 453292 bytes
Variable Size 159383552 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "hello3" RESETLOGS ARCHIVE
LOG
MAXLOGFILES 40
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 16
MAXLOGHISTORY 1815
LOGFILE
GROUP 1 ( 'E:\ORACLE\ORADATA\hello3\REDO01.LOG' ) SIZE 5242880 REUSE,
GROUP 2 ( 'E:\ORACLE\ORADATA\hello3\REDO02.LOG' ) SIZE 5242880 REUSE,
GROUP 3 ( 'E:\ORACLE\ORADATA\hello3\REDO03.LOG' ) SIZE 5242880 REUSE
DATAFILE
'E:\ORACLE\ORADATA\hello3\SYSTEM01.DBF'
CHARACTER SET WE8ISO8859P1

printing stored script: Memory Script
{
catalog clone datafilecopy "E:\ORACLE\ORADATA\hello3\UNDO01";
catalog clone datafilecopy "E:\ORACLE\ORADATA\hello3\TEMP01";
catalog clone datafilecopy "E:\ORACLE\ORADATA\hello3\bnfdTEST";
switch clone datafile all;
}
executing script: Memory Script

cataloged datafile copy
datafile copy filename=E:\ORACLE\ORADATA\hello3\UNDO01 recid=1 stamp=543977210

cataloged datafile copy
datafile copy filename=E:\ORACLE\ORADATA\hello3\TEMP01 recid=2 stamp=543977210

cataloged datafile copy
datafile copy filename=E:\ORACLE\ORADATA\hello3\bnfdTEST recid=3 stamp=5439772
11

datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=543977210 filename=E:\ORACLE\ORADATA\hello3\UN
DO01
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=543977210 filename=E:\ORACLE\ORADATA\hello3\TE
MP01
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=543977211 filename=E:\ORACLE\ORADATA\hello3\SU
MANTEST

printing stored script: Memory Script
{
Alter clone database open resetlogs;
}
executing script: Memory Script

database opened
Finished Duplicate Db at 04-DEC-04

Optach for RDBMS Oracle_HOme

2010-04-09T20:37:00.001-07:00

connect as oracle
set env of instance

1) down load the patch from updates.oracle.com to $ORACLE_HOME/patch

unzip the patch.

During uzipping please note down the directory it is creating.

cd to that directory. copy the directory path(copied path).

2)

export PATH=/OPatch:$PATH

export PATH=/opt/perl/bin:$PATH

3)
Apply the patch by this command

opatch apply -invPtrLoc /oraInst.loc /

Optimizer Statstics & Index Usage!!!

2010-04-09T20:28:00.000-07:00

Optimizer Statistics -

To Verify -

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN,
TO_CHAR(LAST_ANALYZED, 'MM/DD/YYYY HH24:MI:SS')
FROM DBA_TABLES
WHERE TABLE_NAME IN ('SO_LINES_ALL','SO_HEADERS_ALL','SO_LAST_ALL');

TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
------------ -------- ------- ----------- -------------
SO_HEADERS_ALL 1632264 209014 149 07/29/2009 00:59:51
SO_LINES_ALL 10493845 1922196 263 07/29/2009 01:16:09
SO_LAST_ALL

Note - SO_LAST_ALL has no statistics.

Verifying Index Statistics
To verify that index statistics are available and assist you in determining which are the best indexes to use in an application, execute the following statement against the dictionary DBA_INDEXES view:

SQL> SELECT INDEX_NAME "NAME", NUM_ROWS, DISTINCT_KEYS "DISTINCT",
1 LEAF_BLOCKS, CLUSTERING_FACTOR "CF", BLEVEL "LEVEL",
2 AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY"
3 FROM DBA_INDEXES
4 WHERE owner = 'SH'
5* ORDER BY INDEX_NAME;

NAME NUM_ROWS DISTINCT LEAF_BLOCKS CF LEVEL ALFBPKEY
------------------------------ ---------- ---------- ----------- ---------- ---------- ----------
CUSTOMERS_PK 50000 50000 454 4405 2 1
PRODUCTS_PK 10000 10000 90 1552 1 1
PRODUCTS_PROD_CAT_IX 10000 4 99 4422 1 24
PRODUCTS_PROD_SUBCAT_IX 10000 37 170 6148 2 4
SALES_PROD_BIX 6287 909 1480 6287 1 1
SALES_PROMO_BIX 4727 459 570 4727 1 1

6 rows selected.

Optimizer Index Determination Criteria
The optimizer uses the following criteria when determining which index to use:

Number of rows in the index (cardinality)

Number of distinct keys. These define the selectivity of the index.

Level or height of the index. This indicates how deeply the data 'probe' must search in order to find the data.

Number of leaf blocks in the index. This is the number of I/Os needed to find the desired rows of data.

Clustering factor (CF). This is the collocation amount of the index block relative to data blocks. The higher the CF, the less likely the optimizer is to select this index.

Average leaf blocks per key (ALFBKEY). Average number of leaf blocks in which each distinct value in the index appears, rounded to the nearest integer. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is always one.

Determining if You Have Chosen the Right Index
Use the following notes to assist you in deciding whether you have chosen an appropriate index for a table, data, and query:

DISTINCT
Consider index ap_invoices_n3, the number of distinct keys is two. The resulting selectivity based on index ap_invoices_n3 is poor, and the optimizer is not likely to use this index. Using this index fetches 50% of the data in the table. In this case, a full table scan is cheaper than using index ap_invoices_n3.

Index Cost Tie
The optimizer uses alphabetic determination: If the optimizer determines that the selectivity, cost, and cardinality of two finalist indexes is the same, then it uses the two indexes' names as the deciding factor. It chooses the index with name beginning with a lower alphabetic letter or number.

Verifying Column Statistics
To verify that column statistics are available, execute the following statement against the dictionary's DBA_TAB_COL_STATISTICS view:

SQL> SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, DENSITY

FROM DBA_TAB_COL_STATISTICS
WHERE TABLE_NAME ="PA_EXPENDITURE_ITEMS_ALL"
ORDER BY COLUMN_NAME;

This returns the following data:

COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS DENSITY
------------------------------ ------------ ---------- ----------- ----------
BURDEN_COST 4300 71957 1 .000232558
BURDEN_COST_RATE 675 7376401 1 .001481481
CONVERTED_FLAG 1 16793903 1 1
COST_BURDEN_DISTRIBUTED_FLAG 2 15796 1 .5
COST_DISTRIBUTED_FLAG 2 0 1 .5
COST_IND_COMPILED_SET_ID 87 6153143 1 .011494253
EXPENDITURE_ID 1171831 0 1 8.5337E-07
TASK_ID 8648 0 1 .000115634
TRANSFERRED_FROM_EXP_ITEM_ID 1233787 15568891 1 8.1051E-07

Verifying column statistics are important for the following conditions:

Join conditions

When the WHERE clause includes a column(s) with a bind variable; for example:

column x = :variable_y

In these cases, the stored column statistics can be used to get a representative cardinality estimation for the given expression.

Consider the data returned in the previous example.

NUM_DISTINCT Column Statistic
Low
The number of distinct values for the columns CONVERTED_FLAG is one. In this case this column has only one value. If in the WHERE clause, then there is a bind variable on column CONVERTED_FLAG = :variable_y, say. If CONVERTED_FLAG is low, as the case in this example, then this leads to poor selectivity, and CONVERTED_FLAG is a poor candidate to be used as the index.

Column COST_BURDEN_DISTRIBUTED_FLAG: NUM_DISTINCT = 2. Likewise, this is low. COST_BURDEN_DISTRIBUTED_FLAG is not a good candidate for an index unless there is much skew or there are a lot of nulls. If there is data skew of, say, 90%, then 90% of the data has one particular value and 10% of the data has another value. If the query only needs to access the 10%, then a histogram is needed on that column in order for the optimizer to recognize the skew and use an index on this column.

High
NUM_DISTINCT is more than 1 million for column EXPEDITURE_ID. If there is a bind variable on column EXPENDITURE_ID, then this leads to high selectivity (implying high density of data on this column). In other words, EXPENDITURE_ID is a good candidate to be used as the index.

NUM_NULL Column Statistic
NUM_NULLS indicates the number of null statistics.

Low
For example, if a single column index has few nulls, such as the COST_DISTRIBUTED_FLAG column, and if this column is used as the index, then the resulting data set is large.

High
If there are many nulls on a particular column, such as the CONVERTED_FLAG column, and if this column is used as the index, then the resulting data set is small. This means that COST_DISTRIBUTED_FLAG is a more appropriate column to index.

DENSITY Column Statistic
This indicates the density of the values of that column. This is calculated by 1 over NUM_DISTINCT.

Column Statistics and Join Methods
Column statistics are useful to help determine the most efficient join method, which, in turn, is also based on the number of rows returned.

Services in Oracle 10g and 11g

2010-04-09T18:01:00.003-07:00

Cluster Databases in enterprise

2010-04-09T18:01:00.001-07:00

Managing ASM in an Enterprise

2010-04-09T18:00:00.003-07:00

OEM - The Grid Control

2010-04-09T18:00:00.001-07:00

Database 11gR2 - New Features!!!

2010-04-09T17:59:00.001-07:00

Blogger Buzz: Blogger integrates with Amazon Associates

2010-04-09T17:59:00.000-07:00

Blogger Buzz: Blogger integrates with Amazon Associates

Advanced Back up using RMAN

2010-01-16T16:50:00.001-08:00

Best Practices For Oracle Database Adminstrators

2010-01-16T16:49:00.000-08:00

How to determine which Unix shell you are using

2009-12-16T13:29:00.000-08:00

How to determine which Unix shell you are using:

> env | grep SHELL

-or-

> echo $SHELL

-or-

> ps -f ....Will provide a full listing of processes associated
with the current terminal, one of which will be the
shell process.

-or-

> setenv ....On a C shell this will return the current
environment, while other shells will return an error.

Please Note:
The following examples use 'ORACLE_HOME' as the variable name.

BOURNE SHELL(sh):
-----------------

To set environment variables within a Bourne Shell (sh), the variable must be
initialized locally, then exported globally:

> ORACLE_HOME=/u01/app/oracle/product/8.1.7
...defines ORACLE_HOME locally to the shell

> export ORACLE_HOME
...makes it globally available to other processes started from
this shell

To have a variable set automatically when you log into the Bourne Shell of your
Unix server:

Add the above lines (minus the '>' prompt) to the hidden '.profile'
file in your $HOME directory.

If you make changes to your '.profile' and want those changes propagated to
your current running environment (without having to log out, then back in):

> cd $HOME
> . .profile

To unset environment variables within a Bourne Shell (sh):

> unset ORACLE_HOME

To check what an environment variable is set to:

> env | grep ORACLE_HOME

KORN SHELL(ksh):
----------------

To set environment variables within a Korn Shell (ksh), you can use the Bourne
syntax show above, or use the streamlined Korn Shell syntax:

> export ORACLE_HOME=/u01/app/oracle/product/8.1.7

To have a variable set automatically when you log into the Korn Shell of your
Unix server:

Add the above lines (minus the '>' prompt) to the hidden '.profile'
file in your $HOME directory.

If you make changes to your '.profile' and want those changes propagated to
your current running environment (without having to log out, then back in):

> cd $HOME
> . .profile

To unset environment variables within a Korn Shell (ksh), use the same syntax
as you would in a Bourne Shell (sh):

> unset ORACLE_HOME

To check what an environment variable is set to:

> env | grep ORACLE_HOME

C SHELL(csh):
-------------

To set environment variables within a C Shell (csh):

> setenv ORACLE_HOME /u01/app/oracle/product/11.1.0

DBMS_STATS

2009-04-01T15:33:00.000-07:00

Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement

The optimizer statistics are stored in the data dictionary.

Because the objects in a database can be constantly changing, statistics must be regularly updated so that they accurately describe these database objects. Statistics are maintained automatically by Oracle or you can maintain the optimizer statistics manually using the DBMS_STATS package.

Automatic Statistics Gathering:

Oracle gathers statistics on all database objects automatically and maintains those statistics in a regularly-scheduled maintenance job. Automated statistics collection eliminates many of the manual tasks associated with managing the query optimizer, and significantly reduces the chances of getting poor execution plans because of missing or stale statistics.

Optimizer statistics are automatically gathered with the job GATHER_STATS_JOB. This job gathers statistics on all objects in the database which have:

Missing statistics

Stale statistics

This job is created automatically at database creation time and is managed by the Scheduler. The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.

It calls DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure.

Automatic statistics gathering is enabled by default when a database is created, or when a database is upgraded from an earlier database release. You can verify that the job exists by viewing the DBA_SCHEDULER_JOBS view:

SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

SELECT owner, job_name, enabled FROM dba_scheduler_jobs;

OWNER JOB_NAME ENABLE

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

SYS PURGE_LOG TRUE

SYS GATHER_STATS_JOB TRUE

SYS TEST_FULL_JOB_DEFINITION TRUE

SYS TEST_PROG_SCHED_JOB_DEFINITION TRUE

SYS TEST_PROG_JOB_DEFINITION TRUE

SYS TEST_SCHED_JOB_DEFINITION TRUE

Manual Statistics:

Because the automatic statistics gathering runs during an overnight batch window, the statistics on tables which are significantly modified during the day may become stale. There are typically two types of such objects:

Volatile tables that are being deleted or truncated and rebuilt during the course of the day.

Objects which are the target of large bulk loads which add 10% or more to the object's total size.

Statistics are gathered using the DBMS_STATS package. This PL/SQL package is also used to modify, view, export, import, and delete statistics.

The DBMS_STATS package can gather statistics on table and indexes, and well as individual columns and partitions of tables

Procedures in DBMS_STATS Package:

GATHER_INDEX_STATS === Index statistics

GATHER_TABLE_STATS === Table, column, and index statistics

GATHER_SCHEMA_STATS === Statistics for all objects in a schema

GATHER_DICTIONARY_STATS === Statistics for all dictionary objects, for all system schemas, including SYS and SYSTEM and other optional schemas

GATHER_DATABASE_STATS === Statistics for all objects in a database

When to Gather Statistic:

1.For an application in which tables are being incrementally modified

2.For tables which are being substantially modified in batch operations, such as with bulk loads

3.For partitioned tables, there are often cases in which only a single partition is modified.In those cases, statistics can be gathered only on those partitions rather than gathering statistics for the entire table

System Statistics

Describe the system's hardware characteristics, such as I/O and CPU performance and utilization, to the query optimizer. When choosing an execution plan, the optimizer estimates the I/O and CPU resources required for each query

Gather, View, Modify or Delete optimizer statistics for database objects:

Subprocedures:

GATHER_DATABASE_STATS

(estimate_percent,block_sample,method_opt,degree, granularity,cascade,stattab,

statid, options,statown,gather_sys,no_invalidate,gather_temp,gather_fixed,stattype);

GATHER_INDEX_STATS

(ownname,indname,partname,estimate_percent,stattab,statid

statown,degree,granularity,no_invalidate,stattype);

GATHER_SCHEMA_STATS

(ownname,estimate_percent,block_sample,method_opt,degree,granularity,cascade,

stattab,statid,options,statown,no_invalidate,gather_temp,gather_fixed);

GENERATE_STATS

(ownname,objname,organized);

GATHER_SYSTEM_STATS

(gathering_mode,interval,stattab,statid,statown);

GATHER_TABLE_STATS

(ownname,tabname,partname,estimate_percent,block_sample,method_opt,

degree,granularity,cascade,stattab,statid,statown,no_invalidate,stattype);

partname         VARCHAR2 DEFAULT NULL,    estimate_percent NUMBER   DEFAULT NULL,     block_sample     BOOLEAN  DEFAULT FALSE,    method_opt       VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',    degree           NUMBER   DEFAULT NULL,    granularity      VARCHAR2 DEFAULT 'DEFAULT',     cascade          BOOLEAN  DEFAULT FALSE,    stattab          VARCHAR2 DEFAULT NULL,     statid           VARCHAR2 DEFAULT NULL,    statown          VARCHAR2 DEFAULT NULL,    no_invalidate    BOOLEAN  DEFAULT FALSE

PREPARE_COLUMN_VALUES

(srec,values);

SET_COLUMN_STATS

SET_INDEX_STATS

SET_SYSTEM_STATS

SET_TABLE_STATS

CONVERT_RAW_VALUE

GET_COLUMN_STATS

GET_INDEX_STATS

GET_SYSTEM_STATS

GET_TABLE_STATS

DELETE_COLUMN_STATS

(ownname,tabname,colname,partname,stattab,statid,

cascade_parts,statown,no_invalidate,force);

DELETE_DATABASE_STATS

(stattab,statid,statown,no_invalidate,stattype,force);

DELETE_INDEX_STATS

(ownname,indname,partname,stattab,statid,cascade_parts,statown

no_invalidate,stattype,force);

DELETE_SCHEMA_STATS

(ownname,stattab,statid,statown,no_invalidate

stattype,force);

DELETE_SYSTEM_STATS

(stattab,statid,statown);

DELETE_TABLE_STATS

(ownname,tabname,partname,stattab,statid,cascade_parts,cascade_columns,

cascade_indexes,statown,no_invalidate,stattype,force);

CREATE_STAT_TABLE

DROP_STAT_TABLE

EXPORT_COLUMN_STATS

EXPORT_INDEX_STATS

EXPORT_SYSTEM_STATS

EXPORT_TABLE_STATS

EXPORT_SCHEMA_STATS

EXPORT_DATABASE_STATS

IMPORT_COLUMN_STATS

IMPORT_INDEX_STATS

IMPORT_SYSTEM_STATS

IMPORT_TABLE_STATS

IMPORT_SCHEMA_STATS

IMPORT_DATABASE_STATS

FLUSH_SCHEMA_MONITORING_INFO

FLUSH_DATABASE_MONITORING_INFO

ALTER_SCHEMA_TABLE_MONITORING

ALTER_DATABASE_TABLE_MONITORING

Example:

EXEC DBMS_STATS.gather_schema_stats('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);

EXEC DBMS_STATS.gather_schema_stats (ownname => 'SCOTT', cascade =>true,estimate_percent => dbms_stats.auto_sample_size);

EXEC DBMS_STATS.delete_schema_stats('SCOTT');

CODE OPTIMISATION TIPS

2009-03-31T09:27:00.000-07:00

1. Avoid using functions on indexed columns

Example:

where to_char(emp_no) = ‘121212’
The index on emp_no will be suppressed.

Solutions: where emp_no = to_number(’121212’)

2.Comparison of wrong data types, suppresses the index internally

where char_data = 123
The index on char_data will be suppressed as oracle re-writes the query.
Solution:
Avoid such errors
where char_data = to_char(123)

3.Oracle does not make use of an index on a column if NOT IN is present.
Example:
select * from emp where deptno not in (select deptno from dept where deptstatus = ‘A’)
Solution:
Use NOT EXISTS instead of NOT IN
Example: select * from emp where not exists (select ‘X’ from dept where deptstatus = ‘A’ and dept. deptno = emp. deptno).

4.the index on brand will not be used.
Example:
select brand, sum(price) from products group by brand having brand = ‘Arrow’;
Solution:
Replace HAVING with a WHERE clause where ever possible.
Example:
select brand, sum(price) from products where brand = ‘Arrow’ group by brand;

5.Use of nested statements slows down the query execution
Example:
select count(*) from products where itemcode in (Select itemcode from clothing where material = ‘Cotton’);
Solution:
Avoid nested selects.
Example:
select count(*) from products P, clothing C
where P.barcode = C.barcode and C.material = ‘Cotton’;

6.Use of NVL() suppresses index usage.
Example:
where NVL(this_value, 99) = other_value
Try to re-write NVLs.
Example:
where (this_value is null and other_value = 99) OR
(this_value is not null and this_value = other_value)

7.Composite Indexes are not used if the leading indexed column is not present in the WHERE clause.
Example: select count(*) from products where price <>

Solution: The leading column of the index must be present in the WHERE clause in order to hit the index.

select count(*) from products where brand > ‘0’ and price <>

rdbms patch

2009-03-29T15:25:00.000-07:00

Check which rdbms patches are applied in uor database -

/usr/bin/perl $ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc

$ORACLE_HOME/oraInst.loc
$ /home/orart02

$ opatch versionInvoking OPatch 10.2.0.3.0
OPatch Version: 10.2.0.3.0
OPatch succeeded.

$ /home/orart02

$ which opatch

/apps/rt02/oracle/product/10201/OPatch/opatch

$ which perl

/usr/bin/perl

$ /home/orart02 $ /usr/bin/perl /apps/rt02/oracle/product/10201/OPatch/opatch lsinventory -invPtrLoc

$ORACLE_HOME/oraInst.loc

Basic checks for a new Daatabase

2009-03-29T14:51:00.000-07:00

When You will be given an 'USER NAME' and 'PASSWORD' of a new database and a new environment then the basic things you need to check are below,

Goto $ORACLE_HOME/dbs and see the parameter file ,SPFILE

uname -a

$echo $oracle_HOME

$.ps-efgrep pmon --- to check databases

$echo $SID ---

$echo $oracle_sid

$ ps -efgrep pmon ---It ll show u all the databases
user02 319658 1 0 Feb 24 - 3:37 ora_pmon_sun
user02 663636 1 0 Feb 28 - 3:52 ora_pmon_MOON
user02 2265204 1224816 0 12:56:55 pts/0 0:00 grep pmon

$ grep env *To see all the env variables

$sqlplus "/as sysdba"

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

SQL> select status from v$instance;
STATUS
------------
OPEN

SQL> show parameter control
NAME TYPE VALUE
control_file_record_keep_time integer 7
control_files string /apps/user02/oracle/admin/cntr l_rt02.dbf, /apps/user02/oracle/admin/cnrtl_rt02.dbf,/apps/user02/oracle/admin/cntrl_rt03.dbf

SQL>show parameter dump;

NAME TYPE VALUE
background_core_dump string partial
background_dump_dest string /apps/user02/oracle/admin/
bdumpcore_dump_dest string /apps/user02/oracle/admin/
cdumpmax_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /apps/user02/oracle/admin/udump

SQL> select * from v$log;

SQL> select * from v$logfile

SQL> show parameter undo
NAME TYPE VALUE
undo_management string AUTO
undo_retention integer 3600
undo_tablespace string undo_ts

SQL> select file_name,sum(bytes)/1024/1024 from dba_data_files where tablespace_name='UNDO_TS' group by file_name;

SQL> col FILE_NAME for a60
SQL>set lines 200
SQL> /
SQL> select FILE_NAME, TABLESPACE_NAME,(bytes)/1024/1024 from dba_data_files;
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space where tablespace_name='SYSTEM' group by tablespace_name;

SQL> select AUTOEXTENSIBLE,FILE_NAME,TABLESPACE_NAME from dba_data_files where tablespace_name='&TSNAME';
Enter value for tsname: RETEK_DATA
=========
Examples -

SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/apps/rt02/oradata/data03/temp01.dbf' SIZE 8000M AUTOEXTEND OFF

Added a new file -
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/apps/rt02/oradata/data03/temp02.dbf' SIZE 8000M AUTOEXTEND OFF

Reused a existing tempfile which had come from clone -
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/apps/rt02/oradata/data01/temp02.dbf' SIZE 16000M REUSE AUTOEXTEND OFF

Data guard Creation

2009-03-26T13:48:00.000-07:00

1) Ensure the Primary database is in ARCHIVELOG mode:

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /export/home/oracle/temp/oracle/arch
Oldest online log sequence 7
Current log sequence 9
SQL> alter database close;

Database altered.

SQL> alter database archivelog;

Database altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

Modify the Primary database init.ora so that log_archive_start=true and restart
the instance. Verify that database is in archive log mode and that automatic
archiving is enabled.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /export/home/oracle/temp/oracle/arch
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9

2) Create a backup of the Primary database:

You can use an existing backup of the Primary database as long as you have the
archive logs that have been generated since that backup. You may also take a
hot backup as long as you have all archive logs through the end of the backup
of the last tablespace. To create a cold backup do the following:

SQL>
SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------
/export/home/oracle/temp/oracle/data/sys.dbf

SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Create a backup of all datafiles and online redo logs using an OS command or
utility. A backup of the online redo logs is necessary to facilitate switchover.

Once complete startup the instance:

SQL> startup
ORACLE instance started.

Total System Global Area 80512156 bytes
Fixed Size 279708 bytes
Variable Size 71303168 bytes
Database Buffers 8388608 bytes
Redo Buffers 540672 bytes
Database mounted.
Database opened.

3) Connect to the primary database and create the standby control file:

SQL> alter database create standby controlfile as
'/export/home/oracle/temp/oracle/data/backup/standby.ctl';

Database altered.

4) Copy files to the Standby host:

Copy the backup datafiles, standby controlfile, all available archived redo logs,
and online redo logs from the primary site to the standby site. Copying of the
online redo logs is necessary to facilitate switchover.

If the standby is on a separate site with the same directory structure as the
primary database then you can use the same path names for the standby files as
the primary files. In this way, you do not have to rename the primary datafiles
in the standby control file. If the standby is on the same site as the primary
database, or the standby database is on a separate site with a different
directory structure the you must rename the primary datafiles in the standby
control file after copying them to the standby site. This can be done using
the db_file_name_convert and log_file_name_convert parameters or by manually
using the alert database statements.

5) Set the initialization parameters for the primary database:

It is suggested that you maintain two init.ora’s on both the primary and the
standby. This allows you to facilitate role reversal during switchover
operations more easily.

Primary init.ora on Primary host:

log_archive_dest_1='LOCATION=/export/home/oracle/temp/oracle/arch'
log_archive_dest_2='SERVICE=DGD01_hasunclu2 reopen=60'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_format=%t_%s.dbf
log_archive_start=true
remote_archive_enable=true

Create the standby initialization parameter file and set the initialization
parameters for the standby database. Depending on your configuration, you may
need to set filename conversion parameters.

Standby init.ora on Primary host:

log_archive_dest_1='LOCATION=/export/home/oracle/temp/oracle/arch'
log_archive_dest_state_1=enable
log_archive_format=%t_%s.dbf
log_archive_start=true
standby_archive_dest=/export/home/oracle/temp/oracle/arch
standby_file_management=auto
fal_server=DGD01_hasunclu2
fal_client=DGD01_hasunclu1
remote_arhive_enable=true

NOTE: In the above example db_file_name_convert and log_file_name_convert are
not needed as the directory structure on the two hosts are the same. If the
directory structure is not the same then setting of these parameters is
recommended. Please reference notes 47325.1 and 47343.1 for further
information.

Copy the two init.ora’s from the Primary host to the Standby host. You must
modify the Primary init.ora on the Standby host to have log_archive_dest_2 use
the alias that points to the Primary host (ie DGD01_hasunclu1). You must
modify the Standby init.ora on the standby host to have fal_server and
fal_client use the aliases when standby is running on the Primary host
(ie fal_server=DGD01_hasunclu1 and fal_client=DGD01_hasunclu2).

6) Configure networking components:

On the Primary host create a net service name that the Primary database can
use to connect to the Standby database. On the Primary host create a net
service name that Standby, when running on the Primary host, can use to
connect to the Primary, when it is running on the Standby host. Example from
Primary’s host tnsnames.ora:

DGD01_hasunclu1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hasunclu1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = DGD01)
(SERVER = DEDICATED)
)
)
DGD01_hasunclu2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hasunclu2)(PORT = 1521))
)
(CONNECT_DATA =
(SID = DGD01)
(SERVER = DEDICATED)
)
)

The above two net service names must exist in the Standby hosts tnsnames.ora
also.

You must also configure a listener on the standby database. If you plan to
manage this standby database using the Data Guard broker, you must configure
the listener to use the TCP/IP protocol and statically register the standby
database service using its SID.

7) Start the standby instance and mount the standby database.

oracle@hasunclu2:/export/home/oracle/temp/oracle> sqlplus "/ as sysdba"

SQL*Plus: Release 9.0.1.0.0 - Production on Thu Mar 14 18:00:57 2002

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=?/dbs/initDGD.ora
ORACLE instance started.

Total System Global Area 80512156 bytes
Fixed Size 279708 bytes
Variable Size 71303168 bytes
Database Buffers 8388608 bytes
Redo Buffers 540672 bytes
SQL> alter database mount standby database;

Database altered.

SQL>

8) Create standby redo log files, if necessary:

Standby redo logs are necessary for the higher protection levels such as
Guaranteed, Instant, and Rapid. In these protection modes LGWR from the
Primary host writes transactions directly to the standby redo logs.
This enables no data loss solutions and reduces the amount of data loss
in the event of failure. Standby redo logs are not necessary if you are using
the delayed protection mode.

If you configure standby redo on the standby then you should also configure
standby redo logs on the primary database. Even though the standby redo logs
are not used when the database is running in the primary role, configuring
the standby redo logs on the primary database is recommended in preparation
for an eventual switchover operation.

Standby redo logs must be archived before the data can be applied to the
standby database. The standby archival operation occurs automatically, even if
the standby database is not in ARCHIVELOG mode. However, the archiver process
must be started on the standby database. Note that the use of the archiver
process (ARCn) is a requirement for selection of a standby redo log.

You must have the same number of standby redo logs on the standby as you have
online redo logs on production. They must also be exactly the same size.

The following syntax is used to create standby redo logs:

SQL> alter database add standby logfile
2 '/export/home/oracle/temp/oracle/data/srl_1.dbf' size 20m;

Database altered.
SQL> alter database add standby logfile
2 '/export/home/oracle/temp/oracle/data/srl_2.dbf' size 20m;

Database altered.

SQL> alter database add standby logfile
2 '/export/home/oracle/temp/oracle/data/srl_3.dbf' size 20m;

Database altered.

SQL>

9) Manually change the names of the primary datafiles and redo logs in the
standby control file for all files not automatically renamed using
DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT as noted in step 5. Datafile
names can be changed on the standby at a mounted state with the following
syntax:

SQL> alter database rename file
2 '/export/home/oracle/temp/oracle/data/sys.dbf'
3 to
4 '/export/home/oracle/temp/oracle/data2/sys.dbf';

10) Stop and restart the listeners:

On the primary database, and start the listener on the standby database so that
changes made to the listener.ora can be implemented.

11) Activate parameter changes:

Manually enable initialization parameter changes on the primary database
so that it can initiate archiving to the standby site.

At runtime, the LOG_ARCHIVE_DEST_n initialization parameter can be changed
using ALTER SYSTEM and ALTER SESSION statements. Activate the changes made to
these parameters by either bouncing the instance or activating via alter system.
For example:

SQL> alter system set log_archive_dest_2='SERVICE=DGD01_hasunclu2 reopen=60';

System altered.

12) Verify that automatic archiving is occurring:

On the Primary database switch a log and verfy that it has been shipped
properly using the v$archive_dest view.

SQL> alter system switch logfile;

System altered.

SQL> select status, error from v$archive_dest where dest_id=2;

STATUS ERROR
--------- -------------------------------------------------------
VALID

SQL>

13) Optionally place Standby database in managed recovery:

SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> exit

reference metalink document id Note:180031.1

Default USERS

2009-03-25T15:47:00.000-07:00

SYS

The SYS user owns all base tables and user-accessable view of the data dictionary (Oracle configuration information). No Oracle user should ever alter (update, delete, or insert) any rows or schema objects conatained in the SYS schema, because such activity can compromise data integrety. The security administrator must keep strict control of this central account.

SYSTEM

The SYSTEM user is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle options and tools.

SYSMAN

The SYSMAN user represents the Enterprise Manager super admin account. This EM admin can create and modify other EM admin accounts as well as admin the database instance itself.

DBSNMP

The DBSNMP user is used by EM to monitor the database. EM uses this account to access performance stats about the database. The DBSNMP credentials sometimes referred to as the monitoring credentials.

REDO LOG File Resize

2009-03-25T15:44:00.000-07:00

You can't resize logfile, you have to create new ones and drop the old ones.

before you begin ensure your database is not experiencing heavy traffic or do it at the time of least traffic probably at night [with peace at your alongside].

1.have a look at your current log groups/members:

SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

2.Make your last redolog group as current [very important] by using manual log switch until you get desired result.

alter system switch logfile;

select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 CURRENT

3 .After making the last group as current start dropping the groups one by one beginning from group number 1. Do it for all except the last group which has been recently made current.If you get any recovery related

312/624 error than carry out a fresh manual checkpoint and proceed again to drop the group.

alter database drop logfile group 1;

4.Recreate your new loggroups with desired sizes & locations. Ensure you do not create any thing below minimum size [10g : minimum 4Mb, default 50Mb, OMF 100MB]. Ensure a correct log size as it may impact

your database performance. Create number of groups as desired

alter database add logfile group 1 (
'path/member1.log',
'path/member2.log') size 500M reuse;

5.Carry out a manual log switch. See that newly created group becomes current. Carry out manual checkpoint & log switch until your last old log(the only one left) becomes inactive than drop it.Veryfy new logs. If you

are using RMAN than rman may search for dropped logfiles during your next backup.

do i need to restart my database after these operations. ???

No need to restart the Database

Hot Backup in Oracle

2008-10-27T10:42:00.000-07:00

When datafiles are put into hot backup mode, Oracle is still writing to them
Oracle slightly changes the way it performs updates to datafiles by copying whole datafile blocks, rather changed bytes , into the online redo logs.
Hot backups, by their very nature,take a corrupt copy of the datafiles.Since the instance is in ARCHIVELOG mode, all changes to the datafiles are available upon restore to correct any discrepancies created as the archiving process passes over any given datafile. This is why Oracle records redo information in whole blocks during hot backup: to help recover any inconsistent blocks in the datafiles upon restore.
And also during Hot backup mode oracle doesnt update the SCN in the header of the Datafile.
Online redo log files should never be backed up in hot backup mode.

STEPS:
1)Get listing of tablespaces and datafiles
(SELECT tablespace_name, file_name FROM sys.dba_data_files).

2. For each tablespacea) Put tablespace in hot backup mode
(ALTER TABLESPACE $TABLESPACENAME BEGIN BACKUP).
b) Copy, archive, or snapshot each datafile in this tablespace.
c) End hot backup mode for this tablespace
(ALTER TABLESPACE $TABLESPACENAME END BACKUP).

3. Backup the Oracle “controlfile”
(ALTER DATABASE BACKUP CONTROLFILE to trace)

4. Confirm all tablespaces returned to normal mode (SELECT FILE#,STATUS,CHANGE#,TIME FROM v$backup WHERE STATUS != 'NOT ACTIVE').

5.ALter System switch logfile ( it ll force the checkpoint which ll update SCN in the datafiles header)

6. Perform an archive log switch (ALTER SYSTEM ARCHIVE LOG CURRENT).
7. Backup archived redo logs.

Create Database in oracle

2008-10-22T13:11:00.000-07:00

After you have install the binaries using Oracle Universal Installer you need to create the database. And you can do that in two ways:
Using the Database Configuration Assistant (DBCA)
With the SQL “create database” statement

Using “DBCA” :

The Database Configuration Assistant (DBCA) an Oracle supplied tool that enables you to create an Oracle database, configure database options for an existing Oracle database, delete an Oracle database, or manage database templates.
command prompt ( start->run->cmd->dbca).

Using SQL “create database” statement:

Step 1: Decide on Your Instance Identifier (SID)
There can be more than one Oracle instance on a single machine. In order to be able to distinguish these instances, Oracle uses a SID (System Identifier) which is a string.
The SID can be set through the ORACLE_SID environment
Example:

>set ORACLE_SID = OraProdEurope

Step 2: Establish the Database Administrator Authentication Method

Step 3: Create the initialization parameter file
When an Oracle instance starts up, it requires either an initialization paramter file (init.ora) or an SPFILE. SPFILES have binary content and must be created from init.ora files. Therefore, the init.ora file (which is an ordinary text file) is created first.
Create the init.ora file and put it in below path.
Unix Path is: $ORACLE_HOME/dbs
Windows path is: %ORACLE_HOME%\database

Step 4: Connect to the Instance
$ SQLPLUS /nolog
SQL>CONNECT SYS/password AS SYSDBA
Connected to an idle instance.

SQL*Plus tells us that we're connected to an idle instance. That means that it is not yet started. So, let's start the instance. We have to start the instance without mounting (nomount) as there is no database we could mount at the moment.

Step 5: Start the Instance.
SQL> STARTUP NOMOUNT

This created the SGA (System Global Area) and the background processes

Step 6: Issue the CREATE DATABASE Statement
CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY pz6r58
USER SYSTEM IDENTIFIED BY y1tz5p
LOGFILE GROUP 1 ('/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
GROUP 2 ('/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
GROUP 3 ('/oracle/oradata/mynewdb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE tempts1
DATAFILE '/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;

If something goes wrong with the creation, Oracle will write an error into the alert.log. The alert log is normaly found in the directory that is specified with the background_dump_dest. If this parameter was not specified (as is the case in our minimal init.ora), the alert.log will be written into %ORACLE_HOME%/RDMBS/trace.
If an ORA-01031: insufficient privileges is returned, that means most likely, that the current user is not in the dba group (on unix), or the ORA_DBA (windows).
If the init.ora file is not at its default location or has not been found with the pfile attribute, an ORA-01078: failure in processing system parameters and an LRM-00109: could not open parameter file '/appl/oracle/product/9.2.0.2/dbs/initadpdb.ora' error is issued.
The create database command also executes a file whose name is determined by the (hidden) init parameter _init_sql_file (which seems to default to sql.bsq)
After the creation of the database, it can be mounted and opened for use.

Step 7: Create Additional Tablespaces

CONNECT SYS/password AS SYSDBA
-- create a user tablespace to be assigned as the default tablespace for users
CREATE TABLESPACE users LOGGING
DATAFILE '/oracle/oradata/mynewdb/users01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
-- create a tablespace for indexes, separate from user tablespace
CREATE TABLESPACE indx LOGGING
DATAFILE '/oracle/oradata/mynewdb/indx01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
EXIT

Step 8: Run Scripts to Build Data Dictionary Views
CONNECT SYS/password AS SYSDBA
(In order to complete the db creation, the following scripts must be run as sys )

@/oracle/rdbms/admin/catalog.sql
@/oracle/rdbms/admin/catproc.sql
EXIT

CATALOG.SQL
Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.
CATPROC.SQL
Runs all scripts required for or used with PL/SQL.

The user system might also want to run /sqlplus/admin/pupbld.sql.
pupbld.sql creates a table that allows to block someone from using sql plus.

USER Profiles

2008-10-20T08:32:00.000-07:00

Profiles in Oracle
Profiles are a means to limit resources a user can use.
Before profiles can be assigned, they must be created with create profile. Then, they can be assigned to users with alter user ... profile.
For Example:

CREATE PROFILE
SQL> CREATE PROFILE developer LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 0
PASSWORD_REUSE_MAX 0
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LOCK_TIME 2
CPU_PER_CALL 3000
PRIVATE_SGA 500K
LOGICAL_READS_PER_CALL 1000;

ALTER Profile:
SQL>ALTER PROFILE developer LIMIT FAILED_LOGIN_ATTEMPTS 3;

Drop Profile without user:
SQL> drp profile developer;

Drop Profile with user:
SQL> drp profile developer CASCADE;

Assigna profile to User:
SQL> alter user rama profile developer ;

Limitable resources

RESOURCE_LIMIT=TRUE is required for resource limiting portions of the profile. Password limiting functionality is not affected by this parameter.

SQL>SELECT name, valueFROM gv$parameterWHERE name = 'resource_limit';
SQL> ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH;

Kernel limits
Maximum concurrent sessions for a user(sessions_per_user)
CPU time limit per session(cpu_per_session)
CPU time limit per call(cpu_per_call)Call being parse, execute and fetch
Maximum connect time(connect_time)The session will be dropped by oracle after specified time.
Maximum idle time(idle_time)The session will be dropped by oracle after specified time of doing nothing. Long running processes are not idle!
Maximum blocks read per session(logical_reads_per_session)
Maximum blocks read per call(logical_reads_per_call)
Maximum amount of SGA(private_sga)
....(composite_limit)
In order to enforce kernel limits, resource_limit must be set to true.

Password limits
-Maximum failed login attempts(failed_login_attempts)
-Maximum time a password is valid(password_life_time)
-Minimum of different passwords before password can be reused(password_reuse_max)
-Minimum of days before a password can be reused(password_reuse_time)
-Number of days an account is locked after failing to login(password_lock_time)
-Password grace time (password_grace_time)
-Verify function for passwords(password_verify_function)

If a session exceeds one of these limits, Oracle will terminate the session. If there is a logoff trigger, it won't be executed.

History of passwords
In order to track password related profile limits, Oracle stores the history of passwords for a user in user_history$.
Profiles already created and their settings can be queried through dba_profiles