Category: Configuration

Copy ASM SPFILE From ASM Diskgroup to File System

ASMCMD> cd +DATAC1/TESTDB/PARAMETERFILE
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
PARAMETERFILE HIGH COARSE APR 25 23:00:00 Y spfile.921.1133441623


ASMCMD> pwd
+DATAC1/TESTDB/PARAMETERFILE
ASMCMD> ls spfile.921.1133441623
spfile.921.1133441623
ASMCMD> cp spfile.921.1133441623 /tmp/testdb_spfile.921.1133441623
copying +DATAC1/TESTDB/PARAMETERFILE/spfile.921.1133441623 -> /tmp/testdb_spfile.921.1133441623
ASMCMD> ls spfile.921.1133441623
spfile.921.1133441623
ASMCMD> exit

$ls -ltr /tmp/testdb_spfile.921.1133441623
-rw-r—–. 1 grid oinstall 6656 Apr 26 10:11 /tmp/testdb_spfile.921.1133441623

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Wed Apr 26 10:12:40 2023
Version 19.18.0.0.0

Copyright (c) 1982, 2022, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.18.0.0.0

SQL> create pfile=’/tmp/initTESTDB.ora’ from spfile=’/tmp/testdb_spfile.921.1133441623′;

File created.

SQL> ! ls -ltr /tmp/initTESTDB.ora
-rw-r–r–. 1 grid oinstall 2564 Apr 26 10:13 /tmp/initTESTDB.ora

SQL> ! cat /tmp/initTESTDB.ora

Advertisement

database spfile created in wrong place under ASM

SYMPTOMS

DBA is trying to create a new spfile from a pfile as following :

SQL> create SPFILE='+DATAC1/TESTDB/' from pfile='/home/oracle/testdb.pfile';
File created.

Under ASM , the spile is link to the wrong location under ASM:

ASMCMD> ls -alr
Type Redund Striped Time Sys Name
N spfile =>
+DATAC1/DB_UNKNOWN/PARAMETERFILE/SPFILE.272.613162051
ASMCMD>

OR

ASMCMD> ls -alr
Type Redund Striped Time Sys Name
N spfile =>
+DATAC1/ASM/PARAMETERFILE/spfile.4186.1126528877
ASMCMD>

CAUSE

This is because the database instance has no open client session to the ASM instance at the time of the ‘create spfile’ command. That is, the ASM instance is not aware of the db name and therefore uses “DB_UNKNOWN” or ‘ASM’.

SOLUTION

When database in OPEN or MOUNT status, make sure  there is at least one connection to the ASM instance. 

SQL>select * from v$asm_client;

GROUP_NUMBER INSTANCE_NAME
------------ ----------------------------------------------------------------
DB_NAME  CLUSTER_NAME                    STATUS
-------- ------------------------------- ------------
SOFTWARE_VERSION
------------------------------------------------------------
COMPATIBLE_VERSION                                               CON_ID
------------------------------------------------------------ ----------
           0 +ASM1
TESTDB_ usa1234clu07cb8                 CONNECTED
19.0.0.0.0
19.0.0.0.0                                                            0

— Create spfile from a pfile

SQL>  create spfile='+datac1' from pfile='/home/oracle/testdb.pfile';

File created.

— check spfile is created in the right directory now

ASMCMD> pwd
+datac1
ASMCMD> cd TESTDB/
ASMCMD> ls PARAMETERFILE/
spfile.4186.1126530135

— configure TESTDB database with new spfile

$ srvctl modify  database -d TESTDB -spfile  '+DATAC1/TESTDB/PARAMETERFILE/spfile.4186.1126530135'

Cloning an Oracle Database Home in 18c

Here is an example of how to clone an Oracle Home in 18c.  This method is useful if the Oracle home that you are cloning has had patches applied to it. When you clone the Oracle home, the new Oracle home has the patch updates.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Automating Oracle Database Startup and Shutdown by Linux Systemd Service

PURPOSE

Oracle recommends to configure the system to automatically start Oracle Database when the system starts, and to automatically shut it down when the system down.

Environment:

  • Operating System:     Oracle Linux 7
  • Oracle Database:        Oracle 18c

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe