Year: 2018

Prior to Oracle 12c, the password file was stored in ORACLE_HOME/dbs directory. In case of a RAC environment (without ACFS) we would copy this file to each node of the RAC. In the case of a RAC standby this file would also be copied to all the nodes of the standby RAC. 12c now stores this file in ASM which means we only need one copy of the file per RAC cluster.

If you display the database configuration with command

srvctl config database -d CUSTPR

one of the displayed parameters will be the location of the file in ASM:

Password file: +DG_ORA_DATA/CUSTPR/orapwCUSTPR

Despite the above file name, the actual physical file will be in a different location pointed to by a link such as:

ASMCMD> ls -l +DG_ORA_DATA/CUSTPR/orapwCUSTPR
Type      Redund  Striped  Time             Sys  Name
PASSWORD  UNPROT  COARSE   SEP 28 17:00:00  N    orapwCUSTPR => +DG_ORA_DATA/CUSTPR/PASSWORD/pwdCUSTPR.347.967084532

Storing this file in ASM adds a small complexity in moving the file to a standby RAC that houses the physical (data guard) standby as we can no longer just FTP the file directly. The file has to be copied from ASM to a regular unix file system, transferred to a destination file system and then put into ASM. This is accomplished with the ORAPWD command.

On the primary RAC, copy the file to a unix file system with the command

This command is issued from within ASMCMD

pwcopy +DG_ORA_DATA/CUSTPR/orapwCUSTPR /oracle_home/prod/12/db_1/dbs/orapwCUSTDR

Note that the destination file has been renamed to the file (database) name required on the standby; orapwCUSTPR changed to orapwCUSTDR.

After the file has been extracted from ASM, transfer it with any method such as SFTP, SCP etc. to a destination unix folder. After it is transferred, copy it to ASM with the below command

This command is issued from within ASMCMD

pwcopy /oracle_home/prod/12/db_1/dbs/ orapwCUSTDR +DG_UIIOMPDCLU33_ORA1/A7VZRPR/PASSWORD/ orapwCUSTDR

As a precautionary measure, I shutdown the standby RAC with the appropriate SRVCTL commands. After the above copy was completed, I restarted the standby and tested connectivity from the primary with

sqlplus sys@CUSTDR as sysdba

The SYSAUX tablespace on one of our production RACs had grown to over 28 GB and was continuing to alarm for space. I ran the below report:

@?/rdbms/admin/awrinfo.sql

which indicated that the space was being used by “SM/AWR”:

########################################################
(I) AWR Snapshots Information
########################################################

*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size                     29,967.0 MB ( AUTOEXTEND ON for 1 out of 2 files )
| Fixed limit                              499.0 MB
| Auto Extent limit                     32,768.0 MB
|
| Schema  SYS          occupies         29,684.9 MB (  99.1% )
| Schema  SYSTEM       occupies             94.2 MB (   0.3% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
| SM/AWR               SYS                       28,805.8 MB
| SM/OPTSTAT           SYS                          522.6 MB
| SM/ADVISOR           SYS                          234.1 MB
| LOGMNR               SYSTEM                        92.8 MB

Since we were keeping the AWR data for 30 days, we tried purging a few days at a time to see if that would make a difference with the following SQL:

exec DBMS_STATS.PURGE_STATS(SYSDATE-29);

But that did not seem to reduce the space used either. Purging all the data did not seem to make any difference:

exec DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL)

I suspect that we were encountering the issue documented in “Excessive AWR Growth From Partitioned Objects Such as SYS.WRH$_EVENT_HISTOGRAM Causing Sysaux to Grow (Doc ID 1912201.1)”.

I then decided to look at the sizes of the individual objects with the SQL:

select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where  tablespace_name = 'SYSAUX'
group by segment_name,segment_type order by 1 desc
;

This indicated that objects such as WRH$_EVENT_HISTOGRAM, WRH$_LATCH etc. were using up a lot of space.

Before you proceed with the following, please understand that you will be losing AWR data that could be needed for performance tunning etc.

Based on the output from the above SQL, I started truncating the largest objects (tables) with SQL such as:

truncate table WRH$_EVENT_HISTOGRAM;
truncate table WRH$_LATCH;
truncate table WRH$_SQLSTAT;
truncate table WRH$_SYSSTAT;
truncate table WRH$_ACTIVE_SESSION_HISTORY;

After this completed the space utilized was significantly reduced:

(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size                     12,036.6 MB ( AUTOEXTEND ON for 1 out of 2 files )
| Fixed limit                              499.0 MB
| Auto Extent limit                     32,768.0 MB
|
| Schema  SYS          occupies         11,754.4 MB (  97.7% )
| Schema  SYSTEM       occupies             94.2 MB (   0.8% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
| SM/AWR               SYS                       11,394.5 MB
| SM/ADVISOR           SYS                          234.1 MB
| LOGMNR               SYSTEM                        92.8 MB

A user reported the below error:

Select * from users;
ORA-28374: typed master key not found in wallet

gv$encryption_wallet indicated that the wallet was open and backed up on both nodes. It turned out that we had two copies of the wallet files in different locations. In order to resolve the issue, we
– stopped the RAC
– ensured that the wallet files matched and were in the correct location on both nodes
– updated the sqlnet.ora to indicate the wallet location on both nodes
And restarted the RAC. After this, the user was able to access the USERS table.

A colleague of mine ran into the annoying mutating trigger issue and solved it as follows. The requirement was to update TABLE_A with data from TABLE_B. There is no relationship between the two tables other than they get inserted during the same unit of work.

Original trigger:

CREATE OR REPLACE TRIGGER TABLE_A_TRIGGER_01
before INSERT ON TABLE_A 
FOR EACH ROW 
begin 
update TABLE_A  set customer_id = 
(select customer_id from TABLE_B
where master_key = :new.master_key);
END; 
/

This resulted in error:

insert into TABLE_A select * from TABLE_A_TEST_DATA
            *
ERROR at line 1:
ORA-04091: table TABLE_A is mutating, trigger/function may not see it
ORA-06512: at "TABLE_A_TRIGGER_01", line 2
ORA-04088: error during execution of trigger 'TABLE_A_TRIGGER_01'

The workaround was to code it as follows:

CREATE OR REPLACE TRIGGER TABLE_A_TRIGGER_01
BEFORE INSERT ON TABLE_A
FOR EACH ROW
DECLARE
 V_CUSTOMER_ID number(9);
 V_MASTER_KEY varchar2(16);
begin
V_MASTER_KEY := :new.master_key;
select h.customer_id INTO V_CUSTOMER_ID from TABLE_B h
where h.master_key = V_MASTER_KEY;
:new.customer_id := V_CUSTOMER_ID;
END;
/