Wednesday, May 26, 2010
Friday, April 9, 2010
Create Multiple Instance on A Single Database
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
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=
export PATH=/opt/perl/bin:$PATH
3)
Apply the patch by this command
opatch apply -invPtrLoc
Optimizer Statstics & Index Usage!!!
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.
Saturday, January 16, 2010
Subscribe to:
Posts (Atom)
