DBA survival BLOG

DBA stuff and Oracle Data Guard

About Ludovico

Ludovico is a member of the Oracle Database High Availability (HA), Scalability & Maximum Availability Architecture (MAA) Product Management team in Oracle. He focuses on Oracle Data Guard, Flashback technologies, and Cloud MAA.

Bash tips & tricks [ep. 1]: Deal with personal accounts and file permissions

Posted on March 16, 2016 by Ludovico

This is the first episode of a mini series of Bash tips for Linux (in case you are wondering, yes, they are respectively my favorite shell and my favorite OS 😉 ).

Episode 1: Deal with personal accounts and file permissions
Episode 2: Have a smart environment for personal accounts
Epidode 3: Colour your terminal!
Episode 4: Use logging levels
Episode 5: Write the output to a logfile
Episode 6: Check the exit code
Episode 7: Cleanup on EXIT with a trap

Description:

Nowadays it is mandatory at many companies to log in on Linux servers with a personal account (either integrated with LDAP, kerberos or whatelse) to comply with strict auditing rules.

I need to be sure that I have an environment where my modifications do not conflict with my colleagues environment.

BAD:

-bash-4.1$ id
uid=20928(ludo) gid=200(dba) groups=200(dba)
-bash-4.1$ ls -lia
total 8
8196 drwxrwxr-x   2 oracle dba  4096 Mar 15 15:14 .
   2 drwxrwxrwt. 14 root   root 4096 Mar 15 15:15 ..
-bash-4.1$ vi script.sh
... edit here...
-bash-4.1$ ls -l
total 4
-rw-r--r-- 1 ludo  dba 8 Mar 15 15:15 script.sh
-bash-4.1$

-bash-4.1$ id

uid=20928(ludo) gid=200(dba) groups=200(dba)

-bash-4.1$ ls -lia

total 8

8196 drwxrwxr-x 2 oracle dba 4096 Mar 15 15:14 .

2 drwxrwxrwt. 14 root root 4096 Mar 15 15:15 ..

-bash-4.1$ vi script.sh

... edit here...

-bash-4.1$ ls -l

total 4

-rw-r--r-- 1 ludo dba 8 Mar 15 15:15 script.sh

-bash-4.1$

the script has been created by me, but my colleagues may need to modify it! So I need to change the ownership:

$ chown oracle:dba script.sh
chown: changing ownership of `script.sh': Operation not permitted
$

$ chown oracle:dba script.sh

chown: changing ownership of `script.sh': Operation not permitted

But I can only change the permissions:

$ chmod 775 script.sh
$

1 2	$ chmod 775 script.sh $

If I really want to change the owner, I have to ask to someone that has root privileges or delete the file with my account and create it with the correct one (oracle or something else).

GOOD:

Set the setgid bit at the directory level
Define an alias for my favorite editor that use sudoedit instead:

$ chmod 2751 .
$ ls -lia
total 4
8196 drwxr-s--x 2 oracle dba  4096 Mar 15 15:26 .
$ alias vi='SUDO_EDITOR=/usr/bin/vim sudoedit -u oracle '
$ vi script.sh
[sudo] password for ludo:
... edit here ...
$ ls -l script.sh
total 8
-rw-r--r-- 1 oracle dba 6 Mar 15 15:24 script.sh
$

$ chmod 2751 .

$ ls -lia

total 4

8196 drwxr-s--x 2 oracle dba 4096 Mar 15 15:26 .

$ alias vi='SUDO_EDITOR=/usr/bin/vim sudoedit -u oracle '

$ vi script.sh

[sudo] password for ludo:

... edit here ...

$ ls -l script.sh

total 8

-rw-r--r-- 1 oracle dba 6 Mar 15 15:24 script.sh

In case I need to modify other files with MY account, I can either use the full path (/usr/bin/vim) or define another alias:

alias vime="/usr/bin/vim"

1	alias vime="/usr/bin/vim"

How cold incremental recovery saved me once

Posted on March 15, 2016 by Ludovico

UPDATE: In the original version I was missing a few keywords: “incremental level 0” for the base backup and “resetlogs” at the database open. Thanks Gregorz for your comments.

Sorry for this “memories” post, but the technical solution at the end is worth the read, I hope 😉

Back in 2010, I was in charge of a quite complex project and faced some difficulties that led me to recover a database in a different manner. A few years have passed, but I used again the same procedure many times with full satisfaction… I think it’s worth to publish it now.

But first, let me introduce the project details and the problem.

Scope of the project

Transport a >1TB RAC database from AIX 5 on P6 to AIX 6 on P7, from a third-party datacenter in southern Italy to our main datacenter in northern Italy.
The Database featured >1000 datafiles and a huge table (800GB) partitioned by range and sub-partitioned by list (or the opposite, can’t remember).

Challenges

For budget containement, the project owner asked to avoid the use of HACMP (and thus, avoid the use of shared JFS2). I decided then to take the risk and migrate from JFS2 to ASM.

In order to avoid a few platform-related ASM bugs, I also had to upgrade from Oracle 10.2.0.3 to Oracle 10.2.0.4.

Constraints

I had no access to the source database that was 800km far from our datacenter, and I was granted only to ask for RMAN backups.

The total time of service disruption accepted was quite short (<30 minutes) considering the size and the distance of the database, and there was no direct connectivity between the sites (for political reasons).

Globally, the network throughput for sharing files over ftp was very poor.

First solution

This kind of move was very common to me, and because I was not grated to ask for a temporary Data Guard configuration, the easy solution for me was to ask:

1 – one RMAN ONLINE full backup physically sent on disk

2 – many RMAN archive backups sent over network (via ftp)

Then, on my side, restore the full backup, recover the archives sent over time and, at the date X, ask a final archive backup, ask to close the db and send the online redo logs to do a complete recovery on my side, then startup open upgrade.

Problem

I did a first “dry run” open resetlogs in order to test the procedure and make it faster, and also asked to test the application pointing to the destination database.

The very bad surprise was that the source database was doing a huge amount of nologging inserts leading to monster index corruptions after the recovery on the destination database.

ORA-26040: Data block was loaded using the NOLOGGING option

1	ORA-26040: Data block was loaded using the NOLOGGING option

According to the current database maintainer, setting the force logging on the source database was NOT an option because the SAN was not able to cope with the high redo rates.

Solution

By knowing the Oracle recovery mechanisms, I have proposed to the remote maintainer to change the recovery strategy, despite this solution was not clearly stated in the Oracle documentation:

1 – Take a first online incremental backup from the begin scn of the base full backup (thank God block change tracking was in place) and send it physically over disk

2 – Take other smaller online incremental backups, send them over ftp and apply them on the destination with “noredo”

3 – At the date X, shutdown the source, mount it and take a last incremental in mount state

4 – recover noredo the last incremental and open resetlogs the database.

According to the documentation, the “cold incremental strategy” applies if you take “cold full backups”. But from a technical point of view, taking a cold incremental and recovering it on top of a fuzzy online backup this is 100% equivalent of taking a full consistent backup in mount state.
Because all the blocks are consistent to a specific SCN, there are no fuzzy datafiles: they are recovered from incremental taken from a mounted database! This allows to do incremental recovery and open the databases without applying any single archived log and by shutting down the database only once.

Technical steps

First, take a full ONLINE backup on the source:

-- SOURCE
SQL> alter database backup controlfile to '/tmp/source/ludo.cf' reuse;

Database altered.

SQL> exit
$ rman target /
RMAN> backup incremental level 0 database as compressed backupset format '/tmp/source/%U';

-- SOURCE

SQL> alter database backup controlfile to '/tmp/source/ludo.cf' reuse;

Database altered.

SQL> exit

$ rman target /

RMAN> backup incremental level 0 database as compressed backupset format '/tmp/source/%U';

# SOURCE
scp -rp /tmp/source/ destsrv:/tmp/dest/
ludo.cf              100% |*************************************| 40944 KB    00:00
...

# SOURCE

scp -rp /tmp/source/ destsrv:/tmp/dest/

ludo.cf 100% |*************************************| 40944 KB 00:00

...

Then restore it on the destination (with no recovery):

# DEST
RMAN> restore controlfile from '/tmp/ludo.cf';

Starting restore at 11-AUG-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1058 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/.../control01.ctl
output file name=/.../control02.ctl
Finished restore at 11-AUG-15

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> catalog start with '/tmp/dest/';
...
RMAN> run
2> {
3> set newname for database to '+DATA';
4>
5> restore database;
6> }
...
Finished restore at 11-AUG-15
RMAN>

# DEST

RMAN> restore controlfile from '/tmp/ludo.cf';

Starting restore at 11-AUG-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1058 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=/.../control01.ctl

output file name=/.../control02.ctl

Finished restore at 11-AUG-15

RMAN> alter database mount;

Statement processed

released channel: ORA_DISK_1

RMAN> catalog start with '/tmp/dest/';

...

RMAN> run

2> {

3> set newname for database to '+DATA';

5> restore database;

6> }

...

Finished restore at 11-AUG-15

RMAN>

Then, run a COLD incremental backup on the source:

-- SOURCE
SQL> shutdown immediate;
...
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
...
Database mounted.
SQL> exit
$ rman target /
RMAN>  BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 
2> CUMULATIVE DATABASE format '/tmp/source/incr%U';
...
Finished backup at 11-AUG-15
RMAN> exit
$ scp -rp /tmp/source/incr* destsrv:/tmp/dest/

-- SOURCE

SQL> shutdown immediate;

...

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

...

Database mounted.

SQL> exit

$ rman target /

RMAN> BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1

2> CUMULATIVE DATABASE format '/tmp/source/incr%U';

...

Finished backup at 11-AUG-15

RMAN> exit

$ scp -rp /tmp/source/incr* destsrv:/tmp/dest/

And run the incremental recovery on the source (without redo):

# DEST
RMAN> catalog start with '/tmp/dest/incr';
...
RMAN> run {
2> recover database noredo;
3> }
...
channel ORA_DISK_1: starting incremental datafile backup set restore
...
Finished recover at 11-AUG-15
RMAN> exit
$ sqlplus / as sysdba
...
SQL> alter database disable block change tracking;
Database altered.
SQL> alter database flashback off;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> create restore point PREUPG guarantee flashback database;
Restore point created.
SQL> -- open resetlogs can be avoided if I copy the online redo logs
SQL> alter database open resetlogs upgrade;
Database altered.
...
-- run catupgrd here

# DEST

RMAN> catalog start with '/tmp/dest/incr';

...

RMAN> run {

2> recover database noredo;

3> }

...

channel ORA_DISK_1: starting incremental datafile backup set restore

...

Finished recover at 11-AUG-15

RMAN> exit

$ sqlplus / as sysdba

...

SQL> alter database disable block change tracking;

Database altered.

SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> create restore point PREUPG guarantee flashback database;

Restore point created.

SQL> -- open resetlogs can be avoided if I copy the online redo logs

SQL> alter database open resetlogs upgrade;

Database altered.

...

-- run catupgrd here

That’s all!

This solution gave me the opportunity to move physically the whole >1TB nologging database from one region to another one with a minimal service disruption and without touching at all the source database.

I used it many times later on, even for bigger databases and on several platforms (yes, also Windows, sigh), it works like a charm.

HTH

—

Ludovico

Getting the DBID and Incarnation from the RMAN Catalog

Posted on February 15, 2016 by Ludovico

Using the RMAN catalog is an option. There is a long discussion between DBAs on whether should you use the catalog or not.

But because I like (a lot) the RMAN catalog and I generally use it, I assume that most of you do it 😉

When you want to restore from the RMAN catalog, you need to get the DBID of the database you want to restore and, sometimes, also the incarnation key.

The DBID is used to identify the database you want to restore. The DBID is different for every newly created / duplicated database, but beware that if you duplicate your database manually (using restore/recover), you actually need to change your DBID using the nid tool, otherwise you will end up by having more than one database registered in the catalog with the very same DBID. This is evil! The DB_NAME is also something that you may want to make sure is unique within your database farm.

The Incarnation Key changes whenever you do an “open resetlogs”, following for example a flashback database, an incomplete recovery, or just a “open resetlogs” without any specific need.

In the image, you can see that you may want to restore to a point in time after the open resetlogs (blue incarnation) or before it (red incarnation). Depending on which one you need to restore, you may need to use the command RESET DATABASE TO INCARNATION.

https://docs.oracle.com/database/121/RCMRF/rcmsynta2007.htm#RCMRF148

If you have a dynamic and big environment, you probably script your restores procedures, that’s why getting the DBID and incarnation key using the RMAN commands may be more complex than just querying the catalog using sqlplus.

How do I get the history of my database incarnations?

You can get it easily for all your databases using the handy hierarchical queries on the RMAN catalog (db names and ids are obfuscated for obvious reasons):

SQL> SELECT lpad(' ',2*(level-1))
  || TO_CHAR(DBINC_KEY) AS DBINC_KEY,
  db_key,
  db_name,
  TO_CHAR(reset_time,'YYYY-MM-DD HH24:MI:SS'),
  dbinc_status
FROM rman.dbinc
  START WITH PARENT_DBINC_KEY IS NULL
  CONNECT BY prior DBINC_KEY   = PARENT_DBINC_KEY ;

DBINC_KEY                     DB_KEY DB_NAME    TO_CHAR(RESET_TIME, DBINC_ST
------------------------- ---------- ---------- ------------------- --------
356247416                  356247380 A9EE272A   2011-09-24 18:22:58 PARENT
  356247387                356247380 A9EE272A   2012-10-24 08:41:41 PARENT
    1149458631             356247380 A9EE272A   2014-10-10 08:30:57 CURRENT
360319357                  360319322 F5FD787F   2011-10-14 15:39:19 PARENT
  360319323                360319322 F5FD787F   2012-11-08 18:57:26 PARENT
    547928008              360319322 F5FD787F   2013-09-10 10:57:44 PARENT
      576592237            360319322 F5FD787F   2013-11-20 14:54:05 ORPHAN
      576613820            360319322 F5FD787F   2013-11-20 15:57:03 ORPHAN
      584503796            360319322 F5FD787F   2013-11-27 13:57:53 CURRENT
364099232                  364099231 25E64A7F   2012-11-20 08:01:49 PARENT
  415031968                364099231 25E64A7F   2013-02-15 12:16:15 PARENT
    456099512              364099231 25E64A7F   2013-05-03 12:19:52 CURRENT
366065362                  366065336 3AE45141   2011-09-24 18:22:58 PARENT
  366065337                366065336 3AE45141   2012-11-26 17:14:14 CURRENT
394067322                  394067321 C34FFA7E   2013-01-10 17:18:11 CURRENT
402469086                  402469073 D164DDB8   2011-09-24 18:22:58 PARENT
  402469074                402469073 D164DDB8   2013-01-29 11:20:19 CURRENT
410147332                  410147283 27984513   2011-09-24 18:22:58 PARENT
  410147284                410147283 27984513   2013-02-08 11:12:38 CURRENT
...
...

SQL> SELECT lpad(' ',2*(level-1))

|| TO_CHAR(DBINC_KEY) AS DBINC_KEY,

db_key,

db_name,

TO_CHAR(reset_time,'YYYY-MM-DD HH24:MI:SS'),

dbinc_status

FROM rman.dbinc

START WITH PARENT_DBINC_KEY IS NULL

CONNECT BY prior DBINC_KEY = PARENT_DBINC_KEY ;

DBINC_KEY DB_KEY DB_NAME TO_CHAR(RESET_TIME, DBINC_ST

------------------------- ---------- ---------- ------------------- --------

356247416 356247380 A9EE272A 2011-09-24 18:22:58 PARENT

356247387 356247380 A9EE272A 2012-10-24 08:41:41 PARENT

1149458631 356247380 A9EE272A 2014-10-10 08:30:57 CURRENT

360319357 360319322 F5FD787F 2011-10-14 15:39:19 PARENT

360319323 360319322 F5FD787F 2012-11-08 18:57:26 PARENT

547928008 360319322 F5FD787F 2013-09-10 10:57:44 PARENT

576592237 360319322 F5FD787F 2013-11-20 14:54:05 ORPHAN

576613820 360319322 F5FD787F 2013-11-20 15:57:03 ORPHAN

584503796 360319322 F5FD787F 2013-11-27 13:57:53 CURRENT

364099232 364099231 25E64A7F 2012-11-20 08:01:49 PARENT

415031968 364099231 25E64A7F 2013-02-15 12:16:15 PARENT

456099512 364099231 25E64A7F 2013-05-03 12:19:52 CURRENT

366065362 366065336 3AE45141 2011-09-24 18:22:58 PARENT

366065337 366065336 3AE45141 2012-11-26 17:14:14 CURRENT

394067322 394067321 C34FFA7E 2013-01-10 17:18:11 CURRENT

402469086 402469073 D164DDB8 2011-09-24 18:22:58 PARENT

402469074 402469073 D164DDB8 2013-01-29 11:20:19 CURRENT

410147332 410147283 27984513 2011-09-24 18:22:58 PARENT

410147284 410147283 27984513 2013-02-08 11:12:38 CURRENT

...

What about getting the correct DBID/DBINC_KEY pair for a specific database/time?

You can get the time windows for each incarnation using the lead() analytical function:

SQL> WITH dbids AS
  (SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,
    dbinc.db_key,
    dbinc.db_name,
    dbinc.reset_time,
    dbinc.dbinc_status,
    db.db_id
  FROM rman.dbinc dbinc
  JOIN rman.db db
  ON ( 
  dbinc.db_key   =db.db_key)
  )
select * from (
SELECT DBINC_KEY,
  db_name,
  db_id,
  reset_time,
  nvl(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset
FROM dbids
)
ORDER BY db_name ,
  reset_time ;  

DBINC_KEY                 DBNAME          DB_ID RESET_TIME          NEXTRESET
------------------------- ---------- ---------- ------------------- -------------------
1173852671                1DF63C30   2507085371 2014-07-07 05:38:47 2015-01-16 07:29:01
1173852635                1DF63C30   2507085371 2015-01-16 07:29:01 2015-02-27 16:25:13
1244346785                1DF63C30   2531796824 2015-02-27 16:25:13 2015-02-27 16:25:13
1281775847                1DF63C30   2541221473 2015-02-27 16:25:13 2015-02-27 16:25:13
1233975755                1DF63C30   2528008262 2015-02-27 16:25:13 2015-02-27 16:25:13
1220896058                1DF63C30   2523244390 2015-02-27 16:25:13 2015-03-16 16:06:00
1188550385                1DF63C30   2507085371 2015-03-16 16:06:00 2015-07-17 08:06:00
1220896028                1DF63C30   2523244390 2015-07-17 08:06:00 2015-09-10 11:23:53
1233975725                1DF63C30   2528008262 2015-09-10 11:23:53 2015-10-23 07:46:34
1244346755                1DF63C30   2531796824 2015-10-23 07:46:34 2016-02-08 09:44:03
1281775817                1DF63C30   2541221473 2016-02-08 09:44:03 2016-02-15 10:13:49
1201139592                1D0776F6   2025503263 2014-07-07 05:38:47 2015-05-04 17:08:50
1201139578                1D0776F6   2025503263 2015-05-04 17:08:50 2015-06-02 08:48:07
1213295265                1D0776F6   2029287211 2015-06-02 08:48:07 2015-06-02 08:48:07
1256000477                1D0776F6   2044568865 2015-06-02 08:48:07 2015-06-02 08:48:07
1235940868                1D0776F6   2037421528 2015-06-02 08:48:07 2015-06-17 12:14:38
1213295230                1D0776F6   2029287211 2015-06-17 12:14:38 2015-09-18 15:46:34
1235940852                1D0776F6   2037421528 2015-09-18 15:46:34 2015-12-08 09:08:52
1256000461                1D0776F6   2044568865 2015-12-08 09:08:52 2016-02-15 10:13:49
1173653066                2D828C2C   1656607497 2014-07-07 05:38:47 2015-01-15 14:06:04
1173653052                2D828C2C   1656607497 2015-01-15 14:06:04 2015-06-02 08:48:07
1247872446                2D828C2C   1682603029 2015-06-02 08:48:07 2015-06-02 08:48:07
1218354231                2D828C2C   1671898993 2015-06-02 08:48:07 2015-06-02 08:48:07
1278227063                2D828C2C   1690479985 2015-06-02 08:48:07 2015-06-02 08:48:07
1219084145                2D828C2C   1672155073 2015-06-02 08:48:07 2015-06-02 08:48:07
1228714578                2D828C2C   1675699280 2015-06-02 08:48:07 2015-06-02 08:48:07
1211451469                2D828C2C   1669565762 2015-06-02 08:48:07 2015-06-02 08:48:07
1235422982                2D828C2C   1678113471 2015-06-02 08:48:07 2015-06-02 08:48:07
1228713810                2D828C2C   1675697673 2015-06-02 08:48:07 2015-06-02 08:48:07
1240749487                2D828C2C   1680107003 2015-06-02 08:48:07 2015-06-02 08:48:07
1255743496                2D828C2C   1685361979 2015-06-02 08:48:07 2015-06-10 13:37:08
1211451453                2D828C2C   1669565762 2015-06-10 13:37:08 2015-07-06 13:44:20
1218354215                2D828C2C   1671898993 2015-07-06 13:44:20 2015-07-09 12:52:19
1219084129                2D828C2C   1672155073 2015-07-09 12:52:19 2015-08-19 12:55:40
1228713794                2D828C2C   1675697673 2015-08-19 12:55:40 2015-08-19 13:22:27
1228714562                2D828C2C   1675699280 2015-08-19 13:22:27 2015-09-16 11:58:58
1235422966                2D828C2C   1678113471 2015-09-16 11:58:58 2015-10-08 13:44:29
1240749471                2D828C2C   1680107003 2015-10-08 13:44:29 2015-11-06 11:04:55
1247872430                2D828C2C   1682603029 2015-11-06 11:04:55 2015-12-07 09:27:27
1255743480                2D828C2C   1685361979 2015-12-07 09:27:27 2016-02-04 15:07:29
1278227047                2D828C2C   1690479985 2016-02-04 15:07:29 2016-02-15 10:13:49

SQL> WITH dbids AS

(SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,

dbinc.db_key,

dbinc.db_name,

dbinc.reset_time,

dbinc.dbinc_status,

db.db_id

FROM rman.dbinc dbinc

JOIN rman.db db

ON (

dbinc.db_key =db.db_key)

)

select * from (

SELECT DBINC_KEY,

db_name,

db_id,

reset_time,

nvl(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset

FROM dbids

)

ORDER BY db_name ,

reset_time ;

DBINC_KEY DBNAME DB_ID RESET_TIME NEXTRESET

------------------------- ---------- ---------- ------------------- -------------------

1173852671 1DF63C30 2507085371 2014-07-07 05:38:47 2015-01-16 07:29:01

1173852635 1DF63C30 2507085371 2015-01-16 07:29:01 2015-02-27 16:25:13

1244346785 1DF63C30 2531796824 2015-02-27 16:25:13 2015-02-27 16:25:13

1281775847 1DF63C30 2541221473 2015-02-27 16:25:13 2015-02-27 16:25:13

1233975755 1DF63C30 2528008262 2015-02-27 16:25:13 2015-02-27 16:25:13

1220896058 1DF63C30 2523244390 2015-02-27 16:25:13 2015-03-16 16:06:00

1188550385 1DF63C30 2507085371 2015-03-16 16:06:00 2015-07-17 08:06:00

1220896028 1DF63C30 2523244390 2015-07-17 08:06:00 2015-09-10 11:23:53

1233975725 1DF63C30 2528008262 2015-09-10 11:23:53 2015-10-23 07:46:34

1244346755 1DF63C30 2531796824 2015-10-23 07:46:34 2016-02-08 09:44:03

1281775817 1DF63C30 2541221473 2016-02-08 09:44:03 2016-02-15 10:13:49

1201139592 1D0776F6 2025503263 2014-07-07 05:38:47 2015-05-04 17:08:50

1201139578 1D0776F6 2025503263 2015-05-04 17:08:50 2015-06-02 08:48:07

1213295265 1D0776F6 2029287211 2015-06-02 08:48:07 2015-06-02 08:48:07

1256000477 1D0776F6 2044568865 2015-06-02 08:48:07 2015-06-02 08:48:07

1235940868 1D0776F6 2037421528 2015-06-02 08:48:07 2015-06-17 12:14:38

1213295230 1D0776F6 2029287211 2015-06-17 12:14:38 2015-09-18 15:46:34

1235940852 1D0776F6 2037421528 2015-09-18 15:46:34 2015-12-08 09:08:52

1256000461 1D0776F6 2044568865 2015-12-08 09:08:52 2016-02-15 10:13:49

1173653066 2D828C2C 1656607497 2014-07-07 05:38:47 2015-01-15 14:06:04

1173653052 2D828C2C 1656607497 2015-01-15 14:06:04 2015-06-02 08:48:07

1247872446 2D828C2C 1682603029 2015-06-02 08:48:07 2015-06-02 08:48:07

1218354231 2D828C2C 1671898993 2015-06-02 08:48:07 2015-06-02 08:48:07

1278227063 2D828C2C 1690479985 2015-06-02 08:48:07 2015-06-02 08:48:07

1219084145 2D828C2C 1672155073 2015-06-02 08:48:07 2015-06-02 08:48:07

1228714578 2D828C2C 1675699280 2015-06-02 08:48:07 2015-06-02 08:48:07

1211451469 2D828C2C 1669565762 2015-06-02 08:48:07 2015-06-02 08:48:07

1235422982 2D828C2C 1678113471 2015-06-02 08:48:07 2015-06-02 08:48:07

1228713810 2D828C2C 1675697673 2015-06-02 08:48:07 2015-06-02 08:48:07

1240749487 2D828C2C 1680107003 2015-06-02 08:48:07 2015-06-02 08:48:07

1255743496 2D828C2C 1685361979 2015-06-02 08:48:07 2015-06-10 13:37:08

1211451453 2D828C2C 1669565762 2015-06-10 13:37:08 2015-07-06 13:44:20

1218354215 2D828C2C 1671898993 2015-07-06 13:44:20 2015-07-09 12:52:19

1219084129 2D828C2C 1672155073 2015-07-09 12:52:19 2015-08-19 12:55:40

1228713794 2D828C2C 1675697673 2015-08-19 12:55:40 2015-08-19 13:22:27

1228714562 2D828C2C 1675699280 2015-08-19 13:22:27 2015-09-16 11:58:58

1235422966 2D828C2C 1678113471 2015-09-16 11:58:58 2015-10-08 13:44:29

1240749471 2D828C2C 1680107003 2015-10-08 13:44:29 2015-11-06 11:04:55

1247872430 2D828C2C 1682603029 2015-11-06 11:04:55 2015-12-07 09:27:27

1255743480 2D828C2C 1685361979 2015-12-07 09:27:27 2016-02-04 15:07:29

1278227047 2D828C2C 1690479985 2016-02-04 15:07:29 2016-02-15 10:13:49

With this query, you can see that every incarnation has a reset time and a “next reset time”.

It’s easy then to get exactly what you need by adding a couple of where clauses:

SQL> WITH dbids AS
  (SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,
    dbinc.db_key,
    dbinc.db_name,
    dbinc.reset_time,
    dbinc.dbinc_status,
    db.db_id
  FROM rman.dbinc dbinc
  JOIN rman.db db
  ON ( --dbinc.dbinc_key=db.CURR_DBINC_KEY
    --AND
    dbinc.db_key =db.db_key)
  )
SELECT *
FROM
  (SELECT DBINC_KEY,
    db_name,
    db_id,
    reset_time,
    NVL(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset
  FROM dbids
  )
WHERE TO_DATE ('2016-01-20 00:00:00','YYYY-MM-DD HH24:MI;SS') BETWEEN reset_time AND next_reset
AND db_name='1465419F'
ORDER BY db_name ,
  reset_time ; 

DBINC_KEY                 DB_NAME         DB_ID RESET_TIME          NEXT_RESET
------------------------- ---------- ---------- ------------------- -------------------
1256014297                1465419F   1048383773 2015-12-08 11:03:55 2016-02-08 07:55:05

SQL> WITH dbids AS

(SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,

dbinc.db_key,

dbinc.db_name,

dbinc.reset_time,

dbinc.dbinc_status,

db.db_id

FROM rman.dbinc dbinc

JOIN rman.db db

ON ( --dbinc.dbinc_key=db.CURR_DBINC_KEY

--AND

dbinc.db_key =db.db_key)

)

SELECT *

FROM

(SELECT DBINC_KEY,

db_name,

db_id,

reset_time,

NVL(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset

FROM dbids

)

WHERE TO_DATE ('2016-01-20 00:00:00','YYYY-MM-DD HH24:MI;SS') BETWEEN reset_time AND next_reset

AND db_name='1465419F'

ORDER BY db_name ,

reset_time ;

DBINC_KEY DB_NAME DB_ID RESET_TIME NEXT_RESET

------------------------- ---------- ---------- ------------------- -------------------

1256014297 1465419F 1048383773 2015-12-08 11:03:55 2016-02-08 07:55:05

So, if I need to restore the database 1465419F until time 2016-01-20 00:00:00, i need to set DBID=1048383773 and reset the database to incarnation 1256014297.

Cheers

—

Ludo

2015 in numbers…

Posted on January 31, 2016 by Ludovico

I am spending good times since I have moved in Switzerland 3 years ago. 2015 has been as good as 2014. Now that January ends, I am officially late in publishing this information, but it’s MY blog, so who cares? 😛

A few numbers:

25 blog posts (+1)
~52000 page views (+40%), ~43000 visits (+48%)
Speaker at 2 major conferences (#C15LV, #UKOUG_TECH15)
Speaker at 2 Trivadis internal conferences
Speaker at 1 local user group event
Delegate of EOUC at DOAG 2015
A total of 14 public speeches (same as 2014)
I’ve been elected RAC SIG Vice President
2 RAC Attack workshops organized
1 roundtable as organizer, 1 panel
2 T-shirt designed as gifts for 2 RAC Attack workshops
2 articles published
Launched the RAC SIG website and the new session agenda
Countless new friends and/or contacts

I hope that 2016 will be as good as 2015

Configuring the MySQL Database Plug-In for Oracle Enterprise Manager 12c

Posted on January 31, 2016 by Ludovico

I have blogged in the past about MySQL Enterprise Monitor 3.0 and I was quite happy at the very beginning, but after a while I have to admit that I was missing many of the Oracle Enterprise Manager 12c features.

In particular, MEM 3.0 does not have a usable database. In MEM all the tables are crypted and it is not possible to list, for example, all the targets monitored, nor it is possible via API or REST web services because MEM 3.0 lacks these features.

What makes EM12c a GREAT product comparing to MEM, are many features like blackouts, a usable command line interface (emcli), integrated reporting, scheduler, automatic groups… the list would be just huge.

Luckily, Oracle has officially released a MySQL plugin for EM12c, provided that the EM is at least in version 12.1.0.4.

So I’ve upgraded (a while ago) my customer’s EM12c to 12.1.0.5. and decided to try the plugin.

The first step is to download the last version of plugin for MySQL.

I can verify that you have the last version by going to

Setup -> Extensibility -> Self-Update -> Plugins:

The agent has been downloaded, but in order to make it available on the targets, I first need to deploy it on the management servers (2 OMSes in my case):

Check the plugin name and version:

Verify the prerequisites check (here I have one column per OMS):
2015-08-19 13_55_26-Deploy Plug-ins on Management Servers

Specify the credentials for the Management Repository:
Execute the deploy:

If everything went OK, I’m able to check the status of the deployment:

Now I can see that the plugin is correctly deployed on the OMSes, I can do the same for the agents:

I must select one by one the agents that run on the hosts where I have MySQL running. I may select all agents as well, but it’s better to be neat…

again, there are prerequisite checks and confirmations:

The plugin deployment went well:

Now I can run the target discovery on the agent:

But the discovery does not find my MySQL targets. What went wrong?

Each agent has a default list of “discovery modules” used for the discovery, but by default the MySQL one is not enabled after I install the plugin:

so it is necessary to activate it and deactivate the discovery modules I do not need:

Tada! at the next discovery, I have my target available:

The target name is automatically set to hostname:mysqlport:

as all discovered targets, I need to promote it to have it available for monitoring with EM12c:

The target is available, now I can use most of the EM12c features to monitor my MySQL environment.

HTH

—

Ludovico

Recording of “Rapid Home Provisioning” webinar for the RAC SIG

Posted on January 14, 2016 by Ludovico

Yesterday I have presented the Oracle Rapid Home Provisioning technology for the RAC SIG, you can find the recording on YouTube:

Cheers

—

Ludo

Rapid Home Provisioning

Posted on December 4, 2015 by Ludovico

In a few days I will give a presentation at UKOUG Tech15 about Rapid Home Provisioning, it will be the first time that I present this session in public.

I usually like to give the link to the material to my audience, so here we go:

Slides:

Demo:

Enjoy
—
Ludovico

Oracle Active Data Guard and Global Data Services in Action!

Posted on December 4, 2015 by Ludovico

In a few days I will give a presentation at UKOUG Tech15 about Global Data Services, it will be the first time that I present this session.

I usually like to give the link to the material to my audience, so here we go:

Credits

I have to give special credits to my colleague Robert Bialek. I’ve got a late confirmation for this session and my slide deck was not ready at all, so I have used a big part of his original work. Most of the content included in the slides has been created by Robert, not me. (Thank you for your help! :-))

Slides

Demo recording

Demo script

clear

function db {
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
}

function gsm {
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/gsmhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
}

db

echo "#### CURRENT CONFIGURATION: CLASSIC DATA GUARD, 3 DATABASES ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
show configuration
EOF
echo "next: GSM config"
read -p ""

gsm
echo "#### GSM CONFIGURATION ####"
echo "GDS COMMAND:
config"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
config
exit
EOF
echo "next: ADD GDSPOOL"
read -p ""


echo "#### ADD GDSPOOL ####"
echo "GDS COMMAND:
add gdspool -gdspool sales"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
add gdspool -gdspool sales
exit
EOF
echo "next: ADD BROKERCONFIG"
read -p ""


echo "#### ADD BROKERCONFIG ####"
echo "GDS COMMAND:
add brokerconfig -connect gsm02.trivadistraining.com:1521/oltp_de -pwd password1 -gdspool sales -region germany"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
add brokerconfig -connect gsm02.trivadistraining.com:1521/oltp_de -pwd password1 -gdspool sales -region germany
exit
EOF
echo "next: config databases"
read -p ""


echo "#### CONFIG DATABASES ####"
echo "GDS COMMAND:
config database"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
config database
exit
EOF
echo "next: modify databases"
read -p ""

echo "#### MODIFY DATABASES ####"
echo "GDS COMMAND: 
modify database -database oltp_ch1 -region switzerland
modify database -database oltp_ch2 -region switzerland
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
modify database -database oltp_ch1 -region switzerland
modify database -database oltp_ch2 -region switzerland
config database
exit
EOF
echo "next: add service read/write"
read -p ""


echo "#### ADD SERVICE R/W ####"
echo "GDS COMMAND: 
add service -gdspool sales -service gsales_rw -role primary -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover
start service -service gsales_rw
services"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
add service -gdspool sales -service gsales_rw -role primary -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover
start service -service gsales_rw
services
exit
EOF
echo "next: ADD SERVICE R/O"
read -p ""

echo "#### ADD SERVICE R/O ####"
echo "GDS COMMAND: 
add service -gdspool sales -service gsales_ro -role PHYSICAL_STANDBY -failover_primary -lag 20 -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover
start service -service gsales_ro
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
add service -gdspool sales -service gsales_ro -role PHYSICAL_STANDBY -failover_primary -lag 20 -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover
start service -service gsales_ro
services
exit
EOF
echo "next: stop apply ch1 (run cli_ro_short.sh first)"
read -p ""

db
echo "#### STOP APPLY DATA GUARD ON OLTP_CH1 ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
edit database oltp_ch1 set state='apply-off';
EOF
echo "next: gds services"
read -p ""


gsm
echo "#### GDS SERVICES ####"
echo "GDS COMMAND: 
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
services
exit
EOF
echo "next: stop apply ch2 (run cli_ro_short.sh first)"
read -p ""

db
echo "#### STOP APPLY DATA GUARD ON OLTP_CH2 ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
edit database oltp_ch2 set state='apply-off';
EOF
echo "next: gds services"
read -p ""

gsm
echo "#### GDS SERVICES ####"
echo "GDS COMMAND: 
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
services
exit
EOF
echo "next: gds services"
read -p ""

gsm
echo "#### GDS SERVICES ####"
echo "GDS COMMAND: 
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
services
exit
EOF
echo "next: start apply ch1  and ch2"
read -p ""

db
echo "#### START APPLY DATA GUARD ON OLTP_CH1 and OLTP_CH2 ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
edit database oltp_ch1 set state='apply-on';
EOF
echo "sleeping 5"
sleep 5
dgmgrl -echo sys/password1@oltp_de <<EOF
edit database oltp_ch2 set state='apply-on';
EOF
echo "next: gds services"
read -p ""

gsm
echo "#### GDS SERVICES ####"
echo "GDS COMMAND: 
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
services
exit
EOF
echo "next: gds services"
read -p ""

gsm
echo "#### GDS SERVICES ####"
echo "GDS COMMAND: 
services
"
gdsctl <<EOF
connect gsm_admin/password1@gsm1
services
exit
EOF
echo "next: switchover to CH1 (run cli_ro_long.sh and cli_rw_long.sh first)"
read -p ""


db
echo "#### VALIDATE DATABASE OLTP_CH1 ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
validate database oltp_ch1;
EOF
echo "next: switchover"
read -p ""
echo "#### SWITCHOVER TO OLTP_CH1 ####"
dgmgrl -echo sys/password1@oltp_de <<EOF
switchover to oltp_ch1;
EOF
echo "next: gds services"
read -p ""

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

clear

function db {

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

}

function gsm {

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/gsmhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

}

echo "#### CURRENT CONFIGURATION: CLASSIC DATA GUARD, 3 DATABASES ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

show configuration

EOF

echo "next: GSM config"

read -p ""

gsm

echo "#### GSM CONFIGURATION ####"

echo "GDS COMMAND:

config"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

config

exit

EOF

echo "next: ADD GDSPOOL"

read -p ""

echo "#### ADD GDSPOOL ####"

echo "GDS COMMAND:

add gdspool -gdspool sales"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

add gdspool -gdspool sales

exit

EOF

echo "next: ADD BROKERCONFIG"

read -p ""

echo "#### ADD BROKERCONFIG ####"

echo "GDS COMMAND:

add brokerconfig -connect gsm02.trivadistraining.com:1521/oltp_de -pwd password1 -gdspool sales -region germany"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

add brokerconfig -connect gsm02.trivadistraining.com:1521/oltp_de -pwd password1 -gdspool sales -region germany

exit

EOF

echo "next: config databases"

read -p ""

echo "#### CONFIG DATABASES ####"

echo "GDS COMMAND:

config database"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

config database

exit

EOF

echo "next: modify databases"

read -p ""

echo "#### MODIFY DATABASES ####"

echo "GDS COMMAND:

modify database -database oltp_ch1 -region switzerland

modify database -database oltp_ch2 -region switzerland

gdsctl <<EOF

connect gsm_admin/password1@gsm1

modify database -database oltp_ch1 -region switzerland

modify database -database oltp_ch2 -region switzerland

config database

exit

EOF

echo "next: add service read/write"

read -p ""

echo "#### ADD SERVICE R/W ####"

echo "GDS COMMAND:

add service -gdspool sales -service gsales_rw -role primary -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover

start service -service gsales_rw

services"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

add service -gdspool sales -service gsales_rw -role primary -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover

start service -service gsales_rw

services

exit

EOF

echo "next: ADD SERVICE R/O"

read -p ""

echo "#### ADD SERVICE R/O ####"

echo "GDS COMMAND:

add service -gdspool sales -service gsales_ro -role PHYSICAL_STANDBY -failover_primary -lag 20 -preferred_all -failovertype SELECT -failovermethod BASIC -failoverretry 5 -failoverdelay 3 -locality LOCAL_ONLY -region_failover

start service -service gsales_ro

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

start service -service gsales_ro

services

exit

EOF

echo "next: stop apply ch1 (run cli_ro_short.sh first)"

read -p ""

echo "#### STOP APPLY DATA GUARD ON OLTP_CH1 ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

edit database oltp_ch1 set state='apply-off';

EOF

echo "next: gds services"

read -p ""

gsm

echo "#### GDS SERVICES ####"

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: stop apply ch2 (run cli_ro_short.sh first)"

read -p ""

echo "#### STOP APPLY DATA GUARD ON OLTP_CH2 ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

edit database oltp_ch2 set state='apply-off';

EOF

echo "next: gds services"

read -p ""

gsm

echo "#### GDS SERVICES ####"

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: gds services"

read -p ""

gsm

echo "#### GDS SERVICES ####"

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: start apply ch1 and ch2"

read -p ""

echo "#### START APPLY DATA GUARD ON OLTP_CH1 and OLTP_CH2 ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

edit database oltp_ch1 set state='apply-on';

EOF

echo "sleeping 5"

sleep 5

dgmgrl -echo sys/password1@oltp_de <<EOF

edit database oltp_ch2 set state='apply-on';

EOF

echo "next: gds services"

read -p ""

gsm

echo "#### GDS SERVICES ####"

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: gds services"

read -p ""

gsm

echo "#### GDS SERVICES ####"

echo "GDS COMMAND:

services

gdsctl <<EOF

connect gsm_admin/password1@gsm1

services

exit

EOF

echo "next: switchover to CH1 (run cli_ro_long.sh and cli_rw_long.sh first)"

read -p ""

echo "#### VALIDATE DATABASE OLTP_CH1 ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

validate database oltp_ch1;

EOF

echo "next: switchover"

read -p ""

echo "#### SWITCHOVER TO OLTP_CH1 ####"

dgmgrl -echo sys/password1@oltp_de <<EOF

switchover to oltp_ch1;

EOF

echo "next: gds services"

read -p ""

And the script to revert the demo:

clear

function db {
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
}

function gsm {
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/gsmhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
}

db
dgmgrl -echo sys/password1@oltp_de <<EOF
switchover to oltp_de;
EOF

gsm
echo "#### STOP and DELETE SERVICE, REMOVE BROKERCONFIG, REMOVE POOL ####"
 
gdsctl <<EOF
connect gsm_admin/password1@gsm1
stop service -service gsales_ro
stop service -service gsales_rw
remove service -service gsales_ro
remove service -service gsales_rw
remove brokerconfig
remove gdspool -gdspool sales
config
exit
EOF

db
dgmgrl -echo sys/password1@oltp_de <<EOF
show configuration
EOF

echo "DEMO reverted."
read -p ""

clear

function db {

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

}

function gsm {

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/gsmhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin

}

dgmgrl -echo sys/password1@oltp_de <<EOF

switchover to oltp_de;

EOF

gsm

echo "#### STOP and DELETE SERVICE, REMOVE BROKERCONFIG, REMOVE POOL ####"

gdsctl <<EOF

connect gsm_admin/password1@gsm1

stop service -service gsales_ro

stop service -service gsales_rw

remove service -service gsales_ro

remove service -service gsales_rw

remove brokerconfig

remove gdspool -gdspool sales

config

exit

EOF

dgmgrl -echo sys/password1@oltp_de <<EOF

show configuration

EOF

echo "DEMO reverted."

read -p ""

Cheers

—

Ludovico

Oracle Database on ACFS: a perfect marriage?

Posted on November 26, 2015 by Ludovico

Update: I will give this presentation at UKOUG Tech15, Wed 9 December at 14:30.

This presentation has had a very poor score in selections for conferences (no OOW, no DOAG) but people liked it very much at Paris Oracle Meetup. The Database on ACFS is mainstream now, thanks to the new ODA releases. Having some knowledge about why and how you should run (not) Databases on ACFS is definitely worth a read.

Slides

Demo 1 recording

Demo 2 recording

Demo script (DB ACFS clone from Standby Database)

#!/bin/bash
####################
# create the clone #
####################

set -x

NUM=`echo $$ | cut -c 1-4`
export NEWNAME=${1:-SN$NUM}

cat <<EOF 
################################################
################################################
##
## CLONING DATABASE USING NEW SID: $NEWNAME
##
################################################
################################################

EOF

export ORACLE_SID=ACFSDB_1

dgmgrl <<EOF
connect sys/racattack
edit database ACFSDB set state="APPLY-OFF";
exit
EOF

acfsutil snap create -w $NEWNAME /u02

dgmgrl <<EOF
connect sys/racattack
edit database ACFSDB set state="APPLY-ON";
exit
EOF

cd /u02/.ACFS/snaps/$NEWNAME/ACFSDB

sqlplus / as sysdba <<EOF
alter database backup controlfile to trace as '/u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc' reuse resetlogs;
create pfile='/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora' from spfile;
exit
EOF

sed -i -e "s/u02\/ACFSDB\//u02\/.ACFS\/snaps\/$NEWNAME\/ACFSDB\//g" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc
sed -i -e "s/CREATE CONTROLFILE.*$/CREATE CONTROLFILE REUSE SET DATABASE \"$NEWNAME\" RESETLOGS FORCE LOGGING NOARCHIVELOG/" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc
rm /u02/.ACFS/snaps/$NEWNAME/ACFSDB/ACFSDB/fast_recovery_area/ACFSDB/controlfile/*
rm /u02/.ACFS/snaps/$NEWNAME/ACFSDB/ACFSDB/controlfile/*
sed -i '/^ACFS.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i -e "s/u02\/ACFSDB\//u02\/.ACFS\/snaps\/$NEWNAME\/ACFSDB\//g" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.db_name.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.db_unique_name.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.dispatchers.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.audit_file_dest.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.fal_server.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.log_archive_config.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.log_archive_dest_1.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.memory_target.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.service_names.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora
sed -i '/^\*\.cluster_database.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora


#find /u02/.ACFS/snaps/SNAP1/ACFSDB/ACFSDB/fast_recovery_area/ACFSDB/archivelog/ -type f -exec mv {} /u02/.ACFS/snaps/SNAP1/ACFSDB/archivelog/ \;

mkdir -p $ORACLE_BASE/admin/$NEWNAME/adump

cat  >>/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora <<EOF
*.audit_file_dest='$ORACLE_BASE/admin/$NEWNAME/adump'
*.db_name='$NEWNAME'
*.db_unique_name='$NEWNAME'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=${NEWNAME}XDB)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'
*.sga_target=1900M
$NEWNAME.instance_number=1
$NEWNAME.undo_tablespace=UNDOTBS1
*.service_names='$NEWNAME'
*.cluster_database=false
EOF

export ORACLE_SID=$NEWNAME

head -n $((`grep -n ^RECOVER /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc | awk -F: '{print $1}'`-2)) /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc > /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control1.trc

 
sqlplus / as sysdba <<EOF
create spfile from pfile='/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora';
@/u02/.ACFS/snaps/$NEWNAME/ACFSDB/control1.trc
--recover automatic database using backup controlfile until cancel;
--CANCEL
alter database open resetlogs;
alter tablespace temp add tempfile size 50M ;
EOF

#!/bin/bash

####################

# create the clone #

####################

set -x

NUM=`echo $$ | cut -c 1-4`

export NEWNAME=${1:-SN$NUM}

cat <<EOF

################################################

## CLONING DATABASE USING NEW SID: $NEWNAME

################################################

EOF

export ORACLE_SID=ACFSDB_1

dgmgrl <<EOF

connect sys/racattack

edit database ACFSDB set state="APPLY-OFF";

exit

EOF

acfsutil snap create -w $NEWNAME /u02

dgmgrl <<EOF

connect sys/racattack

edit database ACFSDB set state="APPLY-ON";

exit

EOF

cd /u02/.ACFS/snaps/$NEWNAME/ACFSDB

sqlplus / as sysdba <<EOF

alter database backup controlfile to trace as '/u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc' reuse resetlogs;

create pfile='/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora' from spfile;

exit

EOF

sed -i -e "s/u02\/ACFSDB\//u02\/.ACFS\/snaps\/$NEWNAME\/ACFSDB\//g" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc

sed -i -e "s/CREATE CONTROLFILE.*$/CREATE CONTROLFILE REUSE SET DATABASE \"$NEWNAME\" RESETLOGS FORCE LOGGING NOARCHIVELOG/" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc

rm /u02/.ACFS/snaps/$NEWNAME/ACFSDB/ACFSDB/fast_recovery_area/ACFSDB/controlfile/*

rm /u02/.ACFS/snaps/$NEWNAME/ACFSDB/ACFSDB/controlfile/*

sed -i '/^ACFS.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i -e "s/u02\/ACFSDB\//u02\/.ACFS\/snaps\/$NEWNAME\/ACFSDB\//g" /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.db_name.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.db_unique_name.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.dispatchers.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.audit_file_dest.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.fal_server.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.log_archive_config.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.log_archive_dest_1.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.memory_target.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.service_names.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

sed -i '/^\*\.cluster_database.*$/d' /u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora

#find /u02/.ACFS/snaps/SNAP1/ACFSDB/ACFSDB/fast_recovery_area/ACFSDB/archivelog/ -type f -exec mv {} /u02/.ACFS/snaps/SNAP1/ACFSDB/archivelog/ \;

mkdir -p $ORACLE_BASE/admin/$NEWNAME/adump

cat >>/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora <<EOF

*.audit_file_dest='$ORACLE_BASE/admin/$NEWNAME/adump'

*.db_name='$NEWNAME'

*.db_unique_name='$NEWNAME'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=${NEWNAME}XDB)'

*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'

*.sga_target=1900M

$NEWNAME.instance_number=1

$NEWNAME.undo_tablespace=UNDOTBS1

*.service_names='$NEWNAME'

*.cluster_database=false

EOF

export ORACLE_SID=$NEWNAME

head -n $((`grep -n ^RECOVER /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc | awk -F: '{print $1}'`-2)) /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control.trc > /u02/.ACFS/snaps/$NEWNAME/ACFSDB/control1.trc

sqlplus / as sysdba <<EOF

create spfile from pfile='/u02/.ACFS/snaps/$NEWNAME/ACFSDB/init$NEWNAME.ora';

@/u02/.ACFS/snaps/$NEWNAME/ACFSDB/control1.trc

--recover automatic database using backup controlfile until cancel;

--CANCEL

alter database open resetlogs;

alter tablespace temp add tempfile size 50M ;

EOF

Comments are, as always, very appreciated 🙂

—

Ludo

Migrating Oracle RAC from SuSE to OEL (or RHEL) live

Posted on November 10, 2015 by Ludovico

I have a customer that needs to migrate its Oracle RAC cluster from SuSE to OEL.

I know, I know, there is a paper from Dell and Oracle named:

How Dell Migrated from SUSE Linux to Oracle Linux

That explains how Dell migrated its many RAC clusters from SuSE to OEL. The problem is that they used a different strategy:

– backup the configuration of the nodes
– then for each node, one at time
– stop the node
– reinstall the OS
– restore the configuration and the Oracle binaries
– relink
– restart

What I want to achieve instead is:
– add one OEL node to the SuSE cluster as new node
– remove one SuSE node from the now-mixed cluster
– install/restore/relink the RDBMS software (RAC) on the new node
– move the RAC instances to the new node (taking care to NOT run more than the number of licensed nodes/CPUs at any time)
– repeat (for the remaining nodes)

because the customer will also migrate to new hardware.

In order to test this migration path, I’ve set up a SINGLE NODE cluster (if it works for one node, it will for two or more).

oracle@sles01:~> crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       sles01                   STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       sles01                   STABLE
ora.asm
               ONLINE  ONLINE       sles01                   Started,STABLE
ora.net1.network
               ONLINE  ONLINE       sles01                   STABLE
ora.ons
               ONLINE  ONLINE       sles01                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       sles01                   STABLE
ora.cvu
      1        ONLINE  ONLINE       sles01                   STABLE
ora.oc4j
      1        OFFLINE OFFLINE                               STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       sles01                   STABLE
ora.sles01.vip
      1        ONLINE  ONLINE       sles01                   STABLE
--------------------------------------------------------------------------------
oracle@sles01:~> cat /etc/issue

Welcome to SUSE Linux Enterprise Server 11 SP4  (x86_64) - Kernel \r (\l).

oracle@sles01:~> crsctl stat res -t

--------------------------------------------------------------------------------

Name Target State Server State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.DATA.dg

ONLINE ONLINE sles01 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE sles01 STABLE

ora.asm

ONLINE ONLINE sles01 Started,STABLE

ora.net1.network

ONLINE ONLINE sles01 STABLE

ora.ons

ONLINE ONLINE sles01 STABLE

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE sles01 STABLE

ora.cvu

1 ONLINE ONLINE sles01 STABLE

ora.oc4j

1 OFFLINE OFFLINE STABLE

ora.scan1.vip

1 ONLINE ONLINE sles01 STABLE

ora.sles01.vip

1 ONLINE ONLINE sles01 STABLE

--------------------------------------------------------------------------------

oracle@sles01:~> cat /etc/issue

Welcome to SUSE Linux Enterprise Server 11 SP4 (x86_64) - Kernel \r (\l).

I have to setup the new node addition carefully, mainly as I would do with a traditional node addition:

Add new ip addresses (public, private, vip) to the DNS/hosts
Install the new OEL server
Keep the same user and groups (uid, gid, etc)
Verify the network connectivity and setup SSH equivalence
Check that the multicast connection is ok
Add the storage, configure persistent naming (udev) and verify that the disks (major, minor, names) are the very same
The network cards also must be the very same

Once the new host ready, the cluvfy stage -pre nodeadd will likely fail due to

Kernel release mismatch
Package mismatch

Here’s an example of output:

oracle@sles01:~> cluvfy stage -pre nodeadd -n rhel01

Performing pre-checks for node addition

Checking node reachability...
Node reachability check passed from node "sles01"


Checking user equivalence...
User equivalence check passed for user "oracle"
Package existence check passed for "cvuqdisk"

Checking CRS integrity...

CRS integrity check passed

Clusterware version consistency passed.

Checking shared resources...

Checking CRS home location...
Location check passed for: "/u01/app/12.1.0/grid"
Shared resources check for node addition passed


Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity using interfaces on subnet "192.168.56.0"
Node connectivity passed for subnet "192.168.56.0" with node(s) sles01,rhel01
TCP connectivity check passed for subnet "192.168.56.0"


Check: Node connectivity using interfaces on subnet "172.16.100.0"
Node connectivity passed for subnet "172.16.100.0" with node(s) rhel01,sles01
TCP connectivity check passed for subnet "172.16.100.0"

Checking subnet mask consistency...
Subnet mask consistency check passed for subnet "192.168.56.0".
Subnet mask consistency check passed for subnet "172.16.100.0".
Subnet mask consistency check passed.

Node connectivity check passed

Checking multicast communication...

Checking subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251"...
Check of subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251" passed.

Check of multicast communication passed.
Total memory check passed
Available memory check passed
Swap space check passed
Free disk space check passed for "sles01:/usr,sles01:/var,sles01:/etc,sles01:/u01/app/12.1.0/grid,sles01:/sbin,sles01:/tmp"
Free disk space check passed for "rhel01:/usr,rhel01:/var,rhel01:/etc,rhel01:/u01/app/12.1.0/grid,rhel01:/sbin,rhel01:/tmp"
Check for multiple users with UID value 1101 passed
User existence check passed for "oracle"
Run level check passed
Hard limits check passed for "maximum open file descriptors"
Soft limits check passed for "maximum open file descriptors"
Hard limits check passed for "maximum user processes"
Soft limits check passed for "maximum user processes"
System architecture check passed

WARNING:
PRVF-7524 : Kernel version is not consistent across all the nodes.
Kernel version = "3.0.101-63-default" found on nodes: sles01.
Kernel version = "3.8.13-16.2.1.el6uek.x86_64" found on nodes: rhel01.
Kernel version check passed
Kernel parameter check passed for "semmsl"
Kernel parameter check passed for "semmns"
Kernel parameter check passed for "semopm"
Kernel parameter check passed for "semmni"
Kernel parameter check passed for "shmmax"
Kernel parameter check passed for "shmmni"
Kernel parameter check passed for "shmall"
Kernel parameter check passed for "file-max"
Kernel parameter check passed for "ip_local_port_range"
Kernel parameter check passed for "rmem_default"
Kernel parameter check passed for "rmem_max"
Kernel parameter check passed for "wmem_default"
Kernel parameter check passed for "wmem_max"
Kernel parameter check passed for "aio-max-nr"
Package existence check passed for "make"
Package existence check passed for "libaio"
Package existence check passed for "binutils"
Package existence check passed for "gcc(x86_64)"
Package existence check passed for "gcc-c++(x86_64)"
Package existence check passed for "glibc"
Package existence check passed for "glibc-devel"
Package existence check passed for "ksh"
Package existence check passed for "libaio-devel"
Package existence check failed for "libstdc++33"
Check failed on nodes:
        rhel01
Package existence check failed for "libstdc++43-devel"
Check failed on nodes:
        rhel01
Package existence check passed for "libstdc++-devel(x86_64)"
Package existence check failed for "libstdc++46"
Check failed on nodes:
        rhel01
Package existence check failed for "libgcc46"
Check failed on nodes:
        rhel01
Package existence check passed for "sysstat"
Package existence check failed for "libcap1"
Check failed on nodes:
        rhel01
Package existence check failed for "nfs-kernel-server"
Check failed on nodes:
        rhel01
Check for multiple users with UID value 0 passed
Current group ID check passed

Starting check for consistency of primary group of root user

Check for consistency of root user's primary group passed
Group existence check passed for "asmadmin"
Group existence check passed for "asmoper"
Group existence check passed for "asmdba"

Checking ASMLib configuration.
Check for ASMLib configuration passed.

Checking OCR integrity...

OCR integrity check passed

Checking Oracle Cluster Voting Disk configuration...

Oracle Cluster Voting Disk configuration check passed
Time zone consistency check passed

Starting Clock synchronization checks using Network Time Protocol(NTP)...

NTP Configuration file check started...
No NTP Daemons or Services were found to be running

Clock synchronization check using Network Time Protocol(NTP) passed


User "oracle" is not part of "root" group. Check passed
Checking integrity of file "/etc/resolv.conf" across nodes

"domain" and "search" entries do not coexist in any  "/etc/resolv.conf" file
All nodes have same "search" order defined in file "/etc/resolv.conf"
PRVF-5636 : The DNS response time for an unreachable node exceeded "15000" ms on following nodes: sles01,rhel01

Check for integrity of file "/etc/resolv.conf" failed


Checking integrity of name service switch configuration file "/etc/nsswitch.conf" ...
Check for integrity of name service switch configuration file "/etc/nsswitch.conf" passed


Pre-check for node addition was unsuccessful on all the nodes.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

oracle@sles01:~> cluvfy stage -pre nodeadd -n rhel01

Performing pre-checks for node addition

Checking node reachability...

Node reachability check passed from node "sles01"

Checking user equivalence...

User equivalence check passed for user "oracle"

Package existence check passed for "cvuqdisk"

Checking CRS integrity...

CRS integrity check passed

Clusterware version consistency passed.

Checking shared resources...

Checking CRS home location...

Location check passed for: "/u01/app/12.1.0/grid"

Shared resources check for node addition passed

Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity using interfaces on subnet "192.168.56.0"

Node connectivity passed for subnet "192.168.56.0" with node(s) sles01,rhel01

TCP connectivity check passed for subnet "192.168.56.0"

Check: Node connectivity using interfaces on subnet "172.16.100.0"

Node connectivity passed for subnet "172.16.100.0" with node(s) rhel01,sles01

TCP connectivity check passed for subnet "172.16.100.0"

Checking subnet mask consistency...

Subnet mask consistency check passed for subnet "192.168.56.0".

Subnet mask consistency check passed for subnet "172.16.100.0".

Subnet mask consistency check passed.

Node connectivity check passed

Checking multicast communication...

Checking subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251"...

Check of subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251" passed.

Check of multicast communication passed.

Total memory check passed

Available memory check passed

Swap space check passed

Free disk space check passed for "sles01:/usr,sles01:/var,sles01:/etc,sles01:/u01/app/12.1.0/grid,sles01:/sbin,sles01:/tmp"

Free disk space check passed for "rhel01:/usr,rhel01:/var,rhel01:/etc,rhel01:/u01/app/12.1.0/grid,rhel01:/sbin,rhel01:/tmp"

Check for multiple users with UID value 1101 passed

User existence check passed for "oracle"

Run level check passed

Hard limits check passed for "maximum open file descriptors"

Soft limits check passed for "maximum open file descriptors"

Hard limits check passed for "maximum user processes"

Soft limits check passed for "maximum user processes"

System architecture check passed

WARNING:

PRVF-7524 : Kernel version is not consistent across all the nodes.

Kernel version = "3.0.101-63-default" found on nodes: sles01.

Kernel version = "3.8.13-16.2.1.el6uek.x86_64" found on nodes: rhel01.

Kernel version check passed

Kernel parameter check passed for "semmsl"

Kernel parameter check passed for "semmns"

Kernel parameter check passed for "semopm"

Kernel parameter check passed for "semmni"

Kernel parameter check passed for "shmmax"

Kernel parameter check passed for "shmmni"

Kernel parameter check passed for "shmall"

Kernel parameter check passed for "file-max"

Kernel parameter check passed for "ip_local_port_range"

Kernel parameter check passed for "rmem_default"

Kernel parameter check passed for "rmem_max"

Kernel parameter check passed for "wmem_default"

Kernel parameter check passed for "wmem_max"

Kernel parameter check passed for "aio-max-nr"

Package existence check passed for "make"

Package existence check passed for "libaio"

Package existence check passed for "binutils"

Package existence check passed for "gcc(x86_64)"

Package existence check passed for "gcc-c++(x86_64)"

Package existence check passed for "glibc"

Package existence check passed for "glibc-devel"

Package existence check passed for "ksh"

Package existence check passed for "libaio-devel"

Package existence check failed for "libstdc++33"

Check failed on nodes:

rhel01

Package existence check failed for "libstdc++43-devel"

Check failed on nodes:

rhel01

Package existence check passed for "libstdc++-devel(x86_64)"

Package existence check failed for "libstdc++46"

Check failed on nodes:

rhel01

Package existence check failed for "libgcc46"

Check failed on nodes:

rhel01

Package existence check passed for "sysstat"

Package existence check failed for "libcap1"

Check failed on nodes:

rhel01

Package existence check failed for "nfs-kernel-server"

Check failed on nodes:

rhel01

Check for multiple users with UID value 0 passed

Current group ID check passed

Starting check for consistency of primary group of root user

Check for consistency of root user's primary group passed

Group existence check passed for "asmadmin"

Group existence check passed for "asmoper"

Group existence check passed for "asmdba"

Checking ASMLib configuration.

Check for ASMLib configuration passed.

Checking OCR integrity...

OCR integrity check passed

Checking Oracle Cluster Voting Disk configuration...

Oracle Cluster Voting Disk configuration check passed

Time zone consistency check passed

Starting Clock synchronization checks using Network Time Protocol(NTP)...

NTP Configuration file check started...

No NTP Daemons or Services were found to be running

Clock synchronization check using Network Time Protocol(NTP) passed

User "oracle" is not part of "root" group. Check passed

Checking integrity of file "/etc/resolv.conf" across nodes

"domain" and "search" entries do not coexist in any "/etc/resolv.conf" file

All nodes have same "search" order defined in file "/etc/resolv.conf"

PRVF-5636 : The DNS response time for an unreachable node exceeded "15000" ms on following nodes: sles01,rhel01

Check for integrity of file "/etc/resolv.conf" failed

Checking integrity of name service switch configuration file "/etc/nsswitch.conf" ...

Check for integrity of name service switch configuration file "/etc/nsswitch.conf" passed

Pre-check for node addition was unsuccessful on all the nodes.

So the problem is not if the check succeed or not (it will not), but what fails.

Solving all the problems not related to the difference SuSE-OEL is crucial, because the addNode.sh will fail with the same errors. I need to run it using -ignorePrereqs and -ignoreSysPrereqs switches. Let’s see how it works:

oracle@sles01:/u01/app/12.1.0/grid/addnode> ./addnode.sh -silent "CLUSTER_NEW_NODES={rhel01}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={rhel01-vip}" -ignorePrereq -ignoreSysPrereqs
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 27479 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 2032 MB    Passed

Prepare Configuration in progress.

Prepare Configuration successful.
..................................................   9% Done.
You can find the log of this install session at:
 /u01/app/oraInventory/logs/addNodeActions2015-11-09_09-57-16PM.log

Instantiate files in progress.

Instantiate files successful.
..................................................   15% Done.

Copying files to node in progress.

Copying files to node successful.
..................................................   79% Done.

Saving cluster inventory in progress.
..................................................   87% Done.

Saving cluster inventory successful.
The Cluster Node Addition of /u01/app/12.1.0/grid was successful.
Please check '/tmp/silentInstall.log' for more details.

As a root user, execute the following script(s):
        1. /u01/app/oraInventory/orainstRoot.sh
        2. /u01/app/12.1.0/grid/root.sh

Execute /u01/app/oraInventory/orainstRoot.sh on the following nodes:
[rhel01]
Execute /u01/app/12.1.0/grid/root.sh on the following nodes:
[rhel01]

The scripts can be executed in parallel on all the nodes. If there are any policy managed databases managed by cluster, proceed with the addnode procedure without executing the root.sh script. Ensure that root.sh script is executed after all the policy managed databases managed by clusterware are extended to the new nodes.
..........
Update Inventory in progress.
..................................................   100% Done.

Update Inventory successful.
Successfully Setup Software.

oracle@sles01:/u01/app/12.1.0/grid/addnode> ./addnode.sh -silent "CLUSTER_NEW_NODES={rhel01}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={rhel01-vip}" -ignorePrereq -ignoreSysPrereqs

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 27479 MB Passed

Checking swap space: must be greater than 150 MB. Actual 2032 MB Passed

Prepare Configuration in progress.

Prepare Configuration successful.

.................................................. 9% Done.

You can find the log of this install session at:

/u01/app/oraInventory/logs/addNodeActions2015-11-09_09-57-16PM.log

Instantiate files in progress.

Instantiate files successful.

.................................................. 15% Done.

Copying files to node in progress.

Copying files to node successful.

.................................................. 79% Done.

Saving cluster inventory in progress.

.................................................. 87% Done.

Saving cluster inventory successful.

The Cluster Node Addition of /u01/app/12.1.0/grid was successful.

Please check '/tmp/silentInstall.log' for more details.

As a root user, execute the following script(s):

1. /u01/app/oraInventory/orainstRoot.sh

2. /u01/app/12.1.0/grid/root.sh

Execute /u01/app/oraInventory/orainstRoot.sh on the following nodes:

[rhel01]

Execute /u01/app/12.1.0/grid/root.sh on the following nodes:

[rhel01]

The scripts can be executed in parallel on all the nodes. If there are any policy managed databases managed by cluster, proceed with the addnode procedure without executing the root.sh script. Ensure that root.sh script is executed after all the policy managed databases managed by clusterware are extended to the new nodes.

..........

Update Inventory in progress.

.................................................. 100% Done.

Update Inventory successful.

Successfully Setup Software.

Then, as stated by the addNode.sh, I run the root.sh and I expect it to work:

[oracle@rhel01 install]$ sudo /u01/app/12.1.0/grid/root.sh
Performing root user operation for Oracle 12c

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/12.1.0/grid
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Relinking oracle with rac_on option
Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params
2015/11/09 23:18:42 CLSRSC-363: User ignored prerequisites during installation

OLR initialization - successful
2015/11/09 23:19:08 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.conf'

CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Starting Oracle High Availability Services-managed resources
CRS-2672: Attempting to start 'ora.mdnsd' on 'rhel01'
CRS-2672: Attempting to start 'ora.evmd' on 'rhel01'
CRS-2676: Start of 'ora.mdnsd' on 'rhel01' succeeded
CRS-2676: Start of 'ora.evmd' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'rhel01'
CRS-2676: Start of 'ora.gpnpd' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'rhel01'
CRS-2676: Start of 'ora.gipcd' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rhel01'
CRS-2676: Start of 'ora.cssdmonitor' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'rhel01'
CRS-2672: Attempting to start 'ora.diskmon' on 'rhel01'
CRS-2676: Start of 'ora.diskmon' on 'rhel01' succeeded
CRS-2789: Cannot stop resource 'ora.diskmon' as it is not running on server 'rhel01'
CRS-2676: Start of 'ora.cssd' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rhel01'
CRS-2672: Attempting to start 'ora.ctssd' on 'rhel01'
CRS-2676: Start of 'ora.ctssd' on 'rhel01' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'rhel01'
CRS-2676: Start of 'ora.asm' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'rhel01'
CRS-2676: Start of 'ora.storage' on 'rhel01' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'rhel01'
CRS-2676: Start of 'ora.crsd' on 'rhel01' succeeded
CRS-6017: Processing resource auto-start for servers: rhel01
CRS-2672: Attempting to start 'ora.ons' on 'rhel01'
CRS-2676: Start of 'ora.ons' on 'rhel01' succeeded
CRS-6016: Resource auto-start has completed for server rhel01
CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources
CRS-4123: Oracle High Availability Services has been started.
2015/11/09 23:22:06 CLSRSC-343: Successfully started Oracle clusterware stack

clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 12c Release 1.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Preparing packages for installation...
cvuqdisk-1.0.9-1
2015/11/09 23:22:23 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

[oracle@rhel01 install]$ sudo /u01/app/12.1.0/grid/root.sh

Performing root user operation for Oracle 12c

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /u01/app/12.1.0/grid

Copying dbhome to /usr/local/bin ...

Copying oraenv to /usr/local/bin ...

Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Relinking oracle with rac_on option

Using configuration parameter file: /u01/app/12.1.0/grid/crs/install/crsconfig_params

2015/11/09 23:18:42 CLSRSC-363: User ignored prerequisites during installation

OLR initialization - successful

2015/11/09 23:19:08 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.conf'

CRS-4133: Oracle High Availability Services has been stopped.

CRS-4123: Oracle High Availability Services has been started.

CRS-4133: Oracle High Availability Services has been stopped.

CRS-4123: Oracle High Availability Services has been started.

CRS-4133: Oracle High Availability Services has been stopped.

CRS-4123: Starting Oracle High Availability Services-managed resources

CRS-2672: Attempting to start 'ora.mdnsd' on 'rhel01'

CRS-2672: Attempting to start 'ora.evmd' on 'rhel01'

CRS-2676: Start of 'ora.mdnsd' on 'rhel01' succeeded

CRS-2676: Start of 'ora.evmd' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.gpnpd' on 'rhel01'

CRS-2676: Start of 'ora.gpnpd' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.gipcd' on 'rhel01'

CRS-2676: Start of 'ora.gipcd' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rhel01'

CRS-2676: Start of 'ora.cssdmonitor' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.cssd' on 'rhel01'

CRS-2672: Attempting to start 'ora.diskmon' on 'rhel01'

CRS-2676: Start of 'ora.diskmon' on 'rhel01' succeeded

CRS-2789: Cannot stop resource 'ora.diskmon' as it is not running on server 'rhel01'

CRS-2676: Start of 'ora.cssd' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rhel01'

CRS-2672: Attempting to start 'ora.ctssd' on 'rhel01'

CRS-2676: Start of 'ora.ctssd' on 'rhel01' succeeded

CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.asm' on 'rhel01'

CRS-2676: Start of 'ora.asm' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.storage' on 'rhel01'

CRS-2676: Start of 'ora.storage' on 'rhel01' succeeded

CRS-2672: Attempting to start 'ora.crsd' on 'rhel01'

CRS-2676: Start of 'ora.crsd' on 'rhel01' succeeded

CRS-6017: Processing resource auto-start for servers: rhel01

CRS-2672: Attempting to start 'ora.ons' on 'rhel01'

CRS-2676: Start of 'ora.ons' on 'rhel01' succeeded

CRS-6016: Resource auto-start has completed for server rhel01

CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources

CRS-4123: Oracle High Availability Services has been started.

2015/11/09 23:22:06 CLSRSC-343: Successfully started Oracle clusterware stack

clscfg: EXISTING configuration version 5 detected.

clscfg: version 5 is 12c Release 1.

Successfully accumulated necessary OCR keys.

Creating OCR keys for user 'root', privgrp 'root'..

Operation successful.

Preparing packages for installation...

cvuqdisk-1.0.9-1

2015/11/09 23:22:23 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

Bingo! Let’s check if everything is up and running:

[oracle@rhel01 ~]$ /u01/app/12.1.0/grid/bin/crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       rhel01                   STABLE
               ONLINE  ONLINE       sles01                   STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       rhel01                   STABLE
               ONLINE  ONLINE       sles01                   STABLE
ora.asm
               ONLINE  ONLINE       rhel01                   Started,STABLE
               ONLINE  ONLINE       sles01                   Started,STABLE
ora.net1.network
               ONLINE  ONLINE       rhel01                   STABLE
               ONLINE  ONLINE       sles01                   STABLE
ora.ons
               ONLINE  ONLINE       rhel01                   STABLE
               ONLINE  ONLINE       sles01                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       sles01                   STABLE
ora.cvu
      1        ONLINE  ONLINE       sles01                   STABLE
ora.oc4j
      1        OFFLINE OFFLINE                               STABLE
ora.rhel01.vip
      1        ONLINE  ONLINE       rhel01                   STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       sles01                   STABLE
ora.sles01.vip
      1        ONLINE  ONLINE       sles01                   STABLE
--------------------------------------------------------------------------------

[oracle@rhel01 ~]$ /u01/app/12.1.0/grid/bin/crsctl stat res -t

--------------------------------------------------------------------------------

Name Target State Server State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.DATA.dg

ONLINE ONLINE rhel01 STABLE

ONLINE ONLINE sles01 STABLE

ora.LISTENER.lsnr

ONLINE ONLINE rhel01 STABLE

ONLINE ONLINE sles01 STABLE

ora.asm

ONLINE ONLINE rhel01 Started,STABLE

ONLINE ONLINE sles01 Started,STABLE

ora.net1.network

ONLINE ONLINE rhel01 STABLE

ONLINE ONLINE sles01 STABLE

ora.ons

ONLINE ONLINE rhel01 STABLE

ONLINE ONLINE sles01 STABLE

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE sles01 STABLE

ora.cvu

1 ONLINE ONLINE sles01 STABLE

ora.oc4j

1 OFFLINE OFFLINE STABLE

ora.rhel01.vip

1 ONLINE ONLINE rhel01 STABLE

ora.scan1.vip

1 ONLINE ONLINE sles01 STABLE

ora.sles01.vip

1 ONLINE ONLINE sles01 STABLE

--------------------------------------------------------------------------------

[oracle@rhel01 ~]$ olsnodes -s
sles01  Active
rhel01  Active

[oracle@rhel01 ~]$ ssh rhel01 uname -r
3.8.13-16.2.1.el6uek.x86_64
[oracle@rhel01 ~]$ ssh sles01 uname -r
3.0.101-63-default

[oracle@rhel01 ~]$ ssh rhel01 cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)
[oracle@rhel01 ~]$ ssh sles01 cat /etc/issue
Welcome to SUSE Linux Enterprise Server 11 SP4  (x86_64) - Kernel \r (\l).

[oracle@rhel01 ~]$ olsnodes -s

sles01 Active

rhel01 Active

[oracle@rhel01 ~]$ ssh rhel01 uname -r

3.8.13-16.2.1.el6uek.x86_64

[oracle@rhel01 ~]$ ssh sles01 uname -r

3.0.101-63-default

[oracle@rhel01 ~]$ ssh rhel01 cat /etc/redhat-release

Red Hat Enterprise Linux Server release 6.5 (Santiago)

[oracle@rhel01 ~]$ ssh sles01 cat /etc/issue

Welcome to SUSE Linux Enterprise Server 11 SP4 (x86_64) - Kernel \r (\l).

So yes, it works, but remember that it’s not a supported long-term configuration.

In my case I expect to migrate the whole cluster from SLES to OEL in one day.

NOTE: using OEL6 as new target is easy because the interface names do not change. The new OEL7 interface naming changes, if you need to migrate without cluster downtime you need to setup the new OEL7 nodes following this post: http://ask.xmodulo.com/change-network-interface-name-centos7.html

Otherwise, you need to configure a new interface name for the cluster with oifcfg.

HTH

—

Ludovico