How to check whether a library is compiled to 32-bit or 64-bit code on AIX

Use the nm-utility:

"Displays information about symbols in object files, executable files, and object-file libraries."

In the following case, the Library file is 32-bit, since using the -X64 flag is throwing an exception:

nm -X64 /u01/oracle/product/ora11g_client32/mylib.so

0654-210 /u01/oracle/product/ora11g_client32/mylib.so is not valid in the current object file mode.
        Use the -X option to specify the desired object mode.

while using the -X32 flag will produce output similar to the following (abbreviated):

z00u070:ar3u>nm -X32 /u01/oracle/product/ora11g_client32/mylib.so

                     f           -
../../../../../../../src/bos/usr/ccs/lib/libm/m_tables.c f           -
zstcXAForget         U           -
zstcXAOpen           U           -
zstcXAPrepare        U           -
zstcXARecover        U           -
zstcXARollback       U           -
zstcXAStart          U           -

Note that you can use the OBJECT_MODE environment variable and instead execute

export OBJECT_MODE=64
nm /u01/oracle/product/ora11g_client32/mylib.so

How to check whether a library is compiled to 32-bit or 64-bit code on Linux

On Linux, use objdump:

objdump -f /u01/oracle/product/oracle_client32/mylibdir/mylibfile.so
/u01/oracle/product/oracle_client32/mylibdir/mylibfile.so:     file format elf32-i386
architecture: i386, flags 0x00000150:
HAS_SYMS, DYNAMIC, D_PAGED
start address 0x00000560

How to use dbms_metadata to generate DDL for profiles

An easy way to migrate your profiles from a source database to a target database during migration is to use the dbms_metadata package.

To generate one call for each profile:

SELECT UNIQUE 'SELECT DBMS_METADATA.GET_DDL(''PROFILE'',' || ''''|| PROFILE || ''') FROM DUAL;'
FROM DBA_PROFILES;

In my case, the result was a total of three profiles. Use the resulting rows in the script below:

SET HEADING OFF
SET TRIMSPOOL ON
SET FEEDBACK OFF
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', TRUE);
-- your calls to dbms_metadata here
SELECT DBMS_METADATA.GET_DDL('PROFILE','PROF') from dual;
SELECT DBMS_METADATA.GET_DDL('PROFILE','DEFAULT') from dual;
SELECT DBMS_METADATA.GET_DDL('PROFILE','ONLINEUSR') from dual;

How to use the nmap tool for Oracle networking troubleshooting

The Network exploration tool and security / port skanner (nmap) came in handy as I was checking the prerequisites for a Golden Gate installation.

We had to open ports in a firewall between the two servers to allow the Golden Gate Manager processes on each side to communicate with one another.

Initially, the ports seemed to be closed even though the firewall administrator claimed it was open.

Oracle Golden Gate needs two-way communication over the designated manager port, which by default is 7809.

So I used nmap to prove that it was indeed closed.

When the nmap status is closed or filtered, the man pages explains their state as


"Closed ports have no application listening on them, though they could open up at any time. Ports are classified as unfiltered when they are responsive to nmap's probes, but nmap cannot determine whether they are open or closed.

Filtered ports means that a firewall, filter, or other network obstacle is blocking the port so that nmap cannot tell whether it is open or closed."


Port 1521 was opened, as requested from the firewall team:

[root@myserver2 ~]#  nmap -p 1521 myserver1
Starting Nmap 5.51 ( http://nmap.org ) at 2015-03-24 14:02 CET

Nmap scan report for myserver1 (159.216.45.70)
Host is up (0.0018s latency).
rDNS record for 159.216.45.70: myserver1.mydomain.no
PORT     STATE SERVICE
1521/tcp open  oracle

Port 7809 was closed, as seen by the output below:

[root@myserver2 ~]# nmap -p 7809 myserver1 

Starting Nmap 5.51 ( http://nmap.org ) at 2015-03-24 15:14 CET
Nmap scan report for myserver1 (159.216.45.70)
Host is up.
rDNS record for 159.216.45.70: myserver1.mydomain.no
PORT     STATE    SERVICE
7809/tcp filtered unknown

Nmap done: 1 IP address (1 host up) scanned in 2.08 seconds

Later, the port range 7809-7820 was opened, as can be seen below. Note that there is no activity on ports 7810-7820 so they are for the time being marked as closed:

root@myserver2 ~]# nmap  -p 7809-7820 myserver1

Starting Nmap 5.51 ( http://nmap.org ) at 2015-03-24 15:48 CET
Nmap scan report for myserver1(159.216.45.70)
Host is up (0.0024s latency).
rDNS record for 159.216.45.70: myserver1.mydomain.no

PORT     STATE  SERVICE
7809/tcp open   unknown
7810/tcp closed unknown
7811/tcp closed unknown
7812/tcp closed unknown
7813/tcp closed unknown
7814/tcp closed unknown
7815/tcp closed unknown
7816/tcp closed unknown
7817/tcp closed unknown
7818/tcp closed unknown
7819/tcp closed unknown
7820/tcp closed unknown

How to use DECODE to create a script for compilation of both packages and package bodies

connect scott/tiger
alter session set nls_language='AMERICAN';
set heading off
set trimspool on
set feedback off
set verify off
set echo off
set pagesize 0
spool recompl.lst
select 'alter '||decode(object_type, 'PACKAGE BODY', 'package', object_type) || ' ' || object_name || ' compile' || decode(object_type, 'PACKAGE BODY', ' body;', ';')
from user_objects
where status = 'INVALID'
order by object_type;
select 'show errors' from dual;
select 'exit' from dual;
spool off
start recompl.lst

How to solve ORA-02180 when specifying COMPRESSION type

You get

ORA-02180: invalid option for CREATE TABLESPACE

when executing a create tablespace statement like this one:

CREATE TABLESPACE test DATAFILE 
  '/u02/oradata/mydb/test.ora' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
COMPRESS FOR OLTP 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

Solution:
Add the DEFAULT keyword to specify the default parameters for the database:

CREATE TABLESPACE test DATAFILE 
  '/u02/oradata/mydb/test.ora' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
DEFAULT
COMPRESS FOR OLTP 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

How cloning from backup became a little more secure in Oracle 11g

From version 11, Oracle supplied another method to use during cloning, namely "Backup-based duplication without a target connection".

From the Oracle 11g Documentation:

RMAN can perform the duplication in any of the following supported modes:

1.       Active duplication
2.       Backup-based duplication without a target connection
3.       Backup-based duplication with a target connection
4.       Backup-based duplication without connection to target and recovery catalog

If you choose method number 2, you need to use the following syntax:

DUPLICATE DATABASE <target database> TO <auxiliary database>;

In the 10g documentation you will need to use the "target" keyword

DUPLICATE TARGET DATABASE TO <auxiliary database>;

Oracle points out:

«This mode is useful when the target database is not available or a connection to it is not desirable». 

In other words, cloning has become more secure since version 11g, where we can totally avoid connecting to the target database, which is often in production, during cloning from backup.

An example of an incident where a connection to the target could potentially jeopardize production, is when you are scripting jobs for RMAN and accidently issue "shutdown", which will bring down your target database and not your auxiliary database, which was what you intended.

For the record, the keyword "target" is an unfortunate choice of syntax during cloning. In IT, a "target" would generally be interpreted as a synonym for "destination", which is where you want to clone *TO*.