Author: dbalifeeasy

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'

How To Move Data Guard Broker Configuration Files Onto ASM in 19c

By default, the broker configuration files of Oracle Data Guard are created onto local filesystem. In GI environment, we’d like those files are located in ASM filesystem.

Check Current Configurations 

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/dr1TESTDB.dat
dg_broker_config_file2               string      /u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/dr2TESTDB.dat

Stop the Broker 

SQL> alter system set dg_broker_start = FALSE;

Set the dg_broker_config_file1 & 2 parameters to the appropriate location required

SQL> alter system set dg_broker_config_file1 = '+DATAC1/TESTDB/dr1TESTDB.dat';
SQL> alter system set dg_broker_config_file2 = '+DATAC1/TESTDB/dr2TESTDB.dat';

Restart the Broker 

SQL> alter system set dg_broker_start = TRUE

Finally, the broker configuration files will be created in the new ASM location.

ASMCMD> pwd
+DATAC1/TESTDB
ASMCMD> ls -l dr*
Type             Redund  Striped  Time             Sys  Name
DATAGUARDCONFIG  HIGH    COARSE   JAN 13 11:00:00  N    dr1testdb.dat => +DATAC1/TESTDB/DATAGUARDCONFIG/TESTDB.979.1126004761
DATAGUARDCONFIG  HIGH    COARSE   JAN 13 11:00:00  N    dr2testdb.dat => +DATAC1/TESTDB/DATAGUARDCONFIG/TESTDB.980.1126004761
ASMCMD>

ExaCC: How to Run ExaCLI

ExaCLI offers a subset of the commands found in the on-premises Exadata command line utility. The utility runs on the database compute nodes in the Exadata Cloud Service.

Find the Name of the VM Cluster

Run the following command as GI user ( grid ).

[grid@racnode1 ~]$ crsctl get cluster name
CRS-6724: Current cluster name is 'usa2598clu01cb1'

Find Password

The password for cloud_user_clustername is initially set to a random value, which you can view by running the following command as as opc user on any cluster node.

[opc@racnode1 ~]$ /opt/exacloud/get_cs_data.py -h
usage: get_cs_data.py [-h] [--dataonly]
Obtain secrets stored during provisioning
optional arguments:
  -h, --help  show this help message and exit
  --dataonly  data only output

[opc@racnode1 ~]$ /opt/exacloud/get_cs_data.py --dataonly
5k3ew9qYTnKb#LD

As per Oracle doc, the following command should be run but not working, it seems due to ‘get_cs_data.py’ version difference.

[opc@racnode1 ~]$ /opt/exacloud/get_cs_data.py --data_file /opt/exacloud/cs_data.enc
usage: get_cs_data.py [-h] [--dataonly]
get_cs_data.py: error: unrecognized arguments: --data_file /opt/exacloud/cs_data.enc

Connect to a Storage Server with ExaCLI

Find Target Storage Server’s IP Address

[root@racnode1 ~]#cat /etc/oracle/cell/network-config/cellip.oracle
cell="192.168.136.5;cell="192.168.136.6"
cell="192.168.136.7;cell="192.168.136.8"
cell="192.168.136.9;cell="192.168.136.10"
...
..
.

Run ExaCLI Command

#exacli -c cloud_user_usa2598clu01cb1@192.168.136.6 -e LIST IORMPLAN DETAIL

Password: ***************
         name:                   usa2598clu01cb1_IORMPLAN
         catPlan:
         dbPlan:
         clusterPlan:
         objective:              auto
         status:                 active

If you are connecting to a storage cell for the first time using ExaCLI, you may be prompted to accept an SSL certificate. The ExaCLI output in this case will look like the following:

#exacli -c cloud_user_usa2598clu01cb1@192.168.136.6 -e LIST IORMPLAN DETAIL
EXA-30016: This connection is not secure. You have asked ExaCLI to connect to cell 192.168.136.6 securely. The identity of 192.168.136.6 cannot be verified.
Got certificate from server:
C=US,ST=California,L=Redwood City,O=Oracle Corporation,OU=Oracle Exadata,CN=localhost
Do you want to accept and store this certificate? (Press y/n)

Accept the self-signed Oracle certificate by pressing “y” to continue using ExaCLI.

Process RSM0, PID = 23456, will be killed

Standby database DG log:

11/09/2022 11:53:34
Creating process RSM0
11/09/2022 11:53:37
Process RSM0 re-created with PID = 20918
11/09/2022 11:59:37
Process RSM0, PID = 20918, will be killed
11/09/2022 11:59:51

Primary database DG log:

Data Guard Broker Status Summary:
Type Name Severity Status
Configuration DG_CDRDB Warning ORA-16607
Primary Database PCDRDB Error ORA-16778
Physical Standby database SCDRDB Error ORA-16810

SOLUTION

  1. Check and resolve network issues between primary db server and standby server.
  2. Increase the OperationTimeout property value :
DGMGRL> show configuration OperationTimeout;
  OperationTimeout = '30'
 
DGMGRL> EDIT CONFIGURATION SET PROPERTY OperationTimeout=90;
Property "operationtimeout" updated

DGMGRL>  show configuration OperationTimeout;
  OperationTimeout = '90'