Category: Oracle

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');

Column Privileges in Oracle

Table column privileges include only INSERT,UPDATE without SELECT unfortunately.

How to Grant Column Privileges ?

SQL> GRANT update (columnname1),insert (columnname2, columnname3)  ON user1.table1 TO user2;

How to Check Column Privileges ?

SQL> select * from dba_col_privs ;

How to Remove Column Privileges ?

SQL> revoke insert (columnname2, columnname3) ON user1.table1 from user2;
revoke insert (columnname2, columnname3) ON user1.table1 from user2;
              *
ERROR at line 1:
ORA-01750: UPDATE/REFERENCES may only be REVOKEd from the whole table, not by column

SQL> revoke insert  on  user1.table1 from user2;
Revoke succeeded.

Upgrade RMAN Catalog Fails with “RMAN-01005: Error while converting X lock to S lock”

The following errors occur while upgrading RMAN catalog from version 19.10 to version 19.16: 

RMAN> upgrade catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> UPGRADE CATALOG;


recovery catalog upgraded to version 19.16.00.00.00
DBMS_RCVMAN package upgraded to version 19.16.00.00
DBMS_RCVCAT package upgraded to version 19.16.00.00.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-01005: Error while converting X lock to S lock

INVESTIGATION

Check the RMAN debug trace file with following messages:

...
..
.
DBGSQL: RCVCAT> declare ret integer; str varchar2(120); begin str := 'begin :ret := '||dbms_catowner||'.get_lock_on_catalog(dbms_lock.S_MODE); end;'; EXECUTE IMMEDIATE str USING OUT ret; :retlock := ret; end;
DBGSQL: sqlcode = 6550
DBGSQL: B :retlock = NULL
DBGSQL: error: ORA-06550: line 1, column 50: (krmkosqlerr)
DBGSQL: PLS-00201: identifier 'DBMS_LOCK' must be declared (krmkosqlerr)
DBGSQL: ORA-06550: line 1, column 9: (krmkosqlerr)
DBGSQL: PL/SQL: Statement ignored (krmkosqlerr)
DBGSQL: ORA-06512: at line 1 (krmkosqlerr)
...
..
.

SOLUTION

Grant execute privilege to RMAN user, here we assume it is ‘rman’ user used in most environment.

SQL> grant execute on DBMS_LOCK to rman;

Grant succeeded.
RMAN>  UPGRADE CATALOG;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN>  UPGRADE CATALOG;

recovery catalog upgraded to version 19.16.00.00.00
DBMS_RCVMAN package upgraded to version 19.16.00.00
DBMS_RCVCAT package upgraded to version 19.16.00.00.