Flashback RAC Database to Guarantee Restore Point ( GRP )

It is a good practice to create a guarantee restore point ( GRP ), before upgrading or patching databases.

Here is an example of how to create a guarantee  restore point ( GRP ), and how to flashback the RAC database to this guarantee  restore point ( GRP ).

1)Check and make sure database is running at archivelog mode.

SQL> set pagesize 120
SQL> set linesize 180
SQL> select LOG_MODE,FORCE_LOGGING,CURRENT_SCN,FLASHBACK_ON 
      from v$database;

LOG_MODE   FORCE_LOGGING CURRENT_SCN FLASHBACK_ON
---------- ------------- ----------- ------------
ARCHIVELOG YES           6622166     NO

2) Create the guarantee restore point.

SQL> select * from v$restore_point;
no rows selected

SQL> create table before_patching ( id number, name varchar2(30));
Table created.

SQL> create restore point BEFORE_PATCHING guarantee flashback database;
Restore point created.

SQL> create table after_GRP ( id number, name varchar2(30));
Table created.

SQL> select * from v$restore_point;

SCN       DT GUA STORAGE_SIZE TIME      REST NAME           CON_ID
-------- --- --- ------------ --------- ---- -------------- -------
 6623316 1   YES 4294967296   29-NOV-16 YES BEFORE_PATCHING 0

3) Applying the patches unsuccessfully.

 The rollback is required to flashback the database to guarantee restore point ( GRP ).

4) Shutdown RAC database,  and then start up current instance only in mount mode.

$ srvctl stop database -d RACTEST
$ srvctl start instance -d RACTEST -i RACTEST1 -o mount;

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> flashback database to restore point BEFORE_PATCHING;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select LOG_MODE,FORCE_LOGGING,CURRENT_SCN,FLASHBACK_ON 
       from v$database;

LOG_MODE   FORCE_LOGGING CURRENT_SCN  FLASHBACK_ON
---------- ------------  ----------  -----------------
ARCHIVELOG YES           6625602     RESTORE POINT ONLY

SQL> select count(*) from before_patching;

 COUNT(*)
----------
 0

SQL> select count(*) from after_GRP;  <---DB was flasged back to the correct point.
 select count(*) from after_GRP
 *
ERROR at line 1:
ORA-00942: table or view does not exist

5) Start up all the instances.

$ srvctl stop database -d RACTEST
$ srvctl start database -d RACTEST
$ srvctl status database -d RACTEST
Instance RACTEST1 is running on node racnode1
Instance RACTEST2 is running on node racnode2
Instance RACTEST3 is running on node racnode3
Instance RACTEST4 is running on node racnode4

6) Drop the guarantee  restore point ( GRP ).

SQL> drop restore point BEFORE_PATCHING;

Restore point dropped.

SQL> select * from v$restore_point;

no rows selected

How to Tell if Oracle Client is 32-bit or 64-bit installed on Windows

Some vendor applications specifically require 32bit Oracle client.

It is easy to tell 32 bit or 64 bit oracle on Unix/Linux by :

$ file sqlplus
sqlplus: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), not stripped

In Windows, there are a couple of ways to check Oracle client is 32 bit or 64 bit by :

TNSPING

—  32bit Oracle client installed

D:\>tnsping
TNS Ping Utility for 32-bit Windows: Version 12.1.0.2.0 - Production on 29-NOV-2016 09:24:50

— 64 bit Oracle client installed

c:\>tnsping
TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 29-NOV-2016 09:27:19
Copyright (c) 1997, 2014, Oracle. All rights reserved.

TASK MANAGER

Run command “sqlplus” in command prompt window, then open “task manager”.

—  32bit Oracle client installed

oracleclient32bit

— 64 bit Oracle client installed

oracleclient64bit

%ORACLE_HOME%\inventory\ContentsXML\oraclehomeproperties.xml file

—  32bit Oracle client installed

...
..
  
 <ARU_ID>912</ARU_ID>
 <ARU_ID_DESCRIPTION>Microsoft Windows (32-bit)
..
.
 <PROPERTY NAME="ARCHITECTURE" VAL="32"/>
..
.

— 64 bit Oracle client installed

...
..
 <ARU_ID>233</ARU_ID>
 <ARU_ID_DESCRIPTION>Microsoft Windows (64-bit AMD)
..
.
 <PROPERTY NAME="ARCHITECTURE" VAL="64"/>
..
.

PERL under %ORACLE_HOME%

Perl  installed according to the bit version of the ORACLE_HOME but not the OS bit version

cd %ORACLE_HOME%\perl\bin
perl -version

This is perl 5, version 14, subversion 4 (v5.14.4) built for MSWin32-x86-multi-thread ( 32-bit ORACLE_HOME )

This is perl 5, version 14, subversion 4 (v5.14.4) built for MSWin32-x64-multi-thread ( 64-bit ORACLE_HOME )

JAVA under %ORACLE_HOME%

32-bit Java will be installed with 32-bit Oracle software & 64 bit with 64-bit Oracle software

—  32bit Oracle client installed.  32-bit doesn’t shows the bit version.

cd %ORACLE_HOME%\jdk\bin
java -version

java version "1.6.0_75"
Java(TM) SE Runtime Environment (build 1.6.0_75-b13)
Java HotSpot(TM) Client VM (build 20.75-b01, mixed mode)

—  64bit Oracle client installed

cd %ORACLE_HOME%\jdk\bin
java -version

java version "1.6.0_75"
Java(TM) SE Runtime Environment (build 1.6.0_75-b13)
Java HotSpot(TM) 64-Bit Server VM (build 20.75-b01, mixed mode)

“ORA-12505, TNS:listener does not currently know of SID given in connect descriptor” from ORMB ouafDatabasePatch

ouafDatabasePatch.cmd requires Instance Name ( SID ) instead of Service Name.

SYMPTOMS

When  installing Rollup Pack for Oracle Utilities Application Framework Version 4.3.0.1.0, ran ouafDatabasePatch.cmd command , and got the following errors:

D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>set TOOLSBIN=D:\dbpatch_tools\bin
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>set JAVA_HOME=D:\java
D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\FW-V4.3.0.1.0-Rollup\Database>ouafDatabasePatch.cmd
"CMDLINE::: D:\java\bin\java.exe -cp D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\dbpatch_tools\lib\*;D:\RMB\RMB-V2.5.0.1.0-FW-PREREQ-MultiPlatform\dbpatch_tools\config com.oracle.ouaf.database.patch.OUAFPatch"

Enter the target database type (O/M/D) [O]: O
Enter the username that owns the schema: CISADM
Enter the password for the CISADM user: XXXXXX
Enter the name of the Oracle Database Connection String: ractest-scan.ractest.local:1521:ORMB

Couldn't connect to database ORACLE ractest-scan.ractest.local:1521:ORMB CISADM : java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

In connection string “ractest-scan.ractest.local:1521:ORMB”, ORMB is a service name instead of instance name.

SOLUTION

1)Apply patch 22505470: PATCHES APPEND EXTRA SPACE TO STRINGS AND ADD DBSERVICE CONNECTION SUPPORT.

2) Replace D:\dbpatch_tools\lib with new ones.

OR

2) Add below parameter into LISTENER.ora , and bounce/reload the listener.

USE_SID_AS_SERVICE_listener=on

PLS-00201: identifier ‘SYS.DBMS_DATAPUMP’ must be declared

DataPump impdp or expdp requires a PDB open mode.

SYMPOTMS

When trying to run datapump impdp into a PDB, the following errors occurred:

UDI-06550: operation generated ORACLE error 6550
ORA-06550: line 1, column 100:
PLS-00201: identifier 'SYS.DBMS_DATAPUMP' must be declared
ORA-06550: line 1, column 100:
PL/SQL: Statement ignored
ORA-06550: line 1, column 202:
PLS-00201: identifier 'SYS.DBMS_DATAPUMP' must be declared
ORA-06550: line 1, column 202:
PL/SQL: Statement ignored

CAUSES

The PDB database is not open.

SQL> show con_name

CON_NAME
------------------------------
pdb1

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

RESOLUTION

Open the PDB database.

SQL> show con_name

CON_NAME
------------------------------
pdb1

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter pluggable database open;

Pluggable database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Java SE Installation for ORMB

Installing software into a directory that include spaces in the path name (e.g. C:\Program Files\) might cause issues for some vendor applications.

Purpose

To install the Java SE JDK 1.8 as a prerequisite for deploying ORMB 2.5.0.1.

Download Java

Download required JDK 1.8 binary (  jdk-8u112-windows-x64.exe )  here.

Install Java

It is strongly advised to choose an installation directory that does not include spaces in the path name (e.g., do NOT install in C:\Program Files\). Some software will throw exceptions that depend on the value of JAVA_HOME, which either includes a space,or defines as “C:\Progra~1\”. 

  • Run  jdk-8u112-windows-x64.exe.
  • Change the installation directory to a path with no spaces in it, such as D:\jdk1.8.
  • All other installation defaults are accepted.
  • When the installer gets to the stage of installing the J2SE Runtime Environment, it is fine to accept the default, e.g., C:\Program Files\Java.
  • Add Java’s bin directory (e.g.,  D:\jdk1.8\bin) to your PATH environment.
  • Set JAVA_HOME (  e.g., D:\jdk1.8 ).