How to access the Audit Vault and Firewall host OS for administrative purpose

Hello,

Make sure to allow SSH Access via the Network Services tab, otherwise login will be limited to the console only.

1. Login as support user first.

ssh support@<ip of auditvault / firewall>

2. Then switch to root user from support    

su –

(provide root password)

3. After logging in as root, you have to switch to “oracle” user.

su – oracle

(no password is required)

So please don’t forget to login as support user first 🙂

Advertisement

Materialized View Refresh is very slow in Oracle Database 12.2

Hello,

Starting 12.2 Oracle Database collects and stores statistics about materialized view refresh operations. These statistics are accessible using data dictionary views. Statistics for both current and historical materialized view refresh operations are stored in the database. Historical materialized view refresh statistics enable you to understand and analyze materialized view refresh performance over time in your database. Refresh statistics can be collected at varying levels of granularity.

Back to the problem in trace I saw Oracle executed DML’s and selects on below tables and were performing full table scan.

mvref$_stats;

mvref$_run_stats;

mvref$_change_stats;

mvref$_stmt_stats;

There is a collection level parameter which specifies the collection level for materialized view refresh statistics. 

The values that can be set for the COLLECTION_LEVEL parameter are:

NONE: No statistics are collected for materialized view refresh operations.

TYPICAL : Only basic refresh statistics are collected for materialized view refresh operations. This is the default setting.

ADVANCED : Detailed statistics, including the parameters used in the refresh operation and the SQL statements that are run, are collected for materialized view refresh operations.

So to avoid it better to turn off the collection/tracking level. By default collection level is typical. So as to stop completely we need to execute below statement to turn it off. 

exec dbms_mview_stats.set_system_default('COLLECTION_LEVEL', 'NONE');

select * from DBA_MVREF_STATS_SYS_DEFAULTS;

12c Database Alert.log File Shows The Message: Using Deprecated SQLNET.ALLOWED_LOGON_VERSION Parameter

Hello,

On 12c database, the alert.log file shows the following message:

“Using deprecated SQLNET.ALLOWED_LOGON_VERSION parameter”. The Database is reporting these messages because the “SQLNET.ALLOWED_LOGON_VERSION” parameter is no longer valid (with 12c).

The SQLNET.ALLOWED_LOGON_VERSION parameter is deprecated in Oracle Database 12c.
This parameter has been replaced with two new Oracle Net Services parameters:

SQLNET.ALLOWED_LOGON_VERSION_SERVER
SQLNET.ALLOWED_LOGON_VERSION_CLIENT

In order to suppress these messages in the alert log of the database, you need to use the new parameters for the 12c database.

 STEPS:

 1. Edit the sqlnet.ora file of the 12c database. (This needs be done on each database on 12c). So for example if both your EBS and CCG databases are on 12c, you need to do this on each sqlnet.ora file. Typically, the sqlnet.ora file that would be referenced by the database is located in RDBMS_HOME/network/admin

 2. Remove or comment the following entry.

    SQLNET.ALLOWED_LOGON_VERSION

 3.You need to follow the instructions below:

 3a. Add the following two new Oracle Net Services parameters:

    SQLNET.ALLOWED_LOGON_VERSION_SERVER = n
    SQLNET.ALLOWED_LOGON_VERSION_CLIENT = n

    Specify the value for ‘n’ based on your own environment. The default setting for the new parameters is 11. Any client that attempts to connect must be at version 11 or higher unless these   parameters are explicitly set in the server side sqlnet.ora file.      

3b. For example: Set these parameters at the lowest version level that is required in your environment.
        The example shpow below shows the following: All clients at version 10 or higher would require this setting:

       SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
       SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10

    3c. Note that SQLNET.ALLOWED_LOGON_VERSION_CLIENT would be necessary on the server when the database is ‘acting’ as a client. Such as the case of a database link as in the case of CCG applications.

ORA-65346: The PDB version is lower and components (APEX) are missing in CDB.

Hello,

I got the following error of APEX while upgrading the PDB database from lower version to upper by unplug and plug method.

Error:
ERROR at line 1: ORA-65346: The PDB version is lower and components (APEX) are missing in CDB.

1- Check the APEX detail with following

select COMP_ID, VERSION, STATUS from CDB_REGISTRY where COMP_ID='APEX' order by CON_ID;

select r.COMP_NAME, r.VERSION, c.NAME, c.CON_ID from CDB_REGISTRY r, V$CONTAINERS c where r.CON_ID=c.CON_ID and r.COMP_ID='APEX' order by CON_ID;

2- Remove the Apex from CDB
Go to the Oracle home apex directory
cd %ORACLE_HOME%\apex

3- Connect with CDB$ROOT and check all pdb is open state.
sqlplus sys as sysdba
alter pluggable database open all;

4-Remove APEX from the CDB$ROOT and all pluggable database.

sqlplus "sys/syspass as sysdba" @apxremov_con

5- Recompile all invalid objects


@?\rdbms\admin\utlrp.sql
select object_name, status from dba_objects where status='INVALID';

6- Check registry for APEX entry.

select COMP_ID, STATUS from DBA_REGISTRY where COMP_ID='APEX';
No row selected.

7-  Go to the Oracle home apex directory

cd %ORACLE_HOME%\apex

8-  Connect with CDB$ROOT and check all pdb is open state.

sqlplus sys as sysdba
alter pluggable database open all;

9- Install apex in PDB1 database

Syntex:
@apexins.sql tablespace_apex tablespace_files tablespace_temp images

Example:
alter session set container=PDB1;
@apexins.sql SYSAUX SYSAUX TEMP /i/

10- You can also change password for apex.

SQL> @apxchpwd.sql

11- Create the APEX_LISTENER and APEX_REST_PUBLIC_USER users

SQL> @apex_rest_config.sql

12-  Unlock the apex user.

ALTER USER APEX_PUBLIC_USER IDENTIFIED BY Password ACCOUNT UNLOCK;

12.2 or later STATSPACK: Idle Wait Event Such as ‘Data Guard: Timer’,’pman timer’ are Erroneously Included in Top 5 Timed Events

Hello,

The newly added idle wait events is erroneously included in the “Top 5 Timed Events” section in 12.2 or later:

Top 5 Timed Events                                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                                        wait   Call
Event                                            Waits    Time (s)        (ms)   Time
—————————————– ———— ———– ———– ——
Data Guard: Timer                                    8       4,800  600,000.97   33.3 <<== Idle event
pman timer                                       1,548       4,644    2,999.89   32.2 <<== Idle event
Data Guard: Gap Manager                             77       4,620   60,000.57   32.0 <<== Idle event
log file sync                                   38,168         250        6.55    1.7
CPU time                                                        56                 .4

This is due to following bug:

Bug 28523746 – STATSPACK: TOP 5 TIMED EVENTS CONTAINS IDLE WAIT EVENTS
Bug 31397022 – STATSPACK:IDLE WAIT EVENTS MISSING IN STATS$IDLE_EVENT

A new idle wait event is missing in perfstat.STATS$IDLE_EVENT.

For the solution; Add missing idle event rows manually into STATS$IDLE_EVENT table.

connect perfstat/<PASSWORD>

insert into stats$idle_event
  select name from v$event_name where wait_class='Idle'
  minus
  select event from stats$idle_event;

commit;