Tag Archives: Oracle

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

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, 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

11g, hp-ux, itanium, linux, script, SQL, sqlplus, unix

Creating an external file from within the Oracle RDBMS.

Leave a comment

Oracle provides the package UTL_FILE to produce OS layer files from within the Oracle RDBMS. This article will show a basic procedure for the producing an OS layer file from within the Oracle RDBMS.

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.

Mylinux:/home/lcatt:>sqlplus ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 03 09: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. Ensure that the system initialization parameter for utl_file_dir contains the directory to which utl_file is going to write with the command show utl_file_dir.

SQL> show parameter utl_file_dir

NAME TYPE VALUE
———————————— ———– ——————————
utl_file_dir string

SQL>

5. If it does not contain the directory, as is the case in step 4, logon to your Oracle database as sysdba and add the directory structure and bounce the database, as seen below:

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

SQL>

6. Execute the newly create procedure with the SQL*PLUS command; execute test_file

SQL> execute test_file

PL/SQL procedure successfully completed.

SQL>

7. Exit out of SQL*PLUS and change directory to where you wrote your file.

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:>

8. Execute the ls and more command on the file testfile.

Mylinux:/home/lcatt:>ls testfile
testfile
Mylinux:/home/lcatt:>more testfile
Testing file creation
Mylinux:/home/lcatt:>

This completes the creation of external OS layer file from within the Oracle RDBMS.

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

Oracle, RMAN

Oracle 10g RMAN database Setup

Leave a comment

Oracle Recovery Manager is an excellent tool for the backup and recovery of Oracle databases. It allows you to easily backup your information on scheduled bases to ensure against all forms of data loss. Additionally, it makes the process of recovering from data corruption to disk loss in a very expedient manor. The following instruction set covers the setup of an oracle database for storage of your recovery catalog and the cataloging of your first target database. This instruction set was written from a Linux installed Oracle system; however it will work on most any flavor of UNIX.

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

2. Use the ./dbca command as follows to open up the Oracle Database Creation Assistant or create a database named RMAN manually. Note: This oracle database is only used to store the catalog information of backed-up databases, thus the storage and memory requirements are very low. I would not allocate more than 200MB of space and 100MB of memory to this database.

linux1# cd $ORACLE_HOME/bin
linux1#
linux1# ./dbca

3. If using dbca, follow the prompted screens to create a general purpose database.

4. If the database is created using manual methods ensure that the following Oracle scripts are executed after build.

connect sys/oracle@RMAN as sysdba
set echo off
@?/rdbms/admin/catalog
@?/rdbms/admin/catproc
@?/rdbms/admin/catrep
@?/rdbms/admin/catexp
@?/rdbms/admin/catldr
@?/rdbms/admin/dbmsutil
@?/rdbms/admin/prvtutil.plb
@?/rdbms/admin/dbmssql
@?/rdbms/admin/prvtsql.plb
@?/rdbms/admin/dbmsdefr
@?/rdbms/admin/prvtdefr.plb
@?/rdbms/admin/catqueue
@?/rdbms/admin/dbmsaqad
@?/rdbms/admin/prvtaqad.plb
@?/rdbms/admin/catcr
@?/rdbms/admin/prvtcr.plb
connect system/oracle@RMAN
@?/rdbms/admin/catdbsyn
@?/sqlplus/admin/pupbld.sql
conn sys/oracle@RMAN as sysdba
@?/rdbms/admin/utlrp

5. Logon to your newly created database and create a tablespace for the RMAN user with the following command.

create tablespace rman
datafile ‘/u05/oradata/RMAN/rman01.dbf’ size 200m
extent management local autoallocate
segment space management auto;

6. Logon to your newly created database and create the RMAN User with the following command. Then grant the RMAN user the privileges: recovery_catalog_owner, connect, and resource.

create user rman identified by rman
temporary tablespace temp
default tablespace rman quota unlimited on rman;

grant recovery_catalog_owner to rman;

grant connect,resource to rman;

7. Add the following text to the listener.ora file on the server hosting the RMAN database and ensure that an entry is listed on all database servers to be backed-up in their individual tnsnames.ora files for the newly created RMAN database.

(SID_DESC =
(ORACLE_HOME = /u01/opt/app/oracle/product/10.2.0/Db_1)
(SID_NAME = RMAN1)
)

8. Issue a stop and restart of the oracle listener for changes to take affect.

linux1# lsnrctl stop

linux1# lsnrctl start

9. Register the target database in the catalog.

10. Logon to your server and set the ORACLE_SID parameter to your target database.

linux1# echo $ORACLE_SID

linux1# ORACLE_SID=orcl
linux1# export ORACLE_SID
linux1# echo $ORACLE_SID
orcl
linux1#

11. Execute the command , to logon to the RMAN utility.

linux1# rman target / catalog rman/rman@RMAN

Recovery Manager: Release 10.2.0.2.0 – Production on Wed Sep 24 15:42:29 2008

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

connected to target database: ORCL (DBID=3051662701)
connected to recovery catalog database

RMAN>

12. Issue the command in the RMAN utility.

RMAN> create catalog

recovery catalog created

RMAN>

13. Issue the command in the RMAN utility.

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

14. NOTE: By default RMAN will attempt to register a TAPE drive in the system. If you do not have a tape drive available specify.

RMAN> CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ clear;

old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ MAXPIECESIZE 10 G;
old RMAN configuration parameters are successfully deleted
starting full resync of recovery catalog
full resync complete

This completes the creation of RMAN recovery catalog and registration of your target database.

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

Oracle

Configuration of Oracle Flashback

Leave a comment

The Flashback database allows you to maintain a backup version of changes in the database that enables you to recovery from user mistakes without the need for a full database recovery. In this section, we will describe the following:

1. Configuration of Flashback for existing database.
2. Configuration of Flashback to a new database.

Steps in implementing the Flashback for existing database

1. Defining the Initialization Parameters for Flashback area.

a. DB_RECOVERY_FILE_DEST_SIZE – defines the maximum size that the Flashback Area can grow to. NOTE: This parameter must be set before defining DB_RECOVERY_FILE_DEST.
b. DB_RECOVERY_FILE_DEST – this the directory storage area on disk that will hold all files related to backup and recovery, note this is for both flashback recovery and traditional RMAN recovery.

2. Specify initialization parameter by defining in PFILE or Altering the system: In this example we will alter the system.

SQL> alter system set db_recovery_file_dest_size=30G scope = both;
System altered.
SQL> alter system set db_recovery_file_dest=’/u03/oradata/orcl/flash_recovery_area’ scope = both;
System altered.
SQL>

3. Ensure that the parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST are not set in the database. With Flashback you can only specify an LOG_ARCHIVE_DEST_n, by default LOG_ARCHIVE_DEST_10 will be set to your flash_recovery area.

SQL> show parameter log_archive

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_config string
log_archive_dest string
log_archive_dest_1 string LOCATION=use_db_recovery_file_dest
log_archive_dest_10 string
log_archive_dest_2 string LOCATION=/u02/oradata/orcl/arc
hive
log_archive_dest_3 string LOCATION=/u05/oradata/orcl/arc
hive
log_archive_dest_4 string
log_archive_duplex_dest string

4. With the flash recovery area set we can startup our database in flashback mode. NOTE: The database must be mounted in exclusive mode, archiving enabled, and then flashback turned on before opening the database for use.

startup mount exclusive;
alter database archivelog;
alter database flashback on;
alter database open;

5. This completes the configuration of Flashback for existing database.

Configuration of Flashback on a new database

1. The following entries need to be placed in your pfile of new database.

DB_NAME=orcl
# set location for current datafiles:
DB_CREATE_FILE_DEST = ‘/u02/oradata/orcl’
# set location for control files and online redo logs:
DB_CREATE_ONLINE_LOG_DEST_1 = ‘/u03/oradata/orcl’
DB_CREATE_ONLINE_LOG_DEST_2 = ‘/u04/oradata/orcl’
# set flash recovery area location and size
DB_RECOVERY_FILE_DEST = ‘/u03/oradata/orcl/flash_recovery_area’
DB_RECOVERY_FILE_DEST_SIZE = 30G

2. Create your new database with DBCA or manually with scripts.

Larry Catt, OCP 9i, 10g
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

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

UNIX CRON – Shell script errors with [10]: sqlplus: not found

Leave a comment

In normal administration of an Oracle RDBMS on UNIX or LINUX operating systems, the DBA will be required to develop shell and SQL scripts to maintain his/her RDBMS. In fact, to be considered a mid to senior DBA you will need to be well versed in scripting at the OS layer. In this article we will review a common error which occurs in a shell script called by CRON due to a lack of properly set variables.

1. In a normal scenario, the DBA has developed a shell script which calls and executes a SQL script to perform a certain maintenance task. In testing, the DBA executes the script as the oracle software owner and the script performs perfectly. However, when placed in the OS CRON for execution, the following error occurs.

[10]: sqlplus: not found

2. The error occurs because the environmental variables are not being read. Thus, the OS does not know where to find the executable: sqlplus.

3. The solution is to update your shell script and hard code the directory structure for the command sqlplus. An example would be if sqlplus is located in the following path /u01/opt/app/oracle/bin then perform the following:

Replace:

sqlplus

With:

/u01/opt/app/oracle/bin/sqlplus

This completes resolving the error message [10]: sqlplus: not found in a shell script called through CRON.

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

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

Display the time at your SQL*PLUS Prompt

Leave a comment

Oracle SQL*PLUS provides the ability to change the default prompt in your session. This allows you to easily determine information about your current session without having to query the database. In this article we will review displaying the current system time at your SQL*PLUS prompt.

1. Connect to your database through SQL*PLUS.

SQL>connect ljcatt/xxxx@mydb
Connected.
SQL>

2. Execute the following SQL*PLUS command: set time on.

SQL>set time on
19:53:56 SQL>
19:53:58 SQL>

3. The time will now be displayed in your SQL*PLUS prompt.
4. To turn off the time execute the following SQL*PLUS command: set time off

19:53:58 SQL>set time off
SQL>
SQL>

5. This completes displaying the time at your Oracle SQL*PLUS prompt.

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