Thursday, January 31, 2013

0 Auto restart Oracle and related service via dbstart

For  Oracle Grid or ASM environment , the Oracle restart is being used to start the Oracle after the OS startup. However, the Oracle instance does not use the Oracle RAC and ASM, we still need to use dbstart/dbshut to start the oracle.

 

dbstart and dbshut

 

Edit the /etc/oratab file

The file contains the Oracle SID and the oracle home directory. The last parameter decide whether dbstart would bring it up or not.

SID: ORACLE_HOME: Y/N

image

After the ORALCE_SID is set to Y, we can use dbstart to bring up the oracle instance.

Known issue with ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener

There is known issue with the dbstart. We would get the error message about “ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener”. The database would startup fine but the listener does not start.

image

To fix the error, we need to pass the $ORACLE_HOME to the dbstart ..

image

However, this would only bring up the default listener. I can not find anyway to bring up the named listener via dbstart. I have to create the customize script in the init.d to do this.

Create /etc/init.d/oracle

#!/bin/sh
# chkconfig: 345 99 10
 
ORA_OWNER=oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0
 
case "$1" in
    'start')
        su - $ORA_OWNER -c "$ORACLE_HOME/bin/lsnrctl start LISTENER_ORACLEDB1" 
        su - $ORA_OWNER -c "$ORACLE_HOME/bin/dbstart" 
        su - $ORA_OWNER -c "$ORACLE_HOME/bin/emctl start dbconsole" 
    ;;
    'stop')
        su - $ORA_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop LISTENER_ORACLEDB1" 
        su - $ORA_OWNER -c "$ORACLE_HOME/bin/emctl stop dbconsole" 
        su - $ORA_OWNER -c "$ORACLE_HOME/bin/dbshut" 
    ;;
esac

 

In order for chkconfig to recognize the script, we need to have “# chkconfig: 345 99 10

chmod 750 oracle
chkconfig --add oracle
chkconfig --list oracle

image

To test the init.d script, we can use service command

service oracle start
service oracle stop

 

Reference

3 Use netca to configure Oracle Listener

netca (Network Configuration Assistant) is Oracle command line for configure the Oracle listener.
Enterprise Manager has dependence on the listener. If listener is not running, we can not login to the Enterprise manager as EM require connects to database to verify the password. If the listener is started after the EM started, we can login to the EM but the agent would fail connect to TNS. In this case, we need to bounce the EM.

Note: Listener name is not case sensitive. LISTENER_ORACLEDB is same as LISTENER_oracledb.

Use the netca GUI to create the listener


image
image
image
image
image
image
image
image
lsnrctl status

image
The listener is listen to the default port 1521. The oracle database would auto register to the listener ( PMON processes). 

Use responds file to create the listener

Prepare the responds file

The tip for the responds file is INSTALL_TYPE. If you want to have the custom listener name and port instead of default name <LISTENER> and port 1521. We need to set this property as “CUSTOM”

[GENERAL]
RESPONSEFILE_VERSION="11.2"
CREATE_TYPE="CUSTOM"
SHOW_GUI=false
LOG_FILE=""/u01/app/oracle/product/11.2.0/network/tools/log/netca.log""
[oracle.net.ca]
INSTALL_TYPE=""CUSTOM""
LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER_ORACLEDB1"}
LISTENER_PROTOCOLS={"TCP;7001"}
LISTENER_START=""LISTENER_ORACLEDB1""
INSTALLED_COMPONENTS={"server","client","net8","javavm"}

Execute the netca

$ORACLE_HOME/bin/netca –silent -responsefile=/local_dir/netca.rsp

image
image

Manually Create the listener

Manually create the listener.ora file and start the listener from command line.
LISTENER_ORACLEDB1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = panda1.localdomain)(PORT = 7001))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC7001))
    )
  )
 
ADR_BASE_LISTENER_ORACLEDB1 = /u01/app/oracle
 

lsnrctl stop LISTENER_ORACLEDB
lsnrctl status LISTENER_ORACLEDB


If listener dost not listen to the default port

In the example blow, my listener is set to listen port 7001 hence the database instance would not connect to the listener automatically.
image
The solution is to set the local listener ( LOCAL_LISTENER) parameter.

set local listener

localhost can refer to the local host or the host name on the host ( In this example, panda1 ) or the remote host where the listener is running.
alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT=7001))' scope=both;

image
Alternately, we can put the host configuration in the tnsname.ora.
LISTENER_ORACLEDB1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 7003))
 

and change the LOCAL_LISTENER point to the LISTENER_ORACLEDB1
alter system set LOCAL_LISTENER='LISTENER_ORACLEDB1' scope=both;


We must create the entry in the tnsname.ora first otherwise we could get the error while trying to run the alter system command.
image

Reference

Tuesday, January 29, 2013

0 Logout automatically after log in

Today, I try to login to my Oracle Linux VM with my oracle account. It was working properly before reboot. However, somehow when I login , the system log me off immediately.

I suspect it maybe something with my .bash_profile which could cause the error. Luckily, I have another account which I can use to login. After I login and found the error message in the .xsession-errors

image

I was right about the .bash_profile. After fixing it, I can successfully login with my oracle account.

Monday, January 28, 2013

9 How to fix “Device eth0 does not seem to be present, delaying initialization.”

After clone the VM within Virtual box, I receive the error message “

Device eth0 does not seem to be present, delaying initialization.” when start the network service.

image

The /etc/sysconfigu/network-scripts/ifcfg-eth0 does exist. The issue is that the network device eth0 is non longer exist. The VM clone processes create the new device eth1.

The device file is at /sys/class/net.

ip link show

image

We can rename the device by using the up command.

ip link set eth1 name eth0

image

After the device being renamed, we can successfully bring up the network interface.

Sunday, January 27, 2013

3 Install Sample Database/Schema for Oracle 11gR2

I want to install the sample database for my Oracle database server.

Prerequisite

Oracle Database installed. You can refer here for installing the Oracle Database.

Software

 

Install the Oracle Example

image

image

the 11.2.0 installer does not recognize the newer version of package therefore, if the system already has the newer version of the packages, it can be ignore.

image

image

image

After the installer completed, the mksample.sql script is populate to under $ORACLEHOME/demo/schema directory. We can use it to create the sample schema.

image

Install the Sample Schema

I am going to use the mksample.sql to create the sample schema. This is the primary script and will invoke other script to create the correspond schema. If only need some schema, you can just invoke the individual script. For example, the HR schema can be created by invoking hr_main.sql under human_resources folder.

 

@$ORACLE_HOME/demo/schema/mksample.sql

 

The log file path must be end with / .

image

After scripts, we can exam the log file in the log directory to see if there is any error.

 

 

Reference

Saturday, January 26, 2013

1 Alter database set offline take forever …

There are few options after setting the database offline.

 

Alter database set offline

This is the most basic form for the syntax.

alter database "AdventureWorks" set offline 

This statement would attempt to set the database in the restrict mode but would wait any active processes to complete. This includes if user just simply run “use database” or just there is the internal processes “AUTO_UPDATE_STATISTICS_ASYNC”.  If this database option  “AUTO_UPDATE_STATISTICS_ASYNC” is set, it is possible to block the alter database command.

To check whether the AUTO_UPDATE_STATISTICS_ASYNC is set, we can not find it from sp_helpdb or sp_dboption. We need to check the sys.database table.

ALTER DATABASE "AdventureWorks2" SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE "AdventureWorks2" SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
sp_helpdb "AdventureWorks2"
GO
select name,is_auto_update_stats_on,is_auto_update_stats_async_on from sys.databases where name='AdventureWorks2'
Go
sp_dboption "AdventureWorks2"
Go

image

If there is active user connection block the command ( we can find it base on sp_who), just kill the session, and the alter database would continue to bring the database offline.

alter database set offline with no_wait 

alter database "AdventureWorks2" set offline with no_wait  
When use with NO_WAIT option, the alter database command will fail with error if there is any active user connection. So the command would not be hang forever.
image

ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE

alter database "AdventureWorks2" set offline WITH ROLLBACK IMMEDIATE 
image

This would terminate all the user connection, processes and set the database offline.

Wednesday, January 23, 2013

31 Oracle SQL Developer fail to connect to RAC SCAN with ORA 12505 error

When I try to connect to my Oracle RAC test environment via SCAN name with SQL Developer, I got the error “ORA-12505, TNS:listener does not currently know of SID given in connect descriptor” .

RAC1 is my ORACLE_SID name. SQL Developer version is 3.2

 

image

If I use the same string connect from SQLplus, it works fine as screenshot below:

image

After googling, I found the solution in SQL Developer by using the JDBC connection string:

jdbc:oracle:thin:@rac-scan:1521/RAC1 

 

image

Not sure why the SQL developer behavior this way, but at least I have the solution. I will update the post if I found more information.

Tuesday, January 22, 2013

0 Oracle RAC management 3: ocrcheck and srvctl

ocrcheck

ocrcheck is the command to check the Oracle Cluster Register information.

image

 

srvctl

srvctl is the command to  manage, maintain and check the RAC related services, such as Instance, Database, Listener and ASM.  You can see here for the usage example.

Here are other usages for srvctl.

Manage Listener service

srvctl status listener
srvctl status listener -n NODE_NAME
srvctl stop listener 
srvctl stop listener -n NODE_NAME
srvctl start listener
srvctl start listener -n NODE_NAME

Show the listener status

image

Stop listener. There is no output for srvctl, so we need to use crsctl or srvctl to check the listener status

image
Start the listener

image

If we try to start the listener on the node which the listener is already running, we would get the “PRCC-1015 : LISTENER was already running on rac2” error.

image

Manage the database

I already cover the start/stop database via srvctl in here. Here are other usages:

srvctl status database -d DB_UNIQUE_NAME
-- Stop the database,this would stop the Database and instance 
srvctl stop databsae -d DB_UNIQUE_NAME
-- Start the database,this would start the Database and instance 
srvctl start database -d DB_UNIQUE_NAME 
-- Stop instance and database on one instance
srvctl stop instance -d DB_UNIQUE_NAME -i INSTANCE_NAME -o <immediate|abort>
-- Start the instance and database 
srvctl start instance -d DB_UNIQUE_NAME -i INSTANCE_NAME
-- Remove database from the RAC  , This would remove instance, database and Node
srvctl remove database -d DB_UNIQUE_NAME
-- Add database to the RAC
srvctl add database -d DB_UNIQUE_NAME -o ORACLE_HOME
-- Remove Instance from the RAC
srvctl remove instance -d DB_UNIQUE_NAME -i INSTANCE_NAME 
-- Add Instance to the RAC
srvctl add instance -d DB_UNIQUE_NAME -i INSTANCE_NAME -n NODE_NAME
-- Remove node
srvctl remove nodeapps -n NODE_NAME

Stop the database and instance

image

Start the database for one instance. This also bring up the database to the open state.

image

image

image

Reference

:

http://docs.oracle.com/cd/B19306_01/rac.102/b28759/adminoc.htm

2 Oracle RAC management 2: Start/Stop RAC database

 

Stop the RAC database

Step 1: Check the RAC database status

srvctl status database -d DATBASE_UNIQUE_NAME
ps -ef |grep -i smon

Each node has one database instance and one ASM instance.Therefore, the ps –ef |grep –i  smon would see 2 smon processes. One for ASM and another one for database.

image

Step 2: Shutdown database instance via srvctl

srvctl stop databsae -d DATABASE_UNIQUE_NAME
ps -ef |grep -i smon
srvctl status databsae -d DATABASE_UNIQUE_NAME
crsctl status resource RESOURCE_NAME -t

The srvctl stop database would shutdown the database instance on both nodes.

image

Step3: shutdown the clusterware

To shutdown the clusterware, we can not just use oracle or grid account. It would give us the “CRS-4563: Insufficient user privileges” error.

image

su to root , Source the environment for grid and Check the cluster resource status

crsctl status resource -t 

image

crsctl stop cluster

From screenshot below, we can see once we shutdown the cluster resource from RAC1 node, the clusterware would failover to the other node.

image

To verify the clusterware is complete shutdown on the node1 (RAC1), we can use crsctl status resource to check. We would get the “CRS-4535: Cannot communicate with Cluster Ready Services

image

Therefore, in order to complete shutdown the clusterware on all node, we have to run the crsctl stop cluster on all nodes.

On the node2, if we run check the cluster resource ( crsctl status resource –t), we would see the resource is in the failover state.

image

image

Start the RAC database

Step1: Check clusterware is not running

crsctl status resource -t

image

Step2: Use root to start the clusterware

root user needs to source the grid environment.  This needs to be perform on all RAC nodes.

crsctl start cluster

image

image

Step3: Check the cluster resource and ASM instance

crsctl status resource -t
ps -ef |grep -i smon

image

image

Step4: Start the Oracle database instance

This would start the database instance on all the RAC nodes.

srvctl start database -d RAC1

There is no output from above command, just use the crsctl status resource to check the database resource. We can also check the smon processes.

image

We should see 2 smon processes on the host.

image

Step5: Start the OEM

OEM is not part of cluster resource. If you have shutdown earlier, we need to bring it up.

emctl start dbconsole
 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates