We're here to help!

Dbvisit Standby : Graceful Switchover Oracle Cloud to Oracle Cloud

Follow

Read our docs The purpose of this article is to make the reader aware of some important pre-requisite steps needed before creating a Standby Database of a Primary Database hosted in the Oracle cloud. The example shown is relevant for 11.2, 12.1 and 12.2 Oracle Database Cloud Service builds.


Problem Details

Problem Statement

The Oracle Database Cloud Service builds do not use ORACLE_SID in their directory structure for Tempfiles or Redo Log Members. This means whenever a 2nd Database (e.g. A Dbvisit Standby Database) is created on a Machine with an existing database hosted in the cloud, the Tempfile names and Redo Log members will be the same filename.  If ignored, the problem only presents itself during Graceful Switchover (GS) Or Activation when these files are touched by the Standby site..

In the case of the Redo Logs, the new redo logs files created during GS overwrite the redo log members of the exisiting Database. In the case of Tempfiles, a warning message regarding file in use is returned and the resulting new Primary Database has TEMPORARY tablespaces containing no files.

Applicable Error Code

SQL> ALTER TABLESPACE "TEMP" ADD TEMPFILE '/u04/app/oracle/oradata/temp/pdbseed_temp012017-02-16_07-05-38-AM.dbf' SIZE 104857600 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE UNLIMITED;
ALTER TABLESPACE "TEMP" ADD TEMPFILE '/u04/app/oracle/oradata/temp/pdbseed_temp012017-02-16_07-05-38-AM.dbf' SIZE 104857600 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE UNLIMITED
*
ERROR at line 1:
ORA-01119: error in creating database file
'/u04/app/oracle/oradata/temp/pdbseed_temp012017-02-16_07-05-38-AM.dbf'
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 7143

Affected Versions

Product versions which are affected :  11.2, 12.1 and 12.2

Affected Platforms

Oracle Database Cloud Service  (linux oel6)

 

Description

 

Primary Site: Oracle Database Cloud Service Standby Site: Oracle Database Cloud Service
  • Oracle Linux 6.4 x86_64
  • Oracle Database 12.1.0.2
  • Default Database Build on Service Creation
  • Server Name: LAA
  • Database Name : ORCL 
  • Dbvisit Standby : 8.0.10.18542
  • Oracle Linux 6.4 x86_64
  • Oracle Database 12.1.0.2
  • Default Database Build on Service Creation
  • Server Name : LAA4
  • Database Name : LAA 
  • Standby Database: ORCL
  • Dbvisit Standby : 8.0.10.18542


The default build of a Database given when a new Oracle Database Cloud Service is provisioned has the following Tempfile and Redolog Member location. 

On Server 1: LAA


SQL> select name from v$database;
SQL> select name , database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
ORCL      PRIMARY

SQL> select name from v$tempfile;
NAME
-------------------------------------------------------------------------------
/u04/app/oracle/oradata/temp/temp01.dbf
/u04/app/oracle/oradata/temp/pdbseed_temp012017-02-16_07-05-38-AM.dbf
/u04/app/oracle/oradata/temp/temp012017-02-16_07-05-38-AM.dbf 

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u04/app/oracle/redo/redo02.log
/u04/app/oracle/redo/redo01.log
/u04/app/oracle/redo/redo03.log

On Server 2: LAA4


SQL> select name from v$database;
SQL> select name , database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
LAA      PRIMARY

SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u04/app/oracle/oradata/temp/temp01.dbf
/u04/app/oracle/oradata/temp/pdbseed_temp012017-02-16_07-05-38-AM.dbf
/u04/app/oracle/oradata/temp/temp012017-02-16_07-05-38-AM.dbf 

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u04/app/oracle/redo/redo02.log
/u04/app/oracle/redo/redo01.log
/u04/app/oracle/redo/redo03.log

Therefore when a new Standby Database (ORCL) is created on Server 2 the redo log members and the tempfile names will be the same.  The following error occurs during Graceful Switchover when adding the tempfiles. (extract from switchover trace file).

20170424 04:05:52 main::cmn_ora_check: ============errors start==========
20170424 04:05:52 main::cmn_ora_check: line=1: ORA-01119: error in creating database file '/u04/app/oracle/oradata/temp/temp01.dbf'
20170424 04:05:52 main::cmn_ora_check: line=2: ORA-27086: unable to lock file - already in use

Adding the file manually:

SQL> ALTER TABLESPACE "TEMP" ADD TEMPFILE '/u04/app/oracle/oradata/temp/temp01.dbf' SIZE 104857600 REUSE 
AUTOEXTEND ON NEXT 655360 MAXSIZE UNLIMITED;
ALTER TABLESPACE "TEMP" ADD TEMPFILE '
/u04/app/oracle/oradata/temp/temp01.dbf' SIZE 104857600 REUSE
AUTOEXTEND ON NEXT 655360 MAXSIZE UNLIMITED
*
ERROR at line 1:
ORA-01119: error in creating database file '
/u04/app/oracle/oradata/temp/temp01.dbf'
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 8 Additional information: 7143

The end result being the New Primary Database's  TEMP tablespaces are created with no files.

SQL> select name , database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
ORCL      PRIMARY

SQL> select name, con_id from v$tempfile;
no rows selected

Solution

Prior to creating a Standby Database with Dbvisit Standby on a Oracle Database Cloud Service host with an existing Database, move/rename the Redo log file members to a location with the $ORACLE_SID in the file name.  Alter/Add Temporary files to also contain the ORACLE_SID within the Path.

SQL> select name , database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
ORCL      PRIMARY

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u04/app/oracle/oradata/temp/ORCL/temp01.dbf
/u04/app/oracle/oradata/temp/ORCL/pdbseed/temp01.dbf
/u04/app/oracle/oradata/temp/ORCL/pdb1/temp01.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u04/app/oracle/redo/ORCL/redo02.log
/u04/app/oracle/redo/ORCL/redo01.log
/u04/app/oracle/redo/ORCL/redo03.log

After this, proceed to create the Standby Database using Dbvisit Standby and a Graceful Switchover/Activation will proceed with no issues.


Steps Performed
 
Step 1: Ensure TEMPFILES in each of the TEMPORARY TABLESPACES (12c : Containers Root, Seed and any Pluggables (pdb1 in this example)) have a unique name on the Primary Database.

The one way to achieve this is to make an Directory of $ORACLE_SID off the default location.

[oracle@LAA ~]$ cd /u04/app/oracle/oradata/temp 
[oracle@LAA temp]$ mkdir ORCL 
[oracle@LAA temp]$ mkdir ORCL/pdbseed 
[oracle@LAA temp]$ mkdir ORCL/pdb1
[oracle@LAA temp]$ sqlplus / as sysdba
SQL> -- Status Before

SQL> select name from v$tempfile NAME -------------------------------------------------------------------------------- /u04/app/oracle/oradata/temp/temp01.dbf /u04/app/oracle/oradata/temp/pdbseed_temp012017-02-16_07-05-38-AM.dbf /u04/app/oracle/oradata/temp/temp012017-02-16_07-05-38-AM.dbf SQL> ALTER TABLESPACE "TEMP" ADD TEMPFILE '/u04/app/oracle/oradata/temp/ORCL/temp01.dbf' size 100M;
SQL> -- Remove original Tempfile
SQL> ALTER TABLESPACE "TEMP" DROP TEMPFILE '/u04/app/oracle/oradata/temp/temp01.dbf';
SQL> -- Switch Container and Repeat
SQL> ALTER SESSION SET CONTAINER = PDB$SEED; SQL> ALTER TABLESPACE "TEMP" ADD TEMPFILE '/u04/app/oracle/oradata/temp/ORCL/pdbseed/temp01.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED; SQL> ALTER TABLESPACE "TEMP" DROP TEMPFILE 'u04/app/oracle/oradata/temp/pdbseed_temp012017-02-16_07-05-38-AM.dbf';
SQL> -- Switch Container and Repeat
SQL> ALTER SESSION SET CONTAINER = PDB1; SQL> ALTER TABLESPACE "TEMP" ADD TEMPFILE '/u04/app/oracle/oradata/temp/ORCL/pdb1/temp01.dbf' SIZE 100M; SQL> ALTER TABLESPACE "TEMP" DROP TEMPFILE '/u04/app/oracle/oradata/temp/temp012017-02-16_07-05-38-AM.dbf';
SQL> select name from v$tempfile; NAME ---------------------------------------------------------------- /u04/app/oracle/oradata/temp/ORCL/temp01.dbf /u04/app/oracle/oradata/temp/ORCL/pdbseed/temp01.dbf /u04/app/oracle/oradata/temp/ORCL/pdb1/temp01.dbf
Step 2: Repeat for the RedoLog Members.
[oracle@LAA ~]$ cd /u04/app/oracle/redo
[oracle@LAA redo]$mkdir ORCL 

SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                           IS_ CON_ID
---------- ------- ------- ------------------------------------------------------------ --- ----------
     2     ONLINE  /u04/app/oracle/redo/redo02.log              NO       0
     3     ONLINE  /u04/app/oracle/redo/redo03.log              NO       0
     1     ONLINE  /u04/app/oracle/redo/redo01.log              NO       0

Proceed with a Group that is not ACTIVE
SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
	 1 INACTIVE
	 2 CURRENT
	 3 INACTIVE

Starting with Group 1, copy and renamed to the new location.

e.g
SQL> !cp /u04/app/oracle/redo/redo01.log /u04/app/oracle/redo/ORCL/redo01.log
SQL> alter database rename file '/u04/app/oracle/redo/redo01.log' to '/u04/app/oracle/redo/ORCL/redo01.log';

Database altered.

SQL> select member from v$logfile;
SQL> !rm /u04/app/oracle/redo/redo01.log

MEMBER
------------------------------------------------------------
/u04/app/oracle/redo/redo02.log
/u04/app/oracle/redo/redo03.log
/u04/app/oracle/redo/ORCL/redo01.log


... Alter take care to perform ALTER SYSTEM SWITCH LOGFILE to move the location of the current member.
... Repeat until all renamed
... After a few Switches and Renames
SQL> select member from v$logfile; MEMBER ------------------------------------------------------------ /u04/app/oracle/redo/ORCL/redo02.log /u04/app/oracle/redo/ORCL/redo03.log /u04/app/oracle/redo/ORCL/redo01.log
Step 3: Proceed to Create CSD and perform GS and/or Activation
Have more questions? Submit a request

Comments