Oct 31 2017

Unified Auditing in Oracle 12c

Category: Administration,Database SecurityFatih Acar @ 10:30

Unified Auditing is new audit feature came with Oracle 12c version. You have to do enable Unified Auditing to use after install database. Unified Auditing come as disabled by default.

In previous releases of Oracle Database, there were separate audit trails for individual components:

  • SYS.AUD$ for the database audit trail,
  • SYS.FGA_LOG$ for fine-grained auditing,
  • DVSYS.AUDIT_TRAIL$ for Oracle Database Vault, Oracle Label Security, and so on.

In 12c, these audit trails are all unified into one, viewable from the UNIFIED_AUDIT_TRAIL data dictionary view for single-instance installations or Oracle Database Real Application Clusters environments.

Auditable Components With Unified Auditing

  • Audit Any Role
  • Application Context Values
  • Oracle Database Real Application Security Events
  • Oracle Recovery Manager Events
  • Oracle Database Vault Events
  • Oracle Label Security Events
  • Oracle Data Mining Events
  • Oracle Data Pump Events
  • Oracle SQL*Loader Direct Load Path Events
  • Operating System Audit Records into the Unified Audit Trail

The unified audit trail, which resides in a read-only table in the AUDSYS schema in the SYSAUX tablespace, makes this information available in a uniform format in the UNIFIED_AUDIT_TRAIL data dictionary view, and is available in both single-instance and Oracle Database Real Application Clusters environments. In addition to the user SYS, users who have been granted the AUDIT_ADMIN and AUDIT_VIEWER roles can query these views. If your users only need to query the views but not create audit policies, then grant them the AUDIT_VIEWER role.

When the database is writeable, audit records are written to the unified audit trail. If the database is not writable, then audit records are written to new format operating system files in the $ORACLE_BASE/audit/$ORACLE_SID directory.

You can use mixed mode auditing enables both traditional (that is, the audit facility from releases earlier than Release 12c) and the new audit facilities (unified auditing). In mixed mode, you can use the new unified audit facility alongside the traditional auditing facility. In pure unified auditing, you only use the unified audit facility.

As in previous releases, the traditional audit facility is driven by the AUDIT_TRAIL initialization parameter. Only for mixed mode auditing, you should set this parameter to the appropriate traditional audit trail. This traditional audit trail will then be populated with audit records, along with the unified audit trail. When you upgrade your database to the current release, traditional auditing is preserved, and the new audit records are written to the traditional audit trail.

Enable Unified Auditing

You can check current status with below query.


SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER='Unified Auditing';

If VALUE is TRUE, Unified Auditing is enabled.

You have to shutdown all running database process (database,listener) to activate Unified Auditing before run script. If you use Oracle RAC, you have to run script on all of nodes.


SQL> shutdown immediate;
SQL> exit;

[oracle@testdb ~]$ lsnrctl stop LISTENER

[oracle@testdb ~]$ cd $ORACLE_HOME/rdbms/lib/
[oracle@testdb lib]$ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME  

SQL> startup;
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER='Unified Auditing'; 

The VALUE has to be TRUE.

Continue reading “Unified Auditing in Oracle 12c”

336 total views, no views today

Tags: Database Administration, Database Security, Oracle 12c, Oracle 12c Security


Oct 12 2017

Oracle 12c R2 (12.2.0.1) Real Time Apply Data Guard Installation on Oracle Linux 7.3

Category: Administration,Backup And RecoveryFatih Acar @ 11:11

Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Oracle Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Oracle Data Guard maintains these standby databases as copies of the production database.Then, if the production database becomes unavailable because of a planned or an unplanned outage, Oracle Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Oracle Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.

With Oracle Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.

Types of standby databases are Physical standby database, Logical standby database and Snapshot standby database.

I will demonstrate Physical standby database as working real time apply at this document. Most used type is Physical standby database type. You can investigate other types of standby database from Oracle docs.

I used Oracle RAC database as primary side and I used single instance database with asm file system as secondary side (Data Guard).

Firstly, you have to adjust system parameters of operating system on secondary side and create asm disks. After, you can start to install of grid infrastructure. You can create disk groups for DATA and FRA disk groups after install grid. After grid installation, you can install Oracle Data Guard database on grid infrastructure and ASM disks as software only. Finally you can restore and recover standby database from primary side and you can start synchronization apply after add standby redo log. I divide the stages of installation five steps.

You can find primary side (Oracle 12c RAC) installation steps here. I will use this infrastructure as primary side.

First Step : Configure Operation System on Secondery Side

1. Upgrade Packages

yum upgrade

yum install oracleasm-support

Continue reading “Oracle 12c R2 (12.2.0.1) Real Time Apply Data Guard Installation on Oracle Linux 7.3”

534 total views, 2 views today

Tags: Oracle 12c, Oracle Administration, Oracle Backup and Restore, Oracle Data Guard


Jul 27 2017

Oracle 12c R2 (12.2.0.1) RAC Installation Steps on Oracle Linux 7.3

Category: AdministrationFatih Acar @ 14:37

Oracle Real Application Clusters (Oracle RAC) is a clustered database infrastructure of Oracle Database based on a comprehensive high-availability stack that can be used as the foundation of a database cloud system as well as a shared infrastructure, ensuring high availability, scalability, and agility for any application. Oracle Real Application Cluster has been using with Oracle 9i version since in 2001. This feature provides software for clustering and high availability in Oracle database environments.

Top Benefits of Real Application Clusters (RAC)

  • Ability to spread CPU load across multiple servers
  • Continuous Availability / High Availability (HA)

– Protection from single instance failures

– Protection from single server failures

  • RAC can take advantage of larger SGA sizes than can be accommodated by a single instance commodity server
  • Scalability

 

 

 

Oracle RAC installation steps are a bit long. Firstly, you have to adjust system parameters of operating system on both node and create asm disks. After, you can start to install of grid infrastructure. You can create disk groups for DATA and FRA disk groups after install grid. Finally you can install Oracle RAC database on grid infrastructure and ASM disks. I divide the stages of installation four steps.

First Step : Configure Operation System

Continue reading “Oracle 12c R2 (12.2.0.1) RAC Installation Steps on Oracle Linux 7.3”

4,612 total views, 22 views today


Mar 28 2017

Oracle 11g R2 Resize Redo Log Size on Data Guard and Primary Database

Category: Administration,Backup And Recovery,Errors and SolutionsFatih Acar @ 15:07

Sometimes, you may need to change redo log size to perform optimum size for performance. If you want to change redo log size of both standby side and primary side, you can use below commands.

Current Status Of Redo Logs

Primary Side


SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
 
GROUP#     Size in MB
-------    ----------
1          50
2          50
3          50
 
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
 
GROUP#   Size in MB
-------  ----------
 4       50
 5       50
 6       50
 7       50

Standby Side



SQL> select group#, sum(bytes/1024/1024)"Size in MB" from v$loggroup by group#;
 
GROUP# Size in MB
------ -------------
 1     50
 2     50
 3     50
 
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
 
GROUP# Size in MB
------ ----------
 4     50
 5     50
 6     50
 7     50

Change Size Of Redo Logs

Auto File Management Disable on Standby Side


SQL> alter system set standby_file_management=manual

System altered.

Change Redo Log Size Of Primary Side
Continue reading “Oracle 11g R2 Resize Redo Log Size on Data Guard and Primary Database”

4,730 total views, no views today

Tags: Database Administration, Oracle Administration


Mar 28 2017

Oracle 11g R2 Open Data Guard Read Write Mode With Flashback Technology And Revert Restore Point

Category: Administration,Backup And RecoveryFatih Acar @ 13:34

You can use flashback technology on standby side. If you want to make application test on database, you can use data guard as read write mode temporarily with flashback technology. You can create a restore point on standby side then you can do any transaction on standby side to test or any other purpose. After you complete your operations on the standby side, you can revert to restore point and again connect to primary side and apply logs from primary side.

This feature can be use for application test operations.

Perform Flashback Steps

Standby Side


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database flashback on;

Database altered.

Note : Check recovery destination and size. You have to have recovery dest and size.

SQL> show parameter recovery

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /oracle/fra
db_recovery_file_dest_size	     big integer 10G
recovery_parallelism		     integer	 0

SQL> create restore point before_test guarantee flashback database;

Restore point created.

Primary Side


SQL> alter system archive log current;

System altered.

Note : Archive dest for standby log sync parameter set defer.

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

Continue reading “Oracle 11g R2 Open Data Guard Read Write Mode With Flashback Technology And Revert Restore Point”

3,795 total views, no views today

Tags: Database Administration, Oracle Administration, Oracle Backup and Restore, Oracle Data Guard


Mar 28 2017

Oracle 11g R2 Data Guard Manual Switchover Steps

Category: Administration,Backup And RecoveryFatih Acar @ 09:47

You can use the same steps to switchover for single data guard or multiple data guard configuration.

Switchover operation will convert primary side to data guard and data guard to primary side.

You have to control listeners whether it is running before switchover steps.

Switchover Steps

Primary Side


SQL> alter system archive log current;

SQL> alter database commit to switchover to standby with session shutdown;

SQL> shutdown immediate;

SQL> startup mount;

Data Guard Side
Continue reading “Oracle 11g R2 Data Guard Manual Switchover Steps”

2,233 total views, no views today

Tags: Database Administration, Oracle Administration, Oracle Data Guard, Oracle Data Guard Switchover


Mar 27 2017

Oracle 11g R2 Multi Real Time Apply Data Guard Configuration Steps

Category: Administration,Backup And RecoveryFatih Acar @ 16:42

Oracle 11g R2 database supports multi standby database structure up to 30 standby databases. We can use remote destinations for real time apply standby structures with todays network technologies. If your remote destination network is not good, you can use archivelog apply for remote destination with some delay.

System informations are like below.

System Informations

PRIMARY DATABASE
VERSION : 11.2.0.3
IP : 192.168.9.129
SID : PRI
HOSTNAME : primary.localdomain

FIRST STANDBY DATABASE
VERSION : 11.2.0.3
IP : 192.168.9.130
SID : DR1
HOSTNAME : dr1.localdomain

SECOND STANDBY DATABASE
VERSION : 11.2.0.3
IP : 192.168.9.131
SID : DR2
HOSTNAME : dr2.localdomain
Continue reading “Oracle 11g R2 Multi Real Time Apply Data Guard Configuration Steps”

1,503 total views, no views today

Tags: Database Administration, Oracle Administration


Mar 21 2017

Oracle 11g R2 Cascade Standby Data Guard Installation and Configuration Steps

Category: Administration,Backup And RecoveryFatih Acar @ 16:32

Oracle 11g R2 database supports cascade standby database structure. If you are using RAC on primary side, your database version has to be least 11.2.0.2 to support cascade standby structure. You can use to reduce the load on your primary database with using cascade standby database structure.

Primary database redo is written to the standby redo log as it is received at a cascading standby database. The redo is not immediately cascaded however. It is cascaded after the standby redo log file that it was written to has been archived locally. A cascaded destination will therefore always have a greater redo transport lag, with respect to the primary database, than the cascading standby database.

Restrictions

  • Cascading Structure is not supported by Data Guard Broker.
  • To use Oracle RAC on primary side, you have to use least 11.2.0.2 verion.
  • Cascaded standby database do not support Real Time Apply

I will configure cascade standby database structure like below schema.

System Informations

PRIMARY DATABASE
VERSION : 11.2.0.3
IP : 192.168.9.129
SID : PRI
HOSTNAME : primary.localdomain

Continue reading “Oracle 11g R2 Cascade Standby Data Guard Installation and Configuration Steps”

1,487 total views, no views today

Tags: Database Administration, Oracle Administration


Mar 03 2017

Oracle 12c R2 Downloadable Now

Category: AdministrationFatih Acar @ 16:27

You can download Oracle 12c R2 database now. Only Linux x64 and Solaris Operation Systems supported for now.

You can download with below link;

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Also you can find new features about of Oracle 12c R2 with below links;

https://docs.oracle.com/database/122/

https://blogs.oracle.com/sql/entry/12_things_developers_will_love

1,499 total views, 4 views today

Tags: Oracle 12c, Oracle Administration


Oct 12 2016

Oracle 11g Oracle Checkpoint Not Complete, Cannot Allocate New Log Warning

Category: Administration,Errors and SolutionsFatih Acar @ 11:54

Error: Checkpoint not complete, Cannot Allocate New Log Warning

Thread 1 cannot allocate new log, sequence 125487
Checkpoint not complete

Solution: Checkpoint not complete messages are generated due to the logs are switching so fast that the checkpoint associated with the log switch isn’t complete. You should increase redo log file size and amount to resolve. Also, If you use archive_lag_target parameter as near zero for example like 1 or 2 minutes, you should change this parameter zero (no lag) or more than 10-15 min. Oracle recommends that redo log switch operation interval should be between 15-30 minutes.

oracle_redo_log_files

Show and change archive_lag_target parameter

SQL> show parameter archive_lag_target;
SQL> alter system set archive_lag_target=0 scope=both;
or
SQL> alter system set archive_lag_target=1800 scope=both;
1800 is 15 minutes. Parameter value as second.

If your archive_lag_target parameter is normal and you get checkpoint not complete error, you have to look your redo log file size and amount.
Continue reading “Oracle 11g Oracle Checkpoint Not Complete, Cannot Allocate New Log Warning”

5,611 total views, 6 views today

Tags: Oracle Error Solutions


Next Page »