Importing Data via Network


For two projects there has been an assignment to upgrade to 11.20.4 Oracle. One environment was already 11.2.3 with same Cluster stack below it and one environment will come from on Solaris.  For both projects on Linux  an cluster-stack plus database version has been set up on one of the newer shared clusters.  Both environments will be migrated using the export – import method (since they are relatively small ( app 400- 500 GB) ) and of course since one of them is being migrated cross platforms (from Solaris to Linux ) you do not have that much choice.

In other project I had good experience with nfs filesystems between source and target servers and at first was aiming to use them again during these migrations.  However since not every project is able to make it to the time lines ( will have to wait for at least 2 more weeks to get the nfs mounts ) other creativity will be  required. In this specific case will work with datapump via the network.

When looking into this scenario i came across two scenarios. First scenario being covered by a fellow blogger and interesting since it offers the option to export directly into an ASM disk group. In  that scenario extra step would be needed using impdp with directory to the same  asmdiskgroup/subdirectory. Second scenario which is explained in more detail here is even one step beyond. Scenario is simple  using impdp via a dblink directly in the database ( not even a need to park a dumpfile somewhere on filesystem or in diskgroup first and then run the imp). Nope just another  imdp and you are there !



1.     Setting up  tnsnames entry on the target ( receiving ) side.


In order to make this scenario work  you will have to make sure that there is no firewall in place to the source database you will pull the data from when you create the tnsnames.ora entry on the target side.

In my case:


I always try a: telnet <ip> <port>

telnet  666.233.103.203  33012


If  you see something like trying ….  and nothing helps will happen well this was not your lucky day and a firewall is blocking you from making this a happy scenario.  If you see something like this lucky you :

Escape character is '^]'.

Recommendation when you get stuck with trying … then is to make sure that firewall  is opened. In my case my host was a vip address for a rac database and Port 33012 had been assigned to the local listener of that database.


## Let set up the tnsnames entry  NOTE : firewall needs to be freed before proceed with tnsping etc:


One interesting part is that the service_name of the tnsnames  i wanted to use was not present as a service in the database so I had to add to extend the present service (which was not default service since it was without  domain).


## ## On the source side in the database where i want to take the data from:  added service:


alter system set service_names = ‘MYDB’,’’ scope = both ;


SQL> show parameter service


NAME                                                      TYPE VALUE

———————————— ———– ——————————

service_names                                      string               MYDB,


So now we have two services in place which we can use in the tnsnames.ora.


2.     Time to set up a public dblink


## Reading articles by fellow bloggers they recommended to created PUBLIC (this seems mandatory) db link. Since in my case i would do the import with system a normal db link would b okay too. But for the scenarios sake  public database link is fine.


drop public DATABASE LINK old_MYDB;

## worked with this one


3.     Seeing is believing , test the db link.


## performed select

select ‘x’ from  dual@old_MYDB;

4.     Next stop, creating a directory for the logfile of the impdp.


Yes that is correct only a directory for the log file not for the dump itself J  that is why i liked this scenario so much.


## created directory for the logfile

create directory acinu_imp as ‘/opt/oracle/MYDB/admin/create’ ;

grant read,write on directory acinu_imp to system;




5.     Time to perform the import.


Over the years have used expdp and impdp a lot  but most time as an almost 1:1 clone of exp/ imp. But since Google  is your friend when looking for scenarios it was great to explore the  powerful option of exclude= parameter. As you will see ,  creating an import of the full database but excluding the  schemas i don’t care about.


Since i was hmm energy efficient i wanted to type the full statement in Linux but was punished  by having ” ” in my command. However had i used a parfile things would have been easier J . But since i wanted to stick to scenario found that whenever on OS  ” level an \ will be mandatory like below:


## performed import  with success with  command below


impdp system full= yes "EXCLUDE=SCHEMA:\"IN('ADBM','DBSNMP','PERFSTAT','UPDOWN','ORACLE_OCM','OUTLN','SYS','SYSTEM')\"" network_link=old_MYDB directory=acinu_imp logfile=AcinupImport.log parallel=2 job_name=MYDB_DMP_FULL



## Note

At first all my scenarios  had error below


Connected to: Oracle Database 11g Enterprise Edition Release – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39200: Link name “OLD_ACINUP” is invalid.
ORA-02019: connection description for remote database not found


This made me check  the services in the database, the entry in the tnsnames, and test it all again.  After that as A-team Hannibal would say , love it when a plan comes together  it worked !


Happy reading ,


And always don’t believe it just because it is printed.


Mathijs Bruggink



Asm Instance not starting after Cluster Node Reboot


I have been involved again in a situation where  the Rac cluster did not start after a reboot of the server during a maintenance window. And as always a true challenge that was. In such cases it is true that the alert log of the node and the ohasd logging  will be your best friends ( well together with Metalink and Google of course).


After a Os patching action on one of the nodes on one of my 11.2 Racs (Grid Infrastructure)  i was contacted can you please take a look cause the clusterware is not starting. After first investigation it showed that statement was not entirely true .  The cluster ware itself had been started but the  log file for the ohasd. showed following details , that it was not able to start the asm Resource due to ORA-01031: insufficient privileges.

this is what it showed:

## /opt/crs/product/ [+ASM1]# view ohasd.log
2013-08-06 11:05:01.643: [    AGFW][1980881216] {0:0:2} Received the reply to the message: RESOURCE_CLEAN[ora.asm 1 1] ID 4100:411 from the agent /opt/crs/product/
2013-08-06 11:05:01.644: [    AGFW][1980881216] {0:0:2} Agfw Proxy Server sending the reply to PE for message:RESOURCE_CLEAN[ora.asm 1 1] ID 4100:410
2013-08-06 11:05:01.644: [   CRSPE][1991387456] {0:0:2} Received reply to action [Clean] message ID: 410
2013-08-06 11:05:01.644: [   CRSPE][1991387456] {0:0:2} Got agent-specific msg: ORA-01031: insufficient privileges
2013-08-06 11:05:01.646: [    AGFW][1980881216] {0:0:2} Received the reply to the message: RESOURCE_CLEAN[ora.asm 1 1] ID 4100:411 from the agent /opt/crs/product/
2013-08-06 11:05:01.646: [    AGFW][1980881216] {0:0:2} Agfw Proxy Server sending the reply to PE for message:RESOURCE_CLEAN[ora.asm 1 1] ID 4100:410
2013-08-06 11:05:01.646: [   CRSPE][1991387456] {0:0:2} Received reply to action [Clean] message ID: 410
2013-08-06 11:05:01.829: [    AGFW][1980881216] {0:0:2} Received the reply to the message: RESOURCE_CLEAN[ora.asm 1 1] ID 4100:411 from the agent /opt/crs/product/
2013-08-06 11:05:01.829: [    AGFW][1980881216] {0:0:2} Agfw Proxy Server sending the last reply to PE for message:RESOURCE_CLEAN[ora.asm 1 1] ID 4100:410
2013-08-06 11:05:01.829: [   CRSPE][1991387456] {0:0:2} Received reply to action [Clean] message ID: 410
2013-08-06 11:05:01.829: [   CRSPE][1991387456] {0:0:2} RI [ora.asm 1 1] new internal state: [STABLE] old value: [CLEANING]
2013-08-06 11:05:01.829: [   CRSPE][1991387456] {0:0:2} CRS-2681: Clean of 'ora.asm' on 'Mysrvr1r' succeeded

That did not look all to good. I had a first guess about what was going on by trying to connect to the asm instance on that box via sqlplus ( sqlplus / as sysasm). When that showed  also the ORA-01031: insufficient privileges.

I had to giggle cause when  looking for that  message on the web  i ended up with my blog. Which proves once again that you can help yourself by helping others by sharing in the Oracle community.   Basically i  focused on  three metalink notes that might apply:

Troubleshooting ORA-1031: Insufficient Privileges While Connecting As SYSDBA [ID 730067.1]

UNIX: Checklist for Resolving Connect AS SYSDBA Issues [ID 69642.1]

UNIX: Diagnostic C program for ORA-1031 from CONNECT INTERNAL / AS SYSDBA [ID 67984.1]

The third note (67984.1) was my bingo !  So it was proved that my groupid ( dba) altered from 101 to some other value by a ldap lookup.. I have asked the Linux colleague  to disable these lookups and after that the asm instance started and all the instances as well.  As a workaround , in the /etc/ldap.conf they have added the oracle user to the nss_initgroups_ignoreusers to prevent this from happening.

Happy reading,


When Starting a Rac database brings you: ORA-29760: instance_number parameter not specified.


Recently was supporting a colleague who was unable to start a Real Application Database after he had  created the environment by manual scripts. As i already mentioned in another post nothing wrong with exploring both methods ( either using DBCA (with care but hey any tool will come with a manual and  some shortcomings) or manual. Well if you decide to work manually  of course it will take maybe even  more discipline in setting up init.ora files , registering the database and its instances into the Grid Infra structure layer etc.  But generally speaking there is a challenge in either approach you choose. Well on to our case.  The Database would not start via the clusterware (with srvctl ) but manually  it was possible to start either of the two instances in this Rac.


Well first glimpse to see what might be going on:

SQL> select * from gv$instance;

———- ————— —————- —————————————————————- —————– ——————- ———— — ———- ——- —————
———- — —————– —————— ——— —
1 1 MYDB1 server1hr 11.04.2013 16:14:31 OPEN YES 1 STARTED

2 2 MYDB2 server2hr 11.04.2013 16:25:54 OPEN YES 2 STARTED

That query  showed both instances running. 

Then the colleague tried:

srvctl start database -d  … 

PRCR-1079 : Failed to start resource ora.mydb.db

CRS-5017: The resource action “ora.mydb.db start” encountered the following error:
ORA-29760: instance_number parameter not specified
. For details refer to “(:CLSN00107:)” in “/opt/crs/product/11203_ee_64/crs/log/server1hr/agent/crsd/oraagent_oracle/oraagent_oracle.log”.

CRS-2674: Start of ‘ora.mydb.db’ on ‘server1hr’ failed
CRS-2632: There are no more servers to try to place resource ‘ora.mydb.db’ on that would satisfy its placement policy
CRS-5017: The resource action “ora.mydb.db start” encountered the following error:
ORA-29760: instance_number parameter not specified
. For details refer to “(:CLSN00107:)” in “/opt/crs/product/11203_ee_64/crs/log/server2hr/agent/crsd/oraagent_oracle/oraagent_oracle.log”.

CRS-2674: Start of ‘ora.mydb.db’ on ‘server2hr’ failed

Well then it was time to Google  for ORA-29760: instance_number parameter not specified

A Fellow blogger  mentioned the following Mos Note to solve this issue. And it once again proved that if you really settle for doing manual setup of you Rac environment you have to be well prepared and consistent in the naming of your parameters in you configuration file ( init.ora as a base for an spfile) and the way you enter services in the cluster layer. It once again proved that  names are registered in the way you enter them.  In plain English it is Case sensitive so  the old saying : what you type is what you get is very applicable.

MOS note ‘ORA-29760: instance_number parameter not specified’ When Starting the Database with Srvctl [ID 749515.1]

Happy reading and as always best of luck.


11.2 Rac – Instance unwilling to start with Error ORA-00132


This week  had one of those days where after working together with customer and Oracle Consultant  one  of  the Rac Databases needed a restart due to a variable change.  Together with customer it was decided to restart second  instance first so first took care of the  environment by enabling a Maintenance mode so our monitoring tool would not strike me with tickets.

The second instance was   friendly:

  • the shutdown: srvctl stop instance -d -i went smooth .
  • the startup: srvctl start instance -d -i went smooth .

Then i  performed same steps on first Instance:

  • the shutdown: srvctl stop instance -d -i went smooth .
  • the startup: srvctl start instance -d -i   failed  with an error message that puzzled me a lot .

ORA-00132: syntax error or unresolved network name ‘myserver-scan:33000’

I checked my scan listener settings:

oracle@myserver-s1:/opt/oracle [CRS]#
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node myserver-s4
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node myserver-s2
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node myserver-s3

oracle@myserver-s1:/opt/oracle [CRS]# srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:33000
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:33000
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:33000

Crs agent log showed same error and i could not start the instance via the  srvctl tool. The error suggested that my remote listener ( i am using scan ) was the issue.. But the  remote listener was not even part of  my tnsnames.ora, since my spfile value for the remote listener = myserver-scan:33000.

That was soo confusing. Well  I attempted to start the Instance via sqlplus  and that worked. I checked the cluster and it reported  ONLINE ONLINE on the resource so  the first pressure was off.

To solve it , make sure that  the sqlnet.ora is similar on all environments and it seems to need the ezconnect information in there  ( in names.directory_path).([ID 1318026.1])

Happy reading


Some Like it DBCA others prefer manual JVM Installs


For a couple of databases  I had to  install the Java Virtual Machine after the Database had been created. First attempt was  using the DBCA  gui  to get that done so i started it and  made the obvious choices  to work with A Rac database , to configure the database options  and selected the  Database I wanted to work with ( in the list only databases showing no instances).  DBCA was not so nice to me this time, telling me: “service name or Instance name not specified”. Hmm  well since i knew i can do without GUI  anyhow i decided to take the manual route and leave DBCA  be  for the moment. So lets saddle up and do it the old  – still – going strong  command line way .

My environment is a Linux 5 enterprise edition, my Oracle version is My Database is a two Instance Rac Database.

Manual Installation:

In order to  install the JVM  i decided it would be best to have the database  in an exclusive way so after contacting customer  and getting his okay  my first step was to shut down the Rac database. I had two screens open  one to tail the alertlog and in  the other in the Linux Prompt i had :

srvctl stop database -d MYDB

Alert log showed that the database was coming down smoothly so it was time to set up a script to get things working:

— Start of Installation the JVM manually.
— Important note this will only work this way if there is no JVM or part of JVM present in the database
— Investigate MOS if you suspect a former wrong installation  or partly installation before proceeding

spool InstallJvm.lst;
set echo on
-- connecting as sysdba cause this is required for this install 
-- and mount the database.
connect / as sysdba
startup mount

-- set_system_trig_enabled to false and open the database
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;

-- 28= 'JAVA SOURCE', 29= 'JAVA CLASS', 
select obj#, name 
from obj$ 
where type#=28 
or type#=29 
or type#=30 
or namespace=32;

select count(*), object_type 
from all_objects 
where object_type like '%JAVA%' 
group by object_type;

select count(*), object_type 
from all_objects 
where object_type 
like '%JAVA%' 
group by object_type;

select count(*), object_type 
from all_objects 
where object_type 
like '%JAVA%' 
group by object_type;

select count(*), object_type 
from all_objects 
where object_type like '%JAVA%' 
group by object_type;

select count(*), object_type 
from all_objects 
where object_type like '%JAVA%' 
group by object_type;

shutdown immediate
set echo off
spool off
starting the database again via the cluster:
srvctl start database -d MYDB

##  Time to go and check Health of JVM in Oracle : 
select comp_name, version, status 
from dba_registry;

COMP_NAME                                    VERSION         STATUS 
-------------------------------------------- --------------- ----------- 
Oracle Expression Filter                 VALID 
Oracle Application Express              VALID 
Oracle XML Database                      VALID 
Oracle Database Catalog Views            VALID 
Oracle Database Packages and Types       VALID 
JServer JAVA Virtual Machine             VALID 
Oracle XDK                               VALID 
Oracle Database Java Packages            VALID 

##More proof: 
select owner, status, count(*) 
from all_objects 
where object_type like '%JAVA%' 
group by owner, status;   

OWNER                STATUS  COUNT(*)
-------------------- ------- ---------- 
SYS                    VALID    21580 --> In 11GR2 approx. # of objects.
EXFSYS                 VALID       48 

select role from dba_roles where role like '%JAVA%'; 

6 rows selected.  -->  in 11GR2 # of roles.

select owner,status, count(status) 
from dba_objects 
group by owner,status 
order by 1,2; 

OWNER                          STATUS  COUNT(STATUS) 
------------------------------ ------- ------------- 
APEX_040100                    VALID            3341 
APPQOSSYS                      VALID               5 
DBSNMP                         VALID              55 
ERUNBK11                       VALID               5 
EXFSYS                         VALID             191 
FLOWS_FILES                    VALID              13 
ORACLE_OCM                     VALID               8 
OUTLN                          VALID              10 
PUBLIC                         VALID           24187 
SYS                            VALID           31674 
SYSTEM                         VALID             563 
XDB                            VALID            1658 

## If needed consider utlrp to see if recompile is helpful.  


As always Happy reading. Mathijs

First encounters of the ACFS kind


This weeks challenge has become to set up ACFS on two new to be implemented Real Application Clusters. As with any tooling first question will be , what is your goal if you want to start using it. My purpose is to start using ACFS as a shared mountpoint between the servers, nodes in the cluster in order to have each instance do its logging (ADR), audit and listener log  on that mount.

This blog will tell you about my setting it up,  the  issues i came across and the way it is implemented in a working way.

To share the plot of the story:  After implementing I  had the issue that  right after cluster reboot all services where launched automatiocally BUT  the instances would NOT start automatically. In the end i ended up with ACFS being defined as  a shared home for an Oracle RDBMS installation. And it worked/works after cluster reboot  cause all resources are happy and online now.

Continue reading