Tag Archives: oracle 12c

ORA-28040: No matching authentication protocol error

ORA-28040: No matching authentication protocol error

SQLNET parameters ALLOWED_LOGON_VERSION_CLIENT & ALLOWED_LOGON_VERSION_SERVER issue in Oracle 12c.
Set the value to the minimum authentication protocol allowed while making connection to Oracle Database instances.

Note: Version refer to the authentication protocol its not mean that oracle release version.

Error:

Caused by: java.sql.SQLException: ORA-28040: No matching authentication protocol”
ORA-28040: No matching authentication protocol error
ORA-03134: Connections to this server version are no longer supported error.

Solution:
1. Set the following value in SQLNET.ora file in Oracle database Server:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

Note: value permit all most values for authentication protocol.

2. You can also check DBA_USERS table.

select username,password_versions from DBA_USERS
--Password Version has value 10G, 11G, and 12C

Example:
Suppose application is using client version 9i then for connectivity need to set following parameters as follows:

SQLNET.ORA FILE : SQLNET.ALLOWED_LOGON_VERSION_SERVER value to 8.
DBA_USERS table have column PASSWORD VERSION its value should be 8.

SQLNET.ALLOWED_LOGON_VERSION_CLIENT
minimum authentication protocol allowed for clients and used when a server is acting as a client.

Example:
If ORacle database 12c has database link to Oracle 10g database for some information then need to set the paremeter in sqlnet.ora of oracle 12c because it act as client of oracle 10g database.
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10

Note: Version refer to the authentication protocol its not mean that oracle release version.

Value of Parameter

12a for Oracle Database 12c 12.1.0.2 or later
12 for the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols
11 for Oracle Database 11g authentication protocols ----- (default)
10 for Oracle Database 10g authentication protocols
8 for Oracle8i authentication protocol

Advertisements

Check unified audit in Oracle 12c

Check unified audit in Oracle 12c

Unified auditing is new feature added in Oracle 12c based on memory.

Check the Unified auditing is enabled or Disable

COLUMN parameter format A20
COLUMN value format A10
select parameter,value from v$option where parameter = 'Unified Auditing';

PARAMETER VALUE
-------------------- ----------
Unified Auditing FALSE

Check the data present in Unified Auditing

select count(*) from unified_audit_trail;

Check Unified auditing options enabled in the database

COLUMN user_name format a10
COLUMN policy_name FORMAT A25
COLUMN success format a10
COLUMN failure format a10
select user_name,policy_name,enabled_opt,success,failure from audit_unified_enabled_policies;

USER_NAME POLICY_NAME ENABLED_ SUCCESS FAILURE
---------- ------------------------- -------- ---------- ----------
ALL USERS ORA_SECURECONFIG BY YES YES

Note: It show only one policy is enabled ORA_SECURECONFIG

For disable ORA_SECURECONFIG Policy

NOAUDIT POLICY ORA_SECURECONFIG;

For Delete Policy

drop audit policy POL1;

Check all the Unified auditing options available across the database

COLUMN policy_name FORMAT A25
COLUMN audit_option FORMAT A30
COLUMN object_schema FORMAT A15
COLUMN object_name FORMAT A15
COLUMN audit_option_type FORMAT A20
COLUMN audit_condition FORMAT A20
select policy_name,audit_option,object_schema,object_name, audit_option_type,audit_condition FROM audit_unified_policies where object_schema not in ('DVSYS','DVF','LBACSYS');

POLICY_NAME               AUDIT_OPTION                   OBJECT_SCHEMA   OBJECT_NAME  AUDIT_OPTION_TYPE    AUDIT_CONDITION  
------------------------- ------------------------------ --------------- ------------ -------------------- -----------------
ORA_SECURECONFIG          LOGMINING                      NONE            NONE         SYSTEM PRIVILEGE     NONE             
ORA_SECURECONFIG          TRANSLATE ANY SQL              NONE            NONE         SYSTEM PRIVILEGE     NONE             
ORA_SECURECONFIG          EXEMPT REDACTION POLICY        NONE            NONE         SYSTEM PRIVILEGE     NONE             
ORA_SECURECONFIG          PURGE DBA_RECYCLEBIN           NONE            NONE         SYSTEM PRIVILEGE     NONE             
ORA_SECURECONFIG          ADMINISTER KEY MANAGEMENT      NONE            NONE         SYSTEM PRIVILEGE     NONE             
.....................
.....................
ORA_SECURECONFIG          GRANT ANY PRIVILEGE            NONE            NONE         SYSTEM PRIVILEGE     NONE             
ORA_SECURECONFIG          DROP ANY PROCEDURE             NONE            NONE         SYSTEM PRIVILEGE     NONE             

Update Multiple PDBs in a Single DML Statement

Update Multiple PDBs in a Single DML Statement of Application container

With DML Query you can also update the multiple PDBs if having same tables and columns or same database.

Example:
Set the country_name column to the value USA in the sh.sales table.
This table exists in two separate PDBs, with container IDs of 7 and 8.
Both PDBs are in the application container named sales_ac. You can connect to the application root as an administrator, and make the update as follows:

CONNECT sales_admin@sales_ac
Password: *******
UPDATE CONTAINERS(sh.sales) sal
SET sal.country_name = 'USA'
WHERE sal.CON_ID IN (7,8);

Example:
Update the column value city_name to DELHI of a table customers present in sales schema of two PDB having con id 7 and 8 in One DML statements.

UPDATE CONTAINERS(sales.customers) ctab
SET ctab.city_name='DELHI'
WHERE ctab.CON_ID IN(7,8) AND
CUSTOMER_ID=3425;

Install and Remove APEX from CDB PDB databases Oracle

Install and Remove APEX from CDB PDB databases Oracle

I am getting the following error of APEX while upgrade the PDB database from lower verstion to upper version by unplug and plug.

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

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;

Remove the Apex from CDB
Go to the Oracle home apex directory

cd %ORACLE_HOME%\apex

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

sqlplus sys as sysdba
alter pluggable database open all;

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

sqlplus "sys/syspass as sysdba" @apxremov_con

4. Recompile all invalid objects

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

5. Check registry for APEX entry.

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

Install APEX
1. Go to the Oracle home apex directory

cd %ORACLE_HOME%\apex

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

sqlplus sys as sysdba
alter pluggable database open all;

3. 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/

4. you can also change password for apex.

SQL> @apxchpwd.sql

5. Create the APEX_LISTENER and APEX_REST_PUBLIC_USER users

SQL> @apex_rest_config.sql

6. Unlock the apex user.

ALTER USER APEX_PUBLIC_USER IDENTIFIED BY Password ACCOUNT UNLOCK;

Execute SQL script (catcon.pl) in all PDBs database Oracle

Execute SQL script in all PDBs database Oracle 12c

In a CDB Environment, the catcon.pl utility is used to run SQL scripts and SQL statements in all PDBS database provide by Oracle.

catcon.pl script can run from the root container and in specified PDBs and it also have option for log files that you can view to confirm that
the SQL script or SQL statement did run successfully.

Syntax for Catcon for excute or run the SQL Script on all PDBS in once


$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl
[-u username[/password]] [-U username[/password]] [-d directory]
[-l directory] [{-c|-C} container] [-p parallelism] [-e] [-s]
[-E { ON | errorlogging-table-other-than-SPERRORLOG } ] [-I] [-g] [-f]
-b log_file_name_base -- { SQL_script [arguments] | --x'SQL_statement' }

Options
-u for username and password connect with PDB’s or CDB for execute SQL queries or scripts.
-U for username and password with special privileges to perform internal tasks like modified metadata.
-d Directory containing SQL script
-l directory on which log file written.
-c list the containers in which SQL script is run.
-C list the containers in which SQL script is not run.
-p degree of parallelism.
-e means echo on when script running.
-s spool on
-E When ON, errors are written to the table SPERRORLOG in the current schema.
-I Identifier for Error logging option
-g generating debugging information.
-f ignore if PDB’s database is closed.
-b base name of log file (mandatory)

Example of running the Script info.sql with catcon.pl script.

— Execute the script on all database included CDB or PDBs.

%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -d D:\script -b info_output info.sql

— Execute the script on HRPDB or SALESPDB
Note: ‘c’ small c is used for execute

%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -U SYS -d D:\scripts -l 'D:\script\output' -c 'HRPDB SALESPDB' -b info_output info.sql

–Execute the script except HRPDB or SALESPDB
Note: ‘C’ capital C is used for except

%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -U SYS -d D:\scripts -l 'D:\script\output' -C 'HRPDB SALESPDB' -b info_output info.sql

–Execute the SQL Query on all database

%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -e -b info_output -- --x"SELECT * FROM DUAL"

Renaming and Relocating Online Data Files in Oracle 12c

Renaming and Relocating Online Data Files in Oracle 12c

In Oracle 12c, New feature is given from moving the datafiles from one location to another without downtime of the environment.
It increase the great flexibility of the Oracle Database moving datafiles to one location to other without downtime like move datafiles which is less needed to low cost storage or Move datafile to ASM Storage online without downtime.

Renaming an Online Data File

--renames the data file user1.dbf to user01.dbf while keeping the data file in the same location
ALTER DATABASE MOVE DATAFILE '/u02/oradata/ORCL/user1.dbf' TO '/u02/oradata/ORCL/user01.dbf';

Relocating or Move the online Data file

-- Move the datafile from one location to anther online.
ALTER DATABASE MOVE DATAFILE '/u02/oradata/ORCL/user01.dbf' TO '/u03/ORADATA/ORCL/user01.dbf';

Copying an Online Data File
KEEP keyword is used to keep the old file as its and copy file to new location

-- Copy the datafile from one location to anther without removing the old location file.
ALTER DATABASE MOVE DATAFILE '/u02/oradata/ORCL/user01.dbf' TO '/u03/ORADATA/ORCL/user01.dbf' keep;

Relocating or Move an Online Data File and Overwriting an Existing File
REUSE Keyword is used to overwrite the existing file with same name present in Destination folder

ALTER DATABASE MOVE DATAFILE '/u02/oradata/ORCL/user01.dbf' TO '/u03/ORADATA/ORCL/user01.dbf' REUSE;

Move or Relocate an online data file to ASM

ALTER DATABASE MOVE DATAFILE '/u02/oradata/ORCL/user01.dbf' TO '+dg01/data/orcl/datafile/user01.dbf';

WITH MONITOR_DATA AS SELECT INST_ID, KEY, NVL2(PX_QCSID query caused Performance issue Oracle 12c

WITH MONITOR_DATA AS SELECT INST_ID Performance issue in oracle 12c

The following statement causing lot of execution and consuming lot of CPU in my AWR report.

In Oracle 12c, new feature is introduced called as “Automatic Report Capturing Feature”

WITH MONITOR_DATA AS (SELECT INST_ID, KEY, NVL2(PX_QCSID, NULL, STATUS) STATUS, FIRST_REFRESH_TIME, LAST_REFRESH_TIME, REFRESH_COUNT
.....
DBMS_XPLAN.BUILD_PLAN_XML( TABLE_NAME=>'gv$sql_plan', PLAN_TAG=>'plan', FILTER_PREDS=>:B35 , FORMAT=>'-PROJECTION +ALIAS +ADAPTIVE')
ELSE NULL END XPLAN_XML FROM DUAL) V1) CONST_VIEW

It is related with optimizer_adaptive_plans
If the CPU consumption is high then it is not an expected behavior, So we need to disable the monitoring of MMON_SLAVE.

Solution:
Disable the following parameter for stop monitoring the database:
Default setting is 60 seconds

alter system set "_report_capture_cycle_time"=0 scope=both;