Wednesday, November 19, 2014

ORA-12516, TNS: listener could not find available handler with matching protocol stack

My Application Team getting below error and unable to initiate connection to the oracle database:

ORA-12516, TNS: listener could not find available handler with matching protocol stack

Then I connected and checked on the database, here my db is RAC server and using spfile:

On NODE 2:

Check the alert log file 

vi alert_DEVD2.log      

No errors found

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 18 19:35:36 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL>
col name format a30
col value format a10

SQL> select name, value from v$spparameter where name in ('processes','sessions');

NAME                           VALUE
------------------------------ ----------
processes                      400
sessions                        400


SQL>  select count(*) from v$process;

  COUNT(*)
----------
       166

On NODE 1 :

SQL> select * from v$resource_limit where resource_name in ('processes','sessions');



 vi alert_DEVD1.log      

ORA-00020: maximum number of processes (400) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.


SQL> select count(*) from v$process;                

  COUNT(*)
----------
       380


SQL> select count(*) from gv$process;

  COUNT(*)
----------
       546

From the above output I see my processes are exceeded the value set by oracle(400) and resulting the error in alert log. Then checked the connections to the database:

Set linesize 280;
select count(*),INST_ID,username,osuser,status,machine from gv$session group by INST_ID,username,osuser,status,machine order by count(*),INST_ID;

Check the output from the above query and kill if find any INACTIVE sessions (idle)

Solution:

According to oracle, PROCESSES parameter specifies the maximum number of operating system user processes that can simultaneously connect to Oracle.


If the database is a RAC database then each NODE can have different values for PROCESS parameter.

SQL> sho parameter spfile;

NAME                                           TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATADG/DEVD/spfiledevd.ora

Increase the Process parameter (replace xxx with any higher value)

SQL> alter system set processes=XXX  scope=spfile;
System altered.

SQL> alter system set Sessions=XXX  scope=spfile;
System altered.

*** Bounce the database to take effect ***




If processes=x
sessions=x*1.1+5
transactions=sessions*1.1


If the database is running on pfile then follow below steps:

1. Edit the database init.ora
2. Locate and increase the processes parameter to a higher value.
3. Save the init.ora file
4. Stop and restart the database
5. Re-connect to the database instance


For more Refer:

Troubleshooting Guide - ORA-20: Maximum Number Of Processes (%S) Exceeded (Doc ID 1287854.1)


Tuesday, October 14, 2014

OGG-00049 Trails cannot be used when SOURCEISTABLE/SOURCEISFILE is specified

Today when doing Initial load my extract got ABENDED with the error below

2014-10-14 19:46:37  ERROR   OGG-00049  Trails cannot be used when SOURCEISTABLE/SOURCEISFILE is specified.
2014-10-14 19:46:37  ERROR   OGG-01668  PROCESS ABENDING.


GGSCI (oracledev01.com) 1> view params initEXT

EXTRACT initEXT
SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV(ORACLE_SID=orcl)
sourceistable
USERID ggs_admin,PASSWORD xxxxx
RMTHOST oracleqa01, mgrport 7809
RMTTRAIL /u01/dev/oracle/input/ggs/remote/cb/DEV_ORCL.dat, purge
TABLE DEV.TAB1;
TABLE DEV.TAB2;

 Since here i'm sending the ".dat" files to the target server for the reporting purpose i'm not using Datapump and Replicat process, just sending files directly from my extract process (datapump process is always OPTIONAL)

SOURCEISTABLE:  extracts the records from the source tables directly.
RMTFILE:   Specifies the location and file name of the extract file that will be written during the load.

SOLUTION:   Remove the EXTTRAIL or RMTTRAIL parameter.

When I replaced RMTTRAIL with RMTFILE, then my issue got resolved.

RMTFILE /u01/dev/oracle/input/ggs/remote/cb/DEV_ORCL.dat, purge



GGSCI (oracledev01.domain.com) 3> start initEXT
Sending START request to MANAGER ...
EXTRACT INITEXT starting

GGSCI (oracledev01.domain.com) 4> info initEXT

EXTRACT    INITEXT      Initialized   2014-10-14 17:40   Status RUNNING
Checkpoint Lag              Not Available
Log Read Checkpoint   Not Available
                                       First Record         Record 0
Task                              SOURCEISTABLE


Reference: 


Friday, October 10, 2014

Purge 11g LISTENER Logs using ADRCI

While doing server Housekeeping and found my Listener log files are consuming lot of space and contains logs from past few months.

According to Oracle doc, “Listener log files ('.xml' files) are not purged via the 'automatic' purge”. Therefore in 11g releases, these log files (and trace files) will not get automatically purged and so you need to manually delete them when they are no longer required.

Below are my listener log files on node1 of my 2 node RAC

OS version:  RHEL 5.x
Oracle version: 11.2.0.3

[root@oracledev01 alert]# pwd
/u01/app/oracle/diag/tnslsnr/oracledev01/listener/alert

[root@oracledev01 alert]# ls -lrth
total 1.1G
-rw-r----- 1 grid oinstall  11M Aug 13 02:56 log_1.xml
-rw-r----- 1 grid oinstall  11M Aug 13 09:49 log_2.xml
-rw-r----- 1 grid oinstall  11M Aug 13 16:33 log_3.xml
-rw-r----- 1 grid oinstall  11M Aug 13 23:35 log_4.xml
-rw-r----- 1 grid oinstall  11M Aug 14 06:20 log_5.xml
-rw-r----- 1 grid oinstall  11M Aug 14 13:38 log_6.xml
-rw-r----- 1 grid oinstall  11M Aug 14 20:57 log_7.xml
-rw-r----- 1 grid oinstall  11M Aug 15 04:05 log_8.xml
-rw-r----- 1 grid oinstall  11M Aug 15 11:47 log_9.xml
-rw-r----- 1 grid oinstall  11M Aug 15 19:15 log_10.xml
-rw-r----- 1 grid oinstall  11M Aug 16 02:48 log_11.xml
-rw-r----- 1 grid oinstall  11M Aug 16 11:11 log_12.xml
-rw-r----- 1 grid oinstall  11M Aug 16 21:36 log_13.xml
-rw-r----- 1 grid oinstall  11M Aug 17 08:05 log_14.xml
-rw-r----- 1 grid oinstall  11M Aug 17 17:57 log_15.xml
-rw-r----- 1 grid oinstall  11M Aug 18 03:34 log_16.xml
-rw-r----- 1 grid oinstall  11M Aug 18 12:47 log_17.xml
……….
o/p trimmed
……….
-rw-r----- 1 grid oinstall  11M Oct  4 16:30 log_93.xml
-rw-r----- 1 grid oinstall  11M Oct  5 11:11 log_94.xml
-rw-r----- 1 grid oinstall  11M Oct  5 15:28 log_95.xml
-rw-r----- 1 grid oinstall  11M Oct  5 20:27 log_96.xml
-rw-r----- 1 grid oinstall  11M Oct  6 01:28 log_97.xml
-rw-r----- 1 grid oinstall  11M Oct  6 06:44 log_98.xml
-rw-r----- 1 grid oinstall  11M Oct  6 12:09 log_99.xml
-rw-r----- 1 grid oinstall  11M Oct  6 17:33 log_100.xml
-rw-r----- 1 grid oinstall  11M Oct  6 23:33 log_101.xml
-rw-r----- 1 grid oinstall  11M Oct  7 17:04 log_102.xml
-rw-r----- 1 grid oinstall  11M Oct  8 10:42 log_103.xml
-rw-r----- 1 grid oinstall  11M Oct  9 03:27 log_104.xml
-rw-r----- 1 grid oinstall  11M Oct  9 12:21 log_105.xml
-rw-r----- 1 grid oinstall  11M Oct  9 20:59 log_106.xml
-rw-r----- 1 grid oinstall  11M Oct 10 04:27 log_107.xml
-rw-r----- 1 grid oinstall  11M Oct 10 11:57 log_108.xml
-rw-r----- 1 grid oinstall 6.0M Oct 10 16:19 log.xml

NOTE:  log.xml files will be archived into a log_1.xml, log_2.xml and so on... into the same location as soon as it grows to 10MBytes

[root@oracledev01 alert]# . oraenv
ORACLE_SID = [root] ? DBNAME1
The Oracle base has been set to /u01/app/oracle
[root@oracledev01 alert]# adrci

ADRCI: Release 11.2.0.3.0 - Production on Fri Oct 10 16:38:55 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci> show home
ADR Homes:
diag/tnslsnr/oracledev01/listener

adrci> set home diag/tnslsnr/oracledev01/listener

adrci> SHOW CONTROL

ADR Home = /u01/app/oracle/diag/tnslsnr/oracledev01/listener:
*************************************************************************
ADRID   SHORTP_POLICY  LONGP_POLICY LAST_MOD_TIME    LAST_AUTOPRG_TIME                    LAST_MANUPRG_TIME   ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------
2146670485   720    8760   2011-04-22 20:50:43.449927 +00:00                                                                                          1                    2                    80                   1                                 2011-04-22 20:50:43.449927 +00:00

1 rows fetched


From the above output we see LAST_AUTOPRG_TIME & LAST_MANUPRG_TIME are empty, meaning that they are not being purged automatically and no one purged manually

adrci> purge -age 10080 -type ALERT               (purging older than 7 days, 10080 mins)
adrci> exit

Verify:

[root@oracledev01 alert]# ls -lrth
total 197M
-rw-r----- 1 grid oinstall  11M Oct  3 20:41 log_90.xml
-rw-r----- 1 grid oinstall  11M Oct  4 03:01 log_91.xml
-rw-r----- 1 grid oinstall  11M Oct  4 09:44 log_92.xml
-rw-r----- 1 grid oinstall  11M Oct  4 16:30 log_93.xml
-rw-r----- 1 grid oinstall  11M Oct  5 11:11 log_94.xml
-rw-r----- 1 grid oinstall  11M Oct  5 15:28 log_95.xml
-rw-r----- 1 grid oinstall  11M Oct  5 20:27 log_96.xml
-rw-r----- 1 grid oinstall  11M Oct  6 01:28 log_97.xml
-rw-r----- 1 grid oinstall  11M Oct  6 06:44 log_98.xml
-rw-r----- 1 grid oinstall  11M Oct  6 12:09 log_99.xml
-rw-r----- 1 grid oinstall  11M Oct  6 17:33 log_100.xml
-rw-r----- 1 grid oinstall  11M Oct  6 23:33 log_101.xml
-rw-r----- 1 grid oinstall  11M Oct  7 17:04 log_102.xml
-rw-r----- 1 grid oinstall  11M Oct  8 10:42 log_103.xml
-rw-r----- 1 grid oinstall  11M Oct  9 03:27 log_104.xml
-rw-r----- 1 grid oinstall  11M Oct  9 12:21 log_105.xml
-rw-r----- 1 grid oinstall  11M Oct  9 20:59 log_106.xml
-rw-r----- 1 grid oinstall  11M Oct 10 04:27 log_107.xml
-rw-r----- 1 grid oinstall  11M Oct 10 11:57 log_108.xml
-rw-r----- 1 grid oinstall 6.3M Oct 10 16:39 log.xml

$ date
Fri Oct 10 17:36:34 GMT 2014

adrci> SHOW CONTROL

ADR Home = /u01/app/oracle/diag/tnslsnr/oracledev01/listener:
*************************************************************************
ADRID    SHORTP_POLICY   LONGP_POLICY      LAST_MOD_TIME                            LAST_AUTOPRG_TIME         LAST_MANUPRG_TIME     ADRDIR_VERSION     ADRSCHM_VERSION      ADRSCHMV_SUMMARY   ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------
2146670485           720                  8760        2011-04-22 20:50:43.449927 +00:00                                                 2014-10-10 16:39:37.644372 +00:00        1                    2                    80                   1                    2011-04-22 20:50:43.449927 +00:00
1 rows fetched

I see today’s date now in the LAST_MANUPRG_TIME column

NOTE: Similarly we can purge files in different component using same command

ALERT         — files in the ./alert directory
INCIDENT   — files in the ./incident/incdir_<incid> directory. Please note that  'purge -age "n" -type INCIDENT' does not remove the *.lck files from the /lck directory in the incident schema
TRACE        — files in the ./trace directory
CDUMP      — files in the ./cdump directory
UTSCDMP — files in the ./trace/cdmp_<timestamp> directories
STAGE        — files in the ./stage directory
SWEEP       — files in the ./sweep directory
HM              — files in the ./hm directory and metadata in the HM schema
IPS               — files in the ./incpkg directory and metadata in the IPS schema

Referrence:







Monday, August 18, 2014

ORA-26947: Oracle GoldenGate replication is not enabled

When I’m trying to setup OGG On my Linux Machine after finished installing (Installation Steps) when I start my extract process it got ABENDED with the below Error message and when I see the ORA error in the error log and after researching some docs then I realized Oracle introduced a new OGG parameter in order to enable OGG replication

ERROR:

2014-08-15 18:00:38  WARNING OGG-01423  No valid default archive log destination directory found for thread 1.

2014-08-15 18:00:38  INFO    OGG-01515  Positioning to begin time Aug 15, 2014 5:44:29 PM.

----output Trimmed

2014-08-15 18:00:59  ERROR   OGG-00446  Opening ASM file +FRADG/gsource/archivelog/2014_08_15/thread_1_seq_7.302.85568
4013 in DBLOGREADER mode: (26947) ORA-26947: Oracle GoldenGate replication is not enabled.
Not able to establish initial position for begin time 2014-08-15 17:44:29.

2014-08-15 18:00:59  ERROR   OGG-01668  PROCESS ABENDING.


Solution:

For Oracle DB versions 11.2.0.4 and 12.1.0.2 there will be a new init.ora parameter called ENABLE_GOLDENGATE_REPLICATION.  In order for some of the Oracle GoldenGate functionality to work, this parameter must be set to true.

SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE   SCOPE=BOTH;
System altered.

No need to Bounce the database instance

Referrence : ENABLE_GOLDENGATE_REPLICATION database parameter (Doc ID 1568417.1)


Thursday, May 8, 2014

Enable TDE in oracle Goldengate CLASSIC mode


Ref:  [Doc ID: 1451327.1]

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

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


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
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved.

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
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved.

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
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved.

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
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved.

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


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

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


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

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


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]