Lucy's thoughts ...

ADG_REDIRECT_DML in Oracle Active Data Guard 19c

2022-10-27T10:25:00.001+02:00

Happy to know that my request for enhancement will be implemented in new coming release Oracle 23.1 :)

I have requested to have an option to completely disable ADG_REDIRECT_DML on the session level for regular database user from the standby site (in Oracle Active Data Guard).

On one site good feature ADG_REDIRECT_DML in ADG in 19c environment (if you want to use it), but on the other site it can be also "dangerous".

Even if the default value of the ADG_REDIRECT_DML is FALSE, regular user without any special privileges is able to enable this feature on the session level and perform changes on the primary database through standby database.

As this is not possible to completely disable for regular users on the session level and protect primary site, I asked Oracle for enhancement, which was already confirmed by development team in the version 23.1.

As 23c wasn't yet released I had to prepare workaround. If somebody wants to know how to do it, just let me know, I can write an article about this.

ORA-15032 ORA-59303 ORA-15221: ASM operation requires compatible.asm of ...

2021-04-23T15:17:00.000+02:00

During upgrading GI (already visible in 11g/12c/19c) and during execution of rootupgrade.sh following errors was visible:

CRS-6705: Oracle Clusterware Release Version ('19.0.0.0.0') does not match Software Version ('12.1.0.2.0'). Oracle Clusterware cannot be started.

CRS-4000: Command Start failed, or completed with errors.

2021/04/20 09:42:30 CLSRSC-318: Failed to start Oracle OHASD service
Died at /u01/app/oracle/product/19.0.0.0_gi/crs/install/crsupgrade.pm line 6269.

root@oralin1:/u01/app/oracle/product/19.0.0.0_gi

Result was that some disk groups were not mounted:

SQL> select NAME, STATE, COMPATIBILITY from v$asm_diskgroup;

NAME  STATE 		COMPATIBILITY
----- ------------ ---------------
REDO  MOUNTED     	19.0.0.0.0
DATA  DISMOUNTED  	0.0.0.0.0
FRA   DISMOUNTED  	0.0.0.0.0

When trying to mount it:

SQL> alter diskgroup FRA mount;
alter diskgroup FRA mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-59303: The attribute compatible.asm (10.1.0.0.0) of the 
           diskgroup being mounted should be 11.2.0.2.0 or higher.
ORA-15221: ASM operation requires compatible.asm of 11.1.0.0.0 
           or higher

Solution:

SQL> alter diskgroup FRA mount restricted;

Diskgroup altered.

SQL> alter diskgroup FRA set attribute 'compatible.asm'='19.0.0.0.0';

Diskgroup altered.

SQL> alter diskgroup FRA dismount;

Diskgroup altered.

SQL> alter diskgroup DATA mount restricted;

Diskgroup altered.

SQL> alter diskgroup DATA set attribute 'compatible.asm'='19.0.0.0.0';

Diskgroup altered.

SQL> alter diskgroup DATA dismount;

Diskgroup altered.

SQL> alter diskgroup FRA mount;

Diskgroup altered.



SQL>   select NAME, STATE, COMPATIBILITY  from  v$asm_diskgroup;

NAME                           STATE       COMPATIBILITY
------------------------------ ----------- -------------------
REDO                           MOUNTED     19.0.0.0.0
DATA                           MOUNTED     19.0.0.0.0
FRA                            MOUNTED     19.0.0.0.0

Installation of new PDB

2021-04-23T14:59:00.000+02:00

Type	Note
Create a PDB by using the seed	Use the create_pdb_from_seed clause to create a PDB by using the seed in the multitenant container database (CDB) as a template. The files associated with the seed are copied to a new location and the copied files are then associated with the new PDB.
Create a PDB by cloning an existing PDB or non-CDB (local or remote databases via DBLink)	Use the create_pdb_clone clause to create a PDB by copying an existing PDB or non-CDB and then plugging the copy into the CDB. The files associated with the existing PDB or non-CDB are copied to a new location and the copied files are associated with the new PDB.
Create a PDB by plugging an unplugged PDB into a CDB/td>	Use the create_pdb_from_xml clause to plug an unplugged PDB or a non-CDB into a CDB, using an XML metadata file.

Create a PDB by using the seed as a template

CREATE PLUGGABLE DATABASE PDB
ADMIN USER Admin IDENTIFIED BY oracle
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/pdbseed/','/u01/app/oracle/oradata/PDB/') STORAGE (MAXSIZE 2G)
PATH_PREFIX = '/u01/app/oracle/oradata/PDB/';

SQL> !mkdir /u01/app/oracle/oradata/cdb/pdb1

SQL> create pluggable database pdb1 admin user pdb1_admin identified by oracle roles=(CONNECT)
create_file_dest='/u01/app/oracle/oradata/cdb/pdb1';

Pluggable database created.

SQL> select con_id, name, open_mode from v$pdbs;

	CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
		 2 PDB$SEED                       READ ONLY
		 3 PDB                            READ WRITE
		 4 PDB1                           MOUNTED

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> select con_id, name, open_mode from v$pdbs;

	CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
		 2 PDB$SEED                       READ ONLY
		 3 PDB                            READ WRITE
		 4 PDB1                           READ WRITE

SQL> CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb1_adm IDENTIFIED BY oracle
  ROLES=(DBA)
  DEFAULT TABLESPACE test
	DATAFILE '/u01/app/oracle/oradata/cdb/pdb1/test01.dbf' SIZE 5M AUTOEXTEND ON
  file_name_convert=('/u01/app/oracle/oradata/cdb/pdbseed/','/u01/app/oracle/oradata/cdb/pdb1/');  

Pluggable database created.

SQL> col pdb_name for a10
SQL> select pdb_name, status from dba_pdbs;

PDB_NAME   STATUS
---------- ---------
PDB        NORMAL
PDB$SEED   NORMAL
PDB1       NEW

SQL> select con_id, name, open_mode from v$pdbs;

	CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
		 2 PDB$SEED                       READ ONLY
		 3 PDB                            READ ONLY
		 4 PDB1                           MOUNTED

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> select pdb_name, status from dba_pdbs;

PDB_NAME   STATUS
---------- ---------
PDB        NORMAL
PDB$SEED   NORMAL
PDB1       NORMAL

SQL>  select con_id, name, open_mode from v$pdbs;

	CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
		 2 PDB$SEED                       READ ONLY
		 3 PDB                            READ ONLY
		 4 PDB1                           READ WRITE

SQL> alter session set container=pdb1;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb/pdb1/system01.dbf
/u01/app/oracle/oradata/cdb/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb/pdb1/test01.dbf

Cloning a Local PDB With the FILE_NAME_CONVERT:

	CREATE PLUGGABLE DATABASE pdb1 FROM pdb
	  FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/cdb/pdb/', '/u01/app/oracle/oradata/cdb/pdb1/')
	  NOLOGGING;

	SQL> select con_id, name, open_mode from v$pdbs;

		CON_ID NAME                           OPEN_MODE
	---------- ------------------------------ ----------
			 2 PDB$SEED                       READ ONLY
			 3 PDB                            MOUNTED

	SQL> alter pluggable database pdb open;

	Pluggable database altered.

	SQL> select con_id, name, open_mode from v$pdbs;

		CON_ID NAME                           OPEN_MODE
	---------- ------------------------------ ----------
			 2 PDB$SEED                       READ ONLY
			 3 PDB                            READ WRITE

	SQL> CREATE PLUGGABLE DATABASE pdb1 FROM pdb
	  FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/cdb/pdb/', '/u01/app/oracle/oradata/cdb/pdb1/')
	  NOLOGGING;
	  2    3

	Pluggable database created.

	SQL> SQL> select con_id, name, open_mode from v$pdbs;

		CON_ID NAME                           OPEN_MODE
	---------- ------------------------------ ----------
			 2 PDB$SEED                       READ ONLY
			 3 PDB                            READ WRITE
			 4 PDB1                           MOUNTED

	SQL>  alter pluggable database pdb1 open;

	Pluggable database altered.

	SQL> select con_id, name, open_mode from v$pdbs;

		CON_ID NAME                           OPEN_MODE
	---------- ------------------------------ ----------
			 2 PDB$SEED                       READ ONLY
			 3 PDB                            READ WRITE
			 4 PDB1                           READ WRITE

Cloning a Local PDB Without Cloning Its Data:

Restrictions:
The NO DATA clause is only valid is the the source PDB doesn't contain any of the following.

Index-organized tables
Advanced Queue (AQ) tables
Clustered tables
Table clusters

	SQL> ALTER PLUGGABLE DATABASE pdb OPEN READ ONLY;

	Pluggable database altered.

	SQL> select con_id, name, open_mode from v$pdbs;

		CON_ID NAME                           OPEN_MODE
	---------- ------------------------------ ----------
			 2 PDB$SEED                       READ ONLY
			 3 PDB                            READ ONLY
	SQL> CREATE PLUGGABLE DATABASE pdb1 FROM pdb FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb/pdb/','/u01/app/oracle/oradata/cdb/pdb1/') NO DATA;
	CREATE PLUGGABLE DATABASE pdb1 FROM pdb FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb/pdb/','/u01/app/oracle/oradata/cdb/pdb1/') NO DATA
	*
	ERROR at line 1:
	ORA-65161: Unable to create pluggable database with no data


	SQL> !oerr ora 65161
	65161, 00000, "Unable to create pluggable database with no data"
	// *Cause:   An attempt was made to clone a pluggable database which contains
	//           some clustered tables or index organized tables or advanced
	//           queue tables.
	// *Action:  Retry the clone operation after dropping all such objects from the
	//           source pluggable database.

Cloning a Remote PDB or Non-CDB

Prerequisites:

The current user must have the CREATE PLUGGABLE DATABASE system privilege in the root of the CDB that will contain the target PDB.
The source PDB or source non-CDB must be in open read-only mode.
A database link must enable a connection from the CDB that will contain the target PDB to the remote source. If the source is a remote PDB, then the database link can connect to either the root of the remote CDB or to the remote source PDB.
The user that the database link connects with at the remote source must have the CREATE PLUGGABLE DATABASE system privilege in the source PDB or in the non-CDB.
If the database link connects to the root in a remote CDB, then the user that the database link connects with must be a common user.
The source and target platforms must meet these requirements:
- They must have the same endianness.
- The database options installed on the source platform must be the same as, or a subset of, the database options installed on the target platform.
If you are creating a PDB by cloning a non-CDB, then both the CDB and the non-CDB must be running Oracle Database 12c Release 1 (12.1.0.2) or later.

Remote CDB database:

	CREATE PLUGGABLE DATABASE pdb1 FROM cdb1_pdb@cdb1_pdb_link
	file_name_convert=('/u01/app/oracle/oradata/cdb1/cdb1_pdb/','/u01/app/oracle/oradata/cdb/pdb1/');

Example with setup for cloning Remove CDB database:

	SQL> create database link cdb1_pdb_link connect to test identified by oracle using 'CDB1_PDB_SRV';

	Database link created.

	SQL>  select sysdate from dual@cdb1_pdb_link;

	SYSDATE
	---------
	13-MAR-18


	CREATE PLUGGABLE DATABASE pdb1 FROM cdb1_pdb@cdb1_pdb_link
	file_name_convert=('/u01/app/oracle/oradata/cdb1/cdb1_pdb/','/u01/app/oracle/oradata/cdb/pdb1/');


	cdb1:
	SQL> create tablespace test_tbs datafile '/u01/app/oracle/oradata/cdb1/cdb1_pdb/test_tbs_01.dbf' size 5m;

	Tablespace created.

	SQL> alter user test default tablespace test_tbs;

	User altered.

	SQL> create table test.test_table (name varchar2(20));

	Table created.

	SQL> alter user test quota unlimited on test_tbs;

	User altered.

	SQL> insert into test.test_table (name) values ('test entry');

	1 row created.

	SQL> commit;

	Commit complete.



	cdb:
	SQL> CREATE PLUGGABLE DATABASE pdb1 FROM cdb1_pdb@cdb1_pdb_link
	file_name_convert=('/u01/app/oracle/oradata/cdb1/cdb1_pdb/','/u01/app/oracle/oradata/cdb/pdb1/');
	  2

	Pluggable database created.

	SQL> SQL> select con_id, name, open_mode from v$pdbs;

		CON_ID NAME                           OPEN_MODE
	---------- ------------------------------ ----------
			 2 PDB$SEED                       READ ONLY
			 3 PDB                            READ ONLY
			 4 PDB1                           MOUNTED

	SQL> col name for a20
	SQL> desc dba_pdbs
	 Name                                      Null?    Type
	 ----------------------------------------- -------- ----------------------------
	 PDB_ID                                    NOT NULL NUMBER
	 PDB_NAME                                  NOT NULL VARCHAR2(128)
	 DBID                                      NOT NULL NUMBER
	 CON_UID                                   NOT NULL NUMBER
	 GUID                                               RAW(16)
	 STATUS                                             VARCHAR2(9)
	 CREATION_SCN                                       NUMBER
	 VSN                                                NUMBER
	 LOGGING                                            VARCHAR2(9)
	 FORCE_LOGGING                                      VARCHAR2(3)
	 FORCE_NOLOGGING                                    VARCHAR2(3)
	 CON_ID                                    NOT NULL NUMBER

	SQL> col pdb_name for a20
	SQL> select pdb_name, status from dba_pdbs;

	PDB_NAME             STATUS
	-------------------- ---------
	PDB                  NORMAL
	PDB$SEED             NORMAL
	PDB1                 NEW

	SQL> alter pluggable database pdb1 open;

	Pluggable database altered.

	SQL>  select pdb_name, status from dba_pdbs;

	PDB_NAME             STATUS
	-------------------- ---------
	PDB                  NORMAL
	PDB$SEED             NORMAL
	PDB1                 NORMAL


	SQL> alter session set container=pdb1;

	Session altered.

	SQL> select * from test.test_table;

	NAME
	--------------------
	test entry

Remote non-CDB database:

	CREATE PLUGGABLE DATABASE pdb1 FROM orcl@orcl_link
	file_name_convert=('/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/cdb/pdb1/');

Example with setup for cloning Remove non-CDB database:

	SQL> create database link orcl_link connect to test identified by oracle using 'ORCL_SRV';

	Database link created.

	SQL> select sysdate from dual@orcl_link;

	SYSDATE
	---------
	13-MAR-18

	SQL> CREATE PLUGGABLE DATABASE pdb1 FROM orcl@orcl_link
	file_name_convert=('/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/cdb/pdb1/');

	Pluggable database created.

	@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

	SQL> select name, open_mode from v$pdbs;

	NAME                           OPEN_MODE
	------------------------------ ----------
	PDB1                           MOUNTED

	1 row selected.

	SQL> alter pluggable database pdb1 open;

	Pluggable database altered.

	SQL> select * from test.test_table;

	NAME
	--------------------
	test entry orcl

	1 row selected.

Create a PDB by plugging an unplugged PDB into a CDB

Requirements:

The XML file that describes the PDB must exist in a location that is accessible to the CDB. The USING clause must specify the XML file.
The files associated with the PDB (such as the data files and wallet file) must exist in a location that is accessible to the CDB.
The source and target CDB platforms must meet the following requirements:

They must have the same endianness.
The database options installed on the source platform must be the same as, or a subset of, the database options installed on the target platform.
The CDB that contained the unplugged PDB and the target CDB must have compatible character sets and national character sets.

You can use the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function to determine whether these requirements are met. Step 2 in the following procedure describes using this function. On the source pluggable database, if it's opened:

	SQL> BEGIN
	  DBMS_PDB.DESCRIBE(
		pdb_descr_file => '/tmp/cdb1_pdb.xml',
		pdb_name       => 'CDB1_PDB');
	END;
	/  2    3    4    5    6

	PL/SQL procedure successfully completed.

On the target CDB database:

- If the output is YES, then the PDB is compatible, and you can continue with the next step. - If the output is NO, then the PDB is not compatible, and you can check the PDB_PLUG_IN_VIOLATIONS view to see why it is not compatible.

	SQL> SET SERVEROUTPUT ON
	DECLARE
	  compatible CONSTANT VARCHAR2(3) :=
		CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
			   pdb_descr_file => '/tmp/cdb1_pdb.xml',
			   pdb_name       => 'CDB1_PDB')
		WHEN TRUE THEN 'YES'
		ELSE 'NO'
	END;
	BEGIN
	  DBMS_OUTPUT.PUT_LINE(compatible);
	END;
	/SQL>   
	YES

	PL/SQL procedure successfully completed.

Unplugging PDB:

	SQL> ALTER PLUGGABLE DATABASE cdb1_pdb UNPLUG INTO '/tmp/cdb1_pdb.xml';
	ALTER PLUGGABLE DATABASE cdb1_pdb UNPLUG INTO '/tmp/cdb1_pdb.xml'
	*
	ERROR at line 1:
	ORA-65025: Pluggable database CDB1_PDB is not closed on all instances.


	SQL> alter pluggable database cdb1_pdb close;

	Pluggable database altered.

	SQL> select con_id, name, open_mode from v$pdbs;

		CON_ID NAME                           OPEN_MODE
	---------- ------------------------------ ----------
			 2 PDB$SEED                       READ ONLY
			 3 CDB1_PDB                       MOUNTED

	SQL> ALTER PLUGGABLE DATABASE cdb1_pdb UNPLUG INTO '/tmp/cdb1_pdb.xml';

	Pluggable database altered.

Plugging In a Unplugged PDB:

	SQL> CREATE PLUGGABLE DATABASE pdb1 USING '/tmp/cdb1_pdb.xml'
	  SOURCE_FILE_DIRECTORY = '/u01/app/oracle/oradata/cdb1/cdb1_pdb/'
	  COPY
	  FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/cdb1/cdb1_pdb/', '/u01/app/oracle/oradata/cdb/pdb1/')
	  TEMPFILE REUSE;  2    3    4    5

	Pluggable database created.

	SQL> select con_id, name, open_mode from v$pdbs;

		CON_ID NAME                           OPEN_MODE
	---------- ------------------------------ ----------
			 2 PDB$SEED                       READ ONLY
			 3 PDB                            READ ONLY
			 4 PDB1                           MOUNTED

	SQL> col pdb_name for a10
	SQL> select pdb_name, status from dba_pdbs;

	PDB_NAME   STATUS
	---------- ---------
	PDB        NORMAL
	PDB$SEED   NORMAL
	PDB1       NEW

	SQL> alter pluggable database pdb1 open;

	Pluggable database altered.

	SQL> alter session set container=pdb1;

	Session altered.

	SQL> select file_name from dba_data_files;

	FILE_NAME
	--------------------------------------------------------------------------------
	/u01/app/oracle/oradata/cdb/pdb1/system01.dbf
	/u01/app/oracle/oradata/cdb/pdb1/sysaux01.dbf
	/u01/app/oracle/oradata/cdb/pdb1/SAMPLE_SCHEMA_users01.dbf
	/u01/app/oracle/oradata/cdb/pdb1/example01.dbf
	/u01/app/oracle/oradata/cdb/pdb1/test_tbs_01.dbf

Oracle Release Schedule

2017-10-18T09:08:00.002+02:00

Release Schedule of Current Database Releases (Doc ID 742060.1)

How to clone Oracle database from the active database

2017-10-11T11:11:00.002+02:00

In this example I'll describe steps for cloning orcl database to new database orcl2. 1. Create pfile from spfile from the database orcl:

SQL> create pfile from spfile;

2. copy created init file for the new orcl2 database:

cp initorcl.ora initorcl2.ora

3. edit pfile of the database orcl2:

remove all hidden parameters
change orcl to orcl2 except db_name
add new line with:

[oracle@localhost dbs]$ cat initorcl2.ora
*.audit_file_dest='/u01/app/oracle/admin/orcl2/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/orcl2/control01.ctl','/u01/app/oracle/oradata/orcl2/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='orcl2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.local_listener='LISTENER_ORCL'
*.memory_target=1200m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

4. Create directory structure:

[oracle@localhost dbs]$ mkdir -p /u01/app/oracle/admin/orcl2/adump
[oracle@localhost dbs]$ mkdir /u01/app/oracle/oradata/orcl2
[oracle@localhost dbs]$ mkdir /u01/app/oracle/fast_recovery_area/orcl2

5. Edit /etc/oratab:

[oracle@localhost dbs]$ cat /etc/oratab
orcl:/u01/app/oracle/product/12.1.0/dbhome_1:N
orcl2:/u01/app/oracle/product/12.1.0/dbhome_1:N

6. setup new environment for the database orcl2

[oracle@localhost dbs]$ cat /etc/oratab
[oracle@localhost dbs]$ . oraenv
ORACLE_SID = [orcl] ? orcl2
The Oracle base remains unchanged with value /u01/app/oracle

7. startup nomount database orcl2

SQL> startup nomount
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size      2926320 bytes
Variable Size    444598544 bytes
Database Buffers    71303168 bytes
Redo Buffers      5459968 bytes

8. copy password file from database orcl to orcl2 ($ORACLE_HOME/dbs)

[oracle@localhost dbs]$ ls -la orapworcl*
-rw-r----- 1 oracle oinstall 7680 Oct 10 12:42 orapworcl
[oracle@localhost dbs]$ cp orapworcl orapworcl2

9. update information in tnsnames.ora ($ORACLE_HOME/network/admin)

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl2)
    )
  )

10. update information in listener.ora ($ORACLE_HOME/network/admin)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=orcl)
      (ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME=orcl))
    (SID_DESC=
      (GLOBAL_DBNAME=orcl2)
      (ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME=orcl2))
  )

11. connect to the rman

[oracle@localhost admin]$ rman target sys/oracle@orcl auxiliary sys/oracle@orcl2

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Oct 10 15:47:43 2017

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

connected to target database: ORCL (DBID=1484792494)
connected to auxiliary database: ORCL (not mounted)

12. run duplicate command:

run {
        allocate channel prmy1 type disk;
        allocate channel prmy2 type disk;
        allocate channel prmy3 type disk;
        allocate channel prmy4 type disk;
        allocate auxiliary channel stby type disk;
        duplicate target database to orcl2 from active database
        SPFILE PARAMETER_VALUE_CONVERT '/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl2'
                set DB_FILE_NAME_CONVERT 'orcl','orcl2'
                set log_file_name_convert '/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl2'
                set DB_UNIQUE_NAME 'orcl2'
                set control_files='/u01/app/oracle/oradata/orcl2/control01.ctl',
'/u01/app/oracle/fast_recovery_area/orcl2/control02.ctl';
}

"psdgbt: bind csid (1) does not match session csid (873)" and "psdgbt: session charset is AL32UTF8"

2017-08-25T11:24:00.002+02:00

After restart of the server and starting the database you can notice a lot of generated trace files:

Fri Aug 25 10:18:48 2017
Errors in file /oracle/orcldb/base/diag/rdbms/orcldb_dc1/orcldb/trace/orcldb_ora_2131.trc:
Errors in file /oracle/orcldb/base/diag/rdbms/orcldb_dc1/orcldb/trace/orcldb_ora_2131.trc:
Fri Aug 25 10:18:59 2017
Errors in file /oracle/orcldb/base/diag/rdbms/orcldb_dc1/orcldb/trace/orcldb_ora_2131.trc:
Errors in file /oracle/orcldb/base/diag/rdbms/orcldb_dc1/orcldb/trace/orcldb_ora_2131.trc:
Fri Aug 25 10:19:10 2017
Errors in file /oracle/orcldb/base/diag/rdbms/orcldb_dc1/orcldb/trace/orcldb_ora_2131.trc:
Errors in file /oracle/orcldb/base/diag/rdbms/orcldb_dc1/orcldb/trace/orcldb_ora_2131.trc:
Fri Aug 25 10:19:23 2017
Errors in file /oracle/orcldb/base/diag/rdbms/orcldb_dc1/orcldb/trace/orcldb_ora_2131.trc:
Errors in file /oracle/orcldb/base/diag/rdbms/orcldb_dc1/orcldb/trace/orcldb_ora_2131.trc:
Fri Aug 25 10:19:50 2017
Errors in file /oracle/orcldb/base/diag/rdbms/orcldb_dc1/orcldb/trace/orcldb_ora_2131.trc:

Each trace file contains information with error:

psdgbt: bind csid (1) does not match session csid (873)
psdgbt: session charset is AL32UTF8

Reason: The base problem is that there is a session that connected too early (before the the database was fully open) and is now stuck with AL32UTF8. There is no problem with the database.
Related bugs:

Bug 9329749 - TB: FAN: PSDGBT: BIND CSID (1) DOES NOT MATCH SESSION CSID (178)
Bug 13010721 : "PSDGBT: BIND CSID (1) DOES NOT MATCH SESSION CSID (873)" FROM OMS

Solution:
Restart OMS agent:

/oracle/product/12.1/agent12c/agent_inst/bin/emctl stop agent

/oracle/product/12.1/agent12c/agent_inst/bin/emctl start agent

In case that problem still persists:

stop the listener for this database instance.
then restart the database instance.
then restart the listener.

Oracle & PHP: Table with two CLOBs - inserting clobs via formular

2012-10-09T22:57:00.002+02:00

if (!empty($_POST['name'])) {
$sql=$db->executeLob("
insert into table_test 
(
name,
col_clob_1,
col_clob_2
) 
values 
( 
'" . $_POST['name'] . "',
EMPTY(CLOB),
EMPTY(CLOB)
) 
RETURNING 
comment_1, comment_2 INTO :mylob_loc_1, :mylob_loc_2 ",$_POST['comment_1'],$_POST['comment_2']);
}

public function executeLob($sql,$lob1, $lob2) {
 
  if ($this->connection) {
   // Prepare the statement
   $stid = oci_parse($this->connection, $sql);
 
   if (!$stid) {
    $e = oci_error($this->connection);
    var_dump($e);
   }
   
   // Perform the logic of the query   
      
   // Creates an "empty" OCI-Lob object to bind to the locator
   $myLOB1 = oci_new_descriptor($this->connection, OCI_D_LOB);
   $myLOB2 = oci_new_descriptor($this->connection, OCI_D_LOB);
   
   // Bind the returned Oracle LOB locator to the PHP LOB object
   $a = oci_bind_by_name($stid, ":mylob_loc_1", $myLOB1, -1, OCI_B_CLOB);
   $b = oci_bind_by_name($stid, ":mylob_loc_2", $myLOB2, -1, OCI_B_CLOB);
   
   $r = oci_execute($stid,OCI_NO_AUTO_COMMIT);
   if (!$r) {
    $e = oci_error($stid);
    var_dump($e);
   }
      
   // Now save a value to the LOB
   if ((!$myLOB1->save($lob1)) || (!$myLOB2->save($lob2))) {
    // On error, rollback the transaction
    oci_rollback($this->connection);
   } else {
    // On success, commit the transaction
    oci_commit($this->connection);
   }
   
   // Free resources
   oci_free_statement($stid);
   $myLOB1->free();
   $myLOB2->free();
 
   return $r;
  }
 }

Oracle: How to change datatype VARCHAR2 to CLOB

2012-10-06T11:22:00.003+02:00

At the beginning of creation database structure you decided to use datatype VARCHAR2 and that time you thought 4000B should be enough. But later you want to insert more than 4000B, it's time to change this datatype to CLOB.

If you don't have data in the table, it's ideal situation and the solution is very easy:

SQL> create table test (
  2  id number,
  3  name varchar2(50),
  4  comments varchar2(4000)
  5  );

Table created.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(50)
 COMMENTS                                           VARCHAR2(4000)

Because there is not direct way, how to modify column from VARCHAR2 to CLOB, we need to modify column in two steps. 
Steps:
 - VARCHAR2 --> LONG
 - LONG --> CLOB

SQL> alter table test modify comments LONG;

Table altered.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(50)
 COMMENTS                                           LONG

SQL> alter table test modify comments CLOB;

Table altered.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(50)
 COMMENTS                                           CLOB

SQL> insert into test (id, name, comments) values (1,'name_1','comment_1');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

        ID NAME            COMMENTS
---------- --------------- ------------------------------
         1 name_1          comment_1

Not very ideal situation is, if you don't have empty table, in this case is not possible to modify column from VARCHAR2 to CLOB so easy:

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(50)
 COMMENTS                                           VARCHAR2(4000)                                                    

SQL> begin
        for i in 1 .. 100 loop
                insert into test 
                (
                        id,
                        name,
                        comments
                )
                values 
                (
                        i,
                        'name_'||i,
                        'comments_'||i
                );
        end loop;
        commit;
end;
/ 

PL/SQL procedure successfully completed.

SQL> select count(*) from test;

  COUNT(*)
----------
       100

As before, there isn't possible to modify directly from VARCHAR2 to CLOB:

SQL> alter table test modify comments CLOB;
alter table test modify comments CLOB
                        *
ERROR at line 1:
ORA-22858: invalid alteration of datatype


And even more, there isn't possible to change VARCHAR2 to LONG:

SQL> alter table test modify comments LONG;
alter table test modify comments LONG
                        *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

The whole process will be:
 - create new column with datatype CLOB
 - update this column
 - drop old column with database VARCHAR2
 - rename created column to the original column name

SQL> alter table test add (comments_tmp clob);

Table altered.

SQL> desc test
Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(50)
 COMMENTS                                           VARCHAR2(4000)
 COMMENTS_TMP                                       CLOB

SQL> update test set comments_tmp=comments;

100 rows updated.

SQL> commit;

Commit complete.

SQL> select * from test where rownum<5;

        ID NAME            COMMENTS                       COMMENTS_TMP
---------- --------------- ------------------------------ ---------------------------
         1 name_1          comments_1                     comments_1
         2 name_2          comments_2                     comments_2
         3 name_3          comments_3                     comments_3
         4 name_4          comments_4                     comments_4

SQL> alter table test drop column comments;

Table altered.

SQL> desc test
Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(50)
 COMMENTS_TMP                                       CLOB

SQL> select * from test where rownum<5;

        ID NAME            COMMENTS_TMP
---------- --------------- ------------------------------
        90 name_90         comments_90
        92 name_92         comments_92
        93 name_93         comments_93
        94 name_94         comments_94

SQL> alter table test rename column comments_tmp to comments;

Table altered.

SQL> desc test
 Name                                                  Null?    Type
 ---------------------------------- -------- ------------------------------------
 ID                                                             NUMBER
 NAME                                                           VARCHAR2(50)
 COMMENTS                                                       CLOB

SQL> select * from test where rownum<5;

        ID NAME            COMMENTS
---------- --------------- ------------------------------
        90 name_90         comments_90
        92 name_92         comments_92
        93 name_93         comments_93
        94 name_94         comments_94

Oracle: Working with LOBs

2012-10-02T22:35:00.000+02:00

Large Objects (LOBs) are a set of datatypes that are designed to hold large amounts of data. A LOB can hold up to a maximum size ranging from 8 terabytes to 128 terabytes depending on how your database is configured. Storing data in LOBs enables you to access and manipulate the data efficiently in your application.

Load lobs to the database

Create table:

SYS@ORCL> create table orders
(order_id number(12),
order_name varchar2(80),
file_size number,
order_file blob)
tablespace users
lob (order_file) STORE AS
(
tablespace users
enable storage in row
chunk 4096
pctversion 20
nocache
nologging);  

Table created.

Create database directory:

SYS@ORCL> create directory lobdir as '/u01/app/oracle/scripts/lobs';

Directory created.

SYS@ORCL> !mkdir /u01/app/oracle/scripts/lobs

Prepare file for loading to the database:

[oracle@oralin1 lobs]$ ll
total 40
drwxr-xr-x 2 oracle oinstall 4096 Aug  1 20:49 .
drwxr-xr-x 3 oracle oinstall 4096 Aug  1 20:39 ..
-rw-r--r-- 1 oracle oinstall 9216 Aug  1 20:45 order_1.pdf

Load LOB file to the database:

SYS@ORCL> declare
 l_size number;
 l_file bfile;
 l_blob blob;
begin
 l_file := bfilename('LOBDIR','order_1.pdf');
 dbms_lob.fileopen(l_file);
 l_size := dbms_lob.getlength(l_file);
 insert into orders 
 (
  order_id,
  order_name,
  file_size,
  order_file
 )
 values
 (
  1,
  'order-1',
  l_size,
  empty_blob()
 )
 returning order_file into l_blob;
 dbms_lob.loadfromfile(l_blob,l_file,l_size);
 commit;
end;
/  

PL/SQL procedure successfully completed.

Check loaded file:

SYS@ORCL> select
 order_id,
 order_name,
 file_size,
 dbms_lob.getlength(order_file)
from 
 orders
;  

ORDER_ID  ORDER_NAME   ORDER_SIZE   DBMS_LOB.GETLENGTH(ORDER_FILE)
-------- ------------ ------------ -------------------------------
  1       Contract 1 115250          115250

Oracle: Configure the database instance to support shared server connections

2012-10-02T21:54:00.000+02:00

The shared server architecture enables a database server to allow many user processes to share very few server processes, so the number of users that can be supported is increased. With shared server, many user processes connect to a dispatcher. The dispatcher directs multiple incoming network session requests to a common queue. An idle shared server process from a shared pool of server processes picks up a request from the queue. This means a small pool of server processes can serve a large number of clients.

Shared memory resources are preconfigured to allow the enabling of shared server at run time. There is just needed to set one parameter if you are using TCP/IP as your communication protocol, and the rest of the settings are now managed internally.

Parameter connected to managing shared servers:

shared_servers - specifies the number of server processes that you want to create when an instance is started. This is the only required parameter for using shared servers. Setting this to a non-zero value automatically specifies shared server.
max_shared_servers - specifies the maximum number of shared server processes allowed to be running simultaneously. Setting this parameter enables you to reserve process slots for other processes, such as dedicated servers.
shared_server_sessions - specifies the total number of shared server sessions to allow.
dispatchers - configures dispatcher processes in the shared server architecture. One dispatcher is always configured by default for the TCP/IP protocol, even if the parameter is not explicitly specified.
max_dispatchers - specifies the maximum number of dispatcher processes allowed to be running simultaneously
circuits - specifies the total number of virtual circuits that are available for inbound and outbound network sessions

Configuring Dispatchers

SQL> alter system set shared_servers = 2 scope=both;

System altered.

SQL> alter system set dispatchers='(PROTOCOL=tcp)(DISPATCHERS=2)' scope=both;

System altered.

SQL> show parameter shared_servers

NAME                   TYPE       VALUE
-------------------- ----------- ---------
max_shared_servers    integer
shared_servers        integer      2


SQL> show parameter dispatcher

NAME                   TYPE       VALUE
-------------------- ----------- ------------------------------
dispatchers            string     (PROTOCOL=tcp)(DISPATCHERS=2)

tnsnames.ora

PROD database will listen on the port 51521 and the database PROD2 will listen on the port 1521.

PROD=
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=oralin1.oracle.com)(PORT=51521))
   (CONNECT_DATA=
     (SERVICE_NAME=PROD)))
PROD2=
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=oralin1.oracle.com)(PORT=1521))
   (CONNECT_DATA=
     (SERVICE_NAME=PROD)(SERVER=SHARED)))

Check configuration

Shared connection:

Open two consoles:
- connect to sqlplus via listener with account SYSTEM
- check the connection of the account SYSTEM

First console:
[oracle@oralin1 admin]$ sqlplus system/oracle@PROD2

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Apr 11 16:02:20 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

Second console:
SQL> select username, server from v$session where username='SYSTEM';

USERNAME SERVER
------------------------------ ---------
SYSTEM NONE

Dedicated connection:

Again, open two consols, one for connection and second for checking of the connection

First console:
oracle@oralin1 admin]$ sqlplus system/oracle@PROD

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Apr 11 16:22:39 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>


Second console:
SQL> select username, server from v$session where username='SYSTEM';

USERNAME SERVER
------------------------------ ---------
SYSTEM DEDICATED

Non default port

In the case, that shared servers are configured on the another port as is default port (1521), it's neccessary to set the parameter local_listener:

SYS@ORCL> show parameter local

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
local_listener        string
log_archive_local_first       boolean  TRUE
SYS@ORCL> alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=oralin1.oracle.com)(PORT=1526))' scope=both;

System altered.

SYS@ORCL> show parameter local

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
local_listener        string  (ADDRESS = (PROTOCOL=TCP)(HOST
       =oralin1.oracle.com)(PORT=1526
       ))
log_archive_local_first       boolean  TRUE
SYS@ORCL> exit

Virtualization of NFS storage: Create and Manage a tablespace that uses NFS mounted file system file

2012-10-01T19:57:00.000+02:00

Direct NFS is a new feature introduced with Oracle 11g and is an optimized NFS (Network File System) client that provides faster and more scalable access to NFS storage located on NAS storage devices (accessible over TCP/IP). Direct NFS is built directly into the database kernel - just like ASM which is mainly used when using DAS or SAN storage. Direct NFS provides faster performance that what can be provided by the operating system's NFS driver as Oracle bypasses the operating system and generates exactly the requests it needs (no user configuration or tuning required). Data is cached just once in user space, which saves memory (no second copy in kernel space). Performance is further improved by load balancing across multiple network interfaces (if available). Direct NFS is provided as part of the database kernel, and is thus available on all supported database platforms.

In this post I plan to practice to set up a data file on NFS.

Create NFS Volume and Share

For this practice I chosen Open Source Storage - FreeNAS (http://www.freenas.org)

Install and setup FreeNAS on the Linux platform

For this virtual simulation I have chosen VirtualBox (https://www.virtualbox.org) where I have created two Hard Disks:

FreeNAS.vdi - 4GB
NFSDATA.vdi - 4GB

I have created new Virtual Machine with following options:

Name: FreeNAS
Operating system: BSD
Version: FreeBSD or FreeBSD (64 bit)
Memory: 1024MB
Virtual Hard Disk: Create new hard disk
File type: I have chosen VDI
Virtual disk storage details: Dynamically allocated
Location: it's up to you
Size: again it's up to you

Additionally is necessary to create new disk, which will be shared for other virtual machines. In settings for new created virtual machine (FreeNAS):

Storage tab: add new IDE controller - Create new disk (the same steps as was in the previous step for creating new disk. Be aware to have this disk with the size 4GB or bigger. I have chosen the name NFSDATA.vdi)

Installation
Start new created virtual machine FreeNAS with two hard disks (FreeNAS.vdi and NFSDATA.vdi) and boot downloaded installation of FreeNAS:

Reboot and unmount the installation disk.

Configure network interface

Select first option: 1) Configure Network Interfaces
Select an interface: 1)em0
Delete existing config? n
Configure interface for DHCP? n
Configure IPv4? y
Interface name: lan1
IPv4 Address: 192.168.12.101/24
configure IPv6? n
GUI interface for configuration of FreeNAS is on the following IP: 192.168.12.101.

Create disc volumes

Open the GUI interface. IP address is in the previous picture - last two lines - 192.168.12.101.
In the top right you see that red "Alert" light blinking. Click it now.

It means that you should change the password for the admin user.
Create group:
In the left menu expand the "Groups" and click on the "Add Group"

In the case, that you want to simulate NFS mounted filesystem with the Oracle database in the other Virtual machine, the best solution is to choose the same uid/gid as you have for the user/group oracle/dba or oinstall (user and group under who you have installed the Oracle software).
Create user:
In the left menu expand the "Users" and click on the "Add User".
Set up disks and file sharing:
FreeNAS supports two types of file systems and three sharing protocols:
- UFS - An old stand-by, the Unix File System. Don't get us wrong, it's a solid system; we just don't want to use it here. For one thing, it lacks the volume management, RAID and pooling options we can get with our second option, ZFS.
- ZFS - The hot new kid on the block. This is the file system we want to use, mostly because of ZFS' data integrity, which promises safer, more resilient storage. Not an insignificant factor if you're trying to preserve a lifetime's worth of photos, or hundreds of tracks that cost you $0.99 apiece. We could spend a great deal of time talking about ZFS, but that's a topic for another day. For now, let's get on with it and talk about FreeNAS' sharing protocols.
FreeNAS supports three sharing methods:
- CIFS/SAMBA - Open-source implementation of Microsoft's SMB (Now mainly referred to as CIFS)
- AFP - Apple Filing Protocol
- NFS - Network File System
Now set up our disk volumes: in the left menu expand "Storage" -> "Volumes" and click on the "Create Volume"

After creating new volume, you should see:
Change permission:
In the left menu expand "Storage" -> "Volumes" ->"/mnt/nfs_share_1" and click on the "Change permission"

Here you can specify the owner of the volume with the read-write permissions.
Enable services:
In the left menu expand the "Services" and click on the "Control Services". Here you can enable NFS and SSH.
Make Sharing: In the left menu expand "Sharing" -> "NFS Shares" click on the "Add NFS Share".

Now we have prepared the NFS file system. To verify it we can try with "exportfs -v"

Mounting NFS on the client node

To be able to mount the NFS export, as the root user create an empty directory on each cluster node named /u01/app/nfs_share

Add the hostname of the shared storage to the /etc/hosts:

[root@oralin1 ~]# cat /etc/hosts
192.168.12.101 freenas-nas

Make sure the NFS export is mounted on the cluster nodes during boot time by adding the following line to the "/etc/fstab" file on each cluster node

freenas-nas:/mnt/nfs_share_1 /u01/app/nfs_share nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,vers=3,timeo=600 0 0
Mount the NFS export by executing the mount /u01/app/nfs_share command on each server
Check if the NFS export is correctly mounted with the "mount" command

freenas-nas:/mnt/nfs_share_1 on /u01/app/nfs_share type nfs (rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,nfsvers=3,timeo=600,addr=192.168.12.101)

Configure Direct NFS Client (DNFS)

Configure oranfstab file
For reference, Direct NFS searches for mount entries in the following order:

$ORACLE_HOME/dbs/oranfstab
/etc/oranfstab
/etc/mtab

Configure the oranfstab as like below:

[oracle@oralin1 ~]$ cat /u01/app/oracle/product/11.1.0/db_1/dbs/oranfstab 
server:  freenas-nas
path:    192.168.12.101
local:   192.168.12.11
export:  /mnt/nfs_share_1
mount:   /u01/app/nfs_share

Enable the Direct NFS Client ODM Library

cd $ORACLE_HOME/lib
mv libodm11.so libodm11.so_bak
ln -s libnfsodm11.so libodm11.so

Re-start the Database instance
alert log shows Direct NFS client is enabled by checking the below message
Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 2.0

Connect to DB instance and move existing datafiles to NFS drive or create tablespace on NFS drive to check DNFS is working or not

SYS@ORCL> select * from v$dnfs_servers;

        ID SVRNAME              DIRNAME                 MNTPORT    NFSPORT      WTMAX      RTMAX
---------- -------------------- -------------------- ---------- ---------- ---------- ----------
         1 freenas-nas          /mnt/nfs_share_1            958       2049      32768      32768

SYS@ORCL> create tablespace nfs_test datafile '/u01/app/nfs_share/nfs_test_1.dbf' size 5m;

Tablespace created.


SYS@ORCL> select * from v$dnfs_files;

FILENAME                                             FILESIZE       PNUM     SVR_ID
-------------------------------------------------- ---------- ---------- ----------
/u01/app/nfs_share/nfs_test_1.dbf                     5251072          9          1

Oracle Enterprise Manager 10g Release 5 Grid Control: bin/emdctl: error while loading shared libraries: libjava.so: cannot open shared object file

2012-07-17T12:04:00.001+02:00

[oracle@oralin2]$ emctl status agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.  
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
/u01/app/oracle/OracleHomes/agent10g/bin/emdctl: error while loading shared libraries: libjava.so:
 cannot open shared object file: No such file or directory

Solution: Execute the following steps to fix this issue.

Rename jdk to jdk

[oracle@oralin2 ~]$ echo $ORACLE_HOME
/u01/app/oracle/OracleHomes/agent10g
[oracle@oralin2 ~]$ cd $ORACLE_HOME
[oracle@oralin2]$ ls -ld jdk*
drwxr-xr-x 6 oracle oinstall 4096 Jul 17 09:03 jdk2012-07-17_09-35-41-AM
drwxr-x--- 7 oracle oinstall 4096 Jul 16 20:56 jdk2012-07-17_09-03-00-AM
[oracle@oralin2]$ mv jdk2012-07-17_09-35-41-AM jdk

Run root.sh from AGENT_HOME.

[oracle@oralin2]$ su -
Password: 
[root@oralin2 ~]# /u01/app/oracle/OracleHomes/agent10g/root.sh
Finished product-specific root actions.

Verify the jdk director at AGENT_HOME and size of the file.

[oracle@oralin2]$ ls -l $ORACLE_HOME/jdk*
/u01/app/oracle/OracleHomes/agent10g/jdk:
total 132
drwxr-xr-x 2 oracle oinstall  4096 Jul 17 09:03 bin
-rwxr-xr-x 1 oracle oinstall  2487 Oct 15  2008 COPYRIGHT
drwxr-xr-x 3 oracle oinstall  4096 Jul 17 09:03 include
drwxr-xr-x 6 oracle oinstall  4096 Jul 17 09:03 jre
drwxr-xr-x 2 oracle oinstall  4096 Jul 17 09:03 lib
-rwxr-xr-x 1 oracle oinstall 18245 Oct 15  2008 LICENSE
-rwxr-xr-x 1 oracle oinstall 20415 Oct 15  2008 README.html
-rwxr-xr-x 1 oracle oinstall 67235 Oct 15  2008 THIRDPARTYLICENSEREADME.txt

/u01/app/oracle/OracleHomes/agent10g/jdk2012-07-17_09-03-00-AM:
total 84
drwxr-x--- 2 oracle oinstall  4096 Jul 16 20:56 bin
-rw-r--r-- 1 oracle oinstall  4431 Apr 20  2005 COPYRIGHT
drwxr-x--- 3 oracle oinstall  4096 Jul 16 20:56 include
drwxr-x--- 6 oracle oinstall  4096 Jul 16 20:56 jre
drwxr-x--- 2 oracle oinstall  4096 Jul 16 20:56 lib
-rw-r--r-- 1 oracle oinstall 17669 Apr 20  2005 LICENSE
-rw-r--r-- 1 oracle oinstall  8986 Apr 20  2005 README
-rw-r--r-- 1 oracle oinstall 16251 Apr 20  2005 README.html
-rw-r--r-- 1 oracle oinstall 10129 Apr 20  2005 THIRDPARTYLICENSEREADME.txt
[oracle@oralin2]$ du -sh $ORACLE_HOME/jdk*
198M    /u01/app/oracle/OracleHomes/agent10g/jdk
72M     /u01/app/oracle/OracleHomes/agent10g/jdk2012-07-17_09-03-00-AM

Now start the Agent.

[oracle@oralin2]$ emctl start agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.  
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
Agent is already running
[oracle@oralin2]$ emctl status agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.  
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 10.2.0.5.0
OMS Version       : 10.2.0.5.0
Protocol Version  : 10.2.0.5.0
Agent Home        : /u01/app/oracle/OracleHomes/agent10g
Agent binaries    : /u01/app/oracle/OracleHomes/agent10g
Agent Process ID  : 9178
Parent Process ID : 8519
Agent URL         : https://oralin2.oracle.com:3872/emd/main/
Repository URL    : https://oralin2.oracle.com:1159/em/upload
Started at        : 2012-07-17 09:08:05
Started by user   : oracle
Last Reload       : 2012-07-17 09:13:19
Last successful upload                       : 2012-07-17 11:45:16
Total Megabytes of XML files uploaded so far :    12.02
Number of XML files pending upload           :        0
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :    46.75%
Last successful heartbeat to OMS             : 2012-07-17 11:46:17
---------------------------------------------------------------
Agent is Running and Ready

Installing Oracle Enterprise Manager 10g Grid Control: DCM Client Session has been invalidated

2012-07-16T21:18:00.001+02:00

Calling updateConfig to notify DCM of new deployments...
done.
Adding OC4J mount points for application 'IsWebCacheWorking'...done.
Calling SMI to save changes.
ERROR: Caught exception in SMISession.saveChanges: oracle.ias.sysmgmt.exception.InvalidSMISessionException: Base Exception:
This DCM Client Session has been invalidated due to changes made to config files by some other session. All unsaved changes will be lost.
Resolution:
Please close this DCM Client session, start another one and redo the changes made.

ERROR! DEPLOY TOOL CANNOT CONTINUE DEPLOYMENTS: SMI changes for deployed apps could not be saved - ALL APPLICATIONS WILL BE UNDEPLOYED.

Undeploying application 'IsWebCacheWorking' from OC4J instance 'home'.

Application 'IsWebCacheWorking' successfully undeployed. Calling updateConfig to notify DCM of undeployments...
done.
Stopping OC4J instance 'home'...
done.
Terminating DCM...
done.
Copying /u01/app/oracle/OracleHomes/oms10g/j2ee/deploy.ini to /u01/app/oracle/OracleHomes/oms10g/j2ee/deploy.ini.1342465183033.bak.
Writing any undeployed entries back to /u01/app/oracle/OracleHomes/oms10g/j2ee/deploy.ini.

Oc4jDeploy tool completed, but with errors.

Solution:

Run as oracle:(all in one command)
/u01/app/oracle/OracleHomes/oms10g/jdk/bin/java -Djava.net.preferIPv4Stack=true -Djava.io.tmpdir=/tmp -mx512M -classpath
/u01/app/oracle/OracleHomes/oms10g/dcm/lib/dcm.jar:
/u01/app/oracle/OracleHomes/oms10g/dcm/lib/oc4j_deploy_tools.jar:/u01/app/oracle/OracleHomes/oms10g/opmn/lib/opmnplugin.jar
-Doracle.ias.sysmgmt.logging.logdir=/u01/app/oracle/OracleHomes/oms10g/j2ee/home/log oracle.j2ee.tools.deploy.Oc4jDeploy
-oraclehome /u01/app/oracle/OracleHomes/oms10g -verbose -inifile /u01/app/oracle/OracleHomes/oms10g/j2ee/deploy.ini
Reading ini file - '/u01/app/oracle/OracleHomes/oms10g/j2ee/deploy.ini'
Adding web-app 'IsWebCacheWorkingWeb.war' for app 'IsWebCacheWorking'.
Initializing DCM...done.
OC4J instance 'home' already exists.
Starting OC4J instance 'home'...done.
Deploying application 'IsWebCacheWorking' to OC4J instance 'home'.
Notification ==> Application Deployer for IsWebCacheWorking STARTS [ 2012-07-16T21:03:21.870CEST ]
Notification ==> Undeploy previous deployment
Notification ==> Removing files for app file:/u01/app/oracle/OracleHomes/oms10g/j2ee/home/applications/IsWebCacheWorking.ear
Notification ==> Copy the archive to /u01/app/oracle/OracleHomes/oms10g/j2ee/home/applications/IsWebCacheWorking.ear
Notification ==> Unpack IsWebCacheWorking.ear begins...
Notification ==> Unpack IsWebCacheWorking.ear ends...
Notification ==> Initialize IsWebCacheWorking.ear begins...
Notification ==> Initialize IsWebCacheWorking.ear ends...
Notification ==> Initialize IsWebCacheWorkingWeb begins...
Notification ==> Initialize IsWebCacheWorkingWeb ends...
Notification ==> deleting: /u01/app/oracle/OracleHomes/oms10g/j2ee/home/applications/IsWebCacheWorking.ear
Notification ==> deleting: /u01/app/oracle/OracleHomes/oms10g/j2ee/home/applications/IsWebCacheWorking/IsWebCacheWorkingWeb.war
Notification ==> Application Deployer for IsWebCacheWorking COMPLETES [ 2012-07-16T21:03:21.917CEST ]

Calling updateConfig to notify DCM of new deployments...done.
Adding OC4J mount points for application 'IsWebCacheWorking'...done.
Calling SMI to save changes.
SMISession.saveChanges succeeded.
Binding web app 'IsWebCacheWorkingWeb' to default-web-site for application 'IsWebCacheWorking' in OC4J instance 'home'
Web app 'IsWebCacheWorkingWeb' bound successfully.
Calling updateConfig to notify DCM of new web-bindings...done.
Adding application 'IsWebCacheWorking' to the DCM repository...done.
Application 'IsWebCacheWorking' deployed successfully.
Stopping OC4J instance 'home'...done.
Calling SMI to retry init of failed plugins...done.
Terminating DCM...done.
Copying /u01/app/oracle/OracleHomes/oms10g/j2ee/deploy.ini to /u01/app/oracle/OracleHomes/oms10g/j2ee/deploy.ini.1342465416030.bak.
Writing any undeployed entries back to /u01/app/oracle/OracleHomes/oms10g/j2ee/deploy.ini.

Oc4jDeploy tool completed successfully!

Oracle: Understanding the table user$

2012-07-01T14:25:00.000+02:00

Table user$:

SQL> desc user$
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER#        NOT NULL NUMBER
 NAME        NOT NULL VARCHAR2(30)
 TYPE#        NOT NULL NUMBER
 PASSWORD         VARCHAR2(30)
 DATATS#       NOT NULL NUMBER
 TEMPTS#       NOT NULL NUMBER
 CTIME        NOT NULL DATE
 PTIME          DATE
 EXPTIME         DATE
 LTIME          DATE
 RESOURCE$       NOT NULL NUMBER
 AUDIT$          VARCHAR2(38)
 DEFROLE       NOT NULL NUMBER
 DEFGRP#         NUMBER
 DEFGRP_SEQ#         NUMBER
 ASTATUS       NOT NULL NUMBER
 LCOUNT        NOT NULL NUMBER
 DEFSCHCLASS         VARCHAR2(30)
 EXT_USERNAME         VARCHAR2(4000)
 SPARE1          NUMBER
 SPARE2          NUMBER
 SPARE3          NUMBER
 SPARE4          VARCHAR2(1000)
 SPARE5          VARCHAR2(1000)
 SPARE6          DATE

Explanation of some columns:

CTIME is the date the user was created.
LTIME is the date the user was last locked. (Note that it doesn't get NULLed when you unlock the user).
PTIME is the date the password was last changed.
LCOUNT is the number of failed logins.

Table user_astatus_map:

SQL> desc sys.user_astatus_map
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATUS#       NOT NULL NUMBER
 STATUS        NOT NULL VARCHAR2(32)

In this table is a list of all statuses. Therefore you can join those two tables.

   STATUS# STATUS
---------- --------------------------------
  0 OPEN
  1 EXPIRED
  2 EXPIRED(GRACE)
  4 LOCKED(TIMED)
  8 LOCKED
  5 EXPIRED & LOCKED(TIMED)
  6 EXPIRED(GRACE) & LOCKED(TIMED)
  9 EXPIRED & LOCKED
 10 EXPIRED(GRACE) & LOCKED

9 rows selected.

Auto Startup and Shutdown of Database and Listener

2012-06-26T10:32:00.001+02:00

1. change /etc/oratab as oracle

ORCL:/u01/app/oracle/product/11.2.0/db_1:Y

2. As root user create new file with name oracle - for startup and shutdown the database in /etc/init.d/ directory:

#!/bin/bash
#
# oracle Init file for starting and stopping
# Oracle Database. Script is valid for 10g and 11g versions.
#
# chkconfig: 35 80 30
# description: Oracle Database startup script

# Source function library.

. /etc/rc.d/init.d/functions

ORACLE_OWNER="oracle"
ORACLE_HOME="/u01/app/oracle/product/11.2.0/db_1"

case "$1" in
start)
echo -n $"Starting Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
echo "OK"
;;
stop)
echo -n $"Stopping Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
echo "OK"
;;
*)
echo $"Usage: $0 {start|stop}"
esac

3. As root change the permissions and configure runlevel:

chmod 750 /etc/init.d/oracle
chkconfig --add oracle --level 0356

Last Insert ID with PHP & Oracle

2012-06-02T20:51:00.002+02:00

This article is continue of earlier article Using PHP with Oracle database. You can expand the part of dbUtil.php with following code:

public function executeVariable($sql, $printSql = false) {
 
   if ($this->connection) {
      // Prepare the statement
      $stid = oci_parse($this->connection, $sql);

      if($printSql) {
         echo $sql;  
      }

      if (!$stid) {
         $e = oci_error($this->connection);
         var_dump($e);
      }
 
      OCIBindByName($stid,":ID",$id);
  
      // Perform the logic of the query
      $r = oci_execute($stid);

      if (!$r) {
         $e = oci_error($stid);
         var_dump($e);
      }

      return array("result" => $r, "lastID" => $id);
   }
}

Now you can use new function executeVariable() as following:

<?php 
error_reporting(E_ALL);

require_once 'dbUtil.php';
$db = new DBUtil();

$sql = $db -> executeVariable("insert into dbtool_schema.test (id, name) values (1,'test') returning id into :id",true);
print "
".$sql["lastID"];
?>

Ubuntu 11.10 / 12.04: Starting too long because of "Waiting for network configuration"

2012-04-29T21:17:00.000+02:00

If someone still experience delay in system booting with message:
- "Waiting for network configuration ..."
- "Waiting up to 60 more seconds for network configuration ..."
- "Booting system without full network configuration ..."

You can try to comment out the line "auto wlan0" in /etc/network/interfaces.

Ubuntu: Change / Reset Keyring Password

2012-04-29T17:43:00.001+02:00

After changing the password of your user/personal account in the Ubuntu 11.04 (also 11.10, 12.04) you are asked to put Password Keyring all the time:

For unlocking it is necessary to put there your old original password. But sometimes you don't remember it, hmm... With following steps you can change it or reset it:

Open "Passwords and Keys":
In the "Passwords Tab" you can Delete or "Change Password" with right click on you mouse:

If you set the keyring login passwords to the same as your user password, Gnome will automatically unlock your keyring during login.

Run a shell script with an HTML result with CSS styles

2012-04-23T13:34:00.002+02:00

HTML result from shell script useful for reports from database.

Run the script start.sh which will start the script scripts.sql. In the scripts.sql are another SQL scripts - SQL statements for database.

Output:

Code for start.sh

#!/bin/sh
DATE=`date '+%m%d%y_%H%M'`

sSID=$1

# Print beginning of webpage
html_header ()
{
    cat <<END
<html>
  <head><title>${1}</title>

  <style type="text/css">
 div {
  font-size:8pt;
  color: #000;
  margin-top: 1em;
  margin-botton: 2em;
  border-collapse:collapse;
  background-color:#F5F5F5;
  font-family: courier;
  margin-left: 2em;
  width:1100px;
  border:solid 1px;
 }
 .name {
   font-size:18pt;
   color: #7d181e;
   letter-spacing:1pt;
   font-weight:700;
  }

 .name2 {
   font-size:16pt;
   color: #d52a33;
 }

 .name3 {
   font-size:14pt;
   color: #d52a33;
 }

 ol {
   counter-reset: section;
   list-style-type: none;
 }
  
 ol li { counter-increment: section; }
            
 ol li:before  { content: counters(section, ".") ". "; }
  </style>
  </head>
  <body>
    <h3>${1}</h3>
    <!-- Table of Contents links -->
    <p>
 <ol>
  <li>
   <a href='#1_heading'>Database info</a>
   <ol> 
    <li><a href='#1_1_name'>Database name</a></li>
    <li><a href='#1_2_name'>Database version</a></li>
   </ol>
  </li>
  <li>
   <a href='#2_heading'>Database users and sessions</a>
   <ol>
    <li><a href='#2_1_name'>Number of sessions</a></li>
    <li><a href='#2_2_name'>Current sessions</a></li>
   </ol>
  </li>
 </ol>
    </p>
END
}

html_footer ()
{
  cat <<END
  </body>
</html>
END
}

html_title ()
{
  echo "<h3><a name='#${2}'>$1</a></h3>"
}

DatabaseIntroduction ()
{
echo "<pre>"
sqlplus -s /nolog <<EOF 
connect sys/<password>@<database_name> as sysdba
@scripts.sql
EOF
echo "</pre>"
}

html_header "Report Summary for ${DATE}"
DatabaseIntroduction
html_footer

Code for scripts.sql

@database_info.sql
@database_sessions.sql

Code for database_info.sql and database_sessions.sql

database_info.sql

PROMPT <span class='name' id='1_heading'>1. Database info</span>
PROMPT
PROMPT
PROMPT <span class='name2' id='1_1_name'>1.1. Database name</a></span>
PROMPT
PROMPT <div>
PROMPT

select database_name from v$database;

PROMPT
PROMPT </div>
PROMPT

PROMPT
PROMPT <span class='name2' id='1_2_name'>1.2 Database version</span>
PROMPT
PROMPT <div>

select version from v$instance;

PROMPT
PROMPT </div>
PROMPT

database_sessions.sql

PROMPT
PROMPT <span class='name' id='2_heading'>2. Database users and sessions</span>
PROMPT
PROMPT <span class='name2' id='2_1_name'>2.1 Number of sessions</span>
PROMPT
PROMPT <div>
PROMPT

select count(*) from v$session;

PROMPT
PROMPT </div>
PROMPT

PROMPT
PROMPT <span class='name2' id='2_2_name'>2.2 Current sessions</span>
PROMPT
PROMPT <div>
PROMPT

select distinct username from v$session;

PROMPT
PROMPT </div>
PROMPT

How to repair standby database when is problem with one or more datafiles

2012-04-13T17:19:00.001+02:00

In case that recovery of the standby side fell down because of failure of the transferring of the archivelog or based on the another error, in the best case you have to repair one or more datafiles, at worst you have to recreate whole standby database. This post is about the reconstruction one or more datafiles on the standby side.

Terms:
PRIM - primary side
STBY - standby side

If one or more datafiles are unusable, recovery of the standby side crashed automatically. Therefore you don't need to turn off recovery on the standby side. But shipping of archived logs should work without problem.

PRIM: Turn on backup mode

alter tablespace  begin backup; 
 or
alter database begin backup;

STBY: You can delete wrong datafile from the filesystem

  rm /u01/app/oracle/oradata/orcl/example01.dbf

PRIM: Copy datafile from the PRIM to STBY

  scp /u01/app/oracle/oradata/orcl/example01.dbf <stby_host_name>:/u01/app/oracle/oradata/stby/

PRIM: If it's necessary, create standby controlfile

 alter database create standby controlfile as '/tmp/control_stby.bak';

PRIM: Copy standby controlfile from PRIM to STBY

  scp /tmp/control_stby.bak <stby_host_name>:/.../control01.ctl

And copy this one control file to all locations, where controlfiles are located.

STBY: You can test manual recovery of standby database

  RECOVER  standby database until cancel;

STBY: Turn on automatic recovery of the standby database

    RECOVER MANAGED STANDBY DATABASE DISCONNECT;

PRIM: Turn off backup mode

alter tablespace  end backup; 
 or
alter database end backup;

View hidden parameters in Oracle database

2012-04-13T13:57:00.000+02:00

set linesize 200
col "Parameter" for a30
col "Session Value" for a10
col "Instance Value" for a10
col "Default value" for a5
col SESSMOD for a7
col SYSMOD for a9
col "Description" for a45
 
SELECT 
   a.ksppinm  "Parameter", 
   b.ksppstvl "Session Value", 
   c.ksppstvl "Instance Value" ,
   b.ksppstdf "Default value" ,
   decode(bitand(a.ksppiflg/256,3),1, 'True', 'False') SESSMOD,
   decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') SYSMOD,
   a.ksppdesc "Description" 
FROM
   sys.x$ksppi a, 
   sys.x$ksppcv b , 
   sys.x$ksppsv c 
WHERE 
   a.indx = b.indx 
   and a.indx = c.indx 
   and substr(ksppinm,1,1)=’_'  
ORDER BY 
   a.ksppinm;

SYSMODE	system modifiable	IMMEDIATE	ALTER SYSTEM without restarting the instance
		DEFERRED	ALTER SYSTEM regardless of the type of parameter file used in the instance startup.
		FALSE	parameter cannot be changed with ALTER SYSTEM unless the database runs on a spfile
SESSMOD	session modifiable

External procedures: How to run OS commands from the database (SQL*Plus) and result store in the database

2012-04-13T13:06:00.000+02:00

This solution works for all versions of the Oracle database (8i/9i/10g/11g)

OS part
Database part
How to read result and save it to the database

OS part

1. already compiled shell.c for specified OS

/*
 * shell.c
 * Example program used to demonstrate how to call O/S
 * commands from PL/SQL using external procedures. 
 *
 */

#include
#include
#include

void sh(char *command) {

  int num;

  num = system(command);

}

void shell(char *command, char buff[4000]) {
//char buff[4000];
FILE *fp = popen(command, "r" );
while ( fgets( buff, sizeof buff, fp ) != NULL ) {
 // do stuff with lines
 printf("%s\n", buff);
}
pclose( fp );
}

How to compile shell.c
Using the table below, first run the appropriate command to compile the shell.c program then run the command to generate the shared object.

Operating System	extproc bit version	1st: Compile the shell.c program	2nd: Generate the shared object
Solaris SPARC	extproc (64 bit)	gcc -G -c -m64 shell.c	ld -r -o shell.so shell.o
Solaris SPARC*	extproc32 (32 bit)	gcc -G -c shell.c	ld -r -o shell.so shell.o
Linux RH AS3 32 bit	extproc (32 bit)	gcc -c shell.c	ld -shared -o shell.so shell.o
Linux RH AS4 x86-64	extproc (64 bit)	gcc -fPIC -c shell.c	ld -shared -o shell.so shell.o
Linux RH AS4 x86-64*	extproc32 (32 bit)	gcc -m32 -c shell.c	ld -shared -melf_i386 -o shell.so shell.o
AIX 5L	extproc (64 bit)	gcc -maix64 -c shell.c	gcc -maix64 -shared -o shell.so shell.o
AIX 5L*	extproc32 (32 bit)	gcc -c shell.c	gcc -shared -o shell.so shell.o
HPUX 11.11**	extproc32 (32 bit)	gcc -c shell.c	gcc -shared -o shell.sl shell.o
HPUX 11.11	extproc (64 bit)	/usr/local/pa64/bin/gcc -c shell.c	/usr/local/pa64/bin/gcc -shared -o shell.sl shell.o

* Add $ORACLE_HOME/lib32 to LD_LIBRARY_PATH, restart the listener and oracle
** Add $ORACLE_HOME/lib32 to SHLIB_PATH, restart the listener and oracle

2. Change permissions of shell.so and copy the file to $ORACLE_HOME/lib

Copying this file will bypass the need for adding ENVS and EXTPROC_DLLS parameters to the listener.ora file. Remember, tweaking can be done later; for now, we want the configuration to be as simple as possible.

chmod 755 shell.so cp shell.so $ORACLE_HOME/lib

Or there is also possibility to configure listener.ora:

 LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <host_name>)(PORT = <port>))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) --> this must be also included to the definition of listener
    )
  )

 SID_LIST_LISTENER=              
  (SID_LIST=
   (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = <ORACLE_HOME>) --> correct $ORACLE_HOME
    (PROGRAM = extproc)
    (ENVS="EXTPROC_DLLS=ONLY:<directory>/shell.so")  --> exactly specified destination to the file
   )
  )

Changes in tnsnames.ora:

 extproc_connection_data.world=
 (description=
 (address=(protocol=ipc)(key=EXTPROC))
 (connect_data=(sid=PLSExtProc)))

Attention: listener and tnsnames configuration is case sensitive

3. Test configuration for OS part

Use tnsping to test the extproc configuration. This step has to succeed.

  host$ tnsping extproc_connection_data
 
  TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 12-APR-2012 21:21:09
                                
  Copyright (c) 1997,  2007, Oracle.  All rights reserved.
                                 
  Used parameter files:
  /.../sqlnet.ora
                                
                                
  Used TNSNAMES adapter to resolve the alias
  Attempting to contact (description= (address=(protocol=ipc)(key=EXTPROC)) (connect_data=(sid=PLSExtProc)))
  OK (0 msec)

Database part

1. Create a LIBRARY definition in Oracle

  CREATE LIBRARY shell_lib is '<path to shell.so>/shell.so';
  /

2. Create the PL/SQL procedure

  CREATE OR REPLACE PROCEDURE shell(command IN char)
  AS EXTERNAL
  NAME "sh"
  LIBRARY shell_lib
  LANGUAGE C
  PARAMETERS (command string);
  /

3. Test the stored procedure

  exec shell('ls');

  exec shell('ls>output.txt');

How to read result and save it to the database

Create database directory, where will be result of the OS command from the database

  create or replace directory TEST_DIR as '<directory_name>'; --> specify directory name

  grant write on directory TEST_DIR to <user_name>;
  grant read on directory TEST_DIR to <user_name>;
  grant execute on UTL_FILE to <user_name>;
  grant create procedure to <user_name>;

Create table for storing result

 create table test (name varchar2(15), content varchar2(4000));

Create the PL/SQL procedure for reading and update the table

CREATE OR REPLACE PROCEDURE show_file (loc IN VARCHAR2,file IN VARCHAR2)
 IS
 fid UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (loc, file, 'R');
 line VARCHAR2(4000);
 BEGIN
  DBMS_OUTPUT.PUT_LINE (file);
  insert into test (name, content) values (file,'');
  commit;
  LOOP
  UTL_FILE.GET_LINE (fid, line);
  update test set content = content ||'|'|| line where name=file;                 
  END LOOP;
  EXCEPTION
  WHEN OTHERS THEN UTL_FILE.FCLOSE (fid);
 END;
/

How to use it

 exec shell('ls /tmp > /tmp/test');
 exec show_file ('TEST_DIR','test');

 select * from test;

Remove user SYSMAN from the repository database after failed GRID installation

2012-04-11T13:13:00.000+02:00

DROP USER sysman CASCADE;
DROP PUBLIC SYNONYM setemviewusercontext;
DROP ROLE mgmt_user;
DROP PUBLIC SYNONYM mgmt_target_blackouts;
DROP USER mgmt_view;

Upgrading existing Oracle Enterprise Manager 10g Grid Control: How to Change / Reset ias_admin Password

2012-04-10T21:22:00.000+02:00

In the case you know the current password for user ias_admin, you can change it with following command:

emctl set password <old_password> <new_password>

Otherwise it's necessary to change it in the file: $ORACLE_HOME/sysman/j2ee/config/jazn-data.xml

Find following rows:
<user>
   <name>ias_admin</name>
   <credentials>{903}8QkQ/crno3lX0f3+67dj6WxW9KJMXaCu</credentials>
</user>

Change password:
<user>
   <name>ias_admin</name>
   <credentials>!welcome1</credentials>
</user>

Sign "!" means, that the password is stored in clear text.

OEM Gridcontrol 10201 Installation Errors On Oracle Linux 5.5

2012-04-10T14:09:00.000+02:00

Error:

Failed in smi manipulation().
A problem has occurred reading the initial configuration and storing it into repository
Resolution:
Please refer to the base exception for resolution, or call Oracle support.
Base Exception:
/u01/app/oracle/OracleHomes/oms10g/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory
Resolution:
Please make sure the values entered in OHS configuration files are correct.
oracle.ias.sysmgmt.exception.InvalidConfigurationException: Base Exception:
/u01/app/oracle/OracleHomes/oms10g/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory
Resolution:
Please make sure the values entered in OHS configuration files are correct.
        at oracle.ias.sysmgmt.repository.plugin.advanced.apache.StateTranslator.checkConfigFileValidity(Unknown Source)
        at oracle.ias.sysmgmt.repository.plugin.advanced.apache.StateTranslator.validateConfigDuringEvaluate(Unknown Source)
        at oracle.ias.sysmgmt.repository.plugin.advanced.apache.PlugInImpl.localConfigValidation(Unknown Source)
        at oracle.ias.sysmgmt.repository.DcmPlugin.localConfigValidation(Unknown Source)
        at oracle.ias.sysmgmt.repository.RepositoryImpl.performLocalValidation(Unknown Source)
        at oracle.ias.sysmgmt.repository.SyncUpHandler._updatePluginConfigData(Unknown Source)
        at oracle.ias.sysmgmt.repository.SyncUpHandler.syncUpFromLocalFiles(Unknown Source)
        at oracle.ias.sysmgmt.repository.RepositoryImpl.syncUpFromLocalFiles(Unknown Source)
        at oracle.ias.sysmgmt.utility.editpropagator.PropagateLocalEdit.repositoryInit(Unknown Source)
        at oracle.ias.sysmgmt.persistence.utility.PMUtility.initConfiguration(Unknown Source)
        at oracle.ias.sysmgmt.task.TaskMaster.initConfiguration(Unknown Source)
        at oracle.ias.sysmgmt.task.TaskMaster.sysInit(Unknown Source)
        at oracle.ias.sysmgmt.task.TaskMaster.sysInit(Unknown Source)
        at oracle.ias.sysmgmt.task.InstanceManager.sysInit(Unknown Source)
        at oracle.ias.sysmgmt.task.InstanceManager.init(Unknown Source)
        at oracle.ias.sysmgmt.EntryPoint.init(Unknown Source)
        at oracle.ias.webcache.config.WebcacheConfig.smiSetup(WebcacheConfig.java:483)
        at oracle.ias.webcache.config.WebcacheConfig.smiEnableWebcache(WebcacheConfig.java:454)
        at oracle.ias.webcache.config.WebcacheConfig.enableConfiguration(WebcacheConfig.java:299)
        at oracle.ias.webcache.config.WebcacheConfig.main(WebcacheConfig.java:87)

Command = /u01/app/oracle/OracleHomes/oms10g/jdk/bin/java has failed

INFO: Configuration assistant "Web Cache Configuration Assistant" failed
-----------------------------------------------------------------------------

Solution:

[root@oralin2 ~]# ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2
[root@oralin2 ~]# chmod 755 /usr/lib/libgdbm.so.2.0.0
[root@oralin2 ~]# chmod 755 /usr/lib/libdb.so.2
[root@oralin2 ~]# locate libdb.so.2
[root@oralin2 ~]# locate libgdbm.so.2.0.0
/usr/lib/libgdbm.so.2.0.0
[root@oralin2 ~]# ls -ldd libgdbm.so.2.0.0
ls: libgdbm.so.2.0.0: No such file or directory
[root@oralin2 ~]# ls -ldd /usr/lib/libgdbm.so.2.0.0
-rwxr-xr-x 1 root root 25260 Jun  5  2007 /usr/lib/libgdbm.so.2.0.0
[root@oralin2 ~]# ldd /usr/lib/libgdbm.so.2.0.0
 linux-gate.so.1 =>  (0x003ab000)
 libc.so.6 => /lib/libc.so.6 (0x004f7000)
 /lib/ld-linux.so.2 (0x004d9000)
[root@oralin2 ~]# ldd /usr/lib/libgdbm.so.2.0.0
 linux-gate.so.1 =>  (0x0042a000)
 libc.so.6 => /lib/libc.so.6 (0x004f7000)
 /lib/ld-linux.so.2 (0x004d9000)
[root@oralin2 ~]#  ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2
ln: creating symbolic link `/usr/lib/libdb.so.2' to `/usr/lib/libgdbm.so.2.0.0': File exists
[root@oralin2 ~]# chmod 755 /usr/lib/libgdbm.so.2.0.0
[root@oralin2 ~]# chmod 755 /usr/lib/libdb.so.2