Category Archives: Oracle 10g

Oracle, Oracle 10g, oracle 9i

Junior DBA Interview Question

Leave a comment

With well over a decade of Oracle experience from version 6 to 11g, I have had my share of interviewing prospective DBA employees in a range from Junior to Senior DBAs. Needless to say in this field, technical competence is the key to any new hire and early on I made the blunder of not asking the write questions during the interview. I am sure every senior person or manager has made this mistake and had to deal with either training the person from scratch or letting them go. Either of these options is not desirable in our fast past business would. So, I decided to write a couple of articles which cover what I believe a Junior, Mid, and Senior Oracle DBA should know to fill their prospective positions.

In this article we will cover what a Junior DBA should know to be successful. If you have any comments or questions, please email me at larry.catt@relidb.com.

Questions:

1. Give the three modes of starting an oracle database and explain each?
2. Give the three modes of shutting down an Oracle database and explain each?
3. Who can start and stop an Oracle database
4. How do you create a user account in an Oracle database?
5. How do you delete a user account in an Oracle database?
6. Explain the difference between an Oracle database and Oracle instance?
7. Name three physical objects that can be seen from the OS layer of Database Server?
8. Name two Oracle OS layer processes?
9. What is the utility to write an Oracle database to an OS layer file?
10. What is the utility to read an Oracle database to an OS layer file?

Answers:

1. Give the three modes of starting an oracle database and explain each?

Startup nomount – The pfile or initfile is read and memory realm is started.

Startup mount –The controlfile is read and files verified.

Startup –The datafiles are opened in read/write mode and the database is open in read/write mode.

2. Give the three modes of shutting down an Oracle database and explain each?

Shutdown – All new connections are refused, current connections are maintained until the users log off, and once all user connections are completed the database shuts down.

Shutdown immediate – All new connections are refused, all current connection activities are rolled back and disconnected, the database shuts down.

Shutdown abort – All connections are disconnected without roll back and the database shuts down.

3. Who can start and stop an Oracle database?

– The SYS user or any user with sysdba privileges can shutdown an Oracle database.

4. How do you create a user account in an Oracle database?

– Create user ‘username’ identified by ‘password’;

5. How do you delete a user account and all objects owned by that user in an Oracle database?

– Drop user ‘username’ cascade;

6. Explain the difference between an Oracle database and Oracle instance?

-Oracle database includes all of the physical objects of an Oracle environment which reside at the OS layer.

-Oracle Instance is the memory realm and OS processes of the Oracle environment.

7. Name three physical objects that can be seen from the OS layer of Database Server?

-controlfiles
-datafiles
-tempfiles
-redo logs
-archive redologs

8. Name two Oracle OS layer processes?

-PMON
-SMON
-ARCN
-MMON
-MMNL
-MMAN
-LGWR
-CKPT

9. What is the utility to write an Oracle database to an OS layer file?

-EXP or export in any version of Oracle.
-EXPDP or export data pump in Oracle 10g or higher

10. What is the utility to read an Oracle database to an OS layer file?

-IMP or import in any version of Oracle.
-IMPDP or import data pump in Oracle 10g or higher

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, script, security, SQL, sqlplus, unix

Oracle – Use of UTL_FILE to read external OS file into Oracle RDBMS.

Leave a comment

Oracle – Use of UTL_FILE to read external OS file into Oracle RDBMS.

Oracle provides the procedure UTL_FILE to allow for the reading of external OS layer files from within the Oracle RDBMS. This article will give an example of how to read the contents of an external OS file and display it in the Oracle database.

1. Logon to your Oracle database server as the Oracle software owner.

2. Create a file called read.txt with the following text.

This is line one.

3. Logon to SQL*PLUS with an account which has DBA privileges. In this example we are using the account LJCATT.

mylinux:> sqlplus ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Jan 29 23:28:23 2009

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

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4. Create the procedure read_file as detailed below.

create or replace procedure read_file
is

v_record varchar2(50);
v_file varchar2(30) := ‘read.txt’;
v_dir varchar2(512) := ‘/home/lufsora’;
v_write utl_file.file_type;
begin

v_write:=utl_file.fopen(v_dir, v_file, ‘r’, 2000);

utl_file.get_line(v_write,v_record, v_record);

dbms_output.put_line(v_record);

utl_file.fclose(v_write);

end read_file;
/

5. Execute the procedure read_file with the command execute and the test within the OS file will be display.

SQL> execute read_file;
This is line one.

PL/SQL procedure successfully completed.

SQL>

This completes the use of UTL_FILE to read OS level files into your Oracle RDBMS.

Larry J. Catt, OCP9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i

Upgrade of Oracle 9i RDBMS to 10g RDBMS

Leave a comment

Oracle provides upgrade paths for most RDBMS versions, however they are not as simple as some would like. I have ran into several Junior to Mid-level DBA’s who appear to have a difficult time grasping the general steps required to perform this task or just want a Senior person present for them to perform sanity checks with. Thus, I have decided to outline the general steps required to perform the upgrade from 9i to 10g.

1. Downloading the following software from www.oracle.com if you do not have the media available:

• Oracle 10.2.0.1 (this is the base release of Oracle 10g R2)
• Oracle 10.2.0.1 Companion CD (if needed)
• Oracle 10.2.0.4 (This is patch set 6810189)
• Latest Oracle CPU Patch

2. Shutdown your current Oracle 9i database system and listener. Do not delete any of the 9i binaries or database objects.

$ sqlplus ” / as sysdba”
SQL> shutdown immediate
SQL> exit
$
$ cd network/admin
$ lsnrctl stop LISTENER

3. Unpack or Unzip (dependent on OS) your 10.2.0.1 database installation. Execute the runInstaller. Note: It is most likely that your OS will be of a higher version, than is supported by this installer. You may have to use the tags “–jreLoc –ignoreSysPrereqs” to bypass the OS support version. Example:

./runInstaller -jreLoc /opt/java1.4/jre -ignoreSysPrereqs

4. At the Welcome to the Oracle Database 10g Installation page, check the Advanced Installation option and press Next.

5. At the Select Installation Type page, select Enterprise Addition and press Next.

6. At the Specify Home Details page, enter OraDb10g for Name and /app/oracle/10.2.0 for the path and press Next.

7. At the Product-Specific Prerequisite Checks page the Installer will verify the system is ready for installation.

8. At the Product-Specific Prerequisite Checks, review any warning received and press Next.

9. At the Upgrade an Existing Database page, select No and press next.

10. At the Select Configuration Options page, select Install Database Software Only and press Next.

11. At the Summary page, press Install.

12. Once the installer request for you to execute shell script root.sh, contact the SA support for your system and have them execute, then click OK.

13. Record the following output in a safe place and click Exit.

The following J2EE Applications have been deployed and are accessible at the
URLs listed below.
iSQL*Plus URL:
http://linux1:5560/isqlplus
iSQL*Plus DBA URL:
http://linux1:5560/isqlplus/dba

14. This completes Installation of 10.2.0.1 software.

15. Install Oracle companion component. (if desired)

16. Upgrade to 10.2.0.4, unzip the Oracle patch set 6810189 and execute the runInstaller utility. NOTE: You may again have to user the –jreLoc and –ignoreSysPrereqs tags. Example

./runInstaller -jreLoc /opt/java1.4/jre -ignoreSysPrereqs

17. At the Welcome page, select Next.

18. At the Specify Home Details page, enter OraDb10g in Name and /app/oracle/10.2.0 in Path and press Next

19. At the Product-Specific Prerequisite Checks page, press Next.

20. At the Oracle Configuration Manager Registration page, press Next.

21. At the Summary page, press Install.

22. Once requested to execute the script: app/oracle/10.2.0/root.sh, contact the SA and have them execute as root, then press OK.

23. At the End of Installation page, record the below information and press Exit.

The iSQL*Plus URL is:
http://linux1:5560/isqlplus

The iSQL*Plus DBA URL is:
http://linux1:5560/isqlplus/dba

24. At the screen ‘Do you really want to exit?’, press Yes.

25. This completes the upgrade to 10.2.0.4

26. Apply latest CPU patch. Logon to Metalink at metalink.oracle.com and download the latest CPU patch for Oracle 10.2.0.4. In this example, we will download CPU Jan 2009 – patch number 7592346

27. Unzip the patch file.

28. Ensure that the ORACLE_HOME parameter is point to 10.2.0.4 install by setting the parameter again.

Linux1:/oracle_software/7592346:>echo $ORACLE_HOME
/app/oracle/10.2.0
Linux1:/oracle_software/7592346:>

29. Edit your .profile file to ensure that OPATCH parameter is pointing at the correct OPatch directory and your PATH parameter is specifying the correct $ORACLE_HOME/bin directory. NOTE: The OPatch utility installed with 10.2.0.1 maybe too old for your CPU patch, see the README in your CPU to determine the correct OPatch version to use. You can navigate to $ORACLE_HOME/OPatch and execute the command “opatch version” to determine you current version.

30. Note: you may have to user the -jre tag to make the patch work.

opatch napply -skip_subset -skip_duplicate -jre /opt/java1.4/jre

or
$ORACLE_HOME/OPatch/opatch napply -skip_subset -skip_duplicate -jre
/opt/java1.4/jre

31. This completes installation of the latest CPU patch.

Post installation Steps

32. Update the .profile file to point to the new Oracle installation and make sure the following environment variables point to the new ORACLE installation.

ORACLE_HOME
PATH
LD_LIBRARY_PATH
SH_LIB_PATH

33. Logout and login to reset your environment variables.

34. Copy the init.ora file from existing 9.2.0.8 $ORACLE_HOME/dbs to 10g $ORACLE_HOME/dbs folder

Linux1:/app/oracle/9.2.0.8.0/dbs:>cp initorcl.ora ../../10.2.0/dbs/.
Linux1:/app/oracle/9.2.0.8.0/dbs:>

35. NOTE: See listing at bottom of this article for all non-supported 9i init parameters which must be removed or commented out.

36. Increase large pool size=200M in init.ora file.

37. Change folders to $ORACLE_HOME/rdbms/admin/. This is the folder where the SQL files for upgrade are stored.

$ cd $ORACLE_HOME/rdbms/admin

38. Start the database in upgrade mode

$>sqlplus / as sysdba
SQL>startup upgrade

39. Create sysaux tablespace

Ex:CREATE TABLESPACE sysaux DATAFILE
‘/orcl/oradata/orcl/data1/sysaux02.dbf’ SIZE 4096M REUSE EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE;

40. Issue unlimited quota for system

ALTER USER SYSTEM QUOTA UNLIMITED ON system;

41. Start the upgrade by running catupgrd.sql

SQL> spool /scripts/upgrade.log
SQL> @catupgrd.sql
Turn off the spooling of script results to the log file:
SQL> SPOOL OFF

Check the spool file and verify that the packages and procedures compiled successfully. You named the spool file earlier in this step; the suggested name was upgrade.log. Correct any problems you find in this file and rerun the appropriate upgrade script if necessary. You can rerun any of the scripts described in this note as many times as necessary.

42. Run utlu102s.sql, specifying the TEXT option:

SQL> @utlu102s.sql TEXT

43. Insure that all temp files from the production system are attached to temporary tablespace in converted database. If not recreate temp files.

Example:
alter tablespace TEMP add tempfile ‘/orcl/oradata/orcl/data1/temp_01.dbf’
size 2048m;

44. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql

Verify that all expected packages and classes are valid:

45. If there are still objects which are not valid after running the script run
the following:

spool invalid_post.lst
Select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status
from dba_objects where status <>‘VALID’;
spool off

46. Copy old listeners.ora and tnsnames.ora from the previous 9.2.0.8 oracle installation and update their ORACLE_HOME references.
• Copy files to new ORACLE_HOME/network/admin folder
cp listener.ora $ORACLE_HOME/network/admin
cp tnsnames.ora $ORACLE_HOME/network/admin
• Change folders to $ORACLE_HOME/network/admin
cd $ORACLE_HOME/network/admin
• Update the newly copied files to point to the new ORACLE_HOME location

47. Restart the database in a normal mode with “startup open”

48. It is a good idea to know rebuild all of your indexes and gather statistics.

49. NOTE: You will not get all of the benefits of Oracle 10g until you set the COMPATIBILITY parameter to a minimum of 10.1

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Appendix A: Initialization Parameters Obsolete in 10g
—————————————————–

ENQUEUE_RESOURCES
DBLINK_ENCRYPT_LOGIN
HASH_JOIN_ENABLED
LOG_PARALLELISM
MAX_ROLLBACK_SEGMENTS
MTS_CIRCUITS
MTS_DISPATCHERS
MTS_LISTENER_ADDRESS
MTS_MAX_DISPATCHERS
MTS_MAX_SERVERS
MTS_MULTIPLE_LISTENERS
MTS_SERVERS
MTS_SERVICE
MTS_SESSIONS
OPTIMIZER_MAX_PERMUTATIONS
ORACLE_TRACE_COLLECTION_NAME
ORACLE_TRACE_COLLECTION_PATH
ORACLE_TRACE_COLLECTION_SIZE
ORACLE_TRACE_ENABLE
ORACLE_TRACE_FACILITY_NAME
ORACLE_TRACE_FACILITY_PATH
PARTITION_VIEW_ENABLED
PLSQL_NATIVE_C_COMPILER
PLSQL_NATIVE_LINKER
PLSQL_NATIVE_MAKE_FILE_NAME
PLSQL_NATIVE_MAKE_UTILITY
ROW_LOCKING
SERIALIZABLE
TRANSACTION_AUDITING
UNDO_SUPPRESS_ERRORS

Appendix B: Initialization Parameters Deprecated in 10g
——————————————————-

LOGMNR_MAX_PERSISTENT_SESSIONS
MAX_COMMIT_PROPAGATION_DELAY
REMOTE_ARCHIVE_ENABLE
SERIAL_REUSE
SQL_TRACE
BUFFER_POOL_KEEP (replaced by DB_KEEP_CACHE_SIZE)
BUFFER_POOL_RECYCLE (replaced by DB_RECYCLE_CACHE_SIZE)
GLOBAL_CONTEXT_POOL_SIZE
LOCK_NAME_SPACE
LOG_ARCHIVE_START
MAX_ENABLED_ROLES
PARALLEL_AUTOMATIC_TUNING
PLSQL_COMPILER_FLAGS (replaced by PLSQL_CODE_TYPE and PLSQL_DEBUG)

Larry Catt
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i, privileges, script, SQL, sqlplus

Oracle – ORA-00604: error occurred at recursive SQL level 1 and revoking a user privileges.

Leave a comment

A schema is considered all of the objects owned by a single user within the Oracle database. When a user has a privilege revoked in the database, but owns a particular object which requires that privilege, errors will result. This article will recover an error which will result from a user having a privilege revoked which is require by one of the objects he owns.

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 17 11:00:09 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Create the user LJCATT and grant him the DBA role and create procedure privileges.

SQL> create user ljcatt identified by password10;

User created.

SQL> grant dba to ljcatt;

Grant succeeded.

SQL> grant execute on utl_file to ljcatt;

Grant succeeded.

4. Connect as the user ljcatt and create a procedure named test_file.

SQL> connect ljcatt
Enter password:
Connected.

SQL> create or replace procedure test_file
is
2 3 v_record varchar2(50) := ‘Testing file creation’;
4 v_file varchar2(30) := ‘testfile’;
5 v_dir varchar2(100) := ‘/home/lcatt’;
6 v_write utl_file.file_type;
7 begin
8
9 v_write := utl_file.fopen(v_dir, v_file, ‘w’, 4000);

10 11 –utl_file.fopen(v_dir, v_file, ‘w’, 100);
12
13 utl_file.put_line(v_write, v_record);

14 15 utl_file.fclose(v_write);
16
17 end test_file;
/ 18

Procedure created.

5. Reconnect to the database with SYSDBA privileges and revoke the DBA from ljcatt and grant the create session privilege. Then attempt to reconnect as the user LJCATT.

SQL> connect / as sysdba
Connected.
SQL> revoke dba from ljcatt;

Revoke succeeded.

SQL> grant create session to ljcatt;

Grant succeeded.

SQL> connect ljcatt
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00900: invalid SQL statement
ORA-06512: at line 8

Warning: You are no longer connected to ORACLE.

6. NOTE: The error is received due to this user owning objects which he does not have privileges to create or access. This is shown below by reconnecting as SYSDBA and displaying objects owned by the user LJCATT.

SQL> connect / as sysdba
Connected.

SQL> select owner, object_name from dba_objects where object_name like ‘TEST_F%’
and owner=’LJCATT’

OWNER OBJECT_NAME
————————- ————————–
LJCATT TEST_FILE

7. As SYSDBA, re-grant the DBA role to LJCATT and attempt to reconnect.

SQL> grant dba to ljcatt;

Grant succeeded.

SQL> connect ljcatt
Enter password:
Connected.
SQL>

Now that the user LJCATT has the specific privileges required by the objects he owns, he is able to connect without error.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i, privileges

Oracle – User does not have permissions on Oracle package UTL_FILE.

Leave a comment

Oracle provides the package UTL_FILE to produce OS layer files from within the Oracle RDBMS. Due to this procedures ability to create OS layer files from within the database, its access must be controlled through system privileges. This article will show a common PLS-00201 error, which indicates a lack of access permissions and how to resolve it.

1. Logon to your Oracle database server as the Oracle software owner.

2. Create a file called create_file.sql with the following SQL.

create or replace procedure test_file
is

v_record varchar2(50) := ‘Testing file creation’;
v_file varchar2(30) := ‘test_file.txt’;
v_dir varchar2(512) := ‘/home/lcatt’;
v_write utl_file.file_type;
begin

v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);

utl_file.put_line(v_write, v_record);

utl_file.fclose(v_write);

end test_file;
/

3. Logon to SQL*PLUS as the SYSDBA and create the user account LJCATT with the privileges: connect, create session, and resource to LJCATT.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jan 13 22:26:07 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL> create user ljcatt identified by ljcatt;

User created.

SQL> grant connect to ljcatt;

Grant succeeded.

SQL> grant create session to ljcatt;

Grant succeeded.

SQL> grant resource to ljcatt;

Grant succeeded.

SQL>

4. Reconnect to SQL*PLUS as LJCATT and execute the file create_file.sql to create your procedure

Mylinux:/home/lcatt:>sqlplus ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jan 13 22:43:17 2009

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

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set feedback on
SQL> set echo on
SQL>@./create_file.sql

SQL> create or replace procedure test_file
is
2 3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7 v_write utl_file.file_type;
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
utl_file.fclose(v_write);
14 15
16 end test_file;
17 /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TEST_FILE:

LINE/COL ERROR
——– —————————————————————–
7/9 PL/SQL: Item ignored
7/9 PLS-00201: identifier ‘UTL_FILE’ must be declared
10/1 PL/SQL: Statement ignored
10/1 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

12/1 PL/SQL: Statement ignored
12/19 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

14/1 PL/SQL: Statement ignored

LINE/COL ERROR
——– —————————————————————–
14/17 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

SQL>

5. NOTE: The error received indicates that we do not have permissions to use the package UTL_FILE. Now connect to the database as sydba, execute the grant command: grant execute on utl_file to ljcatt;

SQL> connect / as sysdba
Connected.
SQL> grant execute on utl_file to ljcatt;

Grant succeeded.

SQL>

6. Connect back to the database as the user LJCATT, re-execute the SQL file create_file.sql, and execute the procedure test_file.

SQL> connect ljcatt
Enter password:
Connected.
SQL> create or replace procedure test_file
2 is
3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7 v_write utl_file.file_type;
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
14 utl_file.fclose(v_write);
15
end test_file;
16 17 /

Procedure created.

SQL> execute test_file

PL/SQL procedure successfully completed.

SQL>

The procedure now compiles without error and executes, due to the owner having proper privileges on the package UTL_FILE.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, script, SQL, sqlplus, unix

Oracle – ORA-29280: invalid directory path when using UTL_FILE

Leave a comment

Oracle provides the package UTL_FILE to produce OS layer files from within the Oracle RDBMS. This article will describe the common error of ORA-29280 which maybe received when using the UTL_FILE procedure and how to resolve it.

1. Logon to your Oracle database server as the Oracle software owner.

2. Create a file called create_file.sql with the following SQL.

create or replace procedure test_file
is

v_record varchar2(50) := ‘Testing file creation’;
v_file varchar2(30) := ‘test_file.txt’;
v_dir varchar2(512) := ‘/home/lcatt’;
v_write utl_file.file_type;
begin

v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);

utl_file.put_line(v_write, v_record);

utl_file.fclose(v_write);

end test_file;
/

3. Enter SQL*PLUS, set ECHO and FEEDBACK to on, and execute the file
create_file.sql as a user with DBA privileges. In this example we are using the account LJCATT.

Mylinux:/home/lcatt:>sqlplus ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 10 10:43:17 2009

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

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set feedback on
SQL> set echo on
SQL>@./create_file.sql
SQL> create or replace procedure test_file
2 is
3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7 v_write utl_file.file_type;
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
14 utl_file.fclose(v_write);
15
16 end test_file;
17 /

Procedure created.

SQL>

4. Use the SQL*PLUS command show to display the initialization parameter utl_file_dir and ensure that the directory /home/lcatt is not present.

SQL> show parameter utl_file_dir

NAME TYPE VALUE
———————————— ———– ——————————
utl_file_dir string /home/lcatt/output
SQL>

5. If the directory is present, remove it with the following command:

SQL> alter system set utl_file_dir=” scope=spfile;

System altered.

SQL>

6. Logon to the database as sysdba and restart your oracle instance.

SQL> connect / as sysdba
Connected.
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size 2068728 bytes
Variable Size 654315272 bytes
Database Buffers 503316480 bytes
Redo Buffers 48259072 bytes
Database mounted.
Database opened.
SQL>

7. Now logon as the user LJCATT and execute the package test_file with the command execute.

SQL> execute test_file
BEGIN test_file; END;

*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at “SYS.UTL_FILE”, line 29
ORA-06512: at “SYS.UTL_FILE”, line 448
ORA-06512: at “LJCATT.TEST_FILE”, line 10
ORA-06512: at line 1

SQL>

8. NOTE: The error produced is a result of no reference to the defined directory structure in the initialization parameter UTL_FILE_DIR. This parameter is used to allow the database access to OS layer directories. To resolve this error, connect to the database as sysdba and place the directory path in the initialization parameter UTL_FILE_DIR with the command alter system and restart the database.

SQL> connect / as sysdba
Connected.
SQL> alter system set utl_file_dir=’/home/lcatt’ scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size 2068728 bytes
Variable Size 654315272 bytes
Database Buffers 503316480 bytes
Redo Buffers 48259072 bytes
Database mounted.
Database opened.
SQL> show parameter utl_file_dir

NAME TYPE VALUE
———————————— ———– ——————————

utl_file_dir string /home/lcatt

9. Connect back to the database as the user LJCATT and re-execute the procedure test_file.

SQL> connect ljcatt
Enter password:
Connected.
SQL> execute test_file

PL/SQL procedure successfully completed.

SQL>

10. Exit out of SQL*PLUS, change directories to the location of your output file, and perform an ls command to display the file generated.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Mylinux:/home/lcatt:>cd /home/lcatt
Mylinux:/home/lcatt:>ls testfile
testfile
Mylinux:/home/lcatt:>

The file was produced under the directory defined by the initialization parameter utl_file_dir and the error ORA-29280 has been resolved.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g

Recreating Oracle 10g EM

Leave a comment

Oracle 10g Enterprise Manager has been a vast improvement over previous versions. However, I have experienced several situation where its behavior has become very unpredictable due to changes to DBSNMP or SYSMAN schemas and changes made to the your EM configuration files. In these situations, I have found it much easier to remove the EM components and re-install to resolve the problems. NOTE: This may not always be your best option; some objects created in EM may no longer exist after performing this procedure. However, EM will work without error itself.

1. Login to your Oracle server as the oracle software owner, set your ORACLE_SID and shutdown your Oracle EM console.

[root@linux1 ~]# su – oracle
[oracle@linux1 ~]$ ORACLE_SID=orcl
[oracle@linux1 ~]$ export ORACLE_SID
[oracle@linux1 ~]$ emctl stop dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://linux1:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control …
… Stopped.
[oracle@linux1 ~]$

2. Startup the Oracle universal installer, located under $ORACLE_HOME/oui/bin directory. The shell script to execute the installer is runInstaller.

3. At the welcome screen select deinstall Products.

4. At the Inventory screen, expand the tabs Oracle Home and Oracle Database to show all installed components. Ensure that only Oracle Enterprise Manager is selected and press the Remove button.

5. At the Confirmation screen, press the Yes button to continue.

6. The Oracle Universal Installer will remove all Oracle Enterprise Manager components.

7. After the uninstall is complete, the Oracle Universal Installer will return you to the Inventory screen, press the Close button to exit the installer.

8. Login to SQL*Plus with as sysdba and drop the user schemas dbsnmp and sysman with the cascade clause.

login as: oracle
oracle@linux1’s password:
Last login: Mon Jan 14 11:31:32 2008 from 192.168.1.101
[oracle@linux1 ~]$ ORACLE_SID=orcl
[oracle@linux1 ~]$ export ORACLE_SID
[oracle@linux1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Jan 14 13:47:52 2008

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

SQL> connect / as sysdba
SQL> drop user sysman cascade;
User dropped.
SQL> drop user dbsnmp cascade;
User dropped.
SQL>

9. Now execute the script catsnmp.sql located in $ORACLE_HOME/rdbms/admin to recreate the dbsnmp schema.

SQL>$ORACLE_HOME/rdbms/admin/catsnmp.sql

10. Startup the Oracle universal installer, located under $ORACLE_HOME/oui/bin directory. The shell script to execute the installer is runInstaller.
11. At the welcome screen select Next button.

12. At the Specify Source Location page, enter the directory where you unzipped your oracle database software and select Next.

13. At the Select Installation Type, select the Custom radio button and press Next.

14. At the Specify Home Details, enter the Home name and default path for installation and press Next.

15. You will receive a Warning message stating the directory is not empty. Select Yes to continue.

16. At the Available Products Components page, ensure that only Oracle Enterprise Manger is select and press Next button.

17. At the Product-Specific Prerequisite Checks page, select Next.

18. At the Summary page, select the Install button.

19. The Oracle Enterprise Manger will now install.

20. Once the End of Installation page is display, select the exit button.

21. Logon to your Oracle host machine console as the oracle software owner and set your ORACLE_SID variable.

login as: oracle
oracle@linux1’s password:
Last login: Mon Jan 14 11:31:32 2008 from 192.168.1.101
[oracle@linux1 ~]$ ORACLE_SID=orcl
[oracle@linux1 ~]$ export ORACLE_SID

22. Startup the em console and test your connection.

[oracle@linux1 ~]$ emctl start dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://linux1:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ……………….. started.
——————————————————————
Logs are generated in directory /u01/oracle/oracle/product/10.2.0/db_1/linux1_orcl/sysman/log
[oracle@linux1 ~]$

23. Test your connect to the dbconsole at reference address in the emctl start command: normally this would be http://<machine_name>:1158/em

 

 

Larry Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g

DBCA will not start with error NJNI10

Leave a comment

1. If you see the error below when trying to execute DBCA try the next three steps to resolve.

[oracle@mylinux1 bin]$ ./dbca

UnsaUntitled 1tisfiedLinkError exception loading native library: njni10
Exception in thread “main” java.lang.UnsatisfiedLinkError: get
at oracle.net.common.NetGetEnv.get(Native Method)
at oracle.net.config.Config.getNetDir(Unknown Source)
at oracle.net.config.Config.initConfig(Unknown Source)
at oracle.net.config.Config.(Unknown Source)
at oracle.sysman.assistants.util.NetworkUtils.(NetworkUtils.java:225)
at oracle.sysman.assistants.util.step.StepContext.(StepContext.java:264)
at oracle.sysman.assistants.dbca.backend.Host.(Host.java:686)
at oracle.sysman.assistants.dbca.ui.UIHost.(UIHost.java:205)
at oracle.sysman.assistants.dbca.ui.InteractiveHost.(InteractiveHost.java:54)
at oracle.sysman.assistants.dbca.Dbca.getHost(Dbca.java:160)
at oracle.sysman.assistants.dbca.Dbca.execute(Dbca.java:94)
at oracle.sysman.assistants.dbca.Dbca.main(Dbca.java:180)

[oracle@mylinux1 bin]$

2. Create the following soft links in $ORACLE_HOME/bin directory and retry ./dbca command.

[oracle@mylinux1 bin]$ ln -s $ORACLE_HOME/lib/libnjni10.dylib $ORACLE_HOME/lib/libnjni10
[oracle@mylinux1 bin]$ ln -s $ORACLE_HOME/lib/libclntsh.dylib $ORACLE_HOME/lib/libclntsh.dylib.10.1

3. If the previous step did not resolve the issue ensure that the variable LD_LIBRARY_PATH contains both $ORACLE_HOME/lib and $ORACLE_HOME/lib32. Re-execute your .bash file and retry ./dbca command.

[oracle@mylinux1 ~]$ vi .bash_profile
[oracle@mylinux1 ~]$ . ./.bash_profile

4. If the previous step did not resolve the issue, install or re-install the following 32 bit packages: glibc-devel-2.3.4-2.36.i386.rpm and glibc-2.3.4-2.36.i686.rpm. Then cd to $ORACLE_HOME/bin and execute ./relink all. Finally, retry the ./dbca command.

[root@mylinux1 RPMS]# rpm -Uvh –force glibc-devel-2.3.4-2.36.i386.rpm
Preparing… ########################################### [100%]
1:glibc-devel ########################################### [100%]
[root@mylinux1 RPMS]# rpm -Uvh –force glibc-2.3.4-2.36.i686.rpm
Preparing… ########################################### [100%]
1:glibc ########################################### [100%]
[root@mylinux1 RPMS]#
Larry J Catt, OCP
oracle@allcompute.com
www.allcompute.com

ASM, Oracle, Oracle 10g

ASM with RAW Disks will not start after reboot

Leave a comment

ASM (Automatic Storage Management) instances for the storage of database files have become a very popular option in the database world since the release of ORACLE 10g. However, there exist several administration concerns over ASM, which the DBA must take into consideration. This article covers the problem of ASM instance not starting after a reboot of a Linux server and applies to most Unix systems while using RAW Disks.

Error: ORA-15042: ASM disk is missing.

Reason: This error is caused by the RAW device being removed from the OS, corrupted, or/and most likely, not owned by the ORACLE user.

1. Verify that the ASM disk exists.

[root@mylinux1 /]# cd /dev/raw
[root@mylinux1 /dev/raw]# ls
/dev/raw/raw1
/dev/raw/raw2
/dev/raw/raw3
[root@mylinux1 u06]#

2. Check the ownership of the disks.

[root@mylinux1 /]# cd /dev/raw
[root@mylinux1 /dev/raw]# ls –la
-rw-r–r– 1 root root 2 Sep 2 08:33 raw1
-rw-r–r– 1 root root 2 Sep 2 08:33 raw2
-rw-r–r– 1 root root 2 Sep 2 08:34 raw3
[root@mylinux1 /dev/raw]#

NOTE: These raw devices are now owned by ROOT after the reboot, thus ORACLE does not have access.

3. On most UNIX systems you will see that the ownership of RAW devices is reset to ROOT after reboot. To correct this behavior, enter the following code into your init process before the Oracle database startup scripts are executed.

chown oracle:dba /dev/raw/raw1
chown oracle:dba /dev/raw/raw2
chown oracle:dba /dev/raw/raw3
Larry J Catt, OCP
oracle@allcompute.com
www.allcompute.com

ASM, Oracle, Oracle 10g

CRS setup for ORACLE 10g ASM instance

Leave a comment

In order for ASM and RAC to work properly the CRS (Cluster Ready Service) must first be configured. This procedure outlines the configuration of CRS in a Linux environment.

1. Logon to Linux server as the root user.

2. Verify that the correct support packages are installed.

[root@mylinux_1 RPMS]# ls compat-libstdc++*
compat-libstdc++-296-2.96-132.7.2.i386.rpm

3. NOTE: depending on your OS you may require a different version or bit setting of this package. I am running x86_64 system so I will install the newest compat-listdc++ package for this system.

[root@mylinux_1 RPMS]# rpm -i compat-libstdc++-33-3.2.3-47.3.x86_64.rpm
[root@mylinux_1 RPMS]#

4. As the ROOT user navigate to the directory $ORACLE_HOME/bin. Execute the command ./localconfig add. The localconfig command cannot be run as the ORACLE user.

[root@mylinux_1 bin]# ./localconfig add
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
Configuration for local CSS has been initialized

Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process…
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
mylinux_1
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)
[root@mylinux_1 bin]#

5. If CRS fails to install you must back out the files created with the command ./localconfig delete. Most likely the failure has occurred due to missing support packages. Verify that all packages suggested by Oracle Corporation for your OS and product version exist. You can find this listing at www.oracle.com/technology/index.html.

Larry Catt, OCP
oracle@allcompute.com
www.allcompute.com