NAVEEN

Tuesday, December 29, 2009

PATCH apply 10.2.0.2.0 - 10.2.0.4.0

Upgrade Oracle from 10.2.0.1 To 10.2.0.4

1. Patch Set Overview

Patch set release 10.2.0.4. Before installing this patch set you must be need 10.2.0.1 version.

2. Requirements

Oracle DB : Oracle 10.2.0.1 (later)
Operating System: Windows


3. Pre - Installation Tasks

1. Identify prior installation

Before installing this patch you must install oracle 10.2.0.1 (or later version)

2. Download Patch set

Download 6810189 patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.



3. Shutdown oracle database.

C:\>set oracle_sid= GOLDLINK

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 10:49:26 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys@GOLDLINK as sysdba
Enter password:
Connected.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

4. Stop all services

C:\>set oracle_sid=GoldLink

C:\>net stop OracleDBConsoleGOLDLINK
The OracleDBConsoleGOLDLINK service is stopping................
The OracleDBConsoleGOLDLINK service was stopped successfully.


C:\>net stop OracleServiceGOLDLINK
The OracleServiceGOLDLINK service is stopping.
The OracleServiceGOLDLINK service was stopped successfully.


C:\>net stop OracleOraDB10g_Home1iSQL*Plus..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.

C:\>net stop OracleOraDB10g_Home1TNSListener
The OracleOraDb10g_home1TNSListener service is stopping.
The OracleOraDb10g_home1TNSListener service was stopped successfully.

Note: while patch installation time If you face Distributed Transaction Coordinator still running. You should be stop this service

C:\>net stop msdtc
The Distributed Transaction Coordinator service is stopping.
The Distributed Transaction Coordinator service was stopped successfully.

4. Backup your database.

Oracle recommends that you create a backup of the Oracle 10g installation before you install the patch set.

5. Check Tablespace Sizes and Set Parameter Values

Review the following sections before upgrading a database.

8. Upgrade the Database

use runinstaller to INSTALL the PATCH


ERROR:

if any errors like "error in invoking ....." . compile the lib file manually as a oracle user using the following command.


[oracle@localhost bin]$ make -f /ora10gsoft/10.2.0/network/lib/ins_net_client.mkBuilding client shared library libclntsh.so ...
Call script /ora10gsoft/10.2.0/bin/genclntsh ...
/ora10gsoft/10.2.0/bin/genclntsh
genclntsh: genclntsh: Could not locate /ora10gsoft/10.2.0/network/admin/shrept.lst
make: *** [/ora10gsoft/10.2.0/lib/libclntsh.so] Error 1

Note : create a "/ora10gsoft/10.2.0/network/admin/shrept.lst" file manually with any content or blank

[oracle@localhost bin]$ make -f /ora10gsoft/10.2.0/network/lib/ins_net_client.mk
Building client shared library libclntsh.so ...
Call script /ora10gsoft/10.2.0/bin/genclntsh ...
/ora10gsoft/10.2.0/bin/genclntsh
Built /ora10gsoft/10.2.0/lib/libclntsh.so ... DONE


Then press retry in the ERROR showed in popup window




After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

1. Start all services

2. Connect sys user

C:\> sqlplus /NOLOG

SQL> CONNECT SYS/SYS_password AS SYSDBA

3. Enter the following SQL*Plus commands:

SQL> STARTUP UPGRADE

SQL> SPOOL patch.log

SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sql

SQL> SPOOL OFF

Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script.

This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems.

4. Restart the database:



SQL> SHUTDOWN
SQL> STARTUP

5. Compile Invalid Objects

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql

Identifying Invalid Objects
The DBA_OBJECTS view can be used to identify invalid objects using the following query:

COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

With this information you can decide which of the following recompilation methods is suitable for you.
The Manual Approach
For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types:

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

Notice that the package body is compiled in the same way as the package specification, with the addition of the word "BODY" at the end of the command.

An alternative approach is to use the DBMS_DDL package to perform the recompilations:

EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');

This method is limited to PL/SQL objects, so it is not applicable for views.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Backup Strategy in ORACLE

COLD BACKUP (OFFLINE)

When the Database is DOWN (No Archive Mode)
Consistent Backup
Files - Datafiles, Control files.

Backup
Step 1: Stop the Listeners
Step 2: Shutdown the database normal or immediate
Step 3: Copy the determined files (datafiles & control files) to the selected backup disk.
# cp /ora10gdata/nagios/*.* /ora10gdata/backup

Recovery
Step4: Move to copied files to the base (actual) location.
# cp /ora10gdata/backup/ *.* / ora10gdata/nagios/
Step 5: Alter database open resetlogs;


v$recover_file;


HOT BACKUP (ONLINE)

When the Database is UP (Archive Mode)
Inconsistent Backup
Files - Datafiles, Control

Convert to Archive Mode
1. Modify the pfile
i) log_archive_format=%s%arch
i) log_archive_format= /arch
2. Startup pfile=”/…” mount;
3. Alter database archivelog;
4. Alter database open;

Sql>archive loglist;


Backup (1)
Step 1: Alter system switch logfile;
Step 2: Alter database begin backup; (or) Alter tablespace TS1 begin backup;
# Select status from v$backup;
Step 3: Copy the datafiles alone belong to the tablespace to the selected backup disk.
# cp /ora10gdata/nagios/*.* /ora10gdata/backup
Step 4: Alter database end backup; (Or) Alter tablespace TS1 end backup;
Step 5: Alter system switch logfile;

Backup (2)
Step 6: Backup the control files
# alter database backup controlfile to ‘/ora10gdata/backup/ctl.bak’;
Or
# alter database backup controlfile to trace;

Recovery

Step 7: Goto the location /ora10gdata/backup and remove temp files.
rm –rf temp.*
Step 8: Copy the backup files to the original disk.
# cp /ora10gdata/backup /*.* / ora10gdata/nagios/
# cp /ora10gdata/backup /*.bak / ora10gdata/nagios/
# cp /ora10gdata/backup /*.bak / ora10gdata/nagios/ control01.ctl
# cp /ora10gdata/backup /*.bak / ora10gdata/nagios/ control02.ctl
# cp /ora10gdata/backup /*.bak / ora10gdata/nagios/ control03.ctl


Step 9: sqlplus ‘/as sysdba’
sql>Startup pfile nomount;

recover database until cancel using backup controlfile;
(Or) recover database until change SCN;
(0r) recover database until time;
Step 5: Alter database open resetlogs;

Bash Profile for ORACLE

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs


unset USERNAME

ORACLE_BASE=/ora10gsoft
ORACLE_HOME=/ora10gsoft/10.2.0
ORACLE_SID=imstest
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID

export ORACLE_TERM=xterm
# export TNS_ADMIN= Set if sqlnet.ora, tnsnames.ora, etc. are not in $ORACLE_HOME/network/admin
export NLS_LANG=AMERICAN;
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH

# Set shell search paths
export PATH=$PATH:$ORACLE_HOME/bin

Auditing

Introduction
This article will introduce the reader to the basics of auditing an Oracle database. Oracle's RDBMS is a functionally rich product and there are a number of auditing alternatives available to the reader. Because auditing Oracle is such a huge subject, doing all of it justice would take an entire book, so this paper will cover the basics of why, when and how to conduct an audit. It will also use a couple of good example cases to illustrate how useful Oracle audit can be to an organization.
The Issues
There are a number of basic issues that should be considered when contemplating using Oracle's auditing features. These are as follows:
• Why is audit needed in Oracle?
Is this a strange question? Well, lots of companies don't actually use the internal audit features of Oracle. Or, when they do use them, they are so overwhelmed with choice, they turn on everything for good measure, then realise there is far too much output to read and digest so they quickly turn it all off again. It is quite common to use firewalls, intrusion detection systems (IDS) and other security tools to determine if the network or operating system is being misused or abused. So why not audit what users are doing to the "crown jewels" of an organization, the data. Oracle audit can help detect unauthorized access and internal abuse of the data held in the database.
• When should Oracle users be audited?
A simple basic set of audit actions should be active all the time. The ideal minimum is to capture user access, use of system privileges and changes to the database schema structure. This basic set will not show attempted access to specific data that shouldn't be accessed; however, it will give a reasonably simple overview of "incorrect" access and use of privileges. If an employee is suspected of inappropriate actions or if an attack has been suspected then more detailed audit can be turned on for specific tables. From a data management point of view, auditing data changes for all tables in the database is not really practical and could also affect performance. Monitoring data change access on critical tables (such as salaries in a HR database) should be considered.
• How can Oracle users be audited?
The standard audit commands allow all system privileges to be audited along with access at the object level to any table or view on the database for select, delete, insert or update. Audit can be run for either successful or unsuccessful attempts or both. It can be for each individual user or for all users and it can also be done at the session level or access level. At action level a single record is created per action and at session level one record is created for all audit actions per session.
• What are the performance and complexity issues?
Audit is generally perceived to be complex and slow. The reason for this is usually ignorance. If many or all options are turned on, then the resultant audit trail produced can be large and difficult to interpret and manage. Furthermore, if audit is used on all tables and views in the database, then this can have an effect on performance. Every time an action performed is auditable a record is written to the database; clearly the more audit is used, the more records will be written to the system tablespace purely for audit. In some cases double the amount of access to the database can be performed: the original write and the audit record being written.
The watchword here is simplicity and caution. Use only the audit that is needed to give an overall view of what is happening and for detailed monitoring of critical data and objects. The simpler the audit trail set-up, the more likely it is that the data will be analyzed and be of some use. It is important to define what actions or abuses are being checked for so that simple reports can be written to filter the audit trail for these actions. A default installation of Oracle has audit turned off by default and Oracle does not come with any standard default audit settings or reports to analyse any audit trail produced. These reasons, and the fact that there are many options available are, in my opinion, why audit is perceived to be complex.
The standard audit commands do not allow audit to be performed at row level. It is also not possible to audit the actions of privileged users such as SYS and "as sysdba" until Oracle 9iR2.
Oracles Audit Facilities
The task of auditing an Oracle database does not have to be limited only to the audit commands; other techniques can be employed as well. Here are some of the main methods that can be used to audit an Oracle database:
• Oracle audit
This is really the subject of this paper. All privileges that can be granted to a user or role within the database can be audited. This includes read, write and delete access on objects at the table level. For more detailed audit, the database triggers need to be employed.
• System triggers
These were introduced with Oracle 8 and allow the writing of database triggers that fire when system events take place. These include start- up and shutdown of the database, log-on and log-off attempts, and creation, altering and dropping of schema objects. With the aid of autonomous transactions, these allow a log to be written for the above system events.
• Update, delete, and insert triggers
This is the second line of defence in trying to understand users' actions at a more detailed row level. Database triggers need to be written to capture changes at the column and row level. It is possible to write complete rows of data before and after the change being made to a log table in the database. The use of this type of logging is very resource intensive, as many extra records are written and stored. The one failing with this method is that read access cannot be captured with normal database triggers.
• Fine-grained audit
Fine-grained audit solves the problem of capturing read access. This feature is also based on internal triggers that fire when any piece of SQL is parsed. This is very efficient, as the SQL is parsed once for audit and execution. The feature uses predicates that are defined and tested each time the relevant object is accessed. Fine-grained audit is managed by a PL/SQL package called DBMS_FGA. A PL/SQL procedure is executed every time a "match" is made with the predicate. This method allows the audit to be performed down to the row and column level and to also for read statements. Readers should be forewarned that use of this feature requires programming skills.
• System logs
Oracle generates many log files and many of them can provide useful information to assist in auditing the database. One good example is the alert log used by the database to record start-up and shutdown as well as any structural changes such as adding a datafile to the database.
This paper is going to explore only the standard built-in audit commands. The other options will be left for future articles.
Some Examples
Because of the myriad of possibilities, auditing an Oracle database can be a daunting task. In order to try and simplify the discussion of what can be done, we will discuss a couple of simple examples that we will explore and work through.
• Auditing database access
This is a fundamental check to find out who accesses the database, from where and when. Log-on failures can be captured as well as log- ons at strange (anomolous) times of the day.
• Auditing changes to the database structure
In a production database, no user should ever change the schema structure. DBAs should make changes for upgrades at specific times; any other changes should be regarded as suspicious. Watching for structural changes can turn up indicators of incorrect use of the database.
A third simple example that could have been employed here is to audit any use of system privileges. However, this example is left to the reader to explore.
The final group of audit commands that can be employed is to audit any data changes to objects themselves. Unfortunately, as the requirements are very application and installation specific, this is beyond the scope of this paper.
Audit within Oracle is broken into three areas: statement auditing such as CREATE TABLE or CREATE SESSION, privilege auditing such as ALTER USER, and object level auditing such as SELECT TABLE.
Basic Configuration
The audit trail can be either written to the database or to the operating system. Writing the audit trail to the operating system is, in some senses, more secure but the implementation is not available on all platforms and is platform specific. In this article we will concentrate on using the database to store the audit trail.
Audit is turned on for writing to the database by adding the following line to the init.ora file. A symbolic link to it can usually be found in $ORACLE_HOME/dbs
audit_trail = db
The database now needs to be restarted. A simple check will show that audit is indeed now turned on.
SQL> select name,value from v$parameter
where name like 'audit%';

NAME VALUE
------------------------------ ------------------------------
audit_trail DB
audit_file_dest ?/rdbms/audit

SQL>
No audit actions are captured yet until audit actions are defined; that is, except for privileged access to the database, starting and stopping of the database, and structural changes such as adding a datafile. These are logged to operating system files in $ORACLE_HOME/rdbms/audit unless audit_file_dest is redefined in the init.ora file. On Windows these events appear in the Event Viewer.
To check if any privilege or statement audit actions are enabled, do the following:
SQL> select * from dba_stmt_audit_opts
union
select * from dba_priv_audit_opts;

To find out what objects are being audited, query the view dba_obj_audit_opts.
The Worked Examples
Let us now work through our two example cases and see what can be learned. First, turn on audit for the access attempts to the database:
SQL> audit create session;

Audit succeeded.

The above command will capture access by all users by access and whether successful or unsuccessful. The default for this command is by access.
Note: The format of all audit commands from the Oracle documentation is as follows:
audit {statement_option|privilege_option} [by user] [by
{session|access}] [ whenever {successful|unsuccessful}]
Only the statement_option or privilege_option part is mandatory. The other clauses are optional and enabling them allows audit be more specific.
Note:For a user to define audit statements, the privilege "AUDIT SYSTEM" needs to have been granted first. The users that have this privilege can be checked as follows:
SQL> select *
from dba_sys_privs
where privilege like '%AUDIT%';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CTXSYS AUDIT ANY NO
CTXSYS AUDIT SYSTEM NO
DBA AUDIT ANY YES
DBA AUDIT SYSTEM YES
IMP_FULL_DATABASE AUDIT ANY NO
MDSYS AUDIT ANY YES
MDSYS AUDIT SYSTEM YES
WKSYS AUDIT ANY NO
WKSYS AUDIT SYSTEM NO

9 rows selected.

The above results are for a 9i database, the default users MDSYS, CTXSYS and WKSYS would likely be good targets for attackers, as any audit actions could be turned off as one of these users to hide any actions undertaken.
Now that audit will capture all access attempts, we need to wait for some users to log in to do work. So while they do that, let's set up the audit to capture alterations to the schema. For the sake of brevity, in this example, not all schema object changes will be captured. Changes to tables, indexes, clusters, views, sequences, procedures, triggers, libraries and many more can be captured. In this example, audit will be enabled on an example set. Turning on the audit can be performed as a two-stage process, generate the audit commands and then run them as follows:
set head off
set feed off
set pages 0
spool aud.lis
select 'audit '||name||';'
from system_privilege_map
where (name like 'CREATE%TABLE%'
or name like 'CREATE%INDEX%'
or name like 'CREATE%CLUSTER%'
or name like 'CREATE%SEQUENCE%'
or name like 'CREATE%PROCEDURE%'
or name like 'CREATE%TRIGGER%'
or name like 'CREATE%LIBRARY%')
union
select 'audit '||name||';'
from system_privilege_map
where (name like 'ALTER%TABLE%'
or name like 'ALTER%INDEX%'
or name like 'ALTER%CLUSTER%'
or name like 'ALTER%SEQUENCE%'
or name like 'ALTER%PROCEDURE%'
or name like 'ALTER%TRIGGER%'
or name like 'ALTER%LIBRARY%')
union
select 'audit '||name||';'
from system_privilege_map
where (name like 'DROP%TABLE%'
or name like 'DROP%INDEX%'
or name like 'DROP%CLUSTER%'
or name like 'DROP%SEQUENCE%'
or name like 'DROP%PROCEDURE%'
or name like 'DROP%TRIGGER%'
or name like 'DROP%LIBRARY%')
union
select 'audit '||name||';'
from system_privilege_map
where (name like 'EXECUTE%INDEX%'
or name like 'EXECUTE%PROCEDURE%'
or name like 'EXECUTE%LIBRARY%')
/
spool off
@@aud.lis
This will generate a set of audit commands that can be captured to a spool file, which is then run to enable the audit commands.
Another solution would be to audit the actual permissions granted to users by generating the audit commands from the database view dba_sys_privs. While this may seem to be a better solution and potentially involve less audit commands, it would not allow for the case when new permissions are granted to users. In this case, audit would also need to be enabled at the time the privileges are granted.
Now that all of the sample audit is now enabled, the settings can be viewed with this SQL:
SQL> select audit_option,success,failure
from dba_stmt_audit_opts
union
select privilege,success,failure
from dba_priv_audit_opts
/

AUDIT_OPTION SUCCESS FAILURE
---------------------------------------- ---------- ----------
ALTER ANY CLUSTER BY ACCESS BY ACCESS
ALTER ANY INDEX BY ACCESS BY ACCESS
ALTER ANY INDEXTYPE BY ACCESS BY ACCESS
ALTER ANY LIBRARY BY ACCESS BY ACCESS
?
EXECUTE ANY LIBRARY BY SESSION BY SESSION
EXECUTE ANY PROCEDURE BY SESSION BY SESSION

38 rows selected.
Every time a user attempts anything in the database where audit is enabled the Oracle kernel checks to see if an audit record should be created or updated (in the case or a session record) and generates the record in a table owned by the SYS user called AUD$. This table is, by default, located in the SYSTEM tablespace. This in itself can cause problems with potential denial of service attacks. If the SYSTEM tablespace fills up, the database will hang.
The AUD$ table is rare, as it is the only SYS owned table from which Oracle allows records to be deleted. If the audit trail is turned on and written to the database, then the numbers of records in this table need to be monitored carefully to ensure it doesn't grow too fast and fill the system tablespace. A purging strategy needs to be adopted to keep the size of the table in check and, if needed, to archive off audit trail records for future reference. One tactic could be to copy the records to summary tables that allow specific checks for abuse to be performed offline. These summary tables can be in a separate database for added security. Once copied, sys.aud$ can be truncated.
SYS.AUD$ can be moved to a different tablespace other than SYSTEM but check with Oracle support first, as this action is no longer supported.
Only users who have been granted specific access to SYS.AUD$ can access the table to read, alter or delete from it. This is usually just the user SYS or any user who has had permissions. There are two specific roles that allow access to SYS.AUD$ for select and delete, these are DELETE_CATALOG_ROLE and SELECT_CATALOG_ROLE. These roles should not be granted to general users.
Back to the examples, our users have been logging on and working throughout the day and created some audit records. These audit records can be viewed in a number of ways:
• By selecting from SYS.AUD$ - This is the raw audit trail
• By selecting from dba_audit_trail - This is a DBA view showing the raw audit trail.
• By selecting from dba_audit_session - This view shows just log-on and log-off actions.
A simple piece of SQL can show details of the connection attempts:
SQL> get check_create_session
1 --
2 -- check_create_session.sql
3 --
col username for a15
col terminal for a6
col timestamp for a15
col logoff_time for a15
col action_name for a8
col returncode for 9999
select username,
terminal,
action_name,
to_char(timestamp,'DDMMYYYY:HHMISS') timestamp,
to_char(logoff_time,'DDMMYYYY:HHMISS') logoff_time,
returncode
from dba_audit_session
SQL> /
USERNAME TERMIN ACTION_N TIMESTAMP LOGOFF_TIME RETURNCODE
--------------- ------ -------- --------------- --------------- ----------
SYS pts/1 LOGOFF 09042003:051046 09042003:051641 0
ZULIA pts/1 LOGON 09042003:051641 1017
SYS pts/1 LOGOFF 09042003:051649 09042003:053032 0
SYS pts/2 LOGOFF 09042003:052622 09042003:053408 0
ZULIA pts/1 LOGON 09042003:053032 1017

There are a number of simple abuses that can be checked for in the area of user access to the database. As examples for this paper we will look at the following:
• Failed log-on attempts
This can indicate fat fingers or attackers' attempts to gain unauthorized access the database. The following SQL highlights this:
SQL> select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY')
from dba_audit_session
where returncode<>0
group by username,terminal,to_char(timestamp,'DD-MON-YYYY');

COUNT(*) USERNAME TERMIN TO_CHAR(TIM
---------- --------------- ------ -----------
1 BILL pts/3 09-APR-2003
3 FRED pts/3 09-APR-2003
4 ZULIA pts/1 09-APR-2003
This shows two possible abuses, the first is the user Zulia attempting to log on and failing four times on the same day. This could be a forgotten password or it could be someone trying to guess his or her password. A change to the SQL as follows gives a bit more detail:
SQL> select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY'),returncode
from dba_audit_session
group by username,terminal,to_char(timestamp,'DD-MON-YYYY'),returncode;

COUNT(*) USERNAME TERMIN TO_CHAR(TIM RETURNCODE
---------- --------------- ------ ----------- ----------
1 BILL pts/3 09-APR-2003 1017
1 EMIL pts/1 09-APR-2003 0
1 EMIL pts/2 09-APR-2003 0
1 EMIL pts/3 09-APR-2003 0
1 EMIL pts/4 09-APR-2003 0
3 FRED pts/3 09-APR-2003 1017
3 SYS pts/1 09-APR-2003 0
1 SYS pts/2 09-APR-2003 0
1 SYSTEM pts/5 09-APR-2003 0
4 ZULIA pts/1 09-APR-2003 1017
1 ZULIA pts/1 09-APR-2003 0

11 rows selected.

SQL>
This reveals that the user successfully logged on on the same terminal on the same day. A number of failed log-ons should be agreed as part of these checks and the above SQL run every day. Those users with failure numbers above the threshold should be investigated.
• Attempts to access the database with non-existent users
One interesting extension to the above SQL is to find attempts to log in where the user doesn't exist. An audit record is still created in this case. The following SQL illustrates:
SQL> select username,terminal,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS')
2 from dba_audit_session
3 where returncode<>0
4 and not exists (select 'x'
5 from dba_users
6 where dba_users.username=dba_audit_session.username)
SQL> /

USERNAME TERMIN TO_CHAR(TIMESTAMP,'D
--------------- ------ --------------------
FRED pts/3 09-APR-2003 17:31:47
FRED pts/3 09-APR-2003 17:32:02
FRED pts/3 09-APR-2003 17:32:15
BILL pts/3 09-APR-2003 17:33:01

This is probably abuse. All attempts to log on with a user that doesn't exist should be checked each day and investigated.
• Attempts to access the database at unusual hours
Checks should be made for any attempts to access the database outside of working hours. These accesses could be genuine overtime work or maintenance but they could just as easily be unauthorized access attempts and should be checked as follows:
SQL> select username,
2 terminal,
3 action_name,
4 returncode,
5 to_char(timestamp,'DD-MON-YYYY HH24:MI:SS'),
6 to_char(logoff_time,'DD-MON-YYYY HH24:MI:SS')
7 from dba_audit_session
8 where to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') <
to_date('08:00:00','HH24:MI:SS')
9 or to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') >
to_date('19:30:00','HH24:MI:SS')
SQL> /

USERNAME TERMIN ACTION_N RETURNCODE TO_CHAR(TIMESTAMP,'D TO_CHAR(LOGOFF_TIME,
---------- ------ -------- ---------- -------------------- --------------------
SYS pts/1 LOGOFF 0 09-APR-2003 20:10:46 09-APR-2003 20:16:41
SYSTEM pts/5 LOGOFF 0 09-APR-2003 21:49:20 09-APR-2003 21:49:50
ZULIA pts/5 LOGON 0 09-APR-2003 21:49:50
EMIL APOLLO LOGON 0 09-APR-2003 22:49:12

SQL>
The above SQL shows any connections before 8:00 AM and after 7:30 PM. Any connections, particularly those made by privileged users such as SYS and SYSTEM, should be investigated. Particular attention can be made to the location from which the access was made. For instance, if privileged access is made from machines that are not in the administrator department, the administrator needs to find out why.
• Check for users sharing database accounts
The following SQL looks for users who are potentially sharing database accounts:
SQL> select count(distinct(terminal)),username
2 from dba_audit_session
3 having count(distinct(terminal))>1
4 group by username
SQL> /

COUNT(DISTINCT(TERMINAL)) USERNAME
------------------------- ----------
4 EMIL
3 SYS
3 ZULIA
SQL>
This shows that three users have accessed their accounts from more than one location. A further check could be to add a time component to see if they are accessed simultaneously and also to restrict the check per day. The above SQL gives some idea of the potential without complicating it too much. Again, these accounts and users should be investigated.
• Multiple access attempts for different users from the same terminal
The final example checks to find where multiple database accounts have been used from the same terminal. The SQL is again simple and could be extended to group by day and also to print out the users per terminal. This is a simple test to illustrate the abuse idea:
SQL> select count(distinct(username)),terminal
2 from dba_audit_session
3 having count(distinct(username))>1
4 group by terminal
SQL> /

COUNT(DISTINCT(USERNAME)) TERMIN
------------------------- ------
3 pts/1
2 pts/2
3 pts/3
3 pts/5

SQL>
This could indicate someone trying to gain access by trying many accounts and passwords, or it could indicate legitimate users sharing accounts for certain aspects of their work. In either case, the admin should investigate further.
There are, of course, many other scenarios that could indicate possible abuses. Checking for those is as simple as the cases depicted above. It will be left to the reader to experiment. Let me know what you find useful.
The second example case that audit actions were set for is to detect changes made to the database schema. This could include new objects being added or attempts to change existing objects within the database.
A simple piece of SQL will show any audit trail items that relate to objects being created or changed as follows:
col username for a8
col priv_used for a16
col obj_name for a22
col timestamp for a17
col returncode for 9999
select username,
priv_used,
obj_name,
to_char(timestamp,'DD-MON-YYYY HH24:MI') timestamp,
returncode
from dba_audit_trail
where priv_used is not null
and priv_used<>'CREATE SESSION'
/
SQL> @check_obj.sql

ZULIA CREATE TABLE STEAL_SALARY 09-APR-2003 20:07 0
PETE CREATE PROCEDURE HACK 09-APR-2003 20:42 0

This simple example shows that the user ZULIA has created a table and the user PETE has been writing PL/SQL procedures. Any changes such as this that are found should be investigated in a production database. Many more specific abuses can be checked for in relation to object and schema changes but, in general, no user should be able to alter the database schema in a production database. As a result, the check can remain pretty simple.
Protecting the Database Against These Abuses
The two examples given are just two of many possible scenarios that could be detected using Oracle's auditing facilities. Turning on and managing audit is one of the first steps to securing the database. Using audit should be part of an overall organization security plan and policy that includes Oracle. The database should be audited regularly for misconfiguration or known vulnerabilities that could allow security breaches to take place.
Because of its complex nature and vast number of different ways it can be used and configured, the best approach to securing Oracle will always be to follow the principle of least privilege. Once the database is part of the overall security plan and is configured correctly and checked regularly, then auditing it should be considered an important part of the strategy.
In general, do not grant any privileges to general users in a production database, remove most of the PUBLIC privileges and delete or lock and change the passwords of any default accounts. Ensure that users obey password policies and that the password management features of Oracle are employed.
It is important that the audit actions are planned from a performance and usability point of view and that the audit trail is managed. It is also important that the audit trail data is understood in terms of detecting abuse.
The author's recent book by the SANS Institute "Oracle security step- by-step - A survival guide for Oracle security" gives excellent guidelines on how to configure Oracle securely.
Conclusions
Oracle's auditing features are very powerful and sometimes seem very complex. As we saw in the introduction, there is more than one option available for auditing an Oracle database. It is possible to audit almost everything in the Oracle RDBMS with the standard features but not at the row level. If a high-level audit is needed, use the standard features to get a view of overall activity and then home in on the area of concern in more detail.
Because it is possible to audit almost any type of action in an Oracle database using the standard audit features, the reader should experiment with the most useful audit actions for their organization. Keep it simple and do not try to use everything. Above all, predetermine what data will be generated in the audit trail and the abuses that can be checked for. Write reports to check the audit trail and purge it regularly. Finally, monitor the reports each day and take the appropriate action.
For more detailed auditing, use database triggers and fine grained auditing. Keep in mind that both of these methods need programming skills to implement and report on, so they should be considered carefully. A lot of useful information can be gathered without resorting to row-level audit. Above all, employ least privilege principle to avoid any users making changes or reading data that they should not.

Monitoring Script

set verify off
set feedback off

connect username/password


set linesize 120
set pages 500

prompt - Database status
prompt ------------------------------------------------------------------------------------------------------

Select instance_name, status from v$instance;

Select name,open_mode from v$database;

prompt -

prompt ------------------------------------------------------------------------------------------------------
Tablespace Monitoring
prompt ------------------------------------------------------------------------------------------------------

SELECT ts.tablespace_name, "File Count",
TRUNC("SIZE(MB)", 2) "Size(MB)",
TRUNC(fr."FREE(MB)", 2) "Free(MB)",
TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
df."MAX_EXT" "Max Ext(MB)",
(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free"
FROM (SELECT tablespace_name,
SUM (bytes) / (1024 * 1024) "FREE(MB)"
FROM dba_free_space
GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*)
"File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name (+)
AND fr.tablespace_name = ts.tablespace_name (+)
ORDER BY "% Free" desc;

prompt -

prompt ------------------------------------------------------------------------------------------------------
Datafiles Monitoring
prompt ------------------------------------------------------------------------------------------------------

col tablespace_name for a20
col file_name for a40

SELECT tablespace_name,file_name, bytes/1024/1024 MB, status,autoextensible FROM dba_data_files order by tablespace_name;

prompt -

prompt ------------------------------------------------------------------------------------------------------
Database Buffer cache
prompt ------------------------------------------------------------------------------------------------------

select sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
round((sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) -
sum(decode(name, 'physical reads',value, 0))) /
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio" from v$sysstat;


prompt -

prompt ------------------------------------------------------------------------------------------------------
Library Buffer Cache
prompt ------------------------------------------------------------------------------------------------------

SELECT SUM(PINS-RELOADS)/SUM(PINS)*100 "Library Cache Hit Ratio",sum(reloads)/sum(pins) "RELOADS to PINS" FROM V$LIBRARYCACHE;

prompt -

prompt ------------------------------------------------------------------------------------------------------
Dictionary Cache Hit Ratio
prompt ------------------------------------------------------------------------------------------------------

SELECT (SUM(GETS-GETMISSES))/SUM(GETS)*100 "Dictionary Cache Hit Ratio" FROM V$ROWCACHE;

prompt -

prompt ------------------------------------------------------------------------------------------------------
Number of Users Connected
prompt ------------------------------------------------------------------------------------------------------

select count(*) "Number of users Connected" from v$session where type='USER';

prompt -

prompt ------------------------------------------------------------------------------------------------------
Active Users Count
prompt ------------------------------------------------------------------------------------------------------

SELECT count(*) "Number of Active Users" from v$session where type='USER' and status='ACTIVE';

prompt -

prompt ------------------------------------------------------------------------------------------------------
Monitoring User Session Connected
prompt ------------------------------------------------------------------------------------------------------



set linesize 750
column box format a20
column username format a7
column program format a20
column os_user format a20

select b.sid,b.serial#,a.spid, substr(b.machine,1,20) box,b.logon_time logon_date , to_char (b.logon_time, 'hh24:mi:ss') logon_time,
substr(b.username,1,7) username,
substr(b.osuser,1,20) os_user,
substr(b.program,1,20) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by os_user;

prompt ------------------------------------------------------------------------------------------------------
Active Transactions
prompt ------------------------------------------------------------------------------------------------------

select count(*) "Number of Active Transactions"
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address;

prompt-

prompt ------------------------------------------------------------------------------------------------------
Wait Time Ratio Monitoring
prompt ------------------------------------------------------------------------------------------------------

select METRIC_NAME,
VALUE
from SYS.V_$SYSMETRIC
where METRIC_NAME IN ('Database CPU Time Ratio',
'Database Wait Time Ratio') AND
INTSIZE_CSEC =
(select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC);

prompt ------------------------------------------------------------------------------------------------------
Performance of the Database
prompt ------------------------------------------------------------------------------------------------------




select CASE METRIC_NAME
WHEN 'SQL Service Response Time' then 'SQL Service Response Time (secs)'
WHEN 'Response Time Per Txn' then 'Response Time Per Txn (secs)'
ELSE METRIC_NAME
END METRIC_NAME,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((MINVAL / 100),2)
WHEN 'Response Time Per Txn' then ROUND((MINVAL / 100),2)
ELSE MINVAL
END MININUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((MAXVAL / 100),2)
WHEN 'Response Time Per Txn' then ROUND((MAXVAL / 100),2)
ELSE MAXVAL
END MAXIMUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((AVERAGE / 100),2)
WHEN 'Response Time Per Txn' then ROUND((AVERAGE / 100),2)
ELSE AVERAGE
END AVERAGE
from SYS.V_$SYSMETRIC_SUMMARY
where METRIC_NAME in ('CPU Usage Per Sec',
'CPU Usage Per Txn',
'Database CPU Time Ratio',
'Database Wait Time Ratio',
'Executions Per Sec',
'Executions Per Txn',
'Response Time Per Txn',
'SQL Service Response Time',
'User Transaction Per Sec')
ORDER BY 1;

prompt -

prompt ------------------------------------------------------------------------------------------------------
Performance of SQL Queries
prompt ------------------------------------------------------------------------------------------------------

select case db_stat_name
when 'parse time elapsed' then
'soft parse time'
else db_stat_name
end db_stat_name,
case db_stat_name
when 'sql execute elapsed time' then
time_secs - plsql_time
when 'parse time elapsed' then
time_secs - hard_parse_time
else time_secs
end time_secs,
case db_stat_name
when 'sql execute elapsed time' then
round(100 * (time_secs - plsql_time) / db_time,2)
when 'parse time elapsed' then
round(100 * (time_secs - hard_parse_time) / db_time,2)
else round(100 * time_secs / db_time,2)
end pct_time
from
(select stat_name db_stat_name,
round((value / 1000000),3) time_secs
from sys.v_$sys_time_model
where stat_name not in('DB time','background elapsed time',
'background cpu time','DB CPU')),
(select round((value / 1000000),3) db_time
from sys.v_$sys_time_model
where stat_name = 'DB time'),
(select round((value / 1000000),3) plsql_time
from sys.v_$sys_time_model
where stat_name = 'PL/SQL execution elapsed time'),
(select round((value / 1000000),3) hard_parse_time
from sys.v_$sys_time_model
where stat_name = 'hard parse elapsed time')
order by 2 desc;

prompt -



prompt ******************************************************************************************************
prompt ******************************************************************************************************
EXIT;



%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
How do I find the overall database size?
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v$controlfile) d;

------------------------------ EXTRA DBSIZE SCRIPTS -----------------------------

The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles:

select sum(bytes)/1024/1024 "Meg" from dba_data_files;

To get the size of all TEMP files:

select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;

To get the size of the on-line redo-logs:
-------------------------------------------

select sum(bytes)/1024/1024 "Meg" from sys.v_$log;

Putting it all together into a single query:(without controlfile )
------------------------------------------------
select (a.data_size+b.temp_size+c.redo_size)/1024/1024 "total_size(IN MB)"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c;



select (a.data_size+b.temp_size+c.redo_size)/1024/1024/1024 "total_size(IN GB)"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c;



ref: http://www.orafaq.com/wiki/Oracle_database_FAQ



%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

TOTAL DATA FILE SIZE -

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
clear breaks
clear computes
clear columns
set pagesize 50
set linesize 120
set heading on
column tablespace_name heading 'Tablespace' justify left format a20 truncated
column tbsize heading 'Size|(Mb) ' justify left format 9,999,999.99
column tbused heading 'Used|(Mb) ' justify right format 9,999,999.99
column tbfree heading 'Free|(Mb) ' justify right format 9,999,999.99
column tbusedpct heading 'Used % ' justify left format a8
column tbfreepct heading 'Free % ' justify left format a8
break on report
compute sum label 'Totals:' of tbsize tbused tbfree on report
select t.tablespace_name, round(a.bytes,2) tbsize,
nvl(round(c.bytes,2),'0') tbfree,
nvl(round(b.bytes,2),'0') tbused,
to_char(round(100 * (nvl(b.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbusedpct,
to_char(round(100 * (nvl(c.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbfreepct
from dba_tablespaces t,
(select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_data_files
group by tablespace_name
union
select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_temp_files
group by tablespace_name ) a,
(select e.tablespace_name, round(sum(e.bytes)/1024/1024,2) bytes
from dba_segments e
group by e.tablespace_name
union
select tablespace_name, sum(max_size) bytes
from v$sort_segment
group by tablespace_name) b,
(select f.tablespace_name, round(sum(f.bytes)/1024/1024,2) bytes
from dba_free_space f
group by f.tablespace_name
union
select tmp.tablespace_name, (sum(bytes/1024/1024) - sum(max_size)) bytes
from dba_temp_files tmp, v$sort_segment sort
where tmp.tablespace_name = sort.tablespace_name
group by tmp.tablespace_name) c
where
t.tablespace_name = a.tablespace_name (+)
and t.tablespace_name = b.tablespace_name (+)
and t.tablespace_name = c.tablespace_name (+)
order by t.tablespace_name ;


%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Restore point creation

create table gua_test( no varchar2(5));

insert into gua_test values (1);
insert into gua_test values (2);
insert into gua_test values (3);
insert into gua_test values (4);
insert into gua_test values (5);

Commit;

select count(*) from gua_test;


sysdba
----------

shu immediate

startup mount

create restore point five_rows guarantee flashback database;


odb user
---------

insert into gua_test values (6);
insert into gua_test values (7);
insert into gua_test values (8);
insert into gua_test values (9);
insert into gua_test values (10);

commit;

select count(*) from gua_test;


sysdba
----------

shu immediate

startup mount

flashback database to restore point five_rows;

alter database open resetlogs;


odb user
---------

select count(*) from gua_test;

result - 5 rows

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

odb user
---------

insert into gua_test values (6);
insert into gua_test values (7);
insert into gua_test values (8);
insert into gua_test values (9);
insert into gua_test values (10);

commit;

select count(*) from gua_test;

15

sysdba
----------

create restore point ten_rows guarantee flashback database;



odb user
---------

insert into gua_test values (11);
insert into gua_test values (12);
insert into gua_test values (13);
insert into gua_test values (14);
insert into gua_test values (15);

commit;

select count(*) from gua_test;

15

sysdba
----------

shu immediate

startup mount

flashback database to restore point ten_rows;

alter database open resetlogs;

odb user
---------
select count(*) from gua_test;

result - 10

All ORACLE Commands

Creating Control file

sql>shu immediate

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "BANK" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\REDO01.LOG' SIZE 10M,
GROUP 2 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\REDO02.LOG' SIZE 10M,
GROUP 3 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\REDO03.LOG' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\SYSTEM01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\UNDOTBS01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\SYSAUX01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\USERS01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\EXAMPLE01.DBF'
CHARACTER SET WE8MSWIN1252
;

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

creating redolog file;

at open stage


ALTER DATABASE DB_name ADD LOGFILE GROUP 4
('$ORACLE_HOME/ORADATA/u01/log3a.rdo',
'$ORACLE_HOME/ORADATA/u02/log3b.rdo')
SIZE 1M;


ADDING LOG FILE TO GROUP:

ALTER DATABASE db_name ADD LOGFILE MEMBER
'$HOME/ORADATA/u04/log1c.rdo' TO GROUP 1,
'$HOME/ORADATA/u04/log2c.rdo' TO GROUP 2,
'$HOME/ORADATA/u04/log3c.rdo' TO GROUP 3;


Dropping redolog file:


ALTER DATABASE DB_name DROP LOGFILE GROUP 4;


THEN DELETE THE PHYSICAL FILES MANUALLY



clearing online redolog files;

ALTER DATABASE DB_name CLEAR LOGFILE
'$HOME/ORADATA/u01/log2a.rdo';

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


Creating Tablespace:

CREATE TABLESPACE tbs01
DATAFILE '/u01/oradata/userdata01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M;

CREATE TABLESPACE data02
DATAFILE ‘/u01/oradata/data02.dbf’ SIZE 5M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;


CREATE TABLESPACE userdata
DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;


altering Tablespace:


ALTER DATABASE Db_name DATAFILE
'/u01/oradata/userdata02.dbf' SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;


Adding Data Files to a Tablespace

ALTER TABLESPACE app_data
ADD DATAFILE '/u01/oradata/userdata03.dbf'
SIZE 200M;


Resize of Data Files:

ALTER DATABASE db_name
DATAFILE '/u03/oradata/userdata02.dbf'
RESIZE 200M;


1.........Moving (or) Renaming Data Files without shutdown;

1. Take the tablespace offline.
2. Use an operating system command to move or copy the files.
3. Execute the ALTER TABLESPACE RENAME DATAFILE command.
4. Bring the tablespace online.
5. Use an operating system command to delete the file if necessary.

ALTER TABLESPACE userdata
RENAME
DATAFILE '/u01/oradata/userdata01.dbf'
TO '/u01/oradata/userdata01.dbf';



2..........Moving (or) Renaming Data Files with shutdown;

1. Shut down the database.
2. Use an operating system command to move the files.
3. Mount the database.
4. Execute the ALTER DATABASE RENAME FILE command.
5. Open the database.

ALTER DATABASE db_name RENAME
FILE '/u01/oradata/system01.dbf'
TO '/u03/oradata/system01.dbf';




creating undo tablespace:
---------------------------

CREATE UNDO TABLESPACE undo1
DATAFILE '/u01/oradata/undo101.dbf' SIZE 40M;


ALTER TABLESPACE undotbs
ADD DATAFILE 'undotbs2.dbf' SIZE 30M
AUTOEXTEND ON;


switching undo tablespace;

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;

dropping undo tablespace;

DROP TABLESPACE UNDOTBS2;

To drop an active UNDO tablespace:
– Switch to a new UNDO tablespace
– Drop the tablespace after all current transactions
are complete

Undo Data Statistics
--------------------

SQL> SELECT end_time,begin_time,undoblks FROM v$undostat;


Obtaining Undo Segment Information
-----------------------------------

V$ROLLSTAT and V$ROLLNAME Join the V$ROLLSTAT and V$ROLLNAME views to obtain the statistics of the undo segments currently used by the instance.

SQL> SELECT n.name, s.extents, s.rssize,s.hwmsize,
s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;


V$TRANSACTION and V$SESSION
------------------------------

To check the use of a undo segment by currently active transactions, join the
V$TRANSACTION and V$SESSION views:

SQL> SELECT s.username, t.xidusn, t.ubafil,
t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;




Creating Temporary Tablespace:
-------------------------------

CREATE TEMPORARY TABLESPACE temp01
TEMPFILE 'C:\oracle\product\10.1.0\oradata\bank\temp0101.dbf' size 100M AUTOEXTEND ON NEXT 10M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;


ALTER TABLESPACE temp add
TEMPFILE 'C:\oracle\product\10.1.0\oradata\bank\temp01.dbf' size 100M AUTOEXTEND ON NEXT 10M MAXSIZE unlimited;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

ALTER TABLESPACE tablespace_name READ [ONLY | WRITE]



Dropping Tablespace:

DROP TABLESPACE userdata INCLUDING CONTENTS AND DATAFILES;




Tables
--------------

ALTER TABLE hr.employees
ALLOCATE EXTENT(SIZE 500K
DATAFILE ‘/DISK3/DATA01.DBF’);

ALTER TABLE hr.employees
MOVE TABLESPACE data1;


ALTER TABLE hr.employees
DROP COLUMN comments
CASCADE CONSTRAINTS CHECKPOINT 1000;

Mark a column as unused
------------------------

ALTER TABLE hr.employees
SET UNUSED COLUMN comments CASCADE CONSTRAINTS;

Drop unused columns
-------------------

ALTER TABLE hr.employees
DROP UNUSED COLUMNS CHECKPOINT 1000;

Continue to drop column operation
----------------------------------

ALTER TABLE hr.employees
DROP COLUMNS CONTINUE CHECKPOINT 1000;




Creating a Profile:
--------------

Resource Limit
------------------
CREATE PROFILE developer_prof LIMIT
SESSIONS_PER_USER 2
CPU_PER_SESSION 10000
IDLE_TIME 60
CONNECT_TIME 480;




Creating a New User:
-----------------------

CREATE USER aaron
IDENTIFIED BY soccer
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 15m ON users
PASSWORD EXPIRE;


Granting Object Privileges
----------------------------
GRANT EXECUTE ON dbms_output TO jeff;
GRANT UPDATE ON emi.customers TO jeff WITH GRANT OPTION;


Revoking Object Privileges
----------------------------
REVOKE SELECT ON emi.orders FROM jeff;



Auditing
----------

• Statement auditing

AUDIT TABLE;

• Privilege auditing

AUDIT create any trigger;

• Schema object auditing

AUDIT SELECT ON emi.orders;

disable:
--------

Add no infront of audit.

Ex: NOAUDIT TABLE;





Creating Roles
---------------

CREATE ROLE oe_clerk;
CREATE ROLE hr_clerk IDENTIFIED BY bonus;
CREATE ROLE hr_manager IDENTIFIED EXTERNALLY;


Modifying Roles
----------------

ALTER ROLE oe_clerk IDENTIFIED BY order;
ALTER ROLE hr_clerk IDENTIFIED EXTERNALLY;
ALTER ROLE hr_manager NOT IDENTIFIED;


Assigning Roles
------------------

GRANT oe_clerk TO scott;
GRANT hr_clerk TO hr_manager;
GRANT hr_manager TO scott WITH ADMIN OPTION;

Creating controlfile

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "BANK" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\REDO01.LOG' SIZE 10M,
GROUP 2 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\REDO02.LOG' SIZE 10M,
GROUP 3 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\REDO03.LOG' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\SYSTEM01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\UNDOTBS01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\SYSAUX01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\USERS01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\BANK\EXAMPLE01.DBF'
CHARACTER SET WE8MSWIN1252
;

Standby Rebuild until SCN

Implementation Steps

Cancel Recovery on Standby
SQL> alter database recover managed standby database cancel;

Trying to recover on Standby
SQL> recover standby database;
ORA-00279: change 4146871739 generated at 12/31/2008 11:39:03 needed for thread 1
ORA-00289: suggestion : Z:\ORACLE\ORADATA\SATI\ARCHIVE\1_205_674755717.ARC
ORA-00280: change 4146871739 for thread 1 is in sequence #205

Check current_scn on Standby
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
4146871738

Create an Incremental Backup from this SCN on the Primary DB
Z:\backup> rman target sys@PROD_PRIMARY
RMAN> backup incremental from scn 4146871738 database tag 'FORSTANDBY' FORMAT 'Z:\BACKUP\FOR_STANDBY_%U';


Backup the Controlfile for ‘Standby’ on the Primary DB
RMAN> backup current controlfile for standby format 'Z:\BACKUP\FORSTDBYCTRL.bkp';

Transfer the Incremental Backup Sets from Primary DB to the Standby Server (Location = C:\Temp\Incr_Backup)
cd Z:\BACKUP
copy FOR_STANDBY_*.* \\STANDBY\C:\TEMP\INCR_BACKUP
copy FORSTDBYCTRL.bkp \\STANDBY\C:\TEMP\INCR_BACKUP

Restore controlfile on the Standby
RMAN> RESTORE STANDBY CONTROLFILE FROM 'C:\TEMP\INCR_BACKUP\FORSTDBYCTRL.BKP';

Catalog the Incremental Backups on the Standby Server
Note that for the catalog command to succeed you will need to move the backups to be within the Flash Recovery Area.
RMAN> catalog start with 'C:\FRA\SATISTD\BACKUPSET';

Recover the Database and Cleanup Redologs on the Standby Server
RMAN> recover database noredo;


SQL> alter database flashback off;
SQL> alter database flashback on;
SQL> alter database recover managed standby database disconnect from session;

If more archived logs were created on the primary since the finish of the SCN based incremental backup then you can copy them over and recover the standby database using the command : “recover standby database;”

Renaming the database Manually

take a controlfile backup:
----------------------------

SQL>alter database backup controlfile to trace;


modify the initfile:
----------------------

copy the following code and save as inittest.ora in 'E:\oracle_home\database\inittest.ora'


*.background_dump_dest='E:\oracle\product\10.1.0\admin\test\bdump'
*.compatible='10.1.0.2.0'
*.control_files='E:\oracle\product\10.1.0\oradata\test\control01.ctl','E:\oracle\product\10.1.0\oradata\test\control02.ctl','E:\oracle\product\10.1.0\oradata\test\control03.ctl'
*.core_dump_dest='E:\oracle\product\10.1.0\admin\test\cdump'
*.db_block_size=8192
*.db_cache_size=33554432
*.db_domain=''
*.db_file_multiblock_read_count=8
*.db_name='test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.java_pool_size=50331648
*.job_queue_processes=10
*.large_pool_size=8388608
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=83886080
*.sort_area_size=65536
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='E:\oracle\product\10.1.0\admin\test\udump'
*.log_archive_format='arch_%r_%t_%s.arc'
*.log_archive_dest='E:\oracle\product\10.1.0\db_2\database\archive'



Creating a controlfile
-----------------------

copy the following code and save as ctrl.sql in "c:\ctrl.sql"

CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\REDO01.LOG' SIZE 10M,
GROUP 2 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\REDO02.LOG' SIZE 10M,
GROUP 3 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\REDO03.LOG' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSTEM01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\UNDOTBS01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSAUX01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\USERS01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\EXAMPLE01.DBF'
CHARACTER SET WE8MSWIN1252
;


Stutdown the database:
----------------------

SQL> shu immediate;



Stop the Oracle services for old instance in services.msc
----------------------------------------------------------


run --> services.msc

Stop all the services related to old oracle instance through the following command

run --> cmd --> sc delete service_name


rename the folders for
------------------------

1.oradata
2.bdump


create a password file:
------------------------

c:\orapwd file='E:\oracle_home\database\pwdtest.ora' password=test entries=30


start a service for new database
--------------------------------


run --> cmd

c:\set ORACLE_SID=test

c:\oradim -new -sid test -startmode auto -maxusers 100 -pfile E:\oracle\product\10.1.0\db_2\database\inittest.ora

c:\sqlplus

username: sys as sysdba
password: test

connected to idle instance

SQL> startup nomount pfile='E:\oracle_home\database\inittest.ora';

SQL> create spfile from pfile;

SQL> @c:\ctrl.sql

SQL>alter database open resetlogs;

check the database name:
--------------------------

SQL> select instance_name ,status from v$instance;


set the globalname to instance;
-------------------------------

SQL> alter database rename global_name to test;

Cloning-precaution-Script

set head off
set lines 200
set pages 9999
col owner for a20
col db_link for a30
col username for a15
col host for a40
col created for a12

spool db_details.lst

select * from global_name;
select * from dba_db_links;
select name from v$controlfile;
select member from v$logfile;
select file_name from dba_data_files;
select file_name from dba_temp_files;

spool off

spool create_db_links.lst

select 'create DATABASE LINK '||owner#||'.'||NAME|| ' connect to '|| userid || ' identified by '|| password || ' using '||''''|| host ||''''||'; ' FROM sys.link$ order by owner#;

select username,user_id from dba_users where user_id in (select distinct owner# from link$);

spool off

spool alter_user.lst

select ' alter user '||username||' identified by values ' || chr(39)||password||chr(39) || ';' from dba_users;

spool off


Set verify off
Set space 0
set feedback off;
set echo off;
set pages 1000;
set lines 150;

spool create_synonym.sql

Select 'CREATE SYNONYM '||owner||'.'||synonym_name||' FOR '||table_owner||'.'||table_name||'@'||db_link||';' from dba_synonyms where db_link is not null and table_owner is not null;

Select 'CREATE SYNONYM '||owner||'.'||synonym_name||' FOR '||table_name||'@'||db_link||';' from dba_synonyms where db_link is not null and table_owner is null;

spool off;

Spool profile.sql

select ' alter user '||username||' profile '||PROFILE||';' from dba_users;

spool off

Changing dbname Through NID

How to change DBNAME?


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

STEP 1 SQL> select dbid, name from v$database; (Note Down Output)

STEP 2 SQL> shutdown imemdiate;
SQL> startup mount;

STEP 3 SQL> host nid target=sys/Passworda DBNAME=INDIAN (New DB Name) setname=Y

[oracle@testmachine] cp initolddbname.ora initnewdbname.ora

[oracle@testmachine] vi initnewdbname.ora (ex:indian=newdbname)

change the following line


*.db_name=INDIAN

:wq!

STEP 4 SQL> !export ORACLE_SID=INDIAN
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
SQL> create spfile from pfile
SQL> show parameter db_name
SQL> shutdown immediate;

[oracle@testmachine] cd $ORACLE_HOME/dbs
[oracle@testmachine] orapwd file=pwINDIAN.ora password=sys entries=150


SQL> startup mount;
SQL> alter database open resetlogs;
SQL> select name,dbid from v$database;

NAME DBID
--------- ----------
INDIAN 1191660936



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

for more details:

http://indiandba.blogspot.com/2009/03/how-to-change-dbid-and-dbname.html

EXPORT - IMPORT

EXP/IMP IN ORACLE 10G
=====================


Ref: http://www.oracleracexpert.com/2009/08/oracle-data-pump-exportimport.html
http://husnusensoy.wordpress.com/2008/07/12/migrating-data-using-transportable-tablespacetts/

first create a directory and grant access to it


SQL>create directory as '/ora10gdata/export'
SQL>grant read,write on directory to eg scott or hr or system



schema(user) lever
-----------------
expdp scott/tiger123 directory=johnson dumpfile=johnsonexp.dmp logfile=expjohnson.log schemas=scott
impdp scott/tiger123 directory=johnson dumpfile=johnsonexp.dmp logfile=impjohnson.log

table level
----------
expdp scott/tiger123 directory=johnson dumpfile=tableexp.dmp logfile=exptable.log tables=EMP,DEPT
impdp scott/tiger123 directory=johnson dumpfile=tableexp.dmp tables=EMP,DEPT logfile=imptable.log

full database
-------------
expdp system/sys directory=johnson dumpfile=nagiosdb.dmp logfile=expnagios.log full=y

impdp system/sys directory=johnson dumpfile=nagiosdb.dmp logfile=impnagios.log full=y

TABLESPACE LEVEL
----------------
expdp test_user/test123 tablespaces=test_user_tbs directory= export _dir dumpfile=expdp_tbs.dmp logfile=expdp_tbs.log



TRANSPORTABLE TABLESPACES:
----------------------------

SQL> CREATE TABLESPACE dumil LOGGING
DATAFILE 'C:\oracle\product\10.1.0\oradata\test\dumil.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

SQL> begin
sys.dbms_tts.transport_set_check('dumil', TRUE);
end;
/


SQL> ALTER TABLESPACE dumil READ ONLY;


C:\expdp system/test directory=expbkp TRANSPORT_TABLESPACES=dumil dumpfile=dumil_migration.dmp logfile=dumil_miglog.log

Note: In 9i [ exp system/test tablespaces=dumil transport_tablespace=y file=dumil_migration.dmp log=dumil_miglog.log ]



create directory impbkp as '/ora10gdata/impbkp'

grant read,write on directory impbkp to system;

impdp system/sys directory=impbkp transport_datafiles='/ora10gdata/gsvdb/gsvdb/DUMIL.DBF' dumpfile=dumil_migration.dmp logfile=impdplog.log

LVM Problems

lvcreate -L 10G -n lv1 vg1
lvcreate -L 10G -n lv2 vg1
lvcreate -L 10G -n lv3 vg1



mkfs -t ext3 /dev/vg1/lv1
mkfs -t ext3 /dev/vg1/lv2
mkfs -t ext3 /dev/vg1/lv3



umount /dev/vg1/lv1 /zzz
umount /dev/vg1/lv2 /zzz
umount /dev/vg1/lv3 /zzz

-------------------------------------
LVM issues
-------------------------------------

fuser -muv /media/cdrecorder

USER PID ACCESS COMMAND
/media/cdrecorder/ fd2 3069 ..c.. nautilus

aha!!!

kill -9 3069

but this just spawns another nautilus that still uses the file.
As you can see there is a different PID.

fuser -muv /media/cdrecorder/

USER PID ACCESS COMMAND
/media/cdrecorder/ fd2 27246 ..c.. nautilus

Eject does not work since it cannot unmount
I am using RH Enterprise 4

Disk Partition LVM (RHEL4)

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

DISK PARTITIONING

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

df -h

For Groups:
------------

vgdisplay (dispalay the current volume group details)
vgs (direct size,free)


For individual volumes:
----------------------

lvdisplay (separate view ,not group)
lvs (direct size,free)


physical volume:
---------------

pvdisplay (physical volume)
pvs

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


cat /etc/mtab (mount point details)
cat /etc/fstab (filesystem details)


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

extending a logical Volume
--------------------------

lvextend -L +15G path

note:
-----

+15G --> it will add 15 gb in a existing volume.
15g it will resize to 15 gb
path --> took from /etc/fstab

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

resizing a Logical volume
-------------------------

first umount the volume

cat /etc/fstab

umount /ora10gdata

verify with "df -h" command

lventend -L +2G /dev/volgroup/logicalvolume01

Note :
-------

volgroup --> took from /etc/fstab


check through lvs command

e2fsck -f /dev/mapper/volgroup-localvolume01

resize2fs /dev/mapper/volgroup-localvolume01

note:
----
volgroup-localvolume01 --> took from /etc/matb


mount /ora10gdata


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


renaming the mount point
------------------------

take a backup of mtab & fstab

EX:-
----

umount the volume "umount /ora10gdata"

create a new directory "mkdir /newfolder01"

mount "mount /newfolder01"

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

renaming the Volume Group
--------------------------

umount the volume directory

deactivate the volume group

vgchange -a n volgroupname

Note:-
-------

n --> deactivating

y --> activating


vgrename volgroupoldname newname

vgchange -a y newgroupname

mount the volume

Note:-
--------
vgscan for finding volumes

then change /etc/fstab also

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

renaming the logical volume
---------------------------

umount /disk03

lvrename volgroupname oldlogicalvolumename newname

vi /etc/fstab --> change there also

mount /disk03

lvs --> check it


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

creating a logical volume
--------------------------

lvcreate -L 10G -n logicalvolumename volumegroupname

check through lvs (or) lvdisplay command

mkfs -t ext3 /dev/volumegroupname/logicalvolumename

mkdir disk03

mount /dev/loggroup/logvolume /disk03


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


reducing the logical volume
----------------------------

umount /disk03

e2fsck -f /dev/mapper/volgroup-localvolume01

resize2fs /dev/volumegroupname/logvolume 5G

lvreduce /dev/volgroup/logvolume -L 5G

mount /disk03

Note:-
------

5G --> reduced to this (5G) size

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

Tested --> Newly installed system

-------------------------------------------------------------------------------------------
2.5. Removing Physical Volumes

# pvremove /dev/ram15


3.1. Creating Volume Groups

# vgcreate vg1 /dev/sdd1 /dev/sde1


3.2. Adding Physical Volumes to a Volume Group

# vgextend vg1 /dev/sdf1


3.5. Removing Physical Volumes from a Volume Group

# vgreduce my_volume_group /dev/hda1


3.8. Removing Volume Groups

# vgremove officevg

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

3.9. Splitting a Volume Group

# vgsplit bigvg smallvg /dev/ram15
Volume group "smallvg" successfully split from "bigvg"


3.10. Combining Volume Groups

vgmerge -v databases my_vg


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

3.12. Renaming a Volume Group

1.umount the volumes
2.Dismount the volgroup by following command

vgchange -a n mygroup01

3.change the name

vgrename /dev/vg02 /dev/my_volume_group (or)
vgrename vg02 my_volume_group

4.activate the volume

vgchange -a y mynewgroup01

5.change in /etc/fstab

6.mount the disks individualy



4.6. Removing Logical Volumes

[root@tng3-1 lvm]# lvremove /dev/testvg/testlv
Do you really want to remove active logical volume "testlv"? [y/n]: y
Logical volume "testlv" successfully removed


#--------------------------------------------------------------------------------------------------------------------------


-------------------------
New installed system:
------------------------


Creating physical volume first
---------------------------------


[root@delhidata ~]# lvmdiskscan (check the highest GB freespace)
/dev/ramdisk [ 16.00 MB]
/dev/hda [ 74.51 GB]
/dev/dm-0 [ 10.00 GB]
/dev/ram [ 16.00 MB]
/dev/hda1 [ 58.01 GB] LVM physical volume
/dev/dm-1 [ 10.00 GB]
/dev/ram2 [ 16.00 MB]
/dev/hda2 [ 15.00 GB]
/dev/ram3 [ 16.00 MB]
/dev/hda3 [ 1.00 GB]
/dev/ram4 [ 16.00 MB]
/dev/ram5 [ 16.00 MB]
/dev/hda5 [ 502.00 MB]
/dev/ram6 [ 16.00 MB]
/dev/ram7 [ 16.00 MB]
/dev/ram8 [ 16.00 MB]
/dev/ram9 [ 16.00 MB]
/dev/ram10 [ 16.00 MB]
/dev/ram11 [ 16.00 MB]
/dev/ram12 [ 16.00 MB]
/dev/ram13 [ 16.00 MB]
/dev/ram14 [ 16.00 MB]
/dev/ram15 [ 16.00 MB]
3 disks
19 partitions
0 LVM physical volume whole disks
1 LVM physical volume


[root@tng3-1 ]#pvcreate /dev/hda1

dd if=/dev/zero of=/dev/sdi bs=512 count=1


Creating new Volume Group :
-----------------------------

[root@tng3-1 ]#vgcreate vg1(any name) /dev/hda

Upgradation from 10.1 to 10.2


UPGRADING 10.1 to 10.2 (same procedure for upgrading from all version to higher)
-----------------------------------------------------------------------------------------

Ex:

old ORACLE HOME = 'c:\oracle\product\10.1.0\db_1
old db Name =test

Procedure:

1. run a preupgrade check through following command

@?\rdbms\admin\utlu102i.sql

2. create a pfile from spfile

3. alter database backup controlfile to trace .

4. take a backup of last generated trace file which contains information
about controlfiles located under udump

3. take a cold backup including password file,pfile,udump trace file.



INSTALLING 10.2 Software
--------------------------

1. install the oracle 10.2 software in a diffent oracle home location

2. create the same folder stuctures as per init parameters & udump trace files

3. copy the init file,controlfiles,datafiles,password files to a proper
locations under 10.2 home installed.

4. change the location of controlfile,version & dump location in initsid.ora

[note: uninstall the old software 10.1 & all services (If that is unix no need)]


Upgrading the databases
-------------------------

1. set ORACLE_SID=test

2.oradim -new -sid test

3. startup nomount pfile='E:\ORACLE\PRODUCT\10.2.0\db_1\dbs\inittest.ora';

4. create spfile from pfile;

5. alter database mount;

6. change the locations of all datafiles,redolog files under mount stage;(as mentioned below)


alter database rename file 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSTEM01.DBF' to 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF' ;
alter database rename file 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\UNDOTBS01.DBF' to 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF' ;
alter database rename file 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSAUX01.DBF' to 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF' ;
alter database rename file 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\USERS01.DBF' to 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF' ;
alter database rename file 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\RMAN01.DBF' to 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\RMAN01.DBF' ;
alter database rename file 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSAUX02.DBF' to 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX02.DBF' ;
alter database rename file 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SAKTHI01.DBF' to 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SAKTHI01.DBF' ;


9. shu immediate

10. startup upgrade

11. sql> @?\rdbms\admin\catupgrd.sql

12. sql> @?\rdbms\admin\utlrp.sql

























Friday, December 4, 2009

Creation of Database Manually

create database testdb

user sys identified by testdb

user system identified by testdb

maxinstances 1

maxloghistory 1

maxlogfiles 5

maxlogmembers 5

character set US7ASCII

national character set AL16UTF16

datafile 'E:\oracle\product\10.2.0\oradata\testdb\system01.dbf' size 500M

extent management LOCAL

SYSAUX datafile 'E:\oracle\product\10.2.0\oradata\testdb\sysaux01.dbf' size 500M

DEFAULT TEMPORARY tablespace temp01

tempfile 'E:\oracle\product\10.2.0\oradata\testdb\temp01_01.dbf' size 100M

UNDO tablespace undotbs1

datafile 'E:\oracle\product\10.2.0\oradata\testdb\undotbs01.dbf' size 200M

DEFAULT tablespace users

datafile 'E:\oracle\product\10.2.0\oradata\testdb\users01.dbf' size 100M

LOGFILE group 1

('E:\oracle\product\10.2.0\oradata\testdb\redo01.log') size 100M,

group 2

('E:\oracle\product\10.2.0\oradata\testdb\redo02.log') size 100M;