AUDIT SYS/SYSDBA/SYSOPER

The new parameter AUDIT_SYS_OPERATIONS allows the audit of all statements issued by SYS/SYSDBA/SYSOPER in an OS audit trail file. 

Startup database with AUDIT_SYS_OPERATIONS set to TRUE.

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /data/oracle/db/tech_st/11.2.0/rdbms/audit
audit_sys_operations                 boolean     TRUE


Now,do some activity as sysdba
SQL> conn sys/sys as sysdba
Connected.

SQL> create table abc (name varchar2(10));
Table created.

SQL> drop table abc;
Table dropped.

Go to /data/oracle/db/tech_st/11.2.0/rdbms/audit

cat ORCL_ora_17493_1.aud
Tue Apr 29 05:41:51 2014 -04:00
LENGTH : '190'
ACTION :[36] 'create table abc (name varchar2(10))'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[8] 'oraclone'
CLIENT TERMINAL:[5] 'pts/6'
STATUS:[1] '0'
DBID:[9] '761851345'

Tue Apr 29 05:42:00 2014 -04:00
LENGTH : '168'
ACTION :[14] 'drop table abc'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[8] 'oraclone'
CLIENT TERMINAL:[5] 'pts/6'
STATUS:[1] '0'
DBID:[9] '761851345'

Manage Redo Log Groups and Members

Below are the examples how to add redo log groups, how to add redo log members,how to drop redo log group,how to drop redo log member,how to clear redo log

Adding Online Redo Log Groups
ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/orcl/data/redo05a.log','/u01/orcl/data/redo05b.log') SIZE 500M;


Adding Online Redo Log Members
ALTER DATABASE ADD LOGFILE MEMBER
'/u01/orcl/data/redo05c.log' TO GROUP 1,
'/u01/orcl/data/redo05c.log' TO GROUP 2,
'/u01/orcl/data/redo05c.log' TO GROUP 3;


Dropping Online Redo Log Groups
Restrictions
• An instance requires at least two groups of online redo log files.
• An active or current group cannot be dropped.
• When an online redo log group is dropped, the operating system files are not deleted.

ALTER DATABASE DROP LOGFILE GROUP 5;


Dropping Online Redo Log Members
Restrictions
• If the member you want to drop is the last valid member of the group, you cannot drop
that member.
• If the group is current, you must force a log file switch before you can drop the
member.
• If the database is running in ARCHIVELOG mode and the log file group to which the
member belongs is not archived, then the member cannot be dropped.
• When an online redo log member is dropped, the operating system file is not deleted.

ALTER DATABASE DROP LOGFILE MEMBER '/u01/orcl/data/redo05c.log';



Clearing online redo log files:
Restrictions
You can clear an online redo log file whether it is archived or not. However, when it is not
archived, you must include the keyword UNARCHIVED. This makes backups unusable if the
online redo log file is needed for recovery.

ALTER DATABASE CLEAR LOGFILE '/u01/orcl/data/redo05c.log';


Information about group and members can be obtained by querying the data dictionary.
• V$LOG
• V$LOGFILE

SHUTDOWN Database Concepts

Shutdown Options:
a)Shutdown normal
b) Shutdown transactional
c) Shutdown immediate
d) Shutdown abort

Here are the difference between Shutdown Normal, transactional, immediate and abort:

Shutdown Normal
Normal is the default shutdown mode. Normal database shutdown proceeds with the following conditions:
• No new connections can be made.
• The Oracle server waits for all users to disconnect before completing the shutdown.
• Database and redo buffers are written to disk.
• Background processes are terminated, and the SGA is removed from memory.
• Oracle closes and dismounts the database before shutting down the instance.
• The next startup does not require an instance recovery.

Shutdown Transactional
A transactional shutdown prevents clients from losing work. A transactional database shutdown proceeds with the following conditions:
• No client can start a new transaction on this particular instance.
• A client is disconnected when the client ends the transaction that is in progress.
• When all transactions have finished, a shutdown immediately occurs.
• The next startup does not require an instance recovery.

Shutdown Immediate
Immediate database shutdown proceeds with the following conditions:
• Current SQL statements being processed by Oracle are not completed.
• The Oracle server does not wait for users currently connected to the database to disconnect.
• Oracle rolls back active transactions and disconnects all connected users.
• Oracle closes and dismounts the database before shutting down the instance.
• The next startup does not require an instance recovery.

Shutdown Abort
If the normal and immediate shutdown options do not work, you can abort the current
database instance. Aborting an instance proceeds with the following conditions:
• Current SQL statements being processed by the Oracle server are immediately terminated.
• Oracle does not wait for users currently connected to the database to disconnect.
• Database and redo buffers are not written to disk.
• Uncommitted transactions are not rolled back.
• The instance is terminated without closing the files.
• The database is not closed or dismounted.
• The next startup requires instance recovery, which occurs automatically.