Friday, December 12, 2014
Tablespaces DDL - Oracle
There may be situation where you are trying to create a new database similar to old one and it is a fresh install and you need to get the Tablespaces DDL from the old one. This query will be very help full.
SQL>Set pages 999;
SQL>set long 90000;
SQL>spool ddl_list.sql
SQL>select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;
SQL>spool off
Sample Output :
" CREATE TABLESPACE "USERS" DATAFILE
'/u02/oracle/oradata/ORCL/datafiles/users01.dbf' SIZE 5242880
AUTOEXTEND ON NEXT 52428800 MAXSIZE 20000M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
ALTER DATABASE DATAFILE
'/u02/oracle/oradata/ORCL/datafiles/users01.dbf' RESIZE 2097152000";
" CREATE TABLESPACE "TOOLS" DATAFILE
'/u02/oracle/oradata/ORCL/datafiles/tools01.dbf' SIZE 67108864
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO";
" CREATE TABLESPACE "INDX" DATAFILE
'/u02/oracle/oradata/ORCL/datafiles/indx01.dbf' SIZE 268435456
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO";
Wednesday, December 10, 2014
Audit failed logon attempts - Oracle
How to audit failed logon attempts
Oracle Audit -- failed connection
Background:
In some situation DBA team wants to audit failed logon attempts when "unlock account" requirement becomes frequently and user cannot figure out who from where is using incorrect password to cause account get locked.
Audit concern:
Oracle auditing may add extra load and require extra operation support. For this situation DBA only need audit on failed logon attempts and do not need other audit information. Failed logon attempt is only be able to track through Oracle audit trail, logon trigger does not apply to failure logon attempts
Hint: The setting here is suggested to use in a none production system. Please evaluate all concern and load before use it in production.
Approach:
1. Turn on Oracle audit function by set init parameter:
audit_trail=DB
Note:
database installed by manual script, the audit function may not turn on:
database installed by dbca, the default audit function may already turn on:
Check:
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
Turn on Oracle audit
a. If database use spfile
SQL> alter system set audit_trail=DB scope=spfile ;
System altered.
b. if database use pfile, modify init<Sid>.ora directly.
Restart database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.
2. Turn off Oracle default audit
Privilege audit information stored in dba_priv_audit_opts;
Note: Oracle 11g has couple of audit turned on default when the audit_trail is set. It will just utilize more resources that you might not want.
Oracle 10g, audit options is setup by explicit command (we can audit these options back any time).
Generate a script to turn off default privilege audit which we don't need here.
SQL> SELECT 'noaudit '|| privilege||';' from dba_priv_audit_opts where user_name is NULL;
'NOAUDIT'||PRIVILEGE||';'
-------------------------------------------------
noaudit ALTER SYSTEM;
noaudit AUDIT SYSTEM;
noaudit CREATE SESSION;
noaudit CREATE USER;
noaudit ALTER USER;
noaudit DROP USER;
noaudit CREATE ANY TABLE;
noaudit ALTER ANY TABLE;
noaudit DROP ANY TABLE;
noaudit CREATE PUBLIC DATABASE LINK;
noaudit GRANT ANY ROLE;
noaudit ALTER DATABASE;
noaudit CREATE ANY PROCEDURE;
noaudit ALTER ANY PROCEDURE;
noaudit DROP ANY PROCEDURE;
noaudit ALTER PROFILE;
noaudit DROP PROFILE;
noaudit GRANT ANY PRIVILEGE;
noaudit CREATE ANY LIBRARY;
noaudit EXEMPT ACCESS POLICY;
noaudit GRANT ANY OBJECT PRIVILEGE;
noaudit CREATE ANY JOB;
noaudit CREATE EXTERNAL JOB;
23 rows selected.
-- run above commands
3. Turn on audit on failed connection
SQL> AUDIT CONNECT WHENEVER NOT SUCCESSFUL;
Audit succeeded.
SQL> SELECT PRIVILEGE,SUCCESS,FAILURE FROM dba_priv_audit_opts;
PRIVILEGE SUCCESS FAILURE
---------------------------------------- ---------- ----------
CREATE SESSION NOT SET BY ACCESS
4. Retrieve information
Note: audit information is stored on sys.aud$. There multiple views Oracle provide to help you read sys.aud$. Logon failed information can be retrieve from dba_audit_session
SQL> select os_username, username, userhost, to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') logon_time, action_name, returncode from dba_audit_session;
OS_USERNAME USERNAME USERHOST TIMESTAMP ACTION_NAME RETURNCODE
------------------------------ ------------------------------ -------------------------------------------------- ------------------- ---------------------------- ----------
Arvind machine1
HOME-Arvind 12/06/2014 13:40:12 LOGON 1017
Arvind machine1
HOME-lArvind 12/06/2014 13:40:25 LOGON 1017
Arvind machine1
HOME-Arvind 12/06/2014 15:31:29 LOGON 1017
Arvind machine1
HOME-Arvind 12/06/2014 15:31:38 LOGON 1017
4 rows selected.
Note: RETURNCODE is the ORA error code return to user.
ORA-1017 is incorrect password
ORA-28000 is account locked
ORA-1045 is missing connect privilege
------------------------------------------------------------
Up here, we be able to audit who is the bad boy causing account locked.
Turning off the audit:
If you no longer need the audit on failed attempts, run this command to turn off
SQL> noaudit CONNECT;
Noaudit succeeded.
SQL> SELECT PRIVILEGE,SUCCESS,FAILURE FROM dba_priv_audit_opts;
no rows selected
Oracle use system tablespace for sys.aud$. For enhancement, you may consider to move sys.aud$ to separate tablespace.
6. Move sys.aud$ out of system tablespace.
Oracle 11g provide package dbms_audit_mgmt.set_audit_trail_location to relocate the aud$ table.
SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name ='AUD$';
TABLE_NAME TABLESPACE_NAME
----------------------------- ------------------------------
AUD$ SYSTEM
Following example shows how to move sys.aud$ from system tablespace to user_data1 tablespace.
SQL> exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'USER_DATA1');
PL/SQL procedure successfully completed.
SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name ='AUD$';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ USER_DATA1
7. Clean up AUD$
You can simply run delete or truncate command
delete from sys.AUD$;
truncate table sys.AUD$;
Oracle Audit -- failed connection
Background:
In some situation DBA team wants to audit failed logon attempts when "unlock account" requirement becomes frequently and user cannot figure out who from where is using incorrect password to cause account get locked.
Audit concern:
Oracle auditing may add extra load and require extra operation support. For this situation DBA only need audit on failed logon attempts and do not need other audit information. Failed logon attempt is only be able to track through Oracle audit trail, logon trigger does not apply to failure logon attempts
Hint: The setting here is suggested to use in a none production system. Please evaluate all concern and load before use it in production.
Approach:
1. Turn on Oracle audit function by set init parameter:
audit_trail=DB
Note:
database installed by manual script, the audit function may not turn on:
database installed by dbca, the default audit function may already turn on:
Check:
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
Turn on Oracle audit
a. If database use spfile
SQL> alter system set audit_trail=DB scope=spfile ;
System altered.
b. if database use pfile, modify init<Sid>.ora directly.
Restart database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.
2. Turn off Oracle default audit
Privilege audit information stored in dba_priv_audit_opts;
Note: Oracle 11g has couple of audit turned on default when the audit_trail is set. It will just utilize more resources that you might not want.
Oracle 10g, audit options is setup by explicit command (we can audit these options back any time).
Generate a script to turn off default privilege audit which we don't need here.
SQL> SELECT 'noaudit '|| privilege||';' from dba_priv_audit_opts where user_name is NULL;
'NOAUDIT'||PRIVILEGE||';'
-------------------------------------------------
noaudit ALTER SYSTEM;
noaudit AUDIT SYSTEM;
noaudit CREATE SESSION;
noaudit CREATE USER;
noaudit ALTER USER;
noaudit DROP USER;
noaudit CREATE ANY TABLE;
noaudit ALTER ANY TABLE;
noaudit DROP ANY TABLE;
noaudit CREATE PUBLIC DATABASE LINK;
noaudit GRANT ANY ROLE;
noaudit ALTER DATABASE;
noaudit CREATE ANY PROCEDURE;
noaudit ALTER ANY PROCEDURE;
noaudit DROP ANY PROCEDURE;
noaudit ALTER PROFILE;
noaudit DROP PROFILE;
noaudit GRANT ANY PRIVILEGE;
noaudit CREATE ANY LIBRARY;
noaudit EXEMPT ACCESS POLICY;
noaudit GRANT ANY OBJECT PRIVILEGE;
noaudit CREATE ANY JOB;
noaudit CREATE EXTERNAL JOB;
23 rows selected.
-- run above commands
3. Turn on audit on failed connection
SQL> AUDIT CONNECT WHENEVER NOT SUCCESSFUL;
Audit succeeded.
SQL> SELECT PRIVILEGE,SUCCESS,FAILURE FROM dba_priv_audit_opts;
PRIVILEGE SUCCESS FAILURE
---------------------------------------- ---------- ----------
CREATE SESSION NOT SET BY ACCESS
4. Retrieve information
Note: audit information is stored on sys.aud$. There multiple views Oracle provide to help you read sys.aud$. Logon failed information can be retrieve from dba_audit_session
SQL> select os_username, username, userhost, to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') logon_time, action_name, returncode from dba_audit_session;
OS_USERNAME USERNAME USERHOST TIMESTAMP ACTION_NAME RETURNCODE
------------------------------ ------------------------------ -------------------------------------------------- ------------------- ---------------------------- ----------
Arvind machine1
HOME-Arvind 12/06/2014 13:40:12 LOGON 1017
Arvind machine1
HOME-lArvind 12/06/2014 13:40:25 LOGON 1017
Arvind machine1
HOME-Arvind 12/06/2014 15:31:29 LOGON 1017
Arvind machine1
HOME-Arvind 12/06/2014 15:31:38 LOGON 1017
4 rows selected.
Note: RETURNCODE is the ORA error code return to user.
ORA-1017 is incorrect password
ORA-28000 is account locked
ORA-1045 is missing connect privilege
------------------------------------------------------------
Up here, we be able to audit who is the bad boy causing account locked.
Turning off the audit:
If you no longer need the audit on failed attempts, run this command to turn off
SQL> noaudit CONNECT;
Noaudit succeeded.
SQL> SELECT PRIVILEGE,SUCCESS,FAILURE FROM dba_priv_audit_opts;
no rows selected
Oracle use system tablespace for sys.aud$. For enhancement, you may consider to move sys.aud$ to separate tablespace.
6. Move sys.aud$ out of system tablespace.
Oracle 11g provide package dbms_audit_mgmt.set_audit_trail_location to relocate the aud$ table.
SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name ='AUD$';
TABLE_NAME TABLESPACE_NAME
----------------------------- ------------------------------
AUD$ SYSTEM
Following example shows how to move sys.aud$ from system tablespace to user_data1 tablespace.
SQL> exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'USER_DATA1');
PL/SQL procedure successfully completed.
SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name ='AUD$';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ USER_DATA1
7. Clean up AUD$
You can simply run delete or truncate command
delete from sys.AUD$;
truncate table sys.AUD$;
Wednesday, November 19, 2014
TNS-03505: Failed to resolve name
Recently I have installed a client on a local machine and I was trying to connect the database. I know I have everything right but still I was getting this error.
C:\Users\localhost>tnsping orcl
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 19-NOV-2
014 15:22:45
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora
TNS-03505: Failed to resolve name
C:\Users\localhost>
I went back to check the Tnsnames.ora file to see if there is anything wrong in it. But no luck I couldn't find anything.
Sample Tnsnames.ora on my machine.
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
)
)
Wondered what could have gone wrong.
Then figured that there was a simple mistake that I made. There was a empty space in front of the orcl. I deleted the empty space and everything worked.
C:\Users\localhost>tnsping orcl
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 19-NOV-2
014 15:22:45
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora
TNS-03505: Failed to resolve name
C:\Users\localhost>
I went back to check the Tnsnames.ora file to see if there is anything wrong in it. But no luck I couldn't find anything.
Sample Tnsnames.ora on my machine.
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
)
)
Wondered what could have gone wrong.
Then figured that there was a simple mistake that I made. There was a empty space in front of the orcl. I deleted the empty space and everything worked.
Tuesday, November 18, 2014
Oracle audting explained
Oracle AUDIT Explained
Enabling Auditing in Database
#1
You can specify DB,EXTENDED in either of the following ways:
ALTER SYSTEM SET AUDIT_TRAIL=DB, EXTENDED SCOPE=SPFILE;
ALTER SYSTEM SET AUDIT_TRAIL='DB','EXTENDED' SCOPE=SPFILE;
However, do not enclose DB, EXTENDED in quotes, for example:
ALTER SYSTEM SET AUDIT_TRAIL='DB, EXTENDED' SCOPE=SPFILE;
OS Directs all audit records to an operating system file.
#2
Directs audit records to the database audit trail (the SYS.AUD$ table), except for mandatory and SYS audit records, which are always written to the operating system audit trail
#3
The operating system and database audit trails both capture many of the same types of actions.
Operating System Audit Record Equivalent DBA_AUDIT_TRAIL View Column
SESSIONID SESSIONID
ENTRYID ENTRYID
STATEMENT STATEMENTID
USERID USERNAME
USERHOST USERHOST
TERMINAL TERMINAL
ACTION ACTION
SYS$OPTIONS Indicates what audit option was set with AUDIT Or
NOAUDIT, or what privilege was granted or revoked.
RETURNCODE RETURNCODE
OBJ$CREATOR OWNER
OBJ$NAME OBJ_NAME
OBJ$PRIVILEGES OBJ_PRIVILEGE
AUTH$GRANTEE GRANTEE
NEW$OWNER NEW_OWNER
NEW$NAME NEW_NAME
SES$ACTIONS SES_ACTIONS
LOGOFF$PREAD LOGOFF_PREAD
LOGOFF$LWRITE LOGOFF_LWRITE
COMMENT$TEXT COMMENT_TEXT
OS$USERID OS_USERNAME
PRIV$USED PRIV_USED
SES$LABEL CLIENT_ID
SES$TID Does not have an equivalent in the DBA_AUDIT_TRAIL
view, but it does appear in the SYS.AUD$ table
SPARE2 Does not have an equivalent in the DBA_AUDIT_TRAIL
view, but it does appear in the SYS.AUD$ table
#4
If you set AUDIT_SYS_OPERATIONS to TRUE and AUDIT_TRAIL to XML or XML,EXTENDED, then Oracle Database writes SYS audit records operating system files in XML format.
#5
AUDIT_SYSLOG_LEVEL, which writes SYS and standard OS audit records to the system audit log using the SYSLOG utility. This option only applies to UNIX environments.
#6
The XML AUDIT_TRAIL value does not affect syslog audit file. In other words, if you have set the AUDIT_TRAIL parameter to XML, then the syslog audit records will still be in text format, not XML file format.
#7
To write SYS and mandatory audit files to operating system files in XML format: Set AUDIT_TRAIL to XML or XML,EXTENDED, set AUDIT_SYS_OPERATIONS to TRUE, but do not set the AUDIT_SYSLOG_LEVEL parameter.
To write SYS and mandatory audit records to syslog audit files and standard audit
records to XML audit files: Set AUDIT_TRAIL to XML or XML,EXTENDED, set AUDIT_
SYS_OPERATIONS to TRUE, and set the AUDIT_SYSLOG_LEVEL parameter.
#8
If
the AUDIT_TRAIL initialization parameter is set to XML (or XML,
EXTENDED), then Oracle Database writes audit records to the operating
system as XML files. You can use the V$XML_AUDIT_TRAIL view to make XML
audit records available to database administrators through a SQL query,
providing enhanced usability.
#9
The
DBA_COMMON_AUDIT_TRAIL view includes the standard and fine grained
audit trails written to database tables, XML-format audit trail records,
and the contents of the V$XML_AUDIT_TRAIL dynamic view (standard, fine
grained, SYS and mandatory).
#10
Syslog Audit Trail
Potential
security vulnerability for the operating system audit trail is that a
privileged user, such as a database administrator, can modify or delete
database audit records. To minimize this risk, you can use a syslog
audit trail. Syslog is a standard protocol on UNIX-based systems for
logging information from different components of a network. Applications
call the syslog() function to log information to the syslog daemon,
which then determines where to log the information. You can configure
syslog to log information to a file or to a dedicated host by editing
the syslog.conf file. You can also configure syslog to alert a specified
set of users when information is logged.
#11
Setting the Size of the Operating System Audit Trail
To control the size of the operating system audit trail, set theDBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE
property by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY PL/SQL
procedure. Remember that you must have the EXECUTE privilege for the
DBMS_AUDIT_MGMT PL/SQL package before you can use it. When the operating
system file meets the size limitation you set, Oracle Database stops
adding records
to the current file and then creates a new operating system file for
the subsequent records. For more information about the DBMS_AUDIT_MGMT
PL/SQL package.
SETTING THE SIZE OF THE OPERATING SYSTEM AUDIT TRAIL
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
AUDIT_TRAIL_PROPERTY => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
AUDIT_TRAIL_PROPERTY_VALUE => 102400);
END;
/
Note: 1) AUDIT_TRAIL_PROPERTY_VALUE: Sets the maximum size to 102400 kilobytes, that is,
10 MB. The default setting is 10,000 kilobytes (approximately 10 MB). Do not exceed 2 GB.
2) DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE property, which sets the maximum size. To find the
status of the current property settings, query the PARAMETER_NAME and PARAMETER_VALUE
columns of the DBA_AUDIT_MGMT_CONFIG_PARAMS data dictionary view.
SETTING THE AGE OF THE OPERATING SYSTEM AUDIT TRAIL
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
AUDIT_TRAIL_PROPERTY => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
AUDIT_TRAIL_PROPERTY_VALUE => 10 );
END;
/
Note: 1) AUDIT_TRAIL_PROPERTY_VALUE: Sets the maximum age to 10 days. Enter a value
between 1 and 495. The default age is 5 days.
2) DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE property to set the maximum age. To find the status
of the current property setting, query the DBA_AUDIT_MGMT_CONFIG_PARAMS data dictionary
view.
Views
DBA_AUDIT_MGMT_CLEAN_EVENTS
Displays the history of purge events. Periodically, as user SYS
connected with the SYSDBA privilege, you should delete the contents of
this view so that it does not grow too large.
DBA_AUDIT_MGMT_CLEANUP_JOBS Displays the currently configured audit trail purge jobs
DBA_AUDIT_MGMT_CONFIG_PARAMS Displays the currently configured audit trail properties that are used by the DBMS_AUDIT_MGMT PL/SQL package
DBA_AUDIT_MGMT_LAST_ARCH_TS Displays the last archive timestamps that have set for audit trail purges.
#12
Listing Active Object Audit Options for Specific Objects
SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER = 'BHUVAN' AND OBJECT_NAME LIKE 'EMP%';
The view returns information about all the audit options for the specified object. The information in the view is interpreted as follows:
* A dash (-) indicates that the audit option is not set.
* The S character indicates that the audit option is set BY SESSION.
* The A character indicates that the audit option is set BY ACCESS.
*
Each audit option has two possible settings, WHENEVER SUCCESSFUL and
WHENEVER NOT SUCCESSFUL, separated by a slash (/). For example, the
DELETE audit
option for BHUVAN.emp is set by ACCESS for successful DELETE statements
and not set at all for unsuccessful DELETE statements.
#13
Oracle AUDITING VIEWS
DBA_FGA_AUDIT_TRAIL - display the captured audit info (FGA only (F GA_LOG$))
DBA_AUDIT_EXISTS - displays audit trail entries produced by AUDIT EXISTS & AUDIT NOT EXISTS.
DBA_AUDIT_OBJECT -- displays audit trail records for all objects in the database.
DBA_AUDIT_POLICIES -- identify FGA audit policies
DBA_AUDIT_POLICY_COLUMNS - Identify FGA audit policies for columns level in FGA
DBA_AUDIT_SESSION - display session level auditing information
DBA_AUDIT_STATEMENT - display statement level auditing information
DBA_AUDIT_TRAIL - display the captured audit info (Standard auditing only ( AUD$))
DBA_COMMON_AUDIT_TRAIL -- displays the captured audit info for standard & FGA
DBA_OBJ_AUDIT_OPTS -- display any object auditing
DBA_PRIV_AUDIT_OPTS -- display any privilege auditing
DBA_STMT_AUDIT_OPTS -- display any statement auditing
DBA_AUDIT_MGMT_CLEANUP_JOBS - Displays the currently configured audit trail purge jobs
DBA_AUDIT_MGMT_CLEAN_EVENTS -- Displays
the history of purge events. Periodically as user SYS connected with
the SYSDBA privilege, you should delete the contents of this view so
that it does not grow too large.
DBA_AUDIT_MGMT_CONFIG_PARAMS -- displays information about the currently configured audit trail properties that are used by the DBMS_AUDIT_MGMT PL/SQL package.
DBA_AUDIT_MGMT_LAST_ARCH_TS -- Displays the last archive timestamps that have set for audit trail purges.
V$XML_AUDIT_TRAIL -- If you are writing to an XML file, you can query the
#14
Operating System Audit Trail Records: Example
Audit trail:
LENGTH: "349"
SESSIONID:[5] "43464"
ENTRYID:[1] "1"
STATEMENT:[1] "1"
USERID:[6] "DBSNMP"
USERHOST:[7] "SHOBEEN"
TERMINAL:[3] "MAU"
ACTION:[3] "100"
RETURNCODE:[1] "0"
COMMENT$TEXT:[97] "Authenticated by: DATABASE; Client address:
(ADDRESS=(PROTOCOL=tcp)(HOST=192.0.2.4)(PORT=2955))"
OS$USERID:[19] "NT AUTHORITY\SYSTEM"
DBID:[10] "1212547373"
PRIV$USED:[1] "5"
In this example:
■ LENGTH
refers to the total number of bytes used in this audit record. This
number includes the trailing newline bytes (\n), if any, at the end of
the audit record.
■ [] brackets indicate the length of each value for each audit entry. For example, the USERID entry, DBSNMP, is 6 bytes long.
■ SESSIONID
indicates the audit session ID number. You can also find the session ID
by querying the AUDSID column in the V$SESSION data dictionary view.
■ ENTRYID
indicates the current audit entry number, assigned to each audit trail
record. The audit ENTRYID sequence number is shared between fine-grained
audit records and regular audit records.
■ STATEMENT
is a numeric ID assigned to the statement the user runs. It appears for
each statement issued during the user session, because a statement can
result in multiple audit records.
■ ACTION
is a numeric value representing the action the user performed. The
corresponding name of the action type is in the AUDIT_ACTIONS table. For
example, action 100 refers to LOGON.
■ RETURNCODE
indicates if the audited action was successful. 0 indicates success. If
the action fails, the return code lists the Oracle Database error
number. For example, if you try to drop a non-existent table, the error
number is ORA-00903 invalid table name, which in turn translates to 903
in the RETURNCODE setting.
■ COMMENT$TEXT
indicates additional comments about the audit record. For example, for
LOGON audit records, it can indicate the authentication method. It
corresponds to the COMENT_TEXT column of the DBA_COMMON_AUDIT_TRAIL data
dictionary view.
■ DBID is
a database identifier calculated when the database is created. It
corresponds to the DBID column of the V$DATABASE data dictionary view.
■ ECONTEXT_ID indicates the application execution context identifier.
■ PRIVS$USED
refers to the privilege that was used to perform an action. To find the
privilege, query the SYSTEM_PRIVILEGE_MAP table. For example, privilege
5 refers to -5 in this table, which means CREATE SESSION. PRIVS$USED
corresponds to the PRIV_USED column in the DBA_COMMON_AUDIT_TRAIL, which
lists the privilege by name. Other possible values are as follows:
■ SCN
(for example, SCN:8934328925) indicates the System Change Number (SCN).
Use this value if you want to perform a flashback query to find the
value of a setting (for example, a column) at a time in the past. For
example, to find the value of the ORDER_TOTAL column of the OE.ORDERS
table based on the SCN number, use the following SELECT statement:
SELECT ORDER_TOTAL FROM OE.ORDERS AS OF SCN = 8934328925
WHERE ORDER_TOTAL = 86;
■ SES_ACTIONS
indicates the actions that took place during the session. This field is
present only if the event was audited with the BY SESSION clause.
Because this field does not explain in
detail the actions that occurred during the session, you should
configure the audit event with the BY ACCESS clause. The SES_ACTIONS
field contains 16 characters. Positions 14, 15, and 16 are reserved for
future use. In the first 12 characters, each position indicates the
result of an action. They are: ALTER, AUDIT, COMMENT, DELETE, GRANT,
INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, and FLASHBACK. For example,
if the user had successfully run the ALTER statement, the SES_ACTIONS
setting is as follows: S--------------- The S, in the first position
(for ALTER), indicates success. Had the ALTER statement failed, the
letter F would have appeared in its place. If the action resulted in
both a success and failure, then the letter is B.
■ SES$TID indicates the ID of the object affected by the audited action.
■ SPARE2 indicates whether the user modified SYS.AUD$ table. 0 means the user modified SYS.AUD$; otherwise, the value is NULL.Some reference Sites :
http://www.oradba.ch/2011/05/database-audit-and-audit-trail-purging/
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4007.htm
Friday, November 14, 2014
ORA-31633: unable to create master table ".SYS_IMPORT_FULL_05"
Today I encountered a problem while importing a scehma into my local database. I have exported a schema from ORCL (lets say) using expdp command. I tried to import it to another database and I was getting this error.
ORA-31633: unable to create master table
[oracle@orcl dpump]$ impdp sam/oracle directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01031: insufficient privileges
I tried again and again same error. Then I checked may be it is missing some grants, I havegranted sysdba privillage to SAM now and tried.
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 14 14:02:27 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant sysdba to SAM;
I tried again.
[oracle@orcl dpump]$ impdp SAM/SAM directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01031: insufficient privileges.
Then I realized that it might be missing create table permission and granted that permission to user SAM.
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 14 14:02:27 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant create table to SAM;
Grant succeeded.
Now tried again :
[oracle@orcl dpump]$ impdp SAM/SAM directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Import: Release 11.2.0.4.0 - Production on Fri Nov 14 14:02:54 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SAM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SAM"."SYS_IMPORT_SCHEMA_01": sam/******** dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Possible Solutions :
It might be a simple missing of create table if you are trying to import it through a user.
Try to check if there is already a table with the name oracle is trying to create table.
Check if the job name being used is already created in database.
Sunday, October 26, 2014
Applying “version 4″ Time Zone Files on an Oracle Database
Applying “version 4″ Time Zone Files on an Oracle Database
Applying “version 4″ Time Zone Files on an Oracle Database if your timezone file version is less than 4Yesterday I was upgrading the database form 10.2.0.3 to 10.2.0.5 on RHEL 5,
In readme of the 10.2.0.5 patch i came across the timezoe files,
We need to upgarde the timezone files of the base database to 4, is minimum requirement.
I found my timezone files 3,
you can use following query to find the timezone of the database:
SQL> select * from v$timezone_file; FILENAME VERSION ------------ ---------- timezlrg.dat 3If your database versions are 9.2.0.8 & 10.2.0.4,your timezone file version will be 4 by default, upgrading to 11.1.0.6.0 or 11.2.0.1.0 we need timezone version 4, If your database is 9.2.0.7, we need to upgarde it to 9.2.0.8 or database versions are 10.2.0.1, 10.2.0.2, 10.2.0.3, then we need to upgarde the database to 10.2.0.4 then to upgrade the 11.2.0.1 Another & Best way is to apply the “version 4″ Time Zone Files on an Oracle Database you can file the patches for your version from the metalink ID ID 413671.1
Go to end of the article, Identify your version from the table, apply the patch,
your timezonefile version will be updated to 4, & you can proceed for the upgradation to 11g R1, 11gR2
Please refer the following link for applying OPATCH
http://deveshdba.wordpress.com/2011/09/14/applying-opatch/
Check your opatch utility working fine,
[oracle@primary OPatch]$ cd $ORACLE_HOME/OPatch [oracle@primary OPatch]$ ./opatch version Invoking OPatch 10.2.0.3.0 OPatch Version: 10.2.0.3.0 OPatch succeeded. [oracle@primary OPatch]$ ./opatch lsinventory Invoking OPatch 10.2.0.3.0 Oracle interim Patch Installer version 10.2.0.3.0 Copyright (c) 2005, Oracle Corporation. All rights reserved.. Oracle Home : /oracle/10201 Central Inventory : /oracle/oraInventory from : /etc/oraInst.loc OPatch version : 10.2.0.3.0 OUI version : 10.2.0.3.0 OUI location : /oracle/10201/oui Log file location : /oracle/10201/cfgtoollogs/opatch/opatch2011-07-07_12-08-57PM.log Lsinventory Output file location : /oracle/10201/cfgtoollogs/opatch/lsinv/lsinventory2011-07-07_12-08-57PM.txt -------------------------------------------------------------------------------- Installed Top-level Products (2): Oracle Database 10g 10.2.0.1.0 Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0 There are 2 products installed in this Oracle Home. Interim patches (2) : Patch 5556081 : applied on Thu Jul 07 11:20:32 GMT+05:30 2011 Created on 9 Nov 2006, 22:20:50 hrs PST8PDT Bugs fixed: 5556081 Patch 5557962 : applied on Thu Jul 07 11:20:24 GMT+05:30 2011 Created on 9 Nov 2006, 23:23:06 hrs PST8PDT Bugs fixed: 4269423, 5557962, 5528974 --------------------------------------------------------------------------------donwload the patch, unzip it, Follow the instructions in readme of the patch, & apply.
[oracle@primary OPatch]$ ./opatch apply Invoking OPatch 10.2.0.3.0 Oracle interim Patch Installer version 10.2.0.3.0 Copyright (c) 2005, Oracle Corporation. All rights reserved.. Oracle Home : /oracle/10201 Central Inventory : /oracle/oraInventory from : /etc/oraInst.loc OPatch version : 10.2.0.3.0 OUI version : 10.2.0.3.0 OUI location : /oracle/10201/oui Log file location : /oracle/10201/cfgtoollogs/opatch/opatch2011-07-07_12-13-56PM.log ApplySession applying interim patch '5632264' to OH '/oracle/10201' OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only. Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/oracle/10201') Is the local system ready for patching? Do you want to proceed? [y|n] Y User Responded with: Y Backing up files and inventory (not for auto-rollback) for the Oracle Home Backing up files affected by the patch '5632264' for restore. This might take a while... Backing up files affected by the patch '5632264' for rollback. This might take a while... Patching component oracle.oracore.rsf, 10.2.0.3.0... Copying file to "/oracle/10201/oracore/zoneinfo/timezone.dat" Copying file to "/oracle/10201/oracore/zoneinfo/timezlrg.dat" Copying file to "/oracle/10201/oracore/zoneinfo/readme.txt" ApplySession adding interim patch '5632264' to inventory Verifying the update... Inventory check OK: Patch ID 5632264 is registered in Oracle Home inventory with proper meta-data. Files check OK: Files from Patch ID 5632264 are present in Oracle Home. The local system has been patched and can be restarted.
restart the database & check the timezone file version,
SQL> startup ORACLE instance started. Total System Global Area 943718400 bytes Fixed Size 1265172 bytes Variable Size 591397356 bytes Database Buffers 348127232 bytes Redo Buffers 2928640 bytes Database mounted. Database opened. SQL> select * from v$timezone_file; FILENAME VERSION ------------ ---------- timezlrg.dat 4
Subscribe to:
Posts
(
Atom
)