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”

468 total views, 10 views today

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


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”

5,120 total views, 15 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,985 total views, 25 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,327 total views, 6 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,599 total views, 12 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,541 total views, 4 views today

Tags: Database Administration, Oracle Administration


Jan 10 2017

How to Create Linked Server From SQL Server To SQL Server Database

Category: AdministrationFatih Acar @ 15:58

You can connect another SQL Server database or different type database from SQL Server database with Linked Server feature. You can use below operations to create new linked server for SQL Server database to SQL Server database.

Create Linked Server Steps

  1. Create a user on remote database to use connection and give permission on which database using.
  2. SQL Server Management Studio with sysadmin privilege.
  3. Open SQL Server Management Studio with sysadmin privilege.
  4. Click New Linked Server below Server Objects > Linked Servers
  5. General Section > Select SQL Server as Server type.
  6. Write remote Instance Name to Linked Server text box.
  7. Security Section > Add > write Local and Remote User information for connection, and OK.
  8. You can see added new Linked Server below Linked Server section

1,525 total views, no views today

Tags: Database Administration, MsSQL Server, SQL Server Administration


Oct 17 2016

Obtain Surname, Last Name and Mid Name From Full Name in Oracle

Category: Procedure,SQLFatih Acar @ 13:43

If you have a column with full name data and you want to split this data, you can use below function to split operation.

Split Name ve Surname

Table : test
Data1 : Emin Fatih Acar
Data2 : Fatih Acar

SQL> select fullname,
substr(fullname,0,instr(fullname,’ ‘,Instr(fullname,’ ‘,-1))-1) name,
substr(fullname,Instr(fullname,’ ‘,-1)+1) surname
from test;

Result:

Data1
name : Emin Fatih
surname : Acar

Data2
name : Fatih
surname : Acar

Split Name,Midname and Surname

SQL> select fullname,
substr(fullname,0,instr(fullname,’ ‘)-1) name,
substr(fullname,instr(fullname,’ ‘)+1,Instr(fullname,’ ‘,-1,1)- instr(fullname,’ ‘)-1) midname,
substr(fullname,Instr(fullname,’ ‘,-1)+1) surname,
from test;

Result:

Data1
name : Emin
midname : Fatih
surname : Acar

Data2

name : Fatih
midname : null
surname : Acar

3,641 total views, no views today

Tags: Database Administration, Oracle Administration, Oracle SQL Query


Oct 11 2016

SQL Server 2012 AlwaysOn Availability Groups Insallation and Configuration

Category: AdministrationFatih Acar @ 09:00

Always On Availability Group is a new feature in SQL Server Database with 2012 release. Before this feature, we was using database mirroring feature in SQL Server 2005. But, mirroring has some limitations and weaknesses. and mirroring feature will be disabled near future.

AlwaysOn Availability Groups Features

  • Multi or Single Database Failovers
  • Multiple Secondaries
  • Active (Readably) Secondaries
  • Integrated HA Management

sqlserveravailabilitygroup

System Information

OS: Windows 2012 Server
DB: SQL Server 2012
Side 1: TESTDB1
Side 2: TESTDB2
Failover Cluster Name: TESTDB
Availability Group Name: TESTDB_AG
Listener Name: TESTDBVIP
Listener Port: 1433

AlwaysOn Availability Groups Installation Steps
Continue reading “SQL Server 2012 AlwaysOn Availability Groups Insallation and Configuration”

2,631 total views, 8 views today

Tags: Database Administration, MsSQL Server, SQL Server Administration, SQL Server Availability Group


Sep 27 2016

Keep Table in Buffer Pool Oracle 11g

Category: AdministrationFatih Acar @ 09:35

Oracle provides a feature to keep objects in the Buffer pool. This feature allows you to keep the frequently accessed table in memory itself which will provide the faster access of the table. The object will be kept under the KEEP pool and the purpose of the keep pool is to accommodate / cache the small objects like look up tables.

In general the server process has to bring data blocks for the table from the disk to the memory (buffer pool) which requires an overhead. Moreover the object gets removed from the memory whenever the object is not getting used or oracle needs room for another table in the buffer pool. If you keep the frequently accessed objects in the memory the above mentioned overhead can be reduced.

Keep Buffer is also working in First In First Out mode. If Oracle has more objects to keep in the KEEP buffer, automatically it removes the older objects from the keep buffer.  DB_KEEP_CACHE_SIZE is the parameter which determines the size of the Keep Buffer Pool. If this parameter is 0, Oracle adjust automatically size.

Keep Table in Buffer Pool

SQL> alter table testtable storage (buffer_pool keep);

Table altered.

Continue reading “Keep Table in Buffer Pool Oracle 11g”

5,266 total views, no views today

Tags: Database Administration, Oracle Administration


Next Page »