ORA-39006: internal error; ORA-39213: Metadata processing is not available;

> expdp schemas=...... dumpfile=.........

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39213: Metadata processing is not available

Solution:

SQL> execute sys.dbms_metadata_util.load_stylesheets;

Kill all the blocking sessions (Real Application Cluster)

Give it a run as sysdba, from any instance:
begin
for obj in (
        SELECT s.inst_id,
               s.sid,
               s.serial#,
               p.spid,
               s.username,
               s.program
               FROM   gv$session s JOIN gv$process p 
                      ON p.addr = s.paddr 
                      AND p.inst_id = s.inst_id
               WHERE   blocking_session is not null 
                       and s.type != 'USER')
    loop
    execute immediate 'alter system kill session '''||obj.sid||','||obj.serial#||',@'||obj.inst_id||''''||' ';
end loop;
end;
/

Grant select on all tables of a schema to another user

Just replace the "user1" and "user2" names. User2 is the grantee and user1 the owner.

BEGIN
FOR c IN (SELECT owner, table_name FROM all_tables WHERE owner='user1') LOOP
      EXECUTE IMMEDIATE 'grant select on '||c.owner||'.'||c.table_name||' to user2';
   END LOOP;
END;

and for all privileges: 

BEGIN
FOR c IN (SELECT owner, table_name FROM all_tables WHERE owner='user1') LOOP
      EXECUTE IMMEDIATE 'grant all privileges on '||c.owner||'.'||c.table_name||' to user2';
   END LOOP;
END;

ORA-31202: DBMS_LDAP: LDAP client/server error: Sizelimit exceeded

The problem is a parameter of the LDAP Server: MaxPageSize.

On the server:

c:\>ntdsutil.exe
ntdsutil: Ldap policies
ldap policy: connections
server connections: Connect to server ServerName
Binding to ServerName ...
Connected to ServerName using credentials of locally logged on user
server connections: q
ldap policy: Show Values
Policy                          Current(New)

MaxPoolThreads              8
MaxDatagramRecv             1024
MaxReceiveBuffer            10485760
InitRecvTimeout             120
MaxConnections              5000
MaxConnIdleTime             900
MaxActiveQueries            20
MaxPageSize                 1000
MaxQueryDuration            120
MaxTempTableSize            10000
MaxResultSetSize            262144
MaxNotificationPerConn      5
ldap policy: set MaxPageSize to 100000
ldap policy: Commit Changes
ldap policy: Show Values

Policy                          Current(New)

MaxPoolThreads              8
MaxDatagramRecv             1024
MaxReceiveBuffer            10485760
InitRecvTimeout             120
MaxConnections              5000
MaxConnIdleTime             900
MaxActiveQueries            20
MaxPageSize                 100000
MaxQueryDuration            120
MaxTempTableSize            10000
MaxResultSetSize            262144
MaxNotificationPerConn      5

ldap policy: q
ntdsutil: q
Disconnecting from ServerName

How to kill all the sessions of a user, from all the instances of a RAC

Shortly, connect as sys and run the following script:

begin
for objects in (select sid, serial#,inst_id from gv$session where username='USER_TO_KILL') loop
EXECUTE IMMEDIATE 'alter system kill session '''||objects.sid||','||objects.serial#||',@'||objects.inst_id||''''||' immediate';
end loop;
end;

How to authenticate with LDAP in Oracle APEX

First, create an Access Control List (ACL). Let's assume the owner of the application is 'userapp'. The ACL will pe granted to APEX04000 user (first, check the name of the apex user with 'select username from dba_users'), and then a privilege will be added for USERAPP. The ip of the LDAP server is 192.168,132,12, but, of course, better way is to use the name instead of the ip. Your call. The LDAP port is 389.

 BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'ldap_acl_file.xml',
    description  => 'ACL to grant access to LDAP server',
    principal    => 'APEX_040000',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'ldap_acl_file.xml',
    host        => '192.168.132.12',
    lower_port  => 389,
    upper_port  => NULL);

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl         => 'ldap_acl_file.xml',
    principal   => 'USERAPP',
    is_grant    => true,
    privilege   => 'connect',
    position    => NULL,
    start_date  => NULL,
    end_date    => NULL);
                                     
  COMMIT;
END;
/                                     
Now, under the APPUSER scheme let's create the authentication function:

CREATE OR REPLACE FUNCTION appuser.ldap_auth(
  p_username IN VARCHAR2,
  p_password IN VARCHAR2
)
RETURN BOOLEAN IS
  l_ldap_host     VARCHAR2(256) := '192.168.132.12';
  l_ldap_port     VARCHAR2(256) := '389';
  l_ldap_base     VARCHAR2(256) := 'dc=domain,dc=ro';
  l_dn_prefix     VARCHAR2(100) := ''; -- here you can mention
                                       -- some peculiar group, 
                                       -- under your LDAP directory.

  l_retval        PLS_INTEGER;
  l_session       DBMS_LDAP.session;
BEGIN
  -- Choose to raise exceptions.
  DBMS_LDAP.use_exception := TRUE;
 
  -- Connect to the LDAP server.
  l_session := DBMS_LDAP.init(hostname => l_ldap_host,
                              portnum  => l_ldap_port);
 
  l_retval := DBMS_LDAP.simple_bind_s(ld     => l_session,
                                      dn     => l_dn_prefix || p_username,
                                      passwd => p_password);
                                     
  -- No exceptions mean you are authenticated.
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    -- Exception means authentication failed.
    l_retval := DBMS_LDAP.unbind_s(ld => l_session);
    APEX_UTIL.set_custom_auth_status(p_status => 'Incorrect username and/or password');
    RETURN FALSE;   
END;
/

Now, it's time to use the function. Make an authorization scheme, under the Application Builder -> Shared Components -> Authentication Schemes (don't use the LDAP template scheme, just make an ordinary authentication scheme) and the most important thing to do is to put in the  Authentication Function box the following text: "return ldap_auth;"
And that's all, you will authenticate with the LDAP credentials.

Recover a standby database from archived logs gap (ORA-16783: cannot resolve gap for database ....)

First, identify de SCN where the standby has lost the pace


 select current_scn from gv$database;

 CURRENT_SCN
-----------
 2320214727


Of course, the SCN of the primary database is higher and the gap could be pretty big.


dgmgrl

 

DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys

Password:

Connected.

DGMGRL> show configuration;

Configuration - DGC

  Protection Mode: MaxAvailability

  Databases:

    primary - Primary database

      Error: ORA-16810: multiple errors or warnings detected for the database

     - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

ERROR

DGMGRL> edit database 'standby' set state='APPLY-OFF';

on the primary:

rman target /              

RMAN> run

2> {

3> allocate channel ch1 type disk;

4> allocate channel ch2 type disk;

5> backup incremental from scn 2320214726 database format '/home/oracle/standby_%d_%t_%c_%p';

6> }

copy the backup pieces on the standby file system

on the standby:

RMAN> catalog start with '/home/oracle/bkp';

(of course, be careful with the locations, use your own instead of ....)

RMAN> run

2> {

3> allocate channel ch1 type disk;

4> recover database noredo;

5> }

 on the primary:

SQL> alter database create standby controlfile as '/home/oracle/bkp/standby.ctl';

Database altered.

copy the controlfile on the standby

stop the standby database, doesn't matter how

start it nomount

on the standby:

rman target /
RMAN> restore controlfile from '/home/oracle/bkp/standby.ctl';


alter database mount

RMAN> CATALOG START WITH '+standby_DATA1/standby/DATAFILE/';

RMAN> SWITCH DATABASE TO COPY;

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4;
....

$ dgmgrl    

DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys

Password:

Connected.

DGMGRL> edit database 'standby' set state='APPLY-ON';

Succeeded.

sqlplus / as sysdba

> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

Now everything should be ok

12c Management Agent Silent Installation

Grid Control hostname: grid.

The managed host: standby (this was the purpose, in my case, a standby server).

 Put the proper settings into /etc/hosts files, on both machines.

On the grid:

cd /u01/app/oracle/Middleware/oms

cd bin

./emcli login -username=sysman -password=pass

Login successful

Synchronize EMCLI

./emcli sync

Synchronized successfully

Check the platforms for which the Management Agent software is available on the OMS host

 ./emcli get_supported_platforms

Getting list of platforms …

Check the logs at /u01/app/oracle/Middleware/oms/bin/agent.log

About to access self-update code path to retrieve the platforms list..

Getting Platforms list  …

———————————————–

Version = 12.1.0.1.0

 Platform = Linux x86-64

———————————————–

Platforms list displayed successfully.

Download the Management Agent software from Oracle Software Library to a temporary directory on the OMS host

 ./emcli get_agentimage -destination=/u01/app/oracle/agent_software -platform=”Linux x86-64″ -version=”12.1.0.1.0″

Platform:Linux x86-64

Destination:/u01/app/oracle/agent_software

 === Partition Detail ===

Space free : 14 GB

Space required : 1 GB

Check the logs at /u01/app/oracle/agent_software/get_agentimage_2012-04-15_22-50-00-PM.log

Setting property ORACLE_HOME to:/u01/app/oracle/Middleware/oms

calling pulloneoffs with arguments:/u01/app/oracle/Middleware/oms/u01/app/oracle/Middleware/oms/sysman/agent/12.1.0.1.0_AgentCore_226.zip12.1.0.1.0linux_x64

Check this logs for more information: /u01/app/oracle/Middleware/oms/sysman/prov/agentpush/logs

[oracle@gridcontrol agent_software]$ cd /u01/app/oracle/agent_software
[oracle@gridcontrol agent_software]$ ll
total 285476
-rw-r----- 1 oracle oinstall 292032498 Jun 23 06:49 12.1.0.1.0_AgentCore_226.zip
-rw-r--r-- 1 oracle oinstall      1806 Jun 23 06:49 get_agentimage_2015-06-23_06-48-39-AM.log
[oracle@gridcontrol agent_software]$

Transfer the downloaded zip file to the host where you would like to install the 12c management agent

scp  12.1.0.1.0_AgentCore_226.zip oracle@standby:/u01/app/oracle/agent_software

Unzip the file on the target host

Check the directory contents

[oracle@standby ~]$ cd /u01/app/oracle/agent_software
[oracle@standby agent_software]$ ll
total 571528
-rw-r----- 1 oracle oinstall 292032498 Jun 23 14:34 12.1.0.1.0_AgentCore_226.zip
-rwxr-xr-x 1 oracle oinstall   7630039 Jun 23 13:48 12.1.0.1.0_PluginsOneoffs_226.zip
-rw-rw-r-- 1 oracle oinstall 284323730 Feb 11  2012 agentcoreimage.zip
-rw-r--r-- 1 oracle oinstall         0 Jun 23 15:08 agentDeploy_2015-06-23_15_08_19.err
-rw-r--r-- 1 oracle oinstall    137910 Jun 23 15:08 agentDeploy_2015-06-23_15_08_19.log
-rw-r--r-- 1 oracle oinstall         0 Jun 23 15:11 agentDeploy_2015-06-23_15_11_20.err
-rw-r--r-- 1 oracle oinstall    125429 Jun 23 15:11 agentDeploy_2015-06-23_15_11_20.log
-rw-r--r-- 1 oracle oinstall         0 Jun 23 15:12 agentDeploy_2015-06-23_15_12_06.err
-rw-r--r-- 1 oracle oinstall    125429 Jun 23 15:12 agentDeploy_2015-06-23_15_12_06.log
-rw-r--r-- 1 oracle oinstall         0 Jun 23 15:13 agentDeploy_2015-06-23_15_13_03.err
-rw-r--r-- 1 oracle oinstall     70832 Jun 23 15:13 agentDeploy_2015-06-23_15_13_03.log
-rwxrwxr-x 1 oracle oinstall     17998 Feb 11  2012 agentDeploy.sh
-rw-rw-r-- 1 oracle oinstall       145 Feb 11  2012 agentimage.properties
-rw-rw-r-- 1 oracle oinstall      4039 Jun 23 15:13 agent.rsp
drwxr-xr-x 7 oracle oinstall      4096 Jun 23 13:48 plugins
-rwxr-xr-x 1 oracle oinstall       223 Jun 23 13:48 plugins.txt
-rwxr-xr-x 1 oracle oinstall    145976 Feb 11  2012 unzip

Edit the response file agent.rsp (mind the proper password)

OMS_HOST=”grid”

EM_UPLOAD_PORT=”4900″

AGENT_REGISTRATION_PASSWORD=”password”

AGENT_INSTANCE_HOME=”/u01/app/oracle/agent12g/agent_inst”

AGENT_PORT=”3872″

ORACLE_HOSTNAME=”standby”

Run the agent deployment script

> ./agentDeploy.sh AGENT_BASE_DIR=/u01/app/oracle/agent12g RESPONSE_FILE=/u01/app/oracle/agent_software/agent.rsp 

After tons of feedback, assuming that all is ok:

>  su -

>  /u01/app/oracle/agent12g/core/12.1.0.1.0/root.sh

Finished product-specific root actions.

/etc exist

Finished product-specific root actions.

Check the status of the agent on the target host where it has been deployed

>cd /u01/app/oracle/agent12g/core/12.1.0.1.0/bin

> ./emctl status agent

........................... bla bla ............

—————————————————————

Agent is Running and Ready

Enjoy!

VMWare tools problem: "host software version may not be compatible with the upgrader"

Solution:

tar -xzf VMwareTools-9.4.10-2068191.tar.gz

cd vmware-tools-distrib

./vmware-install.pl

Oracle trigger: capturing one less column of a table

If you want to fire a trigger when any of columns but one is updated, the solution ca be to put all the wanted columns in the script, but there is an easier way:

CREATE OR REPLACE TRIGGER ....... before UPDATE or delete ON
 .......... REFERENCING old AS old new as new FOR EACH ROW
declare
...............................
BEGIN
...........................
if updating ('unwanted_column') then  
         null;
             else
    ........the action of trigger..........;
end if;
exception
WHEN exc THEN
............
    rollback;
END;
/



Be aware that if you use 

if not updating ('unwanted_column') then
   ........the action of trigger..........;
end if;

you'll notice, when you update two or more columns, and the unwanted column is not included, anyway, the condition will not be satisfied and the action will not be done. Only in the case of updating one column will succeed, which is not a solid solution.
So, use the condition somehow that the action will be on the ELSE   branch.
Enjoy!

ORA-00054: resource busy and acquire with NOWAIT specified

First of all, i must say this message maybe is not really scary, because you might think with some patience the problem can be overridden. Well, there is a possible scenario giving some headaches to a DBA:

alter table  tn disable constraint tnc;

Everything is fine 'till now, you have disabled a constraint in order to update some data.

After all these updates, and after you verify the data is compliant with the bussiness rules enforced by the constraint tnc:

alter table  tn enable constraint tnc;

 ORA-00054: resource busy and acquire with NOWAIT specified

Now you are in serious trouble. Working hours mean lots of data inserted in your tables, not anymore protected by that constraint, and the users can broke the bussiness rule from now.

Of course, you may say the same problem can occur during that few seconds while you ran the two DDL commands and the DML commands which modified th table tn. Agree, was not a good practice way of doing things, but this kind of manipulating data can be seen very often in practice, when a DBA or a developer doesn't care or can't wait for a maintenance window, in order to manipulate data, ignoring the possibility of any other session, locking a table.

Unfortunately, there isn't any complete solution for this problem. You can run the following code (for Oracle <10g), which tries for n minutes to achieve the lock on the table and run the DDL command:

DECLARE

  is_ok  BOOLEAN;

  t_l    EXCEPTION;

  PRAGMA EXCEPTION_INIT(t_l, -00054);

  start_time     DATE; -- the moment of the first cycle

  no_min number; --how many minutes the program will run, trying to

                 -- achieve the lock

BEGIN

  is_ok := FALSE;

  SELECT SYSDATE INTO start_time FROM DUAL;

  WHILE (NOT is_ok) AND (SYSDATE < start_time + no_min/24/60)

  LOOP

    BEGIN

      EXECUTE IMMEDIATE ('alter table  tn enable constraint tnc');

        is_ok := TRUE;

      DBMS_OUTPUT.PUT_LINE('Succes!');

    EXCEPTION

    WHEN t_l THEN

      NULL;

      DBMS_LOCK.SLEEP(0.1);

    END;

  END LOOP;

  IF is_ok = FALSE THEN

    DBMS_OUTPUT.PUT_LINE('the constraint is still disabled!');

  END IF;

END;

/

For Oracle versions >10g, there is a parameter, ddl_lock_timeout , doing the same job like the code from above. Any DDL command issued against a table will try for a number of seconds equal to ddl_lock_timeout.

And there is another solution, involving   the QUIESCE concept, but i don't like it and i don't recommend it to anyone.

Good luck!

The ESC key problem when you putty a linux machine

You are under vi or vim and, after some edit of the file, try to exit. ESC key doesn't work.
Instead of ESC, press ctrl+[

That's all. Enjoy!

How to change vnc server password

Shortly:

su - name_of_the_user    

(vnc server has different settings for different users)

vncpasswd

sudo service vncserver restart

or 

su -
service vncserver restart