Tag Archives: database

11g, Oracle, Oracle 10g, RAC, utility

Use of rconfig to modify a single instance 11g database to RAC

Leave a comment

This article covers the conversion of a pre-existing Oracle 11g single instance database into a two node RAC. The following assumptions are made for this procedure to execute correctly:
1. Oracle 11g GRID has been installed on both RAC nodes.
2. Oracle 11g RDBMS has been installed on both RAC nodes.
3. Oracle 11g single instance database has been copied to RAC node 1 and operates correctly as a single instance database.

1. Logon to node 1 of your Oracle RAC server as the RDBMS owner.

2. Copy the file $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC_AdminManaged.xml

[oracle@mylinux1 dbhome_1]$ cp $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC_AdminManaged.xml orcl.xml
[oracle@mylinux1 dbhome_1]$

2. The sample XML file gives detailed instruction on how to edit the file for your specific use. In this example edit the following lines:

SourceDBHome = Single instance Oracle Home directory
TargetDBHome = RAC instance Oracle Home, this could be the same as the single instance Oracle Home directory
SourceDBInfo SID= SID of the Source database.
Password = SYS password
Node name= node1 name, normally this is the machine name.
Node name= node2 name, normally this is the machine name.
InstancePrefix = prefix of your RAC database, normally this is equal to the SID of your source database.
SharedStorage type=”ASM” or “OCFS”

3. Ensure that the single instance database is up and running.

[oracle@mylinux1 dbhome_1]$ ps -ef|grep smon
oracle 18508 1 0 02:12 ? 00:00:00 ora_smon_orcl
oracle 26774 7777 0 02:46 pts/1 00:00:00 grep smon
[oracle@mylinux1 dbhome_1]$

4. Execute the rconfig command and pass your edited xml file to it.
[oracle@mylinux1 bin]$ ./rconfig ./orcl.xml
Converting Database “orcl” to Cluster Database. Target Oracle Home: /u01/app/oracle/product/11.2.0/dbhome_1. Database Role: PRIMARY.
Setting Data Files and Control Files
Adding Database Instances
Adding Redo Logs
Enabling threads for all Database Instances
Setting TEMP tablespace
Adding UNDO tablespaces
Adding Trace files
Setting Flash Recovery Area
Updating Oratab
Creating Password file(s)
Configuring Listeners
Configuring related CRS resources
Starting Cluster Database
< ?xml version=”1.0″ ?>

Operation Succeeded

/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@mylinux1 bin]$>

5. Now the database can be confirmed in rac mode on both nodes.

[oracle@mylinux1 bin]$ ps -ef|grep smon
oracle 16544 1 0 03:04 ? 00:00:00 ora_smon_orcl1
oracle 21072 7777 0 03:07 pts/1 00:00:00 grep smon
[oracle@mylinux1 bin]$

[root@mylinux2 ~]# ps -ef|grep smon
oracle 27298 1 0 03:03 ? 00:00:00 ora_smon_orcl2
root 29474 23387 0 03:07 pts/4 00:00:00 grep smon
[root@mylinux2 ~]#

This completes configuration of single instance to Oracle 11g RAC.

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

dbca, utility

Remove Oracle Database with DBCA from command line

Leave a comment

The following procedure shows how to remove an Oracle database with dbca utility from the command line.

1. Logon as the oracle software owner.

[root@mylinux2 root]# su – oracle
[oracle@mylinux2 ~]$

2. Change directories to $ORACLE_HOME/bin

[oracle@mylinux2 ~]$ cd $ORACLE_HOME/bin
[oracle@mylinux2 bin]$

3. Obtain the ORACLE_SID of the database you wish to delete.

[oracle@mylinux2 bin]$ ps -ef|grep smon
oracle 4746 1 0 Feb09 ? 00:00:00 ora_smon_orcl
oracle 7839 7796 0 00:39 pts/1 00:00:00 grep smon
[oracle@mylinux2 bin]$

4. Obtain the userid and password of a user with sysdba privileges, normally this would be sys.

5. Logon to the database if open with the immediate option and exit.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mylinux2 bin]$

6. Ensure that no oracle database processes are running for the ORACLE_SID you wish to delete


[oracle@mylinux2 bin]$ ps -ef|grep orcl
oracle 11485 7796 0 01:46 pts/1 00:00:00 grep orcl
[oracle@mylinux2 bin]$

7. Execute the command dbca -deleteDatabase -sourceDB -sysDBAUserName -sysDBAPassword


[oracle@mylinux2 bin]$ ./dbca -silent -deleteDatabase -sourceDB orcl – sysDBAUserName larry -sysDBAPassword larry
Connecting to database
4% complete
9% complete
14% complete
19% complete
23% complete
28% complete
47% complete
Updating network configuration files
48% complete
52% complete
Deleting instance and datafiles
76% complete
100% complete
Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/orcl.log” for
further details.
[oracle@mylinux2 bin]$

This completes deleting an oracle database from command line with dbca.

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

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

Oracle DBCA – Database Configuration assistant.

Leave a comment

Oracle provides several Configuration Assistant tools to facility administrative duties required by an Oracle DBA or Developer. The Oracle’s DBCA (Database Configuration Assistant) is the GUI tool used to create starter databases within the Oracle RDBMS. The DBCA utility was introduced in Oracle 8i and provides the user a GUI to eliminate the need for manual configuration of database creation scripts. In this article we will review the startup of the Database Configuration Assistant within a RHEL environment.

1. Logon to your Oracle Database server as the Oracle software owner.
2. Change directories to your $ORACLE_HOME/bin.

mylinux:> cd $ORACLE_HOME/bin
mylinux:>

3. Export your display to your local machine if you are not logon to the server itself.

mylinux:> export DISPLAY=192.168.0.110:0.0
mylinux:>

4. You will require a terminal emulation program executing on your local machine to produce the display from your database server. Any terminal display software will do, however I would suggest VNC which can be downloaded for free at the URL: www.realvnc.com

5. Execute the dbca shell script to startup the database configuration assistant.

mylinux:> ./dbca

6. The Welcome screen will display and the wizard from here on is pretty self explanatory. The DBCA utility provides you the ability to Create Databases, Configure Database Options, Delete Databases, Manage Database Templates, and Configure ASM instances for storage.

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

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

Oracle – Profile for single User owning multiple databases with separate ORACLE_HOME:

Leave a comment

In Oracle production database environments, it is desirable to use one Oracle installation for each database which resides on a server. The use of a separate Oracle installation for each database reduces the possibility of corruption of one ORACLE_HOME affecting all of the databases on a single server and allows for maintenance to be performed on one RDBMS without affecting the operations of the others. This is easily accommodated in a LINUX or UNIX environments through the proper setting of your .profile or .bash_profile file. In this article we will review the configuration of your profile to use multiple ORACLE_HOMEs on a single server.

1. To accomplish the setup of our profile for multiple database with separate ORACLE_HOMEs, we will have to create a .profile file with three sections: 1. request for information; 2. local variables definitions; and 3. global variables definitions. The request for information section will appear to the user upon logon or re-initialization of the .profile to determine which database the user wishes to use. The local variable section defines the variables need to connect to a particular database. The global variable section defines the variables which will be the same regardless of our connection to a certain database.

2. The request for information section defines the variable “database” which is used to set the ORACLE_SID, as seen below:

## Beginning of request for information
database=””
while [ -z “$database” ]
do
echo “\n\nEnter database & Oracle version to use:”
echo “1 – ORCL 10.2.0.4 ”
echo “2 – ORCL2 10.2.0.4”
echo “3 – ORCL3 10.2.0.4”
print -n “> ”
read database
if [[ “$database” != “1” &&
“$database” != “2” &&
“$database” != “3” ]]
then
echo “$database” is not valid >&3
database=””
fi
done
## Ending of request for information

3. The local section defines all variables for a specific database, as seen below:

##Beginning of local variable definitions
if [[ $database = “1” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl
export ORACLE_SID=orcl
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin/orcl
cd
elif [[ $database = “2” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl2
export ORACLE_SID=orcl2
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin/orcl2
cd
elif [[ $database = “3” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl3
export ORACLE_SID=orcl3
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin/orcl
cd

else
echo “Invalid options ”
fi

## Ending of local variable definitions

4. The global section defines all variables for any database regardless of the specific ORACLE_HOME, as seen below:

## Beginning of global variable definitions

echo “Your ORACLE_BASE is set to ‘$ORACLE_BASE'”
echo “Your ORACLE_SID is set to ‘$ORACLE_SID'”
echo “Your ORACLE_HOME is set to ‘$ORACLE_HOME'”

export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/obackup/bin
export ORACLE_TERM=vt220

umask 022
export PS1=$ORACLE_SID” $ ”
stty erase ^?
export LINES=24
export COLUMNS=80
export PATH=$PATH:/opt/java1.5:/opt/java1.5/bin

## Ending of global variable definitions

5. Place all profile sections into one file named .profile from UNIX or .bash_profile for linux under your /home/{username} directory. In this example, we will place the following text in the file /home/lcatt/.profile for OS user lcatt.

## Beginning of request for information
database=””
while [ -z “$database” ]
do
echo “\n\nEnter database & Oracle version to use:”
echo “1 – ORCL 10.2.0.4 ”
echo “2 – ORCL2 10.2.0.4”
echo “3 – ORCL3 10.2.0.4”
print -n “> ”
read database
if [[ “$database” != “1” &&
“$database” != “2” &&
“$database” != “3” ]]
then
echo “$database” is not valid >&3
database=””
fi
done
## Ending of request for information

##Beginning of local variable definitions

if [[ $database = “1” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl
export ORACLE_SID=orcl
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin/orcl
cd
elif [[ $database = “2” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl2
export ORACLE_SID=orcl2
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin/orcl2
cd
elif [[ $database = “3” ]]
then
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export ORACLE_BASE=/opt/app/oracle/product
export ORACLE_HOME=/opt/app/oracle/product/10.2/orcl3
export ORACLE_SID=orcl3
export PATH=.:/opt/perl/bin:$PATH:$ORACLE_HOME/bin
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/opt/java1.3
export TNS_AMDIN=$ORACLE_HOME/network/admin/orcl
cd

else
echo “Invalid options ”
fi

## Ending of local variable definitions

## Beginning of global variable definitions

echo “Your ORACLE_BASE is set to ‘$ORACLE_BASE'”
echo “Your ORACLE_SID is set to ‘$ORACLE_SID'”
echo “Your ORACLE_HOME is set to ‘$ORACLE_HOME'”

export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/obackup/bin
export ORACLE_TERM=vt220

umask 022
export PS1=$ORACLE_SID” $ ”
stty erase ^?
export LINES=24
export COLUMNS=80
export PATH=$PATH:/opt/java1.5:/opt/java1.5/bin

## Ending of global variable definitions

6. Change directory to your home directory with command cd, list current user with command id, and Re-initialize your .profile file as shown below:

$ orcl $ cd
orcl $ id uid=103(lcatt) gid=20(users) groups=200(dba)
orcl $ . ./.profile

7. As seen below the request for information section is displayed and we are presented with the choice of 1, 2, or 3. Select 1 to setup variables for the database ORCL.

Enter database & Oracle version to use:
1 – ORCL 10.2.0.4
2 – ORCL2 10.2.0.4
3 – ORCL3 10.2.0.4
> 1
Your ORACLE_BASE is set to ‘/opt/app/oracle/product’
Your ORACLE_SID is set to ‘orcl’
Your ORACLE_HOME is set to ‘/opt/app/oracle/product/10.2/orcl’
orcl $

This completes setup of .profile for multiple databases using the separate ORACLE_HOMEs.

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

linux, Oracle, Oracle 10g, unix

Enable User Tracing on Oracle Listener:

Leave a comment

Most Oracle database connects are made through the use of an Oracle process called the LISTENER which monitors a certain machine and port for connection requests to one or more database instances. There are times when a DBA or SA will want to monitor connects being made through the listener for various reasons. This article covers the setup of USER level tracing of you Oracle Listener and will work on UNIX, Linux, and Windows based machines.

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

2. Open up the LSNRCTL utility.

myhpux:>lsnrctl

LSNRCTL for HPUX: Version 10.2.0.4.0 – Production on 21-OCT-2009 18:17:05

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL>

3. If you are not using the default name of the Oracle listener (LISTENER) then define the Oracle listener name with the following command: set current_listener

LSNRCTL> set current_listener listener_orcl
Current Listener is listener_orcl
LSNRCTL>

4. If you have established a password for your oracle listener set it in the LSNRCTL session with the following command: set password

LSNRCTL> set password oracle
The command completed successfully
LSNRCTL>

5. Turn on user level tracing by specifying 4 or user as the level with the following command: set trc_level

LSNRCTL> set trc_level 4
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to user
The command completed successfully
LSNRCTL>

or

LSNRCTL> set trc_level user
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to user
The command completed successfully
LSNRCTL>

6. Define the name of the trace file that will be produced by the listener process upon establishment of user connection with the command below: set trc_file

LSNRCTL> set trc_file user_access_trace
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_file” set to user_access_trace.trc
The command completed successfully
LSNRCTL>

NOTE: You do not have to set a trace directory by default the trace files for the listener are placed under: $ORACLE_HOME/network/trace

7. Save the changes made to the listener.ora file with the following command: save_config

LSNRCTL> save_config
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
Saved listener_orcl configuration parameters.
Listener Parameter File /opt/app/oracle/10.2.0/network/admin/listener.ora
Old Parameter File /opt/app/oracle/10.2.0/network/admin/listener.bak
The command completed successfully
LSNRCTL>

8. Exit out of LSNRCTL utility, change directories to $ORACLE_HOME/network/trace, and list the files present and you will see that the trace has already begun.

LSNRCTL> exit
mylinux:> cd $ORACLE_HOME/network/trace
mylinux:> ls -lrt
-rw-r—– 1 oracle dba 3010 Oct 21 18:26 user_access_trace.trc

9. Logon to the RDBMS as a normal user and exit.

mylinux:> sqlplus ljcatt/ljcatt@orcl

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Oct 21 18:31:26 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> 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:>

10. Perform another listing of the files under directory
$ORACLE_HOME/network/admin and you will see that the file user_access_trace.trc has grown by recording the connection
from user in step 9.

mylinux:> ls -lrt
-rw-r—– 1 oracle dba 6810 Oct 21 18:31 user_access_trace.trc

11 This information can be used to show where connections in you database are coming from and other statistical information about the network connection being established.

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

Oracle 10g, RAC, SQL

Taking an Oracle database out of a multi-Node RAC

Leave a comment

I have found during some administrative tasks it is easier to have your system in single NODE configuration. An example of this, would be physical changes the database structure that due not include binary upgrades such as a CPU patch. It is easier to startup, shutdown, and restrict access thorough single stand alone system, rather then a RAC environment with several active nodes. I realize that most of you are quite verse in the user of the SRVCTL utility to administrate the instances and database. However, I would like to show the use of starting up the database in single node from within the database and using SRVCTL just to verify the status.

1. Logon to one node as sysdba.

mylinux1>$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Sep 3 18:47:59 2009

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

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

SQL>

2. Execute the following SQL “select inst_id, instance_name, instance_role, host_name from gv$instance;” and as you can see we are attached to a two node RAC.

SQL> select inst_id, instance_name, instance_role, host_name from gv$instance;

INST_ID INSTANCE_NAME INSTANCE_ROLE HOST_NAME
———- ————– —————– ——————
1 node1 PRIMARY_INSTANCE mylinux1
2 node2 PRIMARY_INSTANCE mylinux2

SQL>

3. Exit out of SQL*PLUS and use the SRVCTL utility to obtain the current RAC status. The utility SRVCTL verifies what we saw in the data dictionary view of GV$INSTANCE.

mylinux1>$ srvctl status database -d orcl
Instance node1 is running on node mylinux1
Instance node2 is running on node mylinux2

4. Due to the fact that we have two instance running, we will use the SRVCTL utility to shutdown the database.

mylinux1>$ srvctl stop database -d orcl

5. Again use the SRVCTL utility to view the status of your Instances and the results are displayed that both instance are shutdown.

srvctl status database -d orcl
Instance node1 is not running on node mylinux1
Instance node2 is not running on node mylinux2
mylinux1>$

6. Now you do have a choice here: 1. You could startup the database and a single instance with the SRVCTL utility or 2. You could logon to SQL*PLUS like a non-RAC system and startup the instance and database. We will logon to SQL*PLUS as sysdba and issue the startup command.

mylinux1>$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Sep 3 19:02:13 2009

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size 1262332 bytes
Variable Size 587205892 bytes
Database Buffers 1191182336 bytes
Redo Buffers 15511552 bytes
Database mounted.
Database opened.
SQL>

7. Execute the following SQL “select inst_id, instance_name, instance_role, host_name from gv$instance;” to display the results of starting the database from NODE1.

SQL> select inst_id, instance_name, instance_role, host_name from gv$instance;

INST_ID INSTANCE_NAME INSTANCE_ROLE HOST_NAME
———- ————– —————– ——————
1 node1 PRIMARY_INSTANCE mylinux1

SQL>

8. You can verify these results with the SRVCTL utility.

mylinux1>$ srvctl status database -d orcl
Instance node1 is running on node mylinux1
Instance node2 is not running on node mylinux2
mylinux1>$

9. Your system is now executing in a SINGLE NODE fashion and you can began your administrative tasks.

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

Oracle, Oracle 10g, oracle 9i, SQL

Recreating Oracle Public Database Link

Leave a comment

Oracle provides database links to create direct connections from one database instance to another. This feature can come in handy in the movement of small to moderate amounts of data between systems. In this article we will review the creation of the DDL to regenerate public database links defined within an Oracle database.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jul 3 19:21: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>

2. Create a public database link from your database to another database to demonstrate generation of your DDL.

create public database link oracle11
connect to ljcatt identified by ljcatt
using ‘(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11g)
)
)’;

SQL> create public database link oracle11
2 connect to ljcatt identified by ljcatt
3 using ‘(DESCRIPTION =
4 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1522))
5 (CONNECT_DATA =
6 (SERVER = DEDICATED)
7 (SERVICE_NAME = orcl11g)
8 )
9 )’;

Database link created.

SQL>

3. Execute the following PL/SQL block to generate your DB Links DDL.

set serveroutput on
declare

v_pwd varchar2(30);

begin

for rec in(select * from dba_db_links where owner=’PUBLIC’)
loop

dbms_output.put_line(‘create public database link ‘||rec.db_link);
dbms_output.put_line(‘connect to ‘||rec.username||’ identified by ‘||'(password)’);
dbms_output.put_line(‘using ‘||””||rec.host||””||’;’);

end loop;
end;
/

SQL> set serveroutput on
SQL> declare
2
3 v_pwd varchar2(30);
4
5 begin
6
7 for rec in(select * from dba_db_links where owner=’PUBLIC’)
8 loop
9
10
11 dbms_output.put_line(‘create public database link ‘||rec.db_link);
12 dbms_output.put_line(‘connect to ‘||rec.username||’ identified by ‘||'(pass
word)’);
13 dbms_output.put_line(‘using ‘||””||rec.host||””||’;’);
14
15
16 end loop;
17 end;
18 /
create public database link ORACLE11.REGRESS.RDBMS.DEV.US.ORACLE.COM
connect to LJCATT identified by (password)
using ‘(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT =
1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11g)

)
)’;

PL/SQL procedure successfully completed.

SQL>

4. Now you can use the DDL generated to move or recreate your public database link. NOTE: In Oracle 9i you can retrieve the password through the view sys.link$. However, in Oracle 10g and on, all passwords are encrypted, thus you must manually insert the actual password.

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

Oracle, Oracle 10g, oracle 9i, RMAN

Oracle RMAN – Recovery of entire Oracle database

Leave a comment

Oracle’s Recovery Manger (RMAN), originally released in Oracle 8, is a proprietary utility used for the backup and recovery of Oracle databases. Given a good Oracle RMAN backup, A DBA can recover from any disaster which may occur to their database, to include: loss of controlfiles, loss of datafiles, corruption of datafiles, loss of the entire database and even provides the ability to clone a database. In this article we give detailed instructions on the recovery of an entire Oracle database in Oracle 9i, however these instructions will work in versions 10g and 11g.

1. Logon to the Oracle server as the Oracle software owner and set the ORACLE_SID.

[oracle@testdb02 ORCL]$ su – oracle
Password:
[oracle@testdb02 ~]$ export ORACLE_SID=ORCL
[oracle@testdb02 ~]$

2. Logon to SQL*PLUS and obtain location of all datafiles from dba_data_files and location of all control files by the show parameter control_file command.

[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:45:27 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> select file_name from dba_data_files;

FILE_NAME
——————————————————————————–
/u01/oradata/ORCL/system01.dbf
/u04/oradata/ORCL/undo01.dbf
/u05/oradata/ORCL/users01.dbf
/u05/oradata/ORCL/tools01.dbf

SQL> select file_name from dba_temp_files;

FILE_NAME
——————————————————————————–
/u05/oradata/ORCL/temp02.dbf

SQL> show parameter control_files

NAME TYPE VALUE
———————————— ———– ——————————
control_files string /u01/oradata/ORCL/control01.c
tl, /u03/oradata/ORCL/control
02.ctl, /u06/oradata/ORCL/con
trol03.ctl
SQL>

3. Navigate to each of the files listed and add the extension of _bak to the files.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
[oracle@testdb02 ORCL]$ cd /u01/oradata/ORCL/
[oracle@testdb02 ORCL]$ ls
archive control01.ctl redolog1a.dbf redolog2b.dbf system01.dbf
[oracle@testdb02 ORCL]$ mv control01.ctl control01.ctl_bak
[oracle@testdb02 ORCL]$ mv system01.dbf system01.dbf_bak
[oracle@testdb02 ORCL]$ cd /u03/oradata/ORCL/
[oracle@testdb02 ORCL]$ ls
archive control02.ctl redolog1b.dbf
[oracle@testdb02 ORCL]$ mv control02.ctl control02.ctl_bak
[oracle@testdb02 ORCL]$ cd /u04/oradata/ORCL/
[oracle@testdb02 ORCL]$ ls
undo01.dbf
[oracle@testdb02 ORCL]$ mv undo01.dbf undo01.dbf_bak
[oracle@testdb02 ORCL]$ cd /u05/oradata/ORCL/
[oracle@testdb02 ORCL]$ ls
sqlnet.log temp02.dbf tools01.dbf users01.dbf
[oracle@testdb02 ORCL]$ mv temp02.dbf temp02.dbf_bak
[oracle@testdb02 ORCL]$ mv tools01.dbf tools01.dbf_bak
[oracle@testdb02 ORCL]$ mv users01.dbf users01.dbf_bak
[oracle@testdb02 ORCL]$ cd /u06/oradata/ORCL/
[oracle@testdb02 ORCL]$ ls
archive control03.ctl redolog2a.dbf
[oracle@testdb02 ORCL]$ mv control03.ctl control03.ctl_bak
[oracle@testdb02 ORCL]$ ls
archive control03.ctl_bak redolog2a.dbf
[oracle@testdb02 ORCL]$

4. Stop the Oracle database by killing the active SMON process of the ORCL instance.

[oracle@testdb02 ORCL]$ ps -ef|grep smon
oracle 14206 1 0 21:37 ? 00:00:00 ora_smon_ORCL
oracle 15121 13009 0 21:56 pts/4 00:00:00 grep smon
[oracle@testdb02 ORCL]$ kill -9 14206
[oracle@testdb02 ORCL]$ ps -ef|grep ORCL
oracle 12023 11973 0 20:51 pts/6 00:00:00 vim ORCL_RMAN.sh
oracle 15139 13009 0 21:56 pts/4 00:00:00 grep ORCL
[oracle@testdb02 ORCL]$

5. Enter SQL*PLUS and startup the database in nomount state. You have to startup the database with nomount in order to recovery the controlfiles.

[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:58:01 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 144670496 bytes
Fixed Size 741152 bytes
Variable Size 109051904 bytes
Database Buffers 33554432 bytes
Redo Buffers 1323008 bytes
SQL>

6. Exit SQL*PLUS, enter RMAN and connect to the target database and recovery catalog.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
[oracle@testdb02 ORCL]$ RMAN

Recovery Manager: Release 9.2.0.8.0 – 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target sys/h1g0alz

connected to target database: ORCL (not mounted)

RMAN> connect catalog RMAN/RMAN@RMANDB

connected to recovery catalog database

7. Issue the restore database command, which replaces all database objects with the backup set copies.

RMAN> restore database;

Starting restore at 12-JUN-08

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/ORCL/system01.dbf
restoring datafile 00002 to /u04/oradata/ORCL/undo01.dbf
restoring datafile 00003 to /u05/oradata/ORCL/users01.dbf
restoring datafile 00004 to /u05/oradata/ORCL/tools01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u07/RMAN_BACKUP/ORCL/ORCL_657220129_1_26_1_0qjiooh1.dbf tag=BACKUP_FULL_ORCL params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 12-JUN-08

8. Issue the restore controlfile command, which replaces all controlfiles with the backup set copies.

RMAN> restore controlfile;

Starting restore at 12-JUN-08

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring controlfile
output filename=/u01/oradata/ORCL/control01.ctl
channel ORA_DISK_1: restored backup piece 1
piece handle=/u07/RMAN_BACKUP/ORCL/ORCL_657220219_1_27_1_0rjioojr.ctl tag=TAG20080612T171019 params=NULL
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/u01/oradata/ORCL/control01.ctl
output filename=/u03/oradata/ORCL/control02.ctl
output filename=/u06/oradata/ORCL/control03.ctl
Finished restore at 12-JUN-08

RMAN>

9. Exit RMAN and enter SQL*PLUS, alter the database to a mount state. The mount state will allow you to restore the database using the archive redo logs.

RMAN> exit

Recovery Manager complete.
[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 22:06:33 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> alter database mount;

10. Exit SQL*PLUS and enter RMAN utility, connect to the target database and catalog.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
[oracle@testdb02 ORCL]$ RMAN

Recovery Manager: Release 9.2.0.8.0 – 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target sys/h1g0alz

connected to target database: ORCL (DBID=1373796530)

RMAN> connect catalog RMAN/RMAN@RMANDB

connected to recovery catalog database

11. Issue the recovery database command. This will apply all archive redo logs to the datafiles and controlfiles.

RMAN> recover database;

Starting recover at 12-JUN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK

starting media recovery

archive log thread 1 sequence 28 is already on disk as file /u01/oradata/ORCL/archive/ORCL_ARCH28.arc
archive log thread 1 sequence 29 is already on disk as file /u06/oradata/ORCL/archive/ORCL_ARCH29.arc
archive log thread 1 sequence 30 is already on disk as file /u06/oradata/ORCL/redolog2a.dbf
archive log thread 1 sequence 31 is already on disk as file /u03/oradata/ORCL/redolog1b.dbf
archive log filename=/u01/oradata/ORCL/archive/ORCL_ARCH28.arc thread=1 sequence=28
archive log filename=/u06/oradata/ORCL/archive/ORCL_ARCH29.arc thread=1 sequence=29
archive log filename=/u06/oradata/ORCL/redolog2a.dbf thread=1 sequence=30
archive log filename=/u03/oradata/ORCL/redolog1b.dbf thread=1 sequence=31
media recovery complete
Finished recover at 12-JUN-08

RMAN>

12. Exit RMAN, enter SQL*PLUS and alter the database open with reset logs command. NOTE: you must perform new full database RMAN backup after you reset the logs. ANY backup taken prior will now be useless.

RMAN> exit

Recovery Manager complete.
[oracle@testdb02 ORCL]$ SQL*PLUS ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 22:10:06 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> alter database open resetlogs;

Database altered.

SQL>

This completes the recovery of full database.

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