upg8to9.txt ------------ I started this effort by locating the executable to the "Upgrade Assistant": bikle9-i9-% cd $ORACLE_HOME/bin /u01/app/oracle/product/9201/bin bikle9-i9-% ls -la u* -rwxr-xr-x 1 oracle dba 2279 Mar 4 12:07 umu* -rwxr-xr-x 1 oracle dba 149684 Apr 29 2002 unzip* bikle9-i9-% ls -la *a -rwxr-xr-x 1 oracle dba 3828 Mar 4 12:22 dbca* -rwxr-xr-x 1 oracle dba 3580 Mar 4 12:08 dbua* -rwxr-xr-x 1 oracle dba 681 Mar 4 12:07 dropjava* -rwxr-x--x 1 oracle dba 313936 Mar 4 12:22 dumpsga* -rwxr-xr-x 1 oracle dba 2660 Mar 4 12:22 ela* -rwxr-xr-x 1 oracle dba 2783 Mar 4 12:22 emca* -rwxr-x--x 1 oracle dba 38732 Mar 4 12:22 hsdepxa* -rwxr-xr-x 1 oracle dba 682 Mar 4 12:07 loadjava* -rwxr-x--x 1 oracle dba 313952 Mar 4 12:22 mapsga* -rwxr-xr-x 1 oracle dba 4737 Mar 4 12:04 netca* -rwxr-xr-x 1 oracle dba 2033428 Apr 28 2002 schema* bikle9-i9-% Then, I ran it: bikle9-i9-% cd /u01/home/oracle bikle9-i9-% envg oracle PATH=/usr/ccs/bin:/usr/bin:/etc:/usr/openwin/bin:/usr/sbin:/u01/home/oracle/bikle/j2sdk14/bin:.:/u01/app/oracle/product/9201/bin:/usr/local/bin:/usr/sbin:/sbin ORACLE_BASE=/u01/app/oracle/ CLASSPATH=.:/u01/app/oracle/product/9201/jdbc/lib/classes12.zip:/u01/home/oracle/bikle/j2sdk14/jre/lib/rt.jar:/u01/app/oracle/product/9201/JRE/lib/rt.jar:/u01/app/oracle/product/9201/jlib:/u01/app/oracle/product/9201/rdbms/jlib:/u01/app/oracle/product/9201/omwb/jlib:/u01/app/oracle/product/9201/lib:/u01/app/oracle/product/9201/network/jlib:/u01/app/oracle/product/9201/ord/jlib:/u01/app/oracle/product/9201/owm/jlib:/u01/app/oracle/product/9201/plsql/jlib:/u01/app/oracle/product/9201/sysman/jlib LOGNAME=oracle MAIL=/var/mail/oracle ORACLE_SID=i9 PS1=oracle:i9@bikle9.db.bikle.com $ USER=oracle JAVA_HOME=/u01/home/oracle/bikle/j2sdk14 HOME=/u01/home/oracle LD_LIBRARY_PATH=/usr/lib:/usr/local/lib:/usr/dt/lib:/u01/app/oracle/product/9201/lib ORACLE_HOME=/u01/app/oracle/product/9201 PWD=/u01/home/oracle bikle9-i9-% $ORACLE_HOME/bin/dbua& [1] 18681 bikle9-i9-% The tool noticed my character set as this: WE8ISO8859P9 It then told me that the database is using WE8ISO8859P9 as the NCHAR type. It told me that, under 9i, only UTF8 (or AL16UTF16) may be used as the NCHAR type. It told me to look in the upgrade guide for instructions on how to change the data types of NCHAR columns in user tables so that I could continue to use the WE8ISO8859P9 character set. So... I iconized the Upgrade Assistant and went on a reading trip. I found the following link: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96530/migaftrm.htm#1009299 My reading notes from above link: My DB National Character Set will be converted to AL16UTF16 if it was not UTF8 in the old release. This means my character set will get converted from WE8ISO8859P9 to AL16UTF16. You will encounter the following error when attempting to use the NCHAR columns in Oracle9i until you convert them: ORA-12714: invalid national character set specified Steps: -Shutdown DB -STARTUP RESTRICT -sqlplus '/as sysdba' @?/rdbms/admin/utlnchar.sql -SHUTDOWN IMMEDIATE -STARTUP With that cached in my head, I returned to the Upgrade Assistant and pressed "yes" so I could continue. Eventually the Upgrade Assistant asked my permission to allow it to cold-backup the DB; I told it I already have a backup. The Upgrade Assistant then issued a mini status report with title: "Database Upgrade Summary". The report contained some useful info but I was unable to grab it with my mouse buffer. It listed: -init.ora params it intended to add, change, and rename. -version info -old/new oracle homes -a reminder to manually upgrade the nchar columns after the DB upgrade I told it to continue and eventually it finished and then showed me the same summary report; I wish I could have saved that useful report. Next, I checked the process listing for the listener: bikle9-i9-% psorg tns oracle 7718 7487 0 11:52:09 pts/10 0:00 grep -i tns oracle 20980 1 0 Feb 24 ? 0:04 /u01/app1/oracle/product/817/bin/tnslsnr LISTENER -inherit bikle9-i9-% It looks like the Upgrade Assistant did not replace the 8i listener with a 9i listener. Next, I tried connecting to i9 (Now a 9i DB) from another host: ty-% sql bikle/x49erfan@i9 SQL*Plus: Release 8.1.7.0.0 - Production on Thu Mar 6 11:49:36 2003 (c) Copyright 2000 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> This is good news; it validated some things: -The names server was not confused by the upgrade -The 8i listener had registered the 9i DB -My 8i client could talk with the 9i server Next, I studied the dir: $ORACLE_HOME/dbs/ I found this: bikle9-i9-% cd $ORACLE_HOME/dbs/ /u01/app/oracle/product/9201/dbs bikle9-i9-% ls -latr total 64 -rw-r--r-- 1 oracle dba 12920 Apr 28 2002 initdw.ora -rw-r--r-- 1 oracle dba 8385 Apr 28 2002 init.ora drwxr-xr-x 52 oracle dba 1024 Mar 4 12:37 ../ -rw-r----- 1 oracle dba 3584 Mar 5 19:28 spfilei9.ora drwxr-xr-x 2 oracle dba 512 Mar 5 19:28 ./ -rw-rw---- 1 oracle dba 24 Mar 5 19:28 lkI9 -rwSr----- 1 oracle dba 2560 Mar 6 11:48 orapwi9 bikle9-i9-% It's obvious that the upgraded DB was making use of an spfile. I inspected the contents of the spfile: bikle9-i9-% sqs SQL*Plus: Release 9.2.0.1.0 - Production on Thu Mar 6 12:01:06 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> create pfile='/u01/app/oracle/product/9201/dbs/initi9-ora.txt' 2 from spfile='/u01/app/oracle/product/9201/dbs/spfilei9.ora'; File created. SQL> !cat /u01/app/oracle/product/9201/dbs/initi9-ora.txt *.background_dump_dest='/u01/app/oracle//admin/i90/bdump' *.compatible='8.1.0' *.control_files='/u03/oradata/i9/control01.ctl','/u03/oradata/i9/control02.ctl','/u03/oradata/i9/control03.ctl' *.core_dump_dest='/u01/app/oracle//admin/i90/cdump' *.db_block_buffers=2048 *.db_block_size=8192 *.db_domain='db.bikle.com' *.db_name='i9' *.dispatchers='(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)' *.instance_name='i9' *.java_pool_size=50331648 *.large_pool_size=8388608 *.log_archive_dest_1='location=/u01/app/oracle/admin/i9/arch' *.log_archive_format='arch_%t_%s.arc' *.log_archive_start=TRUE *.log_buffer=3000 *.log_checkpoint_interval=32767 *.log_checkpoint_timeout=1800 *.max_enabled_roles=100 *.open_cursors=555 *.os_authent_prefix='' *.pga_aggregate_target=25165824 *.processes=100 *.remote_login_passwordfile='EXCLUSIVE' *.remote_os_authent=TRUE *.rollback_segments='RBS_SQLBIKLE1','RBS_SQLBIKLE2','RBS_SQLBIKLE3','RBS_SQLBIKLE4' *.service_names='i9.db.bikle.com','i9' *.shared_pool_size=77000000 *.sort_area_retained_size=65536 *.sort_area_size=65536 *.user_dump_dest='/u01/app/oracle//admin/i90/udump' *.utl_file_dir='/export/home/bikle_own/SQLBIKLEsvr/util' SQL> Then, I looked at the 9i processes: oracle 20222 18682 0 19:28:21 pts/3 0:00 /u01/app/oracle/product/9201/bin/sqlplus -S /NOLOG oracle 20266 1 0 19:28:46 ? 0:00 ora_pmon_i9 oracle 20268 1 0 19:28:46 ? 0:00 ora_dbw0_i9 oracle 20270 1 0 19:28:46 ? 0:00 ora_lgwr_i9 oracle 20272 1 0 19:28:46 ? 0:01 ora_ckpt_i9 oracle 20274 1 0 19:28:46 ? 0:02 ora_smon_i9 oracle 20276 1 0 19:28:46 ? 0:00 ora_reco_i9 oracle 20278 1 0 19:28:46 ? 0:00 ora_s000_i9 oracle 20280 1 0 19:28:46 ? 0:00 ora_d000_i9 oracle 20284 1 0 19:28:46 ? 0:00 ora_arc0_i9 oracle 20286 1 0 19:28:46 ? 0:00 ora_arc1_i9 oracle 20289 20222 0 19:28:52 ? 0:00 oraclei9 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) The listing shows a sqlplus script still running from the Upgrade Assistant. I exited the Upgrade Assistant and the script process went away. Next, I followed the instructions to manually upgrade the tables in the i9 DB which contain the NCHAR datatype. A screen dump: bikle9-i9-% sqlplus '/as sysdba' SQL*Plus: Release 9.2.0.1.0 - Production on Thu Mar 6 12:09:54 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> @?/rdbms/admin/utlnchar.sql Warning: The script is to migrate NCHAR data, including nchar nvarcha2 and nclob, from 8i to 9i. Once it runs, it can not undo what have been done. To run it, 1) use SQLPLUS and 2) connect AS SYSDBA Press ctrl-C, then return to quit or any other key to continue: DROP TABLE nrmig0000 * ERROR at line 1: ORA-00942: table or view does not exist Table created. drop table fixedcs_0000 * ERROR at line 1: ORA-00942: table or view does not exist Table created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. Procedure created. Function created. Procedure created. Procedure created. PL/SQL procedure successfully completed. Table dropped. Procedure dropped. Procedure dropped. Table dropped. Procedure dropped. Function dropped. SQL> SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production bikle9-i9-% Next, I changed the compatible parameter: bikle9-i9-% sqs SQL*Plus: Release 9.2.0.1.0 - Production on Thu Mar 6 12:12:03 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> ALTER SYSTEM SET compatible='9.2.0.1.0' SCOPE=SPFILE; System altered. SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP ORACLE instance started. Total System Global Area 185159740 bytes Fixed Size 454716 bytes Variable Size 167772160 bytes Database Buffers 16777216 bytes Redo Buffers 155648 bytes Database mounted. Database opened. SQL> SHOW PARAMETERS compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 9.2.0.1.0 SQL> Next, I began the process of replacing rollback segments with an UNDO tablespace. First, I created an UNDO tablespace: SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /u03/oradata/i9/SQLBIKLE_qa /u03/oradata/i9/SQLBIKLE_multimedia /u03/oradata/i9/SQLBIKLE_temporary_tablespace /u03/oradata/i9/SQLBIKLE_index_tablespace /u03/oradata/i9/SQLBIKLE_tablespace /u03/oradata/i9/indx01.dbf /u03/oradata/i9/users01.dbf /u03/oradata/i9/temp01.dbf /u03/oradata/i9/rbs01.dbf /u03/oradata/i9/tools01.dbf /u03/oradata/i9/system01.dbf 11 rows selected. SQL> CREATE UNDO TABLESPACE undots 2 DATAFILE '/u03/oradata/i9/undo01.dbf' SIZE 1000M 3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 200K; EXTENT MANAGEMENT LOCAL UNIFORM SIZE 200K * ERROR at line 3: ORA-30024: Invalid specification for CREATE UNDO TABLESPACE SQL> c/UNIFORM SIZE 200K/ 3* EXTENT MANAGEMENT LOCAL SQL> l 1 CREATE UNDO TABLESPACE undots 2 DATAFILE '/u03/oradata/i9/undo01.dbf' SIZE 1000M 3* EXTENT MANAGEMENT LOCAL SQL> / Tablespace created. SQL> Then, I updated some DB parameters: SQL> connect bikle/x49erfan@i9 as sysdba Connected. SQL> set time on 13:42:17 SQL> 13:42:18 SQL> show parameters undo NAME TYPE VALUE ------------------------------------ ------- ------------------------------ undo_management string MANUAL undo_retention integer 900 undo_suppress_errors boolean FALSE undo_tablespace string 13:42:37 SQL> ALTER SYSTEM SET undo_tablespace = undots SCOPE=SPFILE; System altered. 13:44:47 SQL> ALTER SYSTEM SET undo_suppress_errors = true SCOPE=SPFILE; System altered. 13:45:22 SQL> ALTER SYSTEM SET undo_management = auto SCOPE=SPFILE; System altered. 13:45:52 SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down. 13:46:12 SQL> STARTUP LRM-00109: could not open parameter file '?/dbs/init@.ora' ORA-01078: failure in processing system parameters 13:46:28 SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production ty-% telnet bikle9 Connected to bikle9. Escape character is '^]'. SunOS 5.8 login: oracle Password: Last login: Thu Mar 6 09:49:03 from ty Sun Microsystems Inc. SunOS 5.8 Generic Patch October 2001 bikle9> i9.csh bikle9-i9-% sqs SQL*Plus: Release 9.2.0.1.0 - Production on Thu Mar 6 13:47:09 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> set time on 13:47:16 SQL> STARTUP ORACLE instance started. Total System Global Area 185159740 bytes Fixed Size 454716 bytes Variable Size 167772160 bytes Database Buffers 16777216 bytes Redo Buffers 155648 bytes Database mounted. Database opened. 13:47:29 SQL> 13:47:29 SQL> show parameters rollback NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fast_start_parallel_rollback string LOW max_rollback_segments integer 30 rollback_segments string RBS_SQLBIKLE1, RBS_SQLBIKLE2, RB S_SQLBIKLE3, RBS_SQLBIKLE4 transactions_per_rollback_segment integer 5 13:50:03 SQL> ALTER SYSTEM SET rollback_segments = '' SCOPE = BOTH; ALTER SYSTEM SET rollback_segments = '' SCOPE = BOTH; ALTER SYSTEM SET rollback_segments = '' SCOPE = BOTH * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified 13:50:49 SQL> ALTER SYSTEM SET rollback_segments = '' SCOPE = SPFILE; System altered. 13:51:05 SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down. 13:51:51 SQL> STARTUP ORACLE instance started. Total System Global Area 185159740 bytes Fixed Size 454716 bytes Variable Size 167772160 bytes Database Buffers 16777216 bytes Redo Buffers 155648 bytes Database mounted. Database opened. 13:52:02 SQL> DESC DBA_SEGMENTS Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) SEGMENT_NAME VARCHAR2(81) PARTITION_NAME VARCHAR2(30) SEGMENT_TYPE VARCHAR2(18) TABLESPACE_NAME VARCHAR2(30) HEADER_FILE NUMBER HEADER_BLOCK NUMBER BYTES NUMBER BLOCKS NUMBER EXTENTS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER RELATIVE_FNO NUMBER BUFFER_POOL VARCHAR2(7) 13:52:45 SQL> SELECT SEGMENT_NAME,SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='RBS01'; no rows selected 13:53:30 SQL> c/01/ c/01/ 1* SELECT SEGMENT_NAME,SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='RBS' 13:54:33 SQL> / / SEGMENT_NAME -------------------------------------------------------------------------------- SEGMENT_TYPE ------------------ RBS0 ROLLBACK RBS1 ROLLBACK RBS2 ROLLBACK SEGMENT_NAME -------------------------------------------------------------------------------- SEGMENT_TYPE ------------------ RBS3 ROLLBACK RBS4 ROLLBACK RBS5 ROLLBACK SEGMENT_NAME -------------------------------------------------------------------------------- SEGMENT_TYPE ------------------ RBS6 ROLLBACK RBS7 ROLLBACK RBS8 ROLLBACK SEGMENT_NAME -------------------------------------------------------------------------------- SEGMENT_TYPE ------------------ RBS9 ROLLBACK RBS10 ROLLBACK RBS11 ROLLBACK SEGMENT_NAME -------------------------------------------------------------------------------- SEGMENT_TYPE ------------------ RBS12 ROLLBACK RBS13 ROLLBACK RBS14 ROLLBACK 15 rows selected. 13:54:35 SQL> SELECT 'DROP ROLLBACK SEGMENT '||SEGMENT_NAME||';' FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='RBS' SELECT 'DROP ROLLBACK SEGMENT '||SEGMENT_NAME||';' FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='RBS' 13:56:43 2 13:56:44 SQL> SET PAGES 99 LINES 99 SET PAGES 99 LINES 99 13:56:50 SQL> / / 'DROPROLLBACKSEGMENT'||SEGMENT_NAME||';' --------------------------------------------------------------------------------------------------- DROP ROLLBACK SEGMENT RBS0; DROP ROLLBACK SEGMENT RBS1; DROP ROLLBACK SEGMENT RBS2; DROP ROLLBACK SEGMENT RBS3; DROP ROLLBACK SEGMENT RBS4; DROP ROLLBACK SEGMENT RBS5; DROP ROLLBACK SEGMENT RBS6; DROP ROLLBACK SEGMENT RBS7; DROP ROLLBACK SEGMENT RBS8; DROP ROLLBACK SEGMENT RBS9; DROP ROLLBACK SEGMENT RBS10; DROP ROLLBACK SEGMENT RBS11; DROP ROLLBACK SEGMENT RBS12; DROP ROLLBACK SEGMENT RBS13; DROP ROLLBACK SEGMENT RBS14; 15 rows selected. 13:56:51 SQL> DROP ROLLBACK SEGMENT RBS0; DROP ROLLBACK SEGMENT RBS1; DROP ROLLBACK SEGMENT RBS2; DROP ROLLBACK SEGMENT RBS3; DROP ROLLBACK SEGMENT RBS4; DROP ROLLBACK SEGMENT RBS5; DROP ROLLBACK SEGMENT RBS6; DROP ROLLBACK SEGMENT RBS7; DROP ROLLBACK SEGMENT RBS8; DROP ROLLBACK SEGMENT RBS9; DROP ROLLBACK SEGMENT RBS10; DROP ROLLBACK SEGMENT RBS11; DROP ROLLBACK SEGMENT RBS12; DROP ROLLBACK SEGMENT RBS13; DROP ROLLBACK SEGMENT RBS14; DROP ROLLBACK SEGMENT RBS0; DROP ROLLBACK SEGMENT RBS1; DROP ROLLBACK SEGMENT RBS2; DROP ROLLBACK SEGMENT RBS3; DROP ROLLBACK SEGMENT RBS4; DROP ROLLBACK SEGMENT RBS5; DROP ROLLBACK SEGMENT RBS6; DROP ROLLBACK SEGMENT RBS7; DROP ROLLBACK SEGMENT RBS8; DROP ROLLBACK SEGMENT RBS9; DROP ROLLBACK SEGMENT RBS10; DROP ROLLBACK SEGMENT RBS11; DROP ROLLBACK SEGMENT RBS12; DROP ROLLBACK SEGMENT RBS13; DROP ROLLBACK SEGMENT RBS14; Rollback segment dropped. 13:57:05 SQL> Rollback segment dropped. 13:57:05 SQL> Rollback segment dropped. 13:57:05 SQL> Rollback segment dropped. 13:57:05 SQL> Rollback segment dropped. 13:57:05 SQL> Rollback segment dropped. 13:57:05 SQL> Rollback segment dropped. 13:57:05 SQL> Rollback segment dropped. 13:57:05 SQL> Rollback segment dropped. 13:57:05 SQL> Rollback segment dropped. 13:57:05 SQL> Rollback segment dropped. 13:57:05 SQL> Rollback segment dropped. 13:57:05 SQL> Rollback segment dropped. 13:57:05 SQL> Rollback segment dropped. 13:57:05 SQL> Rollback segment dropped. 13:57:05 SQL> DROP TABLESPACE RBS; DROP TABLESPACE RBS; Tablespace dropped. 13:57:21 SQL> !/bin/rm -f /u03/oradata/i9/rbs01.dbf !/bin/rm -f /u03/oradata/i9/rbs01.dbf 13:57:50 SQL> Next, I began the process of migrating all tablespaces of dictionary managed format to locally managed format. I first bumped into this concept here: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96530/migcompa.htm#1010514 I found more info here: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/tspaces.htm#25019 Above link has this syntax: EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); which suggests I may migrate any dictionary managed tablelspace to locally managed. I then looked in the ... Oracle9i Supplied PL/SQL Packages and Types Reference Release 2 (9.2) Part Number A96612-01: http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/toc.htm A useful link: http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_spadmn.htm#1002674 reading notes: Before you migrate the SYSTEM tablespace, you should migrate any dictionary-managed tablespaces that you may want to use in read/write mode to locally managed. After the SYSTEM tablespace is migrated, you cannot change dictionary-managed tablespaces to read/write. A useful link: http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_spadm3.htm#1004668 reading notes: TABLESPACE_MIGRATE_TO_LOCAL Procedure Use this procedure to migrate the tablespace from a dictionary-managed format to a locally managed format. Tablespaces migrated to locally managed format are user managed. Syntax DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ( tablespace_name allocation_unit relative_fno) Both allocation_unit and relative_fno are optional. example: To migrate a tablespace 'TS1' with minimum extent size 1m, use execute dbms_space_admin.tablespace_migrate_to_local('TS1', 512, 2); Questions: how do I obtain a list of relative_fnos? how do I obtain a list of dictionary managed tablespaces? ans: SQL> desc dba_data_files Name Null? Type ----------------------------------------- -------- ---------------------------- FILE_NAME VARCHAR2(513) FILE_ID NUMBER TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2(9) RELATIVE_FNO NUMBER -- look here AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER SQL> SQL> desc dba_tablespaces Name Null? Type ----------------------------------------- -------- ---------------------------- TABLESPACE_NAME NOT NULL VARCHAR2(30) BLOCK_SIZE NOT NULL NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NOT NULL NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER MIN_EXTLEN NUMBER STATUS VARCHAR2(9) CONTENTS VARCHAR2(9) LOGGING VARCHAR2(9) FORCE_LOGGING VARCHAR2(3) EXTENT_MANAGEMENT VARCHAR2(10) -- look here ALLOCATION_TYPE VARCHAR2(9) PLUGGED_IN VARCHAR2(3) SEGMENT_SPACE_MANAGEMENT VARCHAR2(6) SQL> So, I went looking for dictionary managed tablespaces: 13:57:50 SQL> SELECT extent_management, tablespace_name FROM dba_tablespaces ORDER BY extent_management, tablespace_name; EXTENT_MAN TABLESPACE_NAME ---------- ------------------------------ DICTIONARY INDX DICTIONARY SQLBIKLE DICTIONARY SQLBIKLE_INDEX DICTIONARY SQLBIKLE_MULTIMEDIA DICTIONARY SQLBIKLE_QA DICTIONARY SQLBIKLE_TEMPORARY DICTIONARY SYSTEM DICTIONARY TEMP DICTIONARY TOOLS DICTIONARY USERS LOCAL UNDOTS 11 rows selected. 14:01:49 SQL> I then attempted to migrate the first one named INDX: 14:05:53 SQL> execute dbms_space_admin.tablespace_migrate_to_local('INDX', 512, 6) BEGIN dbms_space_admin.tablespace_migrate_to_local('INDX', 512, 6); END; * ERROR at line 1: ORA-03241: Invalid unit size ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0 ORA-06512: at line 1 14:06:37 SQL> execute dbms_space_admin.tablespace_migrate_to_local('INDX', 1024, 6) BEGIN dbms_space_admin.tablespace_migrate_to_local('INDX', 1024, 6); END; * ERROR at line 1: ORA-03241: Invalid unit size ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0 ORA-06512: at line 1 14:07:06 SQL> 14:08:46 SQL> execute dbms_space_admin.tablespace_migrate_to_local('INDX') PL/SQL procedure successfully completed. 14:10:49 SQL> That went well (if you ignore the errors I had to work around), so I did the others: 14:18:58 SQL> l 1 SELECT 2 'EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('''||tablespace_name||''')' 3 FROM dba_tablespaces 4 WHERE extent_management='DICTIONARY' 5* AND tablespace_name != 'SYSTEM' 14:18:58 SQL> / / 'EXECUTEDBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('''||TABLESPACE_NAME||''')' -------------------------------------------------------------------------------------- EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TOOLS') EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMP') EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('USERS') EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SQLBIKLE') EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SQLBIKLE_INDEX') EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SQLBIKLE_TEMPORARY') EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SQLBIKLE_MULTIMEDIA') EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SQLBIKLE_QA') 8 rows selected. 14:19:06 SQL> 14:20:13 SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TOOLS') PL/SQL procedure successfully completed. 14:20:23 SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMP') BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMP'); END; * ERROR at line 1: ORA-03245: Tablespace has to be dictionary managed, online and permanent to be able to migrate ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0 ORA-06512: at line 1 14:20:30 SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('USERS') PL/SQL procedure successfully completed. 14:20:50 SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SQLBIKLE') PL/SQL procedure successfully completed. 14:20:57 SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SQLBIKLE_INDEX') PL/SQL procedure successfully completed. 14:21:06 SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SQLBIKLE_TEMPORARY') PL/SQL procedure successfully completed. 14:21:16 SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SQLBIKLE_MULTIMEDIA') PL/SQL procedure successfully completed. 14:21:24 SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SQLBIKLE_QA') PL/SQL procedure successfully completed. 14:21:30 SQL> Then, I migrated the SYSTEM tablespace to locally managed mode: 14:30:12 SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM') BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END; * ERROR at line 1: ORA-10647: Tablespace other than SYSTEM,UNDOTS, TEMP not found in read only mode ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0 ORA-06512: at line 1 14:39:26 SQL> SELECT 'ALTER TABLESPACE '||TABLESPACE_NAME||' READ ONLY;' FROM DBA_TABLESPACES 14:39:39 2 / / 'ALTERTABLESPACE'||TABLESPACE_NAME||'READONLY;' ---------------------------------------------------------- ALTER TABLESPACE SYSTEM READ ONLY; ALTER TABLESPACE TOOLS READ ONLY; ALTER TABLESPACE TEMP READ ONLY; ALTER TABLESPACE USERS READ ONLY; ALTER TABLESPACE INDX READ ONLY; ALTER TABLESPACE SQLBIKLE READ ONLY; ALTER TABLESPACE SQLBIKLE_INDEX READ ONLY; ALTER TABLESPACE SQLBIKLE_TEMPORARY READ ONLY; ALTER TABLESPACE SQLBIKLE_MULTIMEDIA READ ONLY; ALTER TABLESPACE SQLBIKLE_QA READ ONLY; ALTER TABLESPACE UNDOTS READ ONLY; 11 rows selected. 14:39:41 SQL> Ran these: ALTER TABLESPACE TOOLS READ ONLY; ALTER TABLESPACE USERS READ ONLY; ALTER TABLESPACE INDX READ ONLY; ALTER TABLESPACE SQLBIKLE READ ONLY; ALTER TABLESPACE SQLBIKLE_INDEX READ ONLY; ALTER TABLESPACE SQLBIKLE_TEMPORARY READ ONLY; ALTER TABLESPACE SQLBIKLE_MULTIMEDIA READ ONLY; ALTER TABLESPACE SQLBIKLE_QA READ ONLY; Then this: 14:35:40 SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM') PL/SQL procedure successfully completed. Then these: ALTER TABLESPACE TOOLS READ WRITE; ALTER TABLESPACE USERS READ WRITE; ALTER TABLESPACE INDX READ WRITE; ALTER TABLESPACE SQLBIKLE READ WRITE; ALTER TABLESPACE SQLBIKLE_INDEX READ WRITE; ALTER TABLESPACE SQLBIKLE_TEMPORARY READ WRITE; ALTER TABLESPACE SQLBIKLE_MULTIMEDIA READ WRITE; ALTER TABLESPACE SQLBIKLE_QA READ WRITE; After migrating the table spaces, I looked at memory structures in the SGA: 14:39:41 SQL> show parameters size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ bitmap_merge_area_size integer 1048576 create_bitmap_area_size integer 8388608 db_16k_cache_size big integer 0 db_2k_cache_size big integer 0 db_32k_cache_size big integer 0 db_4k_cache_size big integer 0 db_8k_cache_size big integer 0 db_block_size integer 8192 db_cache_size big integer 0 db_keep_cache_size big integer 0 db_recycle_cache_size big integer 0 global_context_pool_size string hash_area_size integer 131072 java_max_sessionspace_size integer 0 java_pool_size big integer 50331648 large_pool_size big integer 16777216 max_dump_file_size string UNLIMITED object_cache_max_size_percent integer 10 object_cache_optimal_size integer 102400 olap_page_pool_size integer 33554432 oracle_trace_collection_size integer 5242880 parallel_execution_message_size integer 2148 sga_max_size big integer 185159740 shared_pool_reserved_size big integer 4194304 shared_pool_size big integer 83886080 sort_area_retained_size integer 65536 sort_area_size integer 65536 workarea_size_policy string AUTO 15:05:40 SQL> show parameters aggregate NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 25165824 15:08:10 SQL> show parameters block NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_buffers integer 2048 db_block_checking boolean FALSE db_block_checksum boolean TRUE db_block_size integer 8192 db_file_multiblock_read_count integer 8 15:09:42 SQL> show sga Total System Global Area 185159740 bytes Fixed Size 454716 bytes Variable Size 167772160 bytes Database Buffers 16777216 bytes Redo Buffers 155648 bytes I made a few changes using 'ALTER SYSTEM SET...' syntax. I attempted to bounce the DB and got this error: ORA-00381: cannot use both new and old parameters for buffer cache size specification Since I could not figure out how to remove parameters using 'ALTER SYSTEM SET...' syntax, I created a pfile from the spfile and then edited the pfile. Then, I tried to create the spfile from the pfile I had just edited: 15:34:41 SQL> L 1 CREATE spfile='/u01/app/oracle/product/9201/dbs/spfilei9.ora' 2* FROM PFILE='/u01/app/oracle/product/9201/dbs/initi9-ora2.txt' 15:34:42 SQL> / CREATE spfile='/u01/app/oracle/product/9201/dbs/spfilei9.ora' * ERROR at line 1: ORA-00600: internal error code, arguments: [908], [], [], [], [], [], [], [] I reacted to this with a shutdown abort and then this: bikle9-i9-% mv spfilei9.ora spfilei9.2003.0306.ora bikle9-i9-% sqs SQL*Plus: Release 9.2.0.1.0 - Production on Thu Mar 6 15:36:42 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> CREATE spfile='/u01/app/oracle/product/9201/dbs/spfilei9.ora' 2 FROM PFILE='/u01/app/oracle/product/9201/dbs/initi9-ora2.txt' 3 / File created. SQL> startup ORACLE instance started. Total System Global Area 269045940 bytes Fixed Size 454836 bytes Variable Size 134217728 bytes Database Buffers 134217728 bytes Redo Buffers 155648 bytes Database mounted. Database opened. SQL> Now that I had memory structures setup the way I wanted, I wanted to see if the DB was still in archivelog mode (in other words, did the Upgrade Assistant mess up archiving?) and if yes, was it automatically archiving: 15:46:39 SQL> alter system switch logfile; alter system switch logfile; System altered. 15:46:48 SQL> show parameters arch show parameters arch NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ archive_lag_target integer 0 log_archive_dest string log_archive_dest_1 string location=/u01/app/oracle/admin /i9/arch log_archive_dest_10 string log_archive_dest_2 string log_archive_dest_3 string log_archive_dest_4 string log_archive_dest_5 string log_archive_dest_6 string log_archive_dest_7 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_8 string log_archive_dest_9 string log_archive_dest_state_1 string enable log_archive_dest_state_10 string enable log_archive_dest_state_2 string enable log_archive_dest_state_3 string enable log_archive_dest_state_4 string enable log_archive_dest_state_5 string enable log_archive_dest_state_6 string enable log_archive_dest_state_7 string enable log_archive_dest_state_8 string enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_9 string enable log_archive_duplex_dest string log_archive_format string arch_%t_%s.arc log_archive_max_processes integer 2 log_archive_min_succeed_dest integer 1 log_archive_start boolean TRUE log_archive_trace integer 0 remote_archive_enable string true standby_archive_dest string ?/dbs/arch 15:47:03 SQL> !ls -latr /u01/app/oracle/admin/i9/arch !ls -latr /u01/app/oracle/admin/i9/arch total 48100 -rw-r----- 1 oracle dba 1236480 Feb 17 13:22 arch_1_1003.arc -rw-r----- 1 oracle dba 2094592 Feb 18 16:07 arch_1_1004.arc -rw-r----- 1 oracle dba 2093568 Feb 18 16:07 arch_1_1005.arc -rw-r----- 1 oracle dba 2097664 Feb 18 16:07 arch_1_1006.arc -rw-r----- 1 oracle dba 2097152 Feb 18 16:07 arch_1_1007.arc drwxr-xr-x 13 oracle dba 512 Feb 18 19:04 .. -rw-r----- 1 oracle dba 852480 Mar 5 18:57 arch_1_1.arc -rw-r----- 1 oracle dba 9418752 Mar 6 12:13 arch_1_78.arc -rw-r----- 1 oracle dba 4638208 Mar 6 15:46 arch_1_79.arc drwxr-xr-x 2 oracle dba 512 Mar 6 15:46 . 15:47:28 SQL> That looks good; next, I wanted to see if the 9.2 software could support client requests to the 8.1.7 DBs in my network: bikle9-bikledev1-% sql bikle/x49erfan@bikledev1 sql bikle/x49erfan@bikledev1 SQL*Plus: Release 8.1.7.0.0 - Production on Thu Mar 6 15:50:26 2003 (c) Copyright 2000 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production SQL> exit exit Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production bikle9-bikledev1-% exit exit bikle9-bikledev1-% bikle9> i9.csh i9.csh bikle9-i9-% sql bikle/x49erfan@bikledev1 sql bikle/x49erfan@bikledev1 SQL*Plus: Release 9.2.0.1.0 - Production on Thu Mar 6 15:50:47 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. ERROR: ORA-12154: TNS:could not resolve service name Enter user-name: As you can see, the 8.1.7.0.0 sqlplus works fine and the 9.2.0.1.0 sqlplus is broken. I looked for the sqlnet.ora. I did not find it; it appears that the Upgrade Assistant does not take care of this for me. I manually copied the sqlnet.ora over from the 8.1.7 tree. I then checked the sqlnet.ora: bikle9-i9-% cat /u01/app/oracle/product/9201/network/admin/sqlnet.ora # SQLNET.ORA Network Configuration File: /u01/app/oracle/product/817/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.PREFERRED_SERVERS = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = bikle9.db.bikle.com)(PORT = 1575)) ) NAMES.DEFAULT_DOMAIN = db.bikle.com NAMES.DIRECTORY_PATH= (ONAMES, TNSNAMES, HOSTNAME) NAMESCTL.NOCONFIRM=ON bikle9-i9-% I tried again to use 9.2.0.1 as an 8i client: bikle9-i9-% sql bikle/x49erfan@bikledev1 sql bikle/x49erfan@bikledev1 SQL*Plus: Release 9.2.0.1.0 - Production on Thu Mar 6 15:56:55 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production SQL> exit exit Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production bikle9-i9-% sql bikle/x49erfan@i9 sql bikle/x49erfan@i9 SQL*Plus: Release 9.2.0.1.0 - Production on Thu Mar 6 15:57:09 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> It worked! As you can see I also tried using it to act as a client to the 9.2 DB and that worked too; I was happy. Next, I worked on starting up the Apache web server. The script to start the Apache web server was located here: $ORACLE_HOME/Apache/Apache/bin/apachectl I ran it from the oracle account: bikle9-i9-% date Thu Mar 6 19:03:57 PST 2003 bikle9-i9-% ./apachectl start ./apachectl start: httpd started bikle9-i9-% ps -ef|grep 19: oracle 15666 15663 0 19:04:11 ? 0:00 /u01/app/oracle/product/9201/Apache/Apache/bin/httpd -d /u01/app/oracle/product oracle 15609 5444 0 19:02:50 pts/11 0:00 /bin/ksh i9.csh oracle 15681 15663 0 19:04:12 ? 0:00 /u01/app/oracle/product/9201/Apache/Apache/bin/httpd -d /u01/app/oracle/product oracle 15686 15663 0 19:04:12 ? 0:00 /u01/app/oracle/product/9201/Apache/Apache/bin/httpd -d /u01/app/oracle/product oracle 15701 15663 0 19:04:15 ? 0:00 /u01/app/oracle/product/9201/Apache/Apache/bin/httpd -d /u01/app/oracle/product oracle 15671 15667 1 19:04:11 ? 0:01 /u01/app/oracle/product/9201/jdk/bin/../bin/sparc/native_threads/java org.apach oracle 15667 15663 0 19:04:11 ? 0:00 /u01/app/oracle/product/9201/Apache/Apache/bin/httpd -d /u01/app/oracle/product oracle 15689 15663 0 19:04:12 ? 0:00 /u01/app/oracle/product/9201/Apache/Apache/bin/httpd -d /u01/app/oracle/product oracle 15670 15666 0 19:04:11 ? 0:00 /u01/app/oracle/product/9201/Apache/Apache/fcgi-bin/echo oracle 15668 15666 0 19:04:11 ? 0:00 /u01/app/oracle/product/9201/bin/isqlplus oracle 15610 15609 0 19:02:50 pts/11 0:00 /bin/csh oracle 15663 1 1 19:04:11 ? 0:01 /u01/app/oracle/product/9201/Apache/Apache/bin/httpd -d /u01/app/oracle/product oracle 15669 15667 1 19:04:11 ? 0:01 /u01/app/oracle/product/9201/jdk/bin/../bin/sparc/native_threads/java -DORACLE_ oracle 15683 15663 0 19:04:12 ? 0:00 /u01/app/oracle/product/9201/Apache/Apache/bin/httpd -d /u01/app/oracle/product oracle 15708 15610 0 19:04:33 pts/11 0:00 grep 19: oracle 15690 15663 0 19:04:12 ? 0:00 /u01/app/oracle/product/9201/Apache/Apache/bin/httpd -d /u01/app/oracle/product oracle 15704 15663 0 19:04:17 ? 0:00 /u01/app/oracle/product/9201/Apache/Apache/bin/httpd -d /u01/app/oracle/product root 15707 15610 0 19:04:33 pts/11 0:00 ps -ef bikle9-i9-% I then tried connecting to it with this url: http://bikle9:7778 I got a nice banner page! Next I looked at implementing some simple security techniques. I looked at this file: $ORACLE_HOME/Apache/Apache/conf/httpd.conf I found the text I was looking for about 1/4 of the way into the file: ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo # # If you wish httpd to run as a different user or group, you must run # httpd as root initially and it will switch. # # User/Group: The name (or #number) of the user/group to run httpd as. # . On SCO (ODT 3) use "User nouser" and "Group nogroup". # . On HPUX you may not be able to use shared memory as nobody, and the # suggested workaround is to create a user www and use that user. # NOTE that some kernels refuse to setgid(Group) or semctl(IPC_SET) # when the value of (unsigned)Group is above 60000; # don't use Group nobody on these systems! # User oracle Group dba ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo From the oracle account, I shutdown apache: bikle9-i9-% ./apachectl stop ./apachectl stop: httpd stopped bikle9-i9-% Next, changed ownership of the log directory and the files in it: bikle9-i9-% cd .. /u01/app/oracle/product/9201/Apache/Apache bikle9-i9-% ls ./ bin/ conf/ htdocs/ include/ logs/ ports.ini setupinfo.txt ../ cgi-bin/ fcgi-bin/ icons/ libexec/ man/ proxy/ bikle9-i9-% cd logs /u01/app/oracle/product/9201/Apache/Apache/logs bikle9-i9-% ls -latr total 14 drwxr-xr-x 13 oracle dba 512 Mar 4 12:22 ../ -rw-r--r-- 1 oracle dba 2367 Mar 7 17:27 access_log -rw-r--r-- 1 oracle dba 1226 Mar 7 18:16 error_log drwxr-xr-x 2 oracle dba 512 Mar 7 18:16 ./ bikle9-i9-% chmod 775 . bikle9-i9-% chmod 664 *log bikle9-i9-% sudo chown nobody . access_log error_log Password: bikle9-i9-% ls -latr total 14 drwxr-xr-x 13 oracle dba 512 Mar 4 12:22 ../ -rw-rw-r-- 1 nobody dba 2367 Mar 7 17:27 access_log -rw-rw-r-- 1 nobody dba 1226 Mar 7 18:16 error_log drwxrwxr-x 2 nobody dba 512 Mar 7 18:16 ./ bikle9-i9-% touch x bikle9-i9-% ls -latr total 14 drwxr-xr-x 13 oracle dba 512 Mar 4 12:22 ../ -rw-rw-r-- 1 nobody dba 2367 Mar 7 17:27 access_log -rw-rw-r-- 1 nobody dba 1226 Mar 7 18:16 error_log -rw-r--r-- 1 oracle dba 0 Mar 7 18:19 x drwxrwxr-x 2 nobody dba 512 Mar 7 18:19 ./ bikle9-i9-% rm x bikle9-i9-% date>>error_log bikle9-i9-% date>>access_log bikle9-i9-% ls -latr total 14 drwxr-xr-x 13 oracle dba 512 Mar 4 12:22 ../ drwxrwxr-x 2 nobody dba 512 Mar 7 18:19 ./ -rw-rw-r-- 1 nobody dba 1255 Mar 7 18:19 error_log -rw-rw-r-- 1 nobody dba 2396 Mar 7 18:19 access_log bikle9-i9-% id uid=5502(oracle) gid=5501(dba) bikle9-i9-% Next, I edited the file below with the goal of having Apache run as user 'nobody': $ORACLE_HOME/Apache/Apache/conf/httpd.conf I found the text I was looking for about 1/4 of the way into the file and I made it look like this: ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo # # If you wish httpd to run as a different user or group, you must run # httpd as root initially and it will switch. # # User/Group: The name (or #number) of the user/group to run httpd as. # . On SCO (ODT 3) use "User nouser" and "Group nogroup". # . On HPUX you may not be able to use shared memory as nobody, and the # suggested workaround is to create a user www and use that user. # NOTE that some kernels refuse to setgid(Group) or semctl(IPC_SET) # when the value of (unsigned)Group is above 60000; # don't use Group nobody on these systems! # User nobody Group nobody ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo Then, I started Apache: bikle9-i9-% cd /u01/app/oracle/product/9201/Apache/Apache/bin /u01/app/oracle/product/9201/Apache/Apache/bin bikle9-i9-% sudo ./apachectl start Password: ./apachectl start: httpd started bikle9-i9-% d Fri Mar 7 18:26:25 PST 2003 bikle9-i9-% psorg 18: root 10368 1 2 18:26:21 ? 0:01 /u01/app/oracle/product/9201/Apache/Apache/bin/httpd -d /u01/app/oracle/product root 10440 13544 0 18:26:29 pts/10 0:00 ps -ef nobody 10369 10368 0 18:26:21 ? 0:00 /u01/app/oracle/product/9201/Apache/Apache/bin/httpd -d /u01/app/oracle/product nobody 10370 10368 0 18:26:21 ? 0:00 /u01/app/oracle/product/9201/Apache/Apache/bin/httpd -d /u01/app/oracle/product nobody 10371 10370 0 18:26:21 ? 0:01 /u01/app/oracle/product/9201/jdk/bin/../bin/sparc/native_threads/java -DORACLE_ nobody 10372 10370 0 18:26:21 ? 0:01 /u01/app/oracle/product/9201/jdk/bin/../bin/sparc/native_threads/java org.apach nobody 10373 10369 0 18:26:21 ? 0:00 /u01/app/oracle/product/9201/bin/isqlplus nobody 10374 10369 0 18:26:21 ? 0:00 /u01/app/oracle/product/9201/Apache/Apache/fcgi-bin/echo bikle9-i9-% bikle9-i9-% I checked the logs: bikle9-i9-% cd /u01/home/oracle/9oh/Apache/Apache/logs /u01/app/oracle/product/9201/Apache/Apache/logs bikle9-i9-% ls -latr total 1644 drwxr-xr-x 13 oracle dba 512 Mar 4 12:22 ../ -rw-rw-r-- 1 nobody dba 2396 Mar 7 18:19 access_log -rw------- 1 nobody other 0 Mar 7 18:26 mm.10367.sem -rw------- 1 nobody other 1056768 Mar 7 18:26 mm.10367.mem -rw------- 1 root other 0 Mar 7 18:26 opm_sys_shm.10368.sem -rw------- 1 root other 2113536 Mar 7 18:26 opm_sys_shm.10368.mem -rwxr-xr-x 1 root other 0 Mar 7 18:26 opm_sys_mtx.10368* -rw-r--r-- 1 root other 6 Mar 7 18:26 httpd.pid drwxrwxr-x 2 nobody dba 512 Mar 7 18:26 ./ -rw-rw-r-- 1 nobody dba 747351 Mar 7 18:29 error_log bikle9-i9-% ls -latr total 1660 drwxr-xr-x 13 oracle dba 512 Mar 4 12:22 ../ -rw-rw-r-- 1 nobody dba 2396 Mar 7 18:19 access_log -rw------- 1 nobody other 0 Mar 7 18:26 mm.10367.sem -rw------- 1 nobody other 1056768 Mar 7 18:26 mm.10367.mem -rw------- 1 root other 0 Mar 7 18:26 opm_sys_shm.10368.sem -rw------- 1 root other 2113536 Mar 7 18:26 opm_sys_shm.10368.mem -rwxr-xr-x 1 root other 0 Mar 7 18:26 opm_sys_mtx.10368* -rw-r--r-- 1 root other 6 Mar 7 18:26 httpd.pid drwxrwxr-x 2 nobody dba 512 Mar 7 18:26 ./ -rw-rw-r-- 1 nobody dba 754143 Mar 7 18:29 error_log bikle9-i9-% tail error_log ORACLE_HOME is [/u01/app/oracle/product/9201] LD_LIBRARY_PATH is [/u01/app/oracle/product/9201/lib] WV_GATEWAY_CFG is [/u01/app/oracle/product/9201/Apache/modplsql/cfg/wdbsvr.app] NLS_LANG is [AMERICAN_AMERICA.WE8ISO8859P1] [Fri Mar 7 18:29:50 2003] [crit] (13)Permission denied: Env var WV_GATEWAY_CFG does not point to a readable wdbsvr.app [Fri Mar 7 18:29:50 2003] [error] (13)Permission denied: Please check your enviroment. ORACLE_HOME is [/u01/app/oracle/product/9201] LD_LIBRARY_PATH is [/u01/app/oracle/product/9201/lib] WV_GATEWAY_CFG is [/u01/app/oracle/product/9201/Apache/modplsql/cfg/wdbsvr.app] NLS_LANG is [AMERICAN_AMERICA.WE8ISO8859P1] bikle9-i9-% It appears that user nobody is not such a great choice for running Apache on this host. I shutdown Apache to prevent the error_log from consuming too much disk space. bikle9-i9-% sudo ./apachectl stop Password: ./apachectl stop: httpd stopped bikle9-i9-% bikle9-i9-% bikle9-i9-% d Fri Mar 7 18:32:29 PST 2003 bikle9-i9-% psorg 18: root 13325 13544 0 18:32:32 pts/10 0:00 ps -ef oracle 13326 13544 0 18:32:32 pts/10 0:00 sort oracle 13327 13544 0 18:32:32 pts/10 0:00 grep -i 18: bikle9-i9-% cd ../logs /u01/app/oracle/product/9201/Apache/Apache/logs bikle9-i9-% ltr total 2394 drwxr-xr-x 13 oracle dba 512 Mar 4 12:22 .. -rw-rw-r-- 1 nobody dba 2396 Mar 7 18:19 access_log -rw-rw-r-- 1 nobody dba 1207377 Mar 7 18:32 error_log drwxrwxr-x 2 nobody dba 512 Mar 7 18:32 . bikle9-i9-% ltr total 2394 drwxr-xr-x 13 oracle dba 512 Mar 4 12:22 .. -rw-rw-r-- 1 nobody dba 2396 Mar 7 18:19 access_log -rw-rw-r-- 1 nobody dba 1207377 Mar 7 18:32 error_log drwxrwxr-x 2 nobody dba 512 Mar 7 18:32 . bikle9-i9-% I created a user named 'apache' and a group named 'apache': bikle9# mkdir /u01/home/apache bikle9# groupadd -g 1969 apache bikle9# useradd -u 1969 -g apache -d /u01/home/apache -s /bin/sh -c Apache apache bikle9# chown apache /u01/home/apache Next, I created a simple .profile for apache: bikle9# echo . /u01/home/oracle/.i9 > /u01/home/apache/.profile bikle9# chown apache /u01/home/apache/.profile Then, I edited $ORACLE_HOME/Apache/Apache/conf/httpd.conf I found the text I was looking for about 1/4 of the way into the file and I made it look like this: ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo # # If you wish httpd to run as a different user or group, you must run # httpd as root initially and it will switch. # # User/Group: The name (or #number) of the user/group to run httpd as. # . On SCO (ODT 3) use "User nouser" and "Group nogroup". # . On HPUX you may not be able to use shared memory as nobody, and the # suggested workaround is to create a user www and use that user. # NOTE that some kernels refuse to setgid(Group) or semctl(IPC_SET) # when the value of (unsigned)Group is above 60000; # don't use Group nobody on these systems! # User apache Group apache ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo Next, I made sure that Apache could log: bikle9# cd /u01/app/oracle/product/9201/Apache/Apache/logs bikle9# ls -la total 2394 drwxrwxr-x 2 nobody dba 512 Mar 7 18:32 . drwxr-xr-x 13 oracle dba 512 Mar 4 12:22 .. -rw-rw-r-- 1 nobody dba 2396 Mar 7 18:19 access_log -rw-rw-r-- 1 nobody dba 1207377 Mar 7 18:32 error_log bikle9# chown apache . access_log error_log bikle9# mv error_log error_log-2003-0307 bikle9# date>error_log bikle9# chown apache error_log bikle9# Then, I started Apache: bikle9# ./apachectl start ./apachectl start: httpd started bikle9# date Fri Mar 7 18:45:53 PST 2003 bikle9# ps -ef|grep 18:4 nobody 13603 13600 0 18:45:51 ? 0:00 /u01/app/oracle/product/9201/Apache/Apache/bin/httpd -d /u01/app/oracle/product root 13600 1 1 18:45:51 ? 0:01 /u01/app/oracle/product/9201/Apache/Apache/bin/httpd -d /u01/app/oracle/product root 13702 13399 0 18:46:06 pts/10 0:00 grep 18:4 nobody 13604 13600 0 18:45:51 ? 0:00 /u01/app/oracle/product/9201/Apache/Apache/bin/httpd -d /u01/app/oracle/product nobody 13610 13603 0 18:45:51 ? 0:00 /u01/app/oracle/product/9201/Apache/Apache/fcgi-bin/echo nobody 13606 13604 0 18:45:51 ? 0:01 /u01/app/oracle/product/9201/jdk/bin/../bin/sparc/native_threads/java -DORACLE_ nobody 13607 13604 0 18:45:51 ? 0:01 /u01/app/oracle/product/9201/jdk/bin/../bin/sparc/native_threads/java org.apach nobody 13605 13603 0 18:45:51 ? 0:00 /u01/app/oracle/product/9201/bin/isqlplus root 13701 13399 0 18:46:06 pts/10 0:00 ps -ef bikle9# Then, I checked the error log: bikle9# cd ../logs bikle9# ls -latr total 2764 drwxr-xr-x 13 oracle dba 512 Mar 4 12:22 .. -rw-rw-r-- 1 apache dba 2396 Mar 7 18:19 access_log -rw-rw-r-- 1 apache dba 1207377 Mar 7 18:32 error_log-2003-0307 -rw------- 1 nobody other 0 Mar 7 18:45 mm.13599.sem -rw------- 1 nobody other 1056768 Mar 7 18:45 mm.13599.mem -rw------- 1 root other 0 Mar 7 18:45 opm_sys_shm.13600.sem -rw------- 1 root other 2113536 Mar 7 18:45 opm_sys_shm.13600.mem -rwxr-xr-x 1 root other 0 Mar 7 18:45 opm_sys_mtx.13600 -rw-r--r-- 1 root other 6 Mar 7 18:45 httpd.pid drwxrwxr-x 2 apache dba 512 Mar 7 18:45 . -rw-r--r-- 1 apache other 104173 Mar 7 18:46 error_log bikle9# tail error_log ORACLE_HOME is [/u01/app/oracle/product/9201] LD_LIBRARY_PATH is [/u01/app/oracle/product/9201/lib] WV_GATEWAY_CFG is [/u01/app/oracle/product/9201/Apache/modplsql/cfg/wdbsvr.app] NLS_LANG is [AMERICAN_AMERICA.WE8ISO8859P1] [Fri Mar 7 18:46:49 2003] [crit] (13)Permission denied: Env var WV_GATEWAY_CFG does not point to a readable wdbsvr.app [Fri Mar 7 18:46:49 2003] [error] (13)Permission denied: Please check your enviroment. ORACLE_HOME is [/u01/app/oracle/product/9201] LD_LIBRARY_PATH is [/u01/app/oracle/product/9201/lib] WV_GATEWAY_CFG is [/u01/app/oracle/product/9201/Apache/modplsql/cfg/wdbsvr.app] NLS_LANG is [AMERICAN_AMERICA.WE8ISO8859P1] bikle9# I checked the permissions of /u01/app/oracle/product/9201/Apache/modplsql/cfg/wdbsvr.app: bikle9# ls -la /u01/app/oracle/product/9201/Apache/modplsql/cfg/wdbsvr.app -rw------- 1 oracle dba 1091 Aug 15 2000 /u01/app/oracle/product/9201/Apache/modplsql/cfg/wdbsvr.app bikle9# I made it readable by apache: bikle9# chmod 640 /u01/app/oracle/product/9201/Apache/modplsql/cfg/wdbsvr.app bikle9# chgrp apache /u01/app/oracle/product/9201/Apache/modplsql/cfg/wdbsvr.app bikle9# ls -la /u01/app/oracle/product/9201/Apache/modplsql/cfg/wdbsvr.app -rw-r----- 1 oracle apache 1091 Aug 15 2000 /u01/app/oracle/product/9201/Apache/modplsql/cfg/wdbsvr.app bikle9# I checked the error log: bikle9# pwd /u01/app/oracle/product/9201/Apache/Apache/logs bikle9# date Fri Mar 7 18:54:28 PST 2003 bikle9# ls -latr total 4924 drwxr-xr-x 13 oracle dba 512 Mar 4 12:22 .. -rw-rw-r-- 1 apache dba 1207377 Mar 7 18:32 error_log-2003-0307 -rw------- 1 apache other 0 Mar 7 18:50 mm.14913.sem -rw------- 1 apache other 1056768 Mar 7 18:50 mm.14913.mem -rw------- 1 root other 0 Mar 7 18:50 opm_sys_shm.14914.sem -rw------- 1 root other 2113536 Mar 7 18:50 opm_sys_shm.14914.mem -rwxr-xr-x 1 root other 0 Mar 7 18:50 opm_sys_mtx.14914 -rw-r--r-- 1 root other 6 Mar 7 18:50 httpd.pid drwxrwxr-x 2 apache dba 512 Mar 7 18:50 . -rw-r--r-- 1 apache other 1205319 Mar 7 18:53 error_log -rw-rw-r-- 1 apache dba 2690 Mar 7 18:53 access_log bikle9# !! ls -latr total 4924 drwxr-xr-x 13 oracle dba 512 Mar 4 12:22 .. -rw-rw-r-- 1 apache dba 1207377 Mar 7 18:32 error_log-2003-0307 -rw------- 1 apache other 0 Mar 7 18:50 mm.14913.sem -rw------- 1 apache other 1056768 Mar 7 18:50 mm.14913.mem -rw------- 1 root other 0 Mar 7 18:50 opm_sys_shm.14914.sem -rw------- 1 root other 2113536 Mar 7 18:50 opm_sys_shm.14914.mem -rwxr-xr-x 1 root other 0 Mar 7 18:50 opm_sys_mtx.14914 -rw-r--r-- 1 root other 6 Mar 7 18:50 httpd.pid drwxrwxr-x 2 apache dba 512 Mar 7 18:50 . -rw-r--r-- 1 apache other 1205319 Mar 7 18:53 error_log -rw-rw-r-- 1 apache dba 2690 Mar 7 18:53 access_log bikle9# bikle9# date Fri Mar 7 18:55:57 PST 2003 bikle9# ls -latr total 4924 drwxr-xr-x 13 oracle dba 512 Mar 4 12:22 .. -rw-rw-r-- 1 apache dba 1207377 Mar 7 18:32 error_log-2003-0307 -rw------- 1 apache other 0 Mar 7 18:50 mm.14913.sem -rw------- 1 apache other 1056768 Mar 7 18:50 mm.14913.mem -rw------- 1 root other 0 Mar 7 18:50 opm_sys_shm.14914.sem -rw------- 1 root other 2113536 Mar 7 18:50 opm_sys_shm.14914.mem -rwxr-xr-x 1 root other 0 Mar 7 18:50 opm_sys_mtx.14914 -rw-r--r-- 1 root other 6 Mar 7 18:50 httpd.pid drwxrwxr-x 2 apache dba 512 Mar 7 18:50 . -rw-r--r-- 1 apache other 1205319 Mar 7 18:53 error_log -rw-rw-r-- 1 apache dba 2690 Mar 7 18:53 access_log bikle9# So, my turning on read bits for apache on the file, /u01/app/oracle/product/9201/Apache/modplsql/cfg/wdbsvr.app seems to have solved that problem. Next, I connected to Apache with a web browser. It acted in a well behaved way. Next, I ran the utlrp.sql script. This script recompiles all PL/SQL modules that may be in an INVALID state. Notes I found in the script: Rem NOTES Rem * This script must be run using SQL*PLUS. Rem * You must be connected AS SYSDBA to run this script. Rem * This script expects the following packages to have been created with Rem VALID status: Rem STANDARD (standard.sql) Rem DBMS_STANDARD (dbmsstdx.sql) Rem * There should be no other DDL on the database while running the Rem script. Not following this recommendation may lead to deadlocks. Rem A screen dump: bikle9-i9-% cd 9oh/rdbms/admin /u01/app/oracle/product/9201/rdbms/admin bikle9-i9-% sqlplus '/as sysdba' SQL*Plus: Release 9.2.0.1.0 - Production on Fri Mar 7 18:59:30 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> @?/rdbms/admin/utlrp.sql PL/SQL procedure successfully completed. Table created. Table created. Table created. Index created. Table created. Table created. View created. View created. Package created. No errors. Package body created. No errors. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. SQL> SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production bikle9-i9-% Then, I set remote password file to none so that the only way to connect as sysdba was from a UNIX shell prompt. SQL> CONNECT bikle/x49erfan@I9 AS SYSDBA Connected. SQL> ALTER SYSTEM SET remote_login_passwordfile = 'NONE' SCOPE=spfile; System altered. SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down. SQL> CONNECT bikle/x49erfan@I9 AS SYSDBA ERROR: ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor Warning: You are no longer connected to ORACLE. SQL> CONNECT bikle/x49erfan AS SYSDBA Connected to an idle instance. SQL> STARTUP ORACLE instance started. Total System Global Area 269045940 bytes Fixed Size 454836 bytes Variable Size 134217728 bytes Database Buffers 134217728 bytes Redo Buffers 155648 bytes Database mounted. Database opened. SQL> CONNECT bikle/x49erfan@I9 AS SYSDBA ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> SQL> CONNECT bikle/x49erfan AS SYSDBA Connected. SQL> Next, I tested isqlplus using the url below: http://bikle9.db.bikle.com:7778/isqlplus It worked fine; I tried a variety of sql commands with no problems. I even used it to connect to an 8i database. I did find interesting links about a buffer overflow security hole in isqlplus: http://www.iss.net/security_center/static/10524.php http://www.nextgenss.com/advisories/ora-isqlplus.txt Then, I setup automated startup/shutdown. I prettied up /var/opt/oracle/oratab: DUMMY:/u01/app/oracle/product/817:N bikledev1:/u01/app/oracle/product/817:Y qbikle:/u01/app1/oracle/product/817:Y i9:/u01/app/oracle/product/9201:Y In /etc/init.d, I created a script named dbora which looks like this: oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo #!/bin/sh # Set ORA_HOME to be equivalent to the $ORACLE_HOME # from which you wish to execute dbstart and dbshut; # # Set ORA_OWNER to the user id of the owner of the # Oracle database in ORA_HOME. . /u01/home/oracle/.i9 ORA_HOME=$ORACLE_HOME ORA_OWNER=oracle if [! -f $ORA_HOME/bin/dbstart] then echo "Oracle startup: cannot start" exit fi case "$1" in 'start') # Start the Oracle databases: # The following command assumes that the oracle login # will not prompt the user for any values su - $ORA_OWNER -c $ORA_HOME/bin/dbstart & ;; 'stop') # Stop the Oracle databases: # The following command assumes that the oracle login # will not prompt the user for any values su - $ORA_OWNER -c $ORA_HOME/bin/dbshut & ;; esac oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo Then I established links to it and made it executable: bikle9# touch dbora bikle9# chown oracle dbora bikle9# bikle9# ln -s /etc/init.d/dbora /etc/rc0.d/K10dbora bikle9# ln -s /etc/init.d/dbora /etc/rc3.d/S99dbora bikle9# In the script $ORACLE_HOME/bin/dbshut, I replaced calls to the command: shutdown with this: shutdown IMMEDIATE # END