Requirements for Flashback Database
1. Your database must be running in ARCHIVELOG mode.
2. You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.
3. For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.
4. To enable Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter
Setups for Setup Oracle Flashback Database
1. Check that the db_flashback_retention_target, db_recovery_file_dest and db_recovery_file_dest_size are set
SQL> select name, value from v$parameter where name like '%flash%';
NAME VALUE
-------------------------------- --------------------------------------- db_flashback_retention_target 1440
SQL> select name, value from v$parameter where name like '%recovery%';
NAME VALUE
------------------------ --------------------------------------- db_recovery_file_dest /opt/oracle/flash_recovery_area
db_recovery_file_dest_size 2147483648
2. Make sure the database is in archive log mode.
SQL> select name, log_mode from v$database ;
NAME LOG_MODE
--------- ------------
ORCL NOARCHIVELOG
3. If database not in archive log mode turn on Archive log mode.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 368263168 bytes
Fixed Size 1299988 bytes
Variable Size 268437996 bytes
Database Buffers 92274688 bytes
Redo Buffers 6250496 bytes
Database mounted.
SQL> alter database archivelog ;
Database altered.
SQL> alter database open ;
Database altered.
4. Turn the flashback on
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 368263168 bytes
Fixed Size 1299988 bytes
Variable Size 268437996 bytes
Database Buffers 92274688 bytes
Redo Buffers 6250496 bytes
Database mounted.
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
SQL> alter database open ;
Database altered.
Wednesday, January 28, 2009
Setup Oracle Transactional Flashback Capability (Automatic Undo)
1. Check if Automatic Undo Management is begin used, undo tablespace is set and undo_retention is set?
SQL> select name, value from v$parameter where name like 'undo%';
NAME VALUE
---------------------- -----------------------------------------
undo_management AUTO
undo_tablespace UNDOTBS1
undo_retention 900
2. If Not Using Automatic Undo Setup Automatic Undo
Create undo tablespace
create undo tablespace undotbs1 datafile ‘c:\oracle\data\ORCL\undotbl1.dbf’ size 500M ;
Set undo_tablespace to undo tablespace created
Alter system set undo_tablespace=undotbs1 scope=spfile ;
Set undo_retention to time period for transactional flashbacks alter system set undo_retention=900 scope=spfile ;
3. If had to setup auto undo bounce database
SQL> shutdown immediate
SQL> startup
4. Update transactional flashback sizing and retention over time
See Sizing Transactional Flashback Capabilities - Sizing the undo tablespace and undo_retention
Oracle Flashback Transaction Query
Summary
Flashback Transaction Query has a lot of the same functionality as Log Miner without the additional overhead associated with Log Miner mining redo logs for undo information. Flashback Transaction Query utilizes an access path to undo data which makes it faster than LogMiner at extracting the undo information. Unlike flashback query, flashback transaction query requires the select any transaction system privilege. How far back to a previous point in time Flashback Transaction Query is capable of executing directly depends on the transaction rate of you database in relationship to the size of the undo tablespace. While the undo_retention database initialization parameter is used to target the undo retention time that makes flashback query possible there must be enough undo tablespace to handle the transactional activity of the database to meet the undo_retention target. The undo_retention is a target and if the transactional activity of the database is high enough and the database needs undo space to continue executing transaction it will overwrite undo information needed to meet the retention. Keep this in mind when setting up and planning your undo retention capabilities.
See Monitoring and Tuning Flashback Capabilities
Executing Flashback Transaction Query
Procedure assumes that the transaction flashback capabilities were setup and tuned as outline in Setup Transactional Flashback Capability (Automatic Undo)
Simple example will utilize the scott.emp table with the following records:
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
1. Record current scn prior to making any changes
SQL> select current_scn scn from sys.v_$database ;
SCN
----------
1068761
2. Change some records with various inserts, updates and deletes.
SQL> delete from scott.emp where empno = 7369 ;
1 row deleted.
SQL> delete from scott.emp where empno = 7782 ;
1 row deleted.
SQL> update scott.emp set sal=10000 where empno = 7876 ;
1 row updated.
SQL> update scott.emp set sal=0 where empno = 7839 ;
1 row updated.
SQL> commit ;
Commit complete.
3. Verify data changes
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 0 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 10000 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
4. Review Undo for Changes Made
SQL> select /*undo_change#,*/ undo_sql
2 from flashback_transaction_query
3 where table_name = 'EMP' and
4 table_owner = 'SCOTT' and
5 logon_user = 'SYS' and
6 commit_scn > 1068761
7 order by undo_change# ;
UNDO_SQL
----------------------------------------------------------------------------------------------------
update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMlsAAEAAAAAgAAI';
update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAAMlsAAEAAAAAgAAK';
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81', 'DD-MON-RR'),'2450',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80', 'DD-MON-RR'),'800',NULL,'20');
5. Undo Changes
See Example Script Below Appendix A: Fix Script Example using Flashback Transaction Query
SQL> @flashback_transaction_query.sql
Enter value for scnnumber: 1068761
old 8: commit_scn > &scnnumber
new 8: commit_scn > 1068761
undo_change#: 1
update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMlsAAEAAAAAgAAI';
undo_change#: 2
update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAAMlsAAEAAAAAgAAK';
undo_change#: 3
insert into
"SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO")
values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81',
'DD-MON-RR'),'2450',NULL,'10');
undo_change#: 4
insert into
"SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO")
values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80',
'DD-MON-RR'),'800',NULL,'20');
PL/SQL procedure successfully completed.
SQL> commit ;
Commit complete.
Or execute the SQL statements from the prior query
update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMlsAAEAAAAAgAAI';
update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAAMlsAAEAAAAAgAAK';
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81', 'DD-MON-RR'),'2450',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80', 'DD-MON-RR'),'800',NULL,'20');
6. Verify Changes are Un-done
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Appendix A: Fix Script Example using Flashback Transaction Query
SET SERVEROUTPUT ON SIZE 1000000
begin
for r in (
select /*undo_change#,*/ undo_sql
from flashback_trasnaction_query
where table_name = 'EMP' and
table_owner = 'SCOTT' and
logon_user = 'SYS' and
commit_scn > &scnnumber
order by undo_change#
) loop
dbms_output.put_line('r.undo_change#: ' r.undo_change#);
dbms_output.put_line(' ' r.undo_sql);
execute immediate
substr(r.undo_sql, 1, length(r.undo_sql)-1);
end loop;
end ;
/
Flashback Transaction Query has a lot of the same functionality as Log Miner without the additional overhead associated with Log Miner mining redo logs for undo information. Flashback Transaction Query utilizes an access path to undo data which makes it faster than LogMiner at extracting the undo information. Unlike flashback query, flashback transaction query requires the select any transaction system privilege. How far back to a previous point in time Flashback Transaction Query is capable of executing directly depends on the transaction rate of you database in relationship to the size of the undo tablespace. While the undo_retention database initialization parameter is used to target the undo retention time that makes flashback query possible there must be enough undo tablespace to handle the transactional activity of the database to meet the undo_retention target. The undo_retention is a target and if the transactional activity of the database is high enough and the database needs undo space to continue executing transaction it will overwrite undo information needed to meet the retention. Keep this in mind when setting up and planning your undo retention capabilities.
See Monitoring and Tuning Flashback Capabilities
Executing Flashback Transaction Query
Procedure assumes that the transaction flashback capabilities were setup and tuned as outline in Setup Transactional Flashback Capability (Automatic Undo)
Simple example will utilize the scott.emp table with the following records:
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
1. Record current scn prior to making any changes
SQL> select current_scn scn from sys.v_$database ;
SCN
----------
1068761
2. Change some records with various inserts, updates and deletes.
SQL> delete from scott.emp where empno = 7369 ;
1 row deleted.
SQL> delete from scott.emp where empno = 7782 ;
1 row deleted.
SQL> update scott.emp set sal=10000 where empno = 7876 ;
1 row updated.
SQL> update scott.emp set sal=0 where empno = 7839 ;
1 row updated.
SQL> commit ;
Commit complete.
3. Verify data changes
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 0 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 10000 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
4. Review Undo for Changes Made
SQL> select /*undo_change#,*/ undo_sql
2 from flashback_transaction_query
3 where table_name = 'EMP' and
4 table_owner = 'SCOTT' and
5 logon_user = 'SYS' and
6 commit_scn > 1068761
7 order by undo_change# ;
UNDO_SQL
----------------------------------------------------------------------------------------------------
update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMlsAAEAAAAAgAAI';
update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAAMlsAAEAAAAAgAAK';
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81', 'DD-MON-RR'),'2450',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80', 'DD-MON-RR'),'800',NULL,'20');
5. Undo Changes
See Example Script Below Appendix A: Fix Script Example using Flashback Transaction Query
SQL> @flashback_transaction_query.sql
Enter value for scnnumber: 1068761
old 8: commit_scn > &scnnumber
new 8: commit_scn > 1068761
undo_change#: 1
update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMlsAAEAAAAAgAAI';
undo_change#: 2
update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAAMlsAAEAAAAAgAAK';
undo_change#: 3
insert into
"SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO")
values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81',
'DD-MON-RR'),'2450',NULL,'10');
undo_change#: 4
insert into
"SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO")
values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80',
'DD-MON-RR'),'800',NULL,'20');
PL/SQL procedure successfully completed.
SQL> commit ;
Commit complete.
Or execute the SQL statements from the prior query
update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMlsAAEAAAAAgAAI';
update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAAMlsAAEAAAAAgAAK';
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81', 'DD-MON-RR'),'2450',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80', 'DD-MON-RR'),'800',NULL,'20');
6. Verify Changes are Un-done
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Appendix A: Fix Script Example using Flashback Transaction Query
SET SERVEROUTPUT ON SIZE 1000000
begin
for r in (
select /*undo_change#,*/ undo_sql
from flashback_trasnaction_query
where table_name = 'EMP' and
table_owner = 'SCOTT' and
logon_user = 'SYS' and
commit_scn > &scnnumber
order by undo_change#
) loop
dbms_output.put_line('r.undo_change#: ' r.undo_change#);
dbms_output.put_line(' ' r.undo_sql);
execute immediate
substr(r.undo_sql, 1, length(r.undo_sql)-1);
end loop;
end ;
/
Wednesday, January 14, 2009
RMAN Backup Encryption
Summary
Oracle backups are necessary to ensure that data is not lost in the event of hardware failures. RMAN is the Oracle recommended way to backup Oracle databases. RMAN has significantly improve Oracle database backups through multi-threaded hot backups, compression, simplified recovery and others. However backups are a way the database data could be exposed. Oracle backups by default are not encrypted and therefore unprotected should the backup be exposed to outside copies that are capable of being restored sue to lack of protection. So how to protect RMAN backups from being able to be restored by unauthorized persons and exposing the data? The answer is encrypting the RMAN backups. RMAN backup encryption requires the use of the Advanced Security Option (ASO). Advanced Security Option requires an additional license therefore before using check your Oracle license and ensure you are authorized to use the Advanced Security Option. Keep in mind that image and datafile copies can not be encrypted.
Oracle offers several flavors for encrypting the RMAN backups:
1. Transparent Encryption -> Requires Wallet on backup and recovery. (Do Not Lose the Wallet!)
2. Password Only -> Required Password on backup and recovery (Do Not Lose Password!)
3. Dual (Password or Transparent Encryption) -> Can be backed up or restored using the password or a wallet. This works well where database is restored locally where the wallet exists, but has a need to be able to be restored off site where the wallet does not exist.
When restoring encrypted backups Oracle RMAN always assumes transparent encryption using a wallet. Therefore during a restore operation and using transparent data encryption ensure the wallet is open, when using password only the password must be supplied, when using dual either the wallet must be open or the password must be supplied.
Setup and Create a Wallet for TDE
1. Set the encryptrion wallet location in the sqlnet.ora file on the database server
Unix server sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY= /cnd7bsw/oracle/network/admin/encrypt)
)
)
WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY= /cnd7bsw/oracle/network/admin/authent)
)
)
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0
2. Create the wallet
o Ensure the oracle account running the database has permissions on the encryption wallet directory defined in the sqlnet.ora file or an ORA-28368: cannot auto-create wallet will occur.
o sqlplus “/ AS SYSDBA”
o SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY “mypass” ;
Opening and Closing the Wallet
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY password>
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE
Encrypting the RMAN Backup
1. Check the available algorithms for encryption.
SELECT * FROM v$rman_encryption_algorithms ORDER BY algorithm_name ;
ALGORITHM_ID ALGORITHM_NAME
------------ ----------------------------------------------------------------
ALGORITHM_DESCRIPTION IS_ RES
---------------------------------------------------------------- --- ---
1 AES128
AES 128-bit key YES NO
2 AES192
AES 192-bit key NO NO
3 AES256
AES 256-bit key NO NO
2. Set the Backup Encryption Type via RMAN
Connect to the target database and RMAN catalog database if being used. Within RMAN set the encryption type via an RMAN configure command. This setting will be stored within the database control file and the RMAN catalog if one is being used.
RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES192';
3. Configure the encryption for the backup
Transparent Backup Encryption (Requires Wallet)
CONFIGURE ENCRYPTION FOR DATABASE ON ;
Turn Encryption off
CONFIGURE ENCRYPTION FOR DATABASE OFF ;
Password Only Encryption
SET ENCRYPTION ON IDENTIFIED BY password ONLY ;
Dual Password/Transparent Encryption
SET ENCRYPTION ON IDENTIFIED BY password ;
4. Execute the backup Database Backup Encrypted
Transparent Encryption** Wallet Must be opened
RMAN> RUN {
# Set the RMAN Encryption
CONFIGURE ENCRYPTION FOR DATABASE ON ;
# Backup the database
BACKUP DATABASE ;
}
Database Backup Encrypted using Password Encryption Only
RMAN> RUN {
# Set password Encryption
SET ENCRYPTION ON IDENTIFIED BYONLY ;
# Backup the database
BACKUP DATABASE ;
}
Database Backup Encrypted using Password Encryption or Wallet Encryption
RMAN> RUN {
# Set password Encryption
SET ENCRYPTION ON IDENTIFIED BY;
# Backup the database
BACKUP DATABASE ;
}
Tablespace Backup Encryption
RMAN> RUN {
# First, clear the current RMAN encryption settings ...
CONFIGURE ENCRYPTION FOR DATABASE OFF ;
# ... then activate encryption for specific tablespaces
CONFIGURE ENCRYPTION FOR TABLESPACE example ON;
CONFIGURE ENCRYPTION FOR TABLESPACE tbs_encrypted ON;
BACKUP TABLESPACE example, tbs_encrypted;
}
Restore Encrypted RMAN Backup
Transparent Encryption Restore
RMAN> RUN {
RESTORE DATABASE ;
RECOVER DATABASE ;
}
Password Encryption Restore
RMAN> RUN {
SET DECRYPTION IDENTIFIED BY;
RESTORE DATABASE ;
RECOVER DATABASE ;
}
Oracle backups are necessary to ensure that data is not lost in the event of hardware failures. RMAN is the Oracle recommended way to backup Oracle databases. RMAN has significantly improve Oracle database backups through multi-threaded hot backups, compression, simplified recovery and others. However backups are a way the database data could be exposed. Oracle backups by default are not encrypted and therefore unprotected should the backup be exposed to outside copies that are capable of being restored sue to lack of protection. So how to protect RMAN backups from being able to be restored by unauthorized persons and exposing the data? The answer is encrypting the RMAN backups. RMAN backup encryption requires the use of the Advanced Security Option (ASO). Advanced Security Option requires an additional license therefore before using check your Oracle license and ensure you are authorized to use the Advanced Security Option. Keep in mind that image and datafile copies can not be encrypted.
Oracle offers several flavors for encrypting the RMAN backups:
1. Transparent Encryption -> Requires Wallet on backup and recovery. (Do Not Lose the Wallet!)
2. Password Only -> Required Password on backup and recovery (Do Not Lose Password!)
3. Dual (Password or Transparent Encryption) -> Can be backed up or restored using the password or a wallet. This works well where database is restored locally where the wallet exists, but has a need to be able to be restored off site where the wallet does not exist.
When restoring encrypted backups Oracle RMAN always assumes transparent encryption using a wallet. Therefore during a restore operation and using transparent data encryption ensure the wallet is open, when using password only the password must be supplied, when using dual either the wallet must be open or the password must be supplied.
Setup and Create a Wallet for TDE
1. Set the encryptrion wallet location in the sqlnet.ora file on the database server
Unix server sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY= /cnd7bsw/oracle/network/admin/encrypt)
)
)
WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY= /cnd7bsw/oracle/network/admin/authent)
)
)
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0
2. Create the wallet
o Ensure the oracle account running the database has permissions on the encryption wallet directory defined in the sqlnet.ora file or an ORA-28368: cannot auto-create wallet will occur.
o sqlplus “/ AS SYSDBA”
o SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY “mypass” ;
Opening and Closing the Wallet
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY password>
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE
Encrypting the RMAN Backup
1. Check the available algorithms for encryption.
SELECT * FROM v$rman_encryption_algorithms ORDER BY algorithm_name ;
ALGORITHM_ID ALGORITHM_NAME
------------ ----------------------------------------------------------------
ALGORITHM_DESCRIPTION IS_ RES
---------------------------------------------------------------- --- ---
1 AES128
AES 128-bit key YES NO
2 AES192
AES 192-bit key NO NO
3 AES256
AES 256-bit key NO NO
2. Set the Backup Encryption Type via RMAN
Connect to the target database and RMAN catalog database if being used. Within RMAN set the encryption type via an RMAN configure command. This setting will be stored within the database control file and the RMAN catalog if one is being used.
RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES192';
3. Configure the encryption for the backup
Transparent Backup Encryption (Requires Wallet)
CONFIGURE ENCRYPTION FOR DATABASE ON ;
Turn Encryption off
CONFIGURE ENCRYPTION FOR DATABASE OFF ;
Password Only Encryption
SET ENCRYPTION ON IDENTIFIED BY password ONLY ;
Dual Password/Transparent Encryption
SET ENCRYPTION ON IDENTIFIED BY password ;
4. Execute the backup Database Backup Encrypted
Transparent Encryption** Wallet Must be opened
RMAN> RUN {
# Set the RMAN Encryption
CONFIGURE ENCRYPTION FOR DATABASE ON ;
# Backup the database
BACKUP DATABASE ;
}
Database Backup Encrypted using Password Encryption Only
RMAN> RUN {
# Set password Encryption
SET ENCRYPTION ON IDENTIFIED BY
# Backup the database
BACKUP DATABASE ;
}
Database Backup Encrypted using Password Encryption or Wallet Encryption
RMAN> RUN {
# Set password Encryption
SET ENCRYPTION ON IDENTIFIED BY
# Backup the database
BACKUP DATABASE ;
}
Tablespace Backup Encryption
RMAN> RUN {
# First, clear the current RMAN encryption settings ...
CONFIGURE ENCRYPTION FOR DATABASE OFF ;
# ... then activate encryption for specific tablespaces
CONFIGURE ENCRYPTION FOR TABLESPACE example ON;
CONFIGURE ENCRYPTION FOR TABLESPACE tbs_encrypted ON;
BACKUP TABLESPACE example, tbs_encrypted;
}
Restore Encrypted RMAN Backup
Transparent Encryption Restore
RMAN> RUN {
RESTORE DATABASE ;
RECOVER DATABASE ;
}
Password Encryption Restore
RMAN> RUN {
SET DECRYPTION IDENTIFIED BY
RESTORE DATABASE ;
RECOVER DATABASE ;
}
Sunday, December 14, 2008
Oracle Heterogenenous Connectivity to MySQL with Database Link 10g
1. We need to install the ODBC driver for the non-Oracle database we are going to connect to.
2. Once the ODBC Driver is installed we will need to configure a data source for the non-Oracle database. For Windows open the control panel and locate the Administrative Tools. Then locate the Data Sources (ODBC) and double click on the Data Sources.
Select the System DSN as for the HS service it will need to be a System DSN then Click on Add button.
Select the Driver for the non-Oracle data source. In this case we will be using the MySQL 3.51 Driver for a local MySQL database.
In this case we will give this data source the name of mysql configured on the localhost, we will be connecting with the root user. In this case we are on the default port therefore we done have to configure the connect options.
3. After configuring the connection settings we can test if the connection is working by pressing the test button near the bottom left of the Window. A window will indicate if the connection was successful.
4. Configure the init.ora for the HS service that well be used for the non-Oracle database connectivity.
initmysql.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
#
# HS init parameters
#
#HS_DB_NAME = mysql
HS_FDS_CONNECT_INFO = mysql
HS_FDS_TRACE_LEVEL = ON
#
# Environment variables required for the non-Oracle system
#
#set=
5. Need to add the non-Oracle data source to the listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
)
(SID_DESC=
(SID_NAME=mysql)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM=hsodbc)
(ENVS=LD_LIBRARY_PATH = C:\oracle\product\10.2.0\db_1\lib32)
)
)
6. Reload the listener and check that we have a service for the non-Oracle data source.
C:\Oracle\product\10.2.0\db_1\BIN> lsnrctl reload
LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 10-AUG-2008 19:14:21
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
7. Check the listener services to make sure the mysql shows
C:\Oracle\product\10.2.0\db_1\BIN> lsnrctl services
LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 10-AUG-2008 19:14:33
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "ORCLXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxx)(PORT=2323))
Service "ORCL_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "mysql" has 1 instance(s).
Instance "mysql", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
8. Need to now add the non-Oracle data source to the tnsnames.ora
mysql =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxx)(PORT = 1521))
(CONNECT_DATA =
(SID = MYSQL)
)
(HS=OK)
)
9. Check that the non-Oracle data source is reachable via a tnsping.
C:\Oracle\product\10.2.0\db_1\BIN>tnsping mysql
TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 14-DEC-2008 18:12:46
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxx)(PORT = 1521)) (CONNECT_DATA = (SID = MYSQL)) (HS=OK))OK (30 msec)
10. Create a public database link and check that it works.
C:\Oracle\product\10.2.0\db_1\BIN>sqlplus /@orcl
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Aug 10 20:10:00 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create public database link mysql connect to "root" identified by "xxxxxxxxxx"
using 'mysql' ;
Database link created.
SQL> select count(*) from alfresco.alf_node@mysql ;
COUNT(*)
----------
1469
SQL>
2. Once the ODBC Driver is installed we will need to configure a data source for the non-Oracle database. For Windows open the control panel and locate the Administrative Tools. Then locate the Data Sources (ODBC) and double click on the Data Sources.
Select the System DSN as for the HS service it will need to be a System DSN then Click on Add button.
Select the Driver for the non-Oracle data source. In this case we will be using the MySQL 3.51 Driver for a local MySQL database.
In this case we will give this data source the name of mysql configured on the localhost, we will be connecting with the root user. In this case we are on the default port therefore we done have to configure the connect options.
3. After configuring the connection settings we can test if the connection is working by pressing the test button near the bottom left of the Window. A window will indicate if the connection was successful.
4. Configure the init.ora for the HS service that well be used for the non-Oracle database connectivity.
initmysql.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
#
# HS init parameters
#
#HS_DB_NAME = mysql
HS_FDS_CONNECT_INFO = mysql
HS_FDS_TRACE_LEVEL = ON
#
# Environment variables required for the non-Oracle system
#
#set
5. Need to add the non-Oracle data source to the listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
)
(SID_DESC=
(SID_NAME=mysql)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM=hsodbc)
(ENVS=LD_LIBRARY_PATH = C:\oracle\product\10.2.0\db_1\lib32)
)
)
6. Reload the listener and check that we have a service for the non-Oracle data source.
C:\Oracle\product\10.2.0\db_1\BIN> lsnrctl reload
LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 10-AUG-2008 19:14:21
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
7. Check the listener services to make sure the mysql shows
C:\Oracle\product\10.2.0\db_1\BIN> lsnrctl services
LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 10-AUG-2008 19:14:33
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "ORCLXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxx)(PORT=2323))
Service "ORCL_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "mysql" has 1 instance(s).
Instance "mysql", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
8. Need to now add the non-Oracle data source to the tnsnames.ora
mysql =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxx)(PORT = 1521))
(CONNECT_DATA =
(SID = MYSQL)
)
(HS=OK)
)
9. Check that the non-Oracle data source is reachable via a tnsping.
C:\Oracle\product\10.2.0\db_1\BIN>tnsping mysql
TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 14-DEC-2008 18:12:46
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxx)(PORT = 1521)) (CONNECT_DATA = (SID = MYSQL)) (HS=OK))OK (30 msec)
10. Create a public database link and check that it works.
C:\Oracle\product\10.2.0\db_1\BIN>sqlplus /@orcl
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Aug 10 20:10:00 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create public database link mysql connect to "root" identified by "xxxxxxxxxx"
using 'mysql' ;
Database link created.
SQL> select count(*) from alfresco.alf_node@mysql ;
COUNT(*)
----------
1469
SQL>
Friday, September 12, 2008
RMAN Tracing
RMAN Tracing allows us the capability to get wom insight into what RMAN is doing behind the scenes so to speak.
Trace an RMAN Session
Trace an RMAN session to a trace file using the trace=/tracefilename.trc as part of the rman execution. Then in the RMAN execute set the debug on.
Example:
rman target username/password@target catalog username/password@catalog trace=/tmp/rman_test.trc
RMAN> run
{debug on;
report obsolete;
debug off;
}
Trace via the RMAN Channel
Another way to trace RMAN activity would be to configure the trace when configuring the RMAN channel.
1. Make sure timed_statistics = true for all performance testing.
2. set the NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'.
3. Configure the Channel with Trace/Debug options
trace 1 - general trace info for skg and sbt
trace 5 - verbose channel tracing of skg and sbt calls
CONFIGURE CHANNEL DEVICE TYPE
DISK DEBUG=5 TRACE 1;
or
CONFIGURE CHANNEL DEVICE TYPE SBT PARMS "ENV=(....) DEBUG=5 TRACE 5;
Note: (Trace is only useful for tape backup and restore tracing)
Trace an RMAN Session
Trace an RMAN session to a trace file using the trace=
Example:
rman target username/password@target catalog username/password@catalog trace=/tmp/rman_test.trc
RMAN> run
{debug on;
report obsolete;
debug off;
}
Trace via the RMAN Channel
Another way to trace RMAN activity would be to configure the trace when configuring the RMAN channel.
1. Make sure timed_statistics = true for all performance testing.
2. set the NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'.
3. Configure the Channel with Trace/Debug options
trace 1 - general trace info for skg and sbt
trace 5 - verbose channel tracing of skg and sbt calls
CONFIGURE CHANNEL DEVICE TYPE
DISK DEBUG=5 TRACE 1;
or
CONFIGURE CHANNEL DEVICE TYPE SBT PARMS "ENV=(....) DEBUG=5 TRACE 5;
Note: (Trace is only useful for tape backup and restore tracing)
Thursday, September 11, 2008
Oracle 11g SecureFiles
Summary
Oracle Secure Files also known as Fast Files is Oracle’s improvement to storage of large object types (LOBs). Secure Files gives comparable performance to a file system for LOBs and LOBs can store many types from data from images, large amounts of text, word documents, excel spreadsheets, XML, HTML, etc. as well as DICOM formatted medical images.
This is a step forward in helping manage unstructured data with the Oracle database by boosting performance, but also by improving security. Secure Files extends Transparent Data Encryption to LOBs, this not only makes storing and managing unstructured content easier, but improves the security of the unstructured content.
If that was not enough Secure Files also gives advanced file system features such as compression and data de-duplication. Data De-duplication is where duplicate objects in LOBs tied to many records within the database is only stored once rather then a copy for each record. This not only improves storage space, but can also offer performance improvements. Compression like it indicates compresses LOB data transparently offering storage savings and a performance boost, but Oracle takes it a step further and automatically determines if the data is able to be compressed and if so are the space savings from the compression of benefit.
By default normal LOB storage is used, called BASIC file. To use SecureFile for LOB storage the SECUREFILE lob Storage keyword must be used.
The default behavior for securefile usage can be changed via the db_securefile initialization parameter.
db_securefile={ALWAYS FORCE PERMITTED NEVER IGNORE}
ALWAYS – Always attempts to create all LOBs as SECUREFILE LOBs
FORCE - all LOBs created in the system will be created as SECUREFILE LOBs.
PERMITTED – Allows LOBs that use SecureFiles.
NEVER – Never Allows LOBs that use SecureFiles, they will always be BASICFILE LOBs
IGNORE – Ignore errors that otherwise would be raised by forcing LOBs as SECUREFILE LOBs
Can be set dynamically via an ALTER SYSTEM:
SQL> ALTER SYSTEM SET db_securefile = 'ALWAYS' ;
Securefiles offer a lot of benefits over the old LOB storage method, such as deduplication capability, compression and encryption.
Compression has 2 forms medium which is the default and high. Keep in mind the high level of compression the larger resource impact on the database you should expect.
CREATE TABLE t1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
(COMPRESS
CACHE
NOLOGGING );
CREATE TABLE t1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
( COMPRESS HIGH
CACHE
NOLOGGING );
Encryption for SecureFiles is implemented via the Transparent Data Encryption (TDE) and SecureFiles extends that TDE for LOB data types. Encryption is performed at the block level and uses the following valid encryption levels 2DES168, AES128, AES192 (default) and AES256. Keep in mind for SecureFiles the NO SALT option is not supported.
CREATE TABLE t1 ( a CLOB ENCRYPT USING 'AES128')
LOB(a) STORE AS SECUREFILE
( CACHE );
DeDuplication can be a powerful feature that can reduce the amount of storage space for LOBs as all duplicated LOBs are only stored once. With the desire to reduce database disk space the compress and DeDuplication can provide significant cost savings for storage.
CREATE TABLE t1 ( REGION VARCHAR2(20), ID NUMBER, a BLOB)
LOB(a) STORE AS SECUREFILE
( DEDUPLICATE
CACHE)
CREATE TABLE t1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
( COMPRESS HIGH
DEDUPLICATE
CACHE ) ;
Oracle Secure Files utilizes Shared IO Pool. The shared IO pool is used from the SGA and allocations are always for a specific session and therefore the data is specific to the session. We can look at the Shared IO Pool via the v$sga_dynamic_componets and v$sgainfo V$ views. If we examine the memory structures of the SGA we can see what the Shared IO Pool max size is in relationship to the other memory structures of the database.
SQL> select name, bytes from v$sgainfo ;
NAME BYTES
-------------------------------- ----------
Fixed SGA Size 1334380
Redo Buffers 5844992
Buffer Cache Size 268435456
Shared Pool Size 239075328
Large Pool Size 4194304
Java Pool Size 12582912
Streams Pool Size 4194304
Shared IO Pool Size 0
Granule Size 4194304
Maximum SGA Size 535662592
Startup overhead in Shared Pool 46137344
Free SGA Memory Available 0
SQL> select * from v$sga_dynamic_components where component='Shared IO Pool' ;
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER
---------- ---------- ------------------- ---------- ------------- ---------
LAST_OPER GRANULE_SIZE
--------- ------------
Shared IO Pool 0
0 0 0 0 STATIC
4194304
When a session is unable to find free memory in the Shared IO Pool, PGA memory would be used. To see PGA memory allocations you can use the V$SECUREFILE_TIMER view which gets an entry each time memory is allocated out of the PGA.
SQL> select * from v$securefile_timer where name like '%PGA%';
NAME LAYER_ID OWNTIME MAXTIME MINTIME INVOCATIONS LAYER_NAME
------------------------- ---------- ------- ------- ------- ----------- --------------------------------------------------
kdlw kcbi PGA alloc timer 2 0 0 0 0
Write gather cache
kdlw kcbi PGA free timer 2 0 0 0 0
Write gather cache
kdlw kcb PGA borrow timer 2 0 0 0 0
Write gather cache
kdlw kcb PGA free timer 2 0 0 0 0
Write gather cache
Oracle Secure Files also known as Fast Files is Oracle’s improvement to storage of large object types (LOBs). Secure Files gives comparable performance to a file system for LOBs and LOBs can store many types from data from images, large amounts of text, word documents, excel spreadsheets, XML, HTML, etc. as well as DICOM formatted medical images.
This is a step forward in helping manage unstructured data with the Oracle database by boosting performance, but also by improving security. Secure Files extends Transparent Data Encryption to LOBs, this not only makes storing and managing unstructured content easier, but improves the security of the unstructured content.
If that was not enough Secure Files also gives advanced file system features such as compression and data de-duplication. Data De-duplication is where duplicate objects in LOBs tied to many records within the database is only stored once rather then a copy for each record. This not only improves storage space, but can also offer performance improvements. Compression like it indicates compresses LOB data transparently offering storage savings and a performance boost, but Oracle takes it a step further and automatically determines if the data is able to be compressed and if so are the space savings from the compression of benefit.
By default normal LOB storage is used, called BASIC file. To use SecureFile for LOB storage the SECUREFILE lob Storage keyword must be used.
The default behavior for securefile usage can be changed via the db_securefile initialization parameter.
db_securefile={ALWAYS FORCE PERMITTED NEVER IGNORE}
ALWAYS – Always attempts to create all LOBs as SECUREFILE LOBs
FORCE - all LOBs created in the system will be created as SECUREFILE LOBs.
PERMITTED – Allows LOBs that use SecureFiles.
NEVER – Never Allows LOBs that use SecureFiles, they will always be BASICFILE LOBs
IGNORE – Ignore errors that otherwise would be raised by forcing LOBs as SECUREFILE LOBs
Can be set dynamically via an ALTER SYSTEM:
SQL> ALTER SYSTEM SET db_securefile = 'ALWAYS' ;
Securefiles offer a lot of benefits over the old LOB storage method, such as deduplication capability, compression and encryption.
Compression has 2 forms medium which is the default and high. Keep in mind the high level of compression the larger resource impact on the database you should expect.
CREATE TABLE t1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
(COMPRESS
CACHE
NOLOGGING );
CREATE TABLE t1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
( COMPRESS HIGH
CACHE
NOLOGGING );
Encryption for SecureFiles is implemented via the Transparent Data Encryption (TDE) and SecureFiles extends that TDE for LOB data types. Encryption is performed at the block level and uses the following valid encryption levels 2DES168, AES128, AES192 (default) and AES256. Keep in mind for SecureFiles the NO SALT option is not supported.
CREATE TABLE t1 ( a CLOB ENCRYPT USING 'AES128')
LOB(a) STORE AS SECUREFILE
( CACHE );
DeDuplication can be a powerful feature that can reduce the amount of storage space for LOBs as all duplicated LOBs are only stored once. With the desire to reduce database disk space the compress and DeDuplication can provide significant cost savings for storage.
CREATE TABLE t1 ( REGION VARCHAR2(20), ID NUMBER, a BLOB)
LOB(a) STORE AS SECUREFILE
( DEDUPLICATE
CACHE)
CREATE TABLE t1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
( COMPRESS HIGH
DEDUPLICATE
CACHE ) ;
Oracle Secure Files utilizes Shared IO Pool. The shared IO pool is used from the SGA and allocations are always for a specific session and therefore the data is specific to the session. We can look at the Shared IO Pool via the v$sga_dynamic_componets and v$sgainfo V$ views. If we examine the memory structures of the SGA we can see what the Shared IO Pool max size is in relationship to the other memory structures of the database.
SQL> select name, bytes from v$sgainfo ;
NAME BYTES
-------------------------------- ----------
Fixed SGA Size 1334380
Redo Buffers 5844992
Buffer Cache Size 268435456
Shared Pool Size 239075328
Large Pool Size 4194304
Java Pool Size 12582912
Streams Pool Size 4194304
Shared IO Pool Size 0
Granule Size 4194304
Maximum SGA Size 535662592
Startup overhead in Shared Pool 46137344
Free SGA Memory Available 0
SQL> select * from v$sga_dynamic_components where component='Shared IO Pool' ;
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER
---------- ---------- ------------------- ---------- ------------- ---------
LAST_OPER GRANULE_SIZE
--------- ------------
Shared IO Pool 0
0 0 0 0 STATIC
4194304
When a session is unable to find free memory in the Shared IO Pool, PGA memory would be used. To see PGA memory allocations you can use the V$SECUREFILE_TIMER view which gets an entry each time memory is allocated out of the PGA.
SQL> select * from v$securefile_timer where name like '%PGA%';
NAME LAYER_ID OWNTIME MAXTIME MINTIME INVOCATIONS LAYER_NAME
------------------------- ---------- ------- ------- ------- ----------- --------------------------------------------------
kdlw kcbi PGA alloc timer 2 0 0 0 0
Write gather cache
kdlw kcbi PGA free timer 2 0 0 0 0
Write gather cache
kdlw kcb PGA borrow timer 2 0 0 0 0
Write gather cache
kdlw kcb PGA free timer 2 0 0 0 0
Write gather cache
Subscribe to:
Posts (Atom)
