Friday, June 14, 2019

CRS-2730: Resource <...> depends on resource 'ora.net1.network'

The situation :

There is an Oracle GI 12.2 on two nodes.

We successfully added new public and administrative networks to all nodes (created vips, scan, scan_listeners etc.).

We migrated all resources to it (database and asm instances).

We need to stop clean up all TCP/IP addresses of the network 1 (default client's network used during GI installation).


So, you need to do the following :
1. Stop all and remove all listener(s) of network 1
2. Stop and remove scan resources of network 1
3. Stop and remove vips of network 1 (this will require higher privileges)
4. Stop network 1 (this will require higher privileges and you'll have to stop dependent resources)
5. Remove network 1

At the step 4 you can encounter into errors like :
- CRS-2730: Resource 'ora.qosmserver' depends on resource 'ora.net1.network' ;
- CRS-2730: Resource 'ora.ons' depends on resource 'ora.net1.network';
- CRS-2730: Resource 'ora.cvu' depends on resource 'ora.net1.network'

Use the following to reassign Grid dependencies to other network (network 2). Use it with caution (they are unsupported by Oracle).
# crsctl modify resource ora.ons -attr "START_DEPENDENCIES=hard(ora.net2.network) pullup(ora.net2.network)" -unsupported
# crsctl modify resource ora.ons -attr "STOP_DEPENDENCIES=hard(intermediate:ora.net2.network)" -unsupported
# crsctl modify resource ora.cvu -attr "START_DEPENDENCIES=hard(ora.net2.network) pullup(ora.net2.network)" -unsupported
# crsctl modify resource ora.cvu -attr "STOP_DEPENDENCIES=hard(intermediate:ora.net2.network)" -unsupported
# crsctl modify resource ora.qosmserver -attr "STOP_DEPENDENCIES=hard(intermediate:ora.net2.network)" -unsupported
# crsctl modify resource ora.qosmserver -attr "START_DEPENDENCIES=hard(ora.net2.network) pullup(ora.net2.network)" -unsupported

Final statement will be successful :

# srvctl remove network -netnum 1

Aftermath run early stopped resources (if needed) :
$ onsctl start
$ srvctl start cvu
$ srvctl start qosmserver

Check that everything works.

Good Luck !

Thursday, May 30, 2019

Create database link between Oracle databases with non-supported client/server connectivity

Lets consider two databases - db_a (Oracle 12.2) and db_b (Oracle 9.2). The way of connection is from A to B. I hadn't managed to connect from B to A using below steps. If you know how, please, share in the comments.

We need proxy database in order to organize supported cascading database links. In my case it was 11.2 database (let name it proxy_db).

What we need to do ? The action plan as in the following :

1. Create empty schema proxy_user in the proxy_db. 

2. Create database link proxy_to_db_b connecting newly created schema (1) to the schema in the target (9.2) database. 

3. Create private synonyms using database link (2) for all (or for all needed) objects of schema of the target (9.2) database.

4. Create database link db_a_to_proxy_db in the source (12.2) database. 

Now you can address tables via table_name@db_a_to_proxy_db clause. BUT, if you're using database links to target (9.2) database inside PL/SQL, you need to do more, as in the following, otherwise you'll not be able to compile your PL/SQL objects.

5. Create empty schema (metadata only and needed PL/SQL objects) target_user in the source database 12.2 (db_a).
6. Create database link (in source 12.2 database) from app schema to schema from item (5) using database link name as in item (2) (proxy_to_db_b). 

Now you will be able to modify and compile PL/SQL which depends (via db_link) on objects in the destination (9.2) database.

Look at My Oracle Support note 453754.1 for additional information. This document also says you
you can use dynamic SQL inside PL/SQL to work around (the database link is not resolved at compile time but at run time).

Good Luck

Tuesday, February 12, 2019

Random number inside sed substitution - example

Let's take an example sql file 1.sql, which contains only one line :

# cat 1.sql
  
CREATE BITMAP INDEX "USER"."xxx_A_xxx_xxx_xxx_ELEMENT_" ON "USER"."A_USER-2004" ("XXX")


We need to replace _ELEMENT_ to number value. Here is one of the ways to do it :


# sed "s/_ELEMENT_/_$RANDOM/ ; s/.*/echo '&'/e" 1.sql
   CREATE BITMAP INDEX "USER"."xxx_A_xxx_xxx_xxx_27237" ON "USER"."A_USER-2004" ("XXX")


Good Luck !

P.S. It seems the sed's info page is quite misleading about substitution flag /e... ;-)

Thursday, January 31, 2019

How to get input and make output to the same file on Linux

There can be several options. Suppose we have text file a, filled up with literal 'a', and our objection is change its contents to 'b', in other words replace every 'a' to 'b'

1. Using GNU sed to edit file in place (with backup of file a applying extension [SUFFIX] to name):

# sed -i[SUFFIX] 's/a/b/g' a

or without backup of file a :

# sed -i 's/a/b/g' a

P.S. On Solaris, it's possible to use gsed instead of sed

2. Using interim file :

# sed 's/a/b/g' a > interim_file && mv interim_file a

3. Playing with file descriptors. One possible variant is :

# exec 3<>a && sed 's/a/b/g' a >&3 && exec 3<&-

4. Using shell process substitution :

# sed 's/a/b/g' a > >(sleep 1 && cat > a)

Sleep is necessary to make some interval after sed finish its work

5. Many other alternatives, using ed, cat&echo etc.

Good Luck !

Tuesday, November 6, 2018

RMAN-12008: could not locate backup piece; BUT file actually exists

Hi !

If you get this error during restoration operation of your DB and mentioned backuppiece is actually on the place and it's not corrupted, then try to disable cluster_database parameter (alter system set cluster_database=false or reset it) and repeat failed operation.

Good Luck !!!

Thursday, September 27, 2018

How to decrypt db_link password in Oracle 10g

Here is example PL/SQL script. Password for db_link was been gotten from ku$_dblink_view.

set serveroutput on
declare
 db_link_password varchar2(100);
begin
 db_link_password := '';

 dbms_output.put_line ('Plain password: ' || utl_raw.cast_to_varchar2 ( dbms_crypto.decrypt ( substr (db_link_password, 19) , dbms_crypto.des_cbc_pkcs5 , substr (db_link_password, 3, 16) ) ) );
end;
/


Good Luck !

Tuesday, August 21, 2018

How to connect to Oracle 9i from SQL Developer 18

If you encounter to the following message ...

An error was encountered performing the requested operation:

ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region  not found
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.
Vendor code 604

... you can fix it by adding

AddVMOption -Duser.language=en
AddVMOption -Duser.region=us
AddVMOption -Duser.timezone="EET"

to your /ide/bin/ide.conf file.

Good Luck !!!