Install Oracle9i client on Linux 64bit – “copying naeet.o”

To record silent mode installation script:
./runInstaller -record -destinationFile /tmp/oracle9iclient.rsp

The recording session hang up when it was 63% complete when doing “copying naeet.o”.

The fix: export LD_ASSUME_KERNEL=2.4.1 (note: export LD_ASSUME_KERNEL=2.4.21 did not work in this case:

$ uname -r
2.6.9-67.ELsmp

./runInstaller -silent -responsefile /home/oracle/admin/Disk1/response/oracle9iclient.rsp
$ Initializing Java Virtual Machine from /tmp/OraInstall2009-02-25_01-20-48AM/jre/bin/java. Please wait…
In Product Registration Page
In UNIX Group Name Page
Install inventory needs to be created on this system before this silent install can proceed. Please run the script in /tmp/orainstRoot.sh with root previleges and retry the silent installation. See /tmp/silentInstall.log for more details.

export LD_ASSUME_KERNEL=2.4.1
$ ./runInstaller -silent -responsefile /home/oracle/admin/Disk1/response/oracle9iclient.rsp
$ Initializing Java Virtual Machine from /tmp/OraInstall2009-02-25_01-24-15AM/jre/bin/java. Please wait…
In Product Registration Page
In UNIX Group Name Page
Inside isClusterMode, bCluster is : false
Inside isCluster, bCluster bfr return is : false
In Cluster Node Selection Page
Inside isCluster, bCluster bfr return is : false
In File Locations Page
In Available Products Page
In Installation Types Page
In Component Locations Page
In Summary Page
Inside isCluster, bCluster bfr return is : false
Inside isCluster, bCluster bfr return is : false
Inside isCluster, bCluster bfr return is : false
In End of Installation Page
The installation of Oracle9i Client was successful.
Please check /home/oracle/oraInventory/logs/silentInstall2009-02-25_01-24-15AM.log for more details.
In Product Registration Page
In UNIX Group Name Page
Inside isCluster, bCluster bfr return is : false
In Cluster Node Selection Page
Inside isCluster, bCluster bfr return is : false

$ cat /etc/issue
Red Hat Enterprise Linux AS release 4 (Nahant Update 6)
Kernel \r on an \m

Metalink notes 360142.1 “When Running OUI, OUI Hangs at 18% Copying naeet.o” mentions to do:
export LD_ASSUME_KERNEL=2.4.21
which did not work in this case.

Metalink notes 377217.1 “What should the value of LD_ASSUME_KERNEL be set to for Linux?”:
RHAS 2.1 2.2.5
RHEL3, RHEL4, OEL4 any value from 2.4.1 to 2.4.19
SLES8, SLES9 2.4.21
RHEL5, OEL5, SLES10 Should not be set

So it is correct that since in this case, it is RHEL4, it should be from 2.4.1 to 2.4.19, not 2.4.21 ..

$ find /lib/ -name libc.so.6
/lib/tls/i686/nosegneg/libc.so.6
/lib/tls/libc.so.6
/lib/i686/libc.so.6
/lib/libc.so.6

# eu-readelf -n /lib/libc.so.6

Note segment of 32 bytes at offset 0×154:
Owner Data size Type
GNU 16 VERSION
OS: Linux, ABI: 2.2.5

# eu-readelf -n /lib/i686/libc.so.6

Note segment of 32 bytes at offset 0×154:
Owner Data size Type
GNU 16 VERSION
OS: Linux, ABI: 2.4.1

# eu-readelf -n /lib/tls/libc.so.6

Note segment of 32 bytes at offset 0×174:
Owner Data size Type
GNU 16 VERSION
OS: Linux, ABI: 2.4.20

Oracle Materialized View

Each night, in OID database I have the following job running (I don’t want to change anything related to built-in table orasso.wwsso_audit_log_table_t, not even creating materialized view/log directly on it):

begin
INSERT INTO SSO_AUDIT_LOG SELECT * FROM orasso.wwsso_audit_log_table_t where LOG_DATE>=(sysdate-1) and LOG_DATE<sysdate;
commit;
end;

For Oracle Portal Last Login Time information:

> CREATE MATERIALIZED VIEW LOG ON SSO_AUDIT_LOG tablespace users with primary key, rowid(user_name) including new values;

Materialized view log created.

> alter materialized view log on “LOGUSER”.”SSO_AUDIT_LOG” add(LOG_DATE);

Materialized view log altered.

> create materialized view mv_last_login
2  refresh fast
3  enable query rewrite
4  as
5  select user_name, max(log_date) from SSO_AUDIT_LOG group by user_name;

Materialized view created.

> select LAST_REFRESH_TYPE, LAST_REFRESH_DATE from user_mviews where mview_name=’MV_LAST_LOGIN’;

> select LAST_REFRESH_TYPE, LAST_REFRESH_DATE from user_mviews where mview_name=’MV_LAST_LOGIN’;

LAST_REF LAST_REFR
——– ———
COMPLETE 13-OCT-08

> desc mlog$_sso_audit_log
Name                                      Null?    Type
—————————————– ——– —————————-
SUBSCRIBER_ID                                      NUMBER
LOG_ID                                             NUMBER
USER_NAME                                          VARCHAR2(256)
M_ROW$$                                            VARCHAR2(255)
SNAPTIME$$                                         DATE
DMLTYPE$$                                          VARCHAR2(1)
OLD_NEW$$                                          VARCHAR2(1)
CHANGE_VECTOR$$                                    RAW(255)
LOG_DATE                                           DATE

> alter materialized view MV_LAST_LOGIN refresh fast on commit;
alter materialized view MV_LAST_LOGIN refresh fast on commit
*
ERROR at line 1:
ORA-32337: cannot alter materialized view with pending changes refresh on
commit

> exec dbms_mview.refresh(‘MV_LAST_LOGIN’);

PL/SQL procedure successfully completed.

> alter materialized view MV_LAST_LOGIN refresh fast on commit;

Materialized view altered.

( If you want fast refresh to occur whenever the database commits a transaction, ON COMMIT clause during creation of the materialized view should be specified )

Oracle Hanganalyze

ref:

http://arup.blogspot.com/2008/08/diagnosing-library-cache-latch.html

215858.1

175006.1

sqlplus -prelim / as sysdba

oradebug setmypid

oradebug hanganalyze 12

RAC Cluster:

oradebug setmypid

oradebug setinst all

oradebug -g def hanganalyze 3

dbfsize

$ /home/oracle/app/oracle/product/11.1.0/db_1/bin/dbfsize redo01.log

Database file: redo01.log
Database file type: file system
Database file size: 102400 512 byte blocks

$ /home/oracle/app/oracle/product/11.1.0/db_1/bin/dbfsize sysaux01.dbf

Database file: sysaux01.dbf
Database file type: file system
Database file size: 106072 8192 byte blocks

Install DBD::Oracle

The following URL are really helpful:

http://onemoretech.blogspot.com/2007/03/oracle-instantclient.html

I followed the same procedure, except I used 11g instant client:

[root@hostname 11g]# ll
total 42720
-rw-r–r– 1 oracle oinstall 42285643 Feb 6 22:48 basic.zip
drwxr-xr-x 3 root root 4096 Feb 6 23:08 instantclient_11_1
-rw-r–r– 1 oracle oinstall 607017 Feb 6 22:46 sdk.zip
-rw-r–r– 1 oracle oinstall 784417 Feb 6 22:47 sqlplus.zip

Download the above 3 files from http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linuxsoft.html. I used CPAN to download and install DBD-Oracle in this case. It would fail in test phase. Just go to CPAN download folder, do ‘make test’, before that, set ORACLE_USERID env variable to be something like ’scott/tiger@tns_name’. In order to get the test to be successfully really quickly, I just grant dba to scott, otherwise some tests might fail. Then ‘make install’ manually.

Today I tried another way to do this:

After line 1446 in file ‘Makefile.PL’, add a hard_coded line to make sure it can fine oci.h and some other header files:

“/usr/include/oracle/11.1.0.1/client/”, # hard_coded

Other steps are almost the same as the steps here at:

http://n8v.enteuxis.org/2008/07/compiling-dbd-oracle-for-linux-with-the-oracle-instant-client/

RMAN OneTime Backupset

RMAN> list backup tag ‘onetime’;

RMAN> change backup tag ‘onetime’ unavailable;

Reason for simple Oracle Streams setup failed

SQL> select * from dba_recoverable_script_errors;

SCRIPT_ID BLOCK_NUM ERROR_NUMBER
——————————– ———- ————
ERROR_MESSAGE
——————————————————————————–
ERROR_CRE
———
43CADDA5148638A9E040C2A396406660 6 -29341
ORA-29341: The transportable set is not self-contained
15-JAN-08

SQL>
SQL> exec dbms_tts.transport_set_check('EX1', FALSE, TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
——————————————————————————–
Sys owned object TE1 in tablespace EX1 not allowed in pluggable set

SQL> drop table te1 purge;

Table dropped.

SQL> exec dbms_tts.transport_set_check(‘EX1′, FALSE, TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL>

SQL> conn strm/strm
Connected.
SQL> DECLARE
2 t_names DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
3 BEGIN
4 t_names(1) := ‘”EX1″‘;
5 DBMS_STREAMS_ADM.MAINTAIN_TABLESPACES(
6 tablespace_names => t_names,
7 source_directory_object => ‘”SRC_DIRS”‘,
8 destination_directory_object => ‘”DST_DIRS”‘,
9 destination_database => ‘EURO.ORACLE.COM’ ,
10 setup_streams => true,
11 script_name => ‘Strm_1200439935274.sql’,
12 script_directory_object => ‘”SRC_DIRS”‘,
13 dump_file_name => ‘Strm_1200439935274.dmp’,
14 capture_name => ‘”STREAMS_CAPTURE”‘,
15 propagation_name => ‘”STREAMS_PROPAGATION”‘,
16 apply_name => ‘”STREAMS_APPLY”‘,
17 source_queue_name => ‘”STRM”.”STREAMS_CAPTURE_Q”‘,
18 destination_queue_name => ‘”STRM”.”STREAMS_CAPTURE_Q”‘,
19 log_file => ‘Strm_1200439935274.log’,
20 bi_directional => true);
21 END;
22 /
DECLARE
*
ERROR at line 1:
ORA-23622: Operation SYS.DBMS_STREAMS_ADM.MAINTAIN_TABLESPACES is in progress.
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 105
ORA-06512: at “SYS.DBMS_STREAMS_MT”, line 2334
ORA-06512: at “SYS.DBMS_STREAMS_MT”, line 7451
ORA-06512: at “SYS.DBMS_STREAMS_ADM”, line 2205
ORA-06512: at line 5

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Pr oduction
With the Partitioning, OLAP and Data Mining options
[oracle@erd-tt-eproof1 ~]$ oerr ora 23622
23622, 00000, “Operation %s.%s.%s is in progress.”
// *Cause: An attempt was made to execute a procedure which was being
// executed in a parallel session or failed execution.
// *Action: Query the DBA_RECOVERABLE_SCRIPT view to identify the operation
// that is currently in progress for the specified invoking
// procedure. Complete the operation before proceeding.
[oracle@erd-tt-eproof1 ~]$ amer

SQL*Plus: Release 10.2.0.2.0 – Production on Tue Jan 15 17:42:27 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from DBA_RECOVERABLE_SCRIPT;

SCRIPT_ID CREATION_ INVOKING_PACKAGE_OWNER
——————————– ——— ——————————
INVOKING_PACKAGE INVOKING_PROCEDURE
—————————— ——————————
INVOKING_USER STATUS TOTAL_BLOCKS DONE_BLOCK_NUM
—————————— ———— ———— ————–
SCRIPT_COMMENT
——————————————————————————–
43CADDA5148638A9E040C2A396406660 15-JAN-08 SYS
DBMS_STREAMS_ADM MAINTAIN_TABLESPACES
STRM ERROR 16 5

SQL> select * from dba_recoverable_script_errors;

SCRIPT_ID BLOCK_NUM ERROR_NUMBER
——————————– ———- ————
ERROR_MESSAGE
——————————————————————————–
ERROR_CRE
———
43CADDA5148638A9E040C2A396406660 6 -29341
ORA-29341: The transportable set is not self-contained
15-JAN-08

SQL> exec DBMS_STREAMS_ADM.RECOVER_OPERATION(‘43CADDA5148638A9E040C2A396406660′,’ROLLBACK’);

PL/SQL procedure successfully completed.

SQL> select * from dba_recoverable_script_errors;

no rows selected

SQL>

After the above troubleshooting, used EM, successfully configured tablespace replication using Oracle Streams.

The following is the script generated by EM:

set echo on;
ACCEPT strm_pwd_src PROMPT ‘Enter Password of Streams Admin “strm” at Source : ‘ HIDE
ACCEPT strm_pwd_dest PROMPT ‘Enter Password of Streams Admin “strm” at Destination : ‘ HIDE
connect “STRM”/&strm_pwd_src;
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => ‘”STREAMS_CAPTURE_QT”‘,
queue_name => ‘”STREAMS_CAPTURE_Q”‘,
queue_user => ‘”STRM”‘);
END;
/
connect “STRM”/&strm_pwd_dest@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=

(PROTOCOL=TCP)(HOST=erd-tt-eproof2.ctc.trb)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=euro.oracle.com)(server=DEDICATED)));
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => ‘”STREAMS_APPLY_QT”‘,
queue_name => ‘”STREAMS_APPLY_Q”‘,
queue_user => ‘”STRM”‘);
END;
/
connect “STRM”/&strm_pwd_src;
create or replace directory “EMSTRMTBLESPCEDIR_0″ AS ‘/home/oracle/oracle/oradata/AMER’;
DECLARE
t_names DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
BEGIN
t_names(1) := ‘”EX1″‘;
DBMS_STREAMS_ADM.MAINTAIN_TABLESPACES(
tablespace_names => t_names,
source_directory_object => ‘”SRC_DIRS”‘,
destination_directory_object => ‘”DST_DIRS”‘,
destination_database => ‘EURO.ORACLE.COM’ ,
setup_streams => true,
script_name => ‘Strm_1200440796848.sql’,
script_directory_object => ‘”SRC_DIRS”‘,
dump_file_name => ‘Strm_1200440796848.dmp’,
capture_name => ‘”STREAMS_CAPTURE”‘,
propagation_name => ‘”STREAMS_PROPAGATION”‘,
apply_name => ‘”STREAMS_APPLY”‘,
source_queue_name => ‘”STRM”.”STREAMS_CAPTURE_Q”‘,
destination_queue_name => ‘”STRM”.”STREAMS_CAPTURE_Q”‘,
log_file => ‘Strm_1200440796848.log’,
bi_directional => true);
END;
/

manually create database again

$ cat initeuro.ora
db_name=euro
db_files = 80
db_file_multiblock_read_count = 8
processes = 200
global_names = TRUE
db_domain = oracle.com
control_files = (/home/oracle/oracle/oradata/EURO/control01.ctl,/home/oracle/oracle/oradata/EURO/control02.ctl,/home/oracle/oracle/oradata/EURO/control03.ctl)
sga_target=450M
sga_max_size=450M
undo_management=auto
undo_tablespace=UNDOTBS1
background_dump_dest=/home/oracle/oracle/admin/bdump
user_dump_dest=/home/oracle/oracle/admin/udump
core_dump_dest=/home/oracle/oracle/admin/cdump

create database euro
maxinstances 4
maxloghistory 200
maxlogmembers 5
maxlogfiles 15
maxdatafiles 500
logfile group 1 (‘/home/oracle/oracle/oradata/EURO/redo01.log’) size 20M,
group 2 (‘/home/oracle/oracle/oradata/EURO/redo02.log’) size 20M,
group 3 (‘/home/oracle/oracle/oradata/EURO/redo03.log’) size 20M
datafile ‘/home/oracle/oracle/oradata/EURO/system01.dbf’ size 400M autoextend on maxsize unlimited extent management local
sysaux datafile ‘/home/oracle/oracle/oradata/EURO/sysaux01.dbf’ size 300M autoextend on maxsize unlimited
default temporary tablespace TEMP tempfile ‘/home/oracle/oracle/oradata/EURO/temp01.dbf’ size 250M autoextend on maxsize unlimited
default tablespace USERS datafile ‘/home/oracle/oracle/oradata/EURO/users01.dbf’ size 50M autoextend on maxsize unlimited extent management local segment space management auto
undo tablespace UNDOTBS1 datafile ‘/home/oracle/oracle/oradata/EURO/undo01.dbf’ size 250M autoextend on maxsize unlimited
character set us7ascii
national character set AL16UTF16
user sys identified by oracle
user system identified by oracle;

@?/rdbms/admin/catalog

@?/rdbms/admin/catproc

SQL> conn system/oracle
Connected.
SQL> @?/sqlplus/admin/pupbld

cd $ORACLE_HOME/demo/schema

conn / as sysdba

create spfile from pfile;

turn on flashback, create restore point before_ord guarantee flashback database; mount mode, then ‘flashback database to restore point before_ord’

SQL> @mkplug

specify password for SYS as parameter 1:
Enter value for 1: oracle

specify password for HR as parameter 2:
Enter value for 2: hr

specify password for OE as parameter 3:
Enter value for 3: oe

specify password for PM as parameter 4:
Enter value for 4: pm

specify password for IX as parameter 5:
Enter value for 5: ix

specify password for SH as parameter 6:
Enter value for 6: sh

specify password for BI as parameter 7:
Enter value for 7: bi

specify INPUT metadata import file as parameter 8:
Enter value for 8: /home/oracle/oracle/product/10.2.0/db_1/assistants/dbca/templ ates/example.dmp

specify INPUT database backup file for tablespace EXAMPLE as parameter 9:
Enter value for 9: /home/oracle/oracle/product/10.2.0/db_1/assistants/dbca/templ ates/example01.dfb

specify OUTPUT database file for tablespace EXAMPLE as parameter 10:
Enter value for 10: /home/oracle/oracle/oradata/EURO/example01.dbf

specify OUTPUT log directory as parameter 11:
Enter value for 11: /home/oracle/oracle/

472408.1 (Demo Schema Installation Using Mkplug.Sql Failed With Ora-19583 Ora-19870 Ora-19615 Ora-19613)

ORA-19613: datafile 10 not found in backup set

The Character Set Check Performed When Plugging In a Transportable Tablespace

The database character set has to be us7ascii.

The compatible parameter has to be 10.2.0.2.2, instead of the default 10.2.0.0.0.

Need to install MDSYS otherwise some tables like OE.CUSTOMERS won’t be imported

• Install ORD: @?/ord/admin/ordinst.sql SYSAUX SYSAUX

• Install JAVA: @?/javavm/install/initjvm.sql @?/xdk/admin/initxml.sql

@?/xdk/admin/xmlja.sql @?/rdbms/admin/catjava.sql
@?/rdbms/admin/catexf.sql

• Install XDB: @?/rdbms/admin/catqm.sql change_on_install SYSAUX TEMP @?/rdbms/admin/catxdbj.sql
• Install InterMedia: @?/ord/im/admin/iminst.sql
• Install OLAP: @?/olap/admin/olap.sql SYSAUX TEMP
• Install Spatial: @?/md/admin/mdinst

• recompile invalid stuff: utlrp.sql

TEST dbms_backup_restore for the above mkplug:

DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
sys.dbms_backup_restore.restoreSetDatafile;
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>6,toname=>'/home/oracle/oracle/ex1.dbf');
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/home/oracle/oracle/product/10.2.0/db_1/assistants/dbca/templates/example01.dfb', params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/


Note: dfnumber should be the next highest file_id in dba_data_files.

Rebuilding Portal Index

> analyze index PORTAL.WWCTX_SSOS_IX2 validate structure;
Index analyzed.

> select used_space, btree_space, pct_used, height, del_lf_rows_len, lf_rows_len from index_stats where name='WWCTX_SSOS_IX2';

USED_SPACE BTREE_SPACE   PCT_USED     HEIGHT DEL_LF_ROWS_LEN LF_ROWS_LEN
---------- ----------- ---------- ---------- --------------- -----------
 378691241   747601176         51          4       369366798   374566486

> alter index PORTAL.WWCTX_SSOS_IX2 rebuild online parallel 3;

> analyze index PORTAL.WWCTX_SSOS_IX2 validate structure;
Index analyzed.

> select used_space, btree_space, pct_used, height, del_lf_rows_len, lf_rows_len from index_stats where name='WWCTX_SSOS_IX2';

USED_SPACE BTREE_SPACE   PCT_USED     HEIGHT DEL_LF_ROWS_LEN LF_ROWS_LEN
---------- ----------- ---------- ---------- --------------- -----------
   5468740     6197124         89          3               0     5435852

> select dbms_metadata.get_ddl('INDEX','WWCTX_SSOS_IX2','PORTAL') from dual;

DBMS_METADATA.GET_DDL('INDEX','WWCTX_SSOS_IX2','PORTAL')
--------------------------------------------------------------------------------

  CREATE INDEX "PORTAL"."WWCTX_SSOS_IX2" ON "PORTAL"."WWCTX_SSO_SESSION$" ("COOK
IE")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 229376 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 20 FREELIST GROUPS 8 BUFFER_POOL DEFAULT)
  TABLESPACE "PORTAL"
  PARALLEL 3

The above FREELISTS and FREELIST GROUPS are for RAC.

Ref: http://download-uk.oracle.com/docs/cd/B10501_01/rac.920/a96598/freelist.htm

From AWR:
Segments by Row Lock Waits

Owner Tablespace Name Object Name Subobject Name Obj. Type Row Lock Waits %Total
PORTAL PORTAL WWCTX_SSOS_PK INDEX 7 100.00

SQL> select lf_blk_len,del_lf_rows_len,used_space,btree_space,pct_used from index_stats where name='WWCTX_SSOS_PK';

LF_BLK_LEN DEL_LF_ROWS_LEN USED_SPACE BTREE_SPACE PCT_USED
---------- --------------- ---------- ----------- ----------
6460 2026 89150 518368 18

SQL> alter index PORTAL.WWCTX_SSOS_PK coalesce;

Index altered.

SQL> analyze index PORTAL.WWCTX_SSOS_PK validate structure;

Index analyzed.

SQL> select lf_blk_len,del_lf_rows_len,used_space,btree_space,pct_used from index_stats where name='WWCTX_SSOS_PK';

LF_BLK_LEN DEL_LF_ROWS_LEN USED_SPACE BTREE_SPACE PCT_USED
---------- --------------- ---------- ----------- ----------
6460 0 86718 92008 95

SQL>

Oracle/Memory

Todo list:
To go through this very nice blog at http://www.pythian.com/blogs/741/pythian-goodies-free-memory-swap-oracle-and-everything.