nmefwmi.exe issue in Oracle 11g on Vista

A window keeps pop-up saying "nmefwmi.exe has stopped working".

It is a known bug in Oracle, should be fixed in 11.1.0.7 release according to metalink.

Suggested Workaround prior to 11.1.0.7:

Check Compatibility in properties of setup.exe to "Run this program as a administrator".

Oracle bids $6.66B for BEA Systems

 

Oracle is opening its mouth again.

 

Check this Oracle bids $6.66B for BEA Systems  in Star web site.

 

Who is going to be the next?

EXP-00056: ORACLE error 904 encountered

Oracle Version: 9.2.0.8.0

Error Messsage when tried to export a schema:

EXP-00056: ORACLE error 904 encountered
ORA-00904: "SYS"."DBMS_EXPORT_EXTENSION"."FUNC_INDEX_DEFAULT": invalid identifier

Explanation from Metalink Note:358508.1:

After the patchset installation, the data dictionary components have not been upgraded to the patchset release (e.g. 10.1.0.4).

A patchset (e.g. 10.1.0.4) was applied to an existing ORACLE_HOME install but one or more of thepatchset steps were not performed per the patchset readme to bring the database components up to the same version as the patched software as shown in DBA_REGISTRY.

If the database components versions do not match the software and the running instance then the exp utility fails as it finds an earlier version of the exp packages in the database.

Check:
From the following I can see three components are still have the old version.
select COMP_ID,COMP_NAME,VERSION,STATUS,SCHEMA,PROCEDURE from DBA_REGISTRY
COMP_ID COMP_NAME VERSION STATUS SCHEMA PROCEDURE
-------- ------------------------------ ---------- ----------- -------- -----------------------------------
CATALOG Oracle9i Catalog Views 9.2.0.6.0 VALID SYS DBMS_REGISTRY_SYS.VALIDATE_CATALOG

CATPROC Oracle9i Packages and Types 9.2.0.6.0 VALID SYS DBMS_REGISTRY_SYS.VALIDATE_CATPROC

OWM Oracle Workspace Manager 9.2.0.1.0 VALID WMSYS OWM_VALIDATE

3 rows selected.

Suggested Solution:
Connect with SYS user with SYSDBA privileges
SQL> STARTUP MIGRATE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF
SQL> SHUTDOWN
SQL> STARTUP

10g Recycle Bin

Oracle 10g Recycle Bin is a virtual container to hold dropped objects until owner or DBA purges them out or when no more free space left in its tablespace which needs to save more data or a new object.

Default recyclebin is ON. After a table or index is dropped, you will see a new table or index with a long name like BIN$% under current schema. As some systems created and dropped tables very often "by design", in 10g this may bother some users to see many BIN$% tables which are not able to be dropped by using "drop table" command".

The following are something need to know about it:

1. Check recycle bin

SQL> show recyclebin;
SQL> select * from recyclebin;
SQL> select * from user_recyclebin;
SQL> select * from dba_recyclebin; --with DBA privilege

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
SEQ_TELE BIN$NEZqOOkqZdrgRAADung28g==$0 TABLE 2007-07-02:06:37:26
TORCMPLIST BIN$NH0oo3EvagvgRAADung28g==$0 TABLE 2007-07-04:23:56:05
TORCMPLIST BIN$NGnVEBsMIJ/gRAADung28g==$0 TABLE 2007-07-04:00:52:42
TORCMPLIST BIN$NFlS+mOZHurgRAADung28g==$0 TABLE 2007-07-03:05:11:00

you can see multiple versions of dropped table. Cool!

2. Check the content in a dropped table.

Dropped objects still occupy the space in the database until manually purged or automatically removed by the database itself.

SQL> select * from "BIN$NFlS+mOZHurgRAADung28g==$0"

3. Flashback dropped table from recycle bin

This gives you a chance to bring back the dropped table.

SQL> FLASHBACK TABLE SEQ_TELE TO BEFORE DROP RENAME TO SEQ_TELE_old;

--Flashback dropped table when it has mltiple versions
SQL> FLASHBACK TABLE "BIN$NFlS+mOZHurgRAADung28g==$0" TO BEFORE DROP RENAME TO TORCMPLIST_20070703051100;

4. Purge recycle bin

In order to avoid losing data by accident, by checking drop time of dropped objects, they can permanently and safely be removed from the database.

SQL> PURGE TABLE tablename;                   -- Remove specific table from recycle bin.
SQL> PURGE INDEX indexname;                   -- Remove specific index from recycle bin.
SQL> PURGE TABLESPACE ts_name;                -- Empty recycle bin for a specific tablespace under current user.
SQL> PURGE TABLESPACE ts_name USER username;  -- Empty recycle bin for a specific user under a specific tablespace.
SQL> PURGE RECYCLEBIN;                        -- Empty recycle bin for current user.
SQL> PURGE DBA_RECYCLEBIN;                    -- Empty the entire recycle bin in the database by DBA

5. Disable recycle bin feature

It is NOT recommneded as you lose the chance to easily restore the accidentally dropped table back. Think about how difficultly to do this prior to 10g.

SQL> alter seesion set recyclebin=off;
SQL> alter system set recyclebin=off;

Oracle Application Server Control 10g page not found

After installed Oracle application server 10g 10.1.3.1.0, ascontrol and applications were working properly. However after sysadmin rebooted the server, ascontrol got 404 error "The page cannot be found" .http://yourhost:7777/em/ Application got the error:

Fatal error
Webapplication did not initialize properly during startup - please try again later

--
--Fix:
--
1. in the file default-web-site.xml under OracleAS home ~/j2ee/home/config/
ohs-routing default is false, change it to true.

2. in the file server.xml under OracleAS home ~/j2ee/home/config/
change start attribute for application ascontrol to true. Default is false.

3. restart the servers

$ opmnctl status
$ opmnctl stopall
$ opmnctl startall
$ opmnctl status

TNS-12518: TNS:listener could not hand off client connection

OS: Red Hat Linux 2.6.9-34
Oracle 10gR2: 10.2.0.1.0

Error Message in listener.log:
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe

Troubleshooting Steps:
1. check current listener connections and services
-bash-3.00$ lsnrctl ser

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 03-APR-2007 13:20:45
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
Services Summary...
Service "mydb" has 1 instance(s).
Instance "mydb", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:593 refused:23
LOCAL SERVER
Service "mydb" has 1 instance(s).
Instance "tedsdb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:572 refused:0 state:ready
LOCAL SERVER
The command completed successfully

2. check current listener.ora

3. take SID_LIST out of listener.ora as PMON can automatically register DB service with listener(since 8i).

3. set up local_listener in the instance

alter system set LOCAL_LISTENER="(address=(protocol=tcp)(host= mydbhost)(port=1521))" SCOPE=both;

4. restart listener.
lsnrctl stop
lsnrctl start

5. check listener service in a minute.

6. monitor listener connection usage.

7. turn on listener trace if cannot solve the problem.

Shutdown Immediate hang on 8.1.7.4.0 with ora_emn0

Yesterday one oracle database(8.1.7.4.0) was hung after I sent "shutdown immediate" command. I found a process ora_emn0 background process was started before shutting down. emn is Event Monitor Process for Advanced Queue Notification, which we don't use it on this instance. See Note 105067.1 to find What is the EMN0 process?

Why does it cause the hang of shutdown? After searching in metalink, I found one note describes this issue ( Database Shuts Down After the Message: Restarting dead background process EMN0). It is related to a known Bug 1800676 in this release.

--
--Solution
--
SQL> shutdown abort
SQL> startup
SQL> shutdown immediate
SQL> startup
SQL> alter trigger AURORA$SERVER$SHUTDOWN disable;
or
Set _system_trig_enabled=false in the init.ora and restart the database.

Oracle Diagnostic Tools

RACDDT - a data collection tool designed and configured specifically for gathering diagnostic data related to Oracle's Real Application Cluster (RAC) technology.
See MetalinkNote:301138.1

RDA - Remote Diagnostic Agent - a set of command line diagnostic scripts that are used to gather detailed information about an Oracle environment.
See Metalink Note:314422.1
RDA 4 - Health Check / Validation Engine Guide
RDA 4 - RAC Cluster Guide

OSW - OS Watcher - a collection of UNIX shell scripts intended to collect and archive operating system and network metrics to aid support in diagnosing complex RAC issues as well as generic performance issues.
SeeMetalinkNote:301137.1

LTOM - The On-Board Monitor - a java program designed as a real-time diagnostic platform for deployment to a customer site. LTOM differs from other support tools, as it is proactive rather than reactive. LTOM provides real-time automatic problem detection and data collection.
See MetalinkNote:352363.1

Support Diagnostics Tools Catalog for E-Business Suite -
a test repository, execution engine, and user interface for customers, support analysts, and developers to plug-in diagnostic test tools/scripts
See MetalinkNote:179661.1

Oracle released Berkely DB 4.5

Yesterday Oracle released Berkely DB 4.5. It is open source(originially from SleepyCat), embeddede, non-relational, unattended DBMS.
Key Features in R4.5:

• Multi-Version Concurrency Control
• Non-stop Upgrades - coooool
  
• Replication Framework

Links:
http://www.sleepycat.com/
http://www.oracle.com/database/berkeley-db.html

SQL Trace (incl. 10g new tracing features)

--
-- sql trace
--
--related parameters
timed_statistics
max_dum_file_size
user_dump_dest

--to get session to be traced
select sid,serial# from v$session where ...
--or
select s.sid,s.serial#,s.username,s.osuser from v$session s,v$process p where s.paddr=p.addr;

use dbms_support to enable and disable the trace
($ORACLE_HOME/rdbms/admin/dbmssupp.sql)

--session level
alter session set sql_trace =true;
--instance level
alter system set sql_trace =true;

--trace other session
--to start tracing
--
--dbms_support(available in Oracle 7.2 and above)
exec dbms_support.start_trace_in_session(,);
exec dbms_support.start_trace_in_session(,,binds=true);
exec dbms_support.start_trace_in_session(,,waits=true,binds=true);
--
----dbms_system(undocumented since Oracle 7.3 to 10g R2)
see a guide at http://orafaq.com/papers/dbms_sys.doc
exec dbms_system.set_sql_trace_in_session(sid,serial#,true);

--to check trace file under user_dum_dest
tkprof source output explain=user/pwd sys=no

--to stop tracing
--dbms_support
exec dbms_support.stop_trace_in_session(,);
--dbms_system
exec dbms_system.set_sql_trace_in_session(sid,serial#,false);

--Introduced in Oracle 10.1
DBMS_MONITOR
trcsess
DBA_ENABLED_TRACES
see http://julian.dyke.users.btopenworld.com/com/Presentations/OracleDiagnostics.ppt

Further Notes:
http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_perf.html
http://www.petefinnigan.com/ramblings/how_to_set_trace.htm
http://www.nyoug.org/Presentations/SIG/DBA/hotsos_perf.PDF

Oracle Background Processes, incl. 10gR2

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

--New in 10gR2

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

PSP0 (new in 10gR2) - Process SPawner - to create and manage other Oracle processes.
NOTE: There is no documentation currently in the Oracle Documentation set on this process.

LNS1(new in 10gR2) - a network server process used in a Data Guard (primary) database.

Further explaination From "What's New in Oracle Data Guard?" in the Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)

"During asynchronous redo transmission, the network server (LNSn) process transmits redo data out of the online redo log
files on the primary database and no longer interacts directly with the log writer process. This change in behavior allows the log writer (LGWR) process to write redo data to the current online redo log file and continue processing the next request without waiting for inter-process communication or network I/O to complete."

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

--New in 10gR1

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

MMAN - Memory MANager - it serves as SGA Memory Broker and coordinates the sizing of the memory components, which keeps track of the sizes of the components and pending resize operations. Used by Automatic Shared Memory Management feature.

RVWR -Recovery Writer - which is responsible for writing flashback logs which stores pre-image(s) of data blocks. It is used by Flashback database feature in 10g, which provides a way to quickly revert an entire Oracle database to the state it was in at a past point in time.
- This is different from traditional point in time recovery.
- One can use Flashback Database to back out changes that:
- Have resulted in logical data corruptions.
- Are a result of user error.
- This feature is not applicable for recovering the database in case of media
failure.
- The time required for flashbacking a database to a specific time in past is
DIRECTLY PROPORTIONAL to the number of changes made and not on the size
of the database.


Jnnn - Job queue processes which are spawned as needed by CJQ0 to complete scheduled jobs. This is not a new process.


CTWR - Change Tracking Writer (CTWR) which works with the new block changed tracking features in 10g for fast RMAN incremental backups.

MMNL - Memory Monitor Light process - which works with the Automatic Workload Repository new features (AWR) to write out full statistics buffers to disk as needed.

MMON - Memory MONitor (MMON) process - is associated with the Automatic Workload Repository new features used for automatic problem detection and self-tuning. MMON writes out the required statistics for AWR on a scheduled basis.

M000 - MMON background slave (m000) processes.

CJQn - Job Queue monitoring process - which is initiated with the job_queue_processes parameter. This is not new.

RBAL - It is the ASM related process that performs rebalancing of disk resources controlled by ASM.

ARBx - These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.

ASMB - is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.

Changes about Queue Monitor Processes

The QMON processes are optional background processes for Oracle Streams Advanced Queueing (AQ) which monitor and maintain all the system and user owned AQ objects. These optional processes, like the job_queue processes, does not cause the instance to fail on process failure. They provide the mechanism for message expiration, retry, and delay, maintain queue statistics, remove processed messages from the queue table and maintain the dequeue IOT.

QMNx - Pre-10g QMON Architecture

The number of queue monitor processes is controlled via the dynamic initialisation parameter AQ_TM_PROCESSES. If this parameter is set to a non-zero value X, Oracle creates that number of QMNX processes starting from ora_qmn0_ (where is the identifier of the database) up to ora_qmnX_ ; if the parameter is not specified or is set to 0, then QMON processes are not created. There can be a maximum of 10 QMON processes running on a single instance. For example the parameter can be set in the init.ora as follows

aq_tm_processes=1 or set dynamically via alter system set aq_tm_processes=1;

QMNC & Qnnn - 10g QMON Architecture

Beginning with release 10.1, the architecture of the QMON processes has been changed to an automatically controlled coordinator slave architecture. The Queue Monitor Coordinator, ora_qmnc_, dynamically spawns slaves named, ora_qXXX_, depending on the system load up to a maximum of 10 in total.

For version 10.01.XX.XX onwards it is no longer necessary to set AQ_TM_PROCESSES when Oracle Streams AQ or Streams is used. However, if you do specify a value, then that value is taken into account. However, the number of qXXX processes can be different from what was specified by AQ_TM_PROCESSES. If AQ_TM_PROCESSES is not specified in versions 10.1 and above, QMNC only runs when you have AQ objects in your database.

Oracle Background processes By Don Burleson

Comparison of Background Processes between 8i OPS and 9i RAC

20 Differences Between Oracle on NT and Oracle on Unix

ORA-1000 MAXIMUM NUMBER OF CURSORS EXCEEDED after Java application migrated

ORA-1000 MAXIMUM NUMBER OF CURSORS EXCEEDED
in Java application orcurred after application migrated for iPlanet to JBoss infrastructure.

It is a very very common error message. I remember I was asked this question in a job interview a couple of years ago. But I cannot remember what I explained. Anyway I got the offer. Ok, I'd like to give it a summary, even I still have a question left.

--
--Troubleshooting Steps:
--
1. Login database as DBA to check current setting of open_cursors
> show parameter open_cursors

2. Check who open too many cursors

set line 140
col username format a16
col machine format a26
SELECT vs.username, vs.sid,vs.serial#, vs.machine,Count(*) "Open_Cursors"
FROM v$open_cursor voc, v$session vs
WHERE voc.sid= vs.sid
GROUP BY vs.username, vs.sid,vs.serial# ,vs.machine
ORDER BY 5;

3. Get the entire queries

SELECT vst.HASH_VALUE, vst.SQL_TEXT, vst.PIECE, Count(vst.SQL_TEXT)
FROM v$open_cursor voc, v$sqltext vst, v$session vs
WHERE voc.sid= vs.sid
AND vs.username='CMGWAOE'
and voc.HASH_VALUE=vst.HASH_VALUE
and voc.ADDRESS=vst.ADDRESS
GROUP BY vst.HASH_VALUE,vst.SQL_TEXT ,vst.PIECE
/

4. Narrow down where these queries are called or from.

5.Review Java code with developer.

Make sure to close "ResultSet" objects before closing "PreparedStatement"/"CallableStement" objects.

if forgot to close the "ResultSet", some java application server(JBoss) may not have ability to close the (Oracle) cursors when close statement.(iPanet does)
If connection pooling is using, this issue may get even worse.

6. After enhance java code, problem went away.

But further question is, why child cursors still are open in shared-pool after parent cursor closed???

sync text index may not work in oracle9i(90130) sometimes

--
--Issue
--
CTX_DDL.SYNC_INDEX doesn't work on local partition of text index after new data is loaded.

CTXSYS.CTX_DDL.SYNC_INDEX('mytext_idx', '5M', 'partition2006');
(introduced in 816)
--
--diagnose
--
1.to check data in the base table

2.to check if data in text index
select text_clob from mytext_table where contains(text_clob,'test',1)>0;

3.to try to re-sync the partition index.
NOT WORK

--
--workaround
--
to rebuild local partition index
alter index mytext_idx rebuild partition partition2006;
--
--Advanced Diagnosis
--
1. to check all index partitions.
select IXP_TABLE_NAME,IXP_INDEX_NAME, IXP_INDEX_NAME,IXP_STATUS
from CTX_USER_INDEX_PARTITIONS;

2. to check which indexes are waiting to be indexed.
re-sync may remove records from this view.
select PND_INDEX_NAME,PND_PARTITION_NAME,COUNT(*) from ctx_user_pending group by PND_INDEX_NAME,PND_PARTITION_NAME;

3.to check the index error.
select * from CTX_USER_INDEX_ERRORS order by ERR_TIMESTAMP


--
--Reseach
--
Local Partitioned Index Maintenance in Technical Overview: Oracle Text version 9.0.1
SYNC OPTIMIZE

sync/optimize won't support in 10g.
alter index INDEXNAME rebuild parameters('sync') partition PARTITIONNAME;
alter index INDEXNAME rebuild parameters('optimize fast/full') partition PARTITIONNAME;

**
**It's not a good idea to use a tentative release in production.
**

oracle10gR2 supports SQL function LNNVL

actually it was undocumented since 7.2 or 7.3 according to the notes of Jonathan Lewis.

Oracle security guru Pete Finnigan's web site

http://www.petefinnigan.com/

Oracle Transaction Isolation

ORA-8117 error - cann't serialize access this transaction.

Oracle has three type of isolation levels:
read committed: it is default, I think it won't need to explain. Also this is how database to keep statement-level read consistency.
serializable: query result is repeatable during the transaction, it won't see the committed change by other transaction. if you try to update the data during the serializable transaction, which was changed by another concurrent user, this ORA-8117 error will happen. It's how database to keep transaction-level read consistency.
read-only: just read-only, no insert/update/delete is allowed.

Commands:
set transaction isolation level serializable;
set transaction isolation level read committed;
set transaction isolation level read only;

Also you can do it in session level, ex:
alter session set isolation level read only;

Oracle 10g DB and MS SQL Server 2005 Comparison by Edison Group

Edison Group just released a comparsing report: Comparative Management Cost Study of Oracle Database 10g Release 2 and Microsoft SQL Server 2005 (PDF).

It provides detailed execises performing on these two databases, it's the greatest report so far I have seen.Worthwhile to read it.

Export technology is changed in 10g

Export Dump 'expdp' is 2-time faster than previous 'exp', and support external tables.
Dump file and logs are located on server side under defined directory in the database.

PL/SQL API: DBMS_DATADUMP

--
--1. Create a directory
--
$ mkdir /test

CONNECT / AS SYSDBA
-- Create directory
create or replace directory TEST_DIR as '/test';

GRANT READ,WRITE ON DIRECTORY TEST_DIR TO scott;

--
--Drop Directory
--
DROP DIRECTORY TEST_DIR;

--
--2. Related Views
--
dba_directories, all_directories

--check privilieges
--
all_tab_privs, user_*,dba_*
all_tab_privs_made, user_tab_privs_made
all_tab_privs_recd, user_tab_privs_recd

--
--3.Estimate export dump before it gets underway
--
expdp mydbauser/mydbapwd ESTIMATE_ONLY=Y SCHEMA=myschema DIRECTORY=TEST_DIR

expdp mydbauser/mydbapwd ESTIMATE_ONLY=Y FULL=Y DIRECTORY=TEST_DIR

--
--Dump
--
expdp mydbauser/mydbapwd DIRECTORY=TEST_DIR SCHEMA=myschema DUMPFILE=schema_dmp LOGFILE=schema_log

expdp mydbauser/mydbapwd DIRECTORY=TEST_DIR FULL=Y DUMPFILE=fulldb_dmp LOGFILE=fulldb_log

--
--4.Monitor Export Dump job
--

select * from dba_datapump_jobs;

Difference between RAMN backup and traditional hot backup

In traditional hot backup, first needs to switch tablespace to backup mode.
Alter Tablespace users Begin Backup;

It'll freeze SCN in the file header of corresponding data files, while SCN of data blocks continute increased to reflect the changes. When recover the database, these two SCN will be compared to see if data block needs to be recovered from archived log files.

RMAN writes frozen SCN of data file header into either control file (nocatalog) or recovery catalog instead to "Alter Tablespace users Begin Backup;".

RMAN Maintenance

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 14;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/dbBackup/hotbackup/controlfile%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/dbhome/product/10.2.0/dbs/snapcf_tedsdb.f'; # default

--
--List
--
list backup;
list backup summary;
list backup by file;
list expired backup;
list copy of database archivelog all;

--
--Report
--
report obsolete;
report need backup;

--
--Delete
--
delete obsolete;
delete force obsolete;

--
--Crosscheck
--
crosscheck backup;
delete expired backup;

--
--Change
--

CHANGE BACKUPSET TAG year_end_2005 KEEP FOREVER NOLOGS;

CHANGE BACKUPSET TAG year_end_2005 NOKEEP;

--
--Monitor RMAN via v$ views
--
COLUMN CLIENT_INFO FORMAT a30
COLUMN SID FORMAT 999
COLUMN SPID FORMAT 9999
SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR
AND CLIENT_INFO LIKE 'rman%'
/
--Monitor Job Progress
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
/
--Monitor sbt event
SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT,
sw.STATE, CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE '%sbt%'
AND s.SID=sw.SID
AND s.PADDR=p.ADDR
/

--Monitor backup and restore performance
V$BACKUP_SYNC_IO and V$BACKUP_ASYNC_IO

--to validate all datafiles
run {
allocate channel d1 type disk;
backup check logical validate database;
release channel d1;
}

--To test the most recent RMAN database backup:
RMAN> restore validate database;

--To test the most recent RMAN spfile backup:
RMAN> restore validate spfile to 'c:\temp\spfile.ora';

--To test the most recent RMAN controlfile backup:
RMAN> restore validate controlfile to 'c:\temp\control01.ctl';

--To test the most recent RMAN archivelog file(s) backup.
--First determine which archivelog sequences are in the last backup:
RMAN> list backup of archivelog all;
or
RMAN> list backup of archivelog all completed after 'sysdate -1';
--then:
--the beginning number is inclusive and the ending sequence number is not.
RMAN> restore validate archivelog from sequence ii until sequence XX?;

--To test for earlier backups via TAG
RMAN> restore validate database from tag=;

--RMAN configuration
select * from v$rman_configuration;

--
--Make Cold/Consistent Backup
--
run {
shutdown immedate;
startup mount pfile=/yourpath/initxxx.ora;
backup incremental level 0
database format '/yourpath/%d_closed_%U'
tag=PROD_COLD_L0;
shutdown;
startup pfile=/yourpath/initxxx.ora;
}


--RMAN related V$ views
select object_name from dba_objects
where object_name like 'V$BACKUP%'
and object_type = 'SYNONYM';

select object_name from dba_objects
where object_name like 'V$RMAN%'
and object_type = 'SYNONYM';

select TYPE, RECORDS_TOTAL, RECORDS_USED
from v$controlfile_record_section
where type like '%BACKUP%';

* V$CONTROLFILE_RECORD_SECTION
* V$COPY_CORRUPTION
* V$DATABASE
* V$DATABASE_BLOCK_CORRUPTION
* V$DATABASE_INCARNATION
* V$DATAFILE
* V$DATAFILE_COPY
* V$PROXY_ARCHIVEDLOG
* V$PROXY_ARCHIVELOG_DETAILS
* V$PROXY_ARCHIVELOG_SUMMARY
* V$PROXY_COPY_DETAILS
* V$PROXY_COPY_SUMMARY
* V$PROXY_DATAFILE
* V$OBSOLETE_BACKUP_FILES
* V$OFFLINE_RANGE
* V$SESSION_LONGOPS

* V$BACKUP
* V$BACKUP_ARCHIVELOG_DETAILS
* V$BACKUP_ARCHIVELOG_SUMMARY
* V$BACKUP_ASYNC_IO
* V$BACKUP_CONTROLFILE_DETAILS
* V$BACKUP_CONTROLFILE_SUMMARY
* V$BACKUP_COPY_DETAILS
* V$BACKUP_COPY_SUMMARY
* V$BACKUP_CORRUPTION
* V$BACKUP_DATAFILE
* V$BACKUP_DATAFILE_DETAILS
* V$BACKUP_DATAFILE_SUMMARY
* V$BACKUP_DEVICE
* V$BACKUP_FILES
* V$BACKUP_PIECE
* V$BACKUP_PIECE_DETAILS
* V$BACKUP_REDOLOG
* V$BACKUP_SET
* V$BACKUP_SET_DETAILS
* V$BACKUP_SET_SUMMARY
* V$BACKUP_SPFILE
* V$BACKUP_SPFILE_DETAILS
* V$BACKUP_SPFILE_SUMMARY
* V$BACKUP_SYNC_IO

* V$RMAN_BACKUP_JOB_DETAILS
* V$RMAN_BACKUP_SUBJOB_DETAILS
* V$RMAN_BACKUP_TYPE
* V$RMAN_CONFIGURATION
* V$RMAN_ENCRYPTION_ALGORITHMS
* V$RMAN_OUTPUT
* V$RMAN_STATUS