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: 7143Affected 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 |
|
|
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: 7143The 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.logAfter this, proceed to create the Standby Database using Dbvisit Standby and a Graceful Switchover/Activation will proceed with no issues.
Steps Performed
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
[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
Comments