Category: General

Oracle Active Data Guard on AWS RDS

On Aug 23, 2022, AWS announced support for managed Oracle Data Guard Switchover and Automated Backups for replicas. See the announcement at:

AWS RDS for Oracle now supports managed Oracle Data Guard

Oracle DBAs are familiar with creating a physical standby in an on-premises environment and opening it in read-only (i.e. Active Data Guard) mode. AWS has now automated the process of creating the standby and the process of switching over in the context of an RDS (i.e. managed database service). All the manual tasks of taking an RMAN backup, transferring to the standby, restoring and recovering the database, setting up Data Guard Manager, etc. can be accomplished by a few clicks on the AWS console. This blog describes the steps required to create and test this scenario.

Prerequisites
1. The database instance size must be db.t3.large at the minimum.
2. The primary database should be using an option group that is exclusive to this database.
3. Ensure you have the required KMS key in both regions
4. Create a parameter group in the target region that exactly matches the parameter group from the source region

Creating a replica
After you have created a primary database in any region:
1. Select the primary database and under the actions button, select “Create replica”.
2. Choose multi-AZ on the target also
3. Click on the create replica button. Replica creation will take approximately 1.5 hours. This may vary based on the size of the database.
4. Change the backup option on the replica. The replica will be created with the backup retention period set to “0” as shown below. This is contrary to the standard practice of not backing up a data guard standby. However, for the replica to switch over to primary at some point in the future, the retention period should be greater than “0”.

Performing a switchover across regions
A switchover is a role reversal between the primary database and one of its standby databases. A switchover guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role.

1. Confirm there is no lag. This can be done via the console under the “Replication” section under the “Lag” column or via SQL

SELECT ARCH.thread#                        "Thread",
       ARCH.sequence#                      "Last Sequence Received",
       APPL.sequence#                      "Last Sequence Applied",
       ( ARCH.sequence# - APPL.sequence# ) "Difference"
FROM   (SELECT thread#,
               sequence#
        FROM   v$archived_log
        WHERE  ( thread#, first_time ) IN (SELECT thread#,
                                                  Max(first_time)
                                           FROM   v$archived_log
                                           GROUP  BY thread#)) ARCH,
       (SELECT thread#,
               sequence#
        FROM   v$log_history
        WHERE  ( thread#, first_time ) IN (SELECT thread#,
                                                  Max(first_time)
                                           FROM   v$log_history
                                           GROUP  BY thread#)) APPL
WHERE  ARCH.thread# = APPL.thread#
ORDER  BY 1;

2. Confirm that there is no pending maintenance on either the primary of the replica

3. Initiate the switchover from the replica (not the primary!).

4. Select the RDS instance and click on “Switch over replica”. And agree to the warning panel that is displayed

5. The former replica will display the message “The Switchover to the read replica started” under “Logs & events”, “Recent events” section. (Remember to sort the messages such that the latest messages are displayed.)

6. After the switchover is complete, both databases will display “The Switchover to the read replica finished successfully”

7. Applications will have to reconnect to the databases

8. You can confirm that the data is replicating from the new primary via the console or SQL

Test reboot with multi-AZ failover – Primary
Initiate a reboot with failover on the current primary.

1. Select the RDS instance, under “Actions”, and click on “Reboot”. Check the option “Reboot With Failover?”

2. After about 8 to 10 minutes, the database will complete the multi-AZ failover. Under “Logs & events”, “Recent events” section, look for messages “Multi-AZ instance failover started” and “Multi-AZ instance failover completed”.

A similar test can be done on the Replica.

Observations
1. After a switchover, both the databases may show as Read-Only. This can be ignored

2. You cannot temporarily shut down the database for the customary 7 days as it has a read replica

3. Ensure that there is no pending maintenance before issuing a switchover

4. In the event that you are using a modified character set on your primary, the console may show the replica’s character set as “AL32UTF8”. However, in the database, it matches the primary in the database. This can be confirmed with

select  property_value 
from    DATABASE_properties 
where   property_name='NLS_CHARACTERSET'
;

5. The console will display a message that the switchover is complete. However, this is displayed prematurely. The actual switchover will take additional time. Monitor the status of the two database instances under the “Status” column.

Trigger with a subprogram in PL/SQL

Last week I had a requirement to create a trigger on a table that had to perform some logic and then run additional updates based on the outcome of the logic. The logic ran into multiple IF statements resulting in long messy code. Additionally, there was second requirement to execute this chunk of code from two places within the trigger functionality. I moved the code to a subprogram within the trigger’s PL/SQL logic resulting in a modular structure. This is a simplified example of the trigger code to demonstrate the technique.

The below lines declare a subprogram with some logic that creates an update statement. The EXECUTE IMMEDIATE is commented out for testing and demonstration:

  PROCEDURE perform_calculations(cust_num number) IS
    BEGIN 

        v_upd_sql :='';

		IF  :OLD.CUST_ZIP <> :NEW.CUST_ZIP THEN  
		    v_upd_sql := ' UPDATE DEAN_CUSTOMER SET CUST_ZIP_OLD = ' || :OLD.CUST_ZIP || ' WHERE CUST_NUM = ''' || CUST_NUM || '''';
   	    END IF;  

		--EXECUTE IMMEDIATE v_upd_sql;
		dbms_output.put_line('SQL --->' || v_upd_sql);  
    END;

The below line of code calls the subprogram:

        perform_calculations(:old.cust_num);

In the real-world scenario, I was working on, the code issued a BULK COLLECT similar to below and ran the subprogram for each row collected:

        v_main_sql := ' SELECT ROWID FROM ' || v_table_name || 
                      ' WHERE COL01 = ' :OLD.COL01;

        EXECUTE IMMEDIATE v_main_sql BULK COLLECT INTO var01;
        FOR i IN 1..var01.COUNT
        LOOP
            perform_calculations(var01(i));
        END LOOP;

“var01” was defined as shown below:

TYPE var01_type IS TABLE OF ROWID;
Var01 var01_type;

Code

create or replace TRIGGER UPDATE_TRIGGER AFTER UPDATE ON DEAN_CUSTOMER FOR EACH ROW 
DECLARE 
  v_upd_sql         VARCHAR2(8000); 

  table_not_found EXCEPTION;  
  PRAGMA EXCEPTION_INIT (table_not_found, -942);  

  PROCEDURE perform_calculations(cust_num number) IS
    BEGIN 

        v_upd_sql :='';

		IF  :OLD.CUST_ZIP <> :NEW.CUST_ZIP THEN  
		    v_upd_sql := ' UPDATE DEAN_CUSTOMER SET CUST_ZIP_OLD = ' || :OLD.CUST_ZIP || ' WHERE CUST_NUM = ''' || CUST_NUM || '''';
   	    END IF;  

		--EXECUTE IMMEDIATE v_upd_sql;
		dbms_output.put_line('SQL --->' || v_upd_sql);  
    END;

    BEGIN 
        dbms_output.put_line(' ');  
        dbms_output.put_line('********** Trigger DC invoked **********');  
        dbms_output.put_line(' ');  
        perform_calculations(:old.cust_num);
END;

Table definition

CREATE TABLE DEAN_CUSTOMER
(
  “CUST_NUM"     NUMBER(8,0),
  "CUST_NAME"    VARCHAR2(10 BYTE), 
  "CUST_ZIP"     NUMBER(12,0),
  "CUST_ZIP_OLD" NUMBER(12,0)
) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  NOCOMPRESS LOGGING
  TABLESPACE "USERS"
;

Finding the sizes of LOBs and listing the associated table and columns

I created the below SQL to identify the largest LOB objects and associate them back to the respective table and column:

set pages 99
set lines 200
col owner           format a12
col table_name      format a25
col column_name     format a25
col segment_name    format a30
col partition_name  format a15
SELECT      tc.owner,
            tc.table_name,
            tc.column_name,
            lb.segment_name,
            sg.partition_name,
            Round(( sg.bytes / (1024*1024*1024)),2) AS size_gb
FROM        dba_tab_cols tc
inner join  dba_lobs lb
        ON  lb.owner        =  tc.owner
       AND  lb.table_name   =  tc.table_name
       AND  lb.column_name  =  tc.column_name
inner join  dba_segments sg
        ON  sg.segment_name =  lb.segment_name
WHERE       tc.owner        = 'schema owner here'
  AND       tc.data_type LIKE '%LOB%'
  and       Round(( sg.bytes / (1024*1024*1024)),2) > 50
ORDER  BY 
            size_gb DESC,
            tc.owner,
            tc.table_name,
            tc.column_name,
            lb.segment_name
;

ORA-00059: maximum number of DB_FILES exceeded

I got paged over the weekend for an Enterprise Edition Release 19.0.0.0.0 RAC database running out of space. When I attempted to add a new data file with the commands:

alter tablespace USERS add datafile '+DATA' size 31G;

the SQL failed with the response

Attempted to add a datafile but encountered
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

I checked the DB_FILES parameter with the command:

SQL> show parameter db_files;
 
NAME           TYPE        VALUE
-------------- ----------- -----
db_files       integer     100

Check the number of data files with the below SQL:

SQL> select count(*) from dba_data_files;
 
  COUNT(*)
----------
       100

This parameter is not dynamically modifiable, so I issued the command:

alter system set db_files=125 scope=spfile sid='*';

and attempted to do a rolling bounce with:

srvctl stop instance -d PRODDB -i PRODDB4 -o immediate

This resulted in the error

CRS-2974: unable to act on resource 'ora.PRODDB.db' on server 'prodbox87' because that would require stopping or relocating resource 'ora.PRODDB.PRODDB_svc.svc' but the appropriate force flag was not specified

I checked the status of the services with:

srvctl status service -d PRODDB
Service PRODDB_svc is running on instance(s) PRODDB1,PRODDB2,PRODDB3,PRODDB4

Since the service was configured to run on the other nodes, I stopped the service on node 4 with:

srvctl stop service -d PRODDB -i PRODDB4

and retried the rolling bounce. The instance shutdown successfully but did not come back up because of the below error:

srvctl start instance -d PRODDB -i PRODDB4
PRCR-1013 : Failed to start resource ora.PRODDB.db
PRCR-1064 : Failed to start resource ora.PRODDB.db on node prodbox87
CRS-5017: The resource action "ora.PRODDB.db start" encountered the following error:
ORA-01105: mount is incompatible with mounts by other instances
ORA-01174: DB_FILES is 125 buts needs to be 100 to be compatible
. For details refer to "(:CLSN00107:)" in "/oracle/product/diag/crs/prodbox87/trace/crsd_oraagent_oracle.trc".
 
CRS-2674: Start of 'ora.PRODDB.db' on 'prodbox87' failed

At this point I was forced to perform a bounce of all nodes of the RAC after which I was able to add a new data file.

Moving a table from one user/schema to another

I had a requirement to move a table from one user (i.e. schema) to another. As this is not possible with an ALTER, the usual approach is to do CREATE TABLE AS or an export-import. Depending on the volume of data, this may take a long time. I found this interesting approach on google and decided to try it

Can you move a table between schemas ?

Many thanks to the person that identified this approach.

My test was as follows:

As sysdba, I created the below users. The assumption is user JIM needs to be dropped but his table named JIMS_TABLE needs to be transferred to user BOB. User Mike is a user who has select on JIM.JIMS_TABLE. The grant on DBA_TABLES to JIM is only to facilitate the creation of a test table. It has no other relevance to the test.

create user jim identified by N0t_Jim#01#  TEMPORARY TABLESPACE TEMP DEFAULT TABLESPACE PRODTSPACE;
grant connect to jim;
GRANT UNLIMITED TABLESPACE TO jim;
grant create table to jim;
grant select on dba_tables to jim;
 
 
create user bob identified by N0t_Bob#01#  TEMPORARY TABLESPACE TEMP DEFAULT TABLESPACE PRODTSPACE;
grant connect to bob;
GRANT UNLIMITED TABLESPACE TO bob;
grant create table to bob;
grant select on dba_tables to bob;
 
 
create user mike identified by N0t_Mike#01#  TEMPORARY TABLESPACE TEMP DEFAULT TABLESPACE PRODTSPACE;
grant connect to mike;

As user Jim, create a table, grant select to user MIKE.

create table jim.jims_table
tablespace PRODTSPACE
as
select * from dba_tables
;
 
GRANT SELECT ON JIM.JIMS_TABLE TO MIKE;

The table now exists

col owner format a5;
col table_name format a10;
select
owner, table_name, tablespace_name, partitioned, row_movement
from dba_tables
where owner in ('JIM','BOB')
;
 
OWNER TABLE_NAME TABLESPACE_NAME                PAR ROW_MOVE
----- ---------- ------------------------------ --- --------
JIM   JIMS_TABLE PRODTSPACE                     NO  DISABLED

With the following grants:

set pages 99;
col grantee  format a10;
col owner format a10;
col table_name format a10;
col grantor  format a10;
col PRIVILEGE format a17;
 
SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE  owner in ('JIM','BOB')
order by grantee
;
 
GRANTEE    OWNER      TABLE_NAME GRANTOR    PRIVILEGE
---------- ---------- ---------- ---------- -----------------
BOB        JIM        JIMS_TABLE JIM        ALTER
BOB        JIM        JIMS_TABLE JIM        DELETE
BOB        JIM        JIMS_TABLE JIM        INDEX
BOB        JIM        JIMS_TABLE JIM        INSERT
BOB        JIM        JIMS_TABLE JIM        SELECT
BOB        JIM        JIMS_TABLE JIM        UPDATE
BOB        JIM        JIMS_TABLE JIM        FLASHBACK
BOB        JIM        JIMS_TABLE JIM        REFERENCES
BOB        JIM        JIMS_TABLE JIM        READ
BOB        JIM        JIMS_TABLE JIM        ON COMMIT REFRESH
BOB        JIM        JIMS_TABLE JIM        QUERY REWRITE
BOB        JIM        JIMS_TABLE JIM        DEBUG
MIKE       JIM        JIMS_TABLE JIM        SELECT

In preparation to move the table, user JIM grants all on the table to the future new owner BOB.

GRANT ALL ON JIM.JIMS_TABLE TO BOB;

Next, JIM generates the DDL of the table

set long 9999999 pages 0 heading off pagesize 0;
SELECT dbms_metadata.get_ddl('TABLE','JIMS_TABLE') FROM dual;

Give the DDL to user BOB, change the table owner and add a partitioning clause (see the last line of DDL). In order to reduce the size of the DDL, I have deleted many of the columns.

  CREATE TABLE "BOB"."JIMS_TABLE"
   (    "OWNER" VARCHAR2(128) NOT NULL ENABLE,
        "TABLE_NAME" VARCHAR2(128) NOT NULL ENABLE,
        "TABLESPACE_NAME" VARCHAR2(30),
        .
        .
        .
        Columns deleted
        .
        .
        .
        "DATA_LINK_DML_ENABLED" VARCHAR2(3),
        "LOGICAL_REPLICATION" VARCHAR2(8)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PRODTSPACE"
  partition BY hash (OWNER) (partition p1);

We now have two tables

col owner format a5;
col table_name format a10;
select
owner, table_name, tablespace_name, partitioned, row_movement
from dba_tables
where owner in ('JIM','BOB')
;
 
OWNER TABLE_NAME TABLESPACE_NAME                PAR ROW_MOVE
----- ---------- ------------------------------ --- --------
JIM   JIMS_TABLE PRODTSPACE                     NO  DISABLED
BOB   JIMS_TABLE                                YES DISABLED

Now to swap the data from JIM’S table to the new BOB table

ALTER TABLE BOB.JIMS_TABLE exchange partition p1 WITH TABLE JIM.JIMS_TABLE;

The data is now in the new table

SELECT COUNT(*) FROM JIM.JIMS_TABLE;
SELECT COUNT(*) FROM BOB.JIMS_TABLE;
 
SQL> SELECT COUNT(*) FROM JIM.JIMS_TABLE;
 
  COUNT(*)
----------
         0
 
SQL> SELECT COUNT(*) FROM BOB.JIMS_TABLE;
 
  COUNT(*)
----------
      2228

The grants will have to be re-granted on the new table named BOB.JIMS_TABLE;

GRANTEE    OWNER      TABLE_NAME GRANTOR    PRIVILEGE
---------- ---------- ---------- ---------- -----------------
BOB        JIM        JIMS_TABLE JIM        ALTER
BOB        JIM        JIMS_TABLE JIM        DELETE
BOB        JIM        JIMS_TABLE JIM        INDEX
BOB        JIM        JIMS_TABLE JIM        INSERT
BOB        JIM        JIMS_TABLE JIM        SELECT
BOB        JIM        JIMS_TABLE JIM        UPDATE
BOB        JIM        JIMS_TABLE JIM        REFERENCES
BOB        JIM        JIMS_TABLE JIM        READ
BOB        JIM        JIMS_TABLE JIM        ON COMMIT REFRESH
BOB        JIM        JIMS_TABLE JIM        QUERY REWRITE
BOB        JIM        JIMS_TABLE JIM        DEBUG
BOB        JIM        JIMS_TABLE JIM        FLASHBACK
MIKE       JIM        JIMS_TABLE JIM        SELECT

Large number of deletes causing table fragmentation resulting in poor query performance

I was asked to look into a nightly script that had significantly increased in elapsed time. The script was rather simple in that it deleted rows older than (today’s date – 3 months) from multiple tables. I ran an ADDM report on the database (12.1.0.2.0 – 64bit Production), and one of the deletes popped up in the report:

   Action
      Run SQL Tuning Advisor on the DELETE statement with SQL_ID
      "75csungpxhvv6".
      Related Object
         SQL statement with SQL_ID 75csungpxhvv6.
         delete from PROD_SCHEMA.PROD_TABLE where ORDER_DT<20201212
   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      I/O and Cluster wait for INDEX "PROD_SCHEMA.PROD_TABLE_ORDER_DT" with object ID
      322364 consumed 100% of the database time spent on this SQL statement.

The table in question was not large; a few 100 thousand rows with reasonable row length; no large varchars etc. The obvious issues were quickly eliminated; the table has current stats, there was an index to exactly match the predicate, and the optimizer was choosing the index. However, I did notice that the table was occupying 28 GB of space. A quick calculation of # of rows multiplied by row length indicated that it should be occupying less than 1 GB of space approx.

I ran the below query and confirmed that most of the space was empty

set line 200;
set pages 9999;
col owner                format a15               heading "Owner"
col table_name           format a30               heading "Table Name"
col avg_row_len          format 999,999,999       heading "Avg Row Len"
col num_rows             format 999,999,999,999   heading "Number of Rows"
col TOTAL_SIZE_MB        format 999,999,999       heading "Total|Size|(MB)"
col ACTUAL_SIZE_MB       format 999,999,999       heading "Actual|Size|(MB)"
col FRAGMENTED_SPACE_MB  format 999,999,999       heading "Frag|Size|(MB)"
col percentage           format 999.99            heading "Per|Cent|Frag"
SELECT   owner
        ,table_name
        ,avg_row_len
        ,num_rows
        ,Round((( blocks * 8 / 1024 )), 2) "TOTAL_SIZE_MB"
        ,Round(( num_rows * avg_row_len / 1024 / 1024 ), 2)  "ACTUAL_SIZE_MB"
        ,Round(( ( blocks * 8 / 1024 ) - ( num_rows * avg_row_len / 1024 / 1024 ) ), 2) "FRAGMENTED_SPACE_MB"
        ,Round(( Round(( ( blocks * 8 / 1024 ) - ( num_rows * avg_row_len / 1024
                                                  / 1024
                                                ) ), 2
                     )
                     / Round((( blocks * 8 / 1024 )), 2) ) * 100, 2)  "percentage"
FROM     dba_tables
WHERE    num_rows > 0
  and    table_name = 'PROD_TABLE'
  and    owner = ' PROD_SCHEMA '
order by FRAGMENTED_SPACE_MB desc 
;

We were able to ask the application for a small outage to export (data pump), truncate, and import that data from the table. After this, the query that used to take almost 8 minutes started executing in less than a second.

ORA-38882 on standby database

We ran into an interesting situation recently when MRP failed on a standby with the error:

ORA-38882: Cannot drop tablespace UNDOTBS2 on standby database due to guaranteed restore points.

The background was that we had to remove a node from our primary RAC to upgrade the server. We did not remove any nodes from the standby. After the node was removed from the primary, MRP failed on the standby with the above error. As far as we could remember or check, we did not have a guaranteed restore point on the database. A google search led us to Mike Dietrich’s Blog, where he had responded to a similar error in a different context. The link is

Mike Dietrich’s Blog About Oracle Database

We were not using a PDB, but since we did not have any other ideas, we decided to try Mike Dietrich’s solution and issued the following alter:

alter system set "_allow_drop_ts_with_grp"=true;

After the above alter, the database was bounced, and MRP started successfully.

ORA-31604: invalid transform NAME parameter “MODIFY” for object type PROCACT_INSTANCE in function ADD_TRANSFORM

While performing an import data pump operation into an Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production we encountered the below error:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_15" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_15":  "/******** AS SYSDBA" directory=exp_dir dumpfile=resolvecode.dmp remap_table=myuser.accounting_codes:accounting_codes_prod 
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOAD_MD_TRANSFORMS [] 
ORA-31604: invalid transform NAME parameter "MODIFY" for object type PROCACT_INSTANCE in function ADD_TRANSFORM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 9710

This issue is documented on Oracle support at

Impdp Fails With ORA-39126 ORA-31604 Iinvalid Transform NAME Parameter "MODIFY" For Object Type PROCACT_INSTANCE in function ADD_TRANSFORM (Doc ID 1596495.1)

The solution is to change the import data pump command from:

impdp \'/ as sysdba \' dumpfile=resolvecode.dmp directory=exp_dir remap_table=myuser.accounting_codes:accounting_codes_prod

to:

impdp \'/ as sysdba \' dumpfile=resolvecode.dmp directory=exp_dir remap_table=myuser.accounting_codes:accounting_codes_prod exclude=PROCACT_INSTANCE

The use of the exclude=PROCACT_SYSTEM will exclude the resource manager objects such as resource plans and groups.

There is also a patch available, but the addition of exclude=PROCACT_INSTANCE solved my issue faster than patching.

CRS-6706: Oracle Clusterware Release patch level (‘xxxxxxxx’) does not match Software patch level (‘xxxxxxxxx’). Oracle Clusterware cannot be started

After applying the October 2020 patch to a 12c GI, we were attempting to bring up CRS and got the below error message:

CRS-6706: Oracle Clusterware Release patch level (‘xxxxxxxx’) does not match Software patch level (‘xxxxxxxxx’). Oracle Clusterware cannot be started

This is described in Oracle support document 1639285.1

We stopped CRS on all nodes and performed the below on the RAC node that reported the error:

Sudo to root and navigate to the grid home/install directory and run

./rootcrs.pl –unlock

The output will look like:

Using configuration parameter file: ./crsconfig_params
2021/01/15 00:52:44 CLSRSC-4012: Shutting down Oracle Trace File Analyzer (TFA) Collector.
 
2021/01/15 00:52:55 CLSRSC-4013: Successfully shut down Oracle Trace File Analyzer (TFA) Collector.
 
2021/01/15 00:52:56 CLSRSC-347: Successfully unlock /grid_home/12.2.0.2

After the above, run the patch command:

./rootcrs.pl –patch

The output will look like:

Using configuration parameter file: ./crsconfig_params
2021/01/15 00:53:12 CLSRSC-4015: Performing install or upgrade action for Oracle Trace File Analyzer (TFA) Collector.
 
2021/01/15 00:53:12 CLSRSC-4005: Failed to patch Oracle Trace File Analyzer (TFA) Collector. Grid Infrastructure operations will continue.
 
2021/01/15 00:53:24 CLSRSC-329: Replacing Clusterware entries in file 'oracle-ohasd.conf'
 
CRS-4123: Oracle High Availability Services has been started.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'pdcc04-d03'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'pdcc04-d03'
CRS-2677: Stop of 'ora.drivers.acfs' on 'pdcc04-d03' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'pdcc04-d03' has completed
CRS-4133: Oracle High Availability Services has been stopped.

After this, we were able to start CRS on all nodes of the RAC.

Export datapump encounters error ORA-39126 and ORA-01690

We ran across the following error while running a metadata-only export data pump on an 11.2.0.4 database:

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [PROCA
CT_SCHEMA:"SYSMAN"]
ORA-01690: sort area size too small
 
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 9715
 
----- PL/SQL Call Stack -----
object line object
handle number name
0x11d4c2bf0 21979 package body SYS.KUPW$WORKER
0x11d4c2bf0 9742 package body SYS.KUPW$WORKER
0x11d4c2bf0 11838 package body SYS.KUPW$WORKER
0x11d4c2bf0 2808 package body SYS.KUPW$WORKER
0x11d4c2bf0 10422 package body SYS.KUPW$WORKER
0x11d4c2bf0 1824 package body SYS.KUPW$WORKER
0x11df95480 2 anonymous block
 
Job "SYS"."SYS_EXPORT_FULL_21" stopped due to fatal error at Wed Dec 23 18:13:11
2020 elapsed 0 00:05:34

According the Oracle Support Doc ID 1358890.1, this is a known bug that occurs after applying patch set 11.2.0.2. There are some solutions in Doc ID 1358890.1 including upgrading to 12.1, applying patch set 11.2.0.4 or an interim patch set. Another solution is to 

update sys.metaview$ set properties = properties + 1024 where viewname = 'KU$_PROCACT_SYS_VIEW';

We tried the update option, including increasing the size up to 4096. However, this did not resolve the error. After some trial and error, we were able to complete the export with the below command:

expdp \'/ as sysdba \' directory=exp_dir dumpfile=rpt.dmp exclude=statistics exclude=schema:\"IN \(\'SYSMAN\',\'SYSTEM\',\'VRRPT1NE\',\'SYS\'\)\" full=y content=metadata_only