Data Recovery Advisor (DRA)

The DRA and the way it can repair a failure seems to me very interesting

rman target /
list failure;
advice failure;
repair failure;

I will test it!

Oracle flashback transaction query

Using Oracle Flashback Transaction Query to retrieve metadada and historical data fro given transaction or for all transactions in a given time interval. Oracle Flashback Transaction Query queries the statis dictionary view FLASHBACK_TRANSACTION_QUERY
Enable flashback transaction query
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Example:


SQL> delete from test;

5 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into test (select * from test as of timestamp sysdate -1);

5 rows created.

SQL> commit;

Commit complete.

ORA-00333: redo log read error block 17258 count 7490

After a power failure, at startup we received folowing error
ORA-00333: redo log read error block 17258 count 7490

When i tried to do
alter database clear logfile '/u02/app/oracle/oradata/redo01.log',

i received
ORA-1624 signalled during: alter database clear logfile '/u02/app/oracle/oradata/redo01.log'...

The only solutions was restore of database, from a full backup with rman.
The control file was ok

RMAN> connect target /
RMAN> startup mount;
RMAN> restore database;
RMAN> alter database open resetlogs;


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 02/27/2012 16:18:45
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

from this connect to SQL Plus

SQL> recover database until cancel;

ORA-00279

SQL> cancel

SQL> alter database open resetlogs;

if the control file was not ok.

RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> alter database open resetlogs;

View session and undo info

View info about session and undo segments:

SQL> select s.sid, s.username, s.osuser, s.logon_time, t.start_date, d.segment_name
  2  from v$session s join v$transaction t
  3  on s.saddr=t.ses_addr
  4  join dba_rollback_segs
  5  d on t.XIDUSN=d.SEGMENT_ID;

View undo info


SQL> select USN, EXTENTS, RSSIZE, XACTS from v$rollstat;

         0          6     385024          0
        11          4    2220032          0
        12          4    2220032          1
        13          5     319488          0
        14          4     253952          0
        15          5     319488          0
        16          4    2220032          0
        17          3    1171456          0
        18         17    2088960          0
        19          6     385024          0
        20          6     385024          0

11 rows selected.

USN - segment number, EXTENTS - extents number, RSSIZE - size in bytes, XACT - number of active transactions

View blocking sessions

V$LOCK details


ADDR RAW(4)

Address of lock state object

KADDR RAW(4)

Address of lock

SID NUMBER 

Identifier for session holding or acquiring the lock

TYPE VARCHAR2(2) 

Type of user or system lock

The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:

TM - DML enqueue

TX - Transaction enqueue

UL - User supplied

The locks on the system types are held for extremely short periods of time.

ID1 NUMBER

Lock identifier #1 (depends on type)

ID2 NUMBER 

Lock identifier #2 (depends on type)

LMODE NUMBER 

Lock mode in which the session holds the lock:

0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)

REQUEST NUMBER 

Lock mode in which the process requests the lock:

0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)

CTIME NUMBER

Time since current mode was granted

BLOCK   NUMBER

The lock is blocking another lock


View blocking sessions (BLOCK=1)

select

   blocking_session,
   sid,
   serial#,
   wait_class,

   seconds_in_wait
from
   v$session

where
   blocking_session is not NULL

order by
   blocking_session;

BLOCKING_SESSION        SID    SERIAL#

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

WAIT_CLASS                                                       SECONDS_IN_WAIT

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

              25         13       1357

Application                                                                  106

SYSTEM LOCK TYPES


BL
Buffer hash table instance

NA..NZ
Library cache pin instance (A..Z = namespace)

CF
Control file schema global enqueue

PF
Password File

CI
Cross-instance function invocation instance

PI, PS
Parallel operation

CU
Cursor bind

PR
Process startup

DF
Data file instance

QA..QZ
Row cache instance (A..Z = cache)

DL
Direct loader parallel index create

RT
Redo thread global enqueue

DM
Mount/startup db primary/secondary instance

SC
System commit number instance

DR
Distributed recovery process

SM
SMON

DX
Distributed transaction entry

SN
Sequence number instance

FS
File set

SQ
Sequence number enqueue

HW
Space management operations on a specific segment

SS
Sort segment

IN
Instance number

ST
Space transaction enqueue

IR
Instance recovery serialization global enqueue

SV
Sequence number value

IS
Instance state

TA
Generic enqueue

IV
Library cache invalidation instance

TS
Temporary segment enqueue (ID2=0)

JQ
Job queue

TS
New block allocation enqueue (ID2=1)

KK
Thread kick

TT
Temporary table enqueue

LA .. LP
Library cache lock instance lock (A..P = namespace)

UN
User name

MM
Mount definition global enqueue

US
Undo segment DDL

MR
Media recovery

WL
Being-written redo log instance



Meaning of ID1 and ID2 depend on the lock TYPE.

   
      TYPE  Name ID1                     ID2
     
      TX    Transaction Decimal RBS & slot Decimal WRAP number
                    (0xRRRRSSSS  RRRR = RBS number, SSSS = slot )
    A TX lock is requested in eXclusive mode if we are waiting on a ROW.
A SHARE mode request implies we are waiting some other resource held
    by the TX
   
      TM Table Locks Object id of table. Always 0.
   
      TS Temp Segment TS# Relative DBA
   
      ST Space Transaction Only ONE enqueue.
      UL   User Locks

Where RBS - rollback segment

Processes Remain In V$Process Without A Related Session

A query against the views V$SESSION and v$PROCESS may show different results. 
Many processes in the view V$PROCESS may exist without having a related session in the view V$SESSION , so that 
V$SESSION has much less entries compared to V$PROCESS . 
At the O/S level, there are numerous processes being generated with a LOCAL=NO description . 


The error ora-00020: maximum number of processes <num> exceeded 
might be seen for new connections. 


This can be from to causes:


1. An application is trying to connect to database with incorrect username /password
2. bug 5557660 which affects db until version 10.2.0.3


When using OCI connection pooling which keeps connecting / disconnecting 
it is possible to leak process groups which eventually leads to ORA-20 
and failure to allow any further logins. 

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

Create temporary table with preserve rows on commit
SQL> create global temporary table temp_test (id number) on commit preserve rows;
Insert rows and commit

SQL> insert into temp_test values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from temp_test;

         1

Try to drop temporary table

SQL> drop table temp_test;

drop table temp_test

           *

ERROR at line 1:

ORA-14452: attempt to create, alter or drop an index on temporary table already

in use

There is a lock on the temporary table

SQL> select * from v$lock

  2  where id1 in (select object_id from dba_objects

  3  where owner='DANCO'

  4  and object_name='TEMP_TEST');

000000008F004058 000000008F0040B0         25 TO      75757          1          3

         0        362          2

This is because the session is still keeping data in table.

To drop table you have first truncate data or exit from session

SQL> truncate table temp_test;

Table truncated.

SQL> drop table temp_test;

Table dropped.

Vi remove ^M

To remove ^M from end of lines in vi type

:%s/{CTRL+v}{CTRL+m}//g

View synomym object

select o1.owner,o1.object_name, o2.owner,o2.object_name
from PUBLIC_DEPENDENCY t, Dba_Objects o1, Dba_Objects o2
WHERE t.object_id=o1.object_id
AND t.referenced_object_id=o2.object_id
AND o1.object_name='SYNONYM' and o2.OWNER='SCHEMA';

ORU-10027: buffer overflow

When running PLSQL with multiple DBMS_OUTPUT.PUT_LINE you may get:

ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 161
ORA-06512: at "SYS.DBMS_OUTPUT", line 123
ORA-06512: at line 9

this is because default buffer is limited at 2000 bytes. This limit can be increased with

exec DBMS_OUTPUT.ENABLE(1000000);

Oracle database views

v$database - displays information about the database from the control file.
v$instance - displays the state of the current instance.
v$controlfile - lists the names of the control files.
v$logfile - contains information about redo log files.
v$log - displays log file information from the control file.
v$datafile - contains datafile information from the control file.
v$recover_file - display the status of files needing media recovery
v$tempfile - displays tempfile information.
v$tablespace- displays tablespace information from the control file.
v$dispatcher - displays information about the dispatcher processes.
v$shared_server - contains information on the shared server processes.
v$circuit - contains information about virtual circuits, which are user connections to the database through dispatchers and servers.
v$session - lists session information for each current session.
v$transaction - list active transactions
v$process - contains information about the currently active processes.
v$lock - lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.
v$services - displays the services in the database.
v$sga - displays summary information about the system global area (SGA).
v$pgastat - provides PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled
v$sql - lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered.
v$sqlarea - lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.
v$sqltext - view contains the text of SQL statements belonging to shared SQL cursors in the SGA.

v$parameter - displays information about the initialization parameters that are currently in effect for the session.
v$parameter2 - displays information about the initialization parameters that are currently in effect for the session, with each list parameter value appearing as a row in the view.
v$spparameter - displays information about the contents of the server parameter file. If a server parameter file was not used to start the instance, then each row of the view will contain FALSE in the ISSPECIFIED column.
v$system_parameter - displays information about the initialization parameters that are currently in effect for the instance.
v$system_parameter2 - displays information about the initialization parameters that are currently in effect for the instance, with each list parameter value appearing as a row in the view.

dba_tablespaces - describes all tablespaces in the database.
dba_data_files - describes data files
dba_segments - describes the storage allocated for all segments in the database.
dba_rollback_segs - describes rollback segments
dba_free_space - describes the free extents in all tablespaces in the database.
dba_ts_quotas - describes tablespace quotas for the current user

v$pwfile_users - lists users who have been granted SYSDBA and SYSOPER privileges as derived from the password file
dba_profiles -  displays all profiles and their limits
dba_users - Information about all users of the database
dba_role_privs - describes the roles granted to all users and roles in the database
dba_tab_privs - describes all object grants in the database
dba_sys_privs - describes system privileges granted to users and roles

dba_tables - description of all relational tables in database
dba_indexes - description of all indexes in database
dba_tab_columns - describe all columns for tables, views and clusters in database
dba_tab_cols - describe all columns for tables, views and clusters in database (including hiden columns)
index_stats - stores information from the last analyze index ... validate structure

dba_hist_snapshot - display info about snapshots in Workload Repository
dba_hist_baseline - -display info about baselines
dba_outstanding_alerts - describe outstanding alerts
dba_alert_history - describe time-limited alerts that are no longer outstanding

v$sesstat - user session statistics. To view name of statistics of STATISTIC#, query v$statname
v$statname - decoded statistics for staistics number stored in v$sesstat si v$sysstat
v$sysstat - display system statistics. To view name of statistics of STATISTIC#, query v$statname
v$sgastat - display detailed info about sga
v$sgainfo - display brief info about sga
v$sga_dynamic_components - display info about sga dynamic components
v$pgastat - display pga memory usage statistics

v$sga_target_advice - info about SGA_TARGET
v$pga_target_advice - info about PGA_TARGET
v$memory_target_advice - info about MEMORY_TARGET

dba_objects - describes all objects in db
all_objects - describe all objects in db available to user
user_objects - describe all user objects
dba_dependencies - describe all dependencies in db for packages, procesdures, functions, views, pachage bodies and triggesr
all_dependencies
user_dependencies
dba_indexes - describe all indexes in db
all_indexes
user_indexes

v$instance_recovery - monitors the mechanisms available to users to limit recovery I/O
v$recovery_file_dest - display info about disk quota and currect disk ussage in flash recovery area
v$flash_recovery_area_usage - usage info for flash recovery areas
v$diag_info - lists all important ADR (Automaic Diagnostic Repository) locations for current Oracle database instance

v$recover_file - display the status of files needing media recovery (e.g. missing datafiles)

Add and register service names with listener

When you add service names, they are automatically registered with listener by PMON. To add a service name you can alter service_names parameter or you can user DBMS_SERVICE.

-bash-3.2$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 10 15:15:32 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      oratest
SQL>
SQL>
SQL>
SQL> alter system set service_names='oratest,oratest1';

System altered.

SQL> show parameter service_names;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      oratest,oratest1
SQL>
SQL>
SQL> host
bash-3.2$ lsnrctl status

LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 10-FEB-2012 15:16:32

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date                10-FEB-2012 09:35:57
Uptime                    0 days 5 hr. 40 min. 35 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/solaris10/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solaris10)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "oratest" has 1 instance(s).
  Instance "oratest", status READY, has 1 handler(s) for this service...
Service "oratest1" has 1 instance(s).
  Instance "oratest", status READY, has 1 handler(s) for this service...
Service "oratestXDB" has 1 instance(s).
  Instance "oratest", status READY, has 1 handler(s) for this service...
The command completed successfully
bash-3.2$
bash-3.2$
bash-3.2$
bash-3.2$ exit
exit

SQL> exec dbms_service.create_service('oratest2','oratest2');

PL/SQL procedure successfully completed.

SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      oratest,oratest1
SQL> host
bash-3.2$ lsnrctl status

LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 10-FEB-2012 15:20:14

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date                10-FEB-2012 09:35:57
Uptime                    0 days 5 hr. 44 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/solaris10/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solaris10)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "oratest" has 1 instance(s).
  Instance "oratest", status READY, has 1 handler(s) for this service...
Service "oratest1" has 1 instance(s).
  Instance "oratest", status READY, has 1 handler(s) for this service...
Service "oratestXDB" has 1 instance(s).
  Instance "oratest", status READY, has 1 handler(s) for this service...
The command completed successfully
bash-3.2$ exit
exit

SQL>
SQL> exec dbms_service.start_service('oratest2');

PL/SQL procedure successfully completed.

SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      oratest1, oratest, oratest2
SQL> host
bash-3.2$ lsnrctl status

LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 10-FEB-2012 15:20:54

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date                10-FEB-2012 09:35:57
Uptime                    0 days 5 hr. 44 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/solaris10/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=solaris10)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "oratest" has 1 instance(s).
  Instance "oratest", status READY, has 1 handler(s) for this service...
Service "oratest1" has 1 instance(s).
  Instance "oratest", status READY, has 1 handler(s) for this service...
Service "oratest2" has 1 instance(s).
  Instance "oratest", status READY, has 1 handler(s) for this service...
Service "oratestXDB" has 1 instance(s).
  Instance "oratest", status READY, has 1 handler(s) for this service...
The command completed successfully
bash-3.2$

Manual registration of service names with listener, in case PMON is not available:


SQL>
SQL> alter system register;

System altered.

TWO_TASK variable

TWO_TASK variable can be set in OS environment to specify that you can connect to a remote db without specifying a service name. The variable must have a value specified in tnsnames.ora file.


>more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/ora11g/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DB11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.74.13)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB11G)
    )
  )

DB10G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.74.13)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB10G)
    )
  )


>
>
>echo $ORACLE_SID
DB11G
>
>export TWO_TASK=DB10G
>
>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 10 14:22:21 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn username
Enter password:
Connected.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
DB10G

SQL>

SQL> select name from v$database;

NAME
---------
DB10G

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
>

Oracle Database Storage Structures Considerations

Storage Structures
Three files types must be present in a database: The controlfile, the online redo log files, and any number of datafiles.


Damage to any controlfile copy will cause the database instance to terminate immediately.



Every database must have at least two online redo log file groups to function. Each group should have at least two members for safety.


Server processes read from the datafiles; DBWn writes to datafiles.


Other Database Files

■ The Instance Parameter File
■ The Password File
■ Archive Redo Log Files
■ Alert Log and Trace Files

The Logical Database Structures


SQL> select distinct segment_type from dba_segments;

SEGMENT_TYPE
------------------
LOBINDEX
INDEX PARTITION
TABLE SUBPARTITION
ROLLBACK
TABLE PARTITION
NESTED TABLE
LOB PARTITION
LOBSEGMENT
INDEX
TABLE
TYPE2 UNDO

SEGMENT_TYPE
------------------
CLUSTER

12 rows selected.


The SYSAUX tablespace must be created at database creation time in Oracle 10g and later. If you do not specify it, one will be created by default.


Which view will show you ALL the tables in the database? DBA_TABLES, not ALL_TABLES


select tablespace_name, extent_id, file_id,block_id, bytes from
dba_extents where owner='USER' and segment_name='TEST'
SQL> /

TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES
------------------------------ ---------- ---------- ---------- ----------
USERS                                   0          4        520      65536


SQL> select name from v$datafile where file#=4;

NAME
--------------------------------------------------------------------------------
+DATA/oratest/datafile/users.259.773672963

SQL> select block_size * &block_id from dba_tablespaces
  2  where tablespace_name='USERS';
Enter value for block_id: 520
old   1: select block_size * &block_id from dba_tablespaces
new   1: select block_size * 520 from dba_tablespaces

BLOCK_SIZE*520
--------------
       4259840

Oracle Database Processes Considerations

SMON, the System Monitor
SMON initially has the task of mounting and opening a database. SMON mounts a database by locating and validating the database controlfile. It then opens a database by locating and validating all the datafiles and online log files. Once the database is opened and in use, SMON is responsible for various housekeeping tasks, such as collating free space in datafiles.

PMON, the Process Monitor

PMON monitors all the server processes and detects any problems with the sessions. If a session has terminated abnormally, PMON will destroy the server process, return its PGA memory to the operating system’s free memory pool, and roll back any incomplete transaction that may have been in progress.

DBWn, the Database Writer

How many database writers do you need? The default number may well be correct. Adding more may help performance, but usually you should look at tuning memory first. As a rule, before you optimize disk I/O, ask why there is any need for disk I/O.

What will cause DBWR to write? No free buffers, too many dirty buffers, a three-second timeout, or a checkpoint.

What does DBWn do when a transaction is committed? It does absolutely nothing.



LGWR, the Log Writer
LGWR writes the contents of the log buffer to the online log files on disk. A write of the log buffer to the online redo log files is often referred to as flushing the log buffer.

Circumstances that will cause LGWR to flush the log buffer: if a session issues a COMMIT; if the log buffer is one-third full; if DBWn is about to write dirty buffers.

It is in fact possible to prevent the LGWR write-on-commit. If this is done, sessions will not have to wait for LGWR when they commit: they issue the command and then carry on working. This will improve performance but can also mean that work can be lost.

CKPT, the Checkpoint Process

When do full checkpoints occur? Only on request, or as part of an orderly database shutdown.

The current checkpoint position, also known as the RBA (the redo byte address), is the point in the redo stream at which recovery must begin in the event of an instance crash. CKPT continually updates the controlfile with the current checkpoint position.

The faster the incremental checkpoint advances, the quicker recovery will be after a failure. But performance will deteriorate due to the extra disk I/O, as DBWn has write out dirty buffers more quickly.

MMON, the Manageability Monitor

By default, MMON gathers a snapshot and launches the ADDM every hour.


MMNL, the Manageability Monitor Light
MMNL is a process that assists the MMON.


MMAN, the Memory Manager
MMAN is a process that was introduced with database release 10g. It enables the automatic management of memory allocations.



ARCn, the Archiver

LGWR writes the online log files; ARCn reads them. In normal running, no other processes touch them at all.


The progress of the ARCn processes and the state of the destination(s) to which they are writing must be monitored. If archiving fails, the database will eventually hang. This monitoring can be done through the alert system.



RECO, the Recoverer Process
A distributed transaction is a transaction that involves updates to two or more databases.

RECO, the Recoverer Process

Distributed transactions require a two-phase commit. A two-phase commit prepares each database by instructing their LGWRs to flush the log buffer to disk (the first phase), and once this is confirmed, the transaction is flagged as committed everywhere (the second phase). If anything goes wrong anywhere between the two phases, RECO takes action to cancel the commit and roll back the work in all databases.



SQL> select program from v$session order by program;

PROGRAM
------------------------------------------------
oracle@solaris10 (ARC0)
oracle@solaris10 (ARC1)
oracle@solaris10 (ARC2)
oracle@solaris10 (ARC3)
oracle@solaris10 (ASMB)
oracle@solaris10 (CJQ0)
oracle@solaris10 (CKPT)
oracle@solaris10 (DBRM)
oracle@solaris10 (DBW0)
oracle@solaris10 (DIA0)
oracle@solaris10 (DIAG)

PROGRAM
------------------------------------------------
oracle@solaris10 (GEN0)
oracle@solaris10 (LGWR)
oracle@solaris10 (MARK)
oracle@solaris10 (MMAN)
oracle@solaris10 (MMNL)
oracle@solaris10 (MMON)
oracle@solaris10 (O000)
oracle@solaris10 (PMON)
oracle@solaris10 (PSP0)
oracle@solaris10 (Q000)
oracle@solaris10 (Q001)

PROGRAM
------------------------------------------------
oracle@solaris10 (QMNC)
oracle@solaris10 (RBAL)
oracle@solaris10 (RECO)
oracle@solaris10 (SMCO)
oracle@solaris10 (SMON)
oracle@solaris10 (VKTM)
oracle@solaris10 (W000)
sqlplus@solaris10 (TNS V1-V3)

30 rows selected.

SQL> select program from v$process order by program;

PROGRAM
------------------------------------------------
PSEUDO
oracle@solaris10 (ARC0)
oracle@solaris10 (ARC1)
oracle@solaris10 (ARC2)
oracle@solaris10 (ARC3)
oracle@solaris10 (ASMB)
oracle@solaris10 (CJQ0)
oracle@solaris10 (CKPT)
oracle@solaris10 (D000)
oracle@solaris10 (DBRM)
oracle@solaris10 (DBW0)

PROGRAM
------------------------------------------------
oracle@solaris10 (DIA0)
oracle@solaris10 (DIAG)
oracle@solaris10 (GEN0)
oracle@solaris10 (LGWR)
oracle@solaris10 (MARK)
oracle@solaris10 (MMAN)
oracle@solaris10 (MMNL)
oracle@solaris10 (MMON)
oracle@solaris10 (O000)
oracle@solaris10 (PMON)
oracle@solaris10 (PSP0)

PROGRAM
------------------------------------------------
oracle@solaris10 (Q000)
oracle@solaris10 (Q001)
oracle@solaris10 (QMNC)
oracle@solaris10 (RBAL)
oracle@solaris10 (RECO)
oracle@solaris10 (S000)
oracle@solaris10 (SMCO)
oracle@solaris10 (SMON)
oracle@solaris10 (TNS V1-V3)
oracle@solaris10 (VKTM)
oracle@solaris10 (W000)

33 rows selected.

SQL>

Oracle Database SGA considerations

Syetem Global Area (SGA)

Which SGA structures are required, and which are optional? The database buffer cache, log buffer, and shared pool are required; the large pool, Java pool, and Streams pool are optional.


Determining the optimal size of the database buffer cache is application specific and a matter of performance tuning. It is impossible to give anything but the vaguest guidelines without making observations, but it is probably true to say that the majority of databases will operate well with a cache sized in hundreds of megabytes up to a few gigabytes. Very few applications will perform well with a cache smaller than this, and not many will need a cache of hundreds of gigabytes.


The size of the database buffer cache can be adjusted dynamically, and can be automatically managed.


Raising the log buffer size above the default may be necessary for some applications, but as a rule start tuning with the log buffer on default.


If redo generation is the limiting factor in a database’s performance, the only option is to go to RAC. In a RAC database, each instance has its own log buffer, and its own LGWR. This is the only way to parallelize writing redo data to disk.


The size of the log buffer is static, fixed at instance startup. It cannot be automatically managed.


The Shared Pool:

■ The library cache
■ The data dictionary cache
■ The PL/SQL area
■ The SQL query and PL/SQL function result caches



The algorithm used to find SQL in the library cache is based on the ASCII values of the characters that make up the statement. The slightest difference (even something as trivial as SELECT instead of select) means that the statement will not match but will be parsed again.


The data dictionary cache is sometimes referred to as the row cache. Whichever term you prefer, it stores recently used object definitions: descriptions of tables, indexes, users, and other metadata definitions.


Shared pool tuning is usually oriented toward making sure that the library cache is the right size. This is because the algorithms Oracle uses to allocate memory in the SGA are designed to favor the dictionary cache, so if the library cache is correct, then the dictionary cache will already be correct.


PL/SQL can be issued from user processes, rather than being stored in the data dictionary. This is called anonymous PL/SQL. Anonymous PL/SQL cannot be cached and reused but must compiled dynamically. It will therefore always perform worse than stored PL/SQL. Developers should be encouraged to convert all anonymous PL/SQL into stored PL/SQL.





By default, use of the SQL query and PL/SQL function result cache is disabled, but if enabled programmatically, it can often dramatically improve performance. The cache is within the shared pool: unlike the other memory areas described previously, it does afford the DBA some control: he can specify a maximum size.


Determining the optimal size is a matter for performance tuning, but it is probably safe to say that most databases will need a shared pool of several hundred megabytes. Some applications will need one of more than a gigabyte, and very few will perform adequately with less than a hundred megabytes.


The shared pool size is dynamic and can be automatically managed.


Sizing the large pool is not a matter for performance. If a process needs large pool of memory, it will fail with an error if that memory is not available. Allocating more memory than is needed will not make statements run faster.


The large pool size is dynamic and can be automatically managed.


The Java pool is only required if your application is going to run Java-stored procedures within the database: it is used for the heap space needed to instantiate the Java objects.  Java code is not cached in the Java pool: it is cached in the shared pool, in the same way that PL/SQL code is cached.


The Java pool size is dynamic and can be automatically managed.


The Streams pool size is dynamic and can be automatically managed.


Show the current, maximum, and minimum sizes of the SGA components that can be dynamically resized:

select COMPONENT,CURRENT_SIZE,MIN_SIZE,MAX_SIZE
from v$sga_dynamic_components;


Determine how much memory has been, and is currently, allocated to program global areas:

select name,value from v$pgastat
where name in ('maximum PGA allocated','total PGA allocated');


SQL> select * from v$sga;

Fixed Size              2225576
Variable Size         247466584
Database Buffers       58720256
Redo Buffers            4747264

SQL> show sga;

Total System Global Area  313159680 bytes
Fixed Size                  2225576 bytes
Variable Size             247466584 bytes
Database Buffers           58720256 bytes
Redo Buffers                4747264 bytes
SQL>

CVS HOWTO

CVS checkout - load CVS repository into local repository

\> cvs co -help
co: invalid option -- h
Usage:
  cvs checkout [-ANPRcflnps] [-r rev] [-D date] [-d dir]
    [-j rev1] [-j rev2] [-k kopt] modules...
        -A      Reset any sticky tags/date/kopts.
        -N      Don't shorten module paths if -d specified.
        -P      Prune empty directories.
        -R      Process directories recursively.
        -c      "cat" the module database.
        -f      Force a head revision match if tag/date not found.
        -l      Local directory only, not recursive
        -n      Do not run module program (if any).
        -p      Check out files to standard output (avoids stickiness).
        -s      Like -c, but include module status.
        -r rev  Check out revision or tag. (implies -P) (is sticky)
        -D date Check out revisions as of date. (implies -P) (is sticky)
        -d dir  Check out into dir instead of module name.
        -k kopt Use RCS kopt -k option on checkout. (is sticky)
        -j rev  Merge in changes made between current revision and rev.
(Specify the --help global option for a list of other help options)

EXAMPLE

cvs co -d directory CVS_directory_path/directory

CVS status - view status of file from local repository

\> cvs status -help

status: invalid option -- h

Usage: cvs status [-vlR] [files...]

        -v      Verbose format; includes tag information for the file

        -l      Process this directory only (not recursive).

        -R      Process directories recursively.

(Specify the --help global option for a list of other help options)

EXAMPLE

\> cvs status -v config.ksh

===================================================================

File: file_name        Status: Up-to-date

   Working revision:    1.6

   Repository revision: 1.6     CVS_directory_path/file_name,v

   Commit Identifier:   3K1Ow4Mss3EvmGRv

   Sticky Tag:          (none)

   Sticky Date:         (none)

   Sticky Options:      (none)

   Existing Tags:

        TAG_34                         (revision: 1.4)

        TAG_33                         (revision: 1.4)

        TAG_30                         (revision: 1.2)

        TAG_29                         (revision: 1.2) ...

CVS diff - view differences between files from CVS repository and files from local repository

\> cvs diff --help

diff: unrecognized option `--help'

Usage: cvs diff [-lR] [-k kopt] [format_options]

    [[-r rev1 | -D date1] [-r rev2 | -D date2]] [files...]

        -l      Local directory only, not recursive

        -R      Process directories recursively.

        -k kopt Specify keyword expansion mode.

        -D d1   Diff revision for date against working file.

        -D d2   Diff rev1/date1 against date2.

        -r rev1 Diff revision for rev1 against working file.

        -r rev2 Diff rev1/date1 against rev2.

format_options:

  -i  --ignore-case  Consider upper- and lower-case to be the same.

  -w  --ignore-all-space  Ignore all white space.

  -b  --ignore-space-change  Ignore changes in the amount of white space.

  -B  --ignore-blank-lines  Ignore changes whose lines are all blank.

  -I RE  --ignore-matching-lines=RE  Ignore changes whose lines all match RE.

  --binary  Read and write data in binary mode.

  -a  --text  Treat all files as text.

  -c  -C NUM  --context[=NUM]  Output NUM (default 2) lines of copied context.

  -u  -U NUM  --unified[=NUM]  Output NUM (default 2) lines of unified context.

    -NUM  Use NUM context lines.

    -L LABEL  --label LABEL  Use LABEL instead of file name.

    -p  --show-c-function  Show which C function each change is in.

    -F RE  --show-function-line=RE  Show the most recent line matching RE.

  --brief  Output only whether files differ.

  -e  --ed  Output an ed script.

  -f  --forward-ed  Output something like an ed script in forward order.

  -n  --rcs  Output an RCS format diff.

  -y  --side-by-side  Output in two columns.

    -W NUM  --width=NUM  Output at most NUM (default 130) characters per line.

    --left-column  Output only the left column of common lines.

    --suppress-common-lines  Do not output common lines.

  --ifdef=NAME  Output merged file to show `#ifdef NAME' diffs.

  --GTYPE-group-format=GFMT  Similar, but format GTYPE input groups with GFMT.

  --line-format=LFMT  Similar, but format all input lines with LFMT.

  --LTYPE-line-format=LFMT  Similar, but format LTYPE input lines with LFMT.

    LTYPE is `old', `new', or `unchanged'.  GTYPE is LTYPE or `changed'.

    GFMT may contain:

      %<  lines from FILE1

      %>  lines from FILE2

      %=  lines common to FILE1 and FILE2

      %[-][WIDTH][.[PREC]]{doxX}LETTER  printf-style spec for LETTER

        LETTERs are as follows for new group, lower case for old group:

          F  first line number

          L  last line number

          N  number of lines = L-F+1

          E  F-1

          M  L+1

    LFMT may contain:

      %L  contents of line

      %l  contents of line, excluding any trailing newline

      %[-][WIDTH][.[PREC]]{doxX}n  printf-style spec for input line number

    Either GFMT or LFMT may contain:

      %%  %

      %c'C'  the single character C

      %c'\OOO'  the character with octal code OOO

  -t  --expand-tabs  Expand tabs to spaces in output.

  -T  --initial-tab  Make tabs line up by prepending a tab.

  -N  --new-file  Treat absent files as empty.

  -s  --report-identical-files  Report when two files are the same.

  --horizon-lines=NUM  Keep NUM lines of the common prefix and suffix.

  -d  --minimal  Try hard to find a smaller set of changes.

  -H  --speed-large-files  Assume large files and many scattered small changes.

(Specify the --help global option for a list of other help options)

EXAMPLE:

 \> cvs diff -r TAG_34 file_name

Index: file_name

===================================================================

RCS file: CVS_directory_path/file_name,v

retrieving revision 1.4

retrieving revision 1.6

diff -r1.4 -r1.6

When no file_name is specified, returns status for all files in current directory.

CVS COMMIT - commits into CVS repository modifications from local repository

\> cvs commit --help

commit: invalid option -- -

Usage: cvs commit [-Rlf] [-m msg | -F logfile] [-r rev] files...

    -R          Process directories recursively.

    -l          Local directory only (not recursive).

    -f          Force the file to be committed; disables recursion.

    -F logfile  Read the log message from file.

    -m msg      Log message.

    -r rev      Commit to this branch or trunk revision.

(Specify the --help global option for a list of other help options)

EXAMPLE:

cvs commit -m "Commit message" file_name

When file_name is not specified, all files in current directory are commited !!!!!

CVS LOG - returns CVS log modifications for file_name

\> cvs log --help

log: invalid option -- -

Usage: cvs log [-lRhtNb] [-r[revisions]] [-d dates] [-s states]

    [-w[logins]] [files...]

        -l      Local directory only, no recursion.

        -b      Only list revisions on the default branch.

        -h      Only print header.

        -R      Only print name of RCS file.

        -t      Only print header and descriptive text.

        -N      Do not list tags.

        -S      Do not print name/header if no revisions selected.  -d, -r,

                -s, & -w have little effect in conjunction with -b, -h, -R, and

                -t without this option.

        -r[revisions]   A comma-separated list of revisions to print:

           rev1:rev2   Between rev1 and rev2, including rev1 and rev2.

           rev1::rev2  Between rev1 and rev2, excluding rev1.

           rev:        rev and following revisions on the same branch.

           rev::       After rev on the same branch.

           :rev        rev and previous revisions on the same branch.

           ::rev       rev and previous revisions on the same branch.

           rev         Just rev.

           branch      All revisions on the branch.

           branch.     The last revision on the branch.

        -d dates        A semicolon-separated list of dates

                        (D1<D2 for range, D for latest before).

        -s states       Only list revisions with specified states.

        -w[logins]      Only list revisions checked in by specified logins.

(Specify the --help global option for a list of other help options)

EXAMPLE:

\> cvs log file_name

RCS file: CVS_directory_path/file_name,v

Working file: file_name

head: 1.6

branch:

locks: strict

access list:

symbolic names:

        TAG_34: 1.4

        TAG_33: 1.4

        TAG_30: 1.2

  .......

keyword substitution: kv

total revisions: 6;     selected revisions: 6

description:

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

revision 1.6

date: 2009-02-03 07:32:51 +0000;  author: roto1;  state: Exp;  lines: +4 -6;  commitid: 3K1Ow4Mss3EvmGRv;

Commit message

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

........

CVS REMOVE - removes a file from CVS repository

 \> cvs remove --help

remove: invalid option -- -

Usage: cvs remove [-flR] [files...]

        -f      Delete the file before removing it.

        -l      Process this directory only (not recursive).

        -R      Process directories recursively.

(Specify the --help global option for a list of other help options)

EXAMPLE:

-- remove files from local repository

\> rm file_name

-- instruct CVS to remove file from CVS repository

\> cvs remove file_name

-- commit into CVS repository file removal

cvs commit -m "remove message" file_name

-- cveck status

 \> cvs status file_name

===================================================================

File: no file file_name              Status: Up-to-date

   Working revision:    No entry for file_name

   Repository revision: 1.2     CVS_directory_path/Attic/file_name,v

   Commit Identifier:   JkdcT2aU3OHDyBRv

CVS ADD - add files to CVS repository

\> cvs add --help

add: invalid option -- -

Usage: cvs add [-k rcs-kflag] [-m message] files...

        -k      Use "rcs-kflag" to add the file with the specified kflag.

        -m      Use "message" for the creation log.

(Specify the --help global option for a list of other help options)

EXAMPLE:

$ mkdir -p foo/bar

$ cp ~/myfile foo/bar/myfile

-- files must exist before CVS add

$ cvs add foo foo/bar

$ cvs add foo/bar/myfile

-- use CVS commit to commit into CVS repository

$ cvs commit -m "Early version." myfile

CVS UPDATE - updates local repository with versions from CVS repository



 \> cvs update --help
update: invalid option -- -
Usage: cvs update [-APCdflRp] [-k kopt] [-r rev] [-D date] [-j rev]
    [-I ign] [-W spec] [files...]
        -A      Reset any sticky tags/date/kopts.
        -P      Prune empty directories.
        -C      Overwrite locally modified files with clean repository copies.
        -d      Build directories, like checkout does.
        -f      Force a head revision match if tag/date not found.
        -l      Local directory only, no recursion.
        -R      Process directories recursively.
        -p      Send updates to standard output (avoids stickiness).
        -k kopt Use RCS kopt -k option on checkout. (is sticky)
        -r rev  Update using specified revision/tag (is sticky).
        -D date Set date to update from (is sticky).
        -j rev  Merge in changes made between current revision and rev.
        -I ign  More files to ignore (! to reset).
        -W spec Wrappers specification line.
(Specify the --help global option for a list of other help options)

EXAMPLE:


/> cvs update file_name
-- cvs update merge between versions
/> cvs update -j TAG_34 -j TAG_30 file_name


CVS will continue ...