Tuesday, May 14, 2019

Recovering Tables and Table Partitions using RMAN Backups

Oracle 12c offers new feature to recover tables and table partitions at a point-in-time using RMAN without affecting the other objects in the database. Use RECOVER TABLE command to recover tables or table partitions from an RMAN backup.

This feature is useful in the following scenarios:
  • Object has Logical corruption or dropped
  • There is no sufficient undo to perform Flashback table
  • DDL operation modified the structure and you want to recover the data (Flashback cannot rewind the structural changes)
  • Recover a small number of tables to a point-in-time
User should verify the pre-requisites before you prepare to recover the tables and table partitions. RMAN enables you to specify the point-in-time either using SCN, Time or sequence number.

The conditions must be met to perform table/ table Partition recovery:

Before you prepare to recover the tables and table partitions,
  • Database must be in ARCHIVELOG mode and read-write mode
  • At least one full backup is available along with archived logs
  • Enough disk space is available on the database server for auxiliary instance
  • If present, any dependent objects to include in recovery
  • At least 1 Gigabyte extra in memory for the auxiliary database
  • Tables and table partitions on standby databases cannot be recovered.
  • Tables with named NOT NULL constraints cannot be recovered with the REMAP option. · COMPATIBLE parameter must be set to 11.1.0 or higher to recover table partition

 
                    
RMAN enables recovery of selected tables without affecting remaining database objects. During the recovery process RMAN creates an auxiliary database, which is used to recover the tables or table partitions to a specified point-in-time. User need to specify the auxiliary database location using AUXILIARY DESTINATION clause in the RECOVERY command or SET NEWNAME command.

Please find the steps performed by RMAN during the recovery process:

1. Determine the backup which has the tables or table partitions that needs to recover to specified point-in-time
2. Create auxiliary database and recovery the tables or table partitions until specified point-in-time
3. Take a data dump export with recovered tables or table partitions
4. Import the dump into target database
5. Rename the recovered tables or table partitions in the target database

Please find an example to recovery TABLE1 table.

RECOVER TABLE TESTUSER.TABLE1
UNTIL SCN 384840289
AUXILIARY DESTINATION '/TEMP/ORCLDB/recover'
DATAPUMP DESTINATION '/TEMP/ORCLDB/dumpfiles'
DUMP FILE 'testdump.dat';

If source table exists then user can specify NOTABLEIMPORT or REMAP TABLE. Also user can use UNTIL TIME or UNTIL SEQUENCE clause to specify point-in-time recovery.

Please find an example to recovery TABLE1 table as TABLE1_REC to the state that it was 2 days before the current date.

RECOVER TABLE TESTUSER.TABLE1
UNTIL TIME ‘SYSDATE-2’
AUXILIARY DESTINATION '/TEMP/ORCLDB/recover'
REMAP TABLE 'TESTUSER'.'TABLE1':'TABLE1_REC';

There are some limitations recovering tables and table partitions:
  • We cannot recover table and table partitions belongs of SYS schema
  • We cannot recover table or table partitions from SYSAUX,SYSTEM tablespace Tables
  • We cannot recover tables with named NOT NULL constraint using REMAP option.
  • We cannot recover Table/Table partitions ON STANDBY database
  • We cannot recovery table partitions if version is prior Oracle Database 11g R1
Note that there are other methods available to recover tables to a point-in-time such as Oracle Flashback and Tablespace Point-in-Time Recovery.

Refer below link for

Flashback and Database Point-in-Time Recovery
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com


Monday, May 6, 2019

Oracle Database Upgrade to 12c and Available Methods

Oracle 12c offers many new features and upgrading database can bring many advantages to organization. There are various upgrade and migration methods available and the best method to use for your upgrade/migration scenario depends on the source database version, the source and destination operating systems, your downtime requirements, and the personal preference of the DBA. Based upon factors there is a method available to best fit your organization needs.

We have several upgrade methods available

• DBUA
• command line
• Full Transportable Export/Import
• Transportable Tablespaces
• Data Pump Export/Import
• Original Export/Import

For complete Article Please check below link

https://www.linkedin.com/pulse/oracle-database-upgrade-12c-available-methods-gunukula-oracle-ace

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Sunday, May 5, 2019

Restoring and Recovering Files over Network in Oracle

Oracle 12c offers many new features and restoring, recovering files over the network is one of the new feature. Using RMAN you can restore and recover a database, datafile, controlfile, tablespace or spfile over the network from a physical standby database. To restore the database over the network, use the RESTORE… FROM SERVICE command and use the RECOVER…FROM SERVICE command to recover the database over the network. The FROM SERVICE clause specifies the service name of the physical standby.

You can also use multisection, compression and encryption to improve backup and restore performance.

  • Use SECTION SIZE with RESTORE command to perform multisection restore
  • Use SET ENCRYPTION clause before the RESTORE command to specify the encryption
  • Use USING COMPRESSED BACKUPSET clause to compress backup sets
This feature is useful to synchronize primary and standby database. Here are the few scenarios
Roll-forward a physical standby database to sync with the primary database
Restore the primary database using physical standby database.
Restore physical standby database using the primary database.

In the following example restoring data file over the network from physical standby to primary database

Connected to primary database implicitly
RMAN> CONNECT TARGET /

Restoring the datafile on the primary using datafile on physical database with service “standby_db”
RMAN> RESTORE DATAFILE '/oradata1/orcl/users.dbf' FROM SERVICE standby_db SECTION SIZE 1024M;

Prior to 12c , to restore and recover files over the network, you need to use RMAN BACKUP INCREMENTAL FROM SCN command to create a backup on the primary database that started at the current SCN of the standby, and was then used to roll the standby database forward in time. That manual, multi-step method is not necessary as of Oracle Database 12c.

Regards
Satishbabu Gunukula, Oracle ACE

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOAD_METADATA

I have recently come across the below error when importing the data using DATA PUMP.

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOAD_METADATA [SELECT..
......................................
ORA-39183: internal error -19 ocurred during decompression phase 2


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

0x64f77ff8     21979  package body SYS.KUPW$WORKER


Cause: Normally users will see ORA-39183 error when the dump got corrupted during the file transfer or user might not be using BINARY mode using FTP

I would recommend taking once more export and copy again, it should work.

Users might come across below known issue

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [PASSWORD_HISTORY:"<USER>"]
ORA-06502: PL/SQL: numeric or value error
LPX-00216: invalid character 0 (0x0)
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8358

Cause: In this case hashes of the old passwords stored in user_history$ are corrupted.

Workaround: Please follow below steps, if you error doesn’t match please contact Oracle support

1. EXCLUDE=PASSWORD_HISTORY on impdp statment
or
2. Delete the user_history$ information pertaining to that user.
    SQL> delete from user_history$ where user# in (select user# from user$ where name='USER1');

Please refer below metatlink notes before making any change

RA-39126: "Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [PASSWORD_HISTORY: ...]" (Doc ID 2114832.1)

DataPump Import Errors ORA-39183 Internal Error -19 Ocurred During Decompression Phase 2 ORA-6512 [ID 1524577.1]

Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

Saturday, May 4, 2019

ORA-04030: out of process memory when trying to allocate 4024 bytes (kxs-heap-b,kghsseg_1 : kokeibib)

Normally users see ORA-04030 error with shortage of RAM, small PGA and kernel parameter config.

 ORA-04030: out of process memory when trying to allocate 4024 bytes (kxs-heap-b,kghsseg_1 : kokeibib)
 Non critical error ORA-48913 caught while writing to trace file "/u01/home/oracle/diag/rdbms/ORCL/ORCL/incident/incdir_8937/ORCL_ora_11258_i8937.trc"
 Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached
 ORA-04030: out of process memory when trying to allocate 169040 bytes (pga heap,kgh stack)
 ORA-04030: out of process memory when trying to allocate 4024 bytes (kxs-heap-b,kghsseg_1 : kokeibib)
 Non critical error ORA-48913 caught while writing to trace file "/u01/home/oracle/diag/rdbms/ORCL/ORCL/incident/incdir_8938/ORCL_ora_11258_i8938.trc"
 Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached

Users can refer metalink Note 233869.1 titled "Diagnosing and Resolving ORA-4030 errors"

By looking the error it is clear that “out of process memory”, where oracle cannot get the required RAM. But note that you need to analyze the trace to find out the root cause

In my case I found that one of the un-optimized PL/SQL job consuming the resources and caused the issue.

Current SQL Statement for this session (sql_id=gjn38xnrxcfbj) -----
BEGIN procedure_1(:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 ) ; END;

57% pid 67: 4089 MB used of 4092 MB allocated <= CURRENT PROC
Dump of Real-Free Memory Allocator Heap [0x7f2c640b4000]
mag=0xfefe0001 flg=0x5000003 fds=0x6 blksz=65536
blkdstbl=0x7f2c640b4010, iniblk=66560 maxblk=524288 numsegs=115
In-use num=65424 siz=4290117632, Freeable num=0 siz=0, Free num=1 siz=20512768

In our case max_map_count value is not enough and needs to investigage why the pl/sql processes requires such a alarge amout of memory. In this case adjusting PGA_AGGREGATE_TARGET or MEMORY_TARGET will not limit the size processes can grow.

To find out current vale of max_map_count run below command

$cat /proc/sys/vm/max_map_count
65530

To change the value run below command
sysctl -w vm.max_map_count= <greater than 65530>

Increase the value by following metalink note
Modification of "vm.max_map_count" on Oracle Linux 6 (Doc ID 2195159.1)

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Monday, April 29, 2019

Webinar: Whats New in Oracle Golden Gate 12c?

The Oracle GoldenGate software package delivers low-impact, real-time data integration and transactional data replication across heterogeneous systems for continuous availability, zero-downtime migration, and business intelligence.

Date and time: Thursday, May 9th, 2019 8:00 am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

Join the Webinar to learn new features in Golden Gate.

· Expanded heterogeneous Support
· Multitenant Container Database (CDB) Support
· Oracle Universal Installer (OUI) Support
· Support for Public and Private Clouds
· Integrated Replicat
· Security
· Coordinated Replicat
· New 32K VARCHAR2 Support
· High Availability (HA) enhancements
· Support for Other Oracle products
· Improvements to feature Functionality

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com and reserve your spot. Registration is limited to first 50 members only.

You will receive an email confirmation with meeting link. For presentation link Click here .

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Wednesday, March 27, 2019

ORA-00600: internal error code, arguments: [kkdlron-max-objid], [4254950911], [4254950914]

When the database reached maximum object id or if the new object id exceeds the limit you will see below error. This situation normally happen in Data ware house databases, especially with SAP BW using Oracle as database.

< ORA-00600: internal error code, arguments: [kkdlron-max-objid], [4254950911], [4254950914], [], [], [], [], [], [], [], [], [] < ORA-00600: internal error code, arguments: [kewrose_1], [600]

You will get the max object id by running below commands.
SQL> select max(obj#) from SYS.obj$;
MAX(OBJ#)
----------
4254909633
SQL> SELECT dataobj# FROM sys.obj$ where name='_NEXT_OBJECT';
SQL> select max(object_id),max(DATA_OBJECT_ID) from dba_objects ;


Oracle Support Document 20529650.8 (Bug 20529650 - ORA-600 [kkdlron-max-objid], [4254950911]) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=20529650.8

Also check SAP note : 2137109 - Data Object ID limit , if you see the issue on SAP related applications.

I see 2 options here
1. Logically rebuild the DB - Permanent fix
2. Apply the patch 20529650 - workaround/temporary fix

The option 2 applying the patch will brings us the new limit 4293950911 (4254950911+39000000). But keep in mind that this new limit can reach quickly in BW systems. Note that the patch will give team some time but the permanent solution is to rebuilding the database.

You can use below query to estimate how much time you might have , but note that this is just an estimate only.


Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Enable access to the Tableau Server repository (Postgres) and update repository access job has failed

Users can connect to tableau server repository suing tableau desktop using 2 built-in user’s i.e tableau, readonly. For reading database tableau and in-depth analysis it is recommended to use readonly user.

Before you use readonly user you need to enable the access . In older versions you will be using “tabadmin” command and in newer versions you will be using “tsm”.

Users will be connecting to tableau repository using port 8060.

Run below command to enable repository access

tabadmin dbpass --username readonly <password>

or

tsm data-access repository-access enable --repository-username readonly --repository-password <password >

When enabling the "readonly" user access i have received error using TSM.

61% - The services failed to stop. 66% - Disabling database services.
72% - Waiting for database services to disable.
The update repository access job has failed.
or
66% - An error occurred while waiting for services to reconfigure.
73% - Disabling database services.
80% - Waiting for database services to disable.
The update repository access job has failed. 

I tried different ways but didn’t help. Finally I have stopped the services and ran the above TSM command and it executed successfully.

Steps:
stop tableau services using TSM
run the command - tsm data-access repository-access enable --repository-username readonly --repository-password <password > 
start tableau services using TSM

This operation will perform a server restart. Are you sure you wish to continue?
(y/n): y
Starting update repository access asynchronous job.
Job id is '8', timeout is 25 minutes.
5% - Enabling the maintenance app.
11% - Waiting for the maintenance app to start.
16% - Reading configuration.
22% - Putting the repository into local trust mode.
27% - Enabling the database services.
33% - Waiting for the database services to enable.
38% - Updating the roles in the repository.
44% - Taking the repository out of local trust mode.
50% - Updated pending configuration.
55% - Disabling all services.
61% - Waiting for the services to stop.
66% - Updating the configuration version on nodes.
72% - Waiting for services to reconfigure.
77% - Enabling all services.
83% - Waiting for the services to start.
88% - Reloading postgres configuration.
94% - Disabling database services.
100% - Waiting for database services to disable.
Successfully updated repository access.

Regards
Satishbabu Gunukula, Oracle ACE

Error: Search & Browse is not available: Make sure all Tableau related files and windows are closed

Users may encounter below error when working with Tableau stop/start.

"***Error: Search & Browse is not available: Make sure all Tableau related files and windows are closed, then stop the server, run "tabadmin reindex" and start the services"

Unable to determine if all components of the service started properly. See tabadmin.log for more information.

When you see this error user unable to connect to tableau and check tabamin.log for more details.

After troubleshooting I ran cleanup with reset coordination and issue has been resolved.

Tabadmin stop
Tabadmin cleanup --reset-coordination
Tabadmin start

Thanks
Satishbabu Gunukula, Oracle ACE

Monday, March 4, 2019

Oracle 11g and 12c Latest Database or OJVM Patch Set Update or Release Update

I have received several emails related to Latest patch set, OJVM and security patch update for Oracle 11g and Oracle 12c versions. Here is the summarized copy of latest patches from Oracle support side

Please see the Blog post on “How to Apply Database or JVM Patch set update or release update

If you still have any questions, I would always recommend to raise a ticket with Oracle support.

11g latest patch info

11.2.0.4

Patch 27734982 - Oracle Database Patch Set Update 11.2.0.4.180717
https://updates.oracle.com/Orion/Services/download?type=readme&aru=22229176

Patch 27923163: OJVM PATCH SET UPDATE 11.2.0.4.180717
https://updates.oracle.com/Orion/Services/download?type=readme&aru=22212880

Patch 27870645: DATABASE SECURITY PATCH UPDATE 11.2.0.4.180717
https://updates.oracle.com/Orion/Services/download?type=readme&aru=22229821

12c Latest Patch info
12.1.0.2
Patch 27547329 - Database Patch Set Update 12.1.0.2.180717
https://updates.oracle.com/Orion/Services/download?type=readme&aru=22280349

Patch 27923320: OJVM PATCH SET UPDATE 12.1.0.2.180717
https://updates.oracle.com/Orion/Services/download?type=readme&aru=22224206

12.2.0.1
Patch 28163133: DATABASE JUL 2018 RELEASE UPDATE 12.2.0.1.180717
https://updates.oracle.com/Orion/Services/download?type=readme&aru=22313390
Patch 27923353: OJVM RELEASE UPDATE 12.2.0.1.180717
https://updates.oracle.com/Orion/Services/download?type=readme&aru=22237223

Patch 27427077: DATABASE OCT 2017 RELEASE UPDATE REVISION 12.2.0.1.180417
https://updates.oracle.com/Orion/Services/download?type=readme&aru=22113335

Please review below master link for Oracle JVM component database PSU and update”

Oracle Recommended Patches -- "Oracle JavaVM Component Database PSU and Update" (OJVM PSU and OJVM Update) Patches (Doc ID 1929745.1)

Thanks 
Satishbabu Gunukula, Oracle ACE

Wednesday, February 27, 2019

How to Apply Oracle Latest patch set, OJVM and security patch...etc

When planning for Oracle patches make sure all pre-requisites are met and required backups are in place. Please refer below link to find out the required patches for 11g and 12c.

Oracle 11g and 12c Latest Database or OJVM Patch Set Update or Release Update

Pre-requisites

1. Before you apply patch make sure you are using latest Opatch version.

$./opatch version
OPatch Version: 11.2.0.3.19
OPatch succeeded.

Downloaded the latest opatch for 12.2 version using the link - https://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=6880880

To apply latest Opatch follow the instructions
OPatch - Where Can I Find the Latest Version of OPatch(6880880)? [Video] (Doc ID 224346.1)


2. Download the required PSU or JVN or intern patch and unzip

3. Make sure there are no conflicts and run below script to find out the conflicts. If you see any conflicts the make sure you fix the reported conflicts.

$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

In case of any conflicts you will see the message like below

There are no patches that can be applied now.
Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :xxxxxxx, xxxxxx


In case if there are no conflicts then you will see the message like below

Invoking prereq "checkconflictagainstohwithdetail"
Prereq” CheckConflictAgainstOHWithDetail” passed.
Opatch Succeeded.


4. Using the -report option to simulate the application of the interim patch or PSU

Using “-report” option with "opatch apply" to simulate the apply without updating any files. Using this option you no need to shutdown the databases and it can be up and running.

$ opatch apply -report
...
Skip patching component oracle.rdbms, 11.2.0.4.0 and its actions.
The actions are reported here, but are not performed.

ApplySession skipping inventory update.

Verifying the update...
Inventory and System verification is performed here.
Patch xxxxxx successfully applied
Log file location: /oracle/product/11.2.0.4/cfgtoollogs/opatch/12834800_May_02_2019_10_27_17/apply2019-05-12_10-27-17AM_1.log

OPatch succeeded

5. Run utlrp.sql to recompile any invalid objects
$ORACLE_HOME/rdbms/admin/utlrp.sql

6. Check Oracle Components are VALID or not. Please make sure you fix INVALID component.
If NOT required then you might have to remove INVALID components.

SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
comp_id,substr(version,1,12) version,status from dba_registry; 
COMP_NAME                                                    COMP_ID              VERSION         STATUS
------------------------------------------------------------ -------------------- --------------- ----------
Oracle Multimedia                                            ORDIM                11.2.0.4.0      VALID
Oracle XML Database                                          XDB                  11.2.0.4.0      VALID
Oracle Expression Filter                                     EXF                  11.2.0.4.0      VALID
Oracle Rules Manager                                         RUL                  11.2.0.4.0      VALID
Oracle Workspace Manager                                     OWM                  11.2.0.4.0      VALID
Oracle Database Catalog Views                                CATALOG              11.2.0.4.0      VALID
Oracle Database Packages and T                               CATPROC              11.2.0.4.0      VALID
JServer JAVA Virtual Machine                                 JAVAVM               11.2.0.4.0      VALID
Oracle XDK                                                   XML                  11.2.0.4.0      VALID
Oracle Database Java Packages                                CATJAVA              11.2.0.4.0      VALID

7. Make sure you have taken TAR of the ORACLE_HOME, in case of any issues

tar -cvf oracle_home_12c.tar 12.2.0.1

Applying interim patch or PSU or JVM

1. Stop the databases and listener

Sqlplus / as sysdba
Shutdown immediate
$lsnrctl stop LISTENER

2. Make sure you are in the patch directory and run the below command to apply the patch

$cd <patch number>
$ORACLE_HOME/OPatch/opatch apply

3. Run post patch script against each database

Sqlplus /as sysdba
startup
@catbundle.sql psu apply
quit

Starting version 12.1 we need to use datapatch to complete post patch actions upon any 12c or later database restart. The datapatch contains the logic to identify if any post-patch SQL actions are pending.

Sqlplus / as sysdba
Startup upgrade

$ cd $ORACLE_HOME/Opatch
./datapatch -verbose

-verbose
Output additional information used for debugging

After datapatch restart the database.
Sqlplus / as sysdba
Shutdown immediate
startup

Post-verification:-
1. After patching check the inventory, to make sure patch has been updated or not
$ORACLE_HOME/OPatch/opatch lsinventory

Oracle Interim Patch Installer version 11.2.0.3.19
Copyright (c) 2019, Oracle Corporation. All rights reserved.

Oracle Home : /oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.19
OUI version : 11.2.0.4.0

Log file location : /oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2019-05-02_09-41-09AM_1.log
Lsinventory Output file location : /oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2019-05-02_09-41-09AM.txt
……
OPatch succeeded.

2. Start the database and Listener, in case if they are still down

Sqlplus / as sysdba
startup

$lsnrctl start LISTENER

3. Run “utlrp.sql” to re-compile all invalid object

$ORACLE_HOME/rdbms/admin/utlrp.sql

4. Verify the patch history using from dba_registry_history using below query

SQL>select action_time,action from dba_registry_history;

ACTION_TIME ACTION

-------------------------------------------------- ------------------------------
02-MAY-19 06.35.23.191942 PM VIEW INVALIDATE
02-MAY-19 06.35.23.217686 PM UPGRADE
02-MAY-19 06.35.41.668820 PM APPLY

5. Please make sure all Oracle COMPONENTS are VALID

select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
comp_id,substr(version,1,12) version,status from dba_registry;

Thanks
Satishbabu Gunukula

Monday, February 18, 2019

Webinar: Data Guard Physical Standby Setup in Oracle?

This Webinar helps you to understand the benefits of Oracle Data Guard, available methods and how to setup and common issues.

Date and time: Feb 28th 2019 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.
· Oracle Data Guard and its Benefits
· Overview of different Data Guard Options
· Difference between RAC vs DG
· Prerequisites
· DataGuard Demonstration
· Common Issues
· Post verification Steps
· References
· Contact Info
· Q&A

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com.

Note that registrations are limited and first come and first serve basis.
You will receive an email confirmation with meeting session link.

For presentation link Click here

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Tuesday, January 15, 2019

ORA-28040: No matching authentication protocol after Oracle 12c upgrade.

After Oracle 12c upgrade users started seeing below errors

ORA-28040: No matching authentication protocol

The main issue is older versions of drivers will not able to connect 12c server. In Oracle 12.1, the default value for the SQLNET.ALLOWED_LOGON_VERSION parameter is set to 11. This means that database clients using pre-11g drivers cannot authenticate to 12.1 database servers unless the SQLNET.ALLOWED_LOGON_VERSION parameter is set to the old default of 8 in $ORACLE_HOME/network/admin/sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 8
SQLNET.ALLOWED_LOGON_VERSION_SERVER = 8

Note that SQLNET.ALLOWED_LOGON_VERSION has been deprecated in 12c.

Even after updating above values users might receive below error.
ORA-01017: invalid username/password: logon denied

In this case users need to add following parameter to sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (NONE)

Also few users received ORA-28040 error when creating older version of databases (for ex: 10g) using DBCA

Thanks
Satishbabu Gunukula, Oracle ACE
https://oracleracexpert.com

Wednesday, January 2, 2019

Physical standby redo log must be renamed

When creating standby database using database duplication it completed but received below standby redo error. This error will not stop the replication but user don’t want to see these ORA- errors.

Finished recover at 2019-02-12:15:02:32
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/oracle/redo1/ORCL/ ORCL _1A.rdo'

RMAN-05535: warning: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 2 thread 1: '/oracle/redo1/ORCL/ORCL _2A.rdo'


Though this didn't stop the data guard replication, the customer wanted to get-rid the message. Starting with v10g, this was an expected behavior to improve the switchover and failover. With v10g, when MRP is started, it will attempt to clear the online log files.

If PRIMARY and STANDBY has the same directory structure then you will see these errors. This is an expected behavior when you don’t set log_file_name_convert parameter. You can get rid-off the error message just setup dummy value

SQL> ALTER SYSTEM SET log_file_name_convert='dummy','dummy';

You should not see ORA messages any more in the alert.log file.

Thanks
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com