Oracle: May 2014

Thursday, May 8, 2014

Enable TDE in oracle Goldengate CLASSIC mode

Ref: [Doc ID: 1451327.1]

Ref: http://docs.oracle.com/cd/E35209_01/doc.1121/e35957.pdf

To support TDE on Oracle 10.2.0.5 or 11.2.0.2, download and apply Oracle Patch 10395645 to the source database. Oracle 11.2.0.3 patchset includes this patch

Since my database is on 11.2.0.2 Two-Node RAC on LINUX so i'm applying below patch (see patch doc for how to apply)

$ opatch apply online -connectString<SID_Node1>:<Username_Node1>:<Password_Node1>:<Node1_Name>,<SID_Node2>:<Username_Node2>:<Password_Node2>:<Node2_Name>,<SID_NodeN>:<Username_NodeN>:<Password_NodeN>:<NodeN_Name><PATCH_TOP_DIR>/10395645

From NODE 1: (everything from NODE1 unless specified)

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

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

DEV011

SQL> select * from gv$encryption_wallet;

INST_ID WRL_TYPE

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

WRL_PARAMETER STATUS

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

1 file

/opt/oracle/database/11.2.0.2/admin/DEV01/wallet CLOSED

2 file

/opt/oracle/database/11.2.0.2/admin/DEV01/wallet CLOSED

Status "CLOSED" because my database doesn't have any Wallet created yet

SQL> select * from v$encryption_wallet;

WRL_TYPE

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

WRL_PARAMETER STATUS

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

file

/opt/oracle/app/admin/DEV01/wallet CLOSED

Put the below code in sqlnet.ora of GI and OH home location :

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/app/admin/DEV01/wallet/)))

NOTE: Create the directories on both nodes if they doesn't exists on the server

ex: mkdir -p /opt/oracle/app/admin/DEV01/wallet

copy sqlnet.ora file to other nodes into same locations (GRID and RDBMS homes)

ex: scp sqlnet.ora oracle@oracledev012.domain.com:/opt/oracle/database/11.2.0.2/network/admin

scp sqlnet.ora oracle@oracledev012.domain.com:/opt/grid/11.2.0.2/network/admin

Now create a wallet password using below sql command from one node:

SQL> alter system set encryption key identified by "xxxxx";

System altered.

SQL> select * from gv$encryption_wallet;

INST_ID WRL_TYPE

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

WRL_PARAMETER STATUS

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

1 file

/opt/oracle/database/11.2.0.2/admin/DEV01/wallet OPEN

2 file

/opt/oracle/database/11.2.0.2/admin/DEV01/wallet CLOSED

copy the wallet file from /opt/oracle/app/admin/DEV01/wallet/ from node1 to other nodes

**** Now, BOUNCE the database ****

$ srvctl stop database -d DEV01

$ srvctl start database -d DEV01

SQL> select * from gv$encryption_wallet;

INST_ID WRL_TYPE

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

WRL_PARAMETER STATUS

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

1 file

/opt/oracle/app/admin/DEV01/wallet/ CLOSED

2 file

/opt/oracle/app/admin/DEV01/wallet/ CLOSED

Observe, Oracle wallet location has been changed after database bounce

SQL> alter system set encryption wallet open identified by “xxxxx";

System altered.

SQL> select * from gv$encryption_wallet;

INST_ID WRL_TYPE

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

WRL_PARAMETER STATUS

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

1 file

/opt/oracle/app/admin/DEV01/wallet/ OPEN

2 file

/opt/oracle/app/admin/DEV01/wallet/ OPEN

From GG_HOME directory, run prvtclkm.plb, and grant privilege to goldengate user

(missing this step may cause extract error: PLS-00201: identifier 'SYS.DBMS_INTERNAL_CLKM' must be declared)

oracle@oracleDEV01.domain.com:/u01/NAS/GGATE INT$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 18 20:59:07 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>@prvtclkm.plb

Package created.

Library created.

Package body created.

SQL>grant execute on sys.dbms_internal_clkm to gguser;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

oracle@oracleDEV01.domain.com:/u01/NAS/GGATE INT$ cd /opt/oracle/app/admin/DEV01/wallet

From wallet location, execute below:

oracle@oracleDEV01.domain.com:/opt/oracle/app/admin/DEV01/wallet INT$ mkstore -wrl . -list

Oracle Secret Store Tool : Version 11.2.0.2.0 - Production

Enter wallet password:

Oracle Secret Store entries:

ORACLE.SECURITY.DB.ENCRYPTION.AQoxcXkgI09Qv3AJzMZizmwAAAAAAAAAAAAAAAAAAAAAAAAA

ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

ORACLE.SECURITY.TS.ENCRYPTION.BTJ9EEoIi7O8MokUyaU1SmMCAwAAAAAAAAAAAAAAAAAAAAAA

Now, create an entry forORACLEGG in the wallet (this will ask you to create sharedsecret password)

oracle@oracleDEV01.domain.com:/opt/oracle/app/admin/DEV01/wallet INT$ mkstore -wrl . -createEntry ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG

Oracle Secret Store Tool : Version 11.2.0.2.0 - Production

Your secret/Password is missing in the command line

Enter your secret/Password:enter_sharedsecret_password

Re-enter your secret/Password:enter_sharedsecret_password

Enter wallet password:enter_wallet_password

Now verify whether OGG entry has been created or not

oracle@oracleDEV01.domain.com:/opt/oracle/app/admin/DEV01/wallet INT$ mkstore -wrl . -list

Oracle Secret Store Tool : Version 11.2.0.2.0 - Production

Enter wallet password:

Oracle Secret Store entries:

ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG

ORACLE.SECURITY.DB.ENCRYPTION.AQoxcXkgI09Qv3AJzMZizmwAAAAAAAAAAAAAAAAAAAAAAAAA

ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

ORACLE.SECURITY.TS.ENCRYPTION.BTJ9EEoIi7O8MokUyaU1SmMCAwAAAAAAAAAAAAAAAAAAAAAA

Now we’ll see the new ORACLEGG entry in the wallet (only in node1), so to create the entry in other nodes copy the wallet to other nodes

Here I’m doing SCP the wallet file from node1 to other nodes

$ scp * oracle@oracledev012.domain.com:/opt/oracle/app/admin/DEV01/wallet

After copying wallet file from node1 to node2 list and see for new encryption for ORACLEGG entry from Node2 server

(from NODE2) oracle@oracledev012.domain.com:/opt/oracle/app/admin/DEV01/wallet INT$ mkstore -wrl . -list

Oracle Secret Store Tool : Version 11.2.0.2.0 - Production

Enter wallet password:

Oracle Secret Store entries:

ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG

ORACLE.SECURITY.DB.ENCRYPTION.AQoxcXkgI09Qv3AJzMZizmwAAAAAAAAAAAAAAAAAAAAAAAAA

ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

ORACLE.SECURITY.TS.ENCRYPTION.BTJ9EEoIi7O8MokUyaU1SmMCAwAAAAAAAAAAAAAAAAAAAAAA

NOTE:

- close wallet from all the instances.

- re-open the wallet from all the instances.

Ex:

alter system set encryption wallet close identified by "xxxx"; (node1)

alter system set encryption wallet open identified by "xxxx"; (node1)

alter system switch logfile;

alter system set encryption wallet close identified by "xxxx"; (node2)

alter system set encryption wallet open identified by "xxxx"; (node2)

alter system switch logfile;

Connect to ggsci and encrypt the sharedsecret password

GGSCI (oracleDEV01.domain.com) 2> ENCRYPT PASSWORD sharedsecret AES128 ENCRYPTKEY WALLETENCR

Encrypted password: AADAAAAAAAAAAAKAVAAIOFMFRHOFLEVHIIMIOJEJKFEGLAWIDFDHRFXFFJCEIELFXAJHCBJDHGJAIANI

Algorithm used: AES128

Where WALLETENCR à key mentioned in the ENCKEYS file

See: Generate GOLDENGATE Encryption Keys usingKeygen

In the Extract parameter file, use the DBOPTIONS parameter with the DECRYPTPASSWORD option

GGSCI (oracleDEV01.domain.com) 3> edit params EXT

DBOPTIONS DECRYPTPASSWORD AADAAAAAAAAAKAVAAIOFMFRHOFLEVHIIMIOJEJKFEGLAWIDFDHRFXFFJCEIELFXAJHCBJDHGJAIANI AES128 &

ENCRYPTKEY WALLETENCR

Bounce the extract process to take this effect

Thursday, May 1, 2014

Adding Disks to ASM diskgroup Manually

Oracle: $ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Apr 21 17:14:46 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning and Automatic Storage Management options

SQL> select GROUP_NUMBER, NAME,TOTAL_MB, FREE_MB, USABLE_FILE_MB from V$ASM_DISKGROUP;

GROUP_NUMBER NAME TOTAL_MB FREE_MB USABLE_FILE_MB

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

1 DATA 221181 128900 128900

2 ORAARCH 73727 73675 73675

3 REDO 8191 1902 1902

SQL> select disk_number, mode_status, name, path from v$asm_disk where group_number = 3 order by disk_number, name ;

DISK_NUMBER MODE_ST NAME PATH

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

0 ONLINE REDO01 ORCL:REDO01

From above we see REDO diskgroup has only I disk(REDO01)

Now to check the available disks on server to add them to ASM diskgroup

SQL> select path, header_status, mode_status from v$asm_disk;

PATH HEADER_STATU MODE_ST

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

ORCL:DATA01 MEMBER ONLINE

ORCL:DATA02 MEMBER ONLINE

ORCL:DATA03 MEMBER ONLINE

ORCL:ORAARCH01 MEMBER ONLINE

ORCL:REDO01 MEMBER ONLINE

ORCL:REDO08 PROVISIONED ONLINE

ORCL:REDO07 PROVISIONED ONLINE

ORCL:REDO06 PROVISIONED ONLINE

ORCL:REDO05 PROVISIONED ONLINE

ORCL:REDO04 PROVISIONED ONLINE

ORCL:REDO03 PROVISIONED ONLINE

ORCL:REDO02 PROVISIONED ONLINE

12 rows selected.

SQL> ALTER DISKGROUP REDO ADD DISK 'ORCL:REDO02';

ALTER DISKGROUP REDO ADD DISK 'ORCL:REDO02'

ERROR at line 1:

ORA-15000: command disallowed by current instance type

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning and Automatic Storage Management options

oracle $:/opt/oracle

oracle:$ export ORACLE_SID=+ASM1

oracle :$ export ORACLE_HOME=

oracle :$ export PATH=

oracle:+ASM-> sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 21 17:41:11 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Automatic Storage Management option

SQL> ALTER DISKGROUP REDO ADD DISK 'ORCL:REDO02';

ALTER DISKGROUP REDO ADD DISK 'ORCL:REDO02'

ERROR at line 1:

ORA-15032: not all alterations performed

ORA-15260: permission denied on ASM disk group

NOTE: According to Doc ID 889810.1 logging in with SYSDBA privilege, that is not allowed for ASM operations

SQL> conn / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 21 17:42:38 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Automatic Storage Management option

SQL> set linesize 280

SQL> col path format a15

SQL> select disk_number, mode_status, name, path from v$asm_disk where group_number = 3 order by disk_number, name ;

DISK_NUMBER MODE_ST NAME PATH

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

0 ONLINE REDO01 ORCL:REDO01

Now add 1 disk to REDO Disk group:

SQL> ALTER DISKGROUP REDO ADD DISK 'ORCL:REDO02';

Diskgroup altered.

SQL> select disk_number, mode_status, name, path from v$asm_disk where group_number = 3 order by disk_number, name ;

DISK_NUMBER MODE_ST NAME PATH

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

0 ONLINE REDO01 ORCL:REDO01

1 ONLINE REDO02 ORCL:REDO02

SQL> select path, group_number group_#, disk_number disk_#, mount_status, header_status, state, total_mb, free_mb from v$asm_disk order by group_number;

PATH GROUP_# DISK_# MOUNT_S HEADER_STATU STATE TOTAL_MB FREE_MB

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

ORCL:REDO03 0 1 CLOSED PROVISIONED NORMAL 0 0

ORCL:REDO04 0 2 CLOSED PROVISIONED NORMAL 0 0

ORCL:REDO05 0 3 CLOSED PROVISIONED NORMAL 0 0

ORCL:REDO06 0 4 CLOSED PROVISIONED NORMAL 0 0

ORCL:REDO07 0 5 CLOSED PROVISIONED NORMAL 0 0

ORCL:REDO08 0 6 CLOSED PROVISIONED NORMAL 0 0

ORCL:DATA01 1 0 CACHED MEMBER NORMAL 73727 42968

ORCL:DATA02 1 1 CACHED MEMBER NORMAL 73727 42965

ORCL:DATA03 1 2 CACHED MEMBER NORMAL 73727 42967

ORCL:ORAARCH01 2 0 CACHED MEMBER NORMAL 73727 73675

ORCL:REDO01 3 0 CACHED MEMBER NORMAL 8191 2408

ORCL:REDO02 3 1 CACHED MEMBER NORMAL 8191 7683

12 rows selected.

From the above output ORCL:REDO02 now became MEMBER from PROVISIONED status

Now check the ASM rebalancing

SQL> select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES

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

3 REBAL RUN 1 1 1712 3148 1104 1

SQL> /

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES

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

3 REBAL RUN 1 1 3142 3148 1145 0

SQL> select GROUP_NUMBER, NAME,TOTAL_MB, FREE_MB, USABLE_FILE_MB from V$ASM_DISKGROUP;

GROUP_NUMBER NAME TOTAL_MB FREE_MB USABLE_FILE_MB

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

1 DATA 221181 128900 128900

2 ORAARCH 73727 73675 73675

3 REDO 16382 10091 10091

After adding all the remaining disks…..

SQL> select GROUP_NUMBER, NAME,TOTAL_MB, FREE_MB, USABLE_FILE_MB from V$ASM_DISKGROUP;

GROUP_NUMBER NAME TOTAL_MB FREE_MB USABLE_FILE_MB

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

1 DATA 221181 128900 128900

2 ORAARCH 73727 73675 73675

3 REDO 65516 59213 59213

From 11gr2 we can also add/delete/alter disks/volumes using ASMCA(ASM configuration assistant)

see [Doc ID 885780.1]