Login to one of the nodes (i.e. linux1) and disable the cluster instance parameter by setting cluster_database to FALSE from the current instance:
$ sqlplus “/ as sysdba”
SQL> alter system set cluster_database=false scope=spfile sid=’orcl1′;

Shutdown all instances accessing the clustered database:
$ srvctl stop database -d orcl

Using the local instance, MOUNT the database:
$ sqlplus “/ as sysdba”
SQL> startup mount
Enable archiving:
SQL> alter database archivelog;

Re-enable support for clustering by modifying the instance parameter cluster_database to TRUE from the current instance:
SQL> alter system set cluster_database=true scope=spfile sid=’orcl1′;
Shutdown the local instance:
SQL> shutdown immediate

Bring all instance back up using srvctl:
$ srvctl start database -d orcl
(Optional) Bring any services (i.e. TAF) back up using srvctl:
$ srvctl start service -d orcl

Login to the local instance and verify Archive Log Mode is enabled:
$ sqlplus “/ as sysdba”
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 83
Next log sequence to archive 84
Current log sequence 84

Sequence of events to bring cluster database back..

1.    Start all services using “start -nodeapps”
2.    Start ASM instnace using “srvctl start asm -n (node)”
3.    Start RAC instances using “srvctl start instance -d (database) -I (instance)”
4.    Finish up by bringing our load balanced/TAF service online “srvctl start service -d orcl -s RAC”

List of nodes and other information for all nodes participating in the cluster:

[oracle@oradb4 oracle]$ olsnodes -n
oradb4 oradb3 oradb2 oradb1

List all nodes participating in the cluster with their assigned node numbers:

[oracle@oradb4 tmp]$ olsnodes -n
oradb4 1 oradb3 2 oradb2 3 oradb1 4

List all nodes participating in the cluster with the private interconnect assigned to each node:

[oracle@oradb4 tmp]$ olsnodes -p
oradb4 oradb4-priv oradb3 oradb3-priv oradb2 oradb2-priv oradb1 oradb1-pr

Check the health of the Oracle Clusterware daemon processes:

[oracle@oradb4 oracle]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

Query and administer css vote disks :

[root@oradb4 root]# crsctl add css votedisk /u03/oradata/ CssVoteDisk.dbf
Now formatting voting disk: /u03/oradata/CssVoteDisk.dbfRead -1 bytes of 512 at offset 0 in voting device (CssVoteDisk.dbf) successful addition of votedisk /u03/oradata/CssVoteDisk.dbf

For dynamic state dump of the CRS:

[root@oradb4 root]# crsctl debug statedump crs
dumping State for crs objects Dynamic state dump information is appended to the crsd log file located in the $ORA_CRS_HOME/log/oradb4/crsd directory.

Verify the Oracle Clusterware version:

[oracle@oradb4 log]$ crsctl query crs softwareversion
CRS software version on node [oradb4] is [10.2.0.0.0]

Verify the current version of Oracle Clusterware being used:

[oracle@oradb4 log]$ crsctl query crs activeversion
CRS active version on the cluster is [10.2.0.0.0]

Different DBAs have different views on % for analyze. The oracle documentation recommends to carry full analyze on entire database which is not possible for most live systems runnning into terabytes.
In past I had performance issue on my database just over 300 GB. There were one table ORDER_DATA with 400 million rows. That one table pulled entire system down number of times just because it wasn’t properly analyzed and oracle didn’t knew data distribution in the table.
I was struggling to understand where things are going wrong as we were analysing entire table every night but not with hash buckets-histograms and surprisingly in SQL execution it was using a right index.

After spending days & weeks investigating the issue, I reanalyzed it with new oracle API for histograms and SQL which used to take between 15-60 min started running at less than 100 milliseconds.

What to look for?
First check STATSPACK and find out the most active tables.
Analyse most active tables once a week with 10-15% sampling
For BIG tables start with 1% sampling and buld over period of time
I also observed adding parallel option in ANALYZE can reduce time taken significantly.

— Added to 9i init.ora
— parallel_automatic_tuning=true
— parallel_max_servers=16
— parallel_min_servers=4
— Changed percent to 1, all idx cols changed degree to 16 from 10
begin
dbms_stats.gather_table_stats(ownname=>’USER’,tabname =>’TABLE_NAME’,
estimate_percent => 1,method_opt=>’for all indexed columns’,
degree=>16,CASCADE=>TRUE);
end ;

begin
dbms_stats.gather_schema_stats(ownname=>’USER’,
estimate_percent => 20,method_opt=>’for all indexed columns’,
degree=>16,CASCADE=>TRUE);
end ;

Other Examples         

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);
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);
GATHER_SCHEMA_STATS(ownname,estimate_percent,block_sample,method_opt,degree,granularity,cascade, stattab,statid,options,statown,no_invalidate,gather_temp,gather_fixed);

Exporting and Importing Statistics

Caveat: Always use import/export and use imp/exp utility on schema user who owns tables.
I have wasted a week where I was exporting as DBA for XYZ user and then importing into
different system under different username.

Statistics can be exported and imported from the data dictionary to user-owned tables. This enables to create multiple versions of statistics for the same schema. One can also copy statistics from one database to another database.

You may want to copy statistics from a production database to a scaled-down test database to look at SQL execution plans.

Note:
Exporting and importing statistics is a distinct concept from the EXP and IMP utilities of the database.

The DBMS_STATS export and import packages do utilize IMP and EXP dump files.

Before exporting statistics, you first need to create a table for holding the statistics.

This statistics table is created using the procedure  DBMS_STATS.CREATE_STAT_TABLE.

After this table is created, then you can export statistics from the data dictionary into your statistics table using the DBMS_STATS.EXPORT_*_STATS procedures.

The statistics can then be imported using the DBMS_STATS.IMPORT_*_STATS procedures.

Note that the optimizer does not use statistics stored in a user-owned table. The only statistics used by the optimizer are the statistics stored in the data dictionary.

In order to have the optimizer use the statistics in a user-owned tables, you must import those statistics into the data dictionary using the statistics import procedures.

In order to move statistics from one database to another, you must first export the statistics on the first database, then copy the statistics table to the second database, using the EXP and IMP utilities or other mechanisms, and finally import the statistics into the second database.

The EXP and IMP utilities export and import optimizer statistics from the database along with the table. One exception is that statistics are not exported with the data if a table has columns with system-generated names.

Restoring Statistics Versus Importing or Exporting Statistics

The functionality for restoring statistics is similar in some respects to the functionality of importing and exporting statistics.

In general, you should use the restore capability when:

• You want to recover older versions of the statistics. For example, to restore the optimizer behavior to an earlier date.
• You want the database to manage the retention and purging of statistics histories.

You should use EXPORT/IMPORT_*_STATS procedures when:

• You want to experiment with multiple sets of statistics and change the values back and forth.
• You want to move the statistics from one database to another database. For example, moving statistics from a production system to a test system.
• You want to preserve a known set of statistics for a longer period of time than the desired retention date for restoring statistics.

1. Create the Statistics table.

exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SCHEMA_NAME' ,stat_tab => 'STATS_TABLE' ,
tblspace => 'STATS_TABLESPACE');
>>>>>>>> For 10G
exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SYSTEM',stat_tab => 'STATS_TABLE');
>>>>>>>>  For 9i and earlier
begin
 DBMS_STATS.CREATE_STAT_TABLE('dba_admin','STATS_TABLE');
end;

2. Export statistics to statistics table

EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('ORIGINAL_SCHEMA' ,'STATS_TABLE',NULL,'SYSTEM');

3. Import statistics into the data dictionary.
exec DBMS_STATS.IMPORT_SCHEMA_STATS('NEW_SCHEMA','STATS_TABLE',NULL,'SYSTEM');

4. Drop the statistics table.
exec DBMS_STATS.DROP_STAT_TABLE('SYSTEM','STATS_TABLE');

FOR 9i
begin
DBMS_STATS.CREATE_STAT_TABLE('dba_admin','STATISTICS_TABLE_060307');
end;
begin
DBMS_STATS.EXPORT_SCHEMA_STATS('SAPBP2' ,'STATISTICS_TABLE_060307',NULL,'DBA_ADMIN');
end;
SQL> exec DBMS_STATS.IMPORT_SCHEMA_STATS('SAGAR','STATISTICS_TABLE_060307',NULL,'SAGAR');
PL/SQL procedure successfully completed.

Monitor export Process >>>>>>>>
select count(*) from &STATS_NAME
Stats table can grow exponentially so look at table size while export is active.
select sum(bytes)/1000000 from dba_extents where segment_name='&TABLE_NAME'
Sample statistics at SAP BW System of size 4.2 Tera bytes
Time Elapsed for Export : 40 Mins
Total stats Table Size : 2GB
Time Elapsed for Import :

How to Validate that Stats are reflected after exp/imp

select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS,
AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN
from dba_tables where owner='&USER'
At both Databases and see they are very similar.

1. Login as PERFSTAT user on database.  It won’t work unless U login as PERFSTAT user

2. Find DBID using  “select dbid from stats$sql_summary”

3. Locate MIN(SNAP_ID) pBgnSnap & MAX(SNAP_ID) pEndSnap

SELECT   MIN (snap_id),
 MAX (snap_id),
 MIN (snap_time),
 MAX (snap_time)
 FROM   stats$snapshot
 WHERE       TO_NUMBER (TO_CHAR (snap_time, 'HH24')) > 10
 AND TO_NUMBER (TO_CHAR (snap_time, 'HH24')) < 13
 AND TRUNC (snap_time) = TRUNC (SYSDATE)

Show All SQL Stmts ordered by Logical Reads

SELECT   e.hash_value "E.HASH_VALUE",
 e.module "Module",
 e.buffer_gets - NVL (b.buffer_gets, 0) "Buffer Gets",
 e.executions - NVL (b.executions, 0) "Executions",
 ROUND (
 DECODE (
 (e.executions - NVL (b.executions, 0)),
 0,
 TO_NUMBER (NULL),
 (e.buffer_gets - NVL (b.buffer_gets, 0))
 / (e.executions - NVL (b.executions, 0))
 ),
 3
 )
 "Gets / Execution",
 ROUND (  100
 * (e.buffer_gets - NVL (b.buffer_gets, 0))
 / sp920.getGets (:pDbID,
 :pInstNum,
 :pBgnSnap,
 :pEndSnap,
 'NO'), 3)
 "Percent of Total",
 ROUND ( (e.cpu_time - NVL (b.cpu_time, 0)) / 1000000, 3) "CPU (s)",
 ROUND ( (e.elapsed_time - NVL (b.elapsed_time, 0)) / 1000000, 3)
 "Elapsed (s)",
 ROUND (e.fetches - NVL (b.fetches, 0)) "Fetches",
 sp920.getSQLText (e.hash_value, 400) "SQL Statement"
 FROM   stats$sql_summary e, stats$sql_summary b
 WHERE       b.snap_id(+) = :pBgnSnap
 AND b.dbid(+) = e.dbid
 AND b.instance_number(+) = e.instance_number
 AND b.hash_value(+) = e.hash_value
 AND b.address(+) = e.address
 AND b.text_subset(+) = e.text_subset
 AND e.snap_id = :pEndSnap
 AND e.dbid = :pDbId
 AND e.instance_number = :pInstNum
ORDER BY   3 DESC

Show SQL Stmts where SQL_TEXT like '%'

SELECT   e.hash_value "E.HASH_VALUE",
 e.module "Module",
 e.buffer_gets - NVL (b.buffer_gets, 0) "Buffer Gets",
 e.executions - NVL (b.executions, 0) "Executions",
 ROUND (
 DECODE (
 (e.executions - NVL (b.executions, 0)),
 0,
 TO_NUMBER (NULL),
 (e.buffer_gets - NVL (b.buffer_gets, 0))
 / (e.executions - NVL (b.executions, 0))
 ),
 3
 )
 "Gets / Execution",
 ROUND (  100
 * (e.buffer_gets - NVL (b.buffer_gets, 0))
 / sp920.getGets (:pDbID,
 :pInstNum,
 :pBgnSnap,
 :pEndSnap,
 'NO'), 3)
 "Percent of Total",
 ROUND ( (e.cpu_time - NVL (b.cpu_time, 0)) / 1000000, 3) "CPU (s)",
 ROUND ( (e.elapsed_time - NVL (b.elapsed_time, 0)) / 1000000, 3)
 "Elapsed (s)",
 ROUND (e.fetches - NVL (b.fetches, 0)) "Fetches",
 sp920.getSQLText (e.hash_value, 400) "SQL Statement"
 FROM   stats$sql_summary e, stats$sql_summary b
 WHERE       b.snap_id(+) = :pBgnSnap
 AND b.dbid(+) = e.dbid
 AND b.instance_number(+) = e.instance_number
 AND b.hash_value(+) = e.hash_value
 AND b.address(+) = e.address
 AND b.text_subset(+) = e.text_subset
 AND e.snap_id = :pEndSnap
 AND e.dbid = 2863128100
 AND e.instance_number = :pInstNum
 AND sp920.getSQLText (e.hash_value, 400) LIKE '%ZPV_DATA%'
ORDER BY   3 DESC

Change a.statistic# to respective value

Stats for Working Hours

select to_char(trunc(b.snap_time),'DD-MM-YYYY') ,statistic#,name, sum(value) from STATS$SYSSTAT A, stats$snapshot B
where a.snap_id=b.snap_id
and trunc(b.snap_time) > trunc(sysdate -30)
and to_char(b.SNAP_TIME,'HH24') > 8
and to_char(b.SNAP_TIME,'HH24') <18
and a.statistic#=54
group by trunc(b.snap_time) ,statistic#,name
order by trunc(b.snap_time)

Locate kind of stats you want to pull from statspack

(select * from STATS$SYSSTAT where name like '%XXX%' )
9 session logical reads
Physical Reads
54 Physical Reads
56 Physical reads direct
58 physical read bytes
39 physical read total bytes
42 physical write total bytes
66 physical write bytes
66 physical writes
CPU Related
355 OS Wait-cpu (latency) time
328 parse time cpu
8 recursive cpu usage
Rollback Related -
176 transaction tables consistent read rollbacks
180 rollbacks only - consistent read gets
181 cleanouts and rollbacks - consistent read gets
187 transaction rollbacks
5 user rollbacks
239 IMU CR rollbacks
186 rollback changes - undo records applied

Stats for Entire Day

select to_char(trunc(b.snap_time),'DD-MM-YYYY') ,statistic#,name, sum(value) from STATS$SYSSTAT A, stats$snapshot B
where a.snap_id=b.snap_id
and trunc(b.snap_time) > trunc(sysdate -30)
and a.statistic#=54
group by trunc(b.snap_time) ,statistic#,name
order by trunc(b.snap_time)

Create RMAN Catalogue steps
• Select database for catalogue.
• Create catalogue owner RMANC, default tablespace TOOLS.
• Grant RECOVERY_CATALOG_OWNER role to RMANC.
• Run the RMAN executable, being the same version as the catalogue database codeset and connect to the catalogue as the RMANC user.
• Issue the CREATE CATALOG command.

At RMAN repository Database

SQL>   create user rmanc identified by xxxxxxxx
   Temporary tablespace TEMP
   Default tablespace TOOLS quota unlimited on TOOLS;
SQL>   grant RECOVERY_CATALOG_OWNER to RMANC;
%RMAN catalog RMANC/xxxxxxxx@catdb
rman>   CREATE CATALOG

At Target Database which need to be backed up thru RMAN
Before registering a target database, create the target rman user in the target database.

SQL>   connect / as sysdba
SQL>   create user RMANT identified by xxxxxxxx
   Default tablespace TOOLS
   Temporary tablespace TEMP;
SQL>   grant SYSDBA,CREATE SESSION to RMANT;

Initialisation Parameters
To improve backup performance following parameters must be set….
• BACKUP_TAPE_IO_SLAVES = TRUE
For 8i
• LARGE_POOL_SIZE = (4 *
<number> *
DB_BLOCK_SIZE *
DB_FILE_DIRECT_IO_COUNT *
<level>
) +
(4 *
<number> *
<size>
)
For 9i
• LARGE_POOL_SIZE= <number>*
(16MB +
<size>
)
The size of a tape buffer is determined by the RMAN parameter BLKSIZE.
BLKSIZE is OS dependant, but performance benefits can be made by explicitly setting it higher. 4M seems to offer improved performance. When testing a new backup, try differing values, in conjunction with the above calculation for LARGE_POOL_SIZE, to determine the optimal setting.
Net Requirements

RMAN requires a dedicated server connection to the target database.
Targetdb_rman.domain =
   (DESCRIPTION=
      (ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521))
      (CONNECT_DATA=(SERVICE_NAME=dbname.db_domain)(SERVER=DEDICATED))
   )

The above should be configured on both the target and catalogue servers. The server hosting the target database also requires a Net configuration for the catalogue database, as this is where the RMAN commands are normally issued.
Database registration

To register the target database, issue the following commands.

> rman TARGET  rmanc/xxxxxxxx@targetdb  CATALOG rmant/xxxxxxxx@cataloguedb
rman> REGISTER DATABASE;
To check successful registration, issue the following command whilst still in RMAN;
rman> REPORT SCHEMA;
RMAN> report schema;
Report of database schema
File K-bytes    Tablespace           RB segs Datafile Name
---- ---------- -------------------- ------- ------------------- 
1        524288 SYSTEM               YES     G:\ORACLE\ORADATA\TKNWP\SYSTEM01.DB
F
2        524288 UNDOTBS              YES     G:\ORACLE\ORADATA\TKNWP\UNDOTBS01.D
BF
3         20480 GENESYS_CONFIG_DATA  NO      I:\ORACLE\ORADATA\TKNWP\GENESYS_CON
FIG_DATA_01.DBF
4         20480 GENESYS_LOGS_DATA    NO      G:\ORACLE\ORADATA\TKNWP\GENESYS_LOG
S_DATA_01.DBF
5        131072 TOOLS                NO      H:\ORACLE\ORADATA\TKNWP\TOOLS_01.DB
F
6         25600 USERS                NO      H:\ORACLE\ORADATA\TKNWP\USERS_01.DB
F
7        256000 PERFSTAT             NO      G:\ORACLE\ORADATA\TKNWP\PERFSTAT_01
.DBF

Post-Recovery/Standby failover Tasks
If a resetlogs command has been issued, or a standby database has been activated, this needs to be reflected in the recovery catalogue. Any operations in RMAN after these operations will fail with the ‘target database incarnation not found in recovery catalog’ error. This is due to the resetlogs command, resetting the SCN’s. As the database Id is still the same, we need to issue the ‘reset database’ command to align the recovery catalogue with the database.
> rman TARGET rmanc/xxxxxxxx@targetdb CATALOG rmant/xxxxxxxx@cataloguedb
rman> RESET DATABASE;
To check;
rman> list incarnation of database;
Monitoring an RMAN Backup
To view the progress of a backup, the v$ views can be queried. Here is an example of a monitoring script:
Execute this whilst the backup is processing:

SELECT sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) "% Complete"
FROM v$session_longops
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND totalwork != 0
AND sofar <totalwork>/
SID SERIAL# CONTEXT SOFAR TOTALWORK % Complete
------ ---------- ---------- ---------- ---------- ---------- 
8 19 1 10377 36617 28.34
SQL>/
SID SERIAL# CONTEXT SOFAR TOTALWORK % Complete
---- ---------- ---------- ---------- ---------- ---------- 
8 19 1 21513 36617 58.75
SQL>/
SID SERIAL# CONTEXT SOFAR TOTALWORK % Complete
----- ---------- ---------- ---------- ---------- ---------- 
8 19 1 29641 36617 80.95
SQL>/
SID SERIAL# CONTEXT SOFAR TOTALWORK % Complete
----- ---------- ---------- ---------- ---------- ---------- 
8 19 1 35849 36617 97.9
SQL>/
no rows selected

The views V$BACKUP_ASYNC_IO and V$BACKUP_SYNC_IO contain detailed information on backup operations. This data is not persistent. If there is data in the V$BACKUP_SYNC_IO view, then you need to investigate why the backups are not running asynchronously. Check BACKUP_TAPE_IO_SLAVES is set.

Datafiles Backed up during past 24 Hours

SELECT dbfiles||' from '||numfiles "Datafiles backed up",
cfiles "Control Files backed up", spfiles "SPFiles backed up"
FROM (select count(*) numfiles from sys.v_$datafile),
(select count(*) dbfiles
from sys.v_$backup_datafile a, sys.v_$datafile b
where a.file# = b.file#
and a.completion_time > sysdate - 1),
(select count(*) cfiles from sys.v_$backup_datafile
where file# = 0 and completion_time > sysdate - 1),
(select count(*) spfiles from sys.v_$backup_spfile
where completion_time > sysdate - 1) 

Archlog Files Backed up during past 24 Hours

SELECT backedup||' from '||archived "Archlog files backed up",
ondisk "Archlog files still on disk"
FROM (select count(*) archived
from sys.v_$archived_log where completion_time > sysdate - 1),
(select count(*) backedup from sys.v_$archived_log
where backup_count > 0
and completion_time > sysdate - 1),
(select count(*) ondisk from sys.v_$archived_log
where archived = 'YES' and deleted = 'NO')

RMAN Backups Still Running:

SELECT to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
sofar, totalwork,
elapsed_seconds/60 "ELAPSE (Min)",
round(sofar/totalwork*100,2) "Complete%"
FROM sys.v_$session_longops
WHERE compnam = 'dbms_backup_restore'
/

BACKUP STARTED,SOFAR,TOTALWORK,ELAPSE (Min),Complete%
27-JUN-07 09:54,755,45683,2.73333333333333,1.65
27-JUN-07 09:52,1283,10947,4.36666666666667,11.72
27-JUN-07 09:46,11275,11275,0.783333333333333,100
27-JUN-07 09:46,58723,58723,5.73333333333333,100
27-JUN-07 09:46,12363,12363,0.333333333333333,100
27-JUN-07 09:44,11115,11115,4.53333333333333,100
27-JUN-07 09:44,12371,12371,0.183333333333333,100
27-JUN-07 07:34,4706,4706,0.166666666666667,100
27-JUN-07 07:34,83729,83729,118.35,100
27-JUN-07 05:21,8433,8433,0.333333333333333,100
27-JUN-07 05:21,83729,83729,132.25,100

Some cheap/easy ways to Install RAC on inexpensive hardware

Importance of VIP
Real Application Clusters in 10g, however, don’t particularly want you to connect to physical IP address associated with network interface.
Doing so means IP packets are routed to a physical MAC address, so that if that address ever ceases to exist (such as when a server dies), we have to wait for TCP/IP networking protocol itself to work out that packets are undeliverable.
That can take up to 10 minutes, and would mean failover in a RAC can potentially be very slow.
Instead, Oracle wants users to connect to a Virtual IP Address (VIP). That’s an IP address that’s bound to a software-controlled MAC address and since it’s software controlled, the software can arrange for failures to be handled a lot quicker than plain old TCP/IP stack (in seconds, usually).
The VIP for a RAC node is quite often the normal, real IP address plus one so, in my case, that would imply a VIP of 192.168.1.111. I won’t be needing this until it comes time to installing the Oracle software, but it’s good to plan ahead.
Even if check fails you can continue the installation,by configuring vipca seaparately , even if that IP does not exist (no need that you own that ip).

Metadata API (DBMS_METADATA)
Prior to Oracle9i metadata could only be extracted using SQL statements, export utilities and the OCIDescribeAny interface, all of which are limited:
* The SQL approach is limited in that as versions change, so must your scripts.
* Using export with ROWS=N and an import with SHOW=Y will produce the text to allow you to recreate a schema, but it can require considerable editing.
* The OCIDescribeAny interface is limited in the objects it supports.
To solve these issues Oracle9i has introduced the DBMS_METADATA package which can be used to retrieve object definitions as XML or SQL DDL:

FUNCTION Get_XML (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
FUNCTION Get_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT NULL)
RETURN CLOB; 

These functions can be used as part of a select statement to produce output for groups of objects:

SET LONG 20000
SET PAGESIZE 0
SELECT DBMS_METADATA.get_ddl ('TABLE','EMP','SCOTT')
FROM dual;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
1 row selected.

The results can be spooled into a file for editing.

http://www.oracle-base.com/articles/8i/ShellCommandsFromPLSQL.php

Our preferred v$sqlarea query is to actually report on physical disk I/O per statement execution. Hit ratios are informative but sometimes misleading. Logical I/O is less relevant. If the statement executes 1,000,000 logical I/Os but still only takes less than one-tenth of a second, who cares? It is the total physical I/O that consumes nearly all the time and identifies the potentially incorrect SQL. For example:

SELECT sql_text, executions,
ROUND(disk_reads / executions, 2) reads_per_run,
disk_reads, buffer_gets,
ROUND((buffer_gets – disk_reads)/
buffer_gets, 2) hit_ratio,
SQL_TEXT
FROM v$sqlarea
WHERE executions > 0
AND buffer_gets > 0
AND (buffer_gets – disk_reads) / buffer_gets < 0.80
ORDER by 3 desc ;

The previous two statements would have reported more enlightening results:
Executions Reads_Per_Run Disk_Reads Buffer_Gets Hit_Ratio Sql_Text
———- ————- ———- ———– ——— ————
2 3 6 19 0.68 SELECT …
2 1812.5 3625 178777 0.98 SELECT …
From this view of the v$sqlarea table, we can immediately isolate all statements that are performing high numbers of physical reads. These statements might not necessarily be inefficient or poorly written, but they are prime candidates for investigation and further tuning.

System Parameters Purpose: Reports all system parameters, including the hidden ones

select
nam.indx+1 numb,
nam.ksppinm name,
val.ksppstvl value,
nam.ksppity type,
val.ksppstdf is_default,
decode(bitand(nam.ksppiflg/256,1),
1,'True',
'False'
) is_session_modifiable,
decode(bitand(nam.ksppiflg/65536,3),
1,'Immediate',
2,'Deferred' ,
3,'Immediate',
'False'
) is_system_modifiable,
decode(bitand(val.ksppstvf,7),
1,'Modified',
4,'System Modified',
'False'
) is_modified,
decode(bitand(val.ksppstvf,2),
2,'True',
'False'
) is_adjusted,
nam.ksppdesc description
from
x$ksppi nam,
x$ksppsv val
where
nam.indx = val.indx
and
nam.ksppinm like '&&PARAMETER%';

Session Parameters

select
nam.indx+1 numb,
nam.ksppinm name,
val.ksppstvl value,
nam.ksppity type,
val.ksppstdf is_default,
decode(bitand(nam.ksppiflg/256,1),
1,'True',
'False'
) is_session_modifiable,
decode(bitand(nam.ksppiflg/65536,3),
1,'Immediate',
2,'Deferred' ,
3,'Immediate',
'False'
) is_system_modifiable,
decode(bitand(val.ksppstvf,7),
1,'Modified',
4,'System Modified',
'False'
) is_modified,
decode(bitand(val.ksppstvf,2),
2,'True',
'False'
) is_adjusted,
nam.ksppdesc description
from
x$ksppi nam,
x$ksppcv val
where
nam.indx = val.indx

I have seen this issue on number of SAP systems. I will compile a procedure I followed here.

select segment_name,
segment_type,
owner,
tablespace_name
from dba_extents
where file_id = <file_id>
and <block> between block_id and block_id + blocks - 1;

For example, given this information:
Corrupt Block Found
TSN = 24, TSNAME = STATS_INDEXES_06
RFN = 23, BLK = 211531, RDBA = 96680523
OBJN = -1, OBJD = 102711, OBJECT = , SUBOBJECT =
I would execute:
select segment_name,
segment_type,
owner,
tablespace_name
from dba_extents
where file_id = 23
and 211531 between block_id and block_id + blocks – 1;

Alrtlog messages
Corrupt Block Found
TSN = 24, TSNAME = STATS_INDEXES_06
RFN = 23, BLK = 293131, RDBA = 96762123
OBJN = -1, OBJD = 103744, OBJECT = , SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE =
ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [4194], [29], [42],
Alrtlog Errors :
Errors in file /oracle/BPA/saptrace/background/bpa_smon_19615.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [29], [42], [], [], [], [], []
Errors in file /oracle/BPA/saptrace/background/bpa_smon_19615.trc:
ORA-00600: internal error code, arguments: [4193], [21415], [64890], [], [], [], [], []
Tue Aug 7 10:10:10 2007
Errors in file /oracle/BPA/saptrace/usertrace/bpa_ora_19821.trc:
ORA-00600: internal error code, arguments: [4194], [29], [42], [], [], [], [], []

As per undo corruption, restore from backup may be the best and fastest solution here. Can this be an option here?
— Restoring from last backup is not an easy option for us. It’s 3.5 Tera byte database. It costs time and resources which I didn’t had here.

DBVERIFY
It can be used to perform a physical data structure integrity check on data files whether the database is online or offline.

dbv file=system_ts_01.dbf blocksize=8192 logfile=dbvsys_ts.log feedback=1000
Data file = system_ts_01.dbf.
Start block is 9
end block is 25
Blocksize 8192

Logfile parameter specifies the file to which logging information written feedback To display one dot on the screen for every 1000 blocks processed

Oracle Support Communication :
This database was restored from live system last Monday. Everything went very well and database was running fine until Monday morning. Y’day we reported this error first time after running system for 5/6
days. A restart at afternoon fixed those ora-600 errors until this morning when we are getting ora-600 at all times. It is necessary to identify what went wrong before thinking for restore. Personally I don’t see a point restoring entire da tabase when there is no guarantee that these errors will go away after restore.
As per undo corruption, restore from backup may be the best and fastest solution here. Can this be an option here?
–Restoring from last backup is not an easy option for us. It’s 3.5 Tera byte database. It costs time and resources which we don’t have available
ACTION PLAN
============
Let’s start checking objects reported on recovery processes:
fno: 81 blk: 322532
fno: 18 blk: 125593
fno: 18 blk: 125673
fno: 116 blk: 266003

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = &fno
and &blk between block_id AND block_id + blocks – 1;
TABLESPACE_NAME SEGMENT_TYPE OWNER
—————————— —————— ——————————
SEGMENT_NAME
——————————————————————————–
PSAPUNDO TYPE2 UNDO SYS
_SYSSMU37$
no rows selected
no rows selected
PSAPUNDO TYPE2 UNDO SYS
_SYSSMU42$

Oracle Support Response We’re facing undo corruption here. Using _corrupted or _offline parameters will be helpful to export full database, but if there are active transactions (as I guess) this database will be desupported after setting them. Let’s try to dump undo headers to check if there are active transactions:

alter system dump undo header <undo>;
Depending on what trace file with header dump tells we can decide what to do. If there are no active transactions, we can try to drop these undo segments. Otherwise, we can just try to restore these datafiles first (the 3 ones) and apply recover on them
== Check if there are Active x’actions before dropping UNDO segments==
We can check
TRN TBL::
which is the starting for the transaction table dump.
On state column, we can see status. 9 means commited, 10 means active. So in our case, all were 9s.

ACTION PLAN
============
As per all transactions are committed, we can try to drop these undo segments. As per using AUM, we need to set smu_debug to allow actions
on undo segments, or place AUM to manual
SQL> alter session set “_smu_debug_mode”=4;
SQL> alter rollback segment “_SYSSMU37$” offline;
-> Check if undo segment went offline in DBA_ROLLBACK_SEGS. If so:
SQL> drop rollback segment “_SYSSMU37$”;
SQL> alter rollback segment “_SYSSMU42$” offline;
-> Check if undo segment went offline in DBA_ROLLBACK_SEGS. If so:
SQL> drop rollback segment “_SYSSMU42$”;
A clean database restart after this was done and things were sorted.

undo_retention parameter will not guarantee a RETENTION unless you define a RETENTION GUARANTEE CLAUSE on tablespace level

Actual Undo Size

SELECT SUM(a.bytes) “UNDO_SIZE”
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = ‘UNDO’
AND c.status = ‘ONLINE’
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;

UNDO_SIZE
———-
209715200

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
“UNDO_BLOCK_PER_SEC”
FROM v$undostat;

UNDO_BLOCK_PER_SEC
——————
3.12166667

DB Block Size
SELECT TO_NUMBER(value) “DB_BLOCK_SIZE [KByte]”
FROM v$parameter
WHERE name = ‘db_block_size’;

DB_BLOCK_SIZE [Byte]
——————–
4096

Calculation for Optimal Undo Retention
209’715’200 / (3.12166667 * 4’096) = 16’401 [Sec]  Using Inline Views, you can do all in one query!

SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]”,
SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]”,
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) “OPTIMAL UNDO RETENTION [Sec]”
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = ‘UNDO’
AND c.status = ‘ONLINE’
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = ‘undo_retention’
AND f.name = ‘db_block_size’
/
ACTUAL UNDO SIZE [MByte]
————————
200
UNDO RETENTION [Sec]
——————–
10800
OPTIMAL UNDO RETENTION [Sec]
—————————-
16401

Calculate Needed UNDO Size for given Database Activity

SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]”,
SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]”,
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
“NEEDED UNDO SIZE [MByte]”
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = ‘UNDO’
AND c.status = ‘ONLINE’
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = ‘undo_retention’
AND f.name = ‘db_block_size’
/
ACTUAL UNDO SIZE [MByte]
————————
200
UNDO RETENTION [Sec]
——————–
10800
NEEDED UNDO SIZE [MByte]
————————
131.695313

1> What resource is currently in high demand?

select  active_session_history.event
 , sum ( active_session_history.wait_time + active_session_history.time_waited ) ttl_wait_time
 from v$active_session_history active_session_history
 where active_session_history.sample_time between
sysdate – 60 / 2880
 and sysdate group by active_session_history.event
 order by 2

select  sesion.sid
 , sesion.username
 , sum ( active_session_history.wait_time + active_session_history.time_waited ) ttl_wait_time
 from v$active_session_history active_session_history
 , v$session sesion
 where active_session_history.sample_time between
sysdate - 60 / 2880
 and sysdate and active_session_history.session_id = sesion.sid
 group by sesion.sid
 , sesion.username
 order by 3

SID User TTL_WAIT_TIME
—– ———- ————-
135 SCOTT 91167481
149 SCOTT 107409491
153 SCOTT 110796799

select  active_session_history.user_id
 , dba_users.username
 , sqlarea.sql_text
 , sum ( active_session_history.wait_time + active_session_history.time_waited ) ttl_wait_time
 from v$active_session_history active_session_history
 , v$sqlarea sqlarea
 , dba_users
 where active_session_history.sample_time between
sysdate - 60 / 2880
 and sysdate and active_session_history.sql_id = sqlarea.sql_id
 and active_session_history.user_id = dba_users.user_id
 group by active_session_history.user_id
 , sqlarea.sql_text
 , dba_users.username
 order by 4;

USER_ID User SQL_TEXT TTL_WAIT_TIME
——- —— —————————————————– ————-
57 SCOTT insert into sys.sourcetable (select * from sys.source$) 304169752

4> What object is currently causing the highest resource waits?

select  dba_objects.object_name
 , dba_objects.object_type
 , active_session_history.event
 , sum ( active_session_history.wait_time + active_session_history.time_waited ) ttl_wait_time
 from v$active_session_history active_session_history
 , dba_objects
 where active_session_history.sample_time between
sysdate - 60 / 2880
 and sysdate and active_session_history.current_obj# = dba_objects.object_id
 group by dba_objects.object_name
 , dba_objects.object_type
 , active_session_history.event
 order by 4

The easiest approach is to use an Oracle utldtree.sql script. This will be located in the %ORACLE_HOME%\rdbms\admin\ directory.

For each schema that you want to be able to get a dependency list, you will open up a SQL*Plus session and execute the script
scott@> @d:\oracle\ora92\rdbms\admin\utldtree.sql
Don’t worry about the ORA-00942 errors you will get– this script tries to drop objects before it re-creates them, so you’ll get errors when the objects don’t exist yet.
Once the script has been run, you can get a listing of dependent objects for a particular object by calling

scott@jcave > exec deptree_fill('table', 'scott', 'emp' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

scott@jcave > select * from ideptree;

DEPENDENCIES

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

TABLE SCOTT.EMP

   VIEW SCOTT.V2

   PROCEDURE SCOTT.INCREASE_SALARY

   VIEW SCOTT.V3

   PROCEDURE SCOTT.CALL_ME

   TRIGGER SCOTT.ODBC_EMP_TST

   <no>

7 rows selected.

Elapsed: 00:00:08.03

Please chnage “like” string for filtering results for a specific SQL statement.

set linesize 150;
set pagesize 1000;
BREAK ON hash_value SKIP 1;
spool 1_sql.lst;
select hash_value,to_char(snap_time,’DD-MON-YY HH24:MI:SS’),snap_id,piece, sql_text from STATS$SQLTEXT a, stats$snapshot b where hash_value in (
select hash_value
from STATS$SQLTEXT where sql_text like ‘SELECT%VBELN%KUNNR%ZZCURRENT_OWNER%VBKA%’)
and b.snap_id=a.last_snap_id
order by snap_time,hash_value,piece;
spool off;

Long running operations such as imports and batch processes sometimes fail because the server is unable to allocate more extents for an object. This may be because the object has reached max_extents or there isn’t sufficient room in the tablespace for the object to expand. In previous releases the operation would have to be rerun, possible with some manual cleanup necessary. In Oracle9i operations that would fail due to space allocation problems can be suspended and restarted once the problem is fixed.
* Resumable Mode
* Timeout Period
* AFTER SUSPEND Trigger
* Views
* DBMS_RESUMABLE Package
* Restrictions

Resumable Mode
Operations can be made resumable by explicitly switching the session mode using:
ALTER SESSION ENABLE RESUMABLE;
ALTER SESSION DISABLE RESUMABLE;

When the session is in resumable mode, any operations that result in the following errors will be suspended:
* Out of space condition.
* Maximum extents reached condition.
* Space quota exceeded condition.
Once the error is corrected the operations will automatically resume.
Timeout Period

Operations will remain in a suspended state until the timeout period, 2 hours by default, is reached. The timeout period can be modified using any of the following commands:
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;
EXECUTE Dbms_Resumable.Set_Timeout(3600);
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME ‘insert into table’;

The final example can be used to assign a name to the suspended session.
AFTER SUSPEND Trigger
Since suspended operations do not produce error messages, an alternative method is required to notify users/DBAs so that the problem can be corrected. Typically, these procedures are initiated using the AFTER SUSPEND trigger which always fires as an autonomous transaction. This trigger can be used to insert rows into an error table or email an operator using the DBMS_SMTP package:

CREATE OR REPLACE TRIGGER resumable_default
AFTER SUSPEND
ON DATABASE
DECLARE
-- Declare any variables 
BEGIN
-- Alter default timeout period. 
Dbms_Resumable.Set_Timeout(3600);
-- Perform resumable space allocation 
-- notification code here. 
COMMIT;
END;
/

Views
Information about suspended sessions can be viewed via the USER_ and DBA_RESUMABLE views. When a session is suspended a row is added to the V$SESSION_WAIT view with the EVENT column containing “suspended on space error”.
DBMS_RESUMABLE Package
* ABORT(sessionID) – Ends the specified suspended session. Caller must be the owner of the session with sessionID, have ALTER SYSTEM privilege, or have DBA privileges.
* GET_SESSION_TIMEOUT(sessionID) – Returns the timeout period in seconds for the specified session, or -1 if the session does not exist.
* SET_SESSION_TIMEOUT(sessionID, timeout) – Sets the timeout in seconds of the specified session with immediate effect.
* GET_TIMEOUT() – Returns the timeout period in seconds for the current session.
* SET_TIMEOUT(timeout) – Sets the timeout in seconds of the current session with immediate effect.
* DBMS_RESUMABLE.SPACE_ERROR_INFO(…) – Returns information about the space error when called from within an AFTER SUSPEND trigger.
Restrictions
* When using dictionary managed tablespaces, DDL CREATE operations with explicit MAXEXTENTS which run out of extents are aborted because the MAXEXTENTS parameter cannot be modified until the object is created. Extension of existing objects is resumable.
* If rollback segments are placed in dictionary managed tablespaces any RBS errors will not be resumable. Oracle recommends that automatic undo management is configured or rollback segments are placed in a locally managed tablespace.
* Remote operations are not supported in resumable mode.
* Individual parallel server processes may be suspended while others proceed without any problem. When the error is corrected any suspended operations will resume normally. If aborted, the whole transaction is aborted.

exec dbms_java.grant_permission ('DBA_ADMIN', 'java.io.FilePermission','/usr/bin/ps', 'execute');
exec dbms_java.grant_permission ('DBA_ADMIN','java.lang.RuntimePermission','*','writeFileDescriptor' );

Create or replace and compile
java source named “Util”
as
import java.io.*;
import java.lang.*;
public class Util extends Object
{
public static int RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
int        rc = -1;
try
{
Process p = rt.exec(args);
int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];
// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);
rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/
Java created.
create or replace
function RUN_CMD(p_cmd in varchar2) return number
as
language java
name ‘Util.RunThis(java.lang.String) return integer’;
/
create or replace procedure RC(p_cmd in varchar2)
as
x number;
begin
x := run_cmd(p_cmd);
end;
/
variable x number;
set serveroutput on
exec dbms_java.set_output(100000);
exec 😡 := RUN_CMD(‘/usr/bin/ls /tmp’);

SQL> exec :x := RUN_CMD('/usr/bin/ls /tmp');
10439.err
10757.err
11319.err
13399.err
13654.err
1429.err
14792.err
20648.err
20994.err
2139.err
21690.err

Oracle trace events are useful for debugging the Oracle database server.

The following two examples are simply to demonstrate syntax. Refer to later notes on this page for an explanation of what these particular events do.

Events can be activated by either adding them to the INIT.ORA parameter file. E.g.:
event=’1401 trace name errorstack, level 12′ … or, by issuing an ALTER SESSION SET EVENTS command: E.g.
alter session set events ‘10046 trace name context forever, level 4’;

Frequently Used Events:

Where to find Event Numbers ?

Event numbers can be found in $ORACLE_HOME/rdbms/mesg/oraus.msg

cd $ORACLE_HOME/rdbms/mesg
vi oraus.msg
/10015

10015, 00000, “Undo Segment Recovery”
// *Cause:
// *Action:

2 Oracle databases – A & B. Server A is available and need to be replicated on server B. We will use rman to backup datafiles including control file and archive logs.

RMAN script

connect rcvcat rmanc/obsequ10us@rcvcat
connect target rmant/obstreper0us@tknwp.yell
run {
 allocate channel tp1 type "sbt_tape";
 allocate channel tp2 type "sbt_tape";
 allocate channel tp3 type "sbt_tape";
 backup current controlfile for standby;
 backup incremental level = 0 database plus archivelog;
 change archivelog until time 'sysdate - 3' delete;
 release channel tp1;
 release channel tp2;
 release channel tp3;
}

Spool of rman script
C:\Documents and Settings\oracle>set oracle_sid=TKNWP
C:\Documents and Settings\oracle>cd S:\nsr
C:\Documents and Settings\oracle>s:
S:\nsr>rman
Recovery Manager: Release 9.2.0.8.0 – Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
RMAN> @tknwp_full.rman
RMAN> connect rcvcat rmanc/obsequ10us@rcvcat
connected to recovery catalog database
RMAN> connect target rmant/obstreper0us@tknwp.yell
connected to target database: TKNWP (DBID=38387564)
RMAN> run {
2> allocate channel tp1 type “sbt_tape”;
3> allocate channel tp2 type “sbt_tape”;
4> allocate channel tp3 type “sbt_tape”;
5> send ‘NSR_ENV=(NSR_SERVER=bsbk10,NSR_DATA_VOLUME_POOL=Default)’;
6> backup current controlfile for standby;
7> backup incremental level = 0 database plus archivelog;
8> change archivelog until time ‘sysdate – 3’ delete;
9> release channel tp1;
10> release channel tp2;
11> release channel tp3;
12> }
allocated channel: tp1
channel tp1: sid=17 devtype=SBT_TAPE
channel tp1: NMO v4.1.0.0
allocated channel: tp2
channel tp2: sid=18 devtype=SBT_TAPE
channel tp2: NMO v4.1.0.0
allocated channel: tp3
channel tp3: sid=22 devtype=SBT_TAPE
channel tp3: NMO v4.1.0.0
sent command to channel: tp1
sent command to channel: tp2
sent command to channel: tp3
Starting backup at 28-FEB-2007:11:55:28
channel tp1: starting full datafile backupset
channel tp1: specifying datafile(s) in backupset
including standby controlfile in backupset
channel tp1: starting piece 1 at 28-FEB-2007:11:55:29
channel tp1: finished piece 1 at 28-FEB-2007:11:55:36
piece handle=01ib6i5g_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp1: backup set complete, elapsed time: 00:00:08
Finished backup at 28-FEB-2007:11:55:36
Starting backup at 28-FEB-2007:11:55:37
current log archived
channel tp1: starting archive log backupset
channel tp1: specifying archive log(s) in backup set
input archive log thread=1 sequence=14 recid=1 stamp=614526721
input archive log thread=1 sequence=15 recid=2 stamp=614526722
input archive log thread=1 sequence=16 recid=3 stamp=614528129
input archive log thread=1 sequence=17 recid=4 stamp=614536625
input archive log thread=1 sequence=18 recid=6 stamp=614536632
input archive log thread=1 sequence=19 recid=8 stamp=614833726
input archive log thread=1 sequence=20 recid=9 stamp=615033366
input archive log thread=1 sequence=21 recid=10 stamp=615042001
input archive log thread=1 sequence=22 recid=11 stamp=615117605
channel tp1: starting piece 1 at 28-FEB-2007:11:55:40
channel tp2: starting archive log backupset
channel tp2: specifying archive log(s) in backup set
input archive log thread=1 sequence=23 recid=12 stamp=615189602
input archive log thread=1 sequence=24 recid=13 stamp=615265205
input archive log thread=1 sequence=25 recid=14 stamp=615337204
input archive log thread=1 sequence=26 recid=15 stamp=615404575
channel tp2: starting piece 1 at 28-FEB-2007:11:55:40
channel tp3: starting archive log backupset
channel tp3: specifying archive log(s) in backup set
input archive log thread=1 sequence=27 recid=16 stamp=615404584
input archive log thread=1 sequence=28 recid=26 stamp=615477606
input archive log thread=1 sequence=29 recid=28 stamp=615553206
input archive log thread=1 sequence=30 recid=29 stamp=615621602
input archive log thread=1 sequence=31 recid=30 stamp=615639537
input archive log thread=1 sequence=32 recid=31 stamp=615639544
input archive log thread=1 sequence=33 recid=32 stamp=615639606
input archive log thread=1 sequence=34 recid=33 stamp=615640180
input archive log thread=1 sequence=35 recid=34 stamp=615640586
channel tp3: starting piece 1 at 28-FEB-2007:11:55:40
channel tp1: finished piece 1 at 28-FEB-2007:11:55:55
piece handle=02ib6i5r_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp1: backup set complete, elapsed time: 00:00:16
channel tp2: finished piece 1 at 28-FEB-2007:11:55:55
piece handle=03ib6i5r_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp2: backup set complete, elapsed time: 00:00:16
channel tp3: finished piece 1 at 28-FEB-2007:11:55:55
piece handle=04ib6i5r_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp3: backup set complete, elapsed time: 00:00:16
channel tp1: starting archive log backupset
channel tp1: specifying archive log(s) in backup set
input archive log thread=1 sequence=36 recid=36 stamp=615640591
input archive log thread=1 sequence=37 recid=44 stamp=615640774
input archive log thread=1 sequence=38 recid=46 stamp=615658800
input archive log thread=1 sequence=39 recid=47 stamp=615658801
input archive log thread=1 sequence=40 recid=48 stamp=615729337
channel tp1: starting piece 1 at 28-FEB-2007:11:55:55
channel tp1: finished piece 1 at 28-FEB-2007:11:56:02
piece handle=05ib6i6b_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp1: backup set complete, elapsed time: 00:00:07
Finished backup at 28-FEB-2007:11:56:02
Starting backup at 28-FEB-2007:11:56:03
channel tp1: starting incremental level 0 datafile backupset
channel tp1: specifying datafile(s) in backupset
input datafile fno=00007 name=G:\ORACLE\ORADATA\TKNWP\PERFSTAT_01.DBF
input datafile fno=00005 name=H:\ORACLE\ORADATA\TKNWP\TOOLS_01.DBF
input datafile fno=00006 name=H:\ORACLE\ORADATA\TKNWP\USERS_01.DBF
channel tp1: starting piece 1 at 28-FEB-2007:11:56:03
channel tp2: starting incremental level 0 datafile backupset
channel tp2: specifying datafile(s) in backupset
including current controlfile in backupset
input datafile fno=00001 name=G:\ORACLE\ORADATA\TKNWP\SYSTEM01.DBF
input datafile fno=00004 name=G:\ORACLE\ORADATA\TKNWP\GENESYS_LOGS_DATA_01.DBF
channel tp2: starting piece 1 at 28-FEB-2007:11:56:03
channel tp3: starting incremental level 0 datafile backupset
channel tp3: specifying datafile(s) in backupset
input datafile fno=00002 name=G:\ORACLE\ORADATA\TKNWP\UNDOTBS01.DBF
input datafile fno=00003 name=I:\ORACLE\ORADATA\TKNWP\GENESYS_CONFIG_DATA_01.DBF
channel tp3: starting piece 1 at 28-FEB-2007:11:56:04
channel tp1: finished piece 1 at 28-FEB-2007:11:56:29
piece handle=06ib6i6j_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp1: backup set complete, elapsed time: 00:00:26
channel tp2: finished piece 1 at 28-FEB-2007:11:56:54
piece handle=07ib6i6j_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp2: backup set complete, elapsed time: 00:00:51
channel tp3: finished piece 1 at 28-FEB-2007:11:56:54
piece handle=08ib6i6j_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp3: backup set complete, elapsed time: 00:00:51
Finished backup at 28-FEB-2007:11:56:54
Starting backup at 28-FEB-2007:11:56:55
current log archived
channel tp1: starting archive log backupset
channel tp1: specifying archive log(s) in backup set
input archive log thread=1 sequence=41 recid=49 stamp=615729415
channel tp1: starting piece 1 at 28-FEB-2007:11:56:57
channel tp1: finished piece 1 at 28-FEB-2007:11:57:04
piece handle=09ib6i88_1_1 comment=API Version 2.0,MMS Version 4.1.0.0
channel tp1: backup set complete, elapsed time: 00:00:08
Finished backup at 28-FEB-2007:11:57:04
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00014.ARC recid=1 stamp=61452
6721
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00015.ARC recid=2 stamp=61452
6722
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00016.ARC recid=3 stamp=61452
8129
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00017.ARC recid=4 stamp=61453
6625
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00018.ARC recid=6 stamp=61453
6632
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00019.ARC recid=8 stamp=61483
3726
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00020.ARC recid=9 stamp=61503
3366
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00021.ARC recid=10 stamp=6150
42001
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00022.ARC recid=11 stamp=6151
17605
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00023.ARC recid=12 stamp=6151
89602
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00024.ARC recid=13 stamp=6152
65205
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00025.ARC recid=14 stamp=6153
37204
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00026.ARC recid=15 stamp=6154
04575
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00027.ARC recid=16 stamp=6154
04584
deleted archive log
archive log filename=F:\ORACLE\ORADATA\TKNWP\TKNWP_00028.ARC recid=26 stamp=6154
77606
Deleted 15 objects
released channel: tp1
released channel: tp2
released channel: tp3
RMAN>
RMAN>
RMAN>
RMAN>
RMAN> **end-of-file**