Tips & Tricks 3 : Exclude table ,schema from Expdp

From command line if you would like to exclude table , tables or schema following command can be used .

For specific table(s)

expdp system/pass directory=dp_dir dumpfile=sample.dmp exclude=TABLE:\ "IN \(\'TAB1\',\'TAB2\',\'TAB3\'\)\"

For schema(s)

expdp system/pass directory=dp_dir dumpfile=sample.dmp exclude=SCHEMA:\ "IN \(\'SCHEMA_NAME1\',\'SCHEMA_NAME2\'\)\"

Tips & Tricks 2 : Disable autostart of High Availability Service

Whenever  any components like ASM,listener,database is down, or when server restarted, component called HAS (High Availability Service) restarts the failed components and bring them up. This is done automatically. Sometimes it is not wanted to start itself automatically. In order to disable these property below command is executed ,

[root@test01 bin]# ./crsctl disable has
CRS-4621: Oracle High Availability Services autostart is disabled.
[root@test01 bin]#

http://docs.oracle.com/cd/E18283_01/server.112/e17120/restart004.htm#CIHGFJEB

Tips & Tricks 1 : Give debug privilege to user

Sometimes small tips and tricks may be useful for administrating db, apps etc... I would like to add related posts into my blog contains tips and tricks.

Tips & Tricks 1 : Give debug privilege to user.

Any user with whom you need debug procedure,function or package , related user must have

DEBUG CONNECT SESSION

DEBUG ANY PROCEDURE

privileges.

In order to give these ,

grant DEBUG CONNECT SESSION userx;

grant DEBUG ANY PROCEDURE userx

UPGAST-00224:The specified database does not contain any schemas for Oracle MDS ...

From Oracle Error Message documentation,

UPGAST-00224: The specified database does not contain any schemas for {0} or the database user lacks privilege to view the schemas.

Cause: The database you have specified does not contain any schemas registered as belonging to the component you are upgrading, or else the current database user lacks privilege to query the contents of the schema version registry.

Action: Verify that the database contains schema entries in schema version registry. If it does not, specify a different database. Verify that the user has DBA privilege. Connect to the database as DBA.

Level: 1

Type: ERROR

Impact: Upgrade

While upgrading OBIEE from 11.1.1.6 to 11.1.1.7 i have encountered UPGAST-00224 error at stage of PSA for the update of MDS schema .

I was doing upgrade process at TEST enviroment and related MDS + BIPLATFORM schemas have been imported from LIVE database to TEST database by changing schema names in order to sepearate TEST and LIVE . My original schema names were OBI_MDS and OBI_BIPLATFORM  and changed to OBIUPG_MDS ,OBIUPG_BIPLATFORM After done this process system.SCHEMA_VERSION_REGISTRY$ must be updated with new schema information.

To do this , system.SCHEMA_VERSION_REGISTRY$ can be exported and imported from live into TEST , or related MDS and BIPLATFORM data can be inserted or updated  at TEST enviroment.

Firefox Update comes with sec_error_ca_cert_invalid (Security Error)

After I have patched firefox to version 31.0 when i try to connect  Enterprise Manager 12c , browser throws an SSL error : sec_error_ca_cert_invalid 

An error occurred during a connection to https://<OMS host>:port . Issuer certificate is invalid. (Error code: sec_error_ca_cert_invalid)

Because of certificate of grid control is self generated and not signed by a  trusted Certificate Authority , browser shows an error that it does not recognise the certificate . To skip this situation there are 2 ways that can be done.

1. Third Party certificate from well know certificate authority  can be used.

Third party certificates can be obtained from a well-known Certificate Authority and imported into the OMS and Agents. 

2. Import Self-signed certificate into browsers certificate store

To recognise self-signed certificate from any browser , this certificate can be added browser store.Whenever grid control URL is called from explorer or chrome , these browsers can continue to work even if you accept risk.But firefox can not. To skip firefox issue i have used "internet explorer" to get related certificate and imported it into firefox store by using following steps,

1.Clear SSL cache from "Internet Options > Content Tab > Clear SSL State"

2.Remove any certificate entry from "Internet Options > Content Tab > Certificates" related with your Grid Console or Enterprise Manager Cloud control in following tabs

Personal

Other People

Intermediate Certification Authorities

Trusted Root Certification Authorities

Trusted Publishers

Untrusted Publishers

3. Click Ok and close the browser

4. Open browser and go to https://<OMS host>:port/em

5. Continue to this website (not recommended) is selected

6. After this login screen is opened but "Certifacate Error" link is came up beside address bar of explorer. Click this link.

7. Click "View certificates"

8. Goto "Certification Path" tab 

9. Select top or root certificate and click "View Certificate"

10. Click "Install Certificate"

11. Click "Next" and choose "Place all certificates in the following store" in the following screen.

10. Click "Browse" button and select "Trusted Root Certification Authorities"

11. Click "Next" and "Finish" button sequentially

12. Click "Yes" on Last "Security Warning" screen and "The import was successful" message comes up

13. After reopen the browser and calling OMS url https://<OMS host>:port/em certificate error does not occur again.

Now , i have imported self-signed certificate into internet explorer certificate store and i can export it to import into firefox store.

1. From internet explorer choose "Internet Options > Content Tab > Certificates" and goto "Trusted Root Certification Authorities" 

2. Select your certificate regarding your OMS host name in "Issued To" column.

3. Click export 

4. Click Next

5. Select "DER encoded binary X.509 (.CER)

6. Click next and give a name as a file name for certificate

7. Click next and finish.

8. Open firefox

9. Goto "Advanced > Certificates > View Certificates"

10. Goto "Authorities" tab and click "Import" button

11. Select your exported certification file with open file editor and click "Ok" without selecting any purposes.

12. Reopen firefox and enter OMS url again.

Login screen opened .

Estimate size of index using explain plan

Today i have learned cool way to define after index rebuilt what will the index size be ? This method , estimating index size is based on explain plan . Not only rebuild process , additionally creation of index is  involved .

Basically ,

1. create table tbl1 as select * from dba_objects;
2. insert into tbl1 select * from tbl1 -- 2 times
3. commit;
3. call dbms_stats.gather_table_stats('USER','TBL1');
4. explain plan for create index user.tbl_idx1 on tbl1(object_name);
5. commit;
6. select * from table (dbms_xplan.display);

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |          |   414K|  9707K|   441   (3)| 00:00:03 |
|   1 |  INDEX BUILD NON UNIQUE| TBL_IDX1 |       |       |            |          |
|   2 |   SORT CREATE INDEX    |          |   414K|  9707K|            |          |
|   3 |    TABLE ACCESS FULL   | TBL1     |   414K|  9707K|   334   (3)| 00:00:02 |
-----------------------------------------------------------------------------------

Note
-----
   - estimated index size: 16M bytes


6. create index user.tbl_idx1 on tbl1(object_name);
7. select bytes/1024 from dba_segments where segment_name='TBL_IDX1'

Size : 16384K

As a result , actual size and estimate size are so close . 

CArlos Sierra has a great article and script about this method which can be applied to whole system , schema ,table or single index . Object create DDL is used with explain plan statement to detect approximate size like this,

declare
  v_ddl clob;
begin
  select replace(dbms_metadata.get_ddl(object_type => 'INDEX',name => 'TBL_IDX1'),chr(10),' ') into v_ddl   FROM DUAL;

  execute immediate 'explain plan for '||v_ddl;
  commit;
end ;

After script execution,below sql gives segment size and estimated size

select object_name,object_owner,info estimatedSize,ds.BYTES segmentSize
  from (select p.object_name,p.object_owner,extractvalue(value(d), '/info/@type') type,
               extractvalue(value(d), '/info') info
          from plan_table p,
               table(xmlsequence(extract(xmltype(p.other_xml), '/*/info'))) d
         where p.other_xml is not null)X,dba_segments ds
 where type = 'index_size'
   and ds.owner=X.object_owner
   and ds.segment_name=X.object_name

OBJECT_NAME    OBJECT_OWNER    ESTIMATEDSIZE    SEGMENTSIZE
TBL_IDX1           USER                     16777216    16777216

This post was based on below blog posts

http://carlos-sierra.net/2014/07/18/free-script-to-very-quickly-and-cheaply-estimate-the-size-of-an-index-if-it-were-to-be-rebuilt/

http://richardfoote.wordpress.com/2014/04/24/estimate-index-size-with-explain-plan-i-cant-explain/

IAS_ADMIN account password reset

When you do not know ias_admin password or forget it , you can reset its password. Ias_admin is used for 

Application Server for administration. For 10.1.2.3.0 below steps can be used in order to change or reset password.

1.Shutdown Application Server console

$ORACLE_HOME/bin/emctl stop iasconsole

2.jazn-data.xml file should be backed up located in $ORACLE_HOME/sysman/j2ee/config

cd  $ORACLE_HOME/sysman/j2ee/config

cp jazn-data.xml jazn-data.xml.bak

3.With vi editor find <name>ias_admin</name>  and remove the credentials line under <name> tag

<name>ias_admin</name>

<credentials>{903}+7qAmdyLk7lsa7CxvLpFeDesddzaJWS3qEA/AvKpA+bw=</credentials>

4.Following command is used to reset ias_admin account password

$ORACLE_HOME/bin/emctl set password reset <newpassword>

5.Start Application Server console

$ORACLE_HOME/bin/emctl start iasconsole

Password can be changed from command line if you know current ias_admin account password,

$ORACLE_HOME/bin/emctl set password <oldpassword> <newpassword>