Header Image - Oracle Certified Master 11g Exam Study Guide

Category Archives

14 Articles

OCM tips – Implement securefile LOB

Reference:
Master Note: Overview of Oracle Large Objects (BasicFiles LOBs and SecureFiles LOBs) (Doc ID 1490228.1)
Application Development -> Database SecureFiles and Large Objects Developer’s Guide -> 4 Using Oracle SecureFiles LOBs

In this section we will talk about SecureFile LOB (Large Object) segments. Detailed information about creating and managing LOB segments is provided in the next chapter.

SecureFiles is a LOB storage architecture that is used to store unstructured data and contains numerous features as deduplication, compression, encryption and etc. If you want to store a LOB as a SecureFile, use STORE AS SECUREFILE clause in the CREATE TABLE statement. If you omit this clause, the LOB will be stored in a regular traditional way which is called BasicFile.

A SecureFile LOB can be created only in a tablespace managed with Automatic Segment Space Management (ASSM). The following error will appear if you attempt to create a SecureFile LOB in a non-ASSM tablespace:

ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace “SYSTEM”

DB_SECUREFILE parameter is used to control the action of the database with regards to the LOB storage. There are four values for this parameter:

– NEVER is used to make all LOBs that are created as a SecureFile to be created as a BasicFile LOB.
– PERMITTED is a default value that allows LOBs to be created as a SecureFile using STORE AS SECUREFILE clause.
– ALWAYS is used to make all LOBs in a ASSM tablespace be created as a SecureFile LOBs.
– IGNORE is used to ignore store as securefile clause and creation of SecureFile LOBs are permitted.

Note: Make sure the value of parameter DB_SECUREFILE is not set to NEVER and IGNORE if you are asked to create a SecureFile LOB during an exam.

Let’s create a simple table with a SecureFile LOB segment. First, create a table with BasicFile LOB as follows:

SQL> CREATE TABLE tbl_basicfile_lob
2 (id number,
3 lob_data clob)
4 LOB (lob_data) STORE AS BASICFILE;
Table created.
 
SQL> INSERT INTO tbl_basicfile_lob VALUES(1,’This is my long text’);
1 row created.
 
SQL> COMMIT;
Commit complete.
 
SQL>

Now create a table with SecureFile LOB data as follows:

SQL> CREATE TABLE tbl_securefile_lob (
2 id number, lob_data clob)
3 LOB (lob_data) STORE AS SECUREFILE (tablespace users);
Table created.
 
SQL> INSERT INTO tbl_securefile_lob VALUES(1,’This is my long text’);
1 row created.
 
SQL> COMMIT;
Commit complete.
SQL>

Query DBA_LOBS view to get more information about the lob segments:

SQL> SET LINESIZE 150
SQL> COL column_name FORMAT a10
SQL> SELECT table_name, column_name, segment_name, securefile FROM dba_lobs where table_name like ‘TBL%’;
 
TABLE_NAME COLUMN_NAM SEGMENT_NAME SEC
—————————— ———- —————————— —
TBL_BASICFILE_LOB LOB_DATA SYS_LOB0000096459C00002$$ NO
TBL_SECUREFILE_LOB LOB_DATA SYS_LOB0000096464C00002$$ YES
SQL>

If the LOB segment is stored as a SecureFile, you will be able to use the following features.

  • Compression
  • Encryption
  • Deduplication
  • Caching
  • Logging

In this newsletter, we will cover the compression feature

Compression
This feature is used to compress the LOB segment. A table compression doesn’t affect the LOB compression. It means that if the table is compressed, the LOB segment will not be compressed automatically. Oracle has two levels of compression, i.e 11gR1 – MEDIUM and HIGH. (a new LOW compression level is added at 11gR2). Check the following example to see how to compress the LOB segment and appreciate the difference between compressed
and noncompressed LOBs.

Create a table with non-compressed LOB segment and insert some data:

SQL> CREATE TABLE tbl_lob_nocompress
(
id NUMBER,
lob_data CLOB
)
LOB (lob_data) STORE AS SECUREFILE
(TABLESPACE users NOCOMPRESS);
Table created.
 
SQL> INSERT INTO tbl_lob_nocompress
SELECT object_id, RPAD (‘ ‘, 5000, object_name) FROM dba_objects
WHERE ROWNUM < 500;
499 rows created.

Now create a table with high compressed lob segment, insert some data and gather statistics from both tables:

SQL> CREATE TABLE tbl_lob_compress_high
(
id NUMBER,
lob_data CLOB
)
LOB (lob_data) STORE AS SECUREFILE
(TABLESPACE users COMPRESS HIGH);
 
Table created.
SQL> INSERT INTO TBL_LOB_COMPRESS_HIGH
SELECT object_id, RPAD (‘ ‘, 5000, object_name) FROM dba_objects
WHERE ROWNUM < 500;
499 rows created.
 
SQL> EXEC DBMS_STATS.gather_table_stats(USER,’TBL_LOB_NOCOMPRESS’);
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.gather_table_stats(USER,’TBL_LOB_COMPRESS_HIGH’);
PL/SQL procedure successfully completed.

Query DBA_LOBS view to find out the status of compression:

SQL> SELECT table_name, segment_name, securefile, compression
FROM dba_lobs
WHERE table_name LIKE ‘%COMPRESS%’;
TABLE_NAME SEGMENT_NAME SEC COMPRE
—————————— —————————— — ——
TBL_LOB_NOCOMPRESS SYS_LOB0000096719C00002$$ YES NO
TBL_LOB_COMPRESS_HIGH SYS_LOB0000096722C00002$$ YES HIGH

Now query USER_SEGMENTS view to see the size difference of compressed and noncompressed lob segments:

SQL> COLUMN segment_name FORMAT A30
SELECT segment_name, bytes
FROM user_segments
WHERE segment_name IN (‘SYS_LOB0000096719C00002$$’,’SYS_LOB0000096722C00002$$’);
 
SEGMENT_NAME BYTES
—————————— ———-
SYS_LOB0000096719C00002$$ 5373952
SYS_LOB0000096722C00002$$ 131072
SQL>

In the next newsletter, we will cover the rest SecureLob features.

If you want to get more information on managing SecureLobs, get my book from the following link:
https://www.amazon.com/Oracle-Certified-Master-Exam-Guide/dp/1536800791/ 

OCM tips – Migration of LOBs to SecureFiles

Reference:

HOW TO ONLINE CONVERT TO/FROM SECUREFILES FROM/TO BASICFILES (Doc ID 728758.1)

During an OCM exam you might be asked to convert a BasicFiles LOB to SecureFiles LOB. To perform this operation, you should use online redefinition feature. More detailed information about this feature was provided in the previous chapter. Check “Using Online Redefinition for Migrating Tables with BasicFiles LOBs” part of the documentation “Database SecureFiles and Large Objects Developer’s Guide – Chapter 4” to see an example related on how to migrate from BasicFiles to Securefiles LOB. Below, we will provide a simple example.

– Create a table with BasicFiles LOB column:

SQL> CREATE TABLE DBSNMP.tbl_basic_lob

(

  id NUMBER,

  clob_data CLOB

)

TABLESPACE users;

Table created.

SQL>

 

– Insert a few rows in it as we did in the previous example:

 

SQL> INSERT INTO DBSNMP.tbl_basic_lob

SELECT object_id, RPAD (‘ ‘, 5000, ‘This is test ‘)  FROM dba_objects WHERE ROWNUM < 3000;

2999 rows created.

SQL>

 

The following error will appear if you try to use SecureFiles LOB features on this table:

 

SQL>ALTER TABLE DBSNMP.tbl_basic_lob MODIFY LOB (clob_data) (CACHE DEDUPLICATE);

ALTER TABLE DBSNMP.tbl_basic_lob MODIFY LOB (clob_data) (CACHE DEDUPLICATE)

*

ERROR at line 1:

ORA-43856: Unsupported LOB type for SECUREFILE LOB operation

 

Create a table with SecureFiles LOB option which will be used as in interim table:

 

SQL> CREATE TABLE DBSNMP.tbl_securefiles_lob

(

id NUMBER,

clob_data CLOB

)

LOB (clob_data) STORE AS SECUREFILE (TABLESPACE users);

Table created.

SQL>

 

Start the redefinition using DBMS_REDEFINITION.START_REDEF_TABLE procedure:

 

SQL> EXEC dbms_redefinition.start_redef_table(‘DBSNMP’, ‘TBL_BASIC_LOB’, ‘TBL_SECUREFILES_LOB’,null,2);

PL/SQL procedure successfully completed.

 

Clone the dependent objects using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS procedure:

 

SQL> DECLARE

error_count PLS_INTEGER := 0;

BEGIN

DBMS_REDEFINITION.copy_table_dependents (‘DBSNMP’, ‘TBL_BASIC_LOB’, ‘TBL_SECUREFILES_LOB’,

1, TRUE, TRUE, TRUE, FALSE, error_count);

DBMS_OUTPUT.put_line (‘errors := ‘ || TO_CHAR (error_count));

END;

/

PL/SQL procedure successfully completed.

 

Complete the redefinition process:

 

SQL> EXEC dbms_redefinition.finish_redef_table(‘DBSNMP’, ‘TBL_BASIC_LOB’, ‘TBL_SECUREFILES_LOB’);

PL/SQL procedure successfully completed.

 

Query DBA_LOBS view to make sure that the table is migrated to SecureFiles LOB:

 

SQL> SELECT securefile FROM dba_lobs WHERE table_name = ‘TBL_BASIC_LOB’;

SEC

---

YES

 

Now try the command you failed to run before to use SecureFiles option. The command will be running successfully:

 

SQL> ALTER TABLE DBSNMP.tbl_basic_lob MODIFY LOB (clob_data) (CACHE DEDUPLICATE);

Table altered.

SQL>

 

To get more detailed information on secure lobs and managing lob segments, get the OCM preparation book from the following link:

http://www.amazon.com/Oracle-Certified-Master-Exam-Guide/dp/1536800791/

OCM Tips – Create Encrypted Tablespace

In the first section of the OCM exam (Server Configuration), there is a topic “1.3. Create and manage temporary, permanent, and undo tablespaces”. I have provided a very detailed information in the book on this topic and covered the following subjects:

• Create Permanent tablespace
• Create Compressed Tablespace
• Create Encrypted Tablespace
• Create Temporary Tablespace
• Create temporary tablespace group
• Specify Nonstandard block size for tablespace
• Modify Tablespace (OFFLINE, ONLINE, READ-ONLY, ALTER TABLESPACE, SHRINK, RENAME, DROP)
• Managing UNDO tablespace

 

In this newsletter I will show you how to create an encrypted tablespace.

In the beginning of each topic of the exam a reference from the documentation or the metalink note is provided. Check the following documentation to get more information on creating an encrypted tablespace during the exam:

Database Advanced Security Administrator’s Guide -> 8 Securing Stored Data Using Transparent

Data Encryption -> 8.2.5 Encrypting Entire Tablespaces

 

Any permanent tablespace can be encrypted to protect sensitive data. Tablespace encryption is completely transparent to your applications, and hence no application modification is required.

It’s not possible to specify encryption for undo and temporary tablespaces, as there is no need to do so, because data from the encrypted tablespace is automatically encrypted even when written to the online redo log files, undo tablespace and temporary tablespace to maximize the security.

Before attempting to create an encrypted tablespace, a wallet must be created to hold the encryption key, otherwise the following error will pop up “ORA-28365: wallet is not open”.

For this, create a wallet folder and change sql.net ora file as follows:

 

[oracle@ocm11g ~]$ cd $ORACLE_HOME

[oracle@ocm11g db_1]$ mkdir wallet

[oracle@ocm11g db_1]$ cd wallet/

[oracle@ocm11g wallet]$ pwd

/home/oracle/product/11.2.0/db_1/wallet

[oracle@ocm11g wallet]$ cd $ORACLE_HOME/network/admin

[oracle@ocm11g wallet]$ vi sqlnet.ora

ENCRYPTION_WALLET_LOCATION=

(SOURCE=

(METHOD=file)

(METHOD_DATA=

(DIRECTORY=/home/oracle/product/11.2.0/db_1/wallet)))

 

Enter the following command to protect the Oracle wallet:

 

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “mywallet”;

System altered.

SQL>

 

The following error will pop up if you run the above command without changing sqlnet.ora file:

 

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “mywallet”;

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “mywallet”

*

ERROR at line 1:

ORA-28368: cannot auto-create wallet

SQL>

 

 

Now, create encrypted tablespace as follows:

 

SQL> CREATE TABLESPACE encrypted_tbs

DATAFILE ‘/home/oracle/oradata/encrypted_tbs01.dbf’ SIZE 100M

ENCRYPTION USING ‘AES256’

DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SQL>

 

After creating an encrypted tablespace, check V$ENCRYPTED_TABLESPACES view:



SQL> SELECT tbs.name, tbs_en.encryptionalg encr_algorithm

FROM v$tablespace tbs, v$encrypted_tablespaces tbs_en

WHERE tbs.ts# = tbs_en.ts#;

NAME ENCR_ALGORITHM

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

ENCRYPTED_TBS AES256

 

If you want to get more information on creating and managing tablespaces, get my book from the following link:

https://www.amazon.com/Oracle-Certified-Master-Exam-Guide/dp/1536800791/

Oracle 11G Certification guide book sumary

Oracle 11G Certification guide book sumary

Congratulations! on the successful completion of the Oracle DBA 11g Certified Master practicum. You are now a member of an elite group of Oracle professionals.
Every Oracle DBA dreams to get this message from Oracle and one day become an Oracle Certified Master. But when it comes to the preparation, it seems to be hard as there s no book available in the market for OCM 11g exam.
It took me 2 years to prepare this book. For every single topic of the exam I made a deep research of Oracle documentation, metalink notes, blog posts and books and prepared practical guidance that will help you to be prepared for the OCM tasks.
This Study Guide contains the following information:

  • In the beginning of each topic, a reference from Oracle documentation and metalink notes is provided to get more information on a specific subject.
  • Each topic is explained in a practical examples using both graphic user and command line interfaces.
  • At the end of each topic a reader is assigned to solve different pre-created tasks in a specified time limit.

The book consists of 9 chapters and more than 200+ practical OCM scenarios.
Server Configuration chapter provides a detailed step-by-step guide on Oracle 11g Database Software setup and new database creation through GUI and silent mode likewise.
Enterprise Manager Grid Control chapter starts by illustrating a detailed step by step installation of OEM Grid Control starting from the download methods of all OEM components till successful installation. In this chapter you will also see how to create Programs, automate Jobs, create Schedulers, Alerts and Notifications.
Managing Database Availability chapter provides steps to create and manage the recovery catalog. A number of ways also introduced to create a backup of a database and detailed step by step explanation of database recovery from different failure cases through RMAN is provided.
Data Management chapter provides detailed information about types of materialized view and materialized view log and how Oracle uses precomputed materialized view instead of querying a table with different aggregate functions and provides a quick result.
Data Warehouse Management chapter provides information about main data warehouse topics such as partitioning and managing large objects. Next, we talk about large objects and show how to use various SecureFile LOB features such as compression, deduplication, encryption, caching and logging.
Performance Tuning chapter provides a detailed information on SQL Tuning tools such as SQL Tuning Advisor, SQL Access Advisor and SQL Performance Advisor. Beside the tuning tools, it provides information about resource manager, result cache, multi-column statistics and implementing instance caging.
Grid Infrastructure and ASM chapter covers more than enough material to get you started with step by step installation of Oracle Grid Infrastructure. This chapter also sheds light on general Oracle ASM technology and provides information on how to create ASM diskgroups, how to configure failure groups and how to practice disk crashes in ASM environment. Next, we introduce ACFS file system and provide steps to create and manage it.
Real Application Clusters chapter explores steps to successfully create a RAC database on two nodes. With only few additional steps, you will successfully create a RAC database. Then you see the way how to create and configure ASM with command line interface. Once ASM is configured, silent RAC database creation steps are provided.
Data Guard chapter starts by creating a data guard using command line interface, OEM and data guard broker. It also provides steps on performing switchover and failover using all mentioned tools.