Tuesday, May 6, 2008

STARTUP SEQUENCE In Oracle Application Server

1.Start the OracleAS Metadata Repository listener
cd /u02/oracle/OraHome_1/bin ./lsnrctl start

2.Start the OracleAS Metadata Repository database
sqlplus "/ as sysdba" startup

3.Use opmnctl to start the OracleAS Infrastructure instance
cd /u02/oracle/OraHome_1/opmn/bin/ ./opmnctl start iasconsole

4.Use emctl to start Application Server Control of the OracleAS Infrastructure instance.
cd /u02/oracle/OraHome_1/bin ./emctl start iasconsole

5.You can start the components from the Application Server Control Console
or use opmnctl from each OracleAS Middle Tier instance to start the processes.
cd /u02/oracle/OraHome_2/opmn/bin/ ./opmnctl start iasconsole

6.Use emctl from each OracleAS Middle Tier instance to start Application Server Control.
cd /u02/oracle/OraHome_2/bin ./emctl start iasconsole
7.
cd /u02/oracle/OraHome_1/bin/ ./emctl start dbconsole

/u02/oracle/OraHome_1/opmn/bin/ ./opmnctl status
/u02/oracle/OraHome_2/opmn/bin/ ./opmnctl status

/u02/oracle/OraHome_1/opmn/bin/ ./opmnctl startall
/u02/oracle/OraHome_2/opmn/bin/ ./opmnctl startall

RMAN backup

1:create user
SQL> create user rman identified by rman ;
SQL> alter user rman default tablespace tools temporary tablespace temp ;
SQL> alter user rman quota unlimited on tools ;
SQL> grant connect,resource,recovery_catalog_owner to rman ;
SQL> exit ;

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

2:login to rman and create cataloge schema
$ rman catalog rman/rman
RMAN> create catalog tablespace tools ;
RMAN> exit ;

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

3:put database in archive log

sqlplus login as sysdba

SQL>select log_mode from v$database ;
select name from v$database;
SQL> shutdown immediate ;
SQL> startup mount ;
SQL>alter database archivelog ;
SQL>alter database open ;
SQL>select log_mode from v$database ;

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

4:register the database in the catalog
$ rman catalog rman/rman target sys/sys
RMAN>register database;
============================================================================
5:create script to make backup
RMAN> create script daily_backup{
backup database plus archivelog;
delete noprompt obsolete;
}


============================================================================
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/backup/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/backup/RMAN_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 2 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\RMANBACKUP\SNCFSULAPP1.ORA';

RMAN>
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'f:\RmanBackup\backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 2 G;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'f:\RmanBackup\backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 2 G;
================================================================================
7: execute script to backup
RMAN> run {execute script daily_backup;}

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

Complete Restore / Recover Example

1/create folder in any directory for exampel d:\backupdatabase
change the configration of rman
rman catalog rman/rman target sys/sys
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'd:\backupdatabase\%F';
RMAN>CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'd:\backupdatabase\backup_db_%d_S_%s_P_%p_T_%t';
RMAN>CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'd:\backupdatabase\SNCFORCL.ORA';
exit;
2/connect to sql
sql> sys/sys as sysdba
shutdown immediate
startup nomount
exit;

3/connect to rman
rman target sys/sys
RMAN>set dbid=1134214906 {this is the id of your database}
RMAN>set controlfile autobackup format for device type disk to 'D:\backup\%F'
RMAN>restore controlfile from autobackup;
RMAN>mount database;
RMAN>restore database;
RMAN>recover database;
RMAN>alter database open resetlogs;
RMAN>exit;
rman catalog rman/rman target sys/sys
RMAN>reset database;
exit;

create batch file
rman catalog rman/rman target sys/sys @d:\cmdfile.txt
create text file in d:\ name cmdfile and write in it rman command

set controlfile autobackup format for device type disk to 'D:\backup\%F'


RMAN>Friday backup
run {
backup incremental level 0 database ;
sql 'alter system archive log current';
backup archivelog all delete input;
delete noprompt obsolete;
}
RMAN>Daily backup
run {
backup incremental level 1 database ;
sql 'alter system archive log current';
backup archivelog all delete input ;
delete noprompt obsolete;
}
RMAN>MONDAY
run {
backup incremental level 2 database ;
sql 'alter system archive log current';
backup archivelog all delete input;
delete noprompt obsolete;
}
RMAN>Wed backup
run {
backup incremental level 2cumulative database ;
sql 'alter system archive log current';
backup archivelog all delete input;
delete noprompt obsolete;
}

ppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp


Check whether backup pieces, proxy copies, or disk copies still exist.

CROSSCHECK BACKUP;
CROSSCHECK COPY;
CROSSCHECK backup of database;
CROSSCHECK backup of controlfile;
CROSSCHECK archivelog all;

uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu
List the backups and copies recorded in the repository.


LIST BACKUP;
RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST INCARNATION;
RMAN> LIST BACKUP BY FILE;
RMAN> LIST COPY OF DATABASE ARCHIVELOG ALL;
RMAN> LIST COPY OF DATAFILE 1, 2, 3;
RMAN> LIST BACKUP OF DATAFILE 11 SUMMARY;
RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;
RMAN> LIST CONTROLFILECOPY "/tmp/cntrlfile.copy";
RMAN> LIST BACKUPSET OF DATAFILE 1;


[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[

list incarnation of database;
reset database to incarnation Your_KEY;
reset database;


////////////////////////////////////////////////////////
ALTER SYSTEM ARCHIVE LOG START;


delete script

Monday, May 5, 2008

Audit In Oracle

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 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

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;

no rows selected


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.
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:
select audit_option,success,failure
from dba_stmt_audit_opts
union
select privilege,success,failure
from dba_priv_audit_opts
SQL> /

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
--
-- check_create_session.sql
--
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

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.

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')
from dba_audit_session
where returncode<>0
and not exists (select 'x'
from dba_users
where dba_users.username=dba_audit_session.username)


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,
terminal,
action_name,
returncode,
to_char(timestamp,'DD-MON-YYYY HH24:MI:SS'),
to_char(logoff_time,'DD-MON-YYYY HH24:MI:SS')
from dba_audit_session
where to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') <
to_date('08:00:00','HH24:MI:SS')
or to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') >
to_date('19:30:00','HH24:MI:SS')


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


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
from dba_audit_session
having count(distinct(terminal))>1
group by username


COUNT(DISTINCT(TERMINAL)) USERNAME
------------------------- ----------
4 EMIL
3 SYS
3 ZULIA

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
from dba_audit_session
having count(distinct(username))>1
group by terminal


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


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.


example:::::\

SQL> SHOW PARAMETER AUDIT
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SQL> SHUTDOWN immediate;
SQL> startup;




First we create a new user called AUDIT_TEST.

SQL> CREATE USER audit_test IDENTIFIED BY audit_test
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

GRANT connect TO audit_test;
GRANT create table, create procedure TO audit_test;


Next we audit all operations by the AUDIT_TEST user.

CONNECT sys/password AS SYSDBA

AUDIT ALL BY user_name BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY user_name BY ACCESS;
AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;

Next, we perform some operations that will be audited.

CONN audit_test/password

CREATE TABLE test_tab (id NUMBER);

INSERT INTO test_tab (id) VALUES (1);
UPDATE test_tab SET id = id;
SELECT * FROM test_tab;
DELETE FROM test_tab;

DROP TABLE test_tab;In the next section we will look at how we view the contents of the audit trail.


COLUMN username FORMAT A10
COLUMN owner FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35

SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = 'AUDIT_TEST'
ORDER BY timestamp;


AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;



select OS_USERNAME, USERNAME, USERHOST, TERMINAL, TIMESTAMP,
OWNER, OBJ_NAME, ACTION_NAME, NEW_OWNER, NEW_NAME,
SES_ACTIONS, COMMENT_TEXT, SESSIONID, ENTRYID, STATEMENTID,
RETURNCODE, PRIV_USED, CLIENT_ID, SESSION_CPU,
EXTENDED_TIMESTAMP, PROXY_SESSIONID, GLOBAL_UID, INSTANCE_NUMBER,
OS_PROCESS, TRANSACTIONID, SCN, SQL_BIND, SQL_TEXT
from dba_audit_trail
where ACTION_NAME like '%INSERT%'

linux command

Logging out
exit
logout
ctrl + d

$touch -- to create file
$cat --open this file
$echo “hakki al saidi” -- display the text
$echo “hakki al saidi”>newfile -- display the text and put it in text file
named newfile
$cat /etc/passwd > myfile -- display the contain of passwd file and put
it in text file named myfile
$ls --display the contain of current directory
$shutdown
$shutdown –r
$halt --for emergent case only
$init 6 --restart
$init 0 --shutdown
$poweroff

$mkdir --to make new directory
$pwd --to see the directory
$cp newfile1 ../newfile2 -- to copy file from the current location to root
$ mv newfile1 newfile2 --move newfile1 and rename it to newfile2
$ mv newfile1 ./newdir/ -- move newfile1 to directory newdir in same
name
$cat newfile1 -- to read file
$more newfile1
$rm newfile1 --to delete file
$rm –r newdir --to delete directory
$rm –fr newdir --to delete directory without prompt
$less filename
$cat file1 file2 file3 -- to open all these files in same time

Changing permission
$chmod
$chmod u-w bin -- u = user
$chmod g + rx bin

For example: chmod -R 775 /u01/app/oracle

$chmod u = rwx , g = r-x , o = -- u=user g=group o=other
$chmod 750 bin -- 7=111=rwx, 5=101=r-x, 0=000=---

Change owner
$chown

Change owner of diractory and all contian
$chown -R
$chown -R hakki ./newdir/


Change mode
$chmod
$chmod u=r-x , g=rwx , o=
u user
g group
o other
$chmod g+rw

$chmod 755
7 rwe
5 r-e
chmod -R 775 /u01/app/oracle

Change group
$chgrp

Change group and all contain
$chgrp -R

In which user you are
$whoami

Whose users are login
$who

The type of file
$file

If you want search on file you know it's name and you don’t know the diractory
$find ./-name
$find ./-name passwod

If you want to know the path of command
$which
$which ls

Variables
=
$var1="hakki"
$echo $var1 --the output hakki

To see the contain of path
$echo $PATH

To unset variable
$unset
$unset var1

To add another directory to path
$PATH=dir:$PATH
$PATH=/root/myls:$PATH

To know the current shell
$echo $shell

To see the available shells
$cat /etc/shells

To change to anther shell
only write the shell name
$bash
To exit from shell
exit

To sharing between shells use
$export
$export var1="hakki"

Links like shortcut in window
To see the information about inode
$ls -li

1-hard link
To make hard link to file
$ln file link
$ln new_file hard_link

2-soft link
$ln -s file link
$ln -s new_file soft_link


To display the contain of hard link
$cat link

To write text in file
$echo "this is my text">new_file

To add text in file
$echo "my name is hakki">>new_file
Output Redirection
$command1>new_file --to write the result in text file
$command1>>new_file --to append the result in text file

Input
$command1 input_new_file
$sort input_new_file
Display contain file sorted and save result in another file
$command1 input_new_file output_new_file
$sort input_new_file output_new_file

To pass the output of command to another command
$ls /etc more


Change prompt

We can use the variable PS1 to change prompt
$PS1=”my prompt”
Export PS1 -- to share this prompt between session
PS1=”[\u!@\h][\w]” -- u = the use , h = machine name ,w = full path

In IBM server

vi ~/.profile
--write in profile
export PS1="[`hostname`][`whoami`]\$PWD\> "
--execute it



Visual editor
1- the command mode
2- the insert mode

$vi –R filename -- to open file for read only
$view filename -- to open file for read only

To move


K
H
L
J


5L -- move corser five character
8J -- move the courser eight lines down
$ -- to go to end of line
0 -- to go to begin of current line
w next word
b previous word
ctrl + F foreword one page
ctrl + B Back one page
G last line
:q! exit
:1 first line
:9 ninth line
6G line number 6

:set nu to number the page
:set nonu to remove line number

Shift + G to go to the end of text file

Insert mod


-To go to insert mode
i it well type before cursor
a it well type after cursor
I it well type in the beginning of line
A it well type in the end of line





Search files

In command mode
/
n find next
N find previous
? text to be search



SAVE AND EXIT



:q exit
:w save in same name
:wq save and exit
:q! force exit
:zz save and exit
:w save as
:e! last version successful of save file [refresh]


DELETE CHARACTER
We must be in command mode

x delete single character under cursor
5x delete five char from cursor
X backslash
dw delete one word
D delete form cursor to end of line\
u undo
dd delete all line


CHANGE COMMAND


cc delete line and take you in insert mode
cw delete the word
r replace the character
R start over writing
s start over writing in insert mode
S delete currant line in insert mode

J join two line
yy copy line to buffer
p past
yw copy word
P past above line
o Create line below and take you in insert mode
O Create line above and take you in insert mode
Ctrl + G information about file
:! To execute command in text file without exit from it ;




FIND AND REPLACE TEXT

Change will be done above cursor
:s //
Change all file

:1,$s: //gc

:1,$s/FEB/MAR/g


1 first file
$ end file
c confirm ask




meta character

1. (*)
rm –fr *
delete all file in the directory
2. (.)
rm -fr c.t
delete all file start with c and end with t and one character between them
rm -fr c*t
delete all file start with c and end with t and any number of character between them
3. ([])

rm -fr c[ioa]t

delete all word start with c and end with t and there is one letter between them i or o or a
4.(^) not
rm –fr c[^aio]t

delete all files start with c and end with t and the letter between two bracket
not in them


4. (^)
rm –fr ^c.t
Delete all files begin with c follow by any character and third character is t

rm -$
delete all line


FTP File Transfer protocol

ftp
ftp
user name:
password:

FTP> LS list contain of directory




To start FTP
service vsftpd status
service vsftpd start
service vsftpd status


Grep Command

-To search any string inside file
grep string filename

-to display all file that not contain root
grep –v root /etc/*

Find command
-to search file and directory
find .-filename passwd –print


to run Xprocess

vi /etc/X11/gdm/gdm.comf
xdmcp=true


to change host name
vi /etc/hosts
add:
IP HOSTNAME

To change the password
passwd


to change the time and date

root>man date
root> [MMDDhhmm[[CC]YY][.ss]]
root>date 1101121807
date 11171543


root> while (true)
> do
> date
> sleep 2
> done








TO SET THE EDITOR IN IBM
set -o vi
%
% setenv EDITOR vi
%
% ksh
$
$
$
$ set -o vi


Esc + K


CREATE USER

/usr/sbin/useradd rozhan
passwd rozhan
chmod -R 775 /uid01/app/oracle/product/10.2.0/db_1/admin/itappsdb/bdump/alert_itappsdb.log




to extract file format cpio

cpio -idvm < as_linux_x86_portal_wireless_101202_disk1.cpio




to update the time

ntpdate -s -b -p 8 -u 192.168.163.1
to monitor the command executed in background
tail -f nohup.out



to start ftp


[root@cdrsub ~]# service vsftpd status
vsftpd is stopped
[root@cdrsub ~]# service vsftpd start




nohup.out

to run the script
nohup ./exp_2006_2005.sql &


to check the package install

rpm –q package name
exp:
rpm –q elfutils-libelf-devel-0.97

to install the package
rpm -Uvh package name
exp:
rpm -Uvh unixODBC-devel-2.2.11-1.RHEL4.1.i386.rpm


to uninstall package
rpm -e package name
exp:
rpm -e oracleasmlib-2.0.2-1




to search file
find / -name filename
exp:
find / -name ora_control1

Installation of Oracle 10g ASM FULL DOC on Linux RHEL – 4

cd /lib
rpm -Uvh oracleasm-support-2.0.3-1.i386.rpm \
> oracleasmlib-2.0.2-1.i386.rpm \ oracleasm-2.6.9-42.ELsmp-2.0.3-1.i6


usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/useradd -g oinstall -G dba oracle
passwd oracle
[root@cdr-master_test lib]# id oracle
uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba)


vi /etc/sysctl.conf

kernel.shmall = 2097152
kernel.shmmax =536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144






vi /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

vi /etc/profile

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi


mkdir -p /u01/oracle
chown -R oracle:oinstall /u01/oracle
chmod -R 775 /u01/oracle

mkdir -p /u02/oracle
chown -R oracle:oinstall /u02/oracle
chmod -R 775 /u02/oracle

# export DISPLAY=10.13.3.102:0.0

vi .bash_profile -----add
umask 022



parted /dev/cciss/c0d2 ----LINUX
print ---PARTED
mkpart primary ext3 1 10240 ----PARTED
cat /proc/partitions ---LINUX
/sbin/mkfs -t ext3 /dev/cciss/c0d2p1 ---LINUX



parted /dev/cciss/c0d2 ----LINUX
print ---PARTED
mkpart primary ext3 10241 20481 ---PARTED
cat /proc/partitions ---LINUX
/sbin/mkfs -t ext3 /dev/cciss/c0d2p2 ---LINUX



Step 1) Here we create 4 zero-filed files using the DD comand
As "root" run the following commands, this will create 4 files of 2Gb each.
cat /proc/partitions
mkdir /asmdisk
dd if=/dev/zero of=/asmdisk/disk1 bs=1024k count=2000
dd if=/dev/zero of=/asmdisk/disk2 bs=1024k count=2000
dd if=/dev/zero of=/asmdisk/disk3 bs=1024k count=2000
dd if=/dev/zero of=/asmdisk/disk4 bs=1024k count=2000
dd if=/dev/zero of=/asmdisk/disk5 bs=1024k count=500

Step 2) Use the loopback device to assign these disks.
/sbin/losetup /dev/loop1 /asmdisk/disk1
/sbin/losetup /dev/loop2 /asmdisk/disk2
/sbin/losetup /dev/loop3 /asmdisk/disk3
/sbin/losetup /dev/loop4 /asmdisk/disk4
/sbin/losetup /dev/loop5 /asmdisk/disk5

Now we need to configure entries to the file "/etc/rc.local" so that these divices are re-initialised on reboot also.
Add the following entries to the file "/etc/rc.local"
/etc/init.d/oracleasm createdisk ASM1 /dev/loop1/etc/init.d/oracleasm createdisk ASM2 /dev/loop2/etc/init.d/oracleasm createdisk ASM3 /dev/loop3/etc/init.d/oracleasm createdisk ASM4 /dev/loop4


###### To delete /sbin/losetup -d /DEV/loop1


Instaling ASM Lib
ASMLib is a support library for the Automatic Storage Management feature of Oracle Database 10g. ASMLib allows an Oracle Database using ASM more efficient and capable access to the disk groups it is using. ASMlib are provided by Oracle from below link : http://www.oracle.com/technology/tech/linux/asmlib/index.html
Please download the Lib files according to your OS & kernel version.
[root@csstage root]# uname -r
2.4.21-27.EL
It gives 3 rpms for download as per the version , pls install then using below command
[root@csstage asm]# rpm –ivh *.rpm
After this completes we go to next step of configuring the disks.
Configuring the ASM Lib & disks
Now we need to use the ASM Lib, configure it & configure the disks accordingly.
[root@csstage root]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver:
This will configure the on-boot properties of the Oracle ASM library driver. The following questions will determine whether the driver is loaded on boot and what permissions it will have. The current values will be shown in brackets ('[]'). Hitting without typing an answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: oracle
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: [ OK ]
Creating /dev/oracleasm mount point: [ OK ]
Loading module "oracleasm": [ OK ]
Mounting ASMlib driver filesystem: [ OK ]
Scanning system for ASM disks: [ OK ]
Now as the ASM Lib are configured, we will configure the disks:
[root@csstage root]# /etc/init.d/oracleasm createdisk ASM1 /dev/loop1
Marking disk "/dev/loop1" as an ASM disk: [ OK ]

[root@csstage root]# /etc/init.d/oracleasm createdisk ASM2 /dev/loop2
Marking disk "/dev/loop2" as an ASM disk: [ OK ]

[root@csstage root]# /etc/init.d/oracleasm createdisk ASM3 /dev/loop3
Marking disk "/dev/loop3" as an ASM disk: [ OK ]

[root@csstage root]# /etc/init.d/oracleasm createdisk ASM4 /dev/loop4
Marking disk "/dev/loop4" as an ASM disk: [ OK ]
So, now our hardware is all set to go & we need to install 10g database on the server using the above disks.
Install DB & ASM instance
Now we install the Database with ASM option
Create the ASM InstanceASM runs as a separate Oracle instance, which can be created and configured using OUI. Now that ASMLib is installed and the disks are marked for use, you can create an ASM instance. Log in as oracle and start runInstaller:
$ ./runInstaller
1. Select Installation Method- Select Advanced Installation- Click on Next

2. Specify Inventory Directory and Credentials - Inventory Directory: /u01/app/oracle/oraInventory- Operating System group name: oinstall- Click on Next


3. Select Installation Type- Select Enterprise Edition- Click on Next

4. Specify Home Details- Name: Ora10gAsm- Path: /u01/app/oracle/product/10.2.0/asm


Note:Oracle recommends using a different ORACLE_HOME for ASM than the ORACLE_HOME used for the database for ease of administration.
- Click on Next


5. Product-specific Prerequisite Checks - If you've been following the steps in this guide, all the checks should pass without difficulty. If one or more checks fail, correct the problem before proceeding. - Click on Next


6. Select Configuration Option- Select Configure Automatic Storage Management (ASM)- Enter the ASM SYS password and confirm- Click on Next


7. Configure Automatic Storage ManagementDisk Group Name: DATA- Redundancy-- High mirrors data twice.-- Normal mirrors data once. This is the default.-- External does not mirror data within ASM. This is typically used if an external RAID array is providing redundancy.- Add DisksThe disks you configured for use with ASMLib are listed as Candidate Disks. Select each disk you wish to include in the disk group.- Click on Next


8. Summary - A summary of the products being installed is presented.- Click on Install.


9. Execute Configuration Scripts- At the end of the installation, a pop up window will appear indicating scripts that need to be run as root. Login as root and run the indicated scripts.- Click on OK when finished.


10. Configuration Assistants- The Oracle Net, Oracle Database, and iSQL*Plus configuration assistants will run automatically


11. End of Installation - Make note of the URLs presented in the summary, and click on Exit when ready.


12. Congratulations! Your new Oracle ASM Instance is up and ready for use.



Create the Database:
Once the ASM instance has been created, create a database that uses ASM for storage:
Log in as oracle and start runInstaller: $ ./runInstaller
1. Select Installation Method- Select Advanced Installation- Click on Next
2. Select Installation Type- Select Enterprise Edition- Click on Next
3. Specify Home Details- Name: OraDb10g- Path: /u01/app/oracle/product/10.2.0/dbNote:Oracle recommends using a different ORACLE_HOME for the database than the ORACLE_HOME used for ASM.- Click on Next
4. Product-specific Prerequisite Checks - If you've been following the steps in this guide, all the checks should pass without difficulty. If one or more checks fail, correct the problem before proceeding. - Click on Next
5. Select Configuration Option- Select Create a Database- Click on Next
6. Select Database Configuration- Select General Purpose- Click on Next
7. Specify Database Configuration Options- Database Naming: Enter the Global Database Name and SID- Database Character Set: Accept the default- Database Examples: Select Create database with sample schemas- Click on Next
8. Select Database Management Option- Select Use Database Control for Database Management- Click on Next
9. Specify Database Storage Option- Select Automatic Storage Management (ASM)- Click on Next
10. Specify Backup and Recovery Options- Select Do not enable Automated backups- Click on Next
11. Select ASM Disk Group- Select the DATA disk group created in the previous section- Click on Next
12. Specify Database Schema Passwords- Select Use the same password for all the accounts- Enter the password and confirm- Click on Next
13. Summary - A summary of the products being installed is presented.- Click on Install.


14. Configuration Assistants- The Oracle Net, Oracle Database, and iSQL*Plus configuration assistants will run automatically
15. Execute Configuration Scripts- At the end of the installation, a pop up window will appear indicating scripts that need to be run as root. Login as root and run the indicated scripts.- Click on OK when finished.

16. End of Installation - Make note of the URLs presented in the summary, and click on Exit when ready.
The following J2EE Applications have been deployed and are accessible at the URLs listed below.
iSQL*Plus URL:
http://cdr-master_test:5562/isqlplus

iSQL*Plus DBA URL:
http://cdr-master_test:5562/isqlplus/dba

Enterprise Manager 10g Database Control URL:
http://cdr-master_test:1158/em

17. Congratulations! Your new Oracle Database is up and ready for use.






Note: In between the installation , You might be asked to start CSS Deamon , if you havent already done.To start the CSS daemon and configure the host to always start the daemon upon reboot, do the following:
1. Log in to the host as root.
2. Ensure that $ORACLE_HOME/bin is in your PATH environment variable.
3. Enter the following command: localconfig add


Start the ASM instance:
$ export ORACLE_SID=+ASM
$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 3 00:28:09 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1217836 bytes
Variable Size 57502420 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL> select group_number,disk_number,name,state,mode_status,mount_status,total_mb from v$asm_disk;

G# D# NAME STATE MODE_ST MOUNT_S TOTAL_MB
---- ---------- ----------
1 0 ASM1 NORMAL ONLINE CACHED 2000
1 1 ASM2 NORMAL ONLINE CACHED 2000
1 2 ASM3 NORMAL ONLINE CACHED 2000
1 3 ASM4 NORMAL ONLINE CACHED 2000
1 4 ASM5 NORMAL ONLINE CACHED 2000
2 0 ASM6 NORMAL ONLINE CACHED 2000
2 1 ASM7 NORMAL ONLINE CACHED 1000