Difference Between oracle 10g,12C & 18C

Database Compersion
Oracle10g	Oracle 12C	Oracle18C
Data Guard improvements	DataGuard enhancements	Active DataGuard added
Realtime apply and log compressions	Detection of gasps and automatic resolution	Standby Database can now be queried where redo apply is active
Backup and Recovery	Backup and Recovery	Improved Manageability
.    Data Pump Added	Block level recovery	ASM Cluster FileSystem (ACFS) Introduced
	Improved reporting and automation	Virtual Columns added
Security	Security	Security
Password is not case sensitive	Password case sensitive	Password  complexity
	Improved reporting and automation
	Oracle 12C supports In-memory aggregation, which improves of queries and  Reduce the CPU Usage	In-Memory in Oracle Database 18c also allows you to place data from external tables in the column store. Also It can now scan compression units in-parallel to double the speed of data read
single index	Multiple indexes	Virtual Indexes
Table space Rename	Move Datafile
Need Database Shutdown to change archive log mode	No need to shutdown database for changing archive log mode.
	Oracle 12c introduced a lot of online partition features	Oracle 18c, we can MERGE PARTITIONS online and maintain the indexes.
Not Possible	-	Oracle 18c users to create temporary database objects that are automatically dropped at the end of a transaction or a session.
	Optimized more database .We can add other application also there which will save licence cost of db server and system cost.	Optimized more database .We can add other application also there which will save licence cost of db server and system cost

What is meaning of i, g and c in Oracle Database Version

What is meaning of i, g and c in Oracle Database Version

Oracle DBAs and Developers .. ever wondered the meaning of i, g and c in Oracle Database Releases version .So I try to explain you in simple language.

Oracle 8i, 9i

The ‘i’ in oracle 8i and 9i stands for INTERNET.Starting in 1999 with Version 8i, Oracle added the "i" to the version name to reflect support for the Internet with its built-in Java Virtual Machine (JVM). Oracle 9i added more support for XML in 2001.

 Oracle 10g, 11g

10g and 11g stands for GRID.Starting in 2003 with version 10g and 11g, G signifies “Grid Computing” with the release of Oracle10g in 2003. Oracle 10g was introduced with emphasis on the “g” for grid computing, which enables clusters of low-cost, industry standard servers to be treated as a single unit. 

 Oracle 12c

In the Oracle Database 12c the "c" stands for "Cloud”.In addition to many new features, this new version of the Oracle Database implements a multitenant architecture, which enables the creation of pluggable databases (PDBs) in a multitenant container database (CDB)

So I hope you all now know what is the meaning of “i”,”c” and “g” in Oracle Database.

Audit - Enabling or Disabling Audit Trail

Auditing in Oracle Database

The Oracle Server provides several auditing options.

The following three types of audits are provide by Oracle

 1. Session audits (LOGON,LOGOFF etc)

 2. Database action and object audits and

 3. DDL(CREATE, ALTER & DROP of objects)

The three main views to see the AUDIT Information are:

·         DBA_AUDIT_TRAIL – Standard auditing only (from AUD$).

·         DBA_FGA_AUDIT_TRAIL – Fine-grained auditing only (from FGA_LOG$) [For 10g].

·         DBA_COMMON_AUDIT_TRAIL – Both standard and fine-grained auditing   [For 10g].

To enable database auditing, you must provide a value for the AUDIT_TRAIL parameter.

Note - Auditing is disabled by default, but can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.

SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN

                                                 \ORCL\ADUMP

audit_sys_operations                 boolean     FALSE

audit_trail                          string      DB

The initialization parameters of audit facility of Oracle

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

DB              Auditing is enabled. Audit records will be written to the

                SYS.AUD$ table.

OS              Auditing is enabled. Audit records will be written to an

                audit trail in the operating system.

db,extended     As db, but the SQL_BIND and SQL_TEXT columns are also populated.

NONE            Auditing is disabled (default).

xml-            Auditing is enabled, with all audit records stored

                as XML format OS files.

xml,extended    As xml, but the SQL_BIND and SQL_TEXT columns are also populated.

TRUE            This value is supported for backward-compatibility

                with versions of Oracle;it is equivalent to the DB value.

FALSE           This value is supported for backward-compatibility

                with versions of Oracle;it is equivalent to the NONE value.

In Oracle 10g Release 1, db_extended was used in place of db,extended. The XML options are new to Oracle 10g Release 2

Set audit_trail to DB in pfile (audit_trail = DB) .

Enable auditing and direct audit records to the database audit trail

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250452 bytes

Variable Size             230689644 bytes

Database Buffers          377487360 bytes

Redo Buffers                2940928 bytes

Database mounted.

The command to begin auditing connects (login) attempts is:

AUDIT SESSION;

AUDIT SESSION WHENEVER SUCCESSFUL;

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

To view the report of Audit session run the following query.

SQL Code: 

SELECT os_username,

     username,

     terminal,

     returncode,

     TO_CHAR(timestamp,   'DD-MON-YYYY HH24:MI:SS') LOGON_TIME,

     TO_CHAR(logoff_time, 'DD-MON-YYYY HH24:MI:SS') LOGOFF_TIME

FROM dba_audit_session;

Disable Session Audit

SQL> NOAUDIT;
SQL> NOAUDIT session;
SQL> NOAUDIT session BY scott, hr;
SQL> NOAUDIT DELETE ON emp;
SQL> NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
SQL> NOAUDIT ALL;
SQL> NOAUDIT ALL PRIVILEGES;
SQL> NOAUDIT ALL ON DEFAULT;

How to check the progress of statistics gathering on a table

-- Script_name          : sid_long_ops.sql
-- Description          : list the sid details for long running session like when it started when last update how much time still left.

SELECT 
opname
target,
ROUND( ( sofar / totalwork ), 4 ) * 100 Percentage_Complete,
start_time,
CEIL( time_remaining / 60 ) Max_Time_Remaining_In_Min,
FLOOR( elapsed_seconds / 60 ) Time_Spent_In_Min,
AR.sql_fulltext,
AR.parsing_schema_name,
AR.module Client_Tool
FROM v$session_longops L, v$sqlarea AR
WHERE L.sql_id = AR.sql_id
AND totalwork > 0
AND AR.users_executing > 0

AND sofar != totalwork;

ORA-39166: Object SYS.AUD$ was not found

ORA-39166: Object SYS.AUD$ was not found

When trying to backup the SYS.AUD$ table using datapump for my oracle 11g database, this is what i'm getting below

[oracle@oracle ~]$ expdp directory=EXPDR dumpfile=SYS_AUD_table.dmp logfile=exp_SYS_AUD_table.log tables=AUD$ exclude=statistics

Export: Release 11.2.0.3.0 - Production on Fri Nov 16 15:31:15 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA directory=EXPDR dumpfile=SYS_AUD_table.dmp logfile=exp_SYS_AUD_table.log tables=AUD$ exclude=statistics

Estimate in progress using BLOCKS method...

Total estimation using BLOCKS method: 0 KB

ORA-39166: Object SYS.AUD$ was not found.

ORA-31655: no data or metadata objects selected for job

Job "SYS"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at 15:31:18

Cause:

According to Oracle there is a restriction on dataPump export. It cannot export schemas like SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP and WMSYS in any mode.

Solution:

Export the table SYS.AUD$ using the traditional export:

[oracle@oracle ~]$ exp file=SYS_AUD_table.dmp log=exp_SYS_AUD_table.log tables=AUD$ statistics=none

Export: Release 11.2.0.3.0 - Production on Fri Nov 16 16:24:40 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P15 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table                           AUD$  3123792 rows exported

Export terminated successfully without warnings.