Dynamic Views

V$SGA	The V$SGA view is useful in determining how much total memory is allocated to the various componentsof the SGA. The following simple query gives you a summary of the SGA memory usage bythe current instance:	SQL> SELECT * FROM V$SGA;
V$SGASTAT	The V$SGASTAT view gives you a detailed breakdown of the SGA memory. It shows you currentmemory allocations broken down into the following main areas:	SQL> SELECT bytes from v$sgastat2 WHERE pool='shared pool' and3 V$SGA name='free memory'
V$SESSION	The V$SESSION view gives you a wealth of information about the users, including their operatingsystem username, terminal name, whether they’re actively executing a transaction or just connectedto the database, and how long their connection has been in place. In Oracle Database 10g,the V$SESSION view also contains several wait-related columns such as WAIT_CLASS_ID, WAIT_CLASS#, WAIT_CLASS, WAIT_TIME, and SECONDS_IN_WAIT.
V$SESSION_LONGOPS	The V$SESSION_LONGOPS view shows the status of all operations that run for a long time (morethan six seconds in absolute time). The columns SOFAR and TIME_REMAINING indicate how much ofthe work is done and how long the operation has to go before completing. The following is a samplequery using the view:	SQL> SELECT sid, opname, sofar,totalwork,2 start_time, time_remaining3* FROM V$SESSION_LONGOPS;
V$LOGFILE	The V$LOGFILE view provides information about each redo log file, including its name andwhether the file is valid or not. The STATUS column has the following values:	SQL> SELECT * FROM V$LOGFILE;
V$ARCHIVED_LOG	The V$ARCHIVED_LOG view is essential when you’re looking at information regarding whicharchive logs you have access to. The view contains one entry for every log that your databasearchives. When you restore an archive log, the operation inserts one row	SQL> SELECT name, thread#, sequence#,2 archived, applied, deleted, completion_time3* FROM V$ARCHIVED_LOG;
V$ARCHIVE_DEST	As its name indicates, the V$ARCHIVE_DEST view shows you each archive log destination and itsstatus. This view has a large number of columns, and you need to pay special attention to the followingcolumns:	SQL> SELECT dest_name2 FROM V$ARCHIVE_DEST;
V$SYSSTAT	The V$SYSSTAT view provides you with all the major system statistics: parse statistics, executionrates, full table scans, and other performance indices. The V$SYSSTAT view provides you with thebuffer-cache hit ratios and a number of other hit ratios. Listing 23-31 shows a summary of the mainclasses of statistics contained in the V$SYSSTAT view.	SQL> SELECT * FROM V$SYSSTAT;
V$OSSTAT	The new V$OSSTAT view comes in handy when you wish to check system usage statistics.

Setting Up Standby Database

When you create your STANDBY database, you'll need to create directories for database administration files, database files, and archive logs. You'll also need to prepare the standby instance by copying and configuring a parameter file, creating a password file, and creating Windows services on Windows


Standby init.ora file

db_name = PRACTICE
instance_name = STANDBY
service_names = STANDBY
control_files = ("/oradata/STANDBY/standby.ctl)
log_archive_dest_l = 'location=/oracata/STANDBY/archive'
LOG_ARCHIVE_DEST_2 = "MANDATORY service=STANDBY reopen=30"
standby_archive_dest = "/oradata/STANDBY/archive"
background_dump_dest = /app/oracle/admin/STANDBY/bdump
user_dump_dest = /app/oracle/admin/STANDBY/udump
db_file_name_convert = "/oradata/PRACTICE", "/oradata/STANDBY" log_file_name_convert = "/oradata/PRACTICE", "/oradata/STANDBY"
lock_name_space = STANDBY

Mount standby database

LINUX> export ORACLE_SID=STANDBY;
LINUX> sqlplus /nolog
SQL> CONNECT sys/standby AS SYSDBA;
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE

Recover standby database
SQL> RECOVER MANAGED STANDBY DATABASE;

If your physical standby has standby redo logs configured, it is possible to have the MRP begin applying changes as soon as they arrive to the standby instead of waiting for a log switch boundary and for the standby redo log to be archived. This new functionality is called real-time apply.

SQL> Recover managed standby database using current logfile;

Activate the Standby Database

1. Cancel standby database
   SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
   
2. Activate Standby databse
   SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
   SQL> SHUTDOWN;
   SQL> STARTUP;

Steps : For Failover

1. Perform terminal recovery on the standby by issuing managed recovery with the FINISH keyword. The following command is to be used if you have
   
   alter database recover managed standby database finish;
2. If you do not have standby redo logs, or they are not active, you must enter the following command:
   
   alter database recover managed standby database finish skip standby logfile;
3. Once the terminal recovery command completes, convert the standby into a primary database by entering the following command:
   
   alter database commit to switchover to primary;
4. Step 5. Restart the new primary database.

Using Rman to create standby database


RMAN > connect target /
RMAN > run {
backup database
include current controlfile for standby;
sql "alter system current log file";
}


RMAN > connect auxiliary /
RMAN > Duplicate target database for standby dorecover;

Checking archive log statusPrimary
SQL > select status, error from v$archive_dest;

Standby
SQL > select * from v$standby_log;

Confirm Managed Recovery
How can you know if the managed archive propagation process is running properly? You can look in three places:.

1. Archive files Look at the STANDBY database archive destinationfor archive logs being transmitted from the primary. New archivelog files on the PRACTICE database will be reproduced in the/oradata/STANDBY/archive directory.
   
   
2. Standby alert log Check the STANDBY alert.log for archive logs applicationentries. If you haven't seen any activity yet, perform a few log switches on thePRACTICE database. Wait a few minutes and look for evidence that the newarchive logs were transported and applied.
   
   
3. Media Recovery Log /oradata/STANDBY/archive/71.arc Media Recovery Waiting for thread 1 seqtt 72
   Log history The third and final test is to select from v$log_history onboth the primary and standby databases. The following query should return the same number, bearing in mind that the standby might be afew seconds behind:.
   
   SQL> SELECT MAX(sequence^) FROM v$log_history;

Net8 Connect-Time Failover

You can use connect-time Net8 failover to cause clients to connect to a backup instance in cases where the primary instance cannot be reached. This makes the most sense in an OPS (Oracle Parallel Server) environment where multiple instances are all accessing the same database. However, it can be done in a non-OPS environment as well. If you are using Oracle's standby database feature, you can configure a net service name so that clients connect to the standby database whenever the primary database is unreachable. Similarly, you could connect to a backup database maintained using Oracle's replication features.

Dynamically registering global database names with your listeners
One important issue to be aware of is that connect-time failover only works if you are dynamically registering global database names with your listeners. If you are statically configuring global database names, then connect-time failover will not work in a consistent manner:

If you want to use Net8's connect-time failover feature, you need to delete the GLOBAL_DBNAME parameter and allow the database to register itself with the listener automatically. You can list the database in your SID_LIST; you just can't include the GLOBAL_DBNAME parameter.

Listener.ora on ARK1
LSNR817 =

(DESCRIPTION_LIST =

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = arkum)(PORT = 1521)) )

(DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = ARK1)) ) )
SID_LIST_LSNR817 = (SID_LIST = (SID_DESC = (ORACLE_HOME = D:\Oracle\Product\8.1.7) (SID_NAME = ARK1) )

)

Listener.ora on DIA3
LSNRDIA3 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.138.21)(PORT = 1523)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = DIA3)) ) )
SID_LIST_LSNRDIA3 = (SID_LIST = (SID_DESC = (ORACLE_HOME = /opt/oracle/product/8.1.7) (SID_NAME = DIA3) ) )

Failover Configuration in Tnsnames.ora on Net8 Client
PROD.WORLD =

(DESCRIPTION_LIST =

(FAILOVER = TRUE)

(LOAD_BALANCE = FALSE)

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = arkum)(PORT = 1521))

(CONNECT_DATA = (SERVICE_NAME = ARK1)

(SERVER = DEDICATED)

)

)

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = diamond)(PORT = 1523))

(CONNECT_DATA = (SERVICE_NAME = DIA3)

(SERVER = DEDICATED)

)

)

)

When you connect using the service name PROD, Net8 will first try to connect to the database service ARK1 on the host arkum. If that connection fails, Net8 will move on to the database service DIA3 on the host named diamond.

Notice that the description list contains both (FAILOVER = true) and (LOAD_BALANCE = false). (FAILOVER = true) still represents the default behavior. It's included here to make it clear that failover is being used. (LOAD_BALANCE = false), however, does not represent the default behavior in this case. It's included to disable client load balancing, which is enabled by default whenever multiple descriptions are being used. With client load balancing enabled, Net8 would randomly choose descriptions from the description list. By disabling client load balance iou ensure that Net8 tries each DESCRIPTION in the order in which it appears in the list.

Using Materialized Views

The use of materialized views, or snapshots as they were previously known, is the simplist way to achive replication of data between sites. The materialized view is a table whose contents are periodically refreshed using a query against a remote table

Query Rewriting
The QUERY_REWRITE_ENABLED initialization parameter determines whether Oracle will rewrite a queryor not. The default value for this parameter is FALSE,

Refresh Mode
You can choose between the ON COMMIT and ON DEMAND modes of data refresh.

• ON COMMIT: In this mode, whenever a data change in one of the master tables is committed,the materialized view is refreshed automatically to reflect the change.
• ON DEMAND: In this mode, you must execute a procedure like DBMS_MVIEW.REFRESH to updatethe materialized view.The default refresh mode is ON DEMAND.

Refresh Type

You can choose from the following four refresh types:

• COMPLETE: This refresh option will completely recalculate the query underlying the materializedview. Thus, if the materialized view originally took you 12 hours to build, it’ll take aboutthe same time to rebuild it. Obviously, you wouldn’t want to use this option each time a fewrows are modified, dropped, or inserted into your master tables.
• FAST: Under the fast refresh mechanism, Oracle will use a materialized view log to log allchanges to the master tables. It’ll then use the materialized view log to update the mastertables, thus avoiding a complete refresh of the view. You can use other techniques toperforma fast refresh, but the materialized view log is the most frequently used devicefor this purpose.

Creating Materialized Views

SQL> GRANT CREATE DATABASE LINK TO scott;
SQL> GRANT CREATE MATERIALIZED VIEW TO scott;
SQL> GRANT QUERY REWRITE TO scott;

Creating the Materialized View Log

Let’s use the FAST refresh mechanism for our materialized view. This will require the creation of twomaterialized logs, of course, to capture the changes to the two master tables that are going to be thebasis for our materialized view. Here’s how you create the materialized view logs:Here’s how you create the materialized view log:

SQL> CREATE MATERIALIZED VIEW LOG ON products;
SQL> CREATE MATERIALIZED VIEW LOG ON sales;

SQL> CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE REFRESH FORCE
ON DEMAND AS SELECT * FROM emp@tsh1.world;

Initialization Parameters

Automatic Undo Management

Prior to Oracle 9i, a DBA had to manage rollback tablespaces and rollback segments manually. Failure to allocate enough segments, or to allocate enough space for those segments, would invariably leads to "ORA-01555: snapshot too old" error during long transactions. Since the advent of 9i, that worry can, and should, largely be eliminated.
3 new initialization parameters were added: UNDO_MANAGEMENT, UNDO_RETENTION, and UNDO_TABLESPACE.

To activate automatic undo management at least one undo tablespace exists.
set the UNDO_MANAGEMENT = AUTO
set the UNDO_RETENTION = 0 (zero),

Oracle will automatically tune for maximum retention of undo information based on the space available in the target undo tablespace, with the caveat that this automatic tuning mechanism will never tune for less than 15 minutes of retention.

Automatic Memory Tuning

Two new parameters, WORKAREA_SIZE_POLICY and PGA_AGGREGATE_TARGET

WORKAREA_SIZE_POLICY = TRUE
PGA_AGGREGATE_TARGET > 0 (zero)

In previous releases, or when not using the new automatic PGA tuning ability, a DBA had to carefully tune the SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE parameters to achieve optimal sort and join performance.

With automatic PGA tuning enabled, a process's needs shrink and grow, so does its PGA.
The recommended starting point for PGA_AGGREGATE_TARGET on an online transaction processing (OLTP) system is 16% of physical memory, and for DSS systems, it is 40% of physical memory.

Metalink Note 223730.1 suggests querying the V$SQL_WORKAREA_ACTIVE view to determine if any PGA work areas are undersized, resulting in writes to temporary segments.

SELECT
to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,
trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM,
trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS,
trunc(TEMPSEG_SIZE/1024) TSIZE
FROM
V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;

The goal is to have a cache hit ratio as close to 100% as possible, and to have zero processes overallocating their PGA.

Optimizer (CBO)

Some of the parameter values affect the decision of CBO. They should be change if necessary.

Default values during installation

• optimizer_index_caching=0
• optimizer_index_cost_adj=100

optimizer_index_caching=0 means “you don’t normally have any index blocks cached in RAM”(percent-value) . It should be around : 80-90

optimizer_index_cost_adj=100 means “index-access is just as expensive as full table scans” It should be about: 20-30 (i.e. cost is 1/5 or so)

Use GATHER_SCHEMA_STATS instead of Analyze Table

GATHER_SCHEMA_STATS( ownname=>’GEO’, cascade=>TRUE, method_opt=>’FOR ALL INDEXED COLUMNS SIZE AUTO’);

• cascade : analyzes indexes,
• method_opt : controls histogram generation,

Cloning a Database

I routinely refresh development and test databases, and they will sometimes need to clone databases to test backup and recovery strategies.

• By using the RMAN DUPLICATE command
• By using the OEM Database Control
• By manually performing the copy with SQL

Using RMAN to Clone a Database
RMAN provides the DUPLICATE command, which uses the backups of a database to create a newdatabase. The files are restored to the target database, after which an incomplete recovery is performedand the new database is opened with the OPEN RESETLOGS command.

1. Create a new init.ora file for the auxiliary database. The init.ora file should have the following parameters, with the data files and log file parameters changed to ensure that theoriginal database files arent used for the new database:
   
   - DB_FILE_NAME_CONVERT
   - LOG_FILE_NAME_CONVERT
2. Start the target database instance.
   
   SQL > startup nomount
3. Connect the recovery catalog to the target database and the auxiliary database
   
   RMAN > CONNECT target / catalog rman/rman1@catalog_db auxiliary sys/password@auxiliary_db
4. Issue the RMAN DUPLICATE command, as follows:
   
   RMAN> DUPLICATE TARGET DATABASE TO auxiliary_db
   pfile =/u01/app/oracle/10.2.0/db_1/dbs/init_auxiliary_db;
5. Opens the duplicated database with the RESETLOGS
   
   SQL> alter database open resetlogs

Manually Cloning a Database
To clone a database manually, you need to first use the operating system to copy all of the source database files to the target location.

1. Copy the prod database files to the target location.
2. Prepare a text file for the creation of a control file for the new database as follows:
   
   SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
   
3. On the target location, create all the directories for the various files.
   
4. change SID, path, of the backup control trace file controlfile_open.sql
5. Run the following command
   
   SQL> startup nomount;
   SQL> @controlfile_open.sql

Blocking Locks

Blocking Locks

A blocking lock occurs when a lock placed onfrom accessing the same object or objects. The information—it tells you which sessions are currentlyobject is presently waiting. You can combine in the V$SESSION tables, to find out who is holding

SQL> SELECT a.username, a.program, a.sid,
FROM v$session a, dba_blockers b
WHERE a.sid = b.holding_session;

DBA_BLOCKERS and DBA_WAITERS

SQL> SELECT waiting_session, blocking_session, lock_type
FROM DBA_BLOCKERS;

For 10g there is additional columns to check blocking session

select lpad(' ',3*(level-1)) SID SID, USERNAME, TERMINAL, CLIENT_INFO, EVENT
from V$SESSION
START WITH BLOCKING_SESSION_STATUS='VALID'
connect by prior BLOCKING_SESSION = SID

Tuning the log buffer

To tune the value for LOG_BUFFER first determine the space request ratio, this is the ratio of redo log space requests to redo log requests:

Select name, value from v$sysstat
Where name in ('redo log space requests', 'redo entries');

If the ratio (redo log space requests / redo entries) is greater than 1:5000, then increase the size of the redo log buffer until the space request ratio stops falling.
Alternately, if memory is not a constraint then try to reduce the number of times that a process had to wait for the log cache to be flushed:

Select name, value from v$sysstat
Where name = 'redo log space requests';

The number of waits should always be zero. If not, increase the size of LOG_BUFFER, until the number returns to zero. Typically, there is no advantage in setting this beyond 1M.
If you want to know how long processes had to wait as well as the number of times then try the following script instead:

Select name, value from v$sysstat
Where name in ('redo log space requests', 'redo log space wait time');

This shows the time in units of 10 milliseconds. Be ware that because of the time granularity, 'redo log space requests' may be greater than zero whilst 'redo log space wait time' is zero. This happens if none of the waits were for 10ms or longer. Unless you have a busy server having 'redo log space wait time' of (or near) zero may indicate an acceptable level of tuning.

Link of Oracle tuning
http://www.cryer.co.uk/brian/oracle/tuning.htm

Automatic Startup Scripts on Linux

Automatic Startup Scripts on Linux

Create a file in the /etc/init.d/ directory, in this case the file is called myservice, containing the commands you wish to run at startup and/or shutdown.

Use the chmod command to set the privileges to 750:
chmod 750 /etc/init.d/dbora

Link the file into the appropriate run-level script directories:
ln -s /etc/init.d/myservice /etc/rc0.d/K10dbora
ln -s /etc/init.d/myservice /etc/rc3.d/S99dbora

Associate the myservice service with the appropriate run levels:
chkconfig --level 345 dbora on

The script should now be automatically run at startup and shutdown (with "start" or "stop" as a commandline parameter) like other service initialization scripts.

Method 2

Create a script at /usr/local/bin with the following information (eg dbora)

/home/oracle/Orahome1/bin/lsnrctl start
sqlplus /nolog << EOF
conn / as sysdba;
startup;
exit;
EOF

Add the following lines into /etc/rc.d/rc.local
su - oracle -c "/usr/local/bin/startdb"

Good Solaris link

http://www.softpanorama.org/Net/Solaris/network_configuration.shtml

Statspack

All about Statspack

Installing Statspack

Run the ‘spcreate.sql’ script using SQL*Plus as user SYS. User PERFSTAT is created by this script, owning all objects needed by the statspack package.

SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin\spcreate

Removing Statspack
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin\spdrop

Taking Snapshot
Interactive way to take a snapshot

SQL> connect perfstat/perfstat
SQL> execute statspack.snap;

Automatically gather StatsPack snapshots
To use an Oracle-automated method for collecting statistics, you can use dbms_job. A sample script on how to do this is supplied in spauto.sql, which schedules a snapshot every hour, on the hour.

• change snapshot interval
  execute dbms_job.interval(,'SYSDATE+(1/48)');
  
• remove the autocollect job,
  execute dbms_job.remove();

To gather a STATSPACK report
@%oracle_home%/rdbms/admin/spreport

Some free statspack analysis report

http://www.statspackanalyzer.com/analyze.asp

Export / Import in Oracle

Export to multiple files

Tip : As of Oracle 8i ,Export file Greater than 2GB is not a problem

Syntax
exp tables= file= filesize=

Eg
C:\>exp sam/dba_pass tables=EMPTEST file=(exp1.dmp,exp2.dmp,exp3.dmp) filesize=1000M

IMPORT from more then one dump file


Syntax
imp tables= file=(file1.dmp ,file2.dmp...) filesize=

Caution : FILESIZE value in imp should match with FILESIZE value of Export eg in our case 1000M

for eg

C:\>imp sam/dba_pass tables=EMPTEST file=(exp1.dmp,exp2.dmp,exp3.dmp) filesize=1000M ignore=Y

Sync time

Windows 2000/XP's W32Time Service

The following example will make ntp1.example.local and ntp2.example.local our two synchronization sources (the quotes are only required when you have more than one server in your list).

net time /setsntp:"ntp1.example.local ntp2.example.local"

Now we need to start the "Windows Time" (W32Time) service. Open a command prompt (Click Start, then Run, type "cmd" and click ok.) and issue the following command:

net start "windows time"

net time [\\ComputerName] [/querysntp] [/setsntp[:NTPServerList]]
Parameters
file://ComputerName/ : Specifies the name of a server you want to check or with which you want to synchronize.
/set : Synchronizes the computer's clock with the time on the specified computer or domain.
/setsntp[:NTPServerList] : Specifies a list of NTP time servers to be used by the local computer. The list can contain IP addresses or DNS names, separated by spaces. If you use multiple time servers, you must enclose the list in quotation marks.

Check the list of time servers here
http://ntp.isc.org/bin/view/Servers/NTPPoolServers




Sync Time on Linux
# rdate -s time.nist.gov

Data Guard Broker thru CLI

The Data Guard broker is a centralized framework that creates, automates, and manages all aspects of a Data Guard configuration. This broker can be accessed locally or remotely by using either of the two clients: the command-line interface (CLI) or the Data Guard page of the new OEM. Once you are connected, you have the ability to change any attribute of the configuration as well as monitor progress and perform health checks.

The CLI Interface
We need to satisfy a few requirements. First, the physical or logical standby must have been created, as the CLI does not have the ability to create a standby (only the Data Guard GUI does). In addition, both the primary and standby databases must have been started with the DG_BROKER_START parameter equal to True (this spawns the DMON process) and be using an spfile.

init need to add
*.DG_BROKER_START=TRUE
*.FAL_CLIENT='ORCL1'
*.FAL_SERVER='ORCL2'
*.STANDBY_FILE_MANAGEMENT='AUTO'

c:> dgmgrl
DGMGRL> connect sys/password

DGMGRL> create
configuration 'MyDR' as
>primary database is 'orcl1'
>connect identifier is orcl1;

DGMGRL> add database 'orcl2' as
>connect identifier is orcl2
>maintained as physical;

DGMGRL> enable configuration;

DGMGRL> show configuration;

how to change the standby of the physical standby to the read-only mode for reporting purposes

DGMGRL> edit database 'orcl2' set state='READ-ONLY';

To return the physical standby to the recovery mode, we change the state once again.

DGMGRL> EDIT DATABASE 'orcl2' SET STATE='ONLINE';

DGMGRL> SWITCHOVER TO "ORCL2";

Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the primary database, as follows:

DGMGRL> SHOW DATABASE VERBOSE 'db01';

Performing a Failover Operation

You invoke a failover operation in response to an emergency situation, usually when the primary database cannot be accessed or is unavailable.

Connect to the target standby database.
To perform the failover operation, you must connect to the standby database to which you want to fail over using the SYSDBA username and password of that database. For example:

DGMGRL> CONNECT sys/oracle@db02

Issue the failover command.
Now you can issue the failover command to make the target standby database the new primary database for the configuration. Note that after the failover completes, the original primary database cannot be used as a viable standby database of the new primary database unless it is re-created as described in Section 4.2. T

DGMGRL> FAILOVER TO "DB02"

Some Data Guard links
http://www.dbasupport.com/oracle/ora10g/logical_standby_db.shtml
http://www.pafumi.net/Standby_Concepts_Configuration.html
http://www.oracle-base.com/articles/9i/DataGuard.php

Some Rman links
http://www.radford.edu/~wkantsio/oracle/rman-clone-win.htm

Manual Recovery - control files, redo log,etc

Recovering from Loss of a Control File

Losing one of the multiplexed control files immediately aborts the instance.
If you have not lost every control file, recovering from this failure is fairly straightforward.

SQL> startup nomount
SQL> select name, value from v$spparameter2 where name = 'control_files';

In the next step, you change the value of CONTROL_FILES in the SPFILE and restart theinstance, as you can see here:
SQL> alter system
set control_files ='/u02/oradata/ord/control01.ctl',
'/u06/oradata/ord/control02.ctl'4 scope = spfile;
SQL> shutdown immediate
SQL> startup

Recovering from Loss of a Redo Log File

A database instance stays up as long as at least one member of a redo log group is available. The alert log records the loss of a redo log group member.

1. Verify which redo log file group member is missing.
2. Archive the log file group’s contents; if you clear this log file group before archiving it, youmust back up the full database to ensure maximum recoverability of the database in the caseof the loss of a datafile. Use the command ALTER SYSTEM ARCHIVE LOG GROUP groupnum; toforce the archive operation.
3. Clear the log group to re-create the missing redo log file members using the command ALTER DATABASE CLEAR LOGFILE GROUP groupnum; you can also replace the missing member by copying one of the good group members to the location of the missing member

SQL> select * from v$logfile order by group#;
SQL> alter system archive log group 1;
SQL> alter database clear logfile group 1;
SQL> select * from v$logfile order by group#;

Linux Networking links

Some good Linux networking links

NIS Configuration

• http://linuxhelp.blogspot.com/2005/06/nis-client-and-server-configuration.html
• www.angelfire.com/linux/linuxclusters/nis.htmwww.linuxhomenetworking.com
  

Samba Configuration

• http://www.reallylinux.com/docs/sambaserver.shtml
• http://www.reallylinux.com/docs/basicnetworking.html

NFS Configuration

• www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/
• www.linuxhomenetworking.com

DBMS_APPLICATION_INFO package

I often see requests to investigate why a particular job is taking longer than expected, or to kill a session running a particular job. The problem is identifying the session, and they trying to identify what the session is doing, or what part of the batch process is running.

If developers used the DBMS_APPLICATION_INFO package to instrument their code it would make mine that their life much easy. The package allows you to specify a Module and Action for the current position in the code. This can be monitored externally using V$SESSION and also appears in V$SQLAREA to allow you to match SQL to a module.

You can also use the package to put your own progress information in V$SESSION_LONGOPS. If you haven't come across this view before Oracle itself populates it when doing "long operations", so you can monitor the progress of an index rebuild, or how far a FTS has got. With the DBMS_APPLICATION_INFO package you can show the progress of you own batch processing, eg. You have processes 300 contracts out of 2000 etc.

- at start
DBMS_APPLICATION_INFO.SET_MODULE( 'TEST MODULE','AT START' );
- when complete
DBMS_APPLICATION_INFO.SET_MODULE( NULL,NULL );

The first monitors what the progress in v$SESSION ...
SELECT sid , module ,action
FROM v$session
WHERE module IS NOT NULL

The second monitors V$SESSION_LONGSOPS through the long ops section

SELECT sid ,opname,sofar,totalwork,units,elapsed_seconds ,time_remaining FROM v$session_longops WHERE sofar != totalwork;

VMware for Oracle

You want to run Oracle on multiple platforms? You want to be able to install once and then go ahead and play with the database after having set it up once and not need to reinstall the software . You are very curious to learn these newer technologies, you want to watch and learn how RAC works but unfortunately you just have one computer at home. Even at work it's not just that easy to get a couple of machines , if not servers, to hook them up, build clusters and have them all shared Cooked or RAW via a SCSI let alone JBOD, SAN or NAS.

Good VMware with links with Oracle
http://www.dbasupport.com/oracle/ora10g/RACingAhead0101.shtml
http://www.oracle.com/technology/tech/linux/vmware/cookbook/index.html
http://oracle-base.com/articles/10g/OracleDB10gR2RACInstallationOnWindows2003UsingVMware.php

Performance monitoring on Windows

Oracle Performance Monitoring on Windows

The Oracle Counters for Windows Performance Monitor package is not installed by default. In order to install them when you install Oracle, select the custom install option. You can also install this option later via the Oracle installer. Once Oracle Counters for Windows Performance Monitor has been installed, you must perform one more piece of setup. The Oracle performance counters are set up to monitor one Oracle instance. Information about this instance must be configured in the registry. In order to do this, from a command prompt run orafcfg.exe with a username, password and Oracle net service name as follows:

operfcfg –U system –P password –D sid

This will update the registry. You should now be able to monitor Oracle via perfmon. Some of the things that you can monitor are:

• The Oracle Buffer Cache. Here you can see the cache miss ratio.
• Shared Pool Stats. This collection includes the data dictionary cache, and the library
  cache.
• Log Buffer. Provides information on log space requests.
• Database Data Files. This object provides physical read and write per second counters.
• DBWR stats. Provides information on the DB Writer processes.
  

Miscellaneous. Other statistics include dynamic space management, free lists and dynamic sorts.
By taking advantage of Oracle Counters for Windows Performance Monitor you can easily and efficiently monitor Oracle along with monitoring the OS. Some of the most important and first counters that I look at when performance monitoring a system are:

• Processor: %Processor Time. This gives me a quick look at how busy the system is.
• Physical Disk: Avg. Disk sec/Read, Avg. Disk sec/Write. This provides me with an overview of how well the I/O subsystem is doing.

When first looking at a system I am actually more interested in disk latencies than throughput. The Avg. Disk sec/Read and Avg. Disk sec/Write should be in the range of 5-15 ms (0.005 – 0.015). Anything higher than this indicates a problem.

Performance monitoring on Linux

There are some built in command in Linux to check the performance of linux eg

• top : Provide information (frequently refreshed) about the most CPU-intensive processes currently running. you can sort by CPU% or MEM% by typing 'F'
• ps -aux : all the processes in the system. use "grep "to filter which processes
• free : Display statistics about memory usage: total free, used, physical, swap, shared, and buffers used by the kernel.Easy monitoring with "SAR"

The SAR suite of utilities is bundled with your system (in fact, it is installed on most flavors of UNIX®), but probably not enabled. To enable SAR, you must run some utilities at periodic intervals through the cron facility. Use the crontab -e command while running as the root user

• mpstat : average CPU statistic
• iostat : flow of data to and from disk drive
• vmstat : memory, like "free"

Useful link

http://www-128.ibm.com/developerworks/aix/library/au-unix-perfmonsar.html