Auditing in Oracle

Auditing DML operations on table level

Audit update,delete,insert on scott.emp by access;

Auditing DDL operations

AUDIT create table,alter table,drop table By scott whenever sucessful;

To turn off all the auditing the statement is.
NOAUDIT ALL;
NOAUDIT ALL ON SCOTT.EMP;

To Check the auditing table details and the session information use below
select * from dba_audit_trail where username='SCOTT' and timestamp like '28-MAY-10%'
select * from aud$
select * from DBA_AUDIT_OBJECT where username ='SCOTT'


The Data dictionary views related auditing are
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_STMT_AUDIT_OPTS
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL

Manual recovery of a standby in case of structural changes in Primary

This scenario applicable if you have done any structural changes in primary database and there is a problem in replicating the changes in standby site.

shutdown standby

startup nomount

alter database mount standby database

recover standby database until cancel

AUTO

http://dbaspot.com/forums/oracle-server/71881-physical-standby-database-cannot-close-gap-if-any-gap-exists.html

'ORA-00280: change 3220290269 for thread 1 is in sequence #17219

ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17218.ARC' no longer needed for this recovery

ORA-00279: change 3220290274 generated at 04/11/2010 19:03:32 needed for thread 1

ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17220.ARC

ORA-00280: change 3220290274 for thread 1 is in sequence #17220

ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17219.ARC' no longer needed for this recovery

ORA-00279: change 3220300520 generated at 04/11/2010 23:27:08 needed for thread 1

ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17221.ARC

ORA-00280: change 3220300520 for thread 1 is in sequence #17221

ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17220.ARC' no longer needed for this recovery

ORA-00279: change 3220300533 generated at 04/11/2010 23:27:16 needed for thread 1

ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17222.ARC

ORA-00280: change 3220300533 for thread 1 is in sequence #17222

ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17221.ARC' no longer needed for this recovery

ORA-00279: change 3220304453 generated at 04/12/2010 01:05:52 needed for thread 1

ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17223.ARC

ORA-00280: change 3220304453 for thread 1 is in sequence #17223

ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17222.ARC' no longer needed for this recovery

ORA-00279: change 3220304456 generated at 04/12/2010 01:05:53 needed for thread 1

ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17224.ARC

ORA-00280: change 3220304456 for thread 1 is in sequence #17224

ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17223.ARC' no longer needed for this recovery

ORA-00279: change 3220429248 generated at 04/12/2010 09:49:53 needed for thread 1

ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17225.ARC

ORA-00280: change 3220429248 for thread 1 is in sequence #17225

ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17224.ARC' no longer needed for this recovery

ORA-00308: cannot open archived log 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17225.ARC'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 32) The process cannot access the file because it is being used by another process.

SQL>

SQL> recover standby database until cancel;

ORA-00279: change 3220429248 generated at 04/12/2010 09:49:53 needed for thread 1

ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17225.ARC

ORA-00280: change 3220429248 for thread 1 is in sequence #17225

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 3220572194 generated at 04/12/2010 10:46:06 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17226.ARC
ORA-00280: change 3220572194 for thread 1 is in sequence #17226
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17225.ARC' no longer needed for this recovery


ORA-00279: change 3220627014 generated at 04/12/2010 11:06:36 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17227.ARC
ORA-00280: change 3220627014 for thread 1 is in sequence #17227
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17226.ARC' no longer needed for this recovery


ORA-00279: change 3220627289 generated at 04/12/2010 11:06:51 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17228.ARC
ORA-00280: change 3220627289 for thread 1 is in sequence #17228
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17227.ARC' no longer needed for this recovery


ORA-00279: change 3220763002 generated at 04/12/2010 12:27:59 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17229.ARC
ORA-00280: change 3220763002 for thread 1 is in sequence #17229
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17228.ARC' no longer needed for this recovery


ORA-00279: change 3220899620 generated at 04/12/2010 13:24:57 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17230.ARC
ORA-00280: change 3220899620 for thread 1 is in sequence #17230
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17229.ARC' no longer needed for this recovery


ORA-00279: change 3221036980 generated at 04/12/2010 14:44:50 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17231.ARC
ORA-00280: change 3221036980 for thread 1 is in sequence #17231
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17230.ARC' no longer needed for this recovery


ORA-00279: change 3221165247 generated at 04/12/2010 16:11:07 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17232.ARC
ORA-00280: change 3221165247 for thread 1 is in sequence #17232
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17231.ARC' no longer needed for this recovery


ORA-00279: change 3221302585 generated at 04/12/2010 17:30:15 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17233.ARC
ORA-00280: change 3221302585 for thread 1 is in sequence #17233
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17232.ARC' no longer needed for this recovery


ORA-00279: change 3221437356 generated at 04/12/2010 18:37:11 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17234.ARC
ORA-00280: change 3221437356 for thread 1 is in sequence #17234
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17233.ARC' no longer needed for this recovery


ORA-00279: change 3221499410 generated at 04/12/2010 19:02:44 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17235.ARC
ORA-00280: change 3221499410 for thread 1 is in sequence #17235
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17234.ARC' no longer needed for this recovery


ORA-00279: change 3221499630 generated at 04/12/2010 19:03:02 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17236.ARC
ORA-00280: change 3221499630 for thread 1 is in sequence #17236
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17235.ARC' no longer needed for this recovery


ORA-00279: change 3221663610 generated at 04/12/2010 20:36:12 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17237.ARC
ORA-00280: change 3221663610 for thread 1 is in sequence #17237
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17236.ARC' no longer needed for this recovery


ORA-00279: change 3221809532 generated at 04/12/2010 21:37:13 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17238.ARC
ORA-00280: change 3221809532 for thread 1 is in sequence #17238
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17237.ARC' no longer needed for this recovery


ORA-00279: change 3221951748 generated at 04/12/2010 22:41:44 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17239.ARC
ORA-00280: change 3221951748 for thread 1 is in sequence #17239
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17238.ARC' no longer needed for this recovery


ORA-00279: change 3222098577 generated at 04/12/2010 23:39:32 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17240.ARC
ORA-00280: change 3222098577 for thread 1 is in sequence #17240
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17239.ARC' no longer needed for this recovery


ORA-00279: change 3222248037 generated at 04/13/2010 00:55:46 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17241.ARC
ORA-00280: change 3222248037 for thread 1 is in sequence #17241
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17240.ARC' no longer needed for this recovery


ORA-00279: change 3222264966 generated at 04/13/2010 01:05:47 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17242.ARC
ORA-00280: change 3222264966 for thread 1 is in sequence #17242
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17241.ARC' no longer needed for this recovery


ORA-00279: change 3222265468 generated at 04/13/2010 01:06:02 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17243.ARC
ORA-00280: change 3222265468 for thread 1 is in sequence #17243
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17242.ARC' no longer needed for this recovery


ORA-00279: change 3222405780 generated at 04/13/2010 02:19:18 needed for thread 1
ORA-00289: suggestion : D:\ORADATA\8sys\ARCHIVE\LOG_1_17244.ARC
ORA-00280: change 3222405780 for thread 1 is in sequence #17244
ORA-00278: log file 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17243.ARC' no longer needed for this recovery


ORA-00308: cannot open archived log 'D:\ORADATA\8sys\ARCHIVE\LOG_1_17244.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> disc

SQL> conn ****/***** as sysdba
Connected.
SQL>
SQL> set pages 100
SQL> set lines 1000
SQL>
SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_time like '12-APR-10%';

SEQUENCE# ARC APP DEL
---------- --- --- ---
17222 YES YES NO
17223 YES YES NO
17224 YES YES NO
17225 YES YES NO
17226 YES YES NO
17227 YES YES NO
17228 YES YES NO
17229 YES YES NO
17230 YES YES NO
17231 YES YES NO
17232 YES YES NO
17233 YES YES NO
17234 YES YES NO
17235 YES YES NO
17236 YES YES NO
17237 YES YES NO
17238 YES YES NO
17239 YES YES NO

18 rows selected.

SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_time like '13-APR-10%';

SEQUENCE# ARC APP DEL
---------- --- --- ---
17240 YES YES NO
17241 YES YES NO
17242 YES YES NO
17243 YES YES NO

SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_time like '13-APR-10%';

SEQUENCE# ARC APP DEL
---------- --- --- ---
17240 YES YES NO
17241 YES YES NO
17242 YES YES NO
17243 YES YES NO
17244 YES NO NO

SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_time like '13-APR-10%';

SEQUENCE# ARC APP DEL
---------- --- --- ---
17240 YES YES NO
17241 YES YES NO
17242 YES YES NO
17243 YES YES NO
17244 YES NO NO
17245 YES NO NO

6 rows selected.

SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_time like '13-APR-10%';

SEQUENCE# ARC APP DEL
---------- --- --- ---
17240 YES YES NO
17241 YES YES NO
17242 YES YES NO
17243 YES YES NO
17244 YES NO NO
17245 YES NO NO

6 rows selected.

SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_time like '13-APR-10%';

SEQUENCE# ARC APP DEL
---------- --- --- ---
17240 YES YES NO
17241 YES YES NO
17242 YES YES NO
17243 YES YES NO
17244 YES NO NO
17245 YES NO NO

6 rows selected.

SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_time like '13-APR-10%';

SEQUENCE# ARC APP DEL
---------- --- --- ---
17240 YES YES NO
17241 YES YES NO
17242 YES YES NO
17243 YES YES NO
17244 YES YES NO
17245 YES YES NO

6 rows selected.

SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_time like '13-APR-10%';

SEQUENCE# ARC APP DEL
---------- --- --- ---
17240 YES YES NO
17241 YES YES NO
17242 YES YES NO
17243 YES YES NO
17244 YES YES NO
17245 YES YES NO

6 rows selected.

SQL> exit

Flashback Drop scenario

The tested scenario for the flashback drop feature.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
D TABLE
SYS_TEMP_FBT TABLE
C TABLE
N TABLE
B TABLE
A1 TABLE

6 rows selected.

SQL> drop table c;

Table dropped.

SQL> select * from c;
select * from c
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME
------------------------------ -------------------------------- --------- ------------------------- ----------
BIN$PRtTP+PTTlqWRcW9NnHSRg==$0 C DROP TABLE USERS

SQL> select autoextensible from dba_data_files;

AUT
---
YES
YES
YES
YES

SQL> alter database datafile 4 autoextend off;

Database altered.

SQL> select file_name,autoextensible from dba_data_files;

FILE_NAME AUT
---------------------------------------------------------------------- ---
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF NO
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF YES
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF YES
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF YES


SQL> create table flashbk (no number);

Table created.

SQL> insert into flashbk select object_id from dba_objects;

49836 rows created.

SQL> /
insert into flashbk select object_id from dba_objects
*
ERROR at line 1:
ORA-01653: unable to extend table NV.FLASHBK by 128 in tablespace USERS


SQL> select * from recyclebin;

no rows selected

Standby Switchover demo steps

1.Script for taking backup of the primary database:
run
{
allocate channel c1 device type disk format 'd:\backup\%d_DATA_%U_%T';
allocate channel c2 device type disk format 'd:\backup\%d_DATA_%U_%T';
allocate channel c3 device type disk format 'd:\backup\%d_DATA_%U_%T';
backup database plus archivelog;
release channel c1;
release channel c2;
release channel c3;
allocate channel c1 type disk format 'D:\backup\%D_CRTL_%U_%T';
backup current controlfile for standby;
release channel c1;
}
2. Switchover the primary database to the standby mode.


3. Bringing the new standby database to the recovery mode


4. Switchover the old standby database to primary mode



5. Configure the standby archive destination in the new primary database:

Move files in windows

The below script runs continously and move files every five minutes from 1 location to another location.

:move_archives
echo %date%, %time% >> D:\move_archives.log
move D:\oradata\archive\*.* E:\ORADATA\archive >> D:\move_archives.log
echo %date%, %time% >> D:\move_archives.log
sleep 1800
goto move_archives

Database connectivity from Oracle to SQL server

By using ODBC connection:

Step 1:

Create a DSN (eg. MSSQL) entry in “/etc/odbc.ini” file by providing the below details

Description = "Description of the connection user identification"
Driver = "Driver path for SQL server"
Server = "SQL Server database server name>"
Port = 5432 "this is default port number"
User = "username for connecting the sql server database"
Password = "password for connect the sql server database"
Sid = "SQL server database name"
Trace = No "default value"
TraceFile = null "default value"
Database = "Database name"
Servername = "Server name were the SQL server database exists"
Username = "database username"

Step 2:

Copy inithsodbc.ora file in ORACLE_HOME \hs\admin folder as initMSSQL.ora file and modify entries like

HS_FDS_CONNECT_INFO = MSSQL --> DSN Name which we created using ODBC
HS_FDS_TRACE_LEVEL = OFF



Step 3:

Add one more entry to SID_List_Listener in Listener.ora file in ORACLE_HOME\Network\Admin folder like the below

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = MSSQL) à DSN Name used as SID of SQL server
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = hsodbc)
)
)

Step 4:

Now create a TNS entry as the following in TnsNames.ora file in Oracle_Home\Network\Admin

MSSQL =
(DESCRIPTION=
(CONNECT_DATA=(SID=MSSQL))
(ADDRESS = (PROTOCOL = TCP)(HOST = C202878)(PORT = 1521))
(HS=OK)
)

Step 5:

Reload the listener.

Step 6:

Try tnspinging the sql server SID and check for the response.

U:\>tnsping MSSQL

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 25-SEP-2009 14:56:26

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (CONNECT_DATA=(SID= MSSQL)) (ADDRESS = (PROTOCOL = TCP)(HOST = local host)(PORT = 1521)) (HS=OK))
OK (30 msec)

Step 7:

Create DB link using the above SID and try connecting.


By Using Oracle Transparent Gateways for Microsoft SQL server:

Step 1:

Install Transparent Gateway for Microsoft SQL server.

While installing it will ask for the server name and MS SQL server database name .Give the Server name where the SQL server database exists and the database name which you are planning to connect.

Installing this component will create a folder “tg4msql” in under ORACLE_HOME location.
Inside this we will have some configuration files inside “D:\oracle\product\10.2.0\db_1\tg4msql\admin” folder.

Optional (only needed when configuring for multiple SQL servers)
Take a copy of inittg4msql.ora file in the ORACLE_HOME\tg4msql\admin folder and change the name of file as init<>.ora where <> is the SID Name given to the Transparent Gateway (eg. initORCL_SqlSer.ora)



Step 2:

Add one more entry to SID_List_Listener in Listener.ora file in ORACLE_HOME\Network\Admin folder like the below


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = tg4msql) à SID for SQL Server connectivity in Listener.ora
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = tg4msql) à Identifies transparent gateway for MS SQL Server
)
)

Step 3:

Now create a TNS entry as the following in TnsNames.ora file in Oracle_Home\Network\Admin

tg4msql =
(DESCRIPTION=
(CONNECT_DATA=(SID= tg4msql )) à SID of the MSQL server database.
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost )(PORT = 1521))
(HS=OK) à Need to identify the connection as a heterogeneous connection
)


Step 4:

Reload the listener.

Step 5:

Try tnspinging the sql server SID and check for the response.

U:\>tnsping tg4msql

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 25-SEP-2009 14:56:26

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (CONNECT_DATA=(SID= tg4msql)) (ADDRESS = (PROTOCOL = TCP)(HOST = local host)(PORT = 1521)) (HS=OK))
OK (30 msec)

Step 6:

Create DB link using the above SID and try connecting.

redo apply

You can specify that Redo Apply runs as a foreground session or as a background process, and enable it with real-time apply.
To start Redo Apply in the foreground, issue the following SQL statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
If you start a foreground session, control is not returned to the command prompt until recovery is canceled by another session.
To start Redo Apply in the background, include the DISCONNECT keyword on the SQL statement. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
This statement starts a detached server process and immediately returns control to the user. While the managed recovery process is performing recovery in the background, the foreground process that issued the RECOVER statement can continue performing other tasks. This does not disconnect the current SQL session.
To start real-time apply, include the USING CURRENT LOGFILE clause on the SQL statement. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/log_apply.htm