ORACLE DBA-APPS DBA

CHANGING DATABASE CHARACTER SET

2008-06-10T03:28:00.000-07:00

This article describes how one can change the character set of a database.
It should be done with extreme caution having noted the following
limitations.

The database character set should only be changed if the characters of the
code page of the originating database correspond to the same characters
of the target database, ie. if the database was created with the
characterset US7ASCII and it is to be updated to WE8ISO8859P1. Since these
have the same encoding scheme for the first 127 bits, changing the
character set from US7ASCII to WE8ISO8859P1 will display all characters up
to 127 bits as the same character before and after. In addition, in this
particular example, if any characters have been entered with the 8th bit
set, then updating the database characterset to 8 bit will allow that 8th
bit to be displayed. You must not change the characterset from one encoding
scheme to another encoding scheme where the code pages do not correspond.
This will completely scramble your database. In addition, if case*designer
diagrams are stored in the database, this method must not be used. Contact
Worldwide Support for further details.

Before attempting to run any of the scripts below, you must take a full
cold backup of your database. In addition, the procedure must be thoroughly
tested before attempting this on a production instance.

Here's a SQL*PLUS script that allows a database's character set to be
changed to a different encoding scheme without having to rebuild the database.
set echo off
set verify off

rem The data dictionary table that records the database
rem character set is sys.props$
rem
rem SQL> describe sys.props$
rem Name Null? Type
rem ------------------------------- -------- ----
rem NAME NOT NULL VARCHAR2(30)
rem VALUE$ VARCHAR2(2000)
rem COMMENT$ VARCHAR2(2000)

rem For example:
rem
rem SQL> column c1 format a30
rem SQL> select name c1, value$ c1 from sys.props$;

rem C1 C1
rem ------------------------------ ------------------------------
rem DICT.BASE 2
rem NLS_LANGUAGE AMERICAN
rem NLS_TERRITORY AMERICA
rem NLS_CURRENCY $
rem NLS_ISO_CURRENCY AMERICA
rem NLS_NUMERIC_CHARACTERS .,
rem NLS_DATE_FORMAT DD-MON-YY
rem NLS_DATE_LANGUAGE AMERICAN
rem NLS_CHARACTERSET WE8DEC
rem NLS_SORT BINARY
rem GLOBAL_DB_NAME NLSV7.WORLD

rem NLS_CHARACTERSET can be changed by updating its value, for example:

rem update sys.props$
rem set value$ = 'WE8ISO8859P1'
rem Where name = 'NLS_CHARACTERSET';

rem The database has to be shutdown and restarted before the change
rem becomes effective.

rem It is very important to specify the character set name correctly.
rem IMPORTANT NOTE
rem =============
rem If NLS_CHARACTERSET is updated to an invalid value, it will not then
rem be possible to restart the database once it has been shutdown.
rem To recover, it will be necessary to re-create the database, since it
rem cannot be restarted to correct the invalid NLS_CHARACTERSET entry.

rem The character set name should be in uppercase.
rem The new value is not effective until the database has been shutdown and
rem restarted.
rem
rem A suggested procedure is as follows, and can be done by running this
rem script from SQL*Plus when logged into the SYSTEM account.
rem
rem USAGE : SQL> start ch_db.sql
rem
rem where is the desired database character set
rem

Prompt First check that the character set name is valid.

set echo on

select convert('a','&1','us7ascii') from dual;

set echo off

prompt If this select statement returns error ORA-01482, then the
prompt specified character set name is not valid for this installation.
prompt Abort the procedure now with Control-c

prompt To continue, press return
accept ans CHAR

Prompt Check the current value of database character set.

column c1 format a30
select name c1, value$ c1 from sys.props$
where name = 'NLS_CHARACTERSET';

prompt To continue, press return

Prompt Update to new character set

update sys.props$
set value$ = upper('&1')
where name = 'NLS_CHARACTERSET';

set echo off

prompt To continue, press return
accept ans CHAR

Prompt Check the new value of database character set

select name c1, value$ c1 from sys.props$
where name = 'NLS_CHARACTERSET';

Prompt If the value is updated as required, press return to continue and
Prompt then manually type COMMIT; to commit the change. Then shutdown and
Prompt restart the database.
Prompt
Prompt If the value is not updated as required, press return to continue and
Prompt than manually type ROLLBACK; to prevent the change.

prompt To continue, press return
accept ans CHAR

Setting Listener Password

2008-06-04T07:36:00.001-07:00

One of the biggest loophole that an Oracle database installation can have is Oracle Listener without a password. At the time of client server this fact somehow can be overlooked as we know who our user are, but now in days of web we don’t them so its becomes very important that we secure Oracle Listener.

Setting the password for the listener can be done in following three ways:

1. Editing the listener.ora file and setting the password in it.
2. Using LSNRCTL utility.
3. Through Oracle Graphical tools such as Net Manager, Oracle Enterprise Manager and so on.

In this blog entry I will mainly concentrate on first two.

Under first method we can edit the listener.ora file and add the following line in it.

PASSWORDS_LISTENER = oraclebrains

and then restart the listener. But the drawback with this method is that password is stored in plain text format without encryption.

In Second method, we can run LSNRCTL utility and then can give command as follows:

LSNRCTL>change_password

After it it will prompt your for old password, if it is there you can type in or press enter.

Then it will prompt you for the new password which you want to set and then press enter.

Then it will again prompt you to reenter the new password for confirmation and then press enter.

After this password will be changes for running instance or session of the listener.

If we want it applicable for all the future instance or session we need to save the configuration for future use as follows:

LSNRCTL> set password

LSNRCTL> save_config

One these steps are completed, if we open listener.ora file we will notice that same line as we add in first method is added but password is in encrypted format.

PASSWORDS_LISTENER = 56ETERY34373WWEGW27362

Understanding Asynchronous COMMIT

2008-06-04T07:35:00.001-07:00

Before Oracle 10g Release 2, Life cycle of a transaction was as follows:

- Transaction Starts When User Issues the First DML, Oracle generates redo entries corresponding to the DML and write it to buffer in memory.
- While user is issuing DML, Oracle generates redo entries corresponding to the DML and write it to buffer in memory.
- Transaction Ends When User Issues a COMMIT (can be explicit or implicit), Oracle immediately writes this buffered redo to disk.

Main point to understand here is that once COMMIT is issued, Oracle does not return the control to the user until the redo entries corresponding to that commited transaction have been physically written to disk.

This way application can be 100% sure that once the transaction is commited, it is protected against instance failure and commited data can be recovered by “rolling forward” log files.

This is another words we can say is synchronous COMMIT.

Now Asynchronous COMMIT:

In Oracle 10g Release 2, keeping in view a very high speed transaction environment, Oracle has introduce two options:

1. One is to return immediately after the COMMIT is issued, rather than waiting for the log activity to complete.
2. Another option batches multiple transactions together in memory before writing to the disk.

The full syntax of the new WRITE clause is:

COMMIT [WRITE [IMMEDIATE | BATCH] [WAIT | NOWAIT] ]

By default, if no WRITE clause is specified, a normal COMMIT is equivalent to

COMMIT WRITE IMMEDIATE WAIT;

The COMMIT statement has a new clause, WRITE, which indicates these options. The default COMMIT statement is the same as:

COMMIT WRITE IMMEDIATE WAIT;

To specify that the transaction should be written individually but that the application should not wait for it, specify:

COMMIT WRITE IMMEDIATE NOWAIT;

or just

COMMIT WRITE NOWAIT;

Likewise, to specify that Oracle’s log writer process (LGWR) is allowed to batch multiple transactions together before writing, specify:

COMMIT WRITE BATCH NOWAIT;

The option can also be set system-wide by modifying the commit_write instance parameter. Multiple options are separated by commas, as:

ALTER SYSTEM SET commit_write = BATCH, NOWAIT;

Enabling Faster Incremental Backups in Oracle 10g

2008-06-04T07:34:00.000-07:00

In this blog entry let me introduce you to a new capability in Oracle 10g called Block Change Tracking, which can help DBA’s do faster incremental backups via RMAN (Recovery Manager).

Once this new capability in Oracle 10g is enabled it start recording the modified since last backup and stores the log of it in a block change tracking file.

Later while doing backup RMAN uses this file to determine which blocks are needed to be backed up?

Logically as we can see, this process improves the performance as RMAN does not have to scan whole datafile to detect which block in it has changed and needs backup.

This may not show any significant impact on smaller database but definitely will have big impact on bigger databases as now with this new functionality it does not have to scan full datafile but only need to query block change tracking file.

To create the Change Tracking file and enable Change Tracking, make sure the database is either open or mounted. Then issue following SQL:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING
FILE ‘c:\oracle\oradata\rkt\block_track\block_track_file01.log’;

Database altered.

Once you issue above SQL, Oracle creates a new background process which is responsible for writing data to the block change tracking file, which is called the block change writer CTRW.

To verify the status of block change tracking, use following SQL:

SQL> SELECT filename, status, bytes FROM v$block_change_tracking;

FILENAME

——————————————————

STATUS BYTE

————— ————–

C:\ORACLE\ORADATA\RKT\BLOCK_TRACK\BLOCK_TRACK_FILE01.LOG

ENABLED 1024

To disable block change tracking, use following SQL:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Posted in Articles:: Oracle Backup, Blog::Oracle Backup | No Comments »

creating UNIQUE index on a table with existing non unique values.
Monday, May 7th, 2007
Today I came up with this challenge of creating UNIQUE index on a table with exiting non unique value. After lot of r&d I came up with following very interested solution:

I am using scott schema to show my solution.

SQL> create table test as select deptno from emp;

Table created.

SQL> select deptno from test;

DEPTNO
———-
20
30
30
20
30
30
10
20
10
30
20

DEPTNO
———-
30
20
10

14 rows selected.

SQL> alter table test add constraint unique_index unique(deptno) disable;

Table altered.

SQL> create index unique_index on test(deptno);

Index created.

SQL> alter table test enable novalidate constraint unique_index;

Table altered.

SQL> insert into test values (20);
insert into test values (20)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UNIQUE_INDEX) violated

Oracle Histograms

2008-06-04T07:30:00.000-07:00

About Oracle Histograms

Histograms may help the Oracle optimizer in deciding whether to use an index vs. a full-table scan (where index values are skewed) or help the optimizer determine the fastest table join order. For determining the best table join order, the WHERE clause of the query can be inspected along with the execution plan for the original query. If the cardinality of the table is too-high, then histograms on the most selective column in the WHERE clause will tip-off the optimizer and change the table join order.

Most Oracle experts only recommend scheduled re-analysis for highly dynamic databases, and most shops save one very-deep sample (with histograms), storing the statistic with the dbms_stats.export_schema_stats procedure. The only exceptions are highly-volatile systems (i.e. lab research systems) where a table is huge one-day and small the next.

For periodic re-analysis, many shops us the table "monitoring" option and also method_opt "auto" after they are confident that all histograms are in-place.

Oracle histograms statistics can be created when you have a highly skewed index, where some values have a disproportional number of rows. In the real world, this is quite rare, and one of the most common mistakes with the CBO is the unnecessary introduction of histograms in the CBO statistics. As a general rule, histograms are used when a column's values warrant a change to the execution plan.

If you need to reanalyze your statistics, the reanalyze task will be less resource intensive with the repeat option. Using the repeat option will only reanalyze indexes with existing histograms, and will not search for other histograms opportunities. This is the way that you will reanalyze you statistics on a regular basis.

--**************************************************************
-- REPEAT OPTION - Only re-analyze histograms for indexes
-- that have histograms
--
-- Following the initial analysis, the weekly analysis
-- job will use the “repeat” option. The repeat option
-- tells dbms_stats that no indexes have changed, and
-- it will only re-analyze histograms for
-- indexes that have histograms.
--**************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 7
);
end;
/

Find histograms for foreign key columns - Many DBAs forget that the CBO must have foreign-key histograms in order to determine the optimal table join order (i.e. the ORDERED hint).

Fix the cause, not the symptom - For example, whenever I see a sub-optimal order for table joins, I resist the temptation to add the ORDERED hint, and instead create histograms on the foreign keys of the join to force the CBO to make the best decision.

For new features, explore the Oracle10g automatic histograms collection mechanism that interrogates v$sql_plan to see where the foreign keys are used. It claims to generate histograms when appropriate, all automatically.

This is one reason that the ORDERED hint is so popular, but it has been shown that having liberal column histograms on the table columns can often aid the optimizer in making better execution plans.

In sum, histograms are not just for non-unique column values that are unevenly distributed (skewed), and several noted DBA’s have suggested that more liberal use of histograms will aid the CBO is making better decisions. The dbms_stats “auto” feature detects and builds column histograms, but it has the shortcoming of being too conservative in some cases.

Savvy DBA’s are now experimenting with broad-brush histograms, for all indexes columns. I first heard of this technique from Jeff Maresh (noted data warehouse consultant), who told me that he has taken to creating 10-bucket histograms for all data warehouse table columns. I heard this advice again at the IOUG conference from Arup Nanda (noted author and DBA of the year) and from Mike Ault.

They are abandoning the use of the “auto” option and manually creating 20-bucket histograms across-the-board, and they claim that it can make a huge difference for databases with lots of multi-table joins in he SQL.

I’ve not tried this technique yet, but when three experts make the assertion, I believe that there may be something to the new technique. The only downside, of course, is the time required to gather the column histograms and a small amount of additional storage in the data dictionary.

One exciting feature of dbms_stats is the ability to automatically look for columns that should have histograms, and create the histograms. Multi-bucket histograms add a huge parsing overhead to SQL statements, and histograms should ONLY be used when the SQL will choose a different execution plan based upon the column value.

Oracle's V$ Views

2008-06-04T07:18:00.000-07:00

v$archive_dest
Shows all archived redo log destinations. Use this view to find out to which place archived redo logs are copied: select dest_id,destination from v$archive_dest These values correspond to the init parameter log_archive_dest_n.
v$archive_dest_status
This view allows to find status and errors for each of the defined
v$archived_log
Displays successfully archived redo logs. shows received logs on a primary standby database.
v$archive_gap
Lists sequence numbers of the archived los that are known to be missing for each thread on a (physical?) standby database (highest gap only).
v$archive_processes
This view provides information on the archive processes. It can be used to find out if an ARCH process is active or not.
v$controlfile
Displays the location and status of each controlfile in the database.
v$controlfile_record_section
See sections in a controlfile.
v$bh
This dynamic view has an entry for each block in the database buffer cache. The column status can be:
free
This block is not in use
xcur
Block held exclusively by this instance
scur
Block held in cache, shared with other instance
cr
Block for consistent read
read
Block being read from disk
mrec
Block in media recovery mode
irec
Block in instance (crash) recovery mode

v$buffer_pool
See buffer pools. This view's column id can be joined with x$kcbwds.indx See also x$kcbwbpd
v$buffer_pool_statistics
v$database
This view lets you access database information. For example, you can check (using log_mode) whether or not the database is in archivelog mode:
ADPDB>select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG
checkpoint_change# records the SCN of the last checkpoint. switchover_status: can be used to determine if it is possible to perform a switchover operation Only available for physical standby databases. Can be:
NOT ALLOWED,
SESSIONS ACTIVE,
SWITCHOVER PENDING,
SWITCHOVER LATENT,
TO PRIMARY,
TO STANDBY or
RECOVERY NEEDED.
See protection modes in data guard for the columns protection_mode and protection_level. database_role determines if a database is a primary or a logical standby database or a physical standby database. force_logging tells if a database is in force logging mode or not.
v$datafile
This view contains an entry for each datafile of the database. This view can be used to find out which datafiles must be backed up in a cold backup: select name from v$datafile
v$datafile_header
Various information about datafile headers. For example, if you're interested in when the a file's last checkpoint was:
select name, checkpoint_change#, to_char(checkpoint_time, 'DD.MM.YYYY HH24:MI:SS') from v$datafile_header
v$dataguard_status
Shows error messages in a data guard environment.
v$db_object_cache
This view displays objects that are cached (pinned) in the library cache. See also dbms_shared_pool.
v$enqueue_stat
If there are a lot of enqueue waits "in" v$session_event or v$system_event, v$enqueue_stat allows to break down those enqueues in enqueue classes. For each such class, the gets, waits, failures and the cumulative sum of waited time can be found.
For a list of enqueue types, refer to enqueue types in x$ksqst. The column cum_wait_time stems from x$ksqst.ksqstwtim.
v$eventmetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$event_name
Contains a record for each wait event.
v$filemetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$filestat
v$fixed_table
This view contains the name of all V$, X$ and GV$ tables. In oracle 8.1.7, there are 187 different v$ tables:
ORA81> select count(*) from v where name like 'V$%';

COUNT(*)
----------
185
If you want to know, which x$ tables there are, do a select name from v$fixed_table where name like 'X$%';
v$fixed_view_definition
Contains the defintion in its attribute view_definition for the views of v$fixed_table.
v$flash_recovery_area_usage
See also v$recovery_file_dest
v$instance
instance_role can be used to determine if an instance is an active instance (=primary instance) or a secondary instance (in a standby environment. dbms_utility.db_version can be used to retrieve the same version as the field version in v$instance.
v$latch
Oracle collects statistics for the activity of all latches and stores these in this view. Gets is the number of successful willing to wait requests for a latch. Similarly, misses is how many times a process didn't successfully request a latch. Spin_gets: number of times a latch is obtained after spinning at least once. Sleeps indicates how many times a willing to wait process slept. Waiters_woken tells how often a sleeping process was 'disturbed'.
v$librarycache
v$lock
This view stores all information relating to locks in the database. The interesting columns in this view are sid (identifying the session holding or aquiring the lock), type, and the lmode/request pair. Important possible values of type are TM (DML or Table Lock), TX (Transaction), MR (Media Recovery), ST (Disk Space Transaction). Exactly one of the lmode, request pair is either 0 or 1 while the other indicates the lock mode. If lmode is not 0 or 1, then the session has aquired the lock, while it waits to aquire the lock if request is other than 0 or 1. The possible values for lmode and request are:
1: null,
2: Row Share (SS),
3: Row Exclusive (SX),
4: Share (S),
5: Share Row Exclusive (SSX) and
6: Exclusive(X)
If the lock type is TM, the column id1 is the object's id and the name of the object can then be queried like so: select name from sys.obj$ where obj# = id1 A lock type of JI indicates that a materialized view is being refreshed. A more detailed example can be found here See also x$kgllk.
v$locked_object
Who is locking what:
select
oracle_username
os_user_name,
locked_mode,
object_name,
object_type
from
v$locked_object a,dba_objects b
where
a.object_id = b.object_id
v$log
Contains information on each log group. See also online redo log. Comman values for the status column are:
UNUSED:
Oracle8 has never written to this group,
CURRENT:
This is the active group.
ACTIVE:
Oracle has written to this log before, it is needed for instance recovery.
The active log is the one with the current log sequence number
INACTIVE:
Oracle has written to this log before; it is not needed for instance recovery.

v$logfile
This view can be queried to find the filenames, group numbers and states of redo log files. For example, to find all files of group 2, use select member from v$logfile where group# = 2
v$logmnr_contents
See dbms_logmnr.
v$log_history
This view contains an entry for each Log Switch that occured. The column first_time indicates the time of the first entry??? On physical standby databases, this view shows applied logs.
v$logstdby
Can be used to verify that archived redo logs are being applied to standby databases.
v$managed_standby
Monitors the progress of a standby database in managed recovery mode, more exactly, it displays information about the activities of log transport service and log apply service. see here
select process, pid, status, client_process, group# "Stdby Redo Log Gr", block# from v$managed_standby;
client_process: the corresponding primary database process. If lgwr log transmission is chosen, one row should have client_process=LGWR. If ARCH transmission is chosen, one row should have ARCH.
v$mystat
This view records statistical data about the session that accesses it. Join statistic# with v$statname.
v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates. See also recording statistics with oracle.
v$nls_parameters
The NLS parameters that are in effect for the session quering this view. The view NLS_SESSION_PARAMETERS is based on v$nls_parameters. See also v$nls_valid_values.
v$nls_valid_values
This view can be used to obtain valid values for NLS parameters such as
supported character sets
languages
territories
sorting orders

v$object_usage
v$object_usage gathers information about used (accessed) indexes when an index is monitored using alter index ... monitoring usage. See On verifying if an index is used.
v$open_cursor
v$option
This view lets you see which options are installed in the server. See also dba_registry.
v$parameter
Lists the name-value pairs of the init.ora file (or their default, if not in the init.ora). For example, if you need to know what your block size is:
select value from v$parameter where name = 'db_block_size'
The columns isses_modifiable and issys_modifiable can be used to determine if a parameter can be changed at session level using alter session or at system level using alter system. A parameter is modifiable at session level if isses_modifiable = 'TRUE'. A parameter is modifiable at system level if issys_modifiable = 'DEFERRED' or issys_modifiable = 'IMMEDIATE'. However, if a parameter is changed at system level if issys_modifiable = 'DEFERRED' it only affects sessions that are started after chaning the parameter. Additionally, the alter system set ... deferred option must be used. There are also some undocumented (or hidden?) parameters.
v$pgastat
See also pga. Thanks to Oleg who notified me of a typo (v$pgastat instead of v$pga_stat).
v$process
Join v$process's addr with v$session paddr. The column traceid is equal to the value used in alter session set .
v$pwfile_users
Lists all users who have been granted sysdba or sysoper privileges. See adding user to a password file.
v$recover_file
Useful to find out which datafiles need recovery. Join with v$datafile to see filenames instead of numbers....
v$recovery_file_dest
See also v$flash_recovery_area_usage
v$reserved_words
This view can be consulted if one is in doubt wheter a particular word is a reserved word (for example when writing PL/SQL Code or assigning a password to a user). Until 9i (is this correct?), the view only consist of two columns: keyword and length. From 10g onwards (?), it has also the columns reserved, res_type, res_attr, res_semi and duplicate. Each of these new columns can only be either 'Y' (meaning: yes) or 'N' (meaning: no) See also reserved words in SQL and reserved words in PL/SQL.
v$resource_limit
v$rollname
The names of online rollback segments. This view's usn field can be joined with v$rollstat's usn field and with v$transaction's xidusn field. v$transaction can be used to track undo by session.
v$rollstat
Statistics for rollback segements
v$session
The column audsid can be joined with sys_context('userenv','SESSIONID') to find out which session is the "own one". Alternatively, dbms_support.mysid can be used. The fields module and action of v$session can be set with dbms_application_info.set_module. (See v$session_longops for an example. The field client_info can be set with dbms_application_info.set_client_info Join sid with v$sesstat if you want to get some statistical information for a particular sesssion. A record in v$session contains sid and serial#. These numbers can be used kill a session (alter system kill session). A client can set some information in client_info. For example, RMAN related sessions can be found with
.... where client_info like 'rman%';
What a session is waiting for can be queried with v$session_wait. However, with Oracle 10g, this is not nessessary anymore, as v$session_wait's information will be exposed within v$session as well. See also sessions.
v$sessmetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$session_event
This views is similar to v$system_event. However, it breaks it down to currently connected sessions. v$session_event has also the column max_wait that shows the maximum time waited for a wait event.
v$session_longops
Use v$session_longops if you have a long running pl/sql procedure and want to give feedback on how far the procedure proceeded. If the following Procedure is run, it will report its progress in v$session_longops. The Procedure will also set the module attribute in v$session which makes it possible to find the sid and serial# of the session.
create table f(g number);

create or replace procedure long_proc as
rindex pls_integer := dbms_application_info.set_session_longops_nohint;
slno pls_integer;
-- Name of task
op_name varchar2(64) := 'long_proc';

target pls_integer := 0; -- ie. The object being worked on
context pls_integer; -- Any info
sofar number; -- how far proceeded
totalwork number := 1000000; -- finished when sofar=totalwork

-- desc of target
target_desc varchar2(32) := 'A long running procedure';

units varchar2(32) := 'inserts'; -- unit of sofar and totalwork
begin

dbms_application_info.set_module('long_proc',null);

dbms_application_info.set_session_longops (
rindex,
slno);

for sofar in 0..totalwork loop

insert into f values (sofar);

if mod(sofar,1000) = 0 then
dbms_application_info.set_session_longops (
rindex,
slno,
op_name,
target,
context,
sofar,
totalwork,
target_desc,
units);

end if;

end loop;
end long_proc;
If the procedure long_proc is run, you can issue the following query to get feedback on its progress:
select time_remaining,sofar,elapsed_seconds
from v$session_longops l, v$session s
where l.sid=s.sid and l.serial# = s.serial# and s.module='long_proc'
v$session_wait
This views shows what wait event each session is waiting for, or what the last event was that it waited for.
In contrast, v$session_event lists the cumulative history of events waited for in a session. The columns P1, P2 and P3 are parameters that are dependant on the event. With Oracle 10g, v$session_wait's information will be exposed within v$session as well. Since 10g, Oracle displays the v$session_wait information also in the v$session view.
v$session_wait_history
This view is new in Oracle 10g and allows improved timing and statistics.
v$sesstat
This view is similar to v$mystat except that it shows cumulated statistics for all sessions. Join sid with v$session and join statistic# with v$statname. v$sesstat is also similar to v$sysstat, except that v$sysstat accumulates the statistics as soon as a session terminates.
v$sga
Shows how much memory the shared global area uses. Selecting * from v$sga is roughly the same as typing show sga in sql plus with the exeption that the latter also show the total.
v$sgastat
Showing free space in the sga:
select * from v$sgastat where name = 'free memory'
v$sga_dynamic_components
Information about SGA resize operations since startup. This view can also be used to find out the granule size of SGA components.
v$sga_resize_ops
v$sort_usage
See temporary tablespaces
v$sort_segment
See Temporary Tablespaces
v$spparameter
Returns the values for the spfile.
v$sql
v$sql is similar to v$sqlarea, the main difference being that v$sql drills down to select * from x$kglob whereas v$sqlarea drills down to select sum from x$kglob. See also here.
v$sqlarea
Join v$sqlarea's address with v$session's sql_address. Find the SQL-text of currently running SQL statements:
select sql_text from v$sqlarea where users_executing > 0;
The field version_count indicates how many versions an sql statement has.
v$sqltext
v$sql_plan
variable addr varchar2(20)
variable hash number
variable child number

exec :addr := '&sqladdr'; :hash := &hashvalue; :child := &childno;

select lpad(' ', 2*(level-1))||operation||' '||
decode(id, 0, 'Cost = '||position) "OPERATION",
options, object_name
from v$sql_plan
start with (address = :addr
and hash_value = :hash
and child_number = :child
and id=0 )
connect by prior id = parent_id
and prior address = address
and prior hash_value = hash_value
and prior child_number = child_number
order by id, position ;
In order to find valid values for sqladdr, hashvalue and childno, this SQL statement can be used:
select sql_text,address,hash_value,child_number from v$sql where users_executing > 0;
v$sql_text_with_newlines
This view can be used to construct the entire text for each session's actual SQL statement. Use the following statement to to that:
set serveroutput on size 1000000

declare
v_stmt varchar2(16000);
v_sql_text v$sqltext_with_newlines.sql_text%type;
v_sid v$session.sid%type;
begin
for r in (
select
sql_text,s.sid
from
v$sqltext_with_newlines t,
v$session s
where
s.sql_address=t.address
order by s.sid, piece) loop

v_sid := nvl(v_sid,r.sid);

if v_sid <> r.sid then
dbms_output.put_line(v_sid);
put_line(v_stmt,100);
v_sid := r.sid;
v_stmt := r.sql_text;
else
v_stmt := v_stmt || r.sql_text;
end if;

end loop;
dbms_output.put_line(v_sid);
dbms_output.put_line(v_stmt,100);

end;
/
Thanks to Sarmad Zafar who notified me of an error in this PL/SQL Block. Note: the function put_line is found here and can be used to prevent ORU-10028.
v$sql_bind_data
Join cursor_num with cno of v$sql_cursor.
v$sql_bind_capture
New with Oracle 10g This view captures bind variables for all sessions and is faster than setting 10046 on level 4.
v$sql_cursor
Join parent_handle with address of v$sql or v$sqlarea.
v$sql_workarea
v$sql_workarea can be joined with v$sqlarea on address and hash_value, and it can be joined with v$sql on address, hash_value and child_number.
v$standby_log
v$statname
Use this view to get decoded names for the statistic# field of v$mystat, v$sysstat and v$sesstat.
v$sysaux_occupants
v$sysaux_occupants doesn't exist in Oracle versions prior to Oracle 10g. See occupants in the sysaux tablepsaces.
v$sysmetric
This view is new in Oracle 10g and allows improved timing and statistics.
v$sysmetric_history
This view is new in Oracle 10g and allows improved timing and statistics.
v$sysstat
v$sysstat is similar to v$sesstat. While v$sesstat displays statitics for the current session, v$sysstat displays the cumulated statitics since startup of the database. For example, it is possible to find out the CPU time (name = 'CPU used by this session') This view is (among others) used to calculate the Hit Ratio.
v$system_event
This view displays the count (total_waits) of all wait events since startup of the instance. If timed_statistics is set to true, the sum of the wait times for all events are also displayed in the column time_waited. The unit of time_waited is one hundreth of a second. Since 10g, an additional column (time_waited_micro) measures wait times in millionth of a second. total_waits where event='buffer busy waits' is equal the sum of count in v$waitstat. v$enqueue_stat can be used to break down waits on the enqueue wait event. While this view totals all events in an instance, v$session_event breaks it down to all currently connected sessions.
v$undostat
undo tablespaces
v$tempfile
v$tempseg_usage
v$tempseg_usage is a public synonym for v$sort_usage.
v$tempstat
v$thread
The Oracle SID can be retrieved through select instance from v$thread
v$timer
This view has only one column (hsecs) which counts hundreths of seconds. Whenever it overflows four bytes, it starts again with 0.
v$transaction
Important fields of v$transaction are used_ublk and used_urec. They tell of how many blocks and records the undo for a transaction consists. In order to find out the name of the corresponding rollback segemnt, join the xidusn field with the usn field of v$rollname. This is demonstrated in Transactions generate undo
v$timezone_names
See also timezones for some values of tzabbrev.
v$transportable_platform
Which platforms are supported for cross platform transportable tablespaces.
v$version
Use this view to find out what version you actually work on: select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
v$waitstat
total_waits where event='buffer busy waits' is equal the sum of count in v$system_event.

Oracle DBA Checklist

2008-06-04T07:16:00.001-07:00

I. Daily Procedures

A. Verify all instances are up
Make sure the database is available. Log into each instance and run daily reports or test scripts. Some sites may wish to automate this.
Optional implementation: use Oracle Enterprise Manager's 'probe' event.
B. Look for any new alert log entries
• Connect to each managed system.
• Use 'telnet' or comparable program.
• For each managed instance, go to the background dump destination, usually $ORACLE_BASE//bdump. Make sure to look under each managed database's SID.
• At the prompt, use the Unix ‘tail’ command to see the alert_.log, or otherwise examine the most recent entries in the file.
• If any ORA-errors have appeared since the previous time you looked, note them in the Database Recovery Log and investigate each one. The recovery log is in .
C. Verify DBSNMP is running
1. Log on to each managed machine to check for the 'dbsnmp' process.
For Unix: at the command line, type ps –ef | grep dbsnmp. There should be two dbsnmp processes running. If not, restart DBSNMP. (Some sites have this disabled on purpose; if this is the case, remove this item from your list, or change it to "verify that DBSNMP is NOT running".)
D. Verify success of database backup
E. Verify success of database archiving to tape
F. Verify enough resources for acceptable performance
1. Verify free space in tablespaces.
For each instance, verify that enough free space exists in each tablespace to handle the day’s expected growth. As of , the minimum free space for : [ < tablespace > is < amount > ]. When incoming data is stable, and average daily growth can be calculated, then the minimum free space should be at least days’ data growth.
a) Go to each instance, run free.sql to check free mb in tablespaces.
Compare to the minimum free MB for that tablespace. Note any low-space conditions and correct.
b) Go to each instance, run space.sql to check percentage free in tablespaces.
Compare to the minimum percent free for that tablespace. Note any low-space conditions and correct.
2. Verify rollback segment.
Status should be ONLINE, not OFFLINE or FULL, except in some cases you may have a special rollback segment for large batch jobs whose normal status is OFFLINE.
a) Optional: each database may have a list of rollback segment names and their expected statuses.
b) For current status of each ONLINE or FULL rollback segment (by ID not by name), query on V$ROLLSTAT.
c) For storage parameters and names of ALL rollback segment, query on DBA_ROLLBACK_SEGS. That view’s STATUS field is less accurate than V$ROLLSTAT, however, as it lacks the PENDING OFFLINE and FULL statuses, showing these as OFFLINE and ONLINE respectively.
3. Identify bad growth projections.
Look for segments in the database that are running out of resources (e.g. extents) or growing at an excessive rate. The storage parameters of these segments may need to be adjusted. For example, if any object reached 200 as the number of current extents, AND it's an object that is supposed to get large, upgrade the max_extents to unlimited.
a) To gather daily sizing information, run analyze5pct.sql. If you are collecting nightly volumetrics, skip this step.
b) To check current extents, run nr_extents.sql
c) Query current table sizing information
d) Query current index sizing information
e) Query growth trends
4. Identify space-bound objects.
Space-bound objects’ next_extents are bigger than the largest extent that the tablespace can offer. Space-bound objects can harm database operation. If we get such object, first need to investigate the situation. Then we can use ALTER TABLESPACE COALESCE. Or add another datafile.
a) Run spacebound.sql. If all is well, zero rows will be returned.
5. Processes to review contention for CPU, memory, network or disk resources.
a) To check CPU utilization, go to x:\web\phase2\default.htm =>system metrics=>CPU utilization page. 400 is the maximum CPU utilization because there are 4 CPUs on phxdev and phxprd machine. We need to investigate if CPU utilization keeps above 350 for a while.

G. Copy Archived Logs to Standby Database and Roll Forward
If you have a Standby Database, copy the appropriate Archived Logs to the expected location on the standby machine and apply those logs (roll forward the changes) to the standby database. This keeps the standby database up-to-date.

The copying of logs, the applying of them, or both, can in some cases be automated. If you have automated them, then your daily task should be to confirm that this happened correctly each day.
H. Read DBA manuals for one hour
Nothing is more valuable in the long run than that the DBA be as widely experienced, and as widely read, as possible. Readings should include DBA manuals, trade journals, and possibly newsgroups or mailing lists.

II. Nightly Procedures
Most production databases (and many development and test databases) will benefit from having certain nightly batch processes run.
A. Collect volumetric data
This example collects table row counts. This can easily be extended to other objects such as indexes, and other data such as average row sizes.
1. Analyze Schemas and Collect Data.
The idea here is to use the more time consuming and more accurate ANALYZE COMPUTE command and save the results, which show up in the data dictionary, to a more permanent store.
a) If you havent' yet, create the volumetrics table with mk_volfact.sql
b) To gather nightly sizing information, run analyze_comp.sql.
c) To collect the resulting statistics, run pop_vol.sql
d) Examine the data at your leisure, probably weekly or monthly.
I use MS Excel and an ODBC connection to examine and graph data growth.

III. Weekly Procedures
A. Look for objects that break rules
For each object-creation policy (naming convention, storage parameters, etc.) have an automated check to verify that the policy is being followed.
1. Every object in a given tablespace should have the exact same size for NEXT_EXTENT, which should match the tablespace default for NEXT_EXTENT. As of 12/14/98, default NEXT_EXTENT for DATAHI is 1 gig (1048576 bytes), DATALO is 500 mb (524288 bytes), and INDEXES is 256 mb (262144 bytes).
a) To check settings for NEXT_EXTENT, run nextext.sql.
b) To check existing extents, run existext.sql

2. All tables should have unique primary keys.
a) To check missing PK, run no_pk.sql.
b) To check disabled PK, run disPK.sql.
c) All primary key indexes should be unique. Run nonuPK.sql to check.

3. All indexes should use INDEXES tablespace. Run mkrebuild_idx.sql.
4. Schemas should look identical between environments, especially test and production.
a) To check data type consistency, run datatype.sql.
b) To check other object consistency, run obj_coord.sql.
c) Better yet, use a tool like Quest Software's Schema Manager.
B. Look for security policy violations
C. Look in SQL*Net logs for errors, issues
1. Client side logs
2. Server side logs
D. Archive all Alert Logs to history

E. Visit home pages of key vendors
1. Oracle Corporation
http://www.oracle.com
http://technet.oracle.com
http://www.oracle.com/support
http://www.oramag.com
2. Quest Software
http://www.quests.com
3. Sun Microsystems
http://www.sun.com

IV. Monthly Procedures
A. Look for Harmful Growth Rates
1. Review changes in segment growth when compared to previous reports to identify segments with a harmful growth rate.
B. Review Tuning Opportunities
1. Review common Oracle tuning points such as cache hit ratio, latch contention, and other points dealing with memory management. Compare with past reports to identify harmful trends or determine impact of recent tuning adjustments.
C. Look for I/O Contention
1. Review database file activity. Compare to past output to identify trends that could lead to possible contention.
D. Review Fragmentation
1. Investigate fragmentation (e.g. row chaining, etc.).
E. Project Performance into the Future
1. Compare reports on CPU, memory, network, and disk utilization from both Oracle and the operating system to identify trends that could lead to contention for any one of these resources in the near future.
2. Compare performance trends to Service Level Agreement to see when the system will go out of bounds
F. Perform Tuning and Maintenance
1. Make the adjustments necessary to avoid contention for system resources. This may include scheduled down time or request for additional resources.

V. Appendix

A. Daily Procedures
1. Free.sql
--
-- free.sql
--
-- To verify free space in tablespaces
-- Minimum amount of free space
-- document your thresholds:
-- = m
--

SELECT tablespace_name, sum ( blocks ) as free_blk , trunc ( sum ( bytes ) / (1024*1024) ) as free_m
, max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks
FROM dba_free_space
GROUP BY tablespace_name

2. Space.sql
--
-- space.sql
--
-- To check free, pct_free, and allocated space within a tablespace
--
-- 11/24/98

SELECT tablespace_name, largest_free_chunk
, nr_free_chunks, sum_alloc_blocks, sum_free_blocks
, to_char(100*sum_free_blocks/sum_alloc_blocks, '09.99') || '%'
AS pct_free
FROM ( SELECT tablespace_name
, sum(blocks) AS sum_alloc_blocks
FROM dba_data_files
GROUP BY tablespace_name
)
, ( SELECT tablespace_name AS fs_ts_name
, max(blocks) AS largest_free_chunk
, count(blocks) AS nr_free_chunks
, sum(blocks) AS sum_free_blocks
FROM dba_free_space
GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name
3. analyze5pct.sql
--
-- analyze5pct.sql
--
-- To analyze tables and indexes quickly, using a 5% sample size
-- (do not use this script if you are performing the overnight
-- collection of volumetric data)
--
-- 11/30/98

BEGIN
dbms_utility.analyze_schema ( '&OWNER', 'ESTIMATE', NULL, 5 ) ;
END ;
/
4. nr_extents.sql
--
-- nr_extents.sql
--
-- To find out any object reaching
-- extents, and manually upgrade it to allow unlimited
-- max_extents (thus only objects we *expect* to be big
-- are allowed to become big)
--
-- 11/30/98

SELECT e.owner, e.segment_type , e.segment_name , count(*) as nr_extents , s.max_extents
, to_char ( sum ( e.bytes ) / ( 1024 * 1024 ) , '999,999.90') as MB
FROM dba_extents e , dba_segments s
WHERE e.segment_name = s.segment_name
GROUP BY e.owner, e.segment_type , e.segment_name , s.max_extents
HAVING count(*) > &THRESHOLD
OR ( ( s.max_extents - count(*) ) < &&THRESHOLD )
ORDER BY count(*) desc

5. spacebound.sql
--
-- spacebound.sql
--
-- To identify space-bound objects. If all is well, no rows are returned.
-- If any space-bound objects are found, look at value of NEXT extent
-- size to figure out what happened.
-- Then use coalesce (alter tablespace coalesce;).
-- Lastly, add another datafile to the tablespace if needed.
--
-- 11/30/98

SELECT a.table_name, a.next_extent, a.tablespace_name
FROM all_tables a,
( SELECT tablespace_name, max(bytes) as big_chunk
FROM dba_free_space
GROUP BY tablespace_name ) f
WHERE f.tablespace_name = a.tablespace_name
AND a.next_extent > f.big_chunk

B. Nightly Procedures

1. mk_volfact.sql
--
-- mk_volfact.sql (only run this once to set it up; do not run it nightly!)
--
-- -- Table UTL_VOL_FACTS

CREATE TABLE utl_vol_facts
(
table_name VARCHAR2(30),
num_rows NUMBER,
meas_dt DATE
)
TABLESPACE platab
STORAGE (
INITIAL 128k
NEXT 128k
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS unlimited
)
/

-- Public Synonym

CREATE PUBLIC SYNONYM utl_vol_facts FOR &OWNER..utl_vol_facts
/

-- Grants for UTL_VOL_FACTS

GRANT SELECT ON utl_vol_facts TO public
/

2. analyze_comp.sql
--
-- analyze_comp.sql
--
BEGIN
sys.dbms_utility.analyze_schema ( '&OWNER','COMPUTE');
END ;
/
3. pop_vol.sql
--
-- pop_vol.sql
--
insert into utl_vol_facts
select table_name
, NVL ( num_rows, 0) as num_rows
, trunc ( last_analyzed ) as meas_dt
from all_tables -- or just user_tables
where owner in ('&OWNER') -- or a comma-separated list of owners
/
commit
/

C. Weekly Procedures

1. nextext.sql
--
-- nextext.sql
--
-- To find tables that don't match the tablespace default for NEXT extent.
-- The implicit rule here is that every table in a given tablespace should
-- use the exact same value for NEXT, which should also be the tablespace's
-- default value for NEXT.
--
-- This tells us what the setting for NEXT is for these objects today.
--
-- 11/30/98

SELECT segment_name, segment_type, ds.next_extent as Actual_Next
, dt.tablespace_name, dt.next_extent as Default_Next
FROM dba_tablespaces dt, dba_segments ds
WHERE dt.tablespace_name = ds.tablespace_name
AND dt.next_extent !=ds.next_extent
AND ds.owner = UPPER ( '&OWNER' )
ORDER BY tablespace_name, segment_type, segment_name

2. existext.sql
--
-- existext.sql
--
-- To check existing extents
--
-- This tells us how many of each object's extents differ in size from
-- the tablespace's default size. If this report shows a lot of different
-- sized extents, your free space is likely to become fragmented. If so,
-- this tablespace is a candidate for reorganizing.
--
-- 12/15/98

SELECT segment_name, segment_type
, count(*) as nr_exts
, sum ( DECODE ( dx.bytes,dt.next_extent,0,1) ) as nr_illsized_exts
, dt.tablespace_name, dt.next_extent as dflt_ext_size
FROM dba_tablespaces dt, dba_extents dx
WHERE dt.tablespace_name = dx.tablespace_name
AND dx.owner = '&OWNER'
GROUP BY segment_name, segment_type, dt.tablespace_name, dt.next_extent

3. No_pk.sql
--
-- no_pk.sql
--
-- To find tables without PK constraint
--
-- 11/2/98

SELECT table_name
FROM all_tables
WHERE owner = '&OWNER'
MINUS
SELECT table_name
FROM all_constraints
WHERE owner = '&&OWNER'
AND constraint_type = 'P'

4. disPK.sql
--
-- disPK.sql
--
-- To find out which primary keys are disabled
--
-- 11/30/98

SELECT owner, constraint_name, table_name, status
FROM all_constraints
WHERE owner = '&OWNER' AND status = 'DISABLED’ AND constraint_type = 'P'

5. nonuPK.sql
--
-- nonuPK.sql
--
-- To find tables with nonunique PK indexes. Requires that PK names
-- follow a naming convention. An alternative query follows that
-- does not have this requirement, but runs more slowly.
--
-- 11/2/98

SELECT index_name, table_name, uniqueness
FROM all_indexes
WHERE index_name like '&PKNAME%'
AND owner = '&OWNER' AND uniqueness = 'NONUNIQUE'

SELECT c.constraint_name, i.tablespace_name, i.uniqueness
FROM all_constraints c , all_indexes i
WHERE c.owner = UPPER ( '&OWNER' ) AND i.uniqueness = 'NONUNIQUE'
AND c.constraint_type = 'P' AND i.index_name = c.constraint_name

6. mkrebuild_idx.sql
--
-- mkrebuild_idx.sql
--
-- Rebuild indexes to have correct storage parameters
--
-- 11/2/98

SELECT 'alter index ' || index_name || ' rebuild '
, 'tablespace INDEXES storage '
|| ' ( initial 256 K next 256 K pctincrease 0 ) ; '
FROM all_indexes
WHERE ( tablespace_name != 'INDEXES'
OR next_extent != ( 256 * 1024 )
)
AND owner = '&OWNER'
/

7. datatype.sql
--
-- datatype.sql
--
-- To check datatype consistency between two environments
--
-- 11/30/98

SELECT
table_name,
column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable
FROM all_tab_columns -- first environment
WHERE owner = '&OWNER'
MINUS
SELECT
table_name,
column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable
FROM all_tab_columns@&my_db_link -- second environment
WHERE owner = '&OWNER2'
order by table_name, column_name

8. obj_coord.sql
--
-- obj_coord.sql
--
-- To find out any difference in objects between two instances
--
-- 12/08/98

SELECT object_name, object_type
FROM user_objects
MINUS
SELECT object_name, object_type
FROM user_objects@&my_db_link

RDBMS

2008-06-04T07:13:00.000-07:00

Databases and Database Management Systems
Let's start from basics. What is a database? In very general terms, a database is a collection of related data. Notice the word related, this implies that the collection of letters on this page do not by themselves constitute a database. But if we think of them as a collection of letters arranged to form words, then they can be conceptualised as data in a database. Using similar reasoning, we can also say that a tome such as a telephone directory is also a database. It is a database first, because it is a collection of letters that form words and second, because it is an alphabetical listing of people's names, their addresses and their telephone numbers. How we think of a database depends on what use we want to make of the information that it contains.

So far, we have talked about a database in its broadest sense. This very general definition is not what most people mean when they talk about a database. In this electronic age, the word database has become synonymous with the term "computerised database". Collins English Dictionary describes a database as "A store of a large amount of information, esp. in a form that can be handled by a computer." In this book, we will be dealing only with computerised databases. In keeping with popular trend though, we will be using the word database to refer to a computerised database.

[Edit section] Storage Requirements
A database (computerised) by itself, is not much use. The data is stored electronically on the computer's disk in a format which we humans cannot read or understand directly. What we need is some way of accessing this data and converting it into a form which we do understand. This is the job of the database management system or DBMS for short.

[Edit section] Management Systems
A DBMS is essentially a suite of programs that act as the interface between the human operator and the data held in the database. Using the DBMS, it is possible to retrieve useful information, update or delete obsolete information and add new information to the database. As well as data entry and retrieval, the DBMS plays an important role in maintaining the overall integrity of the data in the database. The simplest example of is ensuring that the values entered into the database conform to the data types that are specified. For example, in the telephone book database, the DBMS might have to ensure that each phone number entered conforms to a set format of XXX-XXXXXXX where X represents an integer.

[Edit section] 2. The Database as a Collection of Tables
[Edit section] Relational Databases and SQL
In the early days of computerised databases, all large database systems conformed to either the network data model or the hierarchical data model. We will not be discussing the technical details of these models except to say that they are quite complex and not very flexible. One of the main drawbacks of these databases was that in order to retrieve information, the user had to have an idea of where in the database the data was stored. This meant that data processing and information retrieval was a technical job which was beyond the ability of the average office manager. In those days life was simple. data processing staff were expected to prepared the annual or monthly or weekly reports and managers were expected to formulate and implement day to day business strategy according to the information contained in the reports. Computer literate executives were rare and DP staff with business sense were even more rare. This was the state of affairs before the advent of relational databases.

[Edit section] History
The relational data model was introduced in 1970, E. F. Codd, a research fellow working for IBM, in his article `A Relational Model of Data for Large Shared Databanks'. The relational database model represented the database as a collection of tables which related to one another.

Unlike network and hierarchical databases, the relational database is quite intuitive to use, with data organised into tables, columns and rows. The table is a list of rows e.g. names and telephone numbers. It is similar to how we might go about the task of jotting down the phone numbers of some of our friends, in the back of our diary for example.

The relational data model consists of a number of intuitive concepts for storing any type of data in a database, along with a number of functions to manipulate the information.

[Edit section] Relational Data Modeling
The relational data model as proposed by Codd provided the basic concepts for a new database management system, the relational database management system (RDBMS). Soon after the relational model was defined, a number of relational database languages were developed and used for instructing the RDBMS. Structured Query Language being one of them.

[Edit section] SQL Language
The SQL language is so inextricably tied to relational database theory that it is impossible to discuss it without also discussing the relational data model. The next two sections briefly describe some of the concepts of this model.

Script to Generate Statspack report for a day

2008-06-04T07:00:00.000-07:00

– generateReportScript.sql

spool DayReports.sql

set heading off pages 9999

select ‘define begin_snap=’||snap_id||’;'||chr(10)||
‘define end_snap=’||to_number(snap_id + 1)||’;'||chr(10)||
‘define report_name=sp_’||to_char(snap_time,’ddmmhh24mi’)||’.txt’||chr(10)||
‘@?/rdbms/admin/spreport;’||chr(10)
from stats$snapshot
where trunc(snap_time) = trunc(sysdate -1)
order by snap_time asc;

spool off

I don’t have an understanding of analytic functions yet so the above script crudely just adds on 1 to the snap_id to get the end_snap for a report but if you’ve set up your site to take snapshots every 15mins then the script works fine producing the following script:

– DayReports.sql

define begin_snap=1621;
define end_snap=1622;
define report_name=sp_03060600.txt
@?/rdbms/admin/spreport;

etc…

which when run as the PERFSTAT user produces the required files

sp_03060600.txt
sp_03060615.txt
sp_03060630.txt
sp_03060645.txt
sp_03060700.txt
sp_03060715.txt
sp_03060730.txt
sp_03060745.txt
etc…

which can then be grepped as required!

dbms_xplan in 10g

2008-06-04T06:57:00.000-07:00

If you’re using 9i and haven’t learned about the dbms_xplan package, then you should take a good look at it right away. It’s (usually) a much better way of getting execution plans from your system than writing your own queries against the plan_table.

If you’ve been using dbms_xplan, and upgraded from 9i to 10g, make sure that you look at the new features - there are some things which are really useful, and this note is about just one of them.

dbms_xplan.display_cursor()

In 10g, Oracle instroduced a new function in dbms_xplan that can read the in-memory execution plan (v$sql_plan and associated structures). The call is the display_cursor call, and takes three optional parameters, the sql_id and child_number of the sql statement you want to see the plan for, and a formatting string.

The best bit of this function shows up when you look at the script that generates it($ORACLE_HOME/rdbms/admin/dbmsxpln.sql) when you decide to find out how to use the format parameter. Here’s a “live” demo:

set serveroutput off

select
/*+
gather_plan_statistics
ordered use_nl(t1) index(t1)
*/
count(t1.n2), count(t2.n2)
from
t2, t1
where
t2.n2 = 45
and t1.n1 = t2.n1
;

COUNT(T1.N2) COUNT(T2.N2)
------------ ------------
225 225

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 98cw5a9c0pw33, child number 0
-------------------------------------
select /*+ gather_plan_statistics ordered use_nl(t1) index(t1) */ count(t1.n2),
count(t2.n2) from t2, t1 where t2.n2 = 45 and t1.n1 = t2.n1

Plan hash value: 3795562434

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 146 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 15 | 225 |00:00:00.01 | 146 |
| 3 | NESTED LOOPS | | 1 | 225 | 241 |00:00:00.02 | 116 |
|* 4 | TABLE ACCESS FULL | T2 | 1 | 15 | 15 |00:00:00.01 | 99 |
|* 5 | INDEX RANGE SCAN | T_I1 | 15 | 15 | 225 |00:00:00.01 | 17 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T2"."N2"=45)
5 - access("T1"."N1"="T2"."N1")

If you want to call this function, you need access to several of the dynamic performance views -v$session, v$sql, v$sql_plan and v$sql_plan_statistics_all seem to cover all the options between them; and v$sql_plan_statistics_all is the most useful one.
As you can see in the query above, I’ve not supplied an sql_id or child_number, and I’ve used the format ‘ALLSTATS LAST’. By default display_cursor reports the last statement I executed, and the effect of ‘ALLSTATS LAST’ is to report the actual execution stats alongside the predicted row counts.

If you’ve ever got into Wolfgang Breitling’s ”Tuning by Cardinality Feedback” - you’ve now got a tool that can make it a lot easier.

A couple of odd notes - you’ll see that I set serveroutput off. If serveroutput is on when you call this function, the last statement you will have run will be the (hidden) call to dbms_output that follows your execution of any other statement - so you won’t get the plan and statistics.

The hint /*+ gather_plan_statistics */ enables collection of rowsource execution statistics for the statement. It’s a “nearly undocumented” hint, in that the documentation seems only to exist in odd places like the script that generates the package, and as part of the output of the function call if the statistics weren’t enabled when you try to report statistics.

You have to be a little careful comparing the actual and estimated row counts. They are not supposed to be the same in all cases. The estimated values are estimates for each execution of a rowsource, the actual values are the cumulative counts. So, for example, it is perfectly reasonable in line 5 to see E-rows = 15 and A-rows = 225, because line 5 starts 15 times: so 225 Actual rows = 15 starts * 15 estimated rows per start.

Oracle Capacity Planner

2008-06-04T03:37:00.000-07:00

Oracle Capacity Planner is an Oracle Enterprise Manager application that allows you to collect different types of performance statistics and record that data in an historical database. You can then use Oracle Capacity Planner to analyze the historical data to plan future capacity.

--------------------------------------------------------------------------------
Note:
In releases prior to 9i, the Agent data gathering service (also known as the Oracle Data Gatherer) was used to collect data for presentation in real-time performance monitoring utilities and historical data. The Intelligent Agent and the data gathering service were separate services that were started and stopped independently of each other.

With 9i, the data collection capabilities are integrated into the 9i Intelligent Agent. Therefore, there is no separate service that needs to be started. Furthermore, when you stop the 9i Intelligent Agent, the data gathering capabilities are disabled and not available.

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

Oracle Capacity Planner uses the Intelligent Agent (Agent) to collect its historical data. To collect most types of data, you must install the Agent on the system (or systems) where you want to collect data. See the Oracle Intelligent Agent User's Guide for information on managing the Agent.

If you are using Oracle Capacity Planner to collect concurrent manager data, the Agent can be installed on another system.

The types of performance data that Oracle Capacity Planner can collect on a system depend on the products that are installed. Table 4-1 shows the types of data that Oracle Capacity Planner can collect when various Oracle products are installed. The number of targets that appear in the Capacity Planner navigator tree will also vary, depending on the number of applications you have installed.

Table 4-1 Data That Capacity Planner Can Collect When Various Oracle Products are Installed
Oracle Product Oracle Concurrent Manager Data Oracle Database Data Node (Operating System) Data SAP R/3 System Data Microsoft SQL Server Data
Diagnostics Pack
No
Yes
Yes
No
Yes

Management Pack for Oracle Applications
Yes
No
Yes
No
No

Management Pack for SAP R/3
No
No
Yes
Yes
No

If additional products from Oracle are installed, Oracle Capacity Planner may be able to collect additional types of data not shown in Table 4-1.

How Capacity Planner Collects and Stores Performance Data
The following list identifies how Capacity Planner uses the Agent to collect performance data:

The Agent collects the data at an interval you specify from within Capacity Planner.

The Agent stores the data in an efficient binary form.

At a specified loader interval, the data is read from this binary file and stored in a readable form in the Oracle Capacity Planner historical database.

The Agent also aggregates the data by time, creating summary statistics for the data that is collected at different time intervals (hour, day, week, and so on).

Over time, the Agent purges the data in the historical database, keeping only as much data as you specify.

Purging the historical data allows you to have access to a current view of recent performance data for your environment and to manage the amount of stored data to avoid wasting disk space.

Accessing Historical Data Through an Intermediate Host
Oracle Performance Manager relies on the Agent to monitor a target (such as a node, a database, or a concurrent manager). Usually, the collection of the data is done on the system where the target is located. However, for some types of targets Oracle Performance Manager can also monitor data on a system by using the Agent on another system (an intermediate host).

This intermediate host could be the client system where the Oracle Performance Manager is running, or any other system on the network on which the Agent is available.

There are three ways to set the location of the Agent. The method you use depends on how you connect to the target. Consider the following scenarios:

You connect to a database that has been discovered using the Enterprise Manager console and you are running Oracle Capacity Planner while connected to the Oracle Management Server.

Capacity Planner attempts to connect to the Agent on the host where the target is located. If this connection fails or cannot be attempted because no preferred credentials have been set for the database, you will be prompted with a dialog box containing logon credentials for the database. You can use the Connection Details dialog box to specify the location of the Agent that will be used to collect performance data.

You click the Add Service button in the toolbar panel and manually add the target to the navigator.

Capacity Planner displays a Logon dialog box. This dialog box also contains a field to specify the location of the Agent that will be used to collect performance data.

You select a target in the Capacity Planner navigator tree and choose Set Connection Details from the File menu.

Capacity Planner displays the Database Logon dialog box for the selected target. This dialog box also contains a field to specify the location of the Agent that will be used to collect performance data.

Usage Scenarios for Oracle Capacity Planner
Oracle Capacity Planner can perform tasks such as the following:

Track the increase in the amount of used space on a disk over time and estimate when 95 percent of the space on the disk will be consumed

Track the increase in the total I/O rate for a host over time, then estimate what the total I/O rate for the host will be at some future point in time

Determine thresholds to set in Performance Manager by evaluating historical data gathered in Capacity Planner

Provide a predefined set of data that measures performance (Oracle Recommended Collection)

Publish historical data to a website where administrators and system administrators can view data that is periodically updated

Display a chart that shows the number of forms sessions per user and per application; estimate how the numbers will change over a designated period of time.

Starting Oracle Capacity Planner
You can start Oracle Capacity Planner in any of the following ways:

On the Oracle Enterprise Manager console, click the Application Management drawer, then click the Capacity Planner icon.

On the Oracle Enterprise Manager console, select a Concurrent Manager target, click the right mouse button, point at Related Tools in the context menu, then click Capacity Planner.

On the Tools menu of the Oracle Enterprise Manager console, point to Application Management, then click Oracle Capacity Planner.

From the UNIX command line, change directory to the ORACLE_HOME/bin directory (where ORACLE_HOME represents the home directory in which the Management Pack for Oracle Applications is installed), then type the following command:

./oemapp cp

From the Start menu, point to Programs, then to the Oracle Home where Oracle Enterprise Manager is installed, then to Oracle Applications, then click Capacity Planner, which displays the Capacity Planner Login dialog box.

Enter the appropriate connect information for your Oracle Enterprise Manager repository, then click OK.

Using Help
When you are using Oracle Capacity Planner, you can display context-sensitive help topics. You can also display conceptual topics and task topics.

To obtain context-sensitive help for an Oracle Capacity Planner panel, window, dialog box, or property page, select an item in the panel, window, dialog box, or property page, then on the Help menu, choose Window.

To obtain context-sensitive help for a class, chart, or data item on a property page, select the class, chart, or data item on the property page, then take one of the following actions:

Press F1.

Click the Help icon on the toolbar.

On the Help menu, choose Selected Item.

If a help topic is not available for a class, chart, or data item, a No Help Available message is displayed when you request help.

--------------------------------------------------------------------------------
Note:
Not all the charts described in the appendix are available from within Oracle Capacity Planner.

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

To view the Contents page for the Oracle Capacity Planner help, on the Help menu, choose Contents. On the Contents page, you can:

View a list of the conceptual help topics by double-clicking the Conceptual Topics book on the help system's Contents page.

Conceptual topics provide conceptual or overview information of which you should be aware before performing a particular Oracle Capacity Planner task. Conceptual topics do not contain step-by-step information for performing a task.

View a list of the task help topics by double-clicking the Task Topics book on the help system's Contents page.

Task topics provide step-by-step instructions for accomplishing an Oracle Capacity Planner task. They do not provide conceptual information that you should be aware of before performing the task.

You can access a glossary of Oracle Capacity Planner terms by double-clicking the Oracle Capacity Planner Glossary book on the Contents page.

Using Oracle Capacity Planner
You may find it helpful to think of using Oracle Capacity Planner as a two-step process:

Setting up the collection of historical data

See "Setting Up a Collection" for a detailed description of how to collect historical data.

Analyzing the collected data

See "Analyzing Collected Data" for a detailed description of how to analyze collected data.

Setting Up a Collection
To set up a historical data collection with Oracle Capacity Planner, you:

Select (and connect to) the targets for which you want to collect historical data.

Define the frequency of collection samples.

Define the location of the historical database and the frequency at which collected data should be loaded in the historical database.

Define the amount of data to be kept in the Oracle Capacity Planner database at each time interval.

Select the classes of data to collect, based on what is available in the Oracle Capacity Planner navigator.

Figure 4-1 shows the steps to follow when setting up a collection. For more information on each step, see the following sections.

Figure 4-1 Steps for Setting Up a Collection

Text description of the illustration nu-3695a.gif
Selecting Targets
You can select one or more targets from which you want to collect historical performance statistics. For example, when you have the Management Pack for Oracle Applications installed, the target types are typically Nodes and Concurrent Managers.

The procedure for selecting a target depends upon whether or not the server resides on a node that is known to the Enterprise Manager main console. A node is known to the console when it has been discovered with the Enterprise Manager console Discover Nodes command.

Selecting Targets on Discovered Nodes
To select a target on a node that has been discovered by the Enterprise Manager console:

Click the Collection tab of the Oracle Capacity Planner navigator panel, which displays the Oracle Capacity Planner collection view.

Double-click one of the target type folders.

The Oracle Capacity Planner navigator tree displays the targets of the selected type that are known to the Oracle Enterprise Manager main console.

Select the target from which you want to collect performance data and click the Connect button in the Capacity Planner toolbar, or click the plus sign (+) next to the target in the navigator.

When you connect to a target in the Oracle Capacity Planner navigator panel, you may be prompted for credentials if they are required to connect to that target. If a logon dialog box appears, click Help for information on the individual fields on the dialog box.

After you connect to a target, the available collection classes for that target appear in the navigator and Oracle Capacity Planner displays the Target property sheet for the selected target. For example, Figure 4-2 shows the classes that appear when you connect to a Windows NT node target.

Figure 4-2 Selecting a Target in the Oracle Capacity Planner Window

Text description of the illustration cp_win_serv_selected.gif
Selecting Targets on Undiscovered Nodes
If you double-click a target type and the target you are looking for does not appear in the target type folder, you can do one of the following:

Use the Enterprise Manager console to discover the node where the target resides.

This is the preferred method for adding a target to the Capacity Planner navigator panel. When you discover a node with the Enterprise Manager console, Capacity Planner saves key information about the target, such as the preferred credentials, location of the Agent, and historical database information.

Add the target manually to the Capacity Planner navigator panel.

When you add a target manually, the target is added for the current session of Capacity Planner only. You can start gathering data for the target, but the next time you start Capacity Planner, the target will not appear in the navigator panel. To modify collection settings or analyze data collected for the target, you will have to add the target again the next time you start Capacity Planner.

To add a target manually:

Select the target type for the target you want to add.

Click the Add Service button in the Capacity Planner toolbar.

Capacity Planner displays a message to remind you that targets you add manually will be available for the current session of Capacity Planner only.

Click Yes.

Capacity Planner displays a logon dialog box for the target. Click Help if you need information about any of the specific fields on the dialog box.

After you fill in the logon credentials, click OK to connect to the target.

After you connect to a target, the available collection classes for that target appear in the navigator and Oracle Capacity Planner displays the Target property sheet for the selected target (Figure 4-2).

About the Target Property Sheet
When you connect to a target, Oracle Capacity Planner displays the Target property sheet, which contains the following property pages:

Collection Options page

Storage Options page

Purge Options page

The collection, load, and purge settings you select on these pages apply to all the classes in the selected target. You cannot change these settings for a selected class; you can change them only for a selected target.

Defining the Frequency of Collection Samples
When you first connect to a target, Capacity Planner displays the Collection Options property page. The values you select on this page determine how often the Agent will collect information about the selected target and save it to disk. On the Collection Options property page, you can set the frequency of collection samples using either of these two methods:

By selecting the number of data samples to collect for a specified time interval

For example, to collect information 3 times every hour, select Samples per Time Interval, enter 3 in the text field, and choose Hours from the drop-down list.

Text description of the illustration cp_smpl_interval.gif
By specifying the length of time between collection samples

For example, to collect information every 20 minutes, select Time Between Samples, enter 20 in the text field, and choose Minutes from the drop-down list.

Text description of the illustration cp_time_betwn.gif

--------------------------------------------------------------------------------
Note:
To configure the settings for collecting data, the Agent that will collect data for a target must be running. Normally, the Agent is located on the host where the target is located.

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

Defining the Location of the Historical Database
After you set the frequency of collection samples, use the Storage Options tab to specify the location of the Oracle Capacity Planner historical database. This setting defines where Capacity Planner will save information collected for the selected target. When data is saved in the historical database, you can later analyze the data or use it to generate trend analyses.

By default, collected data is saved in your Oracle Enterprise Manager repository. However, you can specify an alternate database for the historical data if you do not want to store historical data for the selected target in the repository.

To save the data collected for a target in a location other than the Oracle Enterprise Manager repository:

From the Storage Options tab, select Store historical data in alternate location.

Enter the credentials for the database that will store the collection data for the target.

Enter the appropriate user name, password, and target for the Oracle Capacity Planner historical database. This connection information is used by the Agent when it loads the collected data at the specified loader interval. Therefore, the target specified for the Oracle Capacity Planner historical database must be known and accessible from the host where the Agent is running.

For example, if you specify MY_DB.WORLD as the historical database and your Agent is running on host MYNODE, then database MY_DB.WORLD must be reachable via Net8 or SQL*Net from host MYNODE (MY_DB.WORLD must exist in the TNSNAMES.ORA file for host MYNODE, for example).

Defining the Frequency of Loading Collection Data
After you define how often data will be collected and where the data will be stored, you can define how often the data will be loaded into the historical database.

You can determine how frequently the Oracle Capacity Planner loader program loads collection data for the target using either of these two methods:

Using the Per Time Interval field to specify the number of loader passes for a specified time interval

Using the Time Between field to specify the length of time between loader passes

--------------------------------------------------------------------------------
Note:
Although data can be collected at short time intervals (such as 5, 10, or 15 minutes), collected data is not available to you for analysis until it has been loaded into the Oracle Capacity Planner historical database. Therefore, the load interval you specify controls when the most recently collected data will be visible to you.

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

The collection frequency you set determines the lowest time interval for which Oracle Capacity Planner can analyze data. For example, if you want to be able to analyze data from hourly intervals, you need to set a collection frequency of at least once an hour.

After you define the frequency of loading collection data, you can set a time for the next loader pass by using the Next Transfer Occurs at field:

Click the value that you want to change in the date and time box.

Click the up or down arrow to select a higher or lower value.

Text description of the illustration cp_next_transfer_field.gif

If the current time is later than the time displayed for the Next Transfer Occurs at setting, click Refresh View to update the display to show the actual time of the next loader pass.

Note that the values that you select on the Storage Options property page apply to all classes of data collected on the selected target.

Understanding When the Loader Runs
The loader runs at the regular time interval specified on the Storage Options property page. The time specified in the Next Transfer Occurs at field controls when the loader runs. For example, if you want the loader to run once a day at 2:00 a.m., then set the transfer interval to once per day, and set the next transfer time to the next day at 2:00 a.m. The loader will run for the first time at 2:00 a.m. the next day, and then will run once a day, each day at 2:00 a.m.

You can use the Next Transfer Occurs at field to force the loader to run immediately--or as soon as possible. Simply set the next transfer time to a time earlier than the current time.

Understanding How Data Aggregation Works
As it loads data into the database, Oracle Capacity Planner automatically aggregates the data. During aggregation, Oracle Capacity Planner examines the collection samples for a given time interval, calculates an average value for that interval, then uses that average value for calculations at the next larger time interval.

On each loader pass, Oracle Capacity Planner examines the time stamps of all collected data to determine whether or not the threshold for a set interval (hour, day, week, month, or year) has been crossed since the previous loader pass. If so, Oracle Capacity Planner performs an aggregation of collected data from the next smaller interval to the interval threshold just passed. Table 4-2 shows the larger time interval to which data at each time interval is aggregated.

Table 4-2 Aggregation of Data from Smaller to Larger Time Intervals
Data at This Interval Aggregates to This Interval
Minutes
Hours

Hours
Days

Days
Weeks

Weeks
Months

Months
Years

An example may help clarify how Oracle Capacity Planner aggregates collection data. Suppose Oracle Capacity Planner is collecting a data sample every 10 minutes to capture the percentage of used space on a particular disk, and the loader runs once an hour. When the Oracle Capacity Planner loader runs and transfers data into the Oracle Capacity Planner database every hour, it examines the values for the percentage of used disk space in the samples collected during the previous hour, then it computes an average percentage for the samples. That average percentage is then used as the average value for the hour.

At the first loader pass after midnight, Oracle Capacity Planner uses the hourly averages since midnight the previous day to compute the day's percentage of used disk space.

At the first loader pass after midnight on Saturday, Oracle Capacity Planner examines the daily averages of used disk space since midnight the previous Saturday and computes an average percentage for the week.

At the first loader pass after midnight on the last calendar day of the month, Oracle Capacity Planner examines the weekly averages since midnight on the last calendar day of the previous month and computes an average percentage for the month.

Finally, at the first loader pass after midnight on December 31, Oracle Capacity Planner examines the last 12 monthly averages to compute a yearly average.

Defining the Amount of Data to Keep
The final step in setting up a target for historical data collection is defining the amount of data to keep.

When you select a target, you can click the Purge Options tab to display the Purge Options property page. On this page, you specify the amount of data you want to store in the Oracle Capacity Planner historical database for each type of data (for example, hour data, day data, and week data). You can accept the default Purge Options property page values or choose different values.

The values set on the Purge Options property page apply to all classes of data collected on the selected target.

The values you specify in the data retention table (the Enter the amount of data you want to keep table on the Purge Options page) determine how far back in time you can go to analyze data for the different types of data.

Suppose, for example, you always want to be able to view hour data for the previous six weeks. In this case, for the Hours Data Type row in the data retention table, you would enter 6 in the Timespan column and Weeks in the To Keep column. This tells Oracle Capacity Planner that you want to keep hour data for a 6-week period. Oracle Capacity Planner calculates 6 weeks from the current time, and the Purge Data Older Than column shows the time span for which hour data is available. Table 4-3 shows the Hours row in the data retention table with this data entered.

Table 4-3 Specifying How Much Data to Keep for a Data Type
Data Type Timespan To Keep Purge Data Older Than
Hours
6
Weeks
(current date minus 6 weeks)

For each row in the data retention table, you can choose the time span to keep data samples for that type of data by entering the desired value in the Timespan to Keep column.

Applying and Reverting the Collection Settings for a Target
When you finish setting all the options on the Target property sheet, do the following to apply your collection settings:

Click Apply.

If you changed the location of the historical database on the Storage Options page, Capacity Planner displays a message box reminding you that any information stored in the previous location will not be available for analysis.

If you don't want to be reminded about this implication the next time you change the database location, select Don't show this message again.

Click OK.

Capacity Planner displays a message stating that the options were set successfully.

Click Continue to close the message box and return to the Capacity Planner main window.

When you apply your collection settings, Capacity Planner sends the settings to the Agent for the selected target. Note that you cannot select data to be collected until these settings have been applied.

At any time during the process of modifying the settings on the Target property sheet, you may revert the settings to their last saved (applied) state by clicking Revert. However, after you apply the settings, they cannot be reverted to their previous settings using the Revert button; they must be modified manually.

Selecting the Classes of Data to Collect
After you set the collection and loading options for a target, you can then select the classes of performance data that you want to collect for that target:

In collection view, select and connect to the target.

For more information, see "Selecting Targets" .

Click the plus sign (+) next to the target to show the classes of data you can collect.

Select one of the classes for the target.

Figure 4-3 shows a typical collection class for a node target on Windows NT and the data sources and data items available for the class.

Figure 4-3 Selecting Data Sources and Data Items for a Collection Class

Text description of the illustration cp_selected_class.gif
The following list explains classes, data items, and data sources in more detail:

Classes

A class is a container for a particular category of data. For example, a node (or operating system) target might include the following classes:

System

Processor

Memory

When you select a target in the navigator, Capacity Planner displays the names of the classes below the target in the tree view.

You can collect data from none, some, or all displayed classes for a target. A class may also be a container for other classes, to provide logical groupings of the performance data at higher levels. For example, an I/O class may be a container for other more specific I/O classes.

Data items

In a class, each of the related statistics is referred to as a data item. For example, a LogicalDisk class might include the following data items:

Percent Free Space

Free Megabytes

Current Disk Queue Length

Disk Writes per Second

A class can contain one or more data items. When you select a class in the navigator, the names of the data items and data sources associated with the data items are displayed on the Collection Details property page. Select the data items that you are interested in collecting.

A green check mark appears next to the name of each data item that is selected for collection. You can toggle the state of all data items between collected and not collected by clicking the Data Items column header that contains the check mark.

Data sources

A data source is a description of where Oracle Capacity Planner can collect the data items for a class. A class can contain one or more data sources. When you select a class in the navigator, the names of the data items and data sources associated with the class are displayed on the Collection Details property page.

The type of data in the class determines the data sources for the class. For example, in a LogicalDisk class, the data sources might be the individual disks for the operating system (for example, disk1, disk2, and so on). In a CPU Utilization class, the sources are the individual CPUs for the host (for example, cpu1, cpu2, and so on). You can collect data items from one or more data sources.

When you select a data source, a green check mark appears next to the name of the data source on the Collection Details property page. You can toggle the state of all data sources between collected and not collected by clicking the Data Sources column header that contains the check mark.

Starting Data Collection
After you select the data items that you want to collect from the available data sources on the Collection Details property page, click Apply to confirm your selections, or click Revert to cancel the selections.

When you click Apply for a class of data, Capacity Planner marks the icon for the selected class and the selected target with a green dot in the Collection view of the navigator panel. A green dot on a collection class icon indicates that one or more data sources are selected for the class. A green dot on a target icon indicates that Capacity Planner is collecting data for one or more classes in the target.

For example, in Figure 4-4, Capacity Planner is actively collecting data for the Processor, Memory, and Cache classes for node ANG-PC. Capacity Planner is not collecting data for the System collection class.

Figure 4-4 Green Dots on a Target or Class Indicate Capacity Planner is Collecting Data

Text description of the illustration cp_green_dots.gif
Stopping Data Collection
To stop collecting data for one or more data items or data sources for a collection class, follow these steps:

Select the collection class in the navigator.

On the Collection Details page, click on the green check marks for those data items or data sources from which you no longer want data collected, which causes the check marks to disappear.

Click Apply.

To stop collecting data for all data items and data sources for a collection class, follow these steps:

Select the collection class in the navigator.

On the Collection Details page, click Stop Collection.

Capacity Planner displays a message box stating that collections for the class have stopped.

Click OK to close the message box and return to the Capacity Planner main window.

When no data sources are selected for collection for a class of data, the icon for that class in the navigator does not display a green dot.

To stop collecting data for all classes in a target:

In collection view, select and connect to the target.

For more information, see "Selecting Targets" .

Click Stop Collection on the Target property sheet.

Capacity Planner displays a confirmation box.

Click Yes to stop collecting data for all classes in the target; click No to cancel the operation.

Generating a Collection Report
A collection report provides a summary of the collection settings for one or more targets. Capacity Planner displays the collection settings in your Web browser. In the browser, you can easily review the settings currently applied to the target or targets you selected.

To display a collection report for one or more targets:

In collection view, on the File menu, choose Collection Report.

Capacity Planner displays the first screen of the Collection Report Wizard.

Use the Collection Report Wizard to specify the target or targets whose collection settings you want to include in the report and to generate and view the report.

At any time while using the wizard, click Help for more information.

When you click Finish on the last screen of the Collection Report Wizard, Capacity Planner saves the report to an HTML file and displays the report in your Web browser.

Copying Collection Settings
After you set up collection settings for one service, you can quickly copy those settings to another similar target.

To copy the collections settings from one target to another:

In collection view, select a target whose collection settings you want to copy.

On the File menu, choose Copy Collection Settings or right mouse click on the Collection Settings from the context menu.

Capacity Planner displays the first screen of the Copy Collections Wizard.

Use the Copy Collection Settings Wizard to specify the destination targets to which you would like to copy the collection settings for the selected target.

From the wizard, you can also view the progress of the copy operation. At any time while you are using the Copy Collection Settings Wizard, click Help for more information.

--------------------------------------------------------------------------------
Note:
Any destination target you select must be running an Agent of the same type and version as the target from which you are copying your settings.

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

Troubleshooting Collection and Load Problems
During the collection and load processes, errors may be encountered by the Agent. You can determine if data is not being collected or loaded by using either of these methods:

Browsing the analysis data in the Oracle Capacity Planner navigator. See "Browsing the Collected Data" for more information.

Accessing the historical database using SQL. See "Understanding the Oracle Capacity Planner Historical Database" for more information.

If data is not being collected or loaded, you should check the status of the Agent on the host from which data is being collected. If the Agent is running, check the log file ($ORACLE_HOME/network/log/dbsnmp.log) for errors related to the collection or loading of data. See the Oracle Intelligent Agent User's Guide for additional details on the configuration of the Agent.

Analyzing Collected Data
After collected data is loaded into the Oracle Capacity Planner historical database, you can perform a variety of analyses on it. To access the analysis features of Oracle Capacity Planner, click the Analysis tab (Figure 4-5) at the bottom of the Oracle Capacity Planner navigator tree view. This displays the Oracle Capacity Planner analysis view.

Figure 4-5 Clicking the Analysis Tab

Text description of the illustration cp_analysis_tab.gif
Connecting to an Oracle Capacity Planner Historical Database
When you expand the Historical Database folder at the root of the navigator tree in analysis view, you are prompted for the logon credentials for an Oracle Capacity Planner historical database.

Text description of the illustration cp_root_analysis.gif
After you connect to a historical database, the folder expands to show the targets for which data is available in that historical database.

Text description of the illustration cp_apps_analysis.gif
If you store data in multiple historical databases, you must disconnect from one historical database before you can connect to a different historical database.

To disconnect from the current historical database,

Click the Historical Database folder at the root of the navigator tree.

Click the Disconnect button in the Capacity Planner toolbar panel.

To connect to a different historical database, expand the Historical Database folder again and enter the credentials for the new historical database in the resulting logon dialog box.

Organization of Data in the Navigator
When you expand a target in the navigator under the Historical Database folder, two forms of data are displayed below the target:

Classes of data

The classes displayed for the target are the collection classes for which data is available in the Oracle Capacity Planner historical database. You can select and view data from these classes by selecting a class in the navigator, specifying the selection criteria, and then clicking Show New Chart. These operations are the first step in creating an analysis.

Analyses

Below each target in the navigator is an Analyses folder, which contains predefined and user-defined analyses. Predefined analyses are provided for some targets. You can create user-defined analyses by selecting a class of data, displaying an analysis of the data, and then saving the analysis. You can also create a user-defined analysis by using an existing analysis as a template, making modifications to the analysis, and then saving the analysis under a new name.

Browsing the Collected Data
To browse the collected data for a particular target:

In analysis view, expand the Historical Database folder.

You are prompted for the credentials for the historical database that contains the collected data that you want to analyze.

Enter your logon credentials and connect to the historical database.

On the navigator panel, expand the folder for the target whose collected data you want to analyze.

The navigator displays the:

Names of the classes for which collected data exists in the historical database for the target

An Analyses folder, which contains predefined and user-defined analyses you created for the target

Viewing an Analysis for a Class of Data
To view an analysis chart for a selected class of data:

On the navigator panel, click the name of a class whose collected data you want to analyze.

Capacity Planner displays the property page for the class (Figure 4-6).

To specify the selection criteria for the class of data you want to analyze, see "Specifying Selection Criteria for a Class of Data" for instructions.

Click Show New Chart.

Capacity Planner displays the Analysis window, which contains two or more pages. The Chart page displays the chart. The remaining pages are Details pages. A separate Details page exists for each class from which one or more data items are included in the analysis.

For information about the Analysis window, choose Window from the Analysis window Help menu. For information about modifying the analysis or changing the way the data is displayed, see "Working with Analyses" .

Saving a Class Analysis as a User-Defined Analysis
After you view an analysis for a class of data, you can save it as a user-defined analysis to view later.

To save the analysis:

While viewing the analysis chart, click Save Chart on the analysis window toolbar.

Click Yes in the message box.

Performance Manager places the new user-defined analysis in the Analyses folder of the navigator panel.

Figure 4-6 Oracle Capacity Planner Analysis View

Text description of the illustration cp_analysis_view.gif
Specifying Selection Criteria for a Class of Data
When you select a class in the navigator panel in analysis view, Capacity Planner displays a property sheet that contains options available for selecting data from the Oracle Capacity Planner historical database for that class.

Before you display a chart of the collected data you must:

Select the time aggregation level from the Time Aggregation drop-down list.

For example, select Hours if you want to analyze hourly data, Days if you want to analyze daily data, and so on.

In the Data Sources section, select one or more data sources whose data items you want to analyze.

In the Data Items section, select one or more data items you want to analyze.

A check mark appears next to each data item that is selected for analysis.

After you specify the selection criteria, there are two ways to display the data in an analysis chart:

To add the selected data to a new analysis chart, click Show New Chart, which displays the new chart.

To add the selected data to an analysis chart that is already displayed in the Analysis window, click Add to Chart, then in the Add Data to Analysis dialog box, select the name of the previously-displayed analysis chart. Note that only charts that are currently opened are displayed in the Add Data to Analysis dialog box.

Viewing and Modifying User-Defined and Predefined Analyses
You can view and modify both user-defined and predefined analyses. However, once modifications are made to a predefined analysis, the only way to save those changes is to save the analysis to a new name. This creates a new user-defined analysis and preserves the original predefined analysis.

--------------------------------------------------------------------------------
Note:
Not all targets provide predefined analyses.

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

To view an existing analysis, which makes it available for modification, follow these steps:

Expand the Analysis folder for the target, which displays the existing predefined and user-defined analyses for the target.

A bar chart icon identifies a predefined analysis. Not all targets provide predefined analyses.

A bar chart and person icon identifies a user-defined analysis. You create a user-defined analysis by displaying an analysis for a class of data or displaying a predefined analysis, and then saving the analysis.

--------------------------------------------------------------------------------
Note:
You can limit the list of displayed analyses to only user-defined analyses by removing the check mark from the View Predefined Analyses option on the Analysis menu. When you choose the View Predefined Analyses option on the Analysis menu again, the check mark preceding the menu option is replaced, and predefined analyses are displayed again.

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

Select a predefined analysis or user-defined analysis in the navigator panel.

The Analysis Summary property page for the selected analysis is displayed in the detail panel. This property page displays the names of the targets and classes whose data is used in the analysis.

To view the data sources and data items in the analysis, click the plus sign (+) next to the target icons in the Service/Source column.

To view the chart for the analysis, click Show.

Note that if you are opening a predefined analysis, Capacity Planner displays the Select Data Sources for Analysis dialog box. Predefined analyses are provided for some targets. Predefined analyses require you to specify the available data sources for your installation. Select the data sources you want to include in the predefined analysis and click OK.

After you have opened an analysis, you can modify the contents and how they are presented. See "Working with Analyses" for more information.

You can also create a copy of an analysis, rename an analysis, or delete an analysis, as explained in the next three sections.

Creating a Copy of an Analysis
You can create an analysis that is an exact copy of an existing analysis, but the copy has a different name. To do this:

In analysis view, locate and expand the target that contains the analysis you want to copy.

Expand the Analyses folder.

Select the analysis you want to copy.

On the Analysis menu, choose Create Like.

In the Create Analysis Like dialog box, enter a unique name for the new analysis that you are creating and click OK.

Capacity Planner creates the new copy of the analysis in the Analyses folder for the target.

Renaming a User-Defined Analysis

--------------------------------------------------------------------------------
Note:
You cannot rename a predefined analysis. This restriction ensures that the original predefined analysis charts are not overwritten.

However, after you make changes to a predefined analysis, you can save the modified version of the predefined analysis as a user-defined chart with a new name. For more information, see "Saving the Results of a Predefined Analysis".

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

To rename a user-defined chart:

In analysis view, locate and expand the target that contains the analysis you want to rename.

Expand the Analyses folder.

Select the user-defined analysis you want to rename.

A bar-chart-and-person icon identifies user-defined analyses.

On the Analysis menu, choose Rename.

In the Rename Analysis dialog box, enter a unique name for the analysis you are renaming and click OK.

Deleting a User-Defined Analysis

--------------------------------------------------------------------------------
Note:
You cannot delete a predefined analysis.

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

To delete a user-defined analysis:

In analysis view, locate and expand the target that contains the analysis you want to delete.

Expand the Analyses folder.

A bar-chart-and-person icon identifies the user-defined analyses in the folder.

Select the user-defined analysis that you want to delete.

On the Analysis menu, choose Delete.

In the message dialog box that prompts you to confirm the deletion, click OK.

Working with Analyses
After you have opened an analysis chart, either by selecting and showing an existing analysis or by browsing collected data to create a new analysis, the data included in that analysis is shown in the Oracle Capacity Planner Analysis window.

The Analysis window contains two or more pages. The first page is called the Chart page and it displays the analysis chart. The remaining page or pages are Details pages. A separate Details page exists for each class from which one or more data items are included in the analysis. Each Details page shows the:

Data sources for the data items plotted in the chart

Time aggregation level of the plotted data items

Value of each plotted data item each time the data item was collected during the time range for the analysis

The time range for the collected data appears along the bottom of each chart, with the beginning of the time range at the left and the end of the time range at the right.

Two types of lines can appear on a chart. Each solid line plots the values of a selected data item during the specified time range. Each dashed line plots the result of a trend analysis to time or trend analysis to value. See "What Is a Trend Analysis?" for more information about performing a trend analysis.

The rest of this section describes other features of the Analysis window.

Viewing the Value of a Data Point
To view the value for a data point (that is, one plotted point of a data item) on a line in an analysis chart, position the cursor over the data point. If there is a point beneath the cursor, Capacity Planner displays the date, time, and value for that data point displays in a box above the point (see Figure 4-7).

Figure 4-7 Viewing the Value of a Data Point

Text description of the illustration cp_cursor_over_data_point.gif
Selecting a Line
When you view an analysis chart, you can perform certain operations after selecting a line in the chart. Most of the available operations are started by clicking a toolbar button on the analysis window after a chart line is selected.

To select a line, click a data point on the line. When a line is selected, the description of the line is displayed in the status bar at the bottom of the window, and the appropriate toolbar buttons are enabled.

Operations available from the toolbar are also available on a context menu. To display the context menu, click the right mouse button on a data point in a line in the analysis chart.

To be sure which line is currently selected (before clicking a toolbar button), read the description of the line in the status bar.

Adding a Class of Data to an Analysis
After you open an analysis, you may want to add data from other classes to the analysis. You can use this feature to compare two different classes of data.

To add a class of data to an analysis that is currently displayed in the Analysis window:

Confirm that Capacity Planner is in analysis view.

Select from the navigator the class of data that you want to add.

Specify the selection criteria (as described in "Specifying Selection Criteria for a Class of Data") for the class.

Click Add to Chart.

In the Add Data to Analysis dialog box, select the name of the chart to which you want to add the data.

You can add data items from multiple classes to an analysis, and you can select different aggregation levels and date ranges for each class.

However, data items within a single class must share the same aggregation level and date range. If you try to add additional data items from the same class to an existing chart, you will receive a prompt, warning that the chart already contains data from this class. If you click OK, Capacity Planner will overwrite the chart with the currently selected data, aggregation levels, and date range.

Modifying Selection Criteria from the Analysis Window
From the analysis window, you can modify the selection criteria associated with any of the classes of data included in the analysis. Modify the selection criteria using one of these methods:

Select a line in the analysis chart and click the Select Items toolbar button.

Click the right mouse button on a data point in the line and choose Select Items from the context menu.

Click the Data tab in the analysis window (as opposed to the Chart tab) and click the Select Items toolbar button.

These actions display the Select Items dialog box, which contains the property sheet for the class of data associated with the selected line. This property sheet contains the selection criteria for that class of data, which you can modify and apply to the current analysis. See "Specifying Selection Criteria for a Class of Data" for more information.

After you modify selection criteria, you can click either Apply or OK on the Analysis Options dialog box. If you click Apply, the changes made to the selection criteria are applied to the analysis and the updated analysis is shown. The Select Items dialog box remains displayed, and you can make other modifications to the same selection criteria, if you want. If you click OK, Capacity Planner applies the modifications to the analysis and closes the Select Items dialog box.

Removing a Line from a Chart
You can remove a line from an analysis chart by either of these methods:

Select the line by clicking the right mouse button, then choose Remove Line from the context menu.

Select the line, then click the Remove Line button on the analysis window toolbar.

These actions remove the data for the selected data item from the chart, but not from the historical database.

Excluding a Data Point from a Chart
You can exclude a data point (that is, one plotted point of a data item) from a chart. To exclude a data point, click the right mouse button on the data point and choose Exclude Point from the context menu.

Excluding a data point is useful if you know that the value of a particular data point is skewed. For example, if you know that a particular CPU-intensive program was not run at the usual time, you can remove the value for the % Processor Time data item for that time from the chart. The selected data point is removed from the chart, but the data for the data point is not removed from the historical database.

After you exclude one or more data points from a line, a red X appears in the status bar when you select the line.

To later redisplay excluded data points for a selected line, click View Exclusions on the Analysis window toolbar, or click the right mouse button on the line and select View Exclusions from the context menu.

These actions display the Data Point Exclusions dialog box, which displays the current list of excluded points for the selected line. You can toggle the check mark for each data point in the list. A check mark indicates that the data point is excluded from the analysis. To display an excluded data point in the analysis again, clear the check mark.

When you click OK, Capacity Planner closes the Data Point Exclusions dialog box and updates the chart to include the points removed from the list of exclusions.

--------------------------------------------------------------------------------
Note:
Data point exclusions are not saved with an analysis.

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

Showing or Hiding the Chart Legend

You can control whether or not the legend is displayed in an analysis chart by toggling the Show/Hide Legend button on the analysis window toolbar. When the legend is not shown, you can identify a line by clicking it, which displays the description of the line in the analysis window status bar.

Zooming and Scrolling to View More or Less Chart Data
Depending on the date range and the number of data items and data sources you are analyzing, you may want to zoom in, zoom out, or scroll the contents of the chart window.

To view more detail or less detail, click the Zoom In and Zoom Out buttons on the Analysis Chart window toolbar. You can also zoom in by pressing the Page Down key on your keyboard or the numeric 3 on your keypad, and you can zoom out by pressing the Page Up key or the numeric 9 on your keypad.

You can also zoom in on a very specific part of the chart by dragging a rectangle around the area you want to magnify (Figure 4-8). Release the mouse button to zoom in on the selected area. You can zoom back out by using the Zoom Out button Home or End key on your keyboard.

Figure 4-8 Zooming in on a Selected Area of the Chart

Text description of the illustration cp_zoom_rect.gif
Often, the chart data will extend beyond the width of the Analysis Chart window. You can view the data that extends beyond the edges of the window by resizing the Analysis Chart window or by scrolling to the right or left.

To scroll the Analysis chart window:

Press and drag the pointer on the black scroll bar below the chart (Figure 4-9).

When the portion of the chart you want to view is displayed in the Analysis Chart window, release the mouse button.

Optionally, you can use the arrow keys on your keyboard to scroll left and right. Press the left arrow to scroll left and the right arrow to scroll right. If you experience a conflict with the Tab function using the left and right arrow keys, you can use the up and down arrow keys on your keyboard to scroll. Press the up arrow to scroll left (or the numeric 8 on your keypad), or the down arrow key to scroll right (or the numeric 2 on your keypad).

Figure 4-9 Scroll Bar in the Analysis Chart Window

Text description of the illustration cp_scroll_chart.gif
Auto-Scaling Data
If an analysis contains data from different classes where the scale of the data is significantly different, you may want to apply scaling to the data. Scaling normalizes the data so that all lines have a maximum value of no greater than 100. Very large data items are scaled down, and very small data items are scaled up.

To apply scaling, click Toggle Auto Scaling on the analysis window toolbar. When scaling is applied to a chart, data lines with very different scales may be visually correlated. When scaling is enabled, Capacity Planner displays the label Scaled Data along the Y-axis of the chart. If you select a line from the chart by clicking it, the scaling factor for that line is included in the status bar.

What Is a Trend Analysis?
A trend analysis infers or estimates future values by projecting and extending known values. Oracle Capacity Planner can perform the following types of trend analyses:

Trend analysis to a point in time

In a trend analysis to a point in time, you provide a goal time for a data item. Oracle Capacity Planner uses the specified values in the historical database to project the value of the data item at the goal time.

A trend analysis to a point in time is used to answer questions such as, "How full will this disk be at the end of the year?"

Trend analysis to a value

In a trend analysis to a value, you provide a goal value for a data item. Oracle Capacity Planner uses the specified values in the historical database to project the time at which the data item will reach the goal value.

A trend analysis to a value is used to answer questions such as, "When will our CPU utilization reach 95 percent?"

As the default, the Capacity Planner Trend Analysis wizard uses the critical threshold for the metric (if one is defined) when you perform a trend to a value.

Specifying and Performing a Trend Analysis
To specify and perform a trend analysis, follow these steps:

In the Analysis window, either:

Select a line by clicking a data point in the line, then click the Trend Analysis Wizard button on the toolbar.

Click the right mouse button on a data point in a line, then select Trend Analysis Wizard from the context menu.

Both of these methods display the Trend Analysis Wizard.

Capacity Planner displays the Trend Analysis Wizard Welcome panel that identifies the selected data on which you can conduct the trend analysis. You can click on the option box that prevents the Welcome screen from displaying in subsequent uses of the wizard. Click on Next to move to the next wizard panel.

In the Date Range panel, select a date range to use as a basis for the trend analysis:

Click Use only the visible date range (as currently zoomed in the chart) to use the range of dates currently visible in the analysis chart window (not including any data that extends beyond the edge of the chart window).

This option is enabled only if you zoomed in to see more detail in the chart. For more information, see "Zooming and Scrolling to View More or Less Chart Data" .

Click Use the date range selected for the chart to use the date range currently used for all the data in the chart (even data that extends beyond the edge of the chart window).

Click Select a new chart range to select a different date range . You can designate either a recent period of time by choosing Use a recent period of time and then selecting the time increment and number of units of time to use, or you can specify a new start and end time by choosing Use a new start and end time.

Click on Next to move to the next wizard panel.

In the Trend Analysis Type panel of the wizard, specify which type of trend analysis you want to perform.

To perform a trend analysis to a value, click the Estimate when the data item will reach the following value: and enter the numeric value for which you wish to determine the date and time.

To perform a trend analysis to a future date and time, click Estimate what the value of the data item will be at the following date: and enter the time and date for which you wish to determine a value.

Click on Next to move to the next wizard panel.

The Summary panel displays the results of the trend analysis. Click on Finish to add the trend line to the chart.

Choose Customize Annotation from the View menu to display the Annotate Trend dialog box and make changes to the annotation. Optionally, you can right mouse button click on the annotation created by the Trend Analysis wizard and then choose Customize Annotation from the context menu.

Use the Annotate Trend dialog box to specify whether Capacity Planner will annotate the trend analysis (see Figure 4-10) and the appearance of the trend analysis line in the chart window. If you need information about the fields in the Annotate Trend dialog box, click Help.

Figure 4-10 Annotation for a Trend Analysis

Text description of the illustration cp_trent_annotation.gif
Click OK to close the Annotate Trend dialog box.

--------------------------------------------------------------------------------
Note:
To move the annotation box in an analysis chart, click the box, hold the mouse button down, then move the mouse to a different position on the chart. Release the mouse button when you have reached the position in the chart where you want the annotation box displayed.

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

Saving Changes to a User-Defined or Predefined Analysis
You can save the changes you make to an analysis. Oracle Capacity Planner can determine if you are saving a user-defined analysis or a predefined analysis. The steps for saving your changes differ, depending on the type of analysis.

Saving the Results of a User-Defined Analysis
To save changes to a user-defined analysis:

While viewing the analysis, click Save Chart on the analysis window toolbar.

Click Yes in the message box.

Your changes are saved to in the Analysis folder for the selected target using the current name of the analysis or data class. To save the analysis under a different name, choose Save As from the Analysis window File menu.

Saving the Results of a Predefined Analysis
If you modify a predefined analysis, you must provide a new name for the analysis when you save the results of the analysis. When you do this, you create a new user-defined analysis. Saving the analysis with a new name ensures that the original predefined analysis is not overwritten.

To save the results of a predefined analysis:

While viewing the analysis, click Save Chart on the analysis window toolbar.

Click Yes in the dialog box that is displayed.

In the Save Analysis As dialog box, type a unique name for the user-defined analysis that will be created.

Click OK in the Save Analysis As dialog box.

Capacity Planner places the new user-defined chart in the Analyses folder of the navigator panel.

Printing an Analysis Chart

To print an analysis chart, click the Print Chart button in the Analysis Chart toolbar while the chart is displayed.

The size of the chart on the screen determines the size of the chart in the chart printout. For example, when you print a chart that is 5 inches high and 7 inches wide on your screen, the chart will be 5 inches high and 7 inches wide in the printout.

Generating a Report for an Analysis Chart

To generate an HTML report for an analysis chart:

Click the Report Chart button while the chart is displayed.

Capacity Planner displays a dialog box that tells you the name of the HTML file used to display the report and where the file will be saved.

Make a note of the file name and location.

Click Yes to preview the chart in your Web browser, or click No if you do not want to preview the report.

You can view the report later using a Web browser, or--if you have experience with Web publishing tools and techniques--you can share the report with your co-workers by publishing the HTML file and its associated graphic files on a Web server.

Understanding the Oracle Capacity Planner Historical Database
The logical structure of the Oracle Capacity Planner historical database schema was designed to facilitate querying of capacity planning data by tools other than Oracle Capacity Planner, such as Microsoft Excel. This section contains a brief description of the design of the Oracle Capacity Planner historical database schema. It is not intended to be a comprehensive description of all tables and columns in the schema.

--------------------------------------------------------------------------------
Caution:
Performing any modification to the Oracle Capacity Planner historical database schema (tables, columns, or indexes) or to the data itself is not recommended and is not supported. Such modifications may render your Oracle Capacity Planner historical database schema unusable as a target to load or retrieve capacity planning data.

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

Understanding the Service Reference Table (vp_service_ref)
The service reference table contains a list of all targets for which data has been stored in the Oracle Capacity Planner historical database. The HOST column contains the name of the host where the Agent that loaded data for the specified target (the NAME column) is located. The SVC_ID column is a column used for lookup into the class reference table, described in the following paragraph.

Understanding the Class Reference Table (vp_class_ref)
The class reference table contains a list of all collection classes for which data has been stored in the Oracle Capacity Planner historical database, for all targets. To see the list of classes for a particular target, a query should look for the SVC_ID column equal to a SVC_ID value from the service reference table, described in the previous paragraph. Such a query would produce a list of all classes for which data exists in the database for a particular target.

Understanding the Data Tables
Each class of data in the class reference table has six data tables that contain the data for that collection class. Each table contains data for a particular time aggregate, for example, hours, days, weeks, months, and years. The base table contains the raw data as it is collected at the collection frequency. Therefore, the base table does not contain any statistical aggregations. The aggregate tables contain aggregate statistics for each data item collected for that class.

Data Table Names
Data tables are named according to the first four columns of the class reference table (in reverse order) plus the aggregate. Example 4-1 shows an entry for a collection class in the class reference table.

Example 4-1 Collection Class Entry in the Class Reference Table
CLS_ID MOD_ID SVC_ID IS_UDS CONTAINER_CLASS_LABEL
---------- ---------- ---------- ---------- -------------------------------
8 2 2 0 I/O

The collection class in Example 4-1 would have the data tables shown in Example 4-2.

Example 4-2 Data Tables for a Collection Class
VP_0_2_2_8_BASE
VP_0_2_2_8_HOUR
VP_0_2_2_8_DAY
VP_0_2_2_8_WEEK
VP_0_2_2_8_MONTH
VP_0_2_2_8_YEAR
Data Table Contents
The first two columns of each data table are always the Timestamp column and the Data Source Name column. The Data Source Name column is essentially the class name, for example, Logical Disk. In aggregate tables (tables other than BASE), the third column is an accuracy calculation, which is not supported in this version of Oracle Capacity Planner.

All subsequent columns contain the values for the data items collected for that class, as they are actually named. In aggregate tables, these columns are prefixed with a two-character abbreviation for the aggregation statistic that the column contains. Table 4-4 shows these abbreviations and their meanings.

Table 4-4 Prefixes Used for Column Names in Aggregate Tables
Prefix Meaning
AV
Average

SM
Sum

MN
Minimum

MX
Maximum

SD
Standard deviation

VR
Variance

CT
Count

These statistics are calculated for the set of data in that aggregate from the next lowest level. For example, a row in the DAY aggregate table contains statistics calculated for all entries in the HOUR table for that day.

Identifying Which Oracle Indexes to Rebuild

2008-06-04T03:35:00.000-07:00

Indexes require rebuilding when deleted leaf rows appear or when the index has a suboptimal number of block gets per access. While it is tempting to write a script that rebuilds every index in the schema, bear in mind that your schema may contain many thousands of indexes, and a complete rebuild can be very time consuming. Hence, we need to develop a method to identify those indexes that will get improved performance with a rebuild. Let’s look at one method for accomplishing this task.

Oracle index nodes are not physically deleted when table rows are deleted, nor are the entries removed from the index. Rather, Oracle "logically" deletes the index entry and leaves "dead" nodes in the index tree where that may be re-used if another adjacent entry is required. However, when large numbers of adjacent rows are deleted, it is highly unlikely that Oracle will have an opportunity to re-use the deleted leaf rows, and these represent wasted space in the index. In addition to wasting space, large volumes of deleted leaf nodes will make index fast-full scans run for longer periods.

These deleted leaf nodes can be easily identified by running the IDL.SQL script (shown in Listing 5-12); the output from the script is shown in Listing 5-11.

In Listing 5-11 we see several important statistics.

The number of deleted leaf rows

The term "deleted leaf node" refers to the number of index inodes that have been logically deleted as a result of row deletes. Remember that Oracle leaves "dead" index nodes in the index when rows are deleted. This is done to speed up SQL deletes, since Oracle does not have to allocate resources to rebalance the index tree when rows are deleted.

Index height

The height of the index refers to the number of levels that are spawned by the index as a result in row inserts. When a large amount of rows are added to a table, Oracle may spawn additional levels of an index to accommodate the new rows. Hence, an Oracle index may have four levels, but only in those areas of the index tree where the massive inserts have occurred. Oracle indexes can support many millions of entries in three levels, and any Oracle index that has four or more levels would benefit from rebuilding.

Gets per index access

The number of "gets" per access refers to the amount of logical I/O that is required to fetch a row with the index. As you may know, a logical "get" is not necessarily a physical I/O since much of the index may reside in the Oracle buffer cache. However, any SAP index with a number greater than 10 would probably benefit from an index rebuild.

Unfortunately, Oracle does not make it easy to capture this information. In Oracle we must issue these commands:

ANALYZE INDEX index_name COMPUTE STATISTICS
ANALYZE INDEX index_name VALIDATE STRUCTURE

After you analyze the report above, you may want to consider rebuilding any index where the height is more than three levels, since three levels will support millions of index entries. Note that Oracle indexes will “spawn” to a fourth level only in areas of the index where a massive insert has occurred, such that 99% of the index has three levels, but the index is reported as having four levels.

We might want to rebuild an index if the “block gets” per access is greater than five, since excessive “blocks gets” indicate a fragmented b-tree structure. Another rebuild condition would be cases where deleted leaf nodes comprise more than 20% of the index nodes. As you may know, you can easily rebuild an Oracle index with the command:

ALTER INDEX index_name REBUILD

The SQL that created this report is shown in Listing 5-12.

revsp2t> cat id1.sql
set pages 9999;
set heading off;
set feedback off;
set echo off;

spool id4.sql;

select '@id2.sql' from dual;

select 'analyze index '||owner||'.'||index_name||' validate structure;',
'@id3.sql;'
from dba_indexes
where
owner not in ('SYS','SYSTEM');

spool off;

set heading on;
set feedback on;
set echo on;

@id4.sql
@id5.sql

revsp2t> cat id2.sql
create table temp_stats as
select
name ,
most_repeated_key ,
distinct_keys ,
del_lf_rows ,
height ,
blks_gets_per_access
from index_stats;

revsp2t> cat id3.sql
insert into temp_stats
(select
name ,
most_repeated_key ,
distinct_keys ,
del_lf_rows ,
height ,
blks_gets_per_access
from index_stats
);

id5.sql
Rem ind_fix.sql - Shows the detals for index stats

set pagesize 60;
set linesize 100;
set echo off;
set feedback off;
set heading off;

column c1 format a18;
column c2 format 9,999,999;
column c3 format 9,999,999;
column c4 format 999,999;
column c5 format 99,999;
column c6 format 9,999;

spool idx_report.lst;

prompt
prompt
prompt ' # rep dist. # deleted blk gets
prompt Index keys keys leaf rows Height per access
prompt -------------------- ------ ----- -------- ------ -----

select distinct
name c1,
most_repeated_key c2,
distinct_keys c3,
del_lf_Rows c4,
height c5,
blks_gets_per_access c6
from temp_stats
where
height > 3
or
del_lf_rows > 10
order by name;

spool off;

spool id6.sql;

select 'alter index '||owner||'.'||name||' rebuild tablespace '||tablespace_name
||';'
from temp_stats, dba_indexes
where
temp_stats.name = dba_indexes.index_name
and
(height > 3
or
del_lf_rows > 10);v

select 'analyze index '||owner||'.'||name||' compute statistics;'
from temp_stats, dba_indexes
where
temp_stats.name = dba_indexes.index_name
and
(height > 3
or
del_lf_rows > 10);

spool off;

Listing 5-12. The id1.sql and other scripts to produce a detailed index report

Now that we have identified the candidates for an index rebuild, we can run the following script during SAP system downtime to re-build all of the indexes (see Listing 5-13).

Set heading off;
Set pages 9999;
Spool run_rebuild.sql;

select 'alter index sapr3.'||
index_name||
' rebuild tablespace '||
tablespace_name||';'
from dba_indexes
where owner = ‘SAPR3’;

spool off;
@run_rebuild

Listing 5-13. A script to generate the index rebuild syntax

Using ALTER INDEX REBUILD to Rebuild Indexes

The ALTER INDEX index_name REBUILD command is very safe way to rebuild indexes. Here is the syntax of the command:

alter index index_name
rebuild
tablespace tablespace_name
storage (initial new_initial next new_next freelists new_freelist_number )

Unlike the traditional method where we drop the index and recreate it, the REBUILD command does not require a full table scan of the table, and the subsequent sorting of the keys and rowids. Rather, the REBUILD command will perform the following steps:

Walk the existing index to get the index keys.
Populate temporary segments with the new tree structure.
Once the operation has completed successfully, drop the old tree, and rename the temporary segments to the new index.
As you can see from the steps, you can rebuild indexes without worrying that you will accidentally lose the index. If the index cannot be rebuilt for any reason, Oracle will abort the operation and leave the existing index intact. Only after the entire index has been rebuilt does Oracle transfer the index to the new b-tree.

Most Oracle administrators run this script, and then select the index that they would like to rebuild. Note that the TABLESPACE clause should always be used with the ALTER INDEX REBUILD command to ensure that the index is rebuilt in the same tablespace as the existing index. Be aware that you must have enough room in that tablespace to hold all of the temporary segments required for the index rebuild, so most Oracle administrators will double-size index tablespaces with enough space for two full index trees.

ASM - Introduction

2008-06-03T04:39:00.000-07:00

ASM - Automatic storage management

Introduce with Oracle 10gr1.

About ASM

http://www.oracle.com/database/demos/asm.html

Benefits of Automatic Storage Management

http://download.oracle.com/docs/cd/B14117_01/server.101/b10743/mgmt_db.htm#sthref2270

ASM provides many of the same benefits as storage technologies such as RAID or logical volume managers (LVMs). Like these technologies, ASM enables you to create a single disk group from a collection of individual disk devices. It balances I/O to the disk group across all of the devices in the disk group. It also implements striping and mirroring to improve I/O performance and data reliability.
However, unlike RAID or LVMs, ASM implements striping and mirroring at the file level. This implementation enables you to specify different storage attributes for individual files in the same disk group.

ASM - Automatic storage management

Introduce with Oracle 10gr1.

About ASM

http://www.oracle.com/database/demos/asm.html

Benefits of Automatic Storage Management

http://download.oracle.com/docs/cd/B14117_01/server.101/b10743/mgmt_db.htm#sthref2270

ASM provides many of the same benefits as storage technologies such as RAID or logical volume managers (LVMs). Like these technologies, ASM enables you to create a single disk group from a collection of individual disk devices. It balances I/O to the disk group across all of the devices in the disk group. It also implements striping and mirroring to improve I/O performance and data reliability.
However, unlike RAID or LVMs, ASM implements striping and mirroring at the file level. This implementation enables you to specify different storage attributes for individual files in the same disk group.

Preparing disk for ASM instance

2008-06-03T04:38:00.000-07:00

Preparing disk for ASM instance on Windows

We can use DISKPART.EXE to create primary,logical partitions and using ASMTOOL (commandline) & ASMTOOLG (GUI)stamp(map) for ASM instance.

Preparing Disks for ASM

http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmprepare.htm#BHCDCFIH

Diskpart.exe

http://download.oracle.com/docs/cd/B28359_01/install.111/b28250/racstorage.htm#BABFIDGI

ASMTOOL & ASMTOOLG

http://download.oracle.com/docs/cd/B28359_01/install.111/b28250/racstorage.htm#sthref212

Demostration

E:\>asmtool -list
NTFS \Device\Harddisk0\Partition1 37997M
NTFS \Device\Harddisk0\Partition2 38319M

E:\>mkdir asmdisks
E:\>cd e:\asmdisks
E:\asmdisks>asmtool -create e:\asmdisks\DISK1 512
E:\asmdisks>asmtool -create e:\asmdisks\DISK2 512
E:\asmdisks>cd c:\app\m.taj\product\11.1.0

More Reference on disks preparing for ASM

http://www.databasejournal.com/features/oracle/article.php/3571371

http://www.databasejournal.com/img/jsc_ASM_Listing2.html

Next Part >>> ASM Instance Creation on Windows

http://dbataj.blogspot.com/2008/04/asm-instance-creation.html

Email this • Share on Facebook • Digg This! • Add to del.icio.us • Save to del.icio.us • Subscribe to this feed • Technorati Links • Sphere: Related Content • outside.in: geotag this story • Discuss on Newsvine • Add to Mixx! • Stumble It!

ASM Instance Creation

2008-06-03T04:37:00.002-07:00

ASM Instance Creation on Windows

1. Create INIT.ORA file for asm instance

DIAGNOSTIC_DEST=C:\app\m.taj\diag
_asm_allow_only_raw_disks= FALSE
instance_type=asm
remote_login_passwordfile=exclusive
memory_target=52m

Note: Minimum required parameters.

C:\>oradim -new -asmsid +ASM -startmode manual
Instance created.

C:\>oradim -edit -asmsid +ASM -startmode a

C:\>oradim -edit -asmsid +ASM -syspwd change_on_install

C:\>set ORACLE_SID=+ASM

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 8 11:22:47 2008

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

Connected to an idle instance.

SQL> startup pfile ='c:\init.ora';
ASM instance started

Total System Global Area 267825152 bytes
Fixed Size 1332584 bytes
Variable Size 241326744 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

SQL> create spfile from pfile='c:\init.ora';

File created.

SQL> shutdown
ORA-15100: invalid or missing diskgroup name

ASM instance shutdown
SQL> startup
ASM instance started

Total System Global Area 267825152 bytes
Fixed Size 1332584 bytes
Variable Size 241326744 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

SQL> SELECT group_number, disk_number, mount_status, header_status, state, path FROM v$asm_disk;
GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE PATH

----------- ----------- ------- ------------ -------- -------------------- 0 1 CLOSED CANDIDATE NORMAL E:\ASMDISKS\DISK2 0 0 CLOSED CANDIDATE NORMAL E:\ASMDISKS\DISK1

Administratering ASM instance

http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asminst.htm#CHDGDJDF

http://www.idevelopment.info/data/Oracle/DBA_tips/Automatic_Storage_Management/ASM_10.shtml

Next Part >>> http://dbataj.blogspot.com/2008/04/managing-diskgroups.html

Email this • Share on Facebook • Digg This! • Add to del.icio.us • Save to del.icio.us • Subscribe to this feed • Technorati Links • Sphere: Related Content • outside.in: geotag this story • Discuss on Newsvine • Add to Mixx! • Stumble It!

Managing Diskgroups

2008-06-03T04:37:00.001-07:00

Creating Diskgroup
1. Assign Unqiue name for each diskgroup
2. Specify redundancy level of disk group
There is three type of redundancy level
1. NORMAL redundancy with 2 way mirroring
2. HIGH redundancy with 3 way mirroring
3. EXTERNAL redundancy with NO mirroring.

3. Specify disks as belonging to specific failgroup.
If we manually not define any failgroup for disk then Oracle automatically create failgroup for each diskgroup.

4. Specify asm disks (v$asm_disk) for diskgroup.

5. Optionally specify disk group attributes such as compatibility or allocation unit size.

Find more http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmdiskgrps.htm#i1009309

SQL> conn / as sysasm
Connected to an idle instance.
SQL> startup nomount
ASM instance started
Total System Global Area 267825152 bytes
Fixed Size 1332584 bytes
Variable Size 241326744 bytes
ASM Cache 25165824 bytes

Create DISKGROUP with NORMAL REDUNDANCY
SQL> CREATE DISKGROUP dgrp1 NORMAL REDUNDANCY
2 FAILGROUP controller1 DISK
3 'e:\asmdisks\disk1' NAME diska1,
4 'e:\asmdisks\disk2' NAME diska2
5 FAILGROUP controller2 DISK
6 'e:\asmdisks\disk3' NAME diska3;
Diskgroup created.

SQL> select group_number,
2 name,
3 type,
4 total_mb,
5 free_mb
6 from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB
------------ ------------------------------ ------ ---------- ------
1 DGRP1 NORMAL 1536 1432

ADD NEW DISK in exiting DISKGROUP dgrp1
SQL> alter diskgroup dgrp1 add disk 'e:\asmdisks\disk4';
Diskgroup altered.

SQL> select group_number,
2 name,
3 type,
4 total_mb,
5 free_mb
6 from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB
------------ ------------------------------ ------ ---------- ------
1 DGRP1 NORMAL 2048 1940

Managing Capacity in Disk Groups
When ASM provides redundancy, such as when you create a disk group with NORMAL or HIGH redundancy, you must have sufficient capacity in each disk group to manage a re-creation of data that is lost after a failure of one or two failure groups. After one or more disks fail, the process of restoring redundancy for all data requires space from the surviving disks in the disk group. If not enough space remains, then some files might end up with reduced redundancy.

Find more http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmdiskgrps.htm#CHDGGGAD

Negative Values of USABLE_FILE_MB
Due to the relationship between FREE_MB, REQUIRED_MIRROR_FREE_MB, and USABLE_FILE_MB, USABLE_FILE_MB can become negative. Although this is not necessarily a critical situation, it does mean that:
Depending on the value of FREE_MB, you may not be able to create new files.

The next failure might result in files with reduced redundancy.

If USABLE_FILE_MB becomes negative, it is strongly recommended that you add more space to the disk group as soon as possible.

When Should External, Normal, or High Redundancy Be Used?

ASM mirroring runs on the database server and Oracle recommends to off load this processing to the storage hardware RAID controller by using external redundancy. You can use normal redundancy in the following scenarios:

Storage system does not have RAID controller

Mirroring across storage arrays

Extended cluster configurations

In general, ASM mirroring is the Oracle alternative to third party logical volume managers. ASM mirroring eliminates the need to deploy additional layers of software complexity in your Oracle database environment.

Find more http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmdiskgrps.htm#CHDHDGDI

Migrating Databases From NON-ASM to ASM

2008-06-03T04:36:00.000-07:00

1. First need to set below parameter for controlfile,datafile or FRA.

NOTE: I have two disk group here i am using "DGRP2" disk group.

SQL> alter system set control_files='+DGRP2' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DGRP2' scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest='+DGRP2' scope=spfile;
System altered.

2. Create New controlfile , datafile or spfile in ASM diskgroup.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.
Total System Global Area 263639040 bytes

Fixed Size 1332552 bytes

Variable Size 218106552 bytes

Database Buffers 37748736 bytes

Redo Buffers 6451200 bytes

Connect with RMAN session.

C:\>RMAN target=sys
Recovery Manager: Release 11.1.0.6.0 - Production on Thu Apr 17 10:12:53 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password:connected to target database: TEST (not mounted)

Restore Controlfile.

RMAN> restore controlfile from 'C:\app\m.try\oradata\test\CONTROL01.ctl';
Starting restore at 17-APR-08using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 device type=DISK
channel ORA_DISK_1: copied control file copyoutput file name=+DGRP2/test/controlfile/current.256.652270419Finished restore at 17-APR-08

RMAN> alter database mount;
database mountedreleased channel: ORA_DISK_1

Take backup of database which we use for ASM diskgroup.

RMAN> backup as copy database format '+DGRP2';

Starting backup at 17-APR-08allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 device type=DISKchannel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=C:\APP\M.try\ORADATA\TEST\SYSTEM01.DBFoutput file name=+DGRP2/test/datafile/system.257.652270565 tag=TAG20080417T101550 RECID=1 STAMP=652270748channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:06channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=C:\APP\M.try\ORADATA\TEST\SYSAUX01.DBFoutput file name=+DGRP2/test/datafile/sysaux.258.652270761 tag=TAG20080417T101550 RECID=2 STAMP=652270908channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:35channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=C:\APP\M.try\ORADATA\TEST\UNDOTBS01.DBFoutput file name=+DGRP2/test/datafile/undotbs1.259.652270927 tag=TAG20080417T101550 RECID=3 STAMP=652270952channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35channel ORA_DISK_1: starting datafile copycopying current control fileoutput file name=+DGRP2/test/controlfile/backup.260.652270971 tag=TAG20080417T101550 RECID=4 STAMP=652270975channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=C:\APP\M.try\ORADATA\TEST\USERS01.DBFoutput file name=+DGRP2/test/datafile/users.261.652270989 tag=TAG20080417T101550 RECID=5 STAMP=652270991channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 17-APR-08channel ORA_DISK_1: finished piece 1 at 17-APR-08piece handle=+DGRP2/test/backupset/2008_04_17/nnsnf0_tag20080417t101550_0.262.652271003 tag=TAG20080417T101550 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 17-APR-08

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DGRP2/test/datafile/system.257.652270565"
datafile 2 switched to datafile copy "+DGRP2/test/datafile/sysaux.258.652270761"
datafile 3 switched to datafile copy "+DGRP2/test/datafile/undotbs1.259.652270927"
datafile 4 switched to datafile copy "+DGRP2/test/datafile/users.261.652270989"

Again connect to sqlplus session and perform incomplete recovery

C:\>sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Apr 17 10:26:24 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 1071679 generated at 04/17/2008 10:11:10 needed for thread 1ORA-00289: suggestion : +DGRP2ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'TEST'ORA-00280: change 1071679 for thread 1 is in sequence #14
Specify log: {=suggested filename AUTO CANCEL}

CANCEL

Media recovery cancelled.

OPEN database with RESETLOGS option.

SQL> alter database open resetlogs;
Database altered.

4. Drop old tempfile and create new tempfile in existing temp tablespace

SQL> alter database tempfile 'c:\app\m.try\oradata\test\temp01.dbf' 2 drop including datafiles;
Database altered.

SQL> alter tablespace temp add tempfile size 512m 2 autoextend on next 250m maxsize unlimited;
Tablespace altered.

SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES------------------------------ ------------------------------ ----------TEMP +DGRP2/test/tempfile/temp.266. 536870912 652271571

5. Recreate All redolog group on ASM diskgroup

SQL> select a.group#, a.member, b.bytes

2 from v$logfile a, v$log b where a.group# = b.group#;

GROUP# MEMBER BYTES

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

3 C:\APP\M.try\ORADATA\TEST\REDO03.LOG 52428800
2 C:\APP\M.try\ORADATA\TEST\REDO02.LOG 52428800
1 C:\APP\M.try\ORADATA\TEST\REDO01.LOG 52428800

SQL> select group#,status from v$log;
GROUP# STATUS

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

1 CURRENT

2 UNUSED

3 UNUSED

SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.

SQL> select group#,status from v$log;
GROUP# STATUS

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

1 ACTIVE

2 ACTIVE

3 CURRENT

SQL> alter database drop logfile group 1;

alter database drop logfile group 1*ERROR at line 1:ORA-01624: log 1 needed for crash recovery of instance test (thread 1)ORA-00312: online log 1 thread 1: 'C:\APP\M.try\ORADATA\TEST\REDO01.LOG'

When you get above error message then set checkpoint with below command.

SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 10m;
Database altered.

SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 10m;
Database altered.

SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 10m;
Database altered.

SQL> column member format a30

SQL> select a.group#, a.member, b.bytes

2 from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES

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

3 +DGRP2/test/onlinelog/group_3. 10485760 277.652273117
2 +DGRP2/test/onlinelog/group_2. 10485760 274.652273019
1 +DGRP2/test/onlinelog/group_1. 10485760 271.652272977
1 +DGRP2/test/onlinelog/group_1. 10485760 272.652272979
2 +DGRP2/test/onlinelog/group_2. 10485760 275.652273021
3 +DGRP2/test/onlinelog/group_3. 10485760 278.652273119
6 rows selected.

6. Recreate SPFILE on ASM diskgroup

SQL> create pfile='c:\initTEST.ora' from spfile;
File created.
SQL> create spfile='+DGRP2/spfileTEST.ora' from pfile='c:\initTEST.ora';
File created.

7. Detele all backup copy from RMAN

RMAN> delete noprompt force copy;

Find more details

http://www.idevelopment.info/data/Oracle/DBA_tips/Automatic_Storage_Management/ASM_33.shtml#Overview

DBA Interview Questions

2008-06-03T04:35:00.000-07:00

“A person who’s responsible to handle and maintain database called DBA”
Means DBA position is very Hard, though, sensitive and confidential.

We can divide Oracle DBA interview in Four Major part.
1. Oracle Database Administration
2. Oracle Database Backup & Recovery Administration
3. Oracle Database Performance Tuning
4. Oracle Database Advanced administration

1. Oracle Database Administration
1. How to install Oracle Database Software on Different platforms?
Above point include Oracle Database Server or Client installation on different flavor like LINUX/SORALIS/UNIX/WINDOWS etc. so whenever you are going for interview must go through each installation guide.

2. How to Create Database?
There is two option for create DATABASE.
1. DBCA or 2. MANUALLY

3. How to create tablespace?
In above point we can include TABLESPACE type, DATAFILE size or AUTOEXTEND option.

4. How to configure Oracle Client to connect to Oracle Server?
In Above point we need to create TNS_ENTRY for database client through NETCA tools.

5. Database User Management
In above point we need to do User Creation, Specific permission on database according user needs. User profile creation.

6. Database Security
In above point we need to do Database Auditing, User Logon, Logoff information.

2. Oracle Database Backup & Recovery Administration
1. What is your backup strategy for database backup?
It is depends on many factor.
1. Database size It is in MB, GB or TB
2. Database type It is OLTP or DSS
3. Database availability It is 24x7
So whenever we design backup strategy for database keeps in mind above points. Because Oracle Expert Mr. Tom kyte said “We can FIX everything except BAD DATABASE BACKUP”.

2. What is your backup method for database backup?
It is also depend on condition.
1. Backup Type COLD backup or HOT backup
2. Backup Method RMAN or USER MANAGED
3. Backup Interval Every Day, Every Week, Every Month.

Oracle Corp. recommended to use RMAN (server managed) backup method for database backup but we can also use USER MANAGED backup if applicable.
NOTE: Always RUN production database in ARCHIVELOG mode.

After set proper backup strategy now practice on database recovery because interviewer must ask question about recovery side.

“Database Recovery is depend on Database backup”
There are so many or different types of recovery so we can divide recovery area in following.

How many types of recovery we can perform?
1. When datafile is lost
2. when controlfile is lost
3. when online redolog file is lost

Above three files is most important file for database if we lost them then we needed to perform recovery.
1. We can perform recovery at DATABASE level when complete database is lost.
2. We can perform recovery at TABLESPACE level when tablespace is lost.
3. We can perform recovery at DATAFILE level when datafile is lost.
4. We can perform recovery at DATA BLOCK level when any block is corrupted.
5. We can Multiplex CONTROLFILE or REDOLOG file for protect against LOST.

And suppose still we lost all controlfile or redolog files then recovery depends on many conditions.

NOTE: Before going for interview must go through backup and recovery user guide.

3. Oracle Database Performance tuning administration
This is third big area where most of DBA questions come.

For database tuning we need years of experience. But we can start with bottom.
1. What is database background process like DBRW, LGWR, CKPT SMON, PMON etc? 2. What is database memory area means SGA or PGA size?
3. What are database activities?

For above question oracle providing same diagnostic tools
1. STATPACK or AWR report
2. Tkprof
3. SQL trace
4. AUTO TRACE

Above are same tool to use find out bottleneck of slow database performance so we must know below things about this tools
1. How to use?
2. How to understand?
3. How to use these tools to solve database performance issue.

Above question is simple but answer is difficult because answer want practical or years of experience in database.
Note: Search on Google or visit OTN site for starting.

4. Oracle Database advanced administration
Oracle Advanced Features
1. Data Guard
2. RAC system
3. Stream
4. Partitioning
5. ASM
6. Others

DBA interviewer should ask question above points but it is also depend on DBA position because above points will include in senior level position.

History of Oracle Technology Introductions

2008-06-03T04:33:00.000-07:00

Oracle Database Products

Oracle Server/ Standard Edition
Version of Oracle server for a small number of users and a smaller database
Oracle Enterprise Edition
Version of Oracle for a large number of users or a large database with advanced features for extensibility, performance, and management
Oracle Personal Edition
Single-user version of Oracle typically used for development of applications for deployment on other Oracle versions
Oracle Lite
Lightweight database engine for mobile computing on notebooks and handheld devices

History of Oracle Technology Introductions

1979

Oracle Release 2--the first commercially available relational database to use SQL

1983

Single code base for Oracle across multiple platforms

1984

Portable toolset

1986

Client/server Oracle relational database

1987

CASE and 4GL toolset

1988

Oracle Financial Applications built on relational database

1989

Oracle6 1991 Oracle Parallel Server on massively parallel platforms

1993

Oracle7 with cost-based optimizer

1994

Oracle Version 7.1 generally available: parallel operations including query, load, and create index

1996

Universal database with extended SQL via cartridges, thin client, and application server

1997

Oracle8 generally available: including object-relational and Very Large Database (VLDB) features

1999

Oracle8i generally available: Java Virtual Machine ( JVM) in the database

2000

Oracle9i Application Server generally available: Oracle tools integrated in middle tier

2001

Oracle9i Database Server generally available: Real Application Clusters, Advanced Analytic Services

Tablespace Size

2008-06-03T04:32:00.001-07:00

SQL> select tablespace_name, 'Mb'||' '||round(sum(bytes/1024/1024)) "Used_Size"

2 from dba_data_files
3 group by tablespace_name;

TABLESPACE_NAME Used_Size
------------------------------ -------------------------------------------
DENI Mb 3
EXAMPLE Mb 150
SYSAUX Mb 310
SYSTEM Mb 450
UNDOTBS1 Mb 875
USERS1 Mb 3629

6 rows selected.

SQL> select tablespace_name, 'Mb'||' '||round(sum(bytes/1024/1024)) "Free_Size"

2 from dba_free_space
3 group by tablespace_name;

TABLESPACE_NAME Free_Size
------------------------------ -------------------------------------------
DENI Mb 3
EXAMPLE Mb 70
SYSAUX Mb 19
SYSTEM Mb 4
UNDOTBS1 Mb 867
USERS1 Mb 1

6 rows selected.

utl_file plsql package.

2008-06-03T04:31:00.001-07:00

Note:

1.2 use the CREATE DIRECTORY feature instead of UTL_FILE_DIR for directory access verification.

2.Up to 8i, Oracle refused to access a file that is not pointed to in the utl_file_dir parameter in
the init.ora file.
In Oracle 9i, in order to access a file, either the utl_file_dir parameter must be set, or one has to create a directory.

3.First Availability
Oracle Version : 7.3.4

4.The UTL_FILE package is similar to the client-side TEXT_IO package currently provided by Oracle Procedure Builder. Restrictions for a server implementation require some API differences between UTL_FILE and TEXT_IO. In PL/SQL file I/O, errors are returned using PL/SQL exceptions.

5.Open Mode.
1.A append
2.R read
3.W write

expdp Export Datapump.

2008-06-03T04:30:00.000-07:00

Note :
1. before export ur database estimate size about export dump file.
used.

"ESTIMATE_ONLY" = Y parameter.
eg: cmd>expdp system/oracle schemas=HR directory=data nologfile=y estimate_only=y

dbms_logmnr package

2008-06-03T04:29:00.000-07:00

Note -

1.You must have the EXECUTE_CATALOG_ROLE role to use the DBMS_LOGMNR package.

2.After you have created a dictionary file with DBMS_LOGMNR_D, you can begin analyzing archived redo logs.

The DBMS_LOGMNR_D package contains the LogMiner procedures, DBMS_LOGMNR_D.BUILD and DBMS_LOGMNR_D.SET_TABLESPACE.The DBMS_LOGMNR_D.BUILD procedure extracts the dictionary to either the redo logs or to a flat file. This information is saved in preparation for future analysis of redo logs using the LogMiner tool. The DBMS_LOGMNR_D.SET_TABLESPACE procedure re-creates all LogMiner tables in an alternate tablespace.

3.The DBMS_LOGMNR_D.BUILD procedure will not run if there are any ongoing DDL operations.

4.1When extracting a dictionary to a flat file, the procedure queries the dictionary tables of the current database and creates a text-based file containing the contents of the tables. To extract a dictionary to a flat file, the following conditions must be met:

1.The dictionary file must be created from the same database that generated the redo logs you want to analyze

2.You must specify a directory for use by the PL/SQL procedure. To do so, set the initialization parameter UTL_FILE_DIR in the init.ora file. For example: UTL_FILE_DIR = /oracle/dictionary

If you do not set this parameter, the procedure will fail.
You must ensure that no DDL operations occur while the dictionary build is running. Otherwise, the dictionary file may not contain a consistent snapshot of the data dictionary

4.2To extract a dictionary file to the redo logs, the following conditions must be met:

1.The DBMS_LOGMNR_D.BUILD procedure must be run on a system that is running Oracle9i or later

2.Archiving mode must be enabled in order to generate usable redo

3.The dictionary redo files must be created from the same database that generated the redo logs you want to analyze

Example 1: Extracting the Dictionary to a Flat File

SQL> alter database add supplemental log data;
Database altered.

SQL> execute dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);
PL/SQL procedure successfully completed.

yet to be complete.

Flashback Oracle10gR1 10.1.0.2.0

2008-06-03T04:28:00.002-07:00

Using Flashback Drop and Managing the Recycle Bin
A user can view his objects in the recycle bin using the following statement:
SELECT * FROM RECYCLEBIN;

When you drop a tablespace including its contents, the objects in the tablespace are not placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the tablespace. The database also purges any recycle bin entries for objects in a tablespace when you drop the tablespace, not including contents, and the tablespace is otherwise empty. Likewise:When you drop a user, any objects belonging to the user are not placed in the recycle bin and any objects in the recycle bin are purged.When you drop a cluster, its member tables are not placed in the recycle bin and any former member tables in the recycle bin are purged.When you drop a type, any dependent objects such as subtypes are not placed in the recycle bin and any former dependent objects in the recycle bin are purged.

When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names.
The renaming convention is as follows:BIN$unique_id$version

where:unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databasesversion is a version number assigned by the database
You can query objects that are in the recycle bin, just as you can query other objects. However, you must specify the name of the object as it is identified in the recycle bin. For example:

SQL> create table hr ( name varchar2(20));
Table created.
SQL> drop table hr;
Table dropped.
SQL> select * from recyclebin;
OBJECT_NAME ORIGINAL_NAME
OPERATION
------------------------------ --------------------------------
---------
TYPE TS_NAME CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME DROPSCN PARTITION_NAME CAN CAN
------------------- ---------- -------------------------------- --- ---
RELATED BASE_OBJECT PURGE_OBJECT SPACE
---------- ----------- ------------ ----------
BIN$74ANPU11QhWw15ah+JM2gQ==$0 HR
DROPTABLE USERS 2006-11-16:17:56:252006-11-16:17:56:34
445402 YES YES
50424 50424 50424 8

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE
DROP TIME
---------------- ------------------------------ ------------ -------------------
HR BIN$74ANPU11QhWw15ah+JM2gQ==$0 TABLE
2006-11-16:17:56:34

SQL> select * from "BIN$74ANPU11QhWw15ah+JM2gQ==$0";
no rows selected

Restoring Tables from the Recycle Bin
SQL> flashback table hr to before drop rename to scott;
Flashback complete.

You can directly drop table using "purge" cluase.
if use "purge" clause then object not goes in recyclebin. it will delete parmanently.

SQL> drop table scott purge;
Table dropped.

You can also "purge" recyclebin
SQL> show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME---------------- ------------------------------ ------------ -------------------
SCOTT BIN$QFb9kDMkRq2RpWJjjW9+ug==$0 TABLE 2006-11-16:18:14:26
SQL> purge recyclebin;
Recyclebin purged.
SQL> show recyclebin;

****************************************
Oracle Flashback Table: Returning Individual Tables to Past States

Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement. You can enable row movement with the following SQL statement
SQL> conn hr/hrConnected.
SQL> create table big_table as select * from all_objects;
Table created.
SQL> alter table big_table enable row movement;
Table altered.
SQL> alter session set nls_date_format = 'hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------
18:43:49
SQL> select count(*) from big_table;
COUNT(*)
----------
46335
SQL> delete from big_table where rownum <= 445; 445 rows deleted. SQL> commit;
Commit complete.
SQL> flashback table big_table to TIMESTAMP 2 to_timestamp ( '2006-11-16 18:44:00','YYYY-MM-DD HH24:MI:SS');
Flashback complete.
SQL> select count(*) from big_table;
COUNT(*)
----------
46335

Flashback Query

you must choose an undo retention interval that is long enough to enable users to construct a snapshot of the database for the oldest version of the database that they are interested in.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter system set undo_retention = 3600;
System altered.
SQL> conn hr/hr
Connected.
SQL> alter session set nls_date_format = 'hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------
19:04:49
SQL> create table hrtest ( no number, name varchar2(20));
Table created.
SQL> begin
2 insert into hrtest values ( 1, 'aaa');
3 insert into hrtest values ( 2, 'bbb');
4 insert into hrtest values ( 3, 'ccc');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select sysdate from dual;
SYSDATE
--------
19:06:23
SQL> delete from hrtest where name = 'ccc';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from hrtest;
NO NAME
---------- --------------------
1 aaa
2 bbb

SQL> edWrote file afiedt.buf
1 select * from hrtest AS OF TIMESTAMP 2 to_timestamp ('2006-11-16 19:06:23','YYYY-MM-DD HH24:MI:SS')
3* where name = 'ccc'
4 /
NO NAME
---------- --------------------
3 ccc
SQL> insert into hrtest 2 ( select * from hrtest AS OF TIMESTAMP 3 to_timestamp ('2006-11-16 19:06:23','YYYY-MM-DD HH24:MI:SS')
4 where name = 'ccc');
1 row created.
SQL> select * from hrtest;
NO NAME
---------- --------------------
1 aaa
2 bbb
3 ccc

x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x-x
SQL> create table SCN_TEST (no number, name varchar2(20));

Table created.

SQL> insert into scn_test values (1,'a');

1 row created.

SQL> insert into scn_test values (2,'b');

1 row created.

SQL> insert into scn_test values (3,'c');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scn_test;

NO NAME
---------- --------------------
1 a
2 b
3 c

SQL> show parameter undo_retention

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 22719
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
15893109

SQL> delete from scn_test where no = 2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from scn_test AS OF SCN 15893109 where no = 2;

NO NAME
---------- --------------------
2 b

SQL> insert into scn_test ( select * from scn_test AS OF SCN 15893109
2 where no = 2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scn_test;

NO NAME
---------- --------------------
1 a
3 c
2 b

************************

Using Flashback Version Query

SQL> select * from hrtest;
NO NAME
---------- --------------------
1 aaa
2 bbb
3 ccc
SQL> begin
2 insert into hrtest values ( 4, 'ddd');
3 insert into hrtest values ( 5, 'eee');
4 commit;
5 end;
6 /
PL/SQL procedure successfully completed.

SQL> select sysdate from dual;
SYSDATE
--------
19:35:26
SQL> select sysdate from dual;
SYSDATE
--------
19:38:57
SQL> delete from hrtest where rownum <= 3;
3 rows deleted.
SQL> commit;
Commit complete.
SQL> begin
2 update hrtest set no = 0;
3 commit;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select sysdate from dual;
SYSDATE
--------
19:48:42
SQL> select * from hrtest;
NO NAME
---------- --------------------
0 ddd
0 eee
SQL> ed
Wrote file afiedt.buf
1 SELECT versions_startscn, versions_starttime,
2 versions_endscn, versions_endtime,
3 versions_xid, versions_operation,
4 hrtest.*
5 FROM hrtest
6 VERSIONS BETWEEN TIMESTAMP
7 TO_TIMESTAMP('2006-11-16 19:35:00', 'YYYY-MM-DD HH24:MI:SS')
8 AND TO_TIMESTAMP('2006-11-16 19:48:00', 'YYYY-MM-DD HH24:MI:SS')
9* WHERE name in ('aaa','bbb','ccc');

SQL> column versions_starttime format a25
SQL> column versions_endtime format a25
SQL> /
VERSIONS_STARTSCN VERSIONS_STARTTIME
VERSIONS_ENDSCN
----------------- -------------------------
---------------
VERSIONS_ENDTIME VERSIONS_XID V NO NAME
------------------------- ---------------- - ---------- -------------------- 449718 16-NOV-06 07.39.10 PM
04000E0028000000 D 3 ccc
449718 16-NOV-06 07.39.10 PM
04000E0028000000 D 2 bbb
449718 16-NOV-06 07.39.10 PM
04000E0028000000 D 1 aaa
44971816-NOV-06 07.39.10 PM
1 aaa

44971816-NOV-06 07.39.10 PM
2 bbb

44971816-NOV-06 07.39.10 PM
3 ccc
6 rows selected.

RMAN-06054: media recovery requesting unknown log: thread x seq xxx lowscn xxxxxx

2008-06-03T04:28:00.001-07:00

SQL> conn / as sysdba
Connected.
SQL> archive log list
Database log mode
Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25

**********************************************************
SQL> host rman target=sys
Recovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004,
Oracle. All rights reserved.
target database
Password:
connected to target database: ORCL (DBID=1134976990)

RMAN> show all;
using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\SNCFORCL.ORA'; # default

****************************
RMAN> configure controlfile autobackup on;new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

*************************************

RMAN> run
2> {
3> allocate channel c1 device type disk;
4> backup database plus archivelog;
5> }

allocated channel: c1channel c1: sid=200 devtype=DISKStarting backup at 23-NOV-06current log archivedStarting backup at 23-NOV-06current log archivedchannel c1: starting archive log backupsetchannel c1: specifying archive log(s) in backup setinput archive log thread=1 sequence=25 recid=1 stamp=607282901channel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_ANNNN_TAG20061123T174141_2PC98Y2P_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:00:11
Finished backup at 23-NOV-06Starting backup at 23-NOV-06channel c1: starting full datafile backupsetchannel c1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:01:33Finished backup at 23-NOV-06Starting backup at 23-NOV-06current log archivedchannel c1: starting archive log backupsetchannel c1: specifying archive log(s) in backup setinput archive log thread=1 sequence=26 recid=2 stamp=607283007channel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_ANNNN_TAG20061123T174328_2PC9D278_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:00:02Finished backup at 23-NOV-06Starting Control File and SPFILE Autobackup at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 23-NOV-06released channel: c1

********************************************************
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.dbf
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.ctl
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.log

**********************************************************
C:\>sqlplus "sys as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Nov 23 17:47:56 2006Copyright (c) 1982, 2004,
Oracle. All rights reserved.
Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area 171966464
bytesFixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes

ORA-00205: error in identifying controlfile, check alert log for more info

*************************************

SQL> host rman target=sys
Recovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004,
Oracle. All rights reserved.
target database Password:connected to target database: orcl (not mounted)

**********************************************

RMAN> restore controlfile from autobackup;
Starting restore at 23-NOV-06using channel ORA_DISK_1recovery area destination: D:\oracle\product\10.1.0\flash_recovery_areadatabase name (or lock name space) used for search: ORCLchannel ORA_DISK_1: autobackup found in the recovery areachannel ORA_DISK_1: autobackup found: D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKPchannel ORA_DISK_1: controlfile restore from autobackup completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 23-NOV-06

****************************************

RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1

****************************************

RMAN> run
2> {
3> restore database;
4> recover database;
5> }

Starting restore at 23-NOV-06Starting implicit crosscheck backup at 23-NOV-06allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=214 devtype=DISKCrosschecked 3 objectsFinished implicit crosscheck backup at 23-NOV-06Starting implicit crosscheck copy at 23-NOV-06using channel ORA_DISK_1Finished implicit crosscheck copy at 23-NOV-06searching for all files in the recovery areacataloging files...cataloging done

List of Cataloged Files
=======================
File Name: D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKPusing channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFrestoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFrestoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFrestoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFrestoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP tag=TAG20061123T174153channel ORA_DISK_1: restore completeFinished restore at 23-NOV-06Starting recover at 23-NOV-06using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 26 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARCarchive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARC thread=1 sequence=26unable to find archive logarchive log thread=1 sequence=27RMAN-00571: ===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/23/2006 17:53:44RMAN-06054: media recovery requesting unknown log: thread 1 seq 27 lowscn 434810

**************************************

Note : RMAN-06054: media recovery requesting unknown log: thread string seq string lowscn string Cause: Media recovery is requesting a log whose existance is not recorded in the recovery catalog or control file.Action: If a copy of the log is available, then add it to the recovery catalog and/or control file via a CATALOG command and then retry the RECOVER command. If not, then a point in time recovery up to the missing log is the only alternative and database can be opened using ALTER DATABASE OPEN RESETLOGS command.

******************************************************

RMAN> run
2> {
3> SET UNTIL SEQUENCE 27 THREAD 1;
4> RESTORE DATABASE;
5> RECOVER DATABASE;
6> ALTER DATABASE OPEN RESETLOGS;
7> }

executing command: SET until clauseStarting restore at 23-NOV-06using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFrestoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFrestoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFrestoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFrestoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP tag=TAG20061123T174153channel ORA_DISK_1: restore completeFinished restore at 23-NOV-06Starting recover at 23-NOV-06using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 26 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARCarchive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARC thread=1 sequence=26media recovery completeFinished recover at 23-NOV-06database opened

*************************************

RMAN> QUIT

Recovery Manager complete.

SQL> select status from v$instance;
STATUS
------------
OPEN

SQL> archive log list
Database log mode Archive Mode
Automatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1

SQL> --take complete consistent bkp of ur database.

Logical_bkp_EXPORT_linux

2008-06-03T04:26:00.001-07:00

Oracle Version : 10.1.3.0.0
OS System : Linux Ver 3
-----------------------------

Export_bkp_scheduling on linux server.
-------------------------------------
shell script for backup.
-----------------------

oracle@oracle]$vi expbkp.sh
#press i or a ( for insert mode)

#!/bin/bash
ORACLE_HOME= /db/app/oracle/orahome #put_oracle_home_path
export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH

#for system date with dmp file.
value= ` date '%d%m%y'`
exp userid/pwd file=file_name$value.dmp log=log_name$value.log

#type shift + zz for file closing.
shift+ZZ
-----------------------------------------
oracle@oracle]$cat expbkp.sh # check expbkp.sh file contents.
--------------------------------------------------------
Schedule ur backup according ur time Eg> every night 10 pm. for scheduling use "CRONTAB" utility.

-------------------------------------
oracle@oracle]$crontab -e #for create new crontab file.
#press i or a for insert mode
0 20 * * * /db/app/expbkp.sh #give compete path of ur .sh ( bkp script file).

------------------------------------------
oracle@oracle]$crontab -l ( for check crontab file)
oracle@oracle]$crontab -r ( for delete crontab file entry )
----------------------------------------------------------

Change User/Schema Name.

2008-06-03T04:25:00.000-07:00

Change Oracle Schema Name

SQL> host exp system/oracle owner=scott file=c:\scottschema.dmp log=c:\scott.log
Export: Release 10.1.0.2.0 - Production on Tue Dec 12 09:36:37 2006
Copyright (c) 1982, 2004, Oracle.
All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
ProductionWith the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified users ....
exporting pre-schema procedural objects and actions.
exporting foreign function library names for user SCOTT.
exporting PUBLIC type synonyms.
exporting private type synonyms.
exporting object type definitions for user SCOTT
About to export SCOTT's objects ....
exporting database links.
exporting sequence numbers.
exporting cluster definitions.
about to export SCOTT's tables via Conventional Path .... .
exporting table A 0 rows exported. .
exporting table ABCDEFGHIJKLMNOPQRSTUVWXYZ 0 rows exported. .
exporting table ABCDEFGHIJKLMNOPQRSTUVWXYZ1234 0 rows exported. .
exporting table A_12345678901234567890123456$# 0 rows exported. .
exporting table B 100 rows exported. .
exporting table BIG 46968 rows exported. .
exporting table BIG_TABLE 46967 rows exported. .
exporting table BONUS 0 rows exported. .
exporting table C 1000 rows exported. .
exporting table D 1000 rows exported. .
exporting table DEPT 4 rows exported. .
exporting table E 1000 rows exported. .
exporting table EMP 14 rows exported. .
exporting table FINAL_OUTPUT. .
exporting table Q 10000 rows exported. .
exporting table SALGRADE 5 rows exported. .
exporting table T 1000 rows exported. .
exporting table TEST 7 rows exported.
exporting synonyms.
exporting views.
exporting stored procedures.
exporting operators.
exporting referential integrity constraints.
exporting triggers. exporting indextypes.
exporting bitmap, functional and extensible indexes.
exporting posttables actions.
exporting materialized views.
exporting snapshot logs.
exporting job queues.
exporting refresh groups and children.
exporting dimensions.
exporting post-schema procedural objects and actions.
exporting statistics
Export terminated successfully without warnings.

SQL> select user_id,username from user_users;
USER_ID USERNAME
---------- ------------------------------
57 SCOTT

SQL> select count(*) from session_privs;
COUNT(*)
----------
81

SQL> select count(*) from session_roles;
COUNT(*)
----------
6

SQL> set echo off
SQL> set feedback off
SQL> set heading off

SQL> spool c:\scottpriv.sql

SQL> select 'grant '||privilege||' to newscott;' from session_privs;
........

SQL>spool off

SQL> spool c:\scottroles.sql

SQL> select 'grant '||role||' to newscott;' from session_roles;
grant CONNECT to newscott;
grant RESOURCE to newscott;
grant IMP_FULL_DATABASE to newscott;
grant SELECT_CATALOG_ROLE to newscott;
grant HS_ADMIN_ROLE to newscott;
grant EXECUTE_CATALOG_ROLE to newscott;

SQL> spool off
SQL> set heading on
SQL> set feedback on
SQL> set echo on

SQL> select default_tablespace from user_users;
DEFAULT_TABLESPACE
------------------------------
TEST
1 row selected.
SQL> conn system/oracleConnected.

SQL> create user newscott identified by newscott default tablespace test;
User created.

SQL>@c:\scottpriv.sql <>

SQL>@c:\scottroles.sql <>

SQL> host imp system/oracle fromuser=scott touser=newscott file=c:\scottschema.dmp log=scottimp.log
Import: Release 10.1.0.2.0 - Production on Tue Dec 12 09:54:39 2006Copyright (c) 1982, 2004, Oracle.
All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport file created by EXPORT:V10.01.00 via conventional pathimport done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set. importing SCOTT's objects into NEWSCOTT. .
importing table "A" 0 rows imported. . importing table "ABCDEFGHIJKLMNOPQRSTUVWXYZ" 0 rows imported. .
importing table "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234" 0 rows imported
........

SQL>drop user scott cascade;

SQL> select username,user_id from user_users;
USERNAME USER_ID
------------------------------ ----------
NEWSCOTT 5
81 row selected.

SQL> select count(*) from session_privs;
COUNT(*)
----------
81
1 row selected.

SQL> select count(*) from session_roles;
COUNT(*)
----------
6
1 row selected.

Duplicating a Database with RMAN

2008-06-03T04:24:00.000-07:00

Operating Environment Windows XP Prof. SP-2
Oracle Release / Version 10.1.0.2.0 Enterprise Edition
ORACLE_BASE d:\oracle
ORACLE_HOME d:\oracle\product\10.1.0
Target Database SID ORA101
Duplicate Database SID TESTDB
RMAN Catalog Database No recovery catalog. Using control file.
Archive Log Mode Enabled
-----------------------------------------------------------------------------------------------
1.Create password File for duplicate database

C:\>orapwd file=d:\oracle\product\10.1.0\db_2\database\pwdtestdb.ora password=oracle entries=5 force=y

----------------------------------------------------------------
2.Create Init.ora file for duplicate database. copy init.ora file from target database (ora101) and edit.

SQL> create pfile='d:\oracle\product\10.1.0\db_2\database\initTESTDB.ora' from spfile;
File created.

#Minimum Changes in init.ora file
db_file_name_convert = ('d:\oracle\product\10.1.0\oradata\ORA101','d:\oracle\product\10.1.0\oradata\TESTDB')
log_file_name_convert = ('d:\oracle\product\10.1.0\oradata\ORA101','d:\oracle\product\10.1.0\oradata\TESTDB')
background_dump_dest='D:\oracle\product\10.1.0\admin\testdb\bdump'
control_files='D:\oracle\product\10.1.0\oradata\testdb\control01.ctl','D:\oracle\product\10.1.0\oradata\testdb\control02.ctl','D:\oracle\product\10.1.0\oradata\testdb\control03.ctl'
core_dump_dest='D:\oracle\product\10.1.0\admin\testdb\cdump'
db_name='testdb'
dispatchers='(PROTOCOL=TCP)(SERVICE=testdbXDB)'
user_dump_dest='D:\oracle\product\10.1.0\admin\testdb\udump'

---------------------------------------------------------------------------------------------
3.Create / Start the Auxiliary Instance (Duplicate Database)

Create all required directory.
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\bdump
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\cdump
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\udump
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\pfile
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\scripts
C:\>mkdir d:\oracle\product\10.1.0\oradata\TESTDB

C:\>ORADIM -NEW -SID TESTDB
Instance created.
C:\>set ORACLE_SID=TESTDB
C:\>SQLPLUS "/ AS SYSDBA"
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Dec 25 12:15:14 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='d:\oracle\product\10.1.0\db_2\database\initTESTDB.ORA';
File created.
SQL> startup force nomount;
ORACLE instance started.
Total System Global Area 180355072 bytes
Fixed Size 788028 bytes
Variable Size 145488324 bytes
Database Buffers 33554432 bytes
Redo Buffers 524288 bytes
SQL>

----------------------------------------------------------------
4.Mount or Open Target Database.(ora101)

C:\>sqlplus sys/oracle@ora101 as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Dec 25 16:33:33 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select status from v$instance;
STATUS
------------
OPEN

-------------------------------------------------
5.Make sure you have valid Target Database backup and Archive redo logs.

C:\>rman target sys@ora101
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
target database Password:connected to target database: ORA101 (DBID=5128390)
RMAN> configure controlfile autobackup on;
using target database controlfile instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> run
2> {
3> backup database;
4> backup archivelog all;
5> }
Starting backup at 25-DEC-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=131 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSAUX01.DBF
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\EXAMPLE01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 25-DEC-06
channel ORA_DISK_1: finished piece 1 at 25-DEC-06
piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\BACKUPSET\2006_12_25\O1_MF_NNNDF_TAG20061225T164042_2RZKPF0Z_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
Finished backup at 25-DEC-06
Starting backup at 25-DEC-06
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=7 recid=1 stamp=610113930
input archive log thread=1 sequence=8 recid=2 stamp=610117104
input archive log thread=1 sequence=9 recid=3 stamp=610120818
input archive log thread=1 sequence=10 recid=4 stamp=610124804
input archive log thread=1 sequence=11 recid=5 stamp=610129547
input archive log thread=1 sequence=12 recid=6 stamp=610130542
channel ORA_DISK_1: starting piece 1 at 25-DEC-06
channel ORA_DISK_1: finished piece 1 at 25-DEC-06
piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\BACKUPSET\2006_12_25\O1_MF_ANNNN_TAG20061225T164224_2RZKSLN0_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:17
Finished backup at 25-DEC-06
Starting Control File and SPFILE Autobackup at 25-DEC-06
piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\AUTOBACKUP\2006_12_25\O1_MF_S_610130562_2RZKT33X_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 25-DEC-06
RMAN>

--------------------------------------------------------------------------
6.Configure TNSNAMES.ORA file for duplicate database.

#TNSNAMES.ORA (ORACLEHOME/NETWORK/ADMIN
TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.64)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)
#LISTENER.ORA ( ORACLEHOME/NETWORK/ADMIN
(SID_LIST=
(SID_DESC=
(ORACLE_HOME=d:\oracle\product\10.1.0\db_2)
(SID_NAME=testdb)
)

cmd>lsnrctl reload

-------------------------------------------------------------------------
7.Connect to RMAN. (Duplicate Database with Different directory structure.)

C:\>rman TARGET=sys/oracle@ora101 AUXILIARY=sys/oracle@testdb
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: ORA101 (DBID=5128390)
connected to auxiliary database: testdb (not mounted)

RMAN>
run
2> {
3> allocate auxiliary channel c1 device type DISK;
4> allocate auxiliary channel c2 device type DISK;
5> allocate auxiliary channel c3 device type DISK;
6> DUPLICATE target database to TESTDB;
7>
}
allocated channel: c1channel c1: sid=160 devtype=DISK
allocated channel: c2channel c2: sid=159 devtype=DISK
allocated channel: c3channel c3: sid=158 devtype=DISK
Starting Duplicate Db at 25-DEC-06
contents of Memory Script:
{
set until scn 380106;
set newname for datafile 1 to
"D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBF";
set newname for datafile 2 to
"D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF";
set newname for datafile 3 to
"D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF";
set newname for datafile 4 to
"D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF";
set newname for datafile 5 to "D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 25-DEC-06
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF
channel c1: restored backup piece 1
piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\BACKUPSET\2006_12_25\O1_MF_NNNDF_TAG20061225T164042_2RZKPF0Z_.BKP tag=TAG20061225T164042channel c1: restore complete
Finished restore at 25-DEC-06
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO01.LOG' ) SIZE 10 M REUSE,
GROUP 2 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO02.LOG' ) SIZE 10 M REUSE,
GROUP 3 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO03.LOG' ) SIZE 10 M REUSE
DATAFILE
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
switch clone datafile all
;
}executing Memory Script
datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=610136063 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=610136063 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=610136063 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=610136064 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF
contents of Memory Script:
{
set until scn 380106;
recover
clone database
delete archivelog ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 25-DEC-06
starting media recovery
archive log thread 1 sequence 12 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\ARCHIVELOG\2006_12_25\O1_MF_1_12_2RZKSFPG_.ARCarchive log thread 1 sequence 13 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\ARCHIVELOG\2006_12_25\O1_MF_1_13_2RZPF26C_.ARCarchive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\ARCHIVELOG\2006_12_25\O1_MF_1_12_2RZKSFPG_.ARC thread=1 sequence=12archive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\ARCHIVELOG\2006_12_25\O1_MF_1_13_2RZPF26C_.ARC thread=1 sequence=13
media recovery complete
Finished recover at 25-DEC-06
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 180355072 bytes
Fixed Size 788028 bytes
Variable Size 145488324 bytes
Database Buffers 33554432 bytes
Redo Buffers 524288 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO01.LOG' ) SIZE 10 M REUSE,
GROUP 2 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO02.LOG' ) SIZE 10 M REUSE,
GROUP 3 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO03.LOG' ) SIZE 10 M REUSE
DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF";
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF";
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF";
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF";
switch clone datafile all;
}
executing Memory Script
cataloged datafile copy
datafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF recid=1 stamp=610136100
cataloged datafile copydatafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF recid=2 stamp=610136100
cataloged datafile copydatafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF recid=3 stamp=610136101
cataloged datafile copydatafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF recid=4 stamp=610136102
datafile 2 switched to datafile copyinput datafilecopy recid=1 stamp=610136100 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=2 stamp=610136100 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy recid=3 stamp=610136101 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBFdatafile 5 switched to datafile copyinput datafilecopy recid=4 stamp=610136102 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 25-DEC-06
RMAN>

----------------------------------------------------------
8.Create Tempfile for Temporary Tbs.

C:\>SQLPLUS SYS/ORACLE@TESTDB AS SYSDBA
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Dec 25 18:25:16 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
ProductionWith the Partitioning, OLAP and Data Mining options
SQL> alter tablespace temp add tempfile
2 'd:\oracle\product\10.1.0\oradata\TESTDB\temp01.dbf' size 10m;
Tablespace altered.

Copy Table From OneDB to AnotherDB

2008-06-03T04:23:00.000-07:00

You can use Three Method.
1.Database link
2.Export or Import
3.Copy Command.

------------------------------------------
1.Database link
sql>conn u1/u1@db2
sql>create table a as select * from all_objects where rownum <= 10; table created. For database link you have "database link" or "create session" privs. sql>conn u1/u1@db1
connected
sql>create database link db2
connect to u1
identified by u1
using 'db2';
database link created
sql>create table t as select * from a@db2;
table created
sql>select count(*) from t;
----------------------------------------------------------
2.Through Export or Import Utilities.
------------------------------------
SQL> conn a1/a1@db1
Connected.
SQL> create table d as select * from all_objects where rownum <= 10; Table created. SQL> host exp a1/a1@db1 tables=d file=d:\oracle\d.dmp log=d:\oracle\d.log
Export: Release 10.1.0.2.0 - Production on Sat Dec 23 18:24:52 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table D 10 rows exported
Export terminated successfully without warnings.
SQL> conn a1/a1@db2
Connected.
SQL> host imp a1/a1@db2 fromuser=a1 touser=a1 file=d:\oracle\d.dmp log=d:\oracle
\d.log ignore=y
Import: Release 10.1.0.2.0 - Production on Sat Dec 23 18:25:58 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table "D" 10 rows imported
Import terminated successfully without warnings.
------------------------------------------------------
3.Through Copy Command.
----------------------
SQL> conn a1/a1@db1
Connected.
SQL> create table m as select * from all_objects where rownum <= 10; Table created. SQL> con a1/a1@db2
Connected.
SQL> copy from a1/a1@db1 to a1/a1@db2 -
> create m -
> using select * from m;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table M created.
10 rows selected from a1@db1.
10 rows inserted into M.
10 rows committed into M at a1@db2.

Clone DB on New Host with HotBkp

2008-06-03T04:20:00.001-07:00

Source Database: Windows XP/Oracle 10gr1
Target Database: Windows 2003/Oracle 10gr1

Source Database:

1. Take Controlfile Backup

SQL> alter session set tracefile_identifier=my_bkp_control_file;

Session altered.

SQL> alter database backup controlfile to trace;

Database altered.

SQL> --check udump folder ($ORACLE_HOME/admin/udump)
SQL> --filename :: _ora_xxx_my_bkp_control_file.trc

2. Take datafiles backup
SQL> alter database begin backup;

SQL> --copy all *.dbf files to BACKUP folder
Note: except TEMP tbs

SQL> alter database end backup;

SQL> alter system switch logfile;

System altered.
Note: Must Switch logfile after end backup command

3. Create init.ora from spfile

SQL> create pfile='c:\tmp\init.ora' from spfile;

Target Database:
1. Change Below parameters location in init.ora file
bdump,udump,cdump
log_archive_dest_n
control_files

2. Restore datafile and controlfile and init.ora backup to target database

3. Set ORACLE_SID=[sid]
C:\>set oracle_sid=orcl

4. Create Oracle Services through ORADIM utility
C:\>oradim -new -sid orcl -startmode auto
Instance created.

5. Create Password File
C:\>orapwd file=d:\oracle\product\10.1.0\db_1\database\pwdorcl.ora password=oracle entries=5

5. startup instance with nomount through init.ora file.
C:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Sun Sep 2 16:35:10 2007

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

SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup nomount pfile='d:\orcl\admin\pfile\init.ora';
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 789060 bytes
Variable Size 90437052 bytes
Database Buffers 192937984 bytes
Redo Buffers 1048576 bytes

6. Create controlfile from trace file
Note: Create Controlfile with RESETLOGS Options

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 454
7 LOGFILE
8 GROUP 1 'd:\ORCL\oradata\REDO01.LOG' SIZE 10M,
9 GROUP 2 'd:\ORCL\oradata\REDO02.LOG' SIZE 10M,
10 GROUP 3 'd:\ORCL\oradata\REDO03.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'd:\ORCL\oradata\SYSTEM01.DBF',
14 'd:\ORCL\oradata\UNDOTBS01.DBF',
15 'd:\ORCL\oradata\SYSAUX01.DBF',
16 'd:\ORCL\oradata\USERS01.DBF'
17 CHARACTER SET WE8MSWIN1252;

Control file created.

7. apply neccessary archivelog file
SQL> recover database using backup controlfile;
ORA-00279: change 529461 generated at 09/03/2007 12:54:37 needed for thread 1
ORA-00289: suggestion : D:\ORCL\ARC\LOG_1_29_632229757.ARC
ORA-00280: change 529461 for thread 1 is in sequence #29

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 529600 generated at 09/03/2007 12:59:51 needed for thread 1
ORA-00289: suggestion : D:\ORCL\ARC\LOG_1_30_632229757.ARC
ORA-00280: change 529600 for thread 1 is in sequence #30
ORA-00278: log file 'D:\ORCL\ARC\LOG_1_29_632229757.ARC' no longer needed for
this recovery

ORA-00308: cannot open archived log 'D:\ORCL\ARC\LOG_1_30_632229757.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

8. Perform Incomplete Recovery with UNTIL CANCEL
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 529600 generated at 09/03/2007 12:59:51 needed for thread 1
ORA-00289: suggestion : D:\ORCL\ARC\LOG_1_30_632229757.ARC
ORA-00280: change 529600 for thread 1 is in sequence #30

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

9. Open database with resetlogs
SQL> alter database open resetlogs;

Database altered.

Note: All archivelog files needed after database backup for recovery.

remote_login_passwordfile

2008-06-03T04:19:00.002-07:00

What is "remote_login_passwordfile" parameter and why we use" ?

We have two options for Super User "SYS" password authentication

1. Operating System authentication
2. Password file authentication

Password file authentication

When we use password file authentication then we have to create PASSWORD FILE for SYS user.

How to create password file ?

commandpromt>orapwd file=$ORACLE_HOME/database/orapwd.ora password=pwd entries=n force=y/n

Note:
entries=n "maximum number of distinct DBAs and OPERs (opt)"
force=y ""y" if password file already created"

When use "REMOTE_LOGIN_PASSWORDFILE" file parameter

When we using Password file authentication for "sys" user then we have to use "remote_login_password" parameter.

There are three value for this parameters

1. EXCLUSIVE

This is default value. if parameter set "exclusive" then we can use password file for authentication and connect to remote machine with sys user.Only an EXCLUSIVE file can contain the names of users other than SYS. Using an EXCLUSIVE password file lets you grant SYSDBA and SYSOPER system privileges to individual users and have them connect as themselves.

2. NONE

If this parameter set "NONE" means passwordfile doesn't exists

3. SHARED

A SHARED password file can be used by multiple databases running on the same server. However, the only user recognized by a SHARED password file is SYS. You cannot add users to a SHARED password file. All users needing SYSDBA or SYSOPER system privileges must connect using the same name, SYS, and password. This option is useful if you have a single DBA administering multiple databases.

Note: In documentation "remote_login_passwordfile" default value is 'NONE' but when we create database through DBCA then it will take 'EXCLUSIVE' as default value.

Multiple Temporary Tablespaces: Using Tablespace Groups

2008-06-03T04:19:00.001-07:00

Multiple Temporary Tablespaces: Using Tablespace Groups

You can create a temporary tablespace group that can be specifically assigned to users in the same way that a single temporary tablespace is assigned. A tablespace group can also be specified as the default temporary tablespace for the database.

A tablespace group has the following characteristics:

1. It contains at least one tablespace. There is no explicit limit on the maximum number of tablespaces that are contained in a group.

2. It shares the namespace of tablespaces, so its name cannot be the same as any tablespace.

3. You can specify a tablespace group name wherever a tablespace name would appear when you assign a default temporary tablespace for the database or a temporary tablespace for a user.

How to create temporary tablespace group

Note: You do not explicitly create a tablespace group. Rather, it is created implicitly when you assign the first temporary tablespace to the group. The group is deleted when the last temporary tablespace it contains is removed from it.

SQL> ALTER TABLESPACE temp TABLESPACE GROUP group1;

Tablespace altered.

SQL> CREATE TEMPORARY TABLESPACE temp1
2 TEMPFILE 'c:\oracle\product\10.1.0\oradata\temp02.dbf' size 5m
3 TABLESPACE GROUP group2;

Tablespace created.

SQL> desc dba_tablespace_groups;
Name Null? Type
----------------------------------------- -------- ----------------------------

GROUP_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME NOT NULL VARCHAR2(30)

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP
GROUP2 TEMP1

How to change TABLESPACE GROUP

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP
GROUP2 TEMP1

SQL> ALTER TABLESPACE temp1 TABLESPACE GROUP group1;

Tablespace altered.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP
GROUP1 TEMP1

How to assign TABLESPACE GROUP to particular user.

SQL> select temporary_tablespace
2 from dba_users
3 where username = 'SCOTT';

TEMPORARY_TABLESPACE
------------------------------
GROUP1

SQL> alter user scott temporary tablespace group2;

How to delete temporary tablespace groups

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP
GROUP2 TEMP1

SQL> alter tablespace temp1 tablespace group '';

Tablespace altered.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP

How to Assigning a Tablespace Group as the Default Temporary Tablespace

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 TEMP
GROUP1 TEMP1

SQL> alter database ORCL default temporary tablespace group1;

Database altered.

Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.

Renaming Tablespaces

2008-06-03T04:18:00.004-07:00

Renaming Tablespaces

Using the RENAME TO clause of the ALTER TABLESPACE, you can rename a permanent or temporary tablespace.

SQL> alter tablespace users RENAME TO userts;

Tablespace altered.

When you rename a tablespace the database updates all references to the tablespace name in the data dictionary, control file, and (online) datafile headers. The database does not change the tablespace ID so if this tablespace were, for example, the default tablespace for a user, then the renamed tablespace would show as the default tablespace for the user in the DBA_USERS view.

The following affect the operation of this statement:

The COMPATIBLE parameter must be set to 10.0 or higher.

If the tablespace being renamed is the SYSTEM tablespace or the SYSAUX tablespace, then it will not be renamed and an error is raised.

SQL> alter tablespace system RENAME TO system1;
alter tablespace system RENAME TO system1
*
ERROR at line 1:
ORA-00712: cannot rename system tablespace

SQL> alter tablespace sysaux RENAME TO system1;
alter tablespace sysaux RENAME TO system1
*
ERROR at line 1:
ORA-13502: Cannot rename SYSAUX tablespace

If any datafile in the tablespace is offline, or if the tablespace is offline, then the tablespace is not renamed and an error is raised.

SQL> alter tablespace userts RENAME TO users;
alter tablespace userts RENAME TO users
*
ERROR at line 1:
ORA-01135: file 4 accessed for DML/query is offline
ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF'

If the tablespace is read only, then datafile headers are not updated. This should not be regarded as corruption; instead, it causes a message to be written to the alert log indicating that datafile headers have not been renamed. The data dictionary and control file are updated.

SQL> alter tablespace userts read only;

Tablespace altered.

SQL> alter tablespace userts RENAME TO users;

Tablespace altered.
alert_orcl.log
Sat Sep 08 19:52:42 2007
alter tablespace userts RENAME TO users
Tablespace 'USERTS' is renamed to 'USERS'.
Tablespace name change is not propagated to file headers because the tablespace is read only.

If the tablespace is the default temporary tablespace, then the corresponding entry in the database properties table is updated and the DATABASE_PROPERTIES view shows the new name.

SQL> select property_value
2 from database_properties
3 where property_name like '%TEMP%';

PROPERTY_VALUE
------------------------------------------------

TEMP2

SQL> alter tablespace temp2 rename to temp;

Tablespace altered.

SQL> select property_value
2 from database_properties
3 where property_name like '%TEMP%';

PROPERTY_VALUE
------------------------------------------------

TEMP

If a traditional initialization parameter file (PFILE) is being used then a message is written to the alert file stating that the initialization parameter file must be manually changed.

SQL> --when we rename undo tablespace and instance is startup with PFILE then
SQL> --rename entry is recorded in alert_sid.log file.
SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> --database is startup from pfile
SQL> alter tablespace undotbs1 rename to undotbs;

Tablespace altered.

Sat Sep 08 20:14:52 2007
Tablespace 'UNDOTBS1' is renamed to 'UNDOTBS'.
PFILE is being used. It must be manually modified to reflect the new tablespace name if the old tablespace name is specified as UNDO_TABLESPACE in the PFILE.

Dropping a Database

2008-06-03T04:18:00.003-07:00

Dropping a database involves removing its datafiles, redo log files, control files, and initialization parameter files. The DROP DATABASE statement deletes all control files and all other database files listed in the control file. To use the DROP DATABASE statement successfully, all of the following conditions must apply:

The database must be mounted and closed.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

The database must be mounted as RESTRICTED.

SQL> select logins from v$instance;

LOGINS
----------
RESTRICTED

The DROP DATABASE statement has no effect on archived log files, nor does it have any effect on copies or backups of the database. It is best to use RMAN to delete such files. If the database is on raw disks, the actual raw disk special files are not deleted.

SQL> drop database;

Database dropped.

Note: Manually delete below folder.
1. $ORACLE_BASE/oradata/[sid]
2. $ORACLE_BASE/admin/[sid]
3. $ORACLE_BASE/flash_recovery_area/[sid]
4. If database created through DBCA then you have to also delete ORACLE_SID through DBCA otherwise you will get error message when you create new database with same ORACLE_SID.

How to Enable ARCHIVELOG mode

2008-06-03T04:18:00.001-07:00

Benefits of ARCHIVELOG mode.

1. We can able to take database backup ONLINE.
2. No Need to Down Oracle DB Server
3. We can better Manage our Backup Policy through RMAN
4. Limited DBA task
5. We can able to RECOVER our database point-in-time

How can enable ARCHIVELOG MODE in 9iR1.

When we create new database then database is created NOARCHIVELOG mode by default.

shutdown database

SQL>shutdown immediate;

Edit below parameters in pfile(init.ora) file.

1. LOG_ARCHIVE_START=TRUE

Create New SPFILE from Modified pfile(init.ora)

SQL> create SPFILE from PFILE;

Startup Database in Mount Mode

SQL> startup mount;

Change Database log mode from NOARCHIVELOG to ARCHIVELOG

SQL> alter database archivelog;

Open DB for normal use

SQL> alter database open;

check archive log status

Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\Oracle\product\RDBMS
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1

Default Archivelog destination is :$ORACLE_HOME/rdbms.

How can enable ARCHIVELOG MODE in 10gr1

In 10g Archivelog mode enabling is easy becuase we need to just turn DB log mode and archive log is automatic start.

shutdown database

SQL> shutdown immediate;

startup database in mount mode

SQL> startup mount;

Change DB log mode

SQL> alter database archivelog;

Open DB for normal use

SQL> alter database open;

check archivelog status

SQL> archive log list

Default Archive log destination.

1. 10g introduce new feature called FRA (flashback recovery area) so all archivelog files created in FRA.

2. If your not using FRA then all files created in $ORACLE_HOME/database

We can check database archivelog mode through below query

SQL> select log_mode from v$database;
or
SQL> archive log list

Datafile Recovery

2008-06-03T04:17:00.000-07:00

Database Oracle 10gr1
Backup Method "user managed"
Archivelog "ENABLE"

When Any datafile is lost due to media failure then through below procedure we can perform media recovery.

Requirements
Recent Valid Database Backup
All archivelog after backup.

Due to Media failure database goes down and everything stop then after fix media problem and trying to open Oracle Database

SQL> conn sys as sysdba
Enter password:
Connected.

SQL> shutdown abort; ---Dummy purpose use ABORT option.
ORACLE instance shut down.

Trying to open database

SQL> startup

But suddenly get below errors message.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF'

Check v$recover_file for more infomation

SQL> column error format a25
SQL> /

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ------------------------- ---------- ---------
4 ONLINE ONLINE FILE NOT FOUND 0

Note: Error column indicate "file not found" means due to media failure we lost our user datafile.

Restore User Datafile from BACKUP LOCATION.

SQL> set autorecovery on

Note: Oracle Corp. recommended set autorecovery on before perform recovery.

SQL> recover tablespace users;
ORA-00279: change 1308298 generated at 09/10/2007 11:19:22 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00140_0632336321.001
ORA-00280: change 1308298 for thread 1 is in sequence #140
[output cut]
Log applied.
Media recovery complete.

Note: All neccessary ARCHIVELOG files applied automatically and media recovery is complete.

SQL> alter database open;

Now open database for normal use.

Cold Backup with RMAN

2008-06-03T04:16:00.004-07:00

Database Cold/offline Backup with RMAN

Database Require in mount mode when cold backup taken through RMAN.

C:\> set ORACLE_SID=sidname
C:\> rman TARGET=SYS

RMAN> run
{
#shutdown database and startup with mount mode for backup.
shutdown immediate;
startup mount;
#perform full database backup
backup database;
#open database for normal use
sql 'alter database open';
#backup current controlfile
backup current controlfile;
sql 'alter database backup controlfile to TRACE';
}

All redolog group lost

2008-06-03T04:16:00.003-07:00

Oracle Database: 10gr1
OS: Windows XP

Due to media failure if we lost all redolog group then recovery depand on database closed condition

When database cloase CONSISTENTLY OR CLEANLY

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> host del c:\oracle\product\10.1.0\oradata\noorcl\*.log

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 789060 bytes
Variable Size 90961340 bytes
Database Buffers 192937984 bytes
Redo Buffers 524288 bytes
Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\NOORCL\REDO01.LOG'

SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

When shutdown close with INCONSISTENT or DIRTY stage
SQL> shutdown abort;
ORACLE instance shut down.
all redolog files lost due to media failure.
SQL> host del c:\oracle\product\10.1.0\oradata\noorcl\*.log

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 789060 bytes
Variable Size 90961340 bytes
Database Buffers 192937984 bytes
Redo Buffers 524288 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1:
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\NOORCL\REDO03.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> --now becuase database close was inconsistent so we have to restore last va
lid backup and open database with resetlogs options.
SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Restore last valid backup
SQL> host copy c:\backup\noorcl\*.* c:\oracle\product\10.1.0\oradata\noorcl\
c:\backup\noorcl\CONTROL01.CTL
c:\backup\noorcl\CONTROL02.CTL
c:\backup\noorcl\CONTROL03.CTL
c:\backup\noorcl\SYSAUX01.DBF
c:\backup\noorcl\SYSTEM01.DBF
c:\backup\noorcl\TEMP01.DBF
c:\backup\noorcl\UNDOTBS01.DBF
c:\backup\noorcl\USERS01.DBF
8 file(s) copied.

SQL> startup mount
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 789060 bytes
Variable Size 90961340 bytes
Database Buffers 192937984 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> recover database until cancel
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

Control file Backup Option

2008-06-03T04:16:00.001-07:00

Controlfile Backup Option

when database running in NO ARCHIVE LOG MODE

1. Shutdown database with immediate,transactional,normal option.
2. through OS copy command copy controlfiles to backup location
3. startup database.

when database running in ARCHIVELOG MODE

Text based controlfile backup

SQL> alter session set tracefile_identifier = ctrlbkp;

Session altered.

SQL> alter database backup controlfile to trace;

Database altered.

Note
1. Tracefile created with tracefile_identifier name like "ORA_SID_XXX_ctrlbkp.trc".
2. Above created backup controlfile is text file.

Exact copy or binary controlfile backup

alter database backup controlfile to 'PATH';

Note
Above statement created binary format controlfile backup.

ORA-19809: limit exceeded for recovery files

2008-06-03T04:15:00.001-07:00

When we are trying to connect to database then getting below error message.
Database 10gr1

SQL> conn system/oracle@orcl

ORA-00257: archiver error. Connect internal only, until freed.

Note: When check alert_sid.log file and come to know flash_recovery_area is full.
then increase "db_recovery_file_dest_size" parameter value

SQL> alter system set db_recovery_file_dest_size =6g;

System altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16038: log 1 sequence# 20351 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\REDO01.LOG'

Note: After increase "db_recovery_file_dest_size" parameter value getting above error.

So need to check flash_recovery_area V$recovery_file_dest for how much space needed.

NAME SPACE USED
---------------------------------------- ---------- ----------
D:\oracle\product\10.1.0\Flash_recovery 6144 8603

Note: need to increase more 3 gb size in flash_recovery_area.

SQL> alter system set db_recovery_file_dest_size = 10g;

System altered.

SQL> alter database open;

Database altered.

Sending-email-from-an-Oracle-10g-Server

2008-06-03T04:14:00.000-07:00

When we configure Oracle E-MAIL notification method through OEM then it is not include notification for DB shutdown or startup events. For these two events we have to configure manually.

Connect sys as sysdba user and run two scripts for install and configure utl_mail package

SQL> conn sys@orcl as sysdba
Enter password: ******
Connected.
SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\utlmail.sql

Package created.

Synonym created.

SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\prvtmail.plb;

Package body created.

No errors.

Set SMTP_OUT_SERVER parameter for smtp_exchange_server. This parameter is not modifiable means we have to bounce our database to set this parameter

SQL> alter system set smtp_out_server = 'smtp_exchange_server_name' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

Grant EXECUTE privs to user which use utl_mail package.

SQL> grant execute on utl_mail to scott;

Grant succeeded.

Create two trigger for startup and shutdown event on database which send us email notification

SQL> create or replace trigger scott.db_shutdown
2 before shutdown on database
3 begin
4 sys.utl_mail.send (
5 sender =>'try@yahoo.com',
6 recipients =>'star_try@yahoo.com',
7 subject => 'Oracle Database Server DOWN',
8 message => 'May be DB Server Down for maintenance’||
‘ but also contact to DBA for further details. ’
9 );
10 end;
11 /

Trigger created.

SQL> create or replace trigger scott.db_startup
2 after startup on database
3 begin
4 sys.utl_mail.send (
5 sender =>'try@yahoo.com',
6 recipients =>'star_try@yahoo.com',
7 subject => 'Oracle Database Server UP',
8 message => 'DB Server OPEN for normal use.'
9 );
10 end;
11 /

Trigger created.

Querying V$ Views to Obtain Backup Information

2008-06-03T04:13:00.001-07:00

Before making a backup, you must identify all the files in your database and decide what to back up. Several V$ views can provide the necessary information.

Listing Database Files Before a Backup

select name from v$datafile
union all
select member from v$logfile
union all
select name from v$controlfile;

Determining Datafile Status for Online Tablespace Backups

You can check V$BACKUP views to determine datafile is part of backup or not.
it is also usefull when you database startup after instance failure through this view you can know datafile is part of online backup when database is open.

V$backup view is not useful when current controlfile is created from restored backup or RE-CREATE statment becuase it is not content information about backup datafile.

Through below query we can know status of datafile backup status

SELECT t.name AS "TB_NAME", d.file# as "DF#", d.name AS "DF_NAME", b.status
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE'

Making User-Managed Backups of Online/Offline Tablespaces and Datafiles

2008-06-03T04:12:00.000-07:00

We can take all or individual datafiles backup when database is open but the procedure is differs depending on whether the online tablespace is READ-WRITE or READ-ONLY mode.

Note: You should not backup temporary tablespace

Tablespace is READ-WRITE mode.
we must put tablespace in backup mode when tablespace is read-write mode, online and database is open.

alter tablespace tablespace_name begin backup;
through OS utility copy datafiles to backup location
alter tablespace tablespace_name end backup;
alter system archive log current;

For multiple tablespace backup we can use

alter database begin backup;
through OS utility copy all datafiles to backup location
alter database end backup;
alter system archive log current;

Ending a Backup After an Instance Failure or SHUTDOWN ABORT

During backup period if database instance failure or power failure or shutdown abort command is issue. and when we try to open database after fix mention error then we will get below error message

ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF'

Then first check V$BACKUP views.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 3224323 27-SEP-07
2 ACTIVE 3224323 27-SEP-07
3 ACTIVE 3224323 27-SEP-07
4 ACTIVE 3224323 27-SEP-07

All datafiles is still in backup mode...oh, so we need to take out all datafiles from backup mode.

SQL> alter database end backup;

Database altered.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 3224323 27-SEP-07
2 NOT ACTIVE 3224323 27-SEP-07
3 NOT ACTIVE 3224323 27-SEP-07
4 NOT ACTIVE 3224323 27-SEP-07

SQL> alter database open;

Database altered.

Keep in mind

1. Do not use ALTER DATABASE END BACKUP if you have restored any of the affected files from a backup.

2. We can only use "alter database end backup" statement when database is mount mode.

3. We can also use "RECOVER DATABASE" command instead of "alter database end backup" statement but it is slow process.

Making User-Managed Backups of Read-Only Tablespaces

We tablespace is READ-ONLY mode then no need to put tablespace in BACKUP mode. becuase oracle server already prevent to make changes on tablespace.

Making User-Managed Backups of Offline Tablespaces and Datafiles
We can take all or individual tablespace backup while tablespace is OFFLINE. and all other tablespace is remain available and open for systemwide use.

Procedure

Identify datafiles associate with tablespace

SQL> select tablespace_name,file_name
2 from dba_data_files
3 where tablespace_name = 'USERS';

TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
USERS C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF

SQL> alter tablespace users offline normal;

Tablespace altered.

SQL> --copy datafiles to backup location
SQL> alter tablespace users online;

Tablespace altered.

SQL> alter system archive log current;

System altered.

Keep in mind

1. We can't take system tablespace or any active undo segment tablespace OFFLINE.
2. Assume that a table is in tablespace Primary and its index is in tablespace Index. Taking tablespace Index offline while leaving tablespace Primary online can cause errors when DML is issued against the indexed tables located in Primary. The problem only manifests when the access method chosen by the optimizer needs to access the indexes in the Index tablespace.
3. If you took the tablespace offline using temporary or immediate priority, then you cannot bring the tablespace online unless you perform tablespace recovery.

Posted by Mohammed Taj at 12:33:00 PM 0 comments
Labels: User Managed Backup
Making User-Managed Backups of the Whole Database
We can take two types of backup
1. Consistent / coldbackup / offline
2. Inconsistent / hotbackup / online

How to take consistent backup and what is consistent backup.

Consistent backup means all datafile , controlfile, redolog file having same SCN number. and consistent must be taken after database shutdown with NORMAL, TRANSACTIONAL, IMMEDIATE options.

For consistent backup no need to operate database in archivelog mode or we can say if database running in no archivelog mode then we can use COLDBACKUP/CONSISTENT/OFFLINE backup method.

Procedure

1. shutdown IMMEDIATE;
2. COPY ALL *.dbf, *.log, *.ctl files
3. COPY spfilesid.ora,initsid.ora files
4. startup

Inconsistent or hotbackup or online backup

When database availability require 24x7 so we have to use ONLINE backup method.
and when we take online backup that backup called is inconsistent backup.
for online backup database MUST BE running in archivelog mode.

Control files play a crucial role in database restore and recovery. For databases running in ARCHIVELOG mode, Oracle Corporation recommends that you back up control files with the ALTER DATABASE BACKUP CONTROLFILE TO 'filename' statement.

Procedure

Hotbackup on Linux

Making User-Managed Backups

2008-06-03T04:11:00.001-07:00

Making User-Managed Backups of Archived Redo Logs

To save disk space in our primary location, we may need to backup archived log file to tape drive or alternative disk.
if we have multiple archivelog location then only back up one copy of each log sequence number.

To back up archived redo logs:
To determine which archived redo log files that the database has generated

SELECT THREAD#,SEQUENCE#,NAME FROM V$ARCHIVED_LOG;

NOte: backup up copy of archivelog through OS utility.

Making Logical Backups with Oracle Export Utilities

Oracle import and export utilities move Oracle data in and out of Oracle databases. Export utilities write exported database objects to operating system files in an Oracle-proprietary format. Import utilities can read the files produced by export utilities and re-create database objects. Logical exports of data can be a useful supplement to physical database backups in some situations, especially in backing up recovery catalog databases.

There are two sets of Oracle database import and export utilities: Original Import and Export (which were used in previous releases(<= 10g) and Data Pump Import and Export (new for Oracle Database Release 10g). The Data Pump utilities offer better performance and more complete support of features of Oracle Database Release 10g.

Export
Import
Export Dump Pump

Making User-Managed Backups of Miscellaneous Oracle Files

Always back up initialization parameter files, networking and configuration files, and password files. If a media failure destroys these files, then you may have difficulty re-creating your environment. For example, if you back up the database and server parameter file but do not back up the networking files, then you can restore and recover the database but will not be able to authenticate users through Oracle Net until you re-create the networking files.

INIT PARAMETER FILES

Keeping Records of Current and Backup Database Files

One of the most important aspects of user-managed backup and recovery is keeping records of all current database files as well as the backups of these files. For example, you should have records for the location of the following files:

Datafiles and control files
Online and archived redo logs (note that online logs are never backed up)
Initialization parameter files
Password files
Networking-related files

Recording the Locations of Datafiles, Control Files, and Online Redo Logs

select name from v$datafile
union all
select name from v$controlfile
union all
select member from v$logfile;

Recording the Locations of Archived Redo Logs

SELECT NAME, VALUE
FROM V$PARAMETER
WHERE NAME LIKE 'log_archive_dest_%'
AND VALUE IS NOT NULL

Determine the format for archived logs by running SHOW as follows

SHOW PARAMETER LOG_ARCHIVE_FORMAT

Autotrace in SQLPLUS

2008-06-03T04:10:00.001-07:00

What is AUTOTRACE

In SQL*Plus you can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after a successful SQL DML statement, such as SELECT, DELETE, UPDATE or INSERT. It is useful for monitoring and tuning the performance of these DML statements.

How to configure AUTOTRACE in SQLPLUS for database user

1. Change directory path and connect with SYSTEM user to database
C:\>cd c:\oracle\product\10.1.0\db_1\rdbms\admin

C:\Oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nolog

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Oct 16 17:08:20 2007

(c) Copyright 2001 Oracle Corporation. All rights reserved.

SQL> conn system/manager
Connected.

2. run UTLXPLAN.SQL script for plan table.
SQL> @utlxplan.sql

3. Create public synonym for plan table or grant all privilege.
SQL> create public synonym plan_table for plan_table;

SQL> grant all on plan_table to public;

Grant succeeded.

4. Exit and again change directory path and connect with SYS user to database
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

C:\Oracle\product\10.1.0\Db_1\RDBMS\ADMIN>cd\

C:\>cd c:\oracle\product\10.1.0\db_1\sqlplus\admin

C:\Oracle\product\10.1.0\Db_1\sqlplus\admin>sqlplus /nolog

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Oct 16 17:12:07 2007

(c) Copyright 2001 Oracle Corporation. All rights reserved.

SQL> conn sys as sysdba
Enter password:
Connected.

5. run plustrce script and script must be run with SYS user.
SQL> @plustrce
SQL>
SQL> drop role plustrace;

Role dropped.

SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> set echo off

6. Grant plustrace role to public.
SQL> grant plustrace to public;

Grant succeeded.

Configuring the Autotrace Report

SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the
default.
SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows only the optimizer
execution path.
SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL
statement execution statistics.
SET AUTOTRACE ON - The AUTOTRACE report includes both the
optimizer execution path and the SQL
statement execution statistics.
SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the
printing of the user's query output, if any.

Database Statistics for SQL Statements

recursive calls
Number of recursive calls generated at both the user and system level. Oracle maintains tables used for internal processing. When Oracle needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.

db block gets
Number of times a CURRENT block was requested.

consistent gets
Number of times a consistent read was requested for a block.

physical reads
Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.

redo size
Total amount of redo generated in bytes.

bytes sent via SQL*Net to client
Total number of bytes sent to the client from the foreground processes.

bytes received via SQL*Net from client
Total number of bytes received from the client over Oracle Net.

SQL*Net roundtrips to/from client
Total number of Oracle Net messages sent to and received from the client.

sorts (memory)
Number of sort operations that were performed completely in memory and did not require any disk writes.

sorts (disk)
Number of sort operations that required at least one disk write.

rows processed
Number of rows processed during the operation.

EXAMPLE

SQL> set autotrace on
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DEPT'

Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
2 consistent gets
0 physical reads
0 redo size
702 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

SQL> set autotrace traceonly
SQL> select * from dept;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DEPT'

Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
2 consistent gets
0 physical reads
0 redo size
702 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

SQL> set autotrace trace explain
SQL> select * from dept;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DEPT'

SQL> set autotrace trace statistics
SQL> select * from dept;

Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
2 consistent gets
0 physical reads
0 redo size
702 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

EXECUTION PLAN

2008-06-03T04:09:00.000-07:00

To run a DML statement, Oracle might need to perform many steps. Each of these steps either retrieves rows of data physically from the database or prepares them in some way for the user issuing the statement. The combination of the steps Oracle uses to run a statement is called an execution plan. An execution plan includes an access method for each table that the statement accesses and an ordering of the tables (the join order). The steps of the execution plan are not performed in the order in which they are numbered.

How to generate execution plan ?

There are three ways to view execution plan

1. Through AUTOTRACE

SQL> set autotrace trace exp
SQL> select * from emp;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=51
8)

1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes
=518)

2. Through DBMS_XPLAN package

SQL> explain plan for select * from emp;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

3. Through trace file (trace event 10046,sql_trace=true) and format trace file through TKPROF utility.

What is 10046 trace events

whenever we want to tune any sql statement and want to know about waits and bind variable ...then we can use 10046 trace events.

How to use 10046 trace events

First define trace identifier for generated trace files so we can easily identify our trace files in UDUMP folder.

SQL> alter session set tracefile_identifier='MYSESSION';

Enable 10046 tracing.

SQL> alter session set events '10046 trace name context forever, level 8';

Now execute SQL Statements...

select e.empno, e.ename, d.dname, d.deptno
from emp e , dept d
where e.deptno = d.deptno;

Disable 10046 tracing.

SQL> alter session set events '10046 trace name context off';

Check UDUMP directory for generated trace file.
file name like "XXXX_MYSESSION.TRC"

C:\Oracle\admin\ora9i\udump>dir *MYSESSION.trc

Now generated trace file is RAW trace file and very hard to read and understand ...so through TKPROF utility create readable output file for generated trace file.

C:\Oracle\admin\ora9i\udump>tkprof ORA01904_MYSESSION.TRC c:\output.log

TKPROF: Release 10.1.0.5.0 - Production on Wed Oct 17 19:01:44 2007

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

Open c:\output.log file and analyze it. below is content of output.log file

select e.empno, e.ename, d.dname, d.deptno
from emp e , dept d
where e.deptno = d.deptno

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.29 0.31 2 0 2 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 3 30 2 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.29 0.31 5 30 4 14

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 66

---Below is execution plan

Rows Row Source Operation
------- ---------------------------------------------------
14 NESTED LOOPS
14 TABLE ACCESS FULL EMP
14 TABLE ACCESS BY INDEX ROWID DEPT
14 INDEX UNIQUE SCAN (object id 32119)

---Waits time information.

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 3 0.00 0.00
SQL*Net message from client 2 56.49 56.51

10046 Trace Level

Level 1
Basic trace level. Like the standard SQL_TRACE trace file. Provides statistics for parse, execute, fetch, commit and rollback database calls.

Level 4
Displays bind variables

Level 8
Displays wait statistics

Level 12
Displays wait statistics and bind variables

Interpreting Your Statspack Report

2008-06-03T04:08:00.000-07:00

The Statspack Header
The beginning of the statspack report shows you some basic information about your instance including the database name, instance name, DB ID, version, host and the start and end times of the snapshots used in your report. Here is an example:

STATSPACK report for

DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ORCL 2586436430 ORCL 1 9.2.0.4.0 NO localhost

Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 4873 13-Dec-05 05:00:05 110 37.4
End Snap: 4875 13-Dec-05 07:00:04 651 203.7
Elapsed: 119.98 (mins)

Cache Sizes
The next section, Cache Sizes, shows you some of your instance settings including: Buffer Cache (DB_CACHE_SIZE), Standard Block Size (DB_BLOCK_SIZE), Shared Pool Size (SHARED_POOL_SIZE), and Log Buffer (LOG_BUFFER). These are all instance parameters which you can modify in your spfile/pfile. :

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 3,008M Std Block Size: 8K
Shared Pool Size: 1,920M Log Buffer: 10,240K

Load Profile
The "Load Profile" section shows you the load on your instance per second and per transaction. You can compare this section between two Statspack Reports to see how the load on your instance is increasing or decreasing over time.

Redo Size & Block Changes Increase: If you see an increase here then more DML statements are taking place (meaning your users are doing more INSERTs, UPDATEs, and DELETEs than before.
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 352,535.71 8,517.66
Logical reads: 202,403.30 4,890.29
Block changes: 2,713.47 65.56
Physical reads: 44.22 1.07
Physical writes: 27.46 0.66
User calls: 787.32 19.02
Parses: 301.40 7.28
Hard parses: 0.05 0.00
Sorts: 317.78 7.68
Logons: 0.10 0.00
Executes: 2,975.84 71.90
Transactions: 41.39

% Blocks changed per Read: 1.34 Recursive Call %: 87.43
Rollback per transaction %: 27.56 Rows per Sort: 7.22

Instance Efficiency Percentages
The "Instance Efficiency Percentages" section is very useful. It gives you an overview of your instance health. Anytime you make instance parameter changes you should take a look to see if this affects your instance efficiency in any way. Here is a description of some of the fields (Note, as stated in the statspack report, your goal here is to have these percentages be as close to 100% as possible):
Buffer Nowait %: This is the percentage of time that the instance made a call to get a buffer (all buffer types are included here) and that buffer was made available immediately (meaning it didn't have to wait for the buffer...hence "Buffer Nowait").

Buffer Hit %: This means that when a request for a buffer took place, the buffer was available in memory and physical disk I/O did not need to take place.

Library Hit %: If your Library Hit percentage is low it could mean that your shared pool size is too small or that the bind variables are not being used (or at least being used properly).

Execute to Parse %: This is the formula used to get this percentage:
round(100*(1-parsevalue/executevalue),2)
So, if you run some SQL and it has to be parsed every time you execute it (because no plan exists for this statement) then your percentage would be 0%. The more times that your SQL statement can reuse an existing plan the higher your Execute to Parse ratio is.

One way to increase your parse ratio is to use bind variables. This allows the same plan to be used for multiple SQL statements. The only thing that changes in the SQL is the parameters used in your statement's WHERE clause. For Java/JDBC Programmers that means using PreparedStatements as opposed to regular Statements.

Parse CPU to Parse Elapsd %: Generally, this is a measure of how available your CPU cycles were for SQL parsing. If this is low, you may see "latch free" as one of your top wait events.

Redo NoWait %: You guessed it...the instance didn't have to wait to use the redo log if this is 100%.

In-memory Sort %: This means the instance could do its sorts in memory as opposed to doing physical I/O...very good. You don't want to be doing your sorts on disk...especially in an OLTP system. Try increasing your SORT_AREA_SIZE or PGA_AGGREGATE_TARGET in your spfile/pfile to see if that helps if your in-memory sorting is not between 95% and 100%.

Soft Parse %: This is an important one...at least for OLTP systems. This means that your SQL is being reused. If this is low (not between 95% and 100%) then make sure that you're using bind variables in the application and that they're being used properly.

Latch Hit %: This should be pretty close to 100%; if it's not then check out what your top wait events are to try to fix the problem (pay specific attention to 'latch free' event).

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.98 In-memory Sort %: 100.00
Library Hit %: 100.04 Soft Parse %: 99.98
Execute to Parse %: 89.87 Latch Hit %: 94.99
Parse CPU to Parse Elapsd %: 75.19 % Non-Parse CPU: 99.46

Top 5 Timed Events (Called "Top 5 Wait Events" in 8i)
This section is crucial in determining what some of the performance drains in your database are. It will actually tell you the amount of time the instance spent waiting. Here are some common reasons for high wait events:
DB file scattered read: This can be seen fairly often. Usually, if this number is high, then it means there are a lot of full tablescans going on. This could be because you need indexes or the indexes you do have are not not being used.

DB file sequential read: This could indicate poor joining orders in your SQL or waiting for writes to 'temp' space. It could mean that a lot of index reads/scans are going on. Depending on the problem it may help to tune PGA_AGGREGATE_TARGET and/or DB_CACHE_SIZE.

CPU Time: This could be completely normal. However, if this is your largest wait event then it could mean that you have some CPU intensive SQL going on. You may want to examine some of the SQL further down in the Statspack report for SQL statements that have large CPU Time.

SQL*Net more data to client: This means the instance is sending a lot of data to the client. You can decrease this time by having the client bring back less data. Maybe the application doesn't need to bring back as much data as it is.

log file sync: A Log File Sync happens each time a commit takes place. If there are a lot of waits in this area then you may want to examine your application to see if you are committing too frequently (or at least more than you need to).

Logfile buffer space: This happens when the instance is writing to the log buffer faster than the log writer process can actually write it to the redo logs. You could try getting faster disks but you may want to first try increasing the size of your redo logs; that could make a big difference (and doesn't cost much).

Logfile switch: This could mean that your committed DML is waiting for a logfile switch to occur. Make sure your filesystem where your archive logs reside are not getting full. Also, the DBWR process may not be fast enough for your system so you could add more DBWR processes or make your redo logs larger so log switches are not needed as much.

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read 187,787 906 88.60
SQL*Net more data to client 49,707 57 5.55
CPU time 54 5.33
log file parallel write 1,011 2 .22
latch free 6,226 2 .16
-------------------------------------------------------------

The SQL Sections (Buffer Gets, Disk Reads, Executions, and Parse Counts)
The following sections show you the Top SQL (or 'worst performing' SQL) grouped by four sections: Buffer Gets, Disk Reads, Executions, and Parse Counts. You'll want to review the top SQL statements in each of these sections to see if they can be tuned better. These sections are a great way to how many times the SQL is being executed, how much CPU time is being used to execute them, and the total time for the statement to execute.
SQL ordered by Parse Calls for DB: ORCL Instance: ORCL Snaps: 4873 -4875
-> End Parse Calls Threshold: 1000

% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
144,300 144,300 6.65 4199666855
Module: JDBC Thin Client
select parameter, value from nls_session_parameters

Note: If you take the hash value for the SQL statement, then you run the ORACLE_HOME/rdbms/admin/sprepsql.sql script, and enter the hash value when it prompts you it will pull up the Execution Plan for that SQL statement. Pretty Cool!

Instance Activity Stats
This section may provide some insight into some potential performance problems that were not as easily visible from previous sections in the report. This section is also useful when comparing statspack reports from the same timeframes on different days.

Tablespace and Data File I/O Statistics
These sections help give you some visibility into I/O rolled up to the tablespace level and I/O stats on your data files.
Tablespace IO Stats for DB: ORCL Instance: ORCL Snaps: 4873 -4875
->ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
UNDOTBS
146 0 5.8 1.0 117,119 16 50,681 1.3
APP1
19,395 3 10.5 1.0 32,613 5 1,886 2.8
INDEX1
36,919 5 0.7 6.3 977 0 526 5.0
APP2
6,969 1 11.7 1.0 13,559 2 2,513 2.5
SYSTEM
15,056 2 0.8 1.8 360 0 13 3.8

AWR Report/Snapshot

2008-06-03T04:06:00.000-07:00

Prior Oracle 10g we use STATSPACK report to analyze instance health for performance. but now in 10g we use AWR(automatic workload repository) report to analyze Oracle Database health.

How to generate AWR report ?

It is very easy to generate AWR report in Oracle 10g.

We have to run just one sql script to generate AWR report.

There is TWO sql script to create AWR report.
1. awrrpt.sql
If we have only One Oracle Database then run awrrpt.sql sql script.

2. awrrpti.sql
If we have more than One Oracle Instance (Like RAC) then run awrrpti.sql script so that we can particular instance for awr report creation.

Location of AWR report sql script

$ORACLE_HOME/rdbms/admin

Example Of AWR report

AWR report Example

What is default interval period between two awr report ?

AWR report generate automatically for database every 1 hr interval period.
But Recommanded is 15 Minutes is enough in two snapshot for better performance bottleneck.

How to Manage & change interval period for awr report ?

There is two to modify or changes in AWR report.
1. Manually ---> through DBMS_WORKLOAD_REPOSITORY plsql package

2. GUI ----> OEM (Oracle Enterprise Manager)
Login to OEM ---> Administration Tag ----> Workload Section --->AUTOMATIC WORKLOAD REPOSITORY

How to Interpreting with AWR report ?

Below is Main Section in AWR report for concern.

Load Profile

In this section we know about Logical Read, Physical Read, Soft Parse, Hard Parse

Instance Efficiency Percentages

Many Oracle Guru's is not recommended to trust on Instance percentage ratio. becuase suppose if your instance percentage show BUFFER CAHCE HIT RATIO is 100% it is not sign for your database is very fast and running smootly.

Top 5 Timed Events

It is very important section in AWR report.
through this we can know most five wait event is effecting database performance.

Oracle Wait Event

SQL Statistics

It this section we will know about Execution time, cpu used, logical & physical read for top most sql statement.

Advisory Statistics

In this section we will get advice for PGA, BUFFER CACHE, SHARED POOL, JAVA POOL size for better performance.

Active Session History (ASH)

2008-06-03T04:05:00.004-07:00

Oracle 10gr2 Introduce new option for capture performance problem.ASH Active session History What is ASH ?Whenever you want to know information about blocker and waiter identifiers and their associated transaction IDs and SQL.About V$ACTIVE_SESSION_HISTORY1. The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. 2. Active sessions are sampled every second and are stored in a circular buffer in SGA. 3. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session. 4. This includes any session that was on the CPU at the time of sampling.5. Each session sample is a set of rows and the V$ACTIVE_SESSION_HISTORY view returns one row for each active session per sample, returning the latest session sample rows first. Because the active session samples are stored in a circular buffer in SGA, the greater the system activity, the smaller the number of seconds of session activity that can be stored in the circular buffer. This means that the duration for which a session sample appears in the V$ view, or the number of seconds of session activity that is displayed in the V$ view, is completely dependent on the database activity.6. Using the Active Session History enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view 7. Historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view, SQL identifier of SQL statementWhat information provide ASH view ?1. Object number, file number, and block number2. Wait event identifier and parameters3. Session identifier and session serial number4. Module and action name5. Client identifier of the session6. Service hash identifierHow to generate ASH report ?It is New feature of 10GR2(10.2.0.1.0)For report creation we have to use ASHRPT.SQL script.Located: In $ORACLE_HOME/rdbms/admin folderHow to run ASHRPT.SQL scriptTo generate a text report of ASH information, run the ashrpt.sql script at the SQL prompt:@$ORACLE_HOME/rdbms/admin/ashrpt.sqlFirst, you need to specify whether you want an HTML or a text report.Enter value for report_type: textSpecify the time frame to collect ASH information by first specifying the begin time in minutes prior to the system date.Enter value for begin_time: -10Note: here you have to just put number in minutes eg: 10 for 10 minutesNext, enter the duration in minutes that the report for which you want to capture ASH information from the begin time. The default duration of system date minus begin time is accepted in the following example:Enter value for duration:Note: left blank for default value. Default value is SYSDATEThe report in this example will gather ASH information beginning from 10 minutes before the current time and ending at the current time. Next, accept the default report name or enter a report name. The default name is accepted in the following example:Enter value for report_name: Using the report name ashrpt_1_0310_0131.txtNote: Left it blank for default value.The session history report is generated.For Futhere Detail please check metalink note: 243132.1

System Statistics

2008-06-03T04:05:00.003-07:00

System statistics describe the system's hardware characteristics, such as I/O and CPU performance and utilization, to the query optimizer. Why gather SYSTEM STATISTICS ?When choosing an execution plan, then opitmizer estimate the I/O and CPU resources required for each query.System statistics enable the query optimizer to more accurately estimate I/O and CPU costs, enabling the query optimizer to choose a better execution plan.It is important to gather system statistics ?System statistics give accurate cost for sql query so optimizer take good decision.If you have good statistics then query take good decision and database performance is increase.Many Oracle Guru's and Oracle Corp. Also recommended to gather system statistics.How Gather System Statistics ?Through DBMS_STATS package we can gather system statisticsStep1SQL> exec dbms_stats.gather_system_stats('Start');Step2SQL>--Wait for some time ...it will 1 hr minimum or 2 hr or whole day according database load.Step3SQL exec dbms_stats.gather_system_stats('Stop');Keep in mind.1. User must granted DBA privilege role for gather system statistics.2. After gather system statistics... Unlike table, index, or column statistics, Oracle does not invalidate already parsed SQL statements when system statistics get updated. All new SQL statements are parsed using new statistics.3. Always Gather System statistics During Heavy Peak Load.Where check gather system statistics data information ?After Gather system statictics query sys.aux_stats$ view.SQL> select pname, pval1 from sys.aux_stats$;PNAME PVAL1------------------------------ ----------STATUSDSTARTDSTOPFLAGS 1CPUSPEEDNW 904.86697IOSEEKTIM 10IOTFRSPEED 4096SREADTIM .995MREADTIM 1.701CPUSPEED 1268MBRC 16MAXTHRSLAVETHR13 rows selected.Note: CPUSPEEDNW,IOSEEKTIM,IOTFRSPEED is New column comes with Oracle 10g. and these columns already populated before gather system statistics.SREADTIMSingle block read time is the average time to read a single block randomly.MREADTIMMultiblock read is the average time to read a multiblock sequentially.MBRCMultiblock count is the average multiblock read count sequentially.

Oracle Latch

2008-06-03T04:05:00.001-07:00

What is Latch ?A mechanism to protect shared data structures in the System Global Area.For Example: latches protect the list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache.A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures.During DB performance we will see LATCH event ...so what is latch event and how many types of latch events ?A latch is a low-level internal lock used by Oracle to protect memory structures.The latch free event is updated when a server process attempts to get a latch, and the latch is unavailable on the first attempt.Most Popular latch wait event are ...
1. Latch: library cache or Latch: shared poolBelow is Possible causes for above both latch events.1. Lack of statement reuse2. Statements not using bind variables3. Insufficient size of application cursor cache4. Cursors closed explicitly after each execution5. Frequent logon/logoffs6. Underlying object structure being modified (for example truncate)7. Shared pool too smallBelow is Possible suggestion for aviod above both latch events.1. Increase SHARED_POOL_SIZE parameter value.2. Modify Frontend application to use BIND VARIABLE3. Use CURSOR_SHARING='force' (for temporary basis)2. Latch: cache buffers lru chainPossible Causes1. Inefficient SQL that accesses incorrect indexes iteratively (large index range scans) or many full table scans. 2. DBWR not keeping up with the dirty workload; hence, foreground process spends longer holding the latch looking for a free buffer3. Cache may be too smallPossible Suggestion1. Look for: Statements with very high logical I/O or physical I/O, using unselective indexes2. Increase DB_CACHE_SIZE parameter value.3. The cache buffers lru chain latches protect the lists of buffers in the cache. When adding, moving, or removing a buffer from a list, a latch must be obtained.For symmetric multiprocessor (SMP) systems, Oracle automatically sets the number of LRU latches to a value equal to one half the number of CPUs on the system. For non-SMP systems, one LRU latch is sufficient.Contention for the LRU latch can impede performance on SMP machines with a large number of CPUs. LRU latch contention is detected by querying V$LATCH, V$SESSION_EVENT, and V$SYSTEM_EVENT. To avoid contention, consider tuning the application, bypassing the buffer cache for DSS jobs, or redesigning the application.Latch: cache buffers chainsPossible Causes1. Repeated access to a block (or small number of blocks), known as a hot block2. From AskTom:Contention for these latches can be caused by:- Very long buffer chains.- very very heavy access to the same blocks.Possible Suggestion1. From AskTom:When I see this, I try to see what SQL the waiters are trying to execute. Many times, what I find, is they are all running the same query for the same data (hot blocks). If you find such a query -- typically it indicates a query that might need to be tuned (to access less blocks hence avoiding the collisions).If it is long buffer chains, you can use multiple buffer pools to spread things out. You can use DB_BLOCK_LRU_LATCHES to increase the number of latches. You can use both together.The cache buffers chains latches are used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache. Contention on this latch usually means that there is a block that is greatly contended for (known as a hot block).To identify the heavily accessed buffer chain, and hence the contended for block, look at latch statistics for the cache buffers chains latches using the view V$LATCH_CHILDREN. If there is a specific cache buffers chains child latch that has many more GETS, MISSES, and SLEEPS when compared with the other child latches, then this is the contended for child latch.This latch has a memory address, identified by the ADDR column. Use the value in the ADDR column joined with the X$BH table to identify the blocks protected by this latch. For example, given the address (V$LATCH_CHILDREN.ADDR) of a heavily contended latch, this queries the file and block numbers:SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCHFROM X$BHWHERE HLADDR = 'address of latch'ORDER BY TCH;X$BH.TCH is a touch count for the buffer. A high value for X$BH.TCH indicates a hot block.Many blocks are protected by each latch. One of these buffers will probably be the hot block. Any block with a high TCH value is a potential hot block. Perform this query a number of times, and identify the block that consistently appears in the output. After you have identified the hot block, query DBA_EXTENTS using the file number and block number, to identify the segment.After you have identified the hot block, you can identify the segment it belongs to with the following query:SELECT OBJECT_NAME, SUBOBJECT_NAMEFROM DBA_OBJECTSWHERE DATA_OBJECT_ID = &obj;In the query, &obj is the value of the OBJ column in the previous query on X$BH.5. Latch: row cache objectsThe row cache objects latches protect the data dictionary.Suggestion: Increase SHARED_POOL_SIZE parameter to avoid this latch.

Materialized view for Data Warehouse

2008-06-03T04:04:00.001-07:00

What is materialized view?1. Materialized view is normal database object like “table,index”2. It is basically use for Data warehouse or Replication purpose.3. Snapshot is synonym for materialized view.4. A materialized view can be stored in the same database as its base tables or in a different database5. A materialized view provides access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views.6. A materialized view improve response time through query rewrite or reduce execution time.Note: Query rewrite is a mechanism where Oracle or applications from the end user or database transparently improve query response time, by automatically rewriting the SQL query to use the materialized view instead of accessing the original tables. Query rewrites are particularly useful in a data warehouse environment.What is syntax for materialized view?Create materialized view “materialized_view_name”Build [immediate / deferred]Refresh [fash [on commit / demand]]Enable query rewriteAs Sql statement/Now Details explanation about syntax 1. Create materialized view “name’I hope it is understood because self explanatory2. Build [immediate / deferred]1. Build immediate -- > materialized view populate with data on creation time from tables.2. Build deferred --- > materialized view not populate with data on creation time, we have to populate later.Eg: With build immediatecreate materialized view mymview1build immediateasselect ename,sum(sal) from emp group by ename;SQL> select count(*) from emp;COUNT(*)----------14Note: materialized view populate with data on creation time.With build deferredcreate materialized view mymview2build deferredasselect ename,sum(sal) from emp group by ename;SQL> select count(*) from mymview2;COUNT(*)----------0Note: materialized view not populate with data on creation time, we have to populate manually through DBMS_MVIEW.RERESH package.3. Refresh [fast [on commit / demand]]Refresh is method for populate materialized view with data whenever made changes in their base tables.There are five refresh method for materialized view1. refresh fast (materialized view log needed)2. refresh on commit3. refresh on demandWhat is materialized view log?A materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally.Each materialized view log is associated with a single master table. The materialized view log resides in the same database and schema as its master table.Eg:With refresh on commitSQL> select count(*) from emp;COUNT(*)----------14SQL> create materialized view MYVIEW12 build immediate3 refresh on commit4 enable query rewrite5 as6 select ename, sum(sal) from emp group by ename;Materialized view created.SQL> select count(*) from myview1;COUNT(*)----------14SQL> insert into emp (empno,ename,sal) values(1754,'TAJ',2500);1 row created.SQL> commit;Commit complete.SQL> select count(*) from myview1;COUNT(*)----------15With refresh on commitSQL> select count(*) from emp;COUNT(*)----------15SQL> create materialized view MYVIEW52 build immediate3 refresh on demand4 enable query rewrite5 as6 select ename,sum(sal) from emp group by ename;Materialized view created.SQL> select count(*) from myview5;COUNT(*)----------15SQL> insert into emp (empno,ename,sal)values(1100,'xyz',1000);1 row created.SQL> commit;Commit complete.SQL> select count(*) from emp;COUNT(*)----------16SQL> select count(*) from myview5;COUNT(*)----------15SQL> exec dbms_mview.refresh('MYVIEW5');PL/SQL procedure successfully completed.SQL> select count(*) from myview5;COUNT(*)----------16Whenever any changes made in base tables and perform commit then materialized view refreshed.4. Enable query rewriteA materialized view is like a query with a result that is materialized and stored in a table. When a user query is found compatible with the query associated with a materialized view, the user query can be rewritten in terms of the materialized view. This technique improves the execution of the user query, because most of the query result has been precomputed. The query transformer looks for any materialized views that are compatible with the user query and selects one or more materialized views to rewrite the user query. The use of materialized views to rewrite a query is cost-based. That is, the query is not rewritten if the plan generated without the materialized views has a lower cost than the plan generated with the materialized views.Note: query_rewrite_enabled=true AND query_rewrite_integrity=enforced must be set to use query rewrite feature5. How Materialized view improve query performance.Let’s check with demo 1. Create Big Table :)SQL> create table big2 as select * from all_objects3 union all4 select * from all_objects5 union all6 select * from all_objects7 union all8 select * from all_objects9 union all10 select * from all_objects;Table created.SQL> select count(*) from big;COUNT(*)----------1889952. Now execute below query and check query statisticsSQL> alter system flush shared_pool;System altered.SQL> set autotrace traceonlySQL> set timing onSQL> select owner,count(*) from big group by owner;12 rows selected.Elapsed: 00:00:00.01Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=624 Card=188024 Bytes=3196408)1 0 SORT (GROUP BY) (Cost=624 Card=188024 Bytes=3196408)2 1 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=598 Card=188024 Bytes=3196408)Statistics----------------------------------------------------------957 recursive calls0 db block gets2844 consistent gets---- > too big :)0 physical reads0 redo size1028 bytes sent via SQL*Net to client504 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client10 sorts (memory)0 sorts (disk)12 rows processed3. Now Create materialized view and try SQL> create materialized view MYVIEWBIG2 build immediate3 refresh on commit4 enable query rewrite5 as6 select owner,count(*) from big group by owner;Materialized view created.SQL> alter system flush shared_pool;System altered.SQL> set autotrace traceonlySQL> set timing onSQL> select owner,count(*) from big group by owner;12 rows selected.Elapsed: 00:00:00.01Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=12 Bytes=360)1 0 MAT_VIEW REWRITE ACCESS (FULL) OF 'MYVIEWBIG' (MAT_VIEW REWRITE) (Cost=3 Card=12 Bytes=360)Statistics----------------------------------------------------------7356 recursive calls0 db block gets1313 consistent gets --- > just less then half :) 0 physical reads0 redo size1028 bytes sent via SQL*Net to client504 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client49 sorts (memory)0 sorts (disk)12 rows processed4. What Privileges Need for materialized view ?To create a materialized view in your own schema, you must have the CREATE MATERIALIZED VIEW privilege and the SELECT privilege to any tables referenced that are in another schema. To create a materialized view in another schema, you must have the CREATE ANY MATERIALIZED VIEW privilege and the owner of the materialized view needs SELECT privileges to the tables referenced if they are from another schema. Moreover, if you enable query rewrite on a materialized view that references tables outside your schema, you must have the GLOBAL QUERY REWRITE privilege or the QUERY REWRITE object privilege on each table outside your schema.5. How to check Status of a Materialized View?SQL> select mview_name,query,rewrite_enabled,refresh_mode,build_mode,staleness,compile_state2 from user_mviews;MVIEW_NAME QUERY R REFRES BUILD_MOD STALENESS COMPILE_ST---------- -------------------- - ------ --------- ---------- ----------MYBIG select owner, count( Y DEMAND IMMEDIATE NEEDS_COMP NEEDS_COMP*) from big group by ILE ILEownerMYMVIEW2 select ename,sum(sal N DEMAND DEFERRED UNUSABLE NEEDS_COMP) from emp group by ILEenameMYVIEW5 select ename,sum(sal Y DEMAND IMMEDIATE FRESH VALID) from emp group byenameMYMVIEW3 select ename,sum(sal Y COMMIT IMMEDIATE UNUSABLE VALID) from emp group byenameMYVIEW1 select ename, sum(sa Y COMMIT IMMEDIATE FRESH VALIDl) from emp group byExplanation:MVIEW_NAME -------- > Materialized View nameQUERY ------------- > Query that defines the materialized viewREFRESH_ENABLED --- > If query_rewrite_enabled=TRUE then value is “Y” otherwise “N”REFRESH_MODE ------ > DEMAND, COMMIT, NEVERBUILD_MODE -------- > IMMEDIATE, DEFERREDSTALENESS --------- > Relationship between the contents of the materialized view and the contents of the materialized view's masters.COMPILE_STATE ----- > Validity of the materialized view with respect to the objects upon which it dependsVALID -> No objects depends has changed since the last validationNEED_COMPILE -> Some objects depends materialized view has changed an “alter materialized view … compile;” statement is required to validate this materialized view

Master Replication

2008-06-03T04:01:00.000-07:00

What is Advance Replication?Replication is the process of copying and maintaining database objects, such as tables, in multiple databases that make up a distributed database system. Changes applied at one site are captured and stored locally before being forwarded and applied at each of the remote locations. Advanced Replication is a fully integrated feature of the Oracle server; it is not a separate server.Replication uses distributed database technology to share data between multiple sites, but a replicated database and a distributed database are not the same. In a distributed database, data is available at many locations, but a particular table resides at only one location. For example, the employees table resides at only the ny.world database in a distributed database system that also includes the hk.world and la.world databases. Replication means that the same data is available at multiple locations. For example, the employees table is available at ny.world, hk.world, and la.world.Some of the common reasons for using replication are:
AvailabilityReplication improves the availability of applications because it provides them with alternative data access options. If one site becomes unavailable, then users can continue to query or even update the remaining locations. In other words, replication provides excellent failover protection.PerformanceReplication provides fast, local access to shared data because it balances activity over multiple sites. Some users can access one server while other users access different servers, thereby reducing the load at all servers. Also, users can access data from the replication site that has the lowest access cost, which is typically the site that is geographically closest to them.Disconnected computingA materialized view is a complete or partial copy (replica) of a target table from a single point in time. Materialized views enable users to work on a subset of a database while disconnected from the central database server. Later, when a connection is established, users can synchronize (refresh) materialized views on demand. When users refresh materialized views, they update the central database with all of their changes, and they receive any changes that may have happened while they were disconnected.Network load reductionReplication can be used to distribute data over multiple regional locations. Then, applications can access various regional servers instead of accessing one central server. This configuration can reduce network load dramatically.Architecture of advance replicationThere are two types of Advance Replication
1. Multimaster Replication2. Materialized view replication1. Multimaster replicationStart --- > set up master site --- > Select object for replication --- > Create Group for selected objects --- > if conflict possible then configure conflict resolution ---> end2. Materialized view replicationStart --- > set up master site -- > set up materialized view site --- > create materialized view group --- > endNote: In Detail explanation is Part-II, III.Replication SitesA replication group can exist at multiple replication sites. Replication environments support two basic types of sites: master sites and materialized view sites. One site can be both a master site for one replication group and a materialized view site for a different replication group. However, one site cannot be both the master site and the materialized view site for the same replication group.The differences between master sites and materialized view sites are the following:Replication Group1. A replication group at a master site is more specifically referred to as a master group.2. A replication group at a materialized view site is based on a master group and is more specifically referred to as a materialized view group.Replication Objects1. A master site maintains a complete copy of all objects in a replication group,Example: if the hr_repg master group contains the table’s employees and departments, then all of the master sites participating in a master group must maintain a complete copy of employees and departments.2. Materialized views at a materialized view site can contain all or a subset of the table data within a master group.Example: one materialized view site might contain only a materialized view of the employees table, while another materialized view site might contain materialized views of both the employees and departments tables.Communication with master sites1. All master sites in a multimaster replication environment communicate directly with one another to continually propagate data changes in the replication group2. Materialized view sites contain an image, or materialized view, of the table data from a certain point in time.Replication ObjectsA replication object is a database object existing on multiple servers in a distributed database system. In a replication environment, any updates made to a replication object at one site are applied to the copies at all other sites. Advanced Replication enables you to replicate the following types of objects:• Tables • Indexes • Views and Object Views • Packages and Package Bodies • Procedures and Functions • User-Defined Types and Type Bodies • Triggers • Synonyms • Index types • User-Defined Operators Replication GroupsIn a replication environment, Oracle manages replication objects using replication groups. A replication group is a collection of replication objects that are logically related.By organizing related database objects within a replication group, it is easier to administer many objects together. Typically, you create and use a replication group to organize the schema objects necessary to support a particular database application. However, replication groups and schemas do not need to correspond with one another. A replication group can contain objects from multiple schemas, and a single schema can have objects in multiple replication groups. However, each replication object can be a member of only one replication group.
Here I explain only Multimaster replication.1. Multimaster replication includes multiple master sites, where each master site operates as an equal peer.2. Multimaster replication, also known as peer-to-peer or n-way replication, is comprised of multiple master sites equally participating in an update-anywhere model.3. Oracle database servers operating as master sites in a multimaster replication environment automatically work to converge the data of all table replicas, and ensure global transaction consistency and data integrity.4. Conflict resolution is independently handled at each of the master sites.5. Multimaster replication provides complete replicas of each replicated table at each of the master sites.Why Use Multimaster Replication?From a very basic point of view, replication is used to make sure that data is available when and where you need it.But some of possibilities when we use multimaster replication are FailoverMultimaster replication can be used to protect the availability of a mission critical database. For example, a multimaster replication environment can replicate data in your database to establish a failover site should the primary site become unavailable due to system or network outages. Such a failover site also can serve as a fully functional database to support application access when the primary site is concurrently operational.Load BalancingMultimaster replication is useful for transaction processing applications that require multiple points of access to database information for the following purposes:1. Distributing a heavy application load 2. Ensuring continuous availability 3. Providing more localized data accessMultimaster Replication ProcessThere are two types of multimaster replication: asynchronous and synchronous.asynchronousAsynchronous replication, often referred to as store-and-forward replication, captures any local changes, stores them in a queue, and, at regular intervals, propagates and applies these changes at remote sites. With this form of replication, there is a period of time before all sites achieve data convergence.synchronousSynchronous replication, also known as real-time replication, applies any changes or executes any replicated procedures at all sites participating in the replication environment as part of a single transaction. If the data manipulation language (DML) statement or procedure fails at any site, then the entire transaction rolls back. Synchronous replication ensures data consistency at all sites in real-time.Note: You can change the propagation mode from asynchronous to synchronous or vice versa for a master site.How to setup Master sites?Set below parametersglobal_namesalter system set global_names=TRUE;job_queue_processesalter system set job_queue_processes = 1;Considerations for Replicated TablesPrimary KeysIf possible, each replicated table should have a primary key. Where a primary key is not possible, each replicated table must have a set of columns that can be used as a unique identifier for each row of the table.Foreign KeysWhen replicating tables with foreign key referential constraints, Oracle Corporation recommends that you always index foreign key columns and replicate these indexes, unless no updates and deletes are allowed in the parent table. Indexes are not replicated automatically. To replicate an index, add it to the master group containing its table using either the Replication Management tool or the CREATE_MASTER_REPOBJECT procedure in the DBMS_REPCAT package.Create DB for replicationHere I created three databases for replicationdb01db02Setting up DB01 master site
Step 1 Connect as SYSTEM at a master site at DB01SQL> conn system/oracle@db01Connected.Step 2 Create the replication administrator at DB01SQL> CREATE USER repadmin IDENTIFIED BY repadmin;User created.Step 3 Grant privileges to the replication administrator at DB01SQL> BEGIN2 DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (3 username => 'repadmin');4 END;5 /PL/SQL procedure successfully completed.Note: This privilege to create and manage a replicated enviourmentsSQL> GRANT SELECT ANY DICTIONARY TO repadmin;Grant succeeded.Note: If you want your repadmin to be able to connect to the Replication Management tool,then grant SELECT ANY DICTIONARY to repadmin:Step 4 Register the propagator at DB01SQL> BEGIN2 DBMS_DEFER_SYS.REGISTER_PROPAGATOR (3 username => 'repadmin');4 END;5 /PL/SQL procedure successfully completed.Note: The propagator is responsible for propagating the deferred transaction queue to other master sites.Step 5 Register the receiver at DB01SQL> BEGIN2 DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (3 username => 'repadmin',4 privilege_type => 'receiver',5 list_of_gnames => NULL);6 END;7 /PL/SQL procedure successfully completed.Note: The receiver receives the propagated deferred transactions sent by the propagator from other master sites.Step 6 Schedule purge at master site at DB01SQL> CONNECT repadmin/repadmin@db01Connected.SQL>SQL> BEGIN2 DBMS_DEFER_SYS.SCHEDULE_PURGE (3 next_date => SYSDATE,4 interval => 'SYSDATE + 1/24',5 delay_seconds => 0);6 END;7 /PL/SQL procedure successfully completed.Note: In order to keep the size of the deferred transaction queue in check, you should purge successfully completed deferred transactions. The SCHEDULE_PURGE procedure automates the purge process for you. You must execute this procedure as the replication administrator.Note: Repeat above 6 steps in database DB02 master sites.Step-2 Create Scheduled Links between the Master Sites
Step 1 Create database links between master sites.The database links provide the necessary distributed mechanisms to allow the different replication sites to replicate data among themselves. Before you create any private database links, you must create the public database links that each private database link will use. You then must create a database link between all replication administrators at each of the master sites that you have set up.SQL> conn system/oracle@db01Connected.SQL> create public database link db02 using 'db02';Database link created.SQL> conn repadmin/repadmin@db01Connected.SQL> create database link db02 connect to repadmin identified by repadmin;Database link created.SQL> conn system/oracle@db02Connected.SQL> create public database link db01 using 'db01';Database link created.SQL> conn repadmin/repadmin@db02Connected.SQL> create database link db01 connect to repadmin identified by repadmin;Database link created.Step 2 Define a schedule for each database link to create scheduled links.Create a scheduled link by defining a database link when you execute the SCHEDULE_PUSH procedure. The scheduled link determines how often your deferred transaction queue is propagated to each of the other master sites. You need to execute the SCHEDULE_PUSH procedure for each database link that you created in Step 1. The database link is specified in the destination parameter of the SCHEDULE_PUSH procedure.Even when using Oracle's asynchronous replication mechanisms, you can configure a scheduled link to simulate continuous, real-time replication. The scheduled links in this example simulate continuous replication.SQL> conn repadmin/repadmin@db01Connected.SQL> edWrote file afiedt.buf1 BEGIN2 DBMS_DEFER_SYS.SCHEDULE_PUSH (3 destination => 'DB02',4 interval => 'SYSDATE + (1/144)',5 next_date => SYSDATE,6 parallelism => 1,7 execution_seconds => 1500,8 delay_seconds => 1200);9* END;SQL> /PL/SQL procedure successfully completed.SQL> conn repadmin/repadmin@db02Connected.SQL> edWrote file afiedt.buf1 BEGIN2 DBMS_DEFER_SYS.SCHEDULE_PUSH (3 destination => 'DB01',4 interval => 'SYSDATE + (1/144)',5 next_date => SYSDATE,6 parallelism => 1,7 execution_seconds => 1500,8 delay_seconds => 1200);9* END;SQL> /PL/SQL procedure successfully completed.Step-3 Create Master Group
Here I am using SCOTT schema and their objects for replicate purpose because SCOTT schema exists in Default Oracle installation.Objects which use for replication in scott schema.1. DEPT - table2. EMP - tablePrimary key must exists on each table which use for replication.Note: If DB version is 10g then first unlock scott account;Step-1 Unlock Scott Schema and reset schema password or Create New Schema for replication.Step 2 Create the master groupSQL> conn repadmin/repadmin@db01Connected.SQL> begin2 dbms_repcat.create_master_repgroup (3 gname => 'scott_repg');4 end;5 /PL/SQL procedure successfully completed.Note: This step must be completed by the replication administrator.Step 3 Add objects to master group.SQL> edWrote file afiedt.buf1 BEGIN2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (3 gname => 'scott_repg',4 type => 'TABLE',5 oname => 'EMP',6 sname => 'scott',7 use_existing_object => TRUE,8 copy_rows => FALSE);9* END;SQL> /PL/SQL procedure successfully completed.SQL> edWrote file afiedt.buf1 BEGIN2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (3 gname => 'scott_repg',4 type => 'TABLE',5 oname => 'DEPT',6 sname => 'scott',7 use_existing_object => TRUE,8 copy_rows => FALSE);9* END;SQL> /PL/SQL procedure successfully completed.Note: Use the CREATE_MASTER_REPOBJECT procedure to add an object to your master group. In most cases, you probably will be adding tables and indexes to your master group, but you can also add procedures, views, synonyms, and so on.Step 4 Add additional master sites.SQL> edWrote file afiedt.buf1 BEGIN2 DBMS_REPCAT.ADD_MASTER_DATABASE (3 gname => 'scott_repg',4 master => 'DB02',5 use_existing_objects => TRUE,6 copy_rows => FALSE,7 propagation_mode => 'ASYNCHRONOUS');8* END;SQL> /PL/SQL procedure successfully completed.Note: You should wait until db02 appears in the DBA_REPSITES view before continuing. Execute the following SELECT statement in another SQL*Plus session to make sure that db02 has appeared:SQL> conn repadmin/repadmin@db01Connected.SQL> SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = 'SCOTT_REPG';DBLINK------------------------------------------------------------------DB01DB02Note:1. In this example, the use_existing_objects parameter in the ADD_MASTER_DATABASE procedure is set to TRUE because it is assumed that the scott schema already exists at all master sites. In other words, it is assumed that the objects in the scott schema are precreated at all master sites. Also, the copy_rows parameter is set to FALSE because it is assumed that the identical data is stored in the tables at each master site.2. Here I am using replication Process is “ASYNCHRONOUS”.3. Here I am skip “conflict resolution methods”Note: Repeat Step-3 at DB02 master site.Step 5 Generate replication support.SQL> edWrote file afiedt.buf1 BEGIN2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (3 sname => 'scott',4 oname => 'emp',5 type => 'TABLE',6 min_communication => TRUE);7* END;SQL> /PL/SQL procedure successfully completed.SQL> edWrote file afiedt.buf1 BEGIN2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (3 sname => 'scott',4 oname => 'dept',5 type => 'TABLE',6 min_communication => TRUE);7* END;SQL> /PL/SQL procedure successfully completed.Note: You should wait until the DBA_REPCATLOG view is empty before resuming master activity. Execute the following SELECT statement to monitor your DBA_REPCATLOG view:SQL> SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = 'SCOTT_REPG';COUNT(*)----------0Step-6 Start replication.After creating your master group, adding replication objects, generating replication support, and adding additional master databases, you need to start replication activity. Use the RESUME_MASTER_ACTIVITY procedure to "turn on" replication for the specified master group.SQL> edWrote file afiedt.buf1 BEGIN2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY (3 gname => 'scott_repg');4* END;SQL> /PL/SQL procedure successfully completed.Here we complete multimaster replication procedure.Now let check what we did is correct or not :)Connect with scott to db01 and insert new rows in dept table and commit;SQL> conn scott/tiger@db01Connected.SQL> select * from dept;DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTONSQL> insert into dept values (50,'DUMMY','DUMMY');1 row created.SQL> commit;Commit complete.SQL> select * from dept;DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON50 DUMMY DUMMY--- New row--Wait some time ...and connect scott to db02 database and check that new row replicate in dept table.SQL> conn scott/tiger@db02Connected.SQL> select * from dept;DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON50 DUMMY DUMMY --- New rowNote: you can perform any DML operation on Master Definition site “db01” and commit. Changes will update in DB02 site and if you perform any changes in DB02 db then update will changes in DB01 vice versa.SQL> conn scott/tiger@db02Connected.SQL> select count(*) from dept;COUNT(*)----------5--Delete One row in dept table “db02”.SQL> delete dept where dname = 'DUMMY';1 row deleted.SQL> commit;Commit complete.SQL> select count(*) from dept;COUNT(*)----------4SQL> disconnSQL> conn scott/tiger@db01Connected.SQL> select count(*) from dept;COUNT(*)----------4Altering a Replicated Object
Step 1 Connect to the master definition site as the replication administrator.SQL> conn repadmin/repadmin@db01Connected.Step 2 If necessary, then quiesce the master group.SQL> edWrote file afiedt.buf1 BEGIN2 DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (3 gname => 'scott_repg');4* END;5 /PL/SQL procedure successfully completed.Step 3 In a separate SQL*Plus session, check the status of the master group you are quiescing.Do not proceed until the group's status is QUIESCED.SQL> SELECT GNAME, STATUS FROM DBA_REPGROUP;GNAME STATUS------------------------------ ---------SCOTT_REPG QUIESCING Step 4 Alter the replicated object.SQL> EDWrote file afiedt.buf1 BEGIN2 DBMS_REPCAT.ALTER_MASTER_REPOBJECT (3 sname => 'SCOTT',4 oname => 'dept',5 type => 'TABLE',6 ddl_text => 'ALTER TABLE SCOTT.DEPT ADD (timestamp DATE)');7* END;SQL> /PL/SQL procedure successfully completed.Note: Add Timestamp column in scott.dept@db01 tableStep 5 Regenerate replication support for the altered object.SQL> edWrote file afiedt.buf1 BEGIN2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (3 sname => 'scott',4 oname => 'dept',5 type => 'TABLE',6 min_communication => TRUE);7* END;8 /PL/SQL procedure successfully completed.Step 6 In a separate SQL*Plus session, check if DBA_REPCATLOG is empty.SQL> select count(*) from dba_repcatlog;COUNT(*)----------0Note: Do not proceed until this view is empty.Step 7 Resume replication activity.SQL> edWrote file afiedt.buf1 BEGIN2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY (3 gname => 'scott_repg');4* END;SQL> /PL/SQL procedure successfully completed.Now check dept table in both database at “db01” or “db02”.SQL> conn scott/tiger@db01Connected.SQL> desc deptName Null? Type----------------------------------------- -------- -------------------DEPTNO NOT NULL NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13)TIMESTAMP DATESQL> conn scott/tiger@db02Connected.SQL> desc deptName Null? Type----------------------------------------- -------- -------------------DEPTNO NOT NULL NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13)TIMESTAMP DATEListing the Master Sites Participating in a Master Group
Here I created two DB, one is master definition site and other is Master site.Through below query we can fintout which one is master definition site or which is master site.SQL> conn repadmin/repadmin@db01Connected.SQL> COLUMN GNAME HEADING 'Master Group' FORMAT A20SQL> COLUMN DBLINK HEADING 'Sites' FORMAT A25SQL> COLUMN MASTERDEF HEADING 'MasterDefinitionSite?' FORMAT A10SQL> SELECT GNAME, DBLINK, MASTERDEF2 FROM DBA_REPSITES3 WHERE MASTER = 'Y'4 AND GNAME NOT IN (SELECT GNAME FROM DBA_REPSITES WHERE SNAPMASTER = 'Y')5 ORDER BY GNAME;MasterDefinitionMaster Group Sites Site?-------------------- ------------------------- ----------SCOTT_REPG DB02 NSCOTT_REPG DB01 YNote: DB02 is master site and DB01 is master definition site.
What is Advance Replication?Replication is the process of copying and maintaining database objects, such as tables, in multiple databases that make up a distributed database system. Changes applied at one site are captured and stored locally before being forwarded and applied at each of the remote locations. Advanced Replication is a fully integrated feature of the Oracle server; it is not a separate server.Replication uses distributed database technology to share data between multiple sites, but a replicated database and a distributed database are not the same. In a distributed database, data is available at many locations, but a particular table resides at only one location. For example, the employees table resides at only the ny.world database in a distributed database system that also includes the hk.world and la.world databases. Replication means that the same data is available at multiple locations. For example, the employees table is available at ny.world, hk.world, and la.world.Some of the common reasons for using replication are:
AvailabilityReplication improves the availability of applications because it provides them with alternative data access options. If one site becomes unavailable, then users can continue to query or even update the remaining locations. In other words, replication provides excellent failover protection.PerformanceReplication provides fast, local access to shared data because it balances activity over multiple sites. Some users can access one server while other users access different servers, thereby reducing the load at all servers. Also, users can access data from the replication site that has the lowest access cost, which is typically the site that is geographically closest to them.Disconnected computingA materialized view is a complete or partial copy (replica) of a target table from a single point in time. Materialized views enable users to work on a subset of a database while disconnected from the central database server. Later, when a connection is established, users can synchronize (refresh) materialized views on demand. When users refresh materialized views, they update the central database with all of their changes, and they receive any changes that may have happened while they were disconnected.Network load reductionReplication can be used to distribute data over multiple regional locations. Then, applications can access various regional servers instead of accessing one central server. This configuration can reduce network load dramatically.Architecture of advance replicationThere are two types of Advance Replication
1. Multimaster Replication2. Materialized view replication1. Multimaster replicationStart --- > set up master site --- > Select object for replication --- > Create Group for selected objects --- > if conflict possible then configure conflict resolution ---> end2. Materialized view replicationStart --- > set up master site -- > set up materialized view site --- > create materialized view group --- > endNote: In Detail explanation is Part-II, III.Replication SitesA replication group can exist at multiple replication sites. Replication environments support two basic types of sites: master sites and materialized view sites. One site can be both a master site for one replication group and a materialized view site for a different replication group. However, one site cannot be both the master site and the materialized view site for the same replication group.The differences between master sites and materialized view sites are the following:Replication Group1. A replication group at a master site is more specifically referred to as a master group.2. A replication group at a materialized view site is based on a master group and is more specifically referred to as a materialized view group.Replication Objects1. A master site maintains a complete copy of all objects in a replication group,Example: if the hr_repg master group contains the table’s employees and departments, then all of the master sites participating in a master group must maintain a complete copy of employees and departments.2. Materialized views at a materialized view site can contain all or a subset of the table data within a master group.Example: one materialized view site might contain only a materialized view of the employees table, while another materialized view site might contain materialized views of both the employees and departments tables.Communication with master sites1. All master sites in a multimaster replication environment communicate directly with one another to continually propagate data changes in the replication group2. Materialized view sites contain an image, or materialized view, of the table data from a certain point in time.Replication ObjectsA replication object is a database object existing on multiple servers in a distributed database system. In a replication environment, any updates made to a replication object at one site are applied to the copies at all other sites. Advanced Replication enables you to replicate the following types of objects:• Tables • Indexes • Views and Object Views • Packages and Package Bodies • Procedures and Functions • User-Defined Types and Type Bodies • Triggers • Synonyms • Index types • User-Defined Operators Replication GroupsIn a replication environment, Oracle manages replication objects using replication groups. A replication group is a collection of replication objects that are logically related.By organizing related database objects within a replication group, it is easier to administer many objects together. Typically, you create and use a replication group to organize the schema objects necessary to support a particular database application. However, replication groups and schemas do not need to correspond with one another. A replication group can contain objects from multiple schemas, and a single schema can have objects in multiple replication groups. However, each replication object can be a member of only one replication group.

Index Fragmentation / Rebuild

2008-06-03T03:59:00.001-07:00

First analyze index
SQL>analyze index INDEX_NAME validate structure;Then query INDEX_STATS view1. If del_lf_rows/lf_rows is > .2 then index should be rebuild.2. If height is 4 then index should be rebuild.3. If lf_rows is lower than lf_blks then index should be rebuild.
SQL> column status format a10SQL> select trunc((del_lf_rows/lf_rows)*100,2)'%' "status" from index_stats;status----------21.83%How to remove index fragmentation?There are two way to remove fragmentation.1. index coalesce2. index rebuildWhat is difference between coalesce and rebuild please go through below link for more detailshttp://download.oracle.com/docs/cd/B14117_01/server.101/b10739/indexes.htm#g1007548
SQL> alter index IDX_OBJ_ID coalesce;SQL> alter index IDX_OBJ_ID rebuild;SQL> alter index IDX_OBJ_ID rebuild online;Note: If any DML statement is running on base table then we have to use ONLINE keyword with index rebuilding.
SQL> analyze index idx_obj_id validate structure;Index analyzed.SQL> select trunc((del_lf_rows/lf_rows)*100,2)'%' "status" from index_stats;status-------40.85%SQL> alter index IDX_OBJ_ID rebuild online;Index altered.SQL> analyze index idx_obj_id validate structure;Index analyzed.SQL> select trunc((del_lf_rows/lf_rows)*100,2)'%' "status" from index_stats;status--------0%Note: Index rebuild when index is fragmented or it is needed, otherwise index rebuilding is myth for improve performance.

Data file fragmentation and Data file resize

2008-06-03T03:58:00.000-07:00

There is no fragmentation at datafile level but sometime datafile is created in big size and actually data in datafile is very less then we can resize datafile to reduce database size.We can reduce datafile size upto HWM (High Water Mark)
SQL> select df.file_name,round(df.bytes/1024/1024) TotalSize,fs.FreeSizefrom dba_data_files df,(select file_id,round(sum(bytes/1024/1024)) FreeSizefrom dba_free_spacegroup by file_id) fswhere df.file_id=fs.file_id(+)order by 2,3 descFILE_NAME TOTALSIZE FREESIZE-------------------------------------------------- ---------- ----------D:\ORACLE\PRODUCT\10.1.0\ORADATA\USERS01.DBF 46 1D:\BIG01.DBF 100 100D:\ORACLE\PRODUCT\10.1.0\ORADATA\UNDOTBS01.DBF 465 436D:\ORACLE\PRODUCT\10.1.0\ORADATA\SYSTEM01.DBF 620 63D:\ORACLE\PRODUCT\10.1.0\ORADATA\SYSAUX01.DBF 700 32Note: Size in MB (Mega Bytes)Two datafiles which we can reduce 1.big01.dbf 2.undotbs01.dbf
SQL> alter databasedatafile ‘d:\big01.dbf’ resize 50m;Database altered.SQL> alter databasedatafile 'd:\oracle\product\10.1.0\oradata\undotbs01.dbf' resize 100m;alter database*ERROR at line 1:ORA-03297: file contains used data beyond requested RESIZE : ORA-03297 error happens because we are trying to reduce datafile below HWM and it is not possible. Go through above link and check from where you can reduce datafile size.

OS Authentication

2008-06-03T03:57:00.002-07:00

• Once authenticated by the operating system, users can connect to Oracle more conveniently, without specifying a user name or password. For example, an operating-system-authenticated user can invoke SQL*Plus and skip the user name and password prompts by entering the following:SQLPLUS / • With control over user authentication centralized in the operating system, Oracle need not store or manage user passwords, though it still maintains user names in the database.• Audit trails in the database and operating system use the same user names.Note: When an operating system is used to authenticate database users, managing distributed database environments and database links requires special care.OS Authentication Configuration Steps
1. Create OS user2. Set below parameters.alter system set os_authent_prefix = ‘OPS$’ scope=spfile;Note: Default parameter value is “OPS$” If OS user is member of domain group then also set below parameter.alter system set remote_os_authent=TRUE scope=spfile;Note: Default parameter value is FALSE, 3. Edit sqlnet.ora file and set below line to NTS Sqlnet.authentication_services=(NTS)4. Bounce databaseshutdown immediatestartup5. Create Oracle User for OS authenticationFirst check OS usernameselect sys_context(‘USERENV’,’OS_USER’) from dual;create user “OPS$USERNAME” identified by EXTERNALLY;Note: Oracle username is must be same as OS user name and must be started with “OPS$”.6. Test to connect through OS authenticationsqlplus /

Relocate Database Files

2008-06-03T03:57:00.001-07:00

Three files is called database files1. Datafiles & Tempfiles2. Control files3. Redo log files
Sometime we need to move database files from old to new location.How to move DATAFILES from old to new locationExcept SYSTEM,UNDO & TEMP tablespaceStep:1. Take tablespace offlinealter tablespace tbsname OFFLINE;2. through OS command MOVE datafiles from old to new location.mv 'old location' to 'new location'3. rename datafiles in database.alter database rename file 'old location' to 'new location';4. Take tablespace ONLINEalter tablespace tbsname ONLINE;For SYSTEM or UNDO tablespaceStep:1. shutdown databaseshutdown immediate;2. move datafile old to new location through OS command.mv old location new location3. startup database with mount stage.startup mount;4. rename datafile in databasealter databaserename file 'old location' to 'new location';5. open database for normal usealter database open;We can't relocate tempfile, if we need to relocate tempfile then best option is recreate temp tablespace with new location.How to move REDO LOG MEMBER from old to new location
Step:1. shutdown databaseshutdown immediate;2. mv redo log member from old to new locationmv oldlocation newlocation3. startup database with mount stagestartup mount;4. rename redo log member in databasealter database rename file 'old location' to 'new location';5. open database for normal usealter database open;How to move control file old to new location
Step:1. shutdown databaseshutdown immediate;2. mv controlfile to old to new locationmv old location new location3. edit CONTROL_FILES parameter in pfile and change location from old to newCONTROL_FILES='new location'4. recreate SPFILE from PFILEcreate spfile from pfile;5. startup databasestartup;

Recovery Catalog

2008-06-03T03:56:00.000-07:00

What is recovery catalog?Recovery catalog is external database which use to store RMAN repositoryWhat is RMAN repository?RMAN repository is collection of target database which store information about RMAN backup, recovery and maintenance.When recovery catalog is not in used then where RMAN repository stored?When recovery catalog is not in used then RMAN repository store in CONTROLFILE.Where we create recovery catalog?We have two option for it.1. We can create recovery catalog on target database through create separate tablespace for recovery catalog.Or2. We can create separate database for recovery catalog.NOTE: if we have more than one database then separate database for recovery catalog is recommended instead of separate tablespace.How recovery catalog store information about RMAN repository?After creation of recovery catalog we have to manually register each target database to recovery catalog.Should we need to take backup of recovery catalog?Yes, We must take backup of recovery catalog because recovery catalog store most important information about RMAN backup, recovery and RMAN configuration if we lost recovery catalog then we can’t get those information so it is always recommended to take recovery catalog backup.How take recovery catalog backup?1. If recovery catalog created as separate tablespace on target databaseThen just export recovery catalog tablespace and store in backup drive.2. If recovery catalog created as separate database.Then we can use any backup method which we use for target database for instance we can use user managed backup or we can also use RMAN method.Is there any view for query information about recovery catalog?Yes, actually for recovery catalog we need to create separate user. And after user and recovery catalog creation there is some view created in recovery catalog users with RC_ prefix. For instance: RC_DATABASE How to configure recovery catalog for target database?There are three steps for recovery catalog configuration.1. Configure the database that will content the recovery catalog,2. Create owner for recovery catalog3. Create recovery catalog SQL> conn sys@dev as sysdbaEnter password:Connected.SQL> create tablespace CATDB2 datafile 'c:\oracle\catdb01.dbf' size 10m;Tablespace created.NOTE: Here I am create separate tablespace for recovery catalog on target database.SQL>conn sys@dev as sysdbaConnected.SQL> create user CATDB2 identified by CATDB3 default tablespace CATDB;NOTE: Owner for recovery catalogSQL> grant connect,resource to catdb;Grant succeeded.SQL> grant recovery_catalog_owner to CATDB;Grant succeeded.NOTE: Grant the RECOVERY_CATALOG_OWNER role to the schema owner. This role provides the user with all privileges required to maintain and query the recovery catalog.C:\>rmanRecovery Manager: Release 10.1.0.5.0 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.RMAN> connect catalog catdb/catdb@devconnected to recovery catalog databaserecovery catalog is not installedRMAN> create catalog tablespace CATDB;recovery catalog createdNOTE: now recovery catalog is created.SQL> conn catdb/catdb@devConnected.SQL> select table_name from user_tables where rownum =1;TABLE_NAME------------------------------RCVERNOTE: after recovery catalog creation we can check all recovery catalog views is created in recovery catalog owner.What we do after create recovery catalog?Now just register target database to recovery catalog and store RMAN repository in recovery catalog.C:\>rman target sys@dev catalog catdb/catdb@devRecovery Manager: Release 10.1.0.5.0 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.target database Password:connected to target database: DEV (DBID=3718891706)connected to recovery catalog databaseRMAN> register database;database registered in recovery catalogstarting full resync of recovery catalogfull resync completeNOTE: “target sys@dev” is use for target database which we want to register to recovery catalog through recovery catalog owner “catdb/catdb@dev”.

Redo Log File Status

2008-06-03T03:55:00.000-07:00

Redo log file is very crucial for database recovery...and most of time we confuse about "status" of v$log file for redo log files.
1. UNUSEDWhenever you create any redo log file ...that time v$log view showing status "UNUSED"2. CURRENTAfter Creation when redo log "lgwr" process use that file ....that time v$log view showing status "CURRENT"3. ACTIVEIf database running in archivelog mode then after log switch "arch" process use log file and create archivelog file that time v$Log file view showing status "ACTIVE"4. CLEARINGWhenever any log file is corrupted or missing ( which is not "active" or not "current") then we use "alter database clear logfile" statement to recreate corrupted log file ... that time v$log view showing status "CLEARING" after clearing it is showing "UNUSED"5. INACTIVEDuring archivelog file creation v$log view showing status "ACTIVE" and when archivelog file is created then it is showing "INACTIVE" for particular one redo log file at a time.6. CLEARING_CURRENTIf any error occured during "alter database clear logfile" then v$log file view showing "CLEARING_CURRENT".

During Import table created in wrong tablespace.

2008-06-03T03:53:00.000-07:00

During import table is created in other than default tablespace is happen what is reason.Suppose we have two databases.1. Orcl2. HgcNow i created one table in ORCL database in SYSTEM tablespace.
SQL> create table test_orcl ( no number) tablespace SYSTEM;Table created.Now in HGC database i import above created table in user default tablespace.
SQL> conn system/oracle@hgcConnected.SQL> alter user scott default tablespace USERS;User altered.NOTE: 1. I assign default tablespace "USERS" to scott user which import table.2. Scott user having "connect" & "resource" role privilegesQuestion: NOW TELL me during IMPORT in which tablespace "USERS" or "SYSTEM" tbs table "test_orcl" created ?NOW guess your answer..we will check later ...
SQL> conn scott/tiger@OrclConnected.SQL> host exp scott/tiger@Orcl file=c:\test_orcl.dmp tables=test_orclExport: Release 10.1.0.5.0 - Production on Wed Jan 2 11:59:18 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.. . exporting table TEST_ORCL 0 rows exportedExport terminated successfully without warnings.NOW import table in HGC database.
SQL> conn scott/tiger@HGCConnected.SQL> host imp scott/tiger@HGC file=c:\test_orcl.dmp fromuser=scott touser=scottImport: Release 10.1.0.5.0 - Production on Wed Jan 2 12:02:26 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.. . importing table "TEST_ORCL" 0 rows importedImport terminated successfully without warnings.now we check in which tablespace table is created ?
SQL> select table_name,tablespace_name2 from user_tables3 where table_name='TEST_ORCL';TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------TEST_ORCL SYSTEMofh table is created in SYSTEM tablespace instead of user default tablespace means "USERS' why it so ?Again import table but now make one changes ...1. Revoke "resource" role and grant explicit quota on tablespace to user.
SQL> conn system/oracle@hgcConnected.SQL> revoke resource from scott;Revoke succeeded.SQL> alter user scott quota 100m on users;User altered.NOW import again...
SQL> conn scott/tiger@hgcConnected.SQL> drop table test_orcl purge;Table dropped.SQL> host imp scott/tiger@HGC file=c:\test_orcl.dmp fromuser=scott touser=scottImport: Release 10.1.0.5.0 - Production on Wed Jan 2 12:06:51 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.. . importing table "TEST_ORCL" 0 rows importedImport terminated successfully without warnings.SQL> select table_name,tablespace_name2 from user_tables3 where table_name='TEST_ORCL';TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------TEST_ORCL USERSBut what is actual reason...?1. Resource role having "unlimited tablespace" privileges means database user have quota on all tablespace in database if user having "unlimited tablespace" privileges.2. table is created in "system" tablespace becuase tables is actually created in SYSTEM tablespace on "ORCL" database during first time creation.
Export file created by EXPORT:V10.01.00 via conventional pathimport done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set"CREATE TABLE "TEST_ORCL" ("NO" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MA""XTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL ""DEFAULT) TABLESPACE "SYSTEM" LOGGING NOCOMPRESS". . skipping table "TEST_ORCL"

How to Drop Datafile from tablespace

2008-06-03T03:52:00.001-07:00

How to Drop Datafile from tablespace.Datafile is physical part of database and it is take storage device /file system to store/save oracle data.Whenever datafile grow more space needed, sametime unwanted datafile created, sametime datafile created in wrong tablespace then we need to drop datafile.Below is overview to drop a datafile from tablespace NOTE: Use below procedure after test on TESTING database.Oracle 8iThere is no direct sql command to drop datafile from tablespace.In that case we need to drop tablespace after move all data to new tablespace.1. create new tablespace2. move all table to new tablespace3. move all index to new tablespace4. move all other objects to new tablespace5. drop old tablespace with including contents;6. through OS command remove all datafiles belongs to droped tablespace.Oracle 9ir1Again There is no direct sql command to drop datafile from tablespace.In that case we need to drop tablespace after move all data to new tablespace.1. create new tablespace2. move all table to new tablespace3. move all index to new tablespace4. move all other objects to new tablespace5. drop old tablespace with including contents and datafiles;NOTE: Oracle9ir1 is introduce “and datafiles” clause with drop tablespace statement which remove datafiles from file system.Oracle 9ir2Again There is no direct sql command to drop datafile from tablespace.In that case we need to drop tablespace after move all data to new tablespace.1. create new tablespace2. move all table to new tablespace3. move all index to new tablespace4. move all other objects to new tablespace5. drop old tablespace with including contents and datafiles;But we can drop TEMPFILE through below statement.SQL> alter database tempfile ‘/u02/oracle/temp01.dbf’ drop including datafiles;[not tested]Oracle 10gr1Same like Oracle 9ir2Oracle 10gr2Now in 10gr2 oracle introduce to drop datafile or tempfile from tablespace through single sql command with few restrictions.From Oracle Documentation:You use the DROP DATAFILE and DROP TEMPFILE clauses of the ALTER TABLESPACE command to drop a single datafile or tempfile. The datafile must be empty. (A datafile is considered to be empty when no extents remain allocated from it.) When you drop a datafile or tempfile, references to the datafile or tempfile are removed from the data dictionary and control files, and the physical file is deleted from the file system or Automatic Storage Management (ASM) disk group.Datafiles:Alter tablespace data DROP DATAFILE ‘/u02/data01.dbf’;Tempfiles:Alter tablespace temp DROP TEMPFILE ‘/u02/temp01.dbf’;Restrictions for drop datafilesThe following are restrictions for dropping datafiles and tempfiles:• The database must be open.• If a datafile is not empty, it cannot be dropped.If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.• You cannot drop the first or only datafile in a tablespace.This means that DROP DATAFILE cannot be used with a bigfile tablespace.• You cannot drop datafiles in a read-only tablespace.• You cannot drop datafiles in the SYSTEM tablespace.• If a datafile in a locally managed tablespace is offline, it cannot be droppedOracle 11gr1Same like Oracle 10gr2Reference:http://www.akadia.com/services/ora_remove_datafile.html http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1396

Database & Patch set Upgrade

2008-06-03T03:51:00.000-07:00

Database & patch upgrade is one of the most important work for Database Administrator.Before going further first understand what are “upgrade” & “migration” & “patch set” & “CPU patch” word.Upgrade:Usually use for oracle database upgrade.Eg: upgrade from oracle 9i to oracle 10g.Migration:Usually use for migrate non oracle database to oracle database.Eg: From SQL server database to oracle databasePatch set:There are two types of patch upgrade 1. Patch set updateFrom one oracle version to another oracle version Eg: oracle 9i to oracle 10gNOTE: Patch set based on oracle base release Eg: oracle 10gr1 (10.1.0.2.0) and for 10gr2 (10.2.0.1.0)2. CPU patch (Critical Patch update)Patches is apply for fix database bug suppose after apply latest patch set for current release if there is any bug occur then oracle release cpu patches in regular interval to fix those bug.Eg: oracle 10gr1 base rel: 10.1.0.2.0, Latest Patch set: 10.1.0.5.0, Latest CPU patch: Jan-08 CPU patch for 10.1.0.5.0NOTE: CPU patch based on latest patch set.For more info: http://www.oracle.com/technology/deploy/security/alerts.htmHow to upgrade database to oracle 10gr2?Determine the Upgrade Path to the New Oracle Database 10g Releasehttp://download.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#i1007814Before upgrading process we need to define upgrade methodThere are four methods to upgrade our database1. DBUA (Database upgrade assistant)2. Manually upgrade3. Export/Import4. Data copyinghttp://download.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#i694345Patch set upgrade example:1. From oracle 9ir2 (9.2.0.2 to 9.2.0.8) on Windows http://babudba.blogspot.com/2007/11/patch-installation.html2. From oracle 9ir2 (9.2.0.1 to 9.2.0.7) on Solaris http://sabdarsyed.blogspot.com/2007/02/upgrade-oracle-database-9201-to-9207-on.html3. From oracle 10gr1 (10.1.0.2 to 10.1.0.5) on windowshttp://dbataj.blogspot.com/2007/06/upgrading-database-from-101020-to.html4. From oracle 10gr2 (10.2.0.2 to 10.2.0.3) on windowshttp://babudba.blogspot.com/2007/11/upgrade-oracle-10202-to-10203.html5. From oracle 10gr2 (10.2.0.1 to 10.2.0.3) on Linuxhttp://sabdarsyed.blogspot.com/2007/03/upgrading-oracle-database-server-10g.html

Critical Patch Update

2008-06-03T03:50:00.000-07:00

Example : Below procedure based on our test enviourment if you follow this instruction please go through README.TXT file comes with PATCH SET.Patch Installation Procedures for Oracle Database Release 10.1.0.5Patch Number : p5907304_10105_WINNT.zipSYSTEM Overview
Platforms : Windows XP SP2Database : 10.1.0.5.0OPatch Utility InformationYou must use the OPatch utility release 1.0.0.0.57 or later. You can download it from OracleMetaLink with patch 2617419.Check Oracle Inventory SetupC:\>set ORACLE_SID=ORCLC:\>set ORACLE_HOME=c:\oracle\product\10.1.0\db_1C:\>cd c:\oracle\product\10.1.0\db_1\opatchC:\Oracle\product\10.1.0\Db_1\OPatch>opatch lsinventoryOracle Interim Patch Installer version 1.0.0.0.53Note: if Opatch utility version is <= 1.0.0.0.53 then you have to download OPATCH utility from metalink site.[output cut]Oracle Home = c:\oracle\product\10.1.0\db_1Location of Oracle Universal Installer components = C:\oracle\product\10.1.0\db_1\ouiLocation of OraInstaller.jar = "C:\oracle\product\10.1.0\db_1\oui\jlib"Oracle Universal Installer shared library = C:\oracle\product\10.1.0\db_1\oui\lib\win32\oraInstaller.dllLocation of Oracle Inventory Pointer = N/ALocation of Oracle Inventory = C:\oracle\product\10.1.0\db_1\inventoryPath to Java = "C:\oracle\product\10.1.0\db_1\jre\1.4.2\bin\java.exe"Log file = c:\oracle\product\10.1.0\db_1/.patch_storage//*.logCreating log file "C:\oracle\product\10.1.0\db_1\.patch_storage\LsInventory__06-13-2007_20-18-49.log"Result:There is no Interim PatchOPatch succeeded.OPatch returns with error code = 0Preinstallation Instructions1. Ensure that your Oracle Database installation is the same release for which you are applying this patch.2. Back up the current database that is installed-->ORACLE_HOME or ORA INVENTORY3. Perform a clean shutdown of all Oracle servicesC:\>net stop OracleServiceORCLThe following services are dependent on the OracleServiceORCL service.Stopping the OracleServiceORCL service will also stop these services.OracleDBConsoleorclDo you want to continue this operation? (Y/N) [N]: YThe OracleDBConsoleorcl service is stopping...............The OracleDBConsoleorcl service was stopped successfully.The OracleServiceORCL service is stopping...........The OracleServiceORCL service was stopped successfully.C:\>net stop OracleServiceCATDBThe OracleServiceCATDB service is stopping......The OracleServiceCATDB service was stopped successfully.C:\>LSNRCTL stopLSNRCTL for 32-bit Windows: Version 10.1.0.5.0 - Production on 13-JUN-2007 20:30:30Copyright (c) 1991, 2004, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))The command completed successfullyC:\>net stop OracleCSServiceThe OracleCSService service was stopped successfully.C:\>net stop OracleOraDb10g_home1iSQL*Plus..The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.4. Set the PERL5LIB and PATH environment variables to point to Perl in Oracle Home C:\>set PERL5LIB=C:\oracle\product\10.1.0\db_1\perl\5.6.1\lib;%PERL5LIB%C:\>set PATH=C:\oracle\product\10.1.0\db_1\perl\5.6.1\bin\MSWin32-x86;%PATH%5. set ORACLE_HOME variableC:\>set ORACLE_HOME=c:\oracle\product\10.1.0\db_16. Check if the java and jar executables are present in your Oracle Home-->JAVA = %ORACLE_HOME%\jre\\bin-->JAR = %ORACLE_HOME%\jdk\bin7. Go to directory where download the patchC:\Oracle\product\10.1.0\Db_1\OPatch>opatch apply c:\5907304Post Installation Instructions1. Start All Oracle Services.C:\>net start OracleServiceORCLC:\>net start OracleServiceCATDBC:\>set ORACLE_SID=orclC:\>emctl start dbconsoleC:\>LSNRCTL startC:\>net start OracleCSServiceC:\>net start OracleOraDb10g_home1iSQL*Plus2. For each database instance running out of the ORACLE_HOME being patched, connect to the database using SQL*Plus as SYSDBA and run catcpu.sql as follows:C:\>rem go to CPU directoryC:\>CD C:\oracle\product\10.1.0\db_1\CPU\CPUApr2007C:\Oracle\product\10.1.0\Db_1\cpu\cpuapr2007>sqlplus /nologSQL*Plus: Release 10.1.0.5.0 - Production on Thu Jun 14 08:21:34 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn sys as sysdbaEnter password:Connected.SQL> select instance_name from v$instance;INSTANCE_NAME----------------orclSQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> STARTUP MIGRATEORACLE instance started.Total System Global Area 356515840 bytesFixed Size 789400 bytesVariable Size 330036328 bytesDatabase Buffers 25165824 bytesRedo Buffers 524288 bytesDatabase mounted.Database opened.SQL> @CATCPU.SQL[OUTPUT CUT]SQL> SHUTDOWN IMMEDIATE;Database closed.Database dismounted.ORACLE instance shut down.SQL> quitIf catcpu.sql reports any Invalid Objects, compile the invalid objects as followsC:\>cd C:\oracle\product\10.1.0\db_1\rdbms\adminC:\Oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nologSQL*Plus: Release 10.1.0.5.0 - Production on Thu Jun 14 08:47:26 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL> conn sys as sysdbaEnter password:Connected to an idle instance.SQL> STARTUPORACLE instance started.Total System Global Area 356515840 bytesFixed Size 789400 bytesVariable Size 330036328 bytesDatabase Buffers 25165824 bytesRedo Buffers 524288 bytesDatabase mounted.Database opened.SQL> @utlprp.sql 0SQL> select OBJECT_NAME from dba_objects where status = 'INVALID';no rows selected3. If any databases have been created without using DBCA (that is, using the command line), and you need to monitor such a database by using Oracle Enterprise Manager 10g, follow these steps:> sqlplus SYS / AS SYSDBASQL> ALTER USER DBSNMP ACCOUNT UNLOCKSQL> PASSWORD DBSNMPChanging password for DBSNMPNew password: new_passwordRetype new password: new_password

Connecting with sys user with Or without pwd

2008-06-03T03:49:00.001-07:00

As we know "SYS" & "SYSDBA" is superuser privilege in oracle database.There is two method to connect SYSDBA user.
1. WITH PASSWORD2. WITHOUT PASSWORDFor example:SQL> CONN / AS SYSDBA ---without passwordFor without password need follow thing to be done.
1. Oracle Database User must have SYSDBA privileges.2. Operating System User must add in DBA group.On windows: ORA_DBA group On linux: DBA group3. In sqlnet.ora file must content "NTS" in below lineSQLNET.AUTHENTICATION_SERVICES=(NTS)For example:SQL> CONN SYS/PWD AS SYSDBA --- with password or prevent to connect without password.
1. Remove OS user from DBA group2. Edit sqlnet.ora file and change "NONE" to "NTS" in below lineSQLNET.AUTHENTICATION_SERVICES=(NONE)Default Value is NTS when sqlnet.ora file created.