Tag Archives: SQL*Plus - Page 2

Backup Script for Oracle 10g

Posted by on December 8, 2009 No comments

Sample script to backup an Oracle 10g database on Linux.

#!/bin/sh
 # orahotbkup.sh
 #
 # Copies Oracle database data files to backup directory on disk
 # and individually gzips them
 #
# set up environment variables
 # need to export the Oracle environment variables
 export ORACLE_SID=orcl
 export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
 export PATH=$ORACLE_HOME/bin:$PATH
FMT_DATE=`/bin/date +%Y%m%d`
 BACKUP_BASE=/u01/app/oracle/oradata/backup/orcl
SCRIPT_LOC=/u01/app/oracle/admin/$ORACLE_SID/scripts/backup
 LOG_LOC=/u01/app/oracle/admin/$ORACLE_SID/scripts/log
 BACKUP_LOC=$BACKUP_BASE/$FMT_DATE
SCRIPT_FILE=$SCRIPT_LOC/dbf$FMT_DATE.sh
 LOG_FILE=$LOG_LOC/$FMT_DATE.log
# make destination directories
 mkdir -p $LOG_LOC
 mkdir -p $SCRIPT_LOC >> $LOG_FILE
 mkdir -p $BACKUP_LOC >> $LOG_FILE
# generate bash script file to copy database data files to destination
 sqlplus / as sysdba <
select '#!/bin/sh ' from dual;
 select 'sqlplus / as sysdba <<EOF'||chr(10)||
 'alter tablespace '||tablespace_name||
 ' begin backup; '||chr(10)||
 'exit'||chr(10)||
 'EOF'||chr(10)||chr(10)||
 'cp '||file_name||' $BACKUP_LOC/'||substr(file_name,instr(file_name,'/',-1)+1)
 ||chr(10)||chr(10)||
 'sqlplus / as sysdba <<EOF'||chr(10)||
 'alter tablespace '||tablespace_name||
 ' end backup; '||chr(10)||
 'exit'||chr(10)||
 'EOF'
 from dba_data_files
 order by file_id
spool $SCRIPT_FILE
/
spool off
 exit
 EOF
# change permission modes on script file to make it executable
 chmod 755 $SCRIPT_FILE
# execute the script file to copy the database data files
 $SCRIPT_FILE
# set name of second script file
 SCRIPT_FILE=$SCRIPT_LOC/arc$FMT_DATE.sh
# generate bash script file to copy archive logs to destination
 sqlplus / as sysdba <
alter system switch logfile;
 alter system switch logfile;
 alter system switch logfile;
alter database backup controlfile to '$BACKUP_LOC/control01.dbf';
select '#!/bin/sh ' from dual;
 select
 'cp '||name||' $BACKUP_LOC/'||substr(name,instr(name,'/',-1)+1)
 ||chr(10)||chr(10)
 from v$archived_log
 where completion_time > sysdate - 1.1
 order by completion_time
spool $SCRIPT_FILE
/
spool off
 exit
 EOF
# change permission modes on script file to make it executable
 chmod 755 $SCRIPT_FILE
# execute the script file to copy the archive log files
 $SCRIPT_FILE
# gzip each database file in the $BACKUP_LOC directory
 gzip $BACKUP_LOC/*
echo All done!

ORA-12162: TNS:net service name is incorrectly specified

Posted by on August 30, 2009 No comments

Problem: When attempting to log on to SQL*Plus as ”/ as sysdba’, the following error appears:

ERROR:
ORA-12162: TNS:net service name is incorrectly specified

Solution: Set ORACLE_SID=ORCL

export ORACLE_SID=ORCL

Database Media Recovery of 11i Instance

Posted by on May 19, 2009 No comments 
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:Documents and Settingsoracle>f:
F:>cd oracleproddb9.2.0
F:oracleproddb9.2.0>PROD_hostname.cmd
ECHO is off.
ECHO is off.
Tue 05/19/2009 09:49 AM
PROD_hostname.cmd exiting with status 0
F:oracleproddb9.2.0>sqlplus apps/apps
SQL*Plus: Release 9.2.0.7.0 - Production on Tue May 19 09:49:27 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Enter user-name: / as sysdba
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 831725752 bytes
Fixed Size 454840 bytes
Variable Size 411041792 bytes
Database Buffers 409600000 bytes
Redo Buffers 10629120 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:ORACLEPRODDATASYSTEM01.DBF'
SQL> recover automatic database
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select * from v$database;
NAME
---------
PROD
SQL>

Checking for Prerequisite Patches and Patch Levels

Posted by on March 16, 2009 No comments

In a patch’s README file, the prerequisite patches and patch levels that must be in place before that patch is applied are listed. Patches are indicated by a patch number (usually seven digits long), for example, 4712852. Patch levels  are usually of the form 11i.<PRODUCT CODE>.<LETTER>, for example, 11i.GL.H.

To determine if a particular prerequisite patch has already been applied to the system, query the AD_BUGS table.

Step 1:        Source the environment to load SQL*Plus. Either the applications tier environment or database tier environment can be sourced.

[applmgr@appslinux /]$ cd /u2/oracle/vis2appl/
[applmgr@appslinux vis2appl]$ . APPSORA.env

Step 2:        Load SQL*Plus:

[applmgr@appslinux vis2appl]$ sqlplus apps/apps

Step 3:        To determine whether a particular patch has been applied, query the BUG_NUMBER column of the AD_BUGS table for the patch number. For example, to determine if the patch 4712852 has been applied to the system, issue this SQL query:

SQL> select bug_number, creation_date from ad_bugs where bug_number = '4712852';
BUG_NUMBER                     CREATION_DATE
------------------------------ -------------
4712852                        16-MAR-07

If the query returns a row, the patch has been applied, whereas if the query does not return any rows, then the patch has not been applied.

Step 4:        To determine the patch level of a product, query the PATCH_LEVEL columns of the FND_PRODUCT_INSTALLATIONS table. For example, to determine the patch level for the GL product, issue this SQL query:

SQL> select patch_level, status from fnd_product_installations where patch_level like '%GL%';
PATCH_LEVEL                    S
------------------------------ -
11i.GL.H                       I

Unable to access Application Express

Posted by on March 10, 2009 No comments

In web browser, navigated to http://shrivastava.test.int:7777/pls/apex. Received message:

Forbidden
You don't have permission to access /pls/apex/apex on this server.
----------------------------------------------------------
Oracle-Application-Server-10g/10.1.2.0.0 Oracle-HTTP-Server Server at SHRIVASTAVA.TEST.INT Port 7777

Solution:

Password in the dads.conf (C:oracleproduct10.2.0http_1Apachemodplsql confdads.conf ) file is incorrect. Confirm by logging on to SQL*Plus as user APEX_PUBLIC_USER. Correct password, then bounce Apache web server.

Creating Cash API For AR

Posted by on November 13, 2007 No comments

Formatting Data File

There are eleven columns in the data file, in the following order:

  1. Receipt number
  2. Currency
  3. Customer number
  4. Payment method id
  5. Receipt Type
  6. Amount applied
  7. Apply date
  8. Apply GL date
  9. Comments
  10. Customer reference

The Excel worksheet must be formatted before exporting to Comma-Separated Values (CSV). There must be NO commas in any cell before exporting the data to CSV. The SQL*Plus script parses the data by commas, so any of the fields with commas will cause the program to fail

Remember to format the Amount Applied column without commas.
Preparing Data File
The file is usually saved with a .csv extension. It is renamed to remove spaces from the filename (8-character file name and 3-character extension). The data file can be named such that the data it contains can be identified from the name. For example, creatcs1.dat.

The first row of the data file contains the column headings of the Excel worksheet. Remember to delete the first row in the data file before uploading the file to the server.
Uploading Data File
Once prepared, the data file is uploaded to the server via FTP. From the Command Prompt, use ftp to log on to the server (as oraprod user) and change to the directory to upload the data file to:

Server Name Upload Directory
test /raid01/oraclone/custload
ora /raid01/oraprod/custload

After uploading the data file, log on to the server using Telnet or PuTTY. Navigate to the upload directory (cd /raid01/oraclone). Copy and paste the data file to ‘creatcsh.dat’. To obtain the number of rows in the file, execute the command:

$ cat creatcsh.dat | wc

Preparing SQL*Plus Environment
Load SQL*Plus on the client machine (not server) and log on to the instance as user oraprod. The scripts automatically sets the ORG ID and serveroutput settings in SQL*Plus, so it is not necessary to set it manually.

Since data from a text file is accessed from the SQL procedure, we must tell the instance the directory to search for the data file. To set this up, execute the ‘CREATE DIRECTORY’ SQL command and specify the directory on the server that the data file is located in:

SQL> CREATE DIRECTORY CLONEDATA3 as ‘/raid01/oraclone’;

To verify that the directory has been set up, query the DBA_DIRECTORIES view:

SQL> select * from DBA_DIRECTORIES;

Running the Create Cash API script
The SQL code for loading the data is in the creatcsh.sql file, located in C:SCRIPTS. The code can be run by either copying/pasting the SQL code, or by running the script:

SQL> @ C:Scriptscreatcsh.sql

Note that the changes are not committed, you must manually commit the changes once you are satisfied:

SQL> commit;

Running Customer Relationships (TCA) API

Posted by on October 12, 2007 No comments

Obtaining Data File

User passes the Microsoft Excel spreadsheet for the agent-customer relationships from analyst. Analyst extracts the customer number and agent number onto a new worksheet in Excel and saves the worksheet as type “Formatted Text (Space delimited)”. A sample of the data file is shown below:

01AAS00101PGCMM       999999
01ARE00101PGCMM       999999
01BLI00101PGCMM       999999
01BMA00101PGCMM       999999
01CPA00101PGCMM       999999
01CTR00101PGCMM       999999
01DEQ00101PGCMM       999999

The customer number is usually about 15 characters wide, right padded with spaces to 22 characters. The agent number is 6 characters wide.

This data file is passed to the DBA for loading.

Preparing Data File
The exported file is saved with a .prn extension by default. Rename the file (in Command Prompt) to an 8.3 format (8-character file name and 3-character extension). The data file can be named such that the data it contains can be identified from the name. For example:

Type of Listing File name
Provisor Customer Relationships provcust.dat
Quantum Customer Relationships quancust.dat

The first row of the data file contains the column headings of the Excel worksheet. This header row must be deleted, so that there is only data in the file.

Uploading Data File
Once prepared, the data file is uploaded to the server via FTP. From the Command Prompt, use ftp to log on to the server (as oraprod user) and change to the directory to upload the data file to:

Server Name Upload Directory
testdata /raid01/oraclone/custload
oradata /raid01/oraprod/custload

After uploading the data file, log on to the server using Telnet or PuTTY. Navigate to the upload directory (cd /raid01/oraclone). Rename (copy and paste) the data file to ‘datafile.dat’. To obtain the number of rows in the file, execute the command (shown in bold):

$ cat datafile.dat | wc

This command returns three figures. The first figure is the number of lines in the file. Verify that the data file has the correct number of rows to upload.

Preparing SQL*Plus Environment
Load SQL*Plus on the client machine (not server) and log on to the instance as user oraprod. Set the ORG ID and serveroutput settings in SQL*Plus before running the SQL procedure:

SQL> exec dbms_application_info.set_client_info(’21’);

SQL> set serveroutput on

Recall that ‘21’ refers to the ID of the Trinidad Operating Unit. Since the customer data is tied to the ORG ID, you must ensure that the correct ORG ID is set before running the code.

Since data from the text file is being accessed from the SQL procedure, the instance must have access to the file system. We must tell the Oracle instance the directory to search for the data file. To set this up, execute the ‘CREATE DIRECTORY’ SQL command and specify the directory on the server that the data file is located in:

SQL> CREATE DIRECTORY CLONEDATA3 as ‘/raid01/oraclone’;

To verify that the directory has been set up, query the DBA_DIRECTORIES view:

SQL> select * from DBA_DIRECTORIES;

Note that you only need to run the ‘CREATE DIRECTORY’ command once.

Running the TCA API
The SQL code for loading the data is in the tcaapi.sql file, located in C:SCRIPTS. The code can be run by either copying/pasting the SQL code, or by running the script:

SQL> @ C:Scriptstcaapi.sql

Note that the changes are not committed, you must manually commit the changes once you are satisfied the script completed without errors:

SQL> commit;

Errors Encountered
Problem: The API code is run with several dozen records in the data file and the following error is thrown:

ERROR at line 1:

ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes

ORA-06512: at “SYS.DBMS_OUTPUT”, line 32

ORA-06512: at “SYS.DBMS_OUTPUT”, line 97

ORA-06512: at “SYS.DBMS_OUTPUT”, line 112

ORA-06512: at line 85

Solution: This error is caused by the put_line statements. To overcome this, disable printing output from the procedure (set the value of c_print_data to ‘N’), or remove all put_line statements. Alternatively, set serveroutput to a large buffer:

SQL> set serveroutput on size 1000000

Problem: The code runs and completes successfully, but does not read or populate any records.

Solution: The record header from Excel still exists in the data file. Remove the headers and rerun.

UTL_FILE Sample Code

Posted by on July 9, 2007 No comments

Here is some sample code using the UTL_FILE package:

DECLARE
  string1 VARCHAR2(32767);
  file1 UTL_FILE.FILE_TYPE;
BEGIN
--  file1 := UTL_FILE.FOPEN('TEMP_DIR','log_file_test','A'); -- open in append mode
  file1 := UTL_FILE.FOPEN('/usr/tmp','log_file_test','A'); -- open in append mode
  string1 := TO_CHAR(SYSDATE) || ' UTL_FILE test';
  UTL_FILE.PUT_LINE(file1, string1); -- write a string to the file
  UTL_FILE.FFLUSH(file1);
  UTL_FILE.FCLOSE_ALL; -- close all open files
END;
/

[amazon asin=0596514468&template=iframe image&chan=default]    [amazon asin=0137142838&template=iframe image&chan=default]    [amazon asin=0071614214&template=iframe image&chan=default]

Enabling Maintenance Mode in SQL

Posted by on September 10, 2006 No comments

Problem: Show how to enable or disable maintenance mode from SQL.

Solution: Run adsetmmd.sql

Step 1: Set up the environment by running APPSORA.env:

. ./$APPL_TOP/APPSORA.env

Step 2: Change directory to $AD_TOP/patch/115/sql/:

cd $AD_TOP/patch/115/sql/

Step 3: Run the SQL script:

sqlplus apps/apps @adsetmmd.sql ENABLE | DISABLE

Step 4: Bounce the Apache Web listener. (adapcctl.sh)

See Metalink Note: 291901.1 (Subject:  Maintenance Mode – A New Feature in 11.5.10)