Auditing SYS user in 10g

There are two parameters that one need to set to audit SYS users in 10g.

The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

The AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.

AUDIT_TRAIL: You can view audit information in TEXT format or in XML format depending on the parameter audit trail setting.

SQL> show parameter audit
NAME TYPE VALUE
----------------------- --------- -------------------------------------------

audit_file_dest string D:\ORACLE\PRODUCT\10.2.0\DB_1
\RDBMS\AUDIT
audit_sys_operations boolean FALSE
audit_trail string NONE

SQL>
we set audit_trail parameter to value "xml, extended"
http://www.oracle-base.com/articles/10g/Auditing_10gR2.php
SQL> alter system set audit_trail=xml,extended scope=spfile;
System altered.
SQL> show parameter audit

SQL> show parameter audit

NAME TYPE VALUE

----------------------- --------- -------------------------------------------
audit_file_dest string D:\ORACLE\PRODUCT\10.2.0\DB_1 \RDBMS\AUDIT audit_sys_operations boolean FALSE

audit_trail string NONE


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 79693180 bytes
Database Buffers 79691776 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.


SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string D:\ORACLE\PRODUCT\10.2.0\DB_1
\RDBMS\AUDIT
audit_sys_operations boolean FALSE
audit_trail string XML, EXTENDED

SQL> alter system set audit_file_dest='D:\oracle\product\10.2.0\admin\rock\audit' scope=spfile;
System altered.


SQL> alter system set audit_sys_operations=true scope=spfile;
System altered.
SQL>

shutdown & restart the database.

SQL> show parameter audit
NAME TYPE VALUE
------------------------ ------------ ----------- ------------------------------
audit_file_dest string D:\ORACLE\PRODUCT\10.2.0ADMIN
\ROCK\AUDIT
audit_sys_operations boolean TRUE
audit_trail string XML, EXTENDED

SQL> create user xyz222
2 identified by xyz222;
User created.
SQL>

NOw we will check whether these commands are audited or not, I will run below query.

set linesize 3333
column db_user format a10
column os_user format a24
column os_host format a20
column extended_timestamp format a20
column sql_text format a300


select db_user, os_user, os_host, TO_CHAR(EXTENDED_TIMESTAMP,'fmDd-MM-YYYY HH:MI:SS AM'), sql_text
from v$xml_audit_trail
where db_user in ('sys', 'SYS', '/')
order by 4
/


DB_USER OS_USER OS_HOST TO_CHAR(EXTENDED_TIMES SQL_TEXT
---------- ------------------------ -------------------- ----------------------
/ ABAMCO\rakesh.kumar ABAMCO\ABM-D0113 26-12-2008 3:47:23 PM select db_user, os_uSER, os_host, TO_CHAR(EXTENDED_TIMESTAMP,'fmDd-MM-YYYY HH:MI:SS AM'), sql_textfrom v$xml_audit_trailwhere db_user in ('sys', 'SYS', '/')order by 4

/ ABAMCO\rakesh.kumar ABAMCO\ABM-D0113 26-12-2008 3:48:38 PM create user xyz222identified by *

/ ABAMCO\rakesh.kumar ABAMCO\ABM-D0113 26-12-2008 3:48:56 PM select db_user, os_uSER, os_host, TO_CHAR(EXTENDED_TIMESTAMP,'fmDd-MM-YYYY HH:MI:SS AM'), sql_textfrom v$xml_audit_trailwhere db_user in ('sys', 'SYS', '/')order by 4

Auditing other users than sys.
http://www.oracle-base.com/articles/10g/Auditing_10gR2.php

Backup and Recovery - Loss of controlfile

Scenario - I have taken HOT backup, then created new tablespace testing1 (the backup controlf file doesn't have new tablespace information). I lost all controlfiles, Restoring controlfile from backup will get new tablespace?

1. I have taken HOT backup.

SQL> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------------------------------------SYSAUX D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSAUX01.DBF

UNDOTBS1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\UNDOTBS01.DBF

SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF

SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM03.DBF

TEST D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TEST01.DBF

TOOLS D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TOOLS01.DBF


6 rows selected.

2. Created tablesapce testing1 (datafile testing1.dbf)

SQL> create tablespace testing1 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF' size 10m;
Tablespace created.
SQL>

SQL> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------------------------------------TESTING1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF

SYSAUX D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSAUX01.DBF

UNDOTBS1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\UNDOTBS01.DBF

SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF

SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM03.DBF

TEST D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TEST01.DBF

TOOLS D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TOOLS01.DBF

7 rows selected.

3. Shutdown DB

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

I deleted all controlfile.

SQL> startup;
ORACLE instance started.
Total System Global Area 167772160bytes
Fixed Size 1247876 bytes
Variable Size 88081788 bytes
Database Buffers 71303168 bytes
Redo Buffers 7139328 bytes

ORA-00205: error in identifying control file, check alert log for more info
SQL>

4. Shutdown the DB, and copy control file from backup, and start the database.

SQL> startup;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 88081788 bytes
Database Buffers 71303168 bytes
Redo Buffers 7139328 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF'
ORA-01207: file is more recent than control file - old control file
SQL>
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 651721784 generated at 11/26/2008 10:50:57 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_13_1_671795289.ARCORA-00280: change 651721784 for thread 1 is in sequence #13

Specify log: {=suggested filename AUTO CANCEL}

ORA-00279: change 651722448 generated at 11/26/2008 11:37:59 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_14_1_671795289.ARC
ORA-00280: change 651722448 for thread 1 is in sequence #14
ORA-00278: log file 'D:\ORACLE\ARCHIVE_ROCK\ARC_13_1_671795289.ARC' no longer needed for this recovery

Specify log: {=suggested filename AUTO CANCEL}

ORA-00283: recovery session canceled due to errorsORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF'
ORA-01112: media recovery not started

SQL> recover database using backup controlfile until cancel;

ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 4 is unknown - rename to correct file
ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00004'
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01111: name for data file 4 is unknown - rename to correct file
ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00004'


SQL> alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00004' to 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF';

Database altered.
SQL>
SQL> alter database open resetlogs;
alter database open resetlogs*
ERROR at line 1:ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF'
SQL>

SQL> recover database using backup controlfile;
ORA-00279: change 651723802 generated at 11/26/2008 11:50:03 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_14_1_671795289.ARC
ORA-00280: change 651723802 for thread 1 is in sequence #14


Specify log: {=suggested filename AUTO CANCEL}
auto
ORA-00279: change 651724316 generated at 11/26/2008 11:52:44 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_15_1_671795289.ARC
ORA-00280: change 651724316 for thread 1 is in sequence #15
ORA-00278: log file 'D:\ORACLE\ARCHIVE_ROCK\ARC_14_1_671795289.ARC' no longer needed for this recovery

ORA-00308: cannot open archived log 'D:\ORACLE\ARCHIVE_ROCK\ARC_15_1_671795289.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL>

The Recovery Process is asking for sequence # 15 and archived log file ''D:\ORACLE\ARCHIVE_ROCK\ARC_15_1_671795289.ARC' which is not even generated at archive log folder.
SQL> select group#, sequence#, archived, status from v$log;

GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
4 13 NO CURRENT
6 12 YES INACTIVE
5 11 YES INACTIVE
SQL>

The sequence# 15 must be available in Online Redo Log files. Instead of giving CANCEL or AUTO, we can give Online Redo Log file name.

SQL> recover database using backup controlfile;
ORA-00279: change 651724316 generated at 11/26/2008 11:52:44 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_15_1_671795289.ARC
ORA-00280: change 651724316 for thread 1 is in sequence #15

Specify log: {=suggested filename AUTO CANCEL}
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO04.LOG
ORA-00310: archived log contains sequence 13; sequence 15 required
ORA-00334: archived log: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO04.LOG'

SQL> recover database using backup controlfile;
ORA-00279: change 651724316 generated at 11/26/2008 11:52:44 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_15_1_671795289.ARC
ORA-00280: change 651724316 for thread 1 is in sequence #15

Specify log: {=suggested filename AUTO CANCEL}
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO05.LOG
ORA-00310: archived log contains sequence 14; sequence 15 required
ORA-00334: archived log: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO05.LOG'

SQL> recover database using backup controlfile;
ORA-00279: change 651724316 generated at 11/26/2008 11:52:44 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ARCHIVE_ROCK\ARC_15_1_671795289.ARC
ORA-00280: change 651724316 for thread 1 is in sequence #15

Specify log: {=suggested filename AUTO CANCEL}
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\REDO06.LOG
Log applied.
Media recovery complete.
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL>

SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------------------------------------TESTING1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TESTING1.DBF

SYSAUX D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSAUX01.DBF

UNDOTBS1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\UNDOTBS01.DBF

SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM01.DBF

SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\SYSTEM03.DBF

TEST D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TEST01.DBF

TOOLS D:\ORACLE\PRODUCT\10.2.0\ORADATA\ROCK\TOOLS01.DBF

7 rows selected.

SQL>

Testing1 tablespace is recoverd.

Scripts..

create user
------------
create user abc
identified by abc
default tablespace tbs1
temporary tablespace temp
quota unlimited on tbs1/

grant connect, create job, create operator, create snapshot, create procedure, create sequence, create synonym, create table, create trigger, create view, debug connect session to abc/
--------------------------------------------------------
only a DBA can import a file exported by another DBA
--------------------------------------------------------
C:\Documents and Settings\Administrator>imp userid=system file=E:\exp_full_db_Sat_26_04_2008_1100PM.dmp fromuser=sa_test touser=sa_test

H:\>imp userid=system@testapp file=D:\abamco_amc.DMP fromuser=abamco_amc touser=amcerptest
--------------------------
FULL_DB_EXPORT.bat
--------------------------
@echo off
for /f "tokens=1,2,3,4 delims=/ " %%a in ('date /t') do set fdate=%%a_%%c_%%b_%%d

for /f "tokens=1,2,3,4,5,6 delims=: " %%i in ('time /t') do set HHMMSS=_%%i%%j%%k%%l%%m%%n

exp system/******* file=D:\Export\exp_full_db_%fdate%%HHMMSS%.dmp full=y log=D:\Export\exp_full_db_%fdate%%HHMMSS%.log
--------------
HOT_BACKUP.bat
--------------
cd D:\oracle\product\10.2.0\db_1\BIN
sqlplus system/***** @F:\HOT_BACKUP\hotbackup.sql
-------------------
HOt Backup.sql
-------------------
host MD F:\HOT_BACKUP\"%DATE:/=_%"
host MD F:\HOT_BACKUP\"%DATE:/=_%"\ARCHIVELOGS
alter tablespace SYSTEM begin backup;
host copy E:\DATAFILES\SYSTEM01.DBF F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\DATAFILES\SYSTEM02.DBF F:\HOT_BACKUP\"%DATE:/=_%;
alter tablespace SYSTEM end backup;
alter tablespace UNDOTBS1 begin backup;
host copy E:\DATAFILES\UNDOTBS01.DBF F:\HOT_BACKUP\"%DATE:/=_%;
alter tablespace UNDOTBS1 end backup;
alter tablespace SYSAUX begin backup;
host copy E:\DATAFILES\SYSAUX01.DBF F:\HOT_BACKUP\"%DATE:/=_%;
alter tablespace SYSAUX end backup;
alter tablespace USERS begin backup;
host copy E:\DATAFILES\USERS01.DBF F:\HOT_BACKUP\"%DATE:/=_%;
alter tablespace USERS end backup;
alter tablespace ABCD begin backup;
host copy E:\DATAFILES\ABCD01.DBF F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\DATAFILES\ABCD02.DBF F:\HOT_BACKUP\"%DATE:/=_%;
alter tablespace ABCD end backup;
alter tablespace AXIS_ALERT begin backup;
host copy E:\DATAFILES\AXIS_ALERT01.DBF F:\HOT_BACKUP\"%DATE:/=_%;
alter tablespace AXIS_ALERT end backup;
alter tablespace ERP begin backup;
host copy E:\DATAFILES\ERP01.DBF F:\HOT_BACKUP\"%DATE:/=_%;
alter tablespace ERP end backup;
host copy D:\oracle\product\10.2.0\db_1\database\INITDB.ora
F:\HOT_BACKUP\"%DATE:/=_%;
host copy D:\oracle\product\10.2.0\db_1\database\PWDDB.ora
F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\REDOLOGS\REDO01.LOG F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\REDOLOGS\REDO02.LOG F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\REDOLOGS\REDO03.LOG F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\CONTROLFILES\CONTROL01.CTL F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\CONTROLFILES\CONTROL02.CTL F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\CONTROLFILES\CONTROL03.CTL F:\HOT_BACKUP\"%DATE:/=_%;
host copy E:\ARCHIVE\*.ARC F:\HOT_BACKUP\"%DATE:/=_%\ARCHIVELOGS
exit;
exit

----------------
verify_db.bat
---------------
cd D:\oracle\product\10.2.0\db_1\BIN

sqlplus "sys/******@DBSTD as sysdba" @D:\verifyDBSTD.sql
---------------
verifyDBSTD.sql
------------
col name format a70

set pages 0

set line 3000

spool verifyDBSTD.txt

select name, applied, to_char(completion_time, 'fmDD-MON-YYYY HH:MI:SS PM') from v$archived_log where completion_time >= '24-MAR-2009' order by name desc;

select process, status from v$managed_standby;

spool off

exit;
---------------------
RMAN_BACKUP.bat
---------------
cd D:\oracle\product\10.2.0\db_1\BIN\

rman target sys/rock@rock cmdfile c:\rman_backup.rcv log c:\rman_log.txt
-------------------
c:\rman_backup.rcv
-------------------
backup as compressed backupset incremental level 0 database plus archivelog;
restore database validate;
exit;
--------------------------
RMAN_BACKUP_Cumulative.bat
--------------------------
backup as compressed backupset incremental level 1 cumulative database plus archivelog;
exit;
---------------------------------
forums.oracle.com
Thread: Incremental Backups are Cumulative Vs Differential??
-------------------------------------------------------
The followings are Weekly Full Backup and Daily Backup scripts:
--------------------
Weekly Full Backup
--------------------
{
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
allocate channel ch1 type disk format
'/u02/db/backup/RMAN/backup_%d_%t_%s_%p_%U.bck';
backup incremental level 0 database plus archivelog delete all input;backup current controlfile;
backup spfile;
release channel ch1;
}
-----------------
Daily Backup:
-----------------
{
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
allocate channel ch1 type disk format '/u02/db/BACKUP/RMAN/backup_%d_%t_%s_%p_%U.bck';
backup incremental level 1 cumulative database plus archivelog delete all input;delete noprompt obsolete;
delete noprompt archivelog all backed up 2 times to disk;
backup current controlfile;
backup spfile;
release channel ch1;
}

Oracle Architecture

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

Oracle Server Architecture:
In order to efficiently and intelligently manage an Oracle database, you have to have a sound knowledge of its underlying architectural details. This knowledge pays off because it is directly linked with DBA’s job. The real job of DBA starts after the installation of database, when he embarks on to setting-up a database and then maintaining it. Naturally, the configuration and maintenance of database cannot be done by a DBA unless he doesn’t know about the ins and outs of its architecture. The following section provides a short overview of Oracle architecture.

The Oracle Server can be divided into a physical and logical portion. The physical portion is Oracle database, which resides on disks and logical portion is Instance, which resides in memory. Instance is basically a mean to access the database.

Oracle Instance: In order to access the database Oracle Server initiates some background processes and allocates some shared memory. These background processes and shared memory structures made up of Oracle Instance. The properties of instance (i.e. the properties of background processes and memory structures) are present in the parameter file in the form of parameters having some values. Oracle Server needs these parameters to create an instance while in startup stage.

Shared Memory Structures: The shared memory structure of instance is called as Shared Global Area (SGA). Basically, it contains the most frequently data and code, along with some control information. SGA divides its work into subcomponents.

Database Buffer Cache: It is commonly called as ‘Buffer Cache’. Buffer Cache stores the most recent and frequent data. Any data read from the database, first comes to buffer cache and from it, multiple user can access it.

Redo Log Buffer: It stores the changes, which are made to the data and is only used for recovery purposes.

Shared Pool: It is that part of SGA, which stores most frequent and recent SQL, PL/SQL code along with data dictionary information. The code part (SQL, PL/SQL) is stored library cache of shared pool and data about schema objects is stored in data dictionary cache of shared pool.

Large Pool: It is an optional area and used to facilitate large jobs.

Java Pool: It is also an optional area used to facilitate Java code.

Stream Pool: It is also an optional area, used in case of Oracle Streams.

Back Ground Processes:
The Background processes are used to facilitate the working of instance with respect to database. Background processes are responsible for the efficient interaction of memory structures of instance and database. Some background processes are always there and some are added up as you configure more features of database. Some common background processes are:
Database Writer: It takes data from buffer cache and writes it to data files.

Log Writer: It takes information from redo log buffer and writes it to online redo logs on disk.
Checkpoint: It ensures that at continuous intervals, all modified data from buffer cache is written to data files.
System Monitor: It performs crash recovery and some space management.
Process Monitor: It performs process recovery, in case of failure of user process.
Archiver: It makes offline copies of online redo logs and helps to recover from media failure.

Private Memory Area: Besides of SGA, there is a private memory structure called Program Global Area (PGA). It contains session information for a single server process. A server process is a foreground or shadow process which acts on behalf of one or more users. If a database is in dedicated mode, then for each user there would be a separate server process, and if a database is in shared mode, then there would be some server process which would be servicing all user processes between them.

http://www.chowrangi.com/oracle-dba-concepts-part-2.html