12c: Transparent Sensitive Data Protection

TSDP is the another new security feature of Oracle 12c , basically a complement to Data Redaction and Virtual Private Database. It allows to categorize sensitive columns across database and then to set a policy to each defined category of columns for example telephone number columns' policy to redact into a random number.

The packages DBMS_TSDP_MANAGE and DBMS_ TSDP_PROTECT are used to manage the categories and policies.

12c: Data Redaction

Oracle Data Redaction  is the feature which enables to mask data at run time that is returned from queries issued by applications. During the time that the data is being masked or redacted, all of the data processing is performed normally, and the back-end referential integrity constraints are preserved. It is ideal for situations in which you must redact specific characters out of the result set of queries of Personally Identifiable Information (PII) returned to certain application users. 

12c: Using Transparent Data Encryption

Oracle Database uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system data files where data is stored. To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). TDE encrypts sensitive data stored in data files. To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database, called a keystore.

12c: Using In-Memory Column Store

The In-Memory Column Store (IM column store) is an optional portion of the system global area (SGA) that stores copies of tables, table partitions, and other database objects. In the IM column store, data is populated by column rather than row as it is in other parts of the SGA, and data is optimized for rapid scans, it does not replace the buffer cache, but acts as a supplement. The IM column store is included with the Oracle Database In-Memory option. To enable the IM column store, the INMEMORY_SIZE initialization parameter must be set to a non-zero value.

12c: Monitor SGA Memory on PDB

The CDB and all pluggable databases (PDBs) share a single database instance composed of the system global area (SGA) and background processes. Because of this sharing of memory resources, it would be desirable to distinguish resource usage among databases sharing the instance. You can use the below script to get it.

12c: Enterprise Manager Database Express

For Oracle Database 12c, Enterprise Manager Express enables you to perform administrative tasks such as managing user security and managing database memory and storage. You can also view performance and status information about your database. You can access Enterprise Manager Database Express using the URL that was displayed during installation. The default port used is 5011. In case you want to use a different port, you need to configure the port using the dynamic protocol registration method. After the HTTP port is configured, you use it to access Enterprise Manager Express.

12c: SYSBACKUP, SYSDG and SYSKM Administrative Users

In order to support segregation of duties requirements, Oracle 12c has introduced new administrative roles to conform to the principle of access to the least privilege. Three new user SYSBACKUP, SYSDG and SYSKM are created in support of this, when the database is created, with their account in the “EXPIRED & LOCKED” status. A equivalent administrative privilege with the same name as the user is created as well.

yum server configurations and oracle rpms installation

Configure yum server

1. copy dvd at /var/ftp/pub

2. rpm -ivh createrepo*

3. createrepo -v  /var/ftp/pub

4. service vsftpd restart

5. chkconfig vsftpd on

6. vim /etc/yum.conf

[local]

baseurl=ftp://localhost/pub

gpgcheck=0

 Install Required Linux Packages for Oracle RAC

yum install binutils elfutils-libelf glibc glibc-common libaio \

            libgcc libstdc++ make compat-libstdc++-33 elfutils-libelf-devel \

            glibc-headers glibc-devel libgomp gcc gcc-c++ libaio-devel \

            libstdc++-devel unixODBC unixODBC-devel sysstat libxp openmotif xorg-x11-libs-compat

Note: download xorg-x11-libs-compat exclusively for r12 installation, this pkg not availble in linux dvd

How to find Exadata database machine version

On any DB Node give the below command to know your Exadata version.

[root@pk3-iub-rp-od01 ~]# grep -i MACHINETYPES /opt/oracle.SupportTools/onecommand/databasemachine.xml

 X3-2 Quarter Rack HP

HP => High Performance

HC => High Capcity

Resetting unrecoverable_time in v$datafile

When you create a table or index as NOLOGGING, the database does not generate redo log records for the operation. Thus, you cannot recover objects created with NOLOGGING, even if you run in ARCHIVELOG mode. If you cannot afford to lose tables or indexes created with NOLOGGING, then make a backup after the unrecoverable table or index is created. 

Raw Device Setup for ASM

Scenario: 

Today one of the DBA requested to have the ASM instance based on raw devices. I provided the below  as a quick demo.

Using OCLUMON to analyze Cluster Health

Cluster Health Monitor & OCLUMON

The Cluster Health Monitor (CHM) stores real-time operating system metrics in the CHM repository that you can use for later triage with the help of Oracle Support should you have cluster issues.

It consists of System Monitor Service, Cluster Logger Service, CHM Repository

ASMCA tabs Volume and ASM Cluster File System are grayed out

Scenario:
DBA wanted to create the ACFS but got the Volume and Cluster File System grayed out.

Solution:

ORA-01427 While running DBMS_HM.RUN_CHECK

SQL> BEGIN
  2      DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'HC1');
  3  END;
  4  /
BEGIN
*

Configuring ACFS (Standalone Server) 11gR2

Scenerio:

Client has standalone GI installation  and wants to check the ACFS feature.

Brief:

Oracle ACFS is designed as a general-purpose, single-node and clusterwide file system that delivers support for customer file system application data maintained outside of Oracle Database. Users and applications can access and manage Oracle ACFS using native operating system file system application programming interfaces (APIs) and command-line interface (CLI) tools. Users can also manage Oracle ACFS with Oracle Enterprise Manager.

Configuring Oracle Grid Infrastructure for a standalone server (11gR2)

Scenario:

1- One of our DBA already had installed Oracle (11gR2 11.2.0.4) RDBMS on Linux 5.8

2- After some times, client requested to use the ASM as test to know its capabilities so DBA was given the task to have the GI installed and configured without disturbing the current environment.

Exadata: Defining the Threshold for Exadata Cell

In Exadata an alert is automatically triggered when a predefined hardware or software issue is detected, or when a metric exceeds a threshold. By default, there are no thresholds defined but you can define your own if you want.

Exadata: Index Elimination

Brief:

In some cases, Exadata Smart Scan may deliver better query performance than using an index. However, this may not always be the case. Even in cases where an index delivers better query performance you might choose to remove it if you determine that the un-indexed query performance is acceptable and the index is otherwise unnecessary. Removing unnecessary indexes saves space and improves DML performance by eliminating the maintenance operations associated with the index. 

Exadata: Using Hybrid Columnar Compression

Scenario:

We have a table TRANSACTION_LOG with more than 10 million rows , estimated size of table is 640MB. We want to  examine the performance of Exadata Hybrid Columnar Compression and want compare predicted and actual compression ratios using an example dataset, so that we can use HCC for our logging tables. We also want to examine how bulk data loading and query operations are affected using the different compression modes.

Exadata: Using Integrated Lights Out Manager (ILOM) CLI

What is ILOM?

Oracle ILOM is integrated service processor hardware and software that is preinstalled on Oracle servers, including the storage and database servers in Oracle Exadata. The service processor runs its own embedded operating system and has a dedicated Ethernet port to provide out-of-band server monitoring and management capabilities. Oracle ILOM can be accessed via a browser-based web interface or a command-line interface, and it also provides an SNMP interface and IPMI support.