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'

Privileges Consideration when Refresh Oracle Schema

When DBA refreshes an Oracle schema or objects, privileges should be taken care carefully , specially for long time running complex environment.

After dropping a schema, all the granted privileges will be lost. So before refresh, DBA should record all the privileges granted to users, roles, etc.

Export a Schema from Source Database

$ expdp Username/Password directory=  dumpfile= logfile= SCHEMAS= FLASHBACK_TIME=systimestamp

Record / Extract the Granted Privileges

Granted Column Privileges

SQL> select * from dba_col_privs where GRANTOR='SCHEMANAME';

Here ‘SCHEMANAME’ is the schema to be refreshed.

Granted Objects Privileges

 SQL>select GRANTEE,OWNER||'.'||TABLE_NAME,GRANTOR,PRIVILEGE,GRANTABLE from dba_tab_privs where GRANTOR ='SCHEMANAME';

Other privileges you may be interested from following views:

  • User_role_privs, dba_role_privs ( Users granted with with roles )
  • User_sys_privs, dba_sys_privs
  • Role_role_privs, role_sys_privs, role_tab_privs
  • Session_privs, session_roles

Refresh Schema

Drop Schema Objects

 SQL> select 'drop '||object_type|| ' '||owner||'.'||object_name ||' ;' from dba_objects where owner='SCHEMANAME';

Import Schema by Data Pump

$ impdp username/password directory= dumpfile= logfile= schemas=sourceschemaname remap_schema= remap_tablespace=

Fix Invalid Objects

Fix all invalid objects, check all synonyms, etc

Collect Stats

SQL>EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'SCHEMANAME');