col username format a12
col FAILED_OVER format a12
col machine format a18
set lines 120
select USERNAME,INST_ID, MACHINE,FAILED_OVER,FAILOVER_TYPE,FAILOVER_METHOD,count(*) from gv$session
where username not in ('SYS','DBSNMP') group by
USERNAME,MACHINE,INST_ID,FAILED_OVER,FAILOVER_TYPE,FAILOVER_METHOD
order by USERNAME,INST_ID;
Tuesday, April 24, 2007
Query Import Status
col table_name format a30
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;
export/import using pipe at the same time
# Reference: Oracle Metalink Note 1018477.6 ‘Exporting on Unix Systems’
# example for demo 9.2.0.4 database on apollo (export consists of MMADMIN schema-
# you can also perform a full exp/imp to import all users to the target database):
#
1. Setup pipe and perform export from source
# example for demo 9.2.0.4 database on apollo
oracle@apollo> mknod /ora01/oracle/admin/demo/exp/demo.dmp p
oracle@apollo> exp system file=/ora01/oracle/admin/demo/exp/demo.dmp \
> owner=MMADMIN consistent=y statistics=none log=demo.log
Export: Release 9.2.0.4.0 - Production on Fri Feb 17 16:22:47 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Password:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user MMADMIN
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user MMADMIN
About to export MMADMIN's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export MMADMIN's tables via Conventional Path ...
. . exporting table MMACCESS 2 rows exported
. . exporting table MMACCESS_BK 5 rows exported
. . exporting table MMACTION 12 rows exported
. . exporting table MMACTIONGROUPING 17 rows exported . . . .
2. Setup pipe and perform import on target
# example for demo 10.2.0.1 database on atlantis
oracle@atlantis> mknod /ora01/oracle/admin/demo/exp/demo2.dmp p
oracle@atlantis> ssh apollo dd if=/ora01/oracle/admin/demo/exp/demo.dmp \
> > /ora01/oracle/admin/demo/exp/demo2.dmp &
[1] 8761
oracle@atlantis> imp system file=/ora01/oracle/admin/demo/exp/demo2.dmp \
> fromuser=MMADMIN touser=MMADMIN buffer=10000000 commit=y log=demo.log
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing MMADMIN's objects into MMADMIN
. . importing table "MMACCESS" 2 rows imported
. . importing table "MMACCESS_BK" 5 rows imported
. . importing table "MMACTION" 12 rows imported
. . importing table "MMACTIONGROUPING" 17 rows imported
. . importing table "MMAPPLICATION" 5 rows imported
. . importing table "MMCLASS" 202 rows imported
. . importing table "MMCLASSMERGEOPTION" 0 rows imported
. . importing table "MMCONTROL" 5 rows imported . . . .
Sunday, April 22, 2007
Gather Financial APPS info
-- For patch information
set pages 1000
set lines 120
column application_name format a50
select a.application_name,
decode(b.status,'I','Installed','S','Shared','N/A') STATUS, PATCH_LEVEL from
APPS.fnd_application_vl a, APPS.fnd_product_installations b where
a.application_id = b.application_id order by 2,1;
--- for URL
select to_char(a.profile_option_id) id,b.profile_option_name,a.profile_option_value
from fnd_profile_option_values a, fnd_profile_options b
where a.profile_option_id=b.profile_option_id and profile_option_value like '%http%';
---for number of nodes
select concurrent_queue_name,target_node,node_name from fnd_concurrent_queues;
select distinct target_node from fnd_concurrent_queues;
select node_name, support_cp,support_forms,support_admin,support_web status from fnd_nodes;
-- node information
select concurrent_queue_name,target_node,node_name from fnd_concurrent_queues;
select distinct target_node from fnd_concurrent_queues;
select node_name, support_cp,support_forms,support_admin,support_web status from fnd_nodes;
set pages 1000
set lines 120
column application_name format a50
select a.application_name,
decode(b.status,'I','Installed','S','Shared','N/A') STATUS, PATCH_LEVEL from
APPS.fnd_application_vl a, APPS.fnd_product_installations b where
a.application_id = b.application_id order by 2,1;
--- for URL
select to_char(a.profile_option_id) id,b.profile_option_name,a.profile_option_value
from fnd_profile_option_values a, fnd_profile_options b
where a.profile_option_id=b.profile_option_id and profile_option_value like '%http%';
---for number of nodes
select concurrent_queue_name,target_node,node_name from fnd_concurrent_queues;
select distinct target_node from fnd_concurrent_queues;
select node_name, support_cp,support_forms,support_admin,support_web status from fnd_nodes;
-- node information
select concurrent_queue_name,target_node,node_name from fnd_concurrent_queues;
select distinct target_node from fnd_concurrent_queues;
select node_name, support_cp,support_forms,support_admin,support_web status from fnd_nodes;
Useful File and Filesystem Commands
directory name
dirname /x/y/z/
removing extension from file
$ basename /this/is/a/file.txt .txt
List subdirectores sorted by size
du -k | sort -n
List file sorted by size
ls -l | sort +4n
only in linux :
file size in K,M : ls -lh
files sorted by size : ls -lS
symbols along with file type
ls -F
List just the directory
ls -ld dirname
dirname /x/y/z/
removing extension from file
$ basename /this/is/a/file.txt .txt
List subdirectores sorted by size
du -k | sort -n
List file sorted by size
ls -l | sort +4n
only in linux :
file size in K,M : ls -lh
files sorted by size : ls -lS
symbols along with file type
ls -F
List just the directory
ls -ld dirname
vmstat
vmstat 1 16 :
Legend:
- kthr = kernel thread - state change (the columns selected are of interest here)
- r = run-queue - number of tasks executing and consuming CPU resources - average per interval
- b = blocked-queue - number of tasks waiting (blocked ) for CPU resources - average per interval
- pi = pages-in - memory contents read to memory from disk (where it was paged out) - avg per second
- po = pages-out - memory contents written from memory to disk (non-zero is NORMAL) - avg per second
- wa = waiting-CPU - percentage of CPU time waiting for external operations (I/O) to finish - avg per interval
A few simple rules of thumb tell us that, when busy, the system is:
MEMORY-BOUND: - the paging size (in MB) > SGA, or non-zero values in the pi column, or page percent too high
CPU-BOUND: - the average values in columns r and b > # of CPU's, or load max > load coef (e.g., 10)
I/O-BOUND: - there are values in the wa column > 20% (up to max 40 percent by some authors)
RUNS-AT-FULL-CAPACITY: - the sum of us and sy approach 100% and id and wa are very small
Legend:
- kthr = kernel thread - state change (the columns selected are of interest here)
- r = run-queue - number of tasks executing and consuming CPU resources - average per interval
- b = blocked-queue - number of tasks waiting (blocked ) for CPU resources - average per interval
- pi = pages-in - memory contents read to memory from disk (where it was paged out) - avg per second
- po = pages-out - memory contents written from memory to disk (non-zero is NORMAL) - avg per second
- wa = waiting-CPU - percentage of CPU time waiting for external operations (I/O) to finish - avg per interval
A few simple rules of thumb tell us that, when busy, the system is:
MEMORY-BOUND: - the paging size (in MB) > SGA, or non-zero values in the pi column, or page percent too high
CPU-BOUND: - the average values in columns r and b > # of CPU's, or load max > load coef (e.g., 10)
I/O-BOUND: - there are values in the wa column > 20% (up to max 40 percent by some authors)
RUNS-AT-FULL-CAPACITY: - the sum of us and sy approach 100% and id and wa are very small
AIX Handy Commands
CPU
lsdev -C|grep Process|wc -l
Memory
lsattr -El mem0
Swap
lsps -s
sar
sar -q -f /var/adm/sa/sa19|pg #sa19 means date 19th of the month
Process Management
truss -a -e -f -rall -wall -p mypid # mypid means pid number
More info on truss
lsdev -C|grep Process|wc -l
Memory
lsattr -El mem0
Swap
lsps -s
sar
sar -q -f /var/adm/sa/sa19|pg #sa19 means date 19th of the month
Process Management
truss -a -e -f -rall -wall -p mypid # mypid means pid number
More info on truss
SQL Performance related Dynamic Views
What is my sesison doing
steps
1. find sid of the session from v$session
2. check v$Session_wait for last wait activity
3. check v$session_event for commuvative waits
4. check v$sesstat for resource usage stats
from where and what
my sid v$mystat
rownum=1
others sid v$session ,v$process
what's up v$sesstat v$statname v$sess_io v$session_wait
CPU used by this session
which segment v$sesion_wait
buffer bust waits db file sequential read db file scattered read free buffer waits
which latch v$session_wait v$latchname
latch free
which sql v$sqltext v$sqlarea v$session
sid
Current State Views
V$SESSION - Sessions currently connected to the instance
v$session_wait - last/current wait
This is a key view for finding bottlenecks. It tells what every session in the
database is currently waiting for (or the last event waited for by the session
if it is not waiting for anything). This view can be used as a starting point
to find which direction to proceed in when a system is experiencing performance
problems.
Since 10g, Oracle displays the v$session_wait information also in the v$session view.
Summary Since Session Startup - cummulative
v$mystat - Resource usage summary for your own session
This view records statistical data about the session that accesses it.
v$session_event - Session-level summary of all the waits for current sessions
This view summarizes wait events for every session. While V$SESSION_WAIT shows
the current waits for a session, V$SESSION_EVENT provides summary of all the
events the session has waited for since it started.
v$sesstat - session-level summary of resource usage since session startup
V$SESSTAT stores session-specific resource usage statistics, beginning at login
and ending at logout.
Includes session logical reads, CPU used by this session, db block changes,
redo size, physical writes, parse count (hard), parse count (total),
sorts (memory), and sorts (disk).
V$SESSTAT can be used to find sessions with the following:
* The highest resource usage
* The highest average resource usage rate (ratio of resource usage to logon time)
* The current resource usage rate (delta between two snapshots)
v$sysstat - Summary of resource usage
V$SYSSTAT stores instance-wide statistics on resource usage, cumulative since
the instance was started.
Similar to V$SESSTAT, this view stores the following types of statistics:
* A count of the number of times an action occurred (user commits)
* A running total of volumes of data generated, accessed, or manipulated (redo size)
* If TIMED_STATISTICS is true, then the cumulative time spent performing some
actions (CPU used by this session)
The data in this view is used for monitoring system performance. Derived statistics, such as the buffer cache hit ratio and soft parse ratio, are computed from V$SYSSTAT data.
v$system_event - cummulative Instance wide summary of resources waited for
This view displays the count (total_waits) of all wait events since startup of the instance.
This view is a summary of waits for an event by an instance. While V$SESSION_WAIT
shows the current waits on the system, V$SYSTEM_EVENT provides a summary of all
the event waits on the instance since it started. It is useful to get a historical
picture of waits on the system. By taking two snapshots and doing the delta on
the waits, you can determine the waits on the system in a given time interval.
v$waitstat - Break down of buffer waits by block class
total_waits where event='buffer busy waits' is equal the sum of count in v$system_event.
This view keeps a summary all buffer waits since instance startup. It is useful
for breaking down the waits by class if you see a large number of buffer busy
waits on the system.
LINK
oracle 9i performance document
oracle 10g performance document
steps
1. find sid of the session from v$session
2. check v$Session_wait for last wait activity
3. check v$session_event for commuvative waits
4. check v$sesstat for resource usage stats
from where and what
my sid v$mystat
rownum=1
others sid v$session ,v$process
what's up v$sesstat v$statname v$sess_io v$session_wait
CPU used by this session
which segment v$sesion_wait
buffer bust waits db file sequential read db file scattered read free buffer waits
which latch v$session_wait v$latchname
latch free
which sql v$sqltext v$sqlarea v$session
sid
Current State Views
V$SESSION - Sessions currently connected to the instance
v$session_wait - last/current wait
This is a key view for finding bottlenecks. It tells what every session in the
database is currently waiting for (or the last event waited for by the session
if it is not waiting for anything). This view can be used as a starting point
to find which direction to proceed in when a system is experiencing performance
problems.
Since 10g, Oracle displays the v$session_wait information also in the v$session view.
Summary Since Session Startup - cummulative
v$mystat - Resource usage summary for your own session
This view records statistical data about the session that accesses it.
v$session_event - Session-level summary of all the waits for current sessions
This view summarizes wait events for every session. While V$SESSION_WAIT shows
the current waits for a session, V$SESSION_EVENT provides summary of all the
events the session has waited for since it started.
v$sesstat - session-level summary of resource usage since session startup
V$SESSTAT stores session-specific resource usage statistics, beginning at login
and ending at logout.
Includes session logical reads, CPU used by this session, db block changes,
redo size, physical writes, parse count (hard), parse count (total),
sorts (memory), and sorts (disk).
V$SESSTAT can be used to find sessions with the following:
* The highest resource usage
* The highest average resource usage rate (ratio of resource usage to logon time)
* The current resource usage rate (delta between two snapshots)
v$sysstat - Summary of resource usage
V$SYSSTAT stores instance-wide statistics on resource usage, cumulative since
the instance was started.
Similar to V$SESSTAT, this view stores the following types of statistics:
* A count of the number of times an action occurred (user commits)
* A running total of volumes of data generated, accessed, or manipulated (redo size)
* If TIMED_STATISTICS is true, then the cumulative time spent performing some
actions (CPU used by this session)
The data in this view is used for monitoring system performance. Derived statistics, such as the buffer cache hit ratio and soft parse ratio, are computed from V$SYSSTAT data.
v$system_event - cummulative Instance wide summary of resources waited for
This view displays the count (total_waits) of all wait events since startup of the instance.
This view is a summary of waits for an event by an instance. While V$SESSION_WAIT
shows the current waits on the system, V$SYSTEM_EVENT provides a summary of all
the event waits on the instance since it started. It is useful to get a historical
picture of waits on the system. By taking two snapshots and doing the delta on
the waits, you can determine the waits on the system in a given time interval.
v$waitstat - Break down of buffer waits by block class
total_waits where event='buffer busy waits' is equal the sum of count in v$system_event.
This view keeps a summary all buffer waits since instance startup. It is useful
for breaking down the waits by class if you see a large number of buffer busy
waits on the system.
LINK
oracle 9i performance document
oracle 10g performance document
Friday, April 20, 2007
HOW THE RULE-BASED OPTIMIZER WORKS
But first, let’s start at the beginning…
The rule-based optimizer (RBO) has only a small amount of information to use in deciding upon an execution plan for a SQL statement:
• The text of the SQL statement itself
• Basic information about the objects in the SQL statement, such as the tables, clusters, and views in the FROM clause and the data type of the columns referenced in the other clauses
• Basic information about indexes associated with the tables referenced by the SQL statement
• Data dictionary information is only available for the local database. If you’re referencing a remote database, the remote dictionary information is not available to the RBO…
In order to determine the execution plan, the RBO first examines the WHERE clause of the statement, separating each predicate from one another for evaluation, starting from the bottom of the statement. It applies a score for each predicate, using the fifteen access methods ordered by their alleged merit:
1. Single row by ROWID
2. Single row by cluster join
3. Single row by hash cluster key with unique key
4. Single row by unique index
5. Cluster join
6. Hash cluster key
7. Indexed cluster key
8. Composite key
9. Single-column non-unique index
10. Bounded range search on indexed columns
11. Unbounded range search on indexed columns
12. Sort-merge join
13. MAX or MIN of indexed column
14. ORDER BY on indexed columns
15. Full table-scan
Suggest you check out:
www.evdbt.com/SearchIntelligenceCBO.doc
which is a pretty good paper on this topic.
The rule-based optimizer (RBO) has only a small amount of information to use in deciding upon an execution plan for a SQL statement:
• The text of the SQL statement itself
• Basic information about the objects in the SQL statement, such as the tables, clusters, and views in the FROM clause and the data type of the columns referenced in the other clauses
• Basic information about indexes associated with the tables referenced by the SQL statement
• Data dictionary information is only available for the local database. If you’re referencing a remote database, the remote dictionary information is not available to the RBO…
In order to determine the execution plan, the RBO first examines the WHERE clause of the statement, separating each predicate from one another for evaluation, starting from the bottom of the statement. It applies a score for each predicate, using the fifteen access methods ordered by their alleged merit:
1. Single row by ROWID
2. Single row by cluster join
3. Single row by hash cluster key with unique key
4. Single row by unique index
5. Cluster join
6. Hash cluster key
7. Indexed cluster key
8. Composite key
9. Single-column non-unique index
10. Bounded range search on indexed columns
11. Unbounded range search on indexed columns
12. Sort-merge join
13. MAX or MIN of indexed column
14. ORDER BY on indexed columns
15. Full table-scan
Suggest you check out:
www.evdbt.com/SearchIntelligenceCBO.doc
which is a pretty good paper on this topic.
Optimizer Settings
o optimizer_index_caching - percentage of blocks expected to be found in the buffer cache during an index hit. default of 0 implies that every (logical) LIO is a (physical) PIO.
o optimizer_index_cost_adj - represents relative cost of PIO's for indexed access vs full scan. Default value of 100 indicates that an indexed access is just as costly as a full
access.
o optimizer_index_cost_adj - represents relative cost of PIO's for indexed access vs full scan. Default value of 100 indicates that an indexed access is just as costly as a full
access.
Thursday, April 19, 2007
ps command - Shows current status of processes
Linux
ps -p XXXX -o %cpu,pid,user,state,start,time,etime,%cpu,%mem,cmd
ps h -e -o %cpu,pid,user,state,start,time,etime,%cpu,%mem,cmd|sort -rn|more
AIX
ps augxww | head
Solaris
/usr/ucb/ps uaxw| more
ps -p XXXX -o %cpu,pid,user,state,start,time,etime,%cpu,%mem,cmd
ps h -e -o %cpu,pid,user,state,start,time,etime,%cpu,%mem,cmd|sort -rn|more
AIX
ps augxww | head
Solaris
/usr/ucb/ps uaxw| more
RAC Commands
find out which NIC (private obviously) was used for Cache Fusion
SQL> oradebug setmypid
SQL> oradebug ipc
SQL> oradebug tracefile_name
Nodes in cluster
$ORA_CRS_HOME/bin/olsnodes -n
CRS
see CRS cheat Sheet
Storage -- List of rawdevices
cat /etc/sysconfig/rawdevices
OCR
see OCR Cheat Sheet
Voting disk
see Voting Disk Cheat Sheet
SQL> oradebug setmypid
SQL> oradebug ipc
SQL> oradebug tracefile_name
Nodes in cluster
$ORA_CRS_HOME/bin/olsnodes -n
CRS
see CRS cheat Sheet
Storage -- List of rawdevices
cat /etc/sysconfig/rawdevices
OCR
see OCR Cheat Sheet
Voting disk
see Voting Disk Cheat Sheet
linux Handy commands
OS Version
uname -r
Overall tools
top, sar,vmstat, vmstat 3 5, top -c -p 16514
CPU
/proc/cpuinfo, mpstat, top, iostat -xtc 5 3
grep "model name" /proc/cpuinfo
To get the count of physical CPUs, this works by counting the unique physical ids
grep "physical id" /proc/cpuinfo |sort -u|wc -l
Memory
/proc/meminfo, /proc/slabinfo, free
grep MemTotal /proc/meminfo
swap
grep SwapTotal /proc/meminfo
Disk IO
iostat
Network
/proc/net/dev, netstat, mii-tool, netstat -i
Kernel Version
/proc/version
Types of IO cards
lspci –vv
Startup changes
/etc/sysctl.conf, /etc/rc.local
OS Log
/var/log/messages, /var/log/dmesg
OS Error codes
/usr/src/linux/include/asm/errno.h
Process Management
/usr/sbin/strace -p
strace -tt -o /tmp/ckpt.out -p
kernel setting
/etc/sysctl.conf
User id and group id
id oracle
ulimit -a
IP addresses and Names
/etc/hosts
Network
oifcfg iflist
# general info
private bonding : bond0
public : eth0
vip : eth0:1
Display the number of CPUs cat /proc/cpuinfo|grep processor|wc –l
Show top CPU% ps aux|sort -n +2
Display top-10 CPU consumers ps aux|sort -rn +2|head -10
RAM memory display free
Shutdown server as root /sbin/shutdown -r now
Kill all xxx processes pkill [-9] “xxx”
Show swap paging space /sbin/swapon -s
Show Linux syslog errors tail /var/log/messages
Show swap disk details swapon -s
See held memory segments ipcs -m
Show Linux system parms sysctl -a
Linux command history files history|more
RAM Size in Linux : free
uname -r
Overall tools
top, sar,vmstat, vmstat 3 5, top -c -p 16514
CPU
/proc/cpuinfo, mpstat, top, iostat -xtc 5 3
grep "model name" /proc/cpuinfo
To get the count of physical CPUs, this works by counting the unique physical ids
grep "physical id" /proc/cpuinfo |sort -u|wc -l
Memory
/proc/meminfo, /proc/slabinfo, free
grep MemTotal /proc/meminfo
swap
grep SwapTotal /proc/meminfo
Disk IO
iostat
Network
/proc/net/dev, netstat, mii-tool, netstat -i
Kernel Version
/proc/version
Types of IO cards
lspci –vv
Startup changes
/etc/sysctl.conf, /etc/rc.local
OS Log
/var/log/messages, /var/log/dmesg
OS Error codes
/usr/src/linux/include/asm/errno.h
Process Management
/usr/sbin/strace -p
strace -tt -o /tmp/ckpt.out -p
kernel setting
/etc/sysctl.conf
User id and group id
id oracle
ulimit -a
IP addresses and Names
/etc/hosts
Network
oifcfg iflist
# general info
private bonding : bond0
public : eth0
vip : eth0:1
Display the number of CPUs cat /proc/cpuinfo|grep processor|wc –l
Show top CPU% ps aux|sort -n +2
Display top-10 CPU consumers ps aux|sort -rn +2|head -10
RAM memory display free
Shutdown server as root /sbin/shutdown -r now
Kill all xxx processes pkill [-9] “xxx”
Show swap paging space /sbin/swapon -s
Show Linux syslog errors tail /var/log/messages
Show swap disk details swapon -s
See held memory segments ipcs -m
Show Linux system parms sysctl -a
Linux command history files history|more
RAM Size in Linux : free
Wednesday, April 18, 2007
FlashBack Database
### IMPORTANT CONSIDERATIONS
1. If flashback database cannot be used, an incomplete
recovery operation should be used.
2. To undo a flashback operation, perform a complete recovery
3. Flashback logs may deleted automatically, if space is limited.
### CONFIGURE FLASHBACK PARAMETERS
ALTER SYSTEM SET db_recovery_file_dest_size = 30G SCOPE=BOTH SID='*'
# Ensure flashback dest, size and retention is defined
SHOW PARAMETER RECOVERY
SHOW PARAMETER FLASH
# archive is set to null, means, it would default to FRA
SHOW PARAMETER ARCHIVE
### ENABLE FLASHBACK
select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
### MUST HAVE : DATABASE IN ARCHIVELOG MODE
ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 416
Next log sequence to archive 418
Current log sequence 418
### STOP AND START DATABASE IN MOUNT MODE
srvctl stop database -d demo
srvctl start instance –d demo –i demo1 –o mount
select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
### DATABASE W/ FLASHBACK ENABLED
ALTER DATABASE FLASHBACK ON;
Database altered.
select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
### STOP AND START DATABASE W/ FLASHBACK ENABLED
srvctl stop database -d demo
srvctl start database -d demo
select flashback_on from v$database;
### SPACE USAGE OF FLASH RECOVERY AREA(FRA)
col name format a20
col space_limit format 999999999999
select * from v$recovery_file_dest;
select * from v$flash_recovery_area_usage;
### DETERMINING THE CURRENT WINDOW FOR FLASHBACK DATABASE
select * from V$flashback_database_log;
### ESITMATE STORAGE REQUIREMENT FOR FLASHBACK DATABASE
select estimated_flashback_size, flashback_size
from v$flashback_database_log;
### Creating normal and guaranteed restore points
CREATE RESTORE POINT before_upgrade;
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;
### Dropping restore points
DROP RESTORE POINT before_app_upgrade;
### Listing restore points
select name, scn, time, database_incarnation#,
guarantee_flashback_database,storage_size
from v$restore_point;
select name, scn, time, database_incarnation#,
guarantee_flashback_database, storage_size
from v$restore_point
where guarantee_flashback_database='yes';
#### Performing flashback database: scenario
### Using Flashback Database with RMAN
# Step 1:
Determine the desired SCN, restore point or point in time for the
FLASHBACK DATABASE command
select current_scn from v$database;
select oldest_flashback_scn,oldest_flashback_time
from v$flashback_database_log;
# Step 2
rman TARGET /
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT
select oldest_flashback_scn,oldest_flashback_time
from v$flashback_database_log;
### options:
FLASHBACK DATABASE TO SCN my_scn;
FLASHBACK DATABASE TO BEFORE SCN my_scn;
FLASHBACK DATABASE TO SEQUENCE my_seq;
FLASHBACK DATABASE TO TIME('2006-01-05 16:10:00');
FLASHBACK DATABASE TO TIME = TO_DATE('2006-12-10 16:10:00','YYYY-MM-DD HH24:MI:SS')
FLASHBACK DATABASE TO RESTORE POINT before_changes;
FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
# Step 3
Once flashback is complete;Test the database to determine if it was successful
ALTER DATABASE OPEN READ ONLY;
# Options After a Successful Flashback Database Operation
you are satisfied with the state of the database after the Flashback Database
operation, you have two choices:
Option 1:
Make the database available for updates by performing an OPEN RESETLOGS operation:
RMAN> ALTER DATABASE OPEN RESETLOGS;
Option 2:
RMAN> RECOVER DATABASE;
This step undoes the effect of the Flashback Database, by re-applying all
changes in the redo logs to the database, returning it to the most recent SCN.
# OPTIONS AFTER FLASHBACK DATABASE TO THE WRONG TIME
Option 3:
If your chosen target time was not far enough in the past, then you can use
another FLASHBACK DATABASE command to rewind the database further in time.
RMAN> FLASHBACK DATABASE TO SCN 42963; #earlier than current SCN
Option 4:
If you chose a target SCN that is too far in the past, then you can mount the
database and use RECOVER DATABASE UNTIL to wind the database forward in time
to the desired SCN:
RMAN> RECOVER DATABASE UNTIL SCN 56963; #later than current SCN
Option 5:
If you want to completely undo the effect of the FLASBACK DATABASE command,
you can perform complete recovery of the database by using the RECOVER DATABASE
command without an UNTIL clause or SET UNTIL command:
RMAN> RECOVER DATABASE;
This re-applies all changes to the database, returning it to the most
recent SCN.
### PERFORMING FLASHBACK DATABASE TO A GUARANTEED RESTORE POINT
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
Having identified the restore point to use, mount the database and run the
FLASHBACK DATABASE command, using the restore point. For example:
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_CHANGES';
When the command completes, you may open the database read-only and inspect
the effects of the operation, and if satisfied, open the database with the
RESETLOGS option.
### PERFORMING FLASHBACK DATABASE TO UNDO AN OPEN RESETLOGS
Before performing the flashback, verify that the beginning of the flashback
window is earlier than the time of the most recent OPEN RESETLOGS.
select resetlogs_change# from v$database;
select oldest_flashback_scn from v$flashback_database_log;
If V$DATABASE.RESETLOGS_CHANGE# > V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN
shutdown database, mount it; re-check the flashback window, if true, then
RMAN> FLASHBACK DATABASE TO BEFORE RESETLOGS;
verify, using
alter database open read only;
Looks good, then
ALTER DATABASE OPEN RESETLOGS;
### FLASHBACK DATABASE TO THE RIGHT OF OPEN RESETLOGS:
EXAMPLE
In some cases, you may need to return the database to a point in time in the
parent incarnation, later than the SCN of the OPEN RESETLOGS at which the
current incarnation path branched from the old incarnation
# Verify that the flashback logs contain enough information to flash back to
that SCN:
select oldest_flashback_scn from v$flashback_database_log;
# Determine the target incarnation number for the flashback, that is, the
incarnation key for the parent incarnation:
select prior_incarnation# from v$database_incarnation
where status = 'CURRENT';
# In RMAN, shut down the database, then mount it:
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
# Set the database incarnation to the parent incarnation:
RMAN> RESET DATABASE TO INCARNATION 1;
# Run the FLASHBACK DATABASE command:
RMAN> FLASHBACK DATABASE TO SCN 1500;
Once the flashback is complete, you can verify the results, and if successful,
open the database with RESETLOGS
### Test performed on demo
create table t1 ( x number) tablespace users;
Table created.
insert into t1 values (1);
1 row created.
commit;
Commit complete.
exit
RMAN> flashback database to scn 9208793;
Starting flashback at 15-MAY-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 instance=demo1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=140 instance=demo1 devtype=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=139 instance=mcwd1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: VERITAS NetBackup for Oracle - Release 5.1 (2005081402)
starting media recovery
media recovery complete, elapsed time: 00:00:15
Finished flashback at 15-MAY-06
RMAN> EXIT
Tablespaces in Flashback mode
select name, flashback_on from v$tablespace;
FLASH RECOVERY AREA (FRA)
Configure
sql > alter system set db_recovery_file_dest_size = 2G scope=both sid='*';
sql > alter system set db_recovery_file_dest='+ORAFLASH001' scope=both sid='*';
sql > show parameter db_reco
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +ORAFLASH001
db_recovery_file_dest_size big integer 2G
Check space utilization
summary
col name format a20
select * from v$recovery_file_dest;
details
select * from v$flash_recovery_area_usage;
RMAN Incomplete Recovery
1-2-3 steps for incomplete recovery
Assuming control file available.
Assuming control file available.
Login to database server
Set oracle enviornment
startup database in mount mode
connect to database using RMAN to start the restore process
rman target / nocatalog
Step 1
Restore Database
Step 2
recover database until time '2005-04-05 20:20:25' using backup controlfile;
Step 3
alter database open database resetlogs;
RMAN - Restore from disk using until time
startup mount;
run {
set until time = "to_date('Jan 20 2004 16:32:29','Mon DD YYYY HH24:MI:SS')";
allocate channel llat_d1 type disk;
allocate channel llat_d2 type disk;
restore database;
recover database;
alter database open resetlogs;
PS: set until scn 562204384143; # for restoring until SCN
run {
set until time = "to_date('Jan 20 2004 16:32:29','Mon DD YYYY HH24:MI:SS')";
allocate channel llat_d1 type disk;
allocate channel llat_d2 type disk;
restore database;
recover database;
alter database open resetlogs;
PS: set until scn 562204384143; # for restoring until SCN
RMAN Restore Commands
step 1
set DBID = 2468127084;
startup nomount;
Step 2
Allocate Channel on Tape
allocate channel ch1 type sbt
parms='ENV=(NB_ORA_CLASS=RMAN_MTBackup_Tier3)';
Step 3
restore controlfile from autobackup;
Step 4
alter database mount;
Step 5 - set recovery time/scn /log_seq
set until scn 562449965260;
set until time 'Jul 01 2002 00:02:00';
set until time "to_date('01-28-2004 11:40:54','mm-dd-yyyy hh24:mi:ss')";
Step6
restore database;
recover database;
alter database open resetlogs;
Metalink
224354.1
set DBID = 2468127084;
startup nomount;
Step 2
Allocate Channel on Tape
allocate channel ch1 type sbt
parms='ENV=(NB_ORA_CLASS=RMAN_MTBackup_Tier3)';
Step 3
restore controlfile from autobackup;
Step 4
alter database mount;
Step 5 - set recovery time/scn /log_seq
set until logseq=366 thread=1;
set until scn 562449965260;
set until time 'Jul 01 2002 00:02:00';
set until time "to_date('01-28-2004 11:40:54','mm-dd-yyyy hh24:mi:ss')";
Step6
restore database;
recover database;
alter database open resetlogs;
Metalink
224354.1
Thursday, April 12, 2007
How to Use DBMS_STATS to Move Statistics to a Different Database
Create stat table
SQL> exec dbms_stats.create_stat_table('SCOTT','STATS');
Export statistics into stat table
SQL> exec dbms_stats.export_table_stats('SCOTT',-
'EMP',NULL,'STATS',NULL,TRUE);
Export stat table from source database
%exp scott/tiger tables=STATS file=expstat.dmp
Import stat table into target database
%imp scott/tiger file=expstat.dmp full=y log=implog.txt
Import statistics into table
SQL> exec dbms_stats.import_table_stats('SCOTT',-
'EMP',-NULL,'STATS',NULL,TRUE);
SQL >
SQL> exec dbms_stats.create_stat_table('SCOTT','STATS');
Export statistics into stat table
SQL> exec dbms_stats.export_table_stats('SCOTT',-
'EMP',NULL,'STATS',NULL,TRUE);
Export stat table from source database
%exp scott/tiger tables=STATS file=expstat.dmp
Import stat table into target database
%imp scott/tiger file=expstat.dmp full=y log=implog.txt
Import statistics into table
SQL> exec dbms_stats.import_table_stats('SCOTT',-
'EMP',-NULL,'STATS',NULL,TRUE);
SQL >
Tuesday, April 10, 2007
How To Trace Problem SQL
Trace Other's Session
exec sys.dbms_system.set_ev(130, 54378, 10046, 8, ''); #(sid=130 and serial#=54378)
exec sys.dbms_system.set_ev(130, 54378, 10046, 0, '');
LE - Level eg: 1 (SQL_TRACE), 4 (SQL_TRACE+BINDS), 8 (SQL_TRACE+WAITS), or 12(SQL_TRACE+BINDS+WAITS)
Trace Own Session
Set session parameter, if any
alter session set "_optim_peek_user_binds"=false;
Generate 10046 and 10053 traces for query
-- Set event 10046 and run the query.
alter session set tracefile_identifier = ee10046_literal;
alter session set events '10046 trace name context forever, level 12';
@sql
alter session set events '10046 trace name context off';
exit
-- Set event 10053 and run the query.
alter session set tracefile_identifier = ee10053_literal;
alter session set events '10053 trace name context forever, level 1';
@sql
alter session set events '10053 trace name context off';
exit
Generate tkprof output the 10046 trace files using following syntax (change the file name, username & password):
tkprof tracefile outputfile sys=no explain=query_username/password
exec sys.dbms_system.set_ev(130, 54378, 10046, 8, ''); #(sid=130 and serial#=54378)
exec sys.dbms_system.set_ev(130, 54378, 10046, 0, '');
LE - Level eg: 1 (SQL_TRACE), 4 (SQL_TRACE+BINDS), 8 (SQL_TRACE+WAITS), or 12(SQL_TRACE+BINDS+WAITS)
Trace Own Session
Set session parameter, if any
alter session set "_optim_peek_user_binds"=false;
Generate 10046 and 10053 traces for query
-- Set event 10046 and run the query.
alter session set tracefile_identifier = ee10046_literal;
alter session set events '10046 trace name context forever, level 12';
@sql
alter session set events '10046 trace name context off';
exit
-- Set event 10053 and run the query.
alter session set tracefile_identifier = ee10053_literal;
alter session set events '10053 trace name context forever, level 1';
@sql
alter session set events '10053 trace name context off';
exit
Generate tkprof output the 10046 trace files using following syntax (change the file name, username & password):
tkprof tracefile outputfile sys=no explain=query_username/password
Subscribe to:
Posts (Atom)