ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

Uses can receive below error during the export in case if you did not provide enough file names

$expdp xxx/xxxx directory=EXP_DIR DUMPFILE=exp_user.dmp LOGFILE=exp_user.log SCHEMAS=EMPUSER FILESIZE=20m
…..

ORA-39095: dump file space has been exhausted: unable to allocate 4096 bytes
Job "EMPUSER"."sys_export_table_01" stopped due to fatal error at 10:05:03

You will also receive this error if you specify one dump file or a number less than parallelism value. The slave processes locking the file does not release the lock even after finishing as it wait other processes to write to the file.

$expdp xxx/xxxx directory= EXP_DIR DUMPFILE=exp_user.dmp LOGFILE=exp_user.log SCHEMAS=EMPUSER PARALLEL=5

ORA-39095: dump file space has been exhausted: unable to allocate 8192 bytes

DONOT use PARALLEL clause to avoid this error.

In case if you have space issue on file system, then you can generate multiple dumps on multiple file systems or disks.


Thanks,
http://oracleracexpert.com
Oracle ACE

Oracle 19c Security New Features

This webinar is intended for database administrators (DBAs), security admins, developers, and others tasked with performing operations securely and efficiently.

Join the Webinar to learn New Security Features in Oracle 19c

Date and time: Dec 18th 2020 7:00am-8:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com. Note that registrations are limited, first come and first serve basis only. You will receive an email confirmation with meeting session link.

For Presentation, link "Click here"

Thanks & Regards
http://www.oracleracexpert.com

DML operations on Active Data Guard standby in Oracle 19c

The DML operations is an Active Data Guard only feature enables DML operations on the standby database to be redirected to the primary to accommodate infrequent writes by reporting applications running against Active Data Guard standby database.

This DML feature introduced in Oracle 19c, this includes DML statements that are part of PL/SQL blocks.

The DML on the standby database will have below steps

• The user executes DML against open standby database.
• DML will be automatically redirects to the primary database.
• The DML will be applied on the primary Datable
• The redo info related change is streamed back to the standby database.
• The application change based redo info completes the DML and data is available for the client.

There are two methods to configure DML Redirection with the help of ADG_REDIRECT_DML parameter.

1. The DML Redirect can be configured at system level, this will apply to all sessions connection to the standby database

Run below command for SYSTEM LEVEL DML Redirection
SQL> ALTER SYSTEM SET ADG_REDIRECT_DML=TRUE SCOPE=BOTH;

2. You can overwrite the system level parameter with alert session command to enable the DML redirect for the current session only.

Run below command to enable SESSION LEVEL DML Redirection 

SQL>ALTER SESSION ENABLE ADG_REDIRECT_DML;

Thanks & Regards

http://oracleracexpert.com

 

Oracle RAC and Grid new features in 19c

Oracle Database 19c has many exciting new features and in order to take advantage of these features you need to upgrade the databases from older versions to Oracle 19c

Join the Webinar to learn New Features in Oracle 19c RAC and Grid

Date and time: Nov 25th 2020 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)


To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com. Note that registrations are limited, first come and first serve basis only. You will receive an email confirmation with meeting session link.

For Presentation, link "Click here"

Thanks & Regards
http://www.oracleracexpert.com

Jumpstart Your Success with Oracle MySQL Database Service

MySQL is the preferred database of developers to build applications. It is easy to use, highly scalable, and cost-effective. Which is why MySQL Database Service is the strategic provider of our customer’s technology infrastructure.
MySQL invites you to join this virtual session as we run through the steps, and demonstrate how easy it is, to set-up your MySQL Cloud instance before launching your applications. Acquire the knowledge you need to easily:

• Provision your MySQL Cloud instance
• Save time by automating time-consuming tasks, such as patching and backup
• Lower operation costs by trying the only managed database service which is 3.6 less expensive than Amazon RDS

This step-by-step introduction will establish the steps to get started on MySQL Database Service and get your questions answered by MySQL experts. Save your spot today and register!

Don't miss the opportunity and Register Today using below
https://go.oracle.com/LP=102674?elqCampaignId=275257

Thanks & Regards

OracleRACExpert.com

Oracle Data Guard New Features in Oracle 18c & 19c

This Webinar helps you to understand the benefits of Oracle Data Guard, Standby Database types, protection modes and new features in Oracle 18c and 19c.

Date and time: Nov 17th 2020 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• Introduction to Oracle Data Guard
• Oracle Data Guard Benefits
• Standby Database types
• Data Guard Protection Modes
• What's new in Oracle 18c
• What's new in Oracle 19c
• 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

Thanks & Regards,
http://www.oracleracexpert.com

Convert the database from MyISAM to innoDB

When we are trying to use application tool to convert MySQL DB engine from MyISAM to innoDB it failed and received below error message.

Error: Pre-conversion step failed: unable to restart database: exit status 5

So I have tried converting manually using below steps.

1. Before you convert make sure you stop the application or web proxy

2. Run below command to generate a “alter table” command to convert to InnoDB engine.

mysql -u root -p -N -e "select concat('alter table ', table_name, ' engine =InnoDB;') from information_schema.tables where table_schema = 'TimeDB'" > TimeDB_innodb.sql

3. Run below command to start conversion processes. Note that the below command will run sequentially and may take some time.

mysql -u root -p TimeDB < TimeDB_innodb.sql

4. After the conversion completed bring up the application or web proxy

Thanks & Regards,
Satishbabu G, Oracle ACE
http://www.oracleracexpert.com

ORA-03291: Invalid truncate option - missing STORAGE keyword

Users creating objects for development activity and come across below error message when truncating a table

ORA-03291: Invalid truncate option - missing STORAGE keyword

The truncate statement expects STORAGE keywork, either specify DROP STORAGE or REUSE STORAGE 

For ex:- SQL> truncate table dept drop storage ;

I have created this blog post shared with all developers.

Thanks & Regards,

Satishbabu G, Oracle ACE

http://www.oracleracexpert.com

ORA-16198: Received timed out error from KSR & LAD:2 network reconnect

I have setup Data Guard and log synchronization happening without any issue but recently I found below errors, which are happening rarely in alert.log file

GWR (PID:13557): ORA-16198: Received timed out error from KSR
LGWR (PID:13557): Attempting LAD:2 network reconnect (16198)
LGWR (PID:13557): LAD:2 network reconnect abandoned
2020-07-13T15:32:25.865034-07:00
Errors in file /oracle/diag/rdbms/orcl/ORCL/trace/orcl_lgwr_13557.trc:
ORA-16198: Timeout incurred on internal channel during remote archival
LGWR (PID:13557): Error 16198 for LNO:2 to 'orcl_stdby'
2020-07-13T15:32:25.865253-07:00
LGWR (PID:13557): LAD:2 is UNSYNCHRONIZED
LGWR (PID:13557): Failed to archive LNO:2 T-1.S-5003, error=16198
LGWR (PID:13557): Error 16198 disconnecting from LAD:2 standby host 'orcl_stdby'

Redo Transport Services failed with ORA-16198 error from primary database standby database using LGWR SYNC mode.

Data Guard Broker log shows below error messages…

Data Guard Broker Status Summary:

  Type                        Name                             Severity  Status

  Configuration               orcl_dg_fsfconf                   Warning  ORA-16608: one or more members have warnings

  Primary Database            orcl                              Success  ORA-0: normal, successful completion

  Physical Standby Database   orcl_stdby                        Warning  ORA-16853: apply lag has exceeded specified threshold

When I very configuration everything looks good and no issues.

DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Aug 3 15:19:29 2020
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ORCL"
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration - orcl_dg_fsfconf
Protection Mode: MaxAvailability
Members:
orcl - Primary database
orcl_stdby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 34 seconds ago)

If this is happening frequently then it is critical and must take action immediately. In my case it is happening rarely which lean towards time out setting. I see that my current DB has net_timeout as 30.

After research, I have increased the net_timeout to 60 and issue has been resolved.

In case if you are not using Data Guard broker then you can run below command from SQL*Plus

SQL> alter system set log_archive_dest_2 service="orcl_stdby", SYNC AFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="orcl_stdby" net_timeout=30, valid_for=(online_logfile,all_roles)

In case if you are using DGMGRL command-line interface, then you can run below command

DGMGRL> EDIT DATABASE 'ORCL' SET PROPERTY NetTimeout = 30;

Thanks,
Satishbabu G, Oracle ACE
http://oracleracepxert.com

ORA-00700: soft internal error, arguments

I have come across ORA-00700 error recently and found below error in the logs. This

ORA-00700: soft internal error, arguments: [dbgrfafr_1], [60], [60], [1], [0x7FB5DD11BC70], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/orcl/ORCL/incident/incdir_75765/ORCL_ora_8274_i75765.trc

Incident details in the trace will have more detailed error. In my case below connections are coming from foglight monitoring tool . After research found that this error can occur due failing statement called from monitoring tools like TOAD,foglight..etc

Dump file /oracle/diag/rdbms/orcl/ORCL/incident/incdir_75765/ORCL_ora_8274_i75765.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
Build label: RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
ORACLE_HOME: /oracle/product/19.0.0.0/dbhome_1
System name: Linux
Node name: server1
Release: 3.10.0-1062.12.1.el7.x86_64
Version: #1 SMP Thu Dec 12 06:44:49 EST 2019
Machine: x86_64
Instance name: ORCL
Redo thread mounted by this instance: 1
Oracle process number: 130
Unix process pid: 29807, image: oracle@server1

*** 2020-05-20T00:45:56.870884-07:00
*** SESSION ID:(204.25936) 2020-05-20T00:45:56.870895-07:00
*** CLIENT ID:() 2020-05-20T00:45:56.870900-07:00
*** SERVICE NAME:(ORCL) 2020-05-20T00:45:56.870904-07:00
*** MODULE NAME:(Foglight for Oracle <5.9.5.20>) 2020-05-20T00:45:56.870909-07:00
*** ACTION NAME:(isrgorcl-db-ORCL-DBO_Instance_General) 2020-05-20T00:45:56.870914-07:00
*** CLIENT DRIVER:(jdbcthin : 12.2.0.1.0) 2020-05-20T00:45:56.870919-07:00

[TOC00000]
Jump to table of contents
Dump continued from file: /oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_29807.trc
[TOC00001]
ORA-00700: soft internal error, arguments: [dbgrfafr_1], [60], [60], [0], [0x7FFC2A3E6488], [], [], [], [], [], [], []

[TOC00001-END]
[TOC00002]
========= Dump for incident 201097 (ORA 700 [dbgrfafr_1]) ========

*** 2020-05-20T00:45:56.871792-07:00
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
[TOC00003]
----- Current SQL Statement for this session (sql_id=gxpn94tgbwz4d) -----
select /*+ RULE */ version "db_version",
decode(log_mode,'NOARCHIVELOG','NO','YES') "is_archived",
decode(a.asm_files,0,'NO','YES') "is_asm",
case when dg.dataguard > 0 THEN 'YES'
when controlfile_type = 'STANDBY' THEN 'YES'
else 'NO'
end as "is_dataguard",
database_role,
decode(r.rman_conf,0,'NO','YES') "is_rman",
flashback_on "is_flashback",
instance_name,
instance_number,
open_mode,
decode(c.cell_count,0,'NO','YES') "is_exadata",
decode(al.al_count,0,'NO','YES') "is_alertlog",
'NO' "is_rds",
decode(cdb,'YES','YES','NO') is_pluggable
from sys.v$instance, sys.v$database,
(select count(*) asm_files from v$datafile where name like '+%' and rownum<2) a,
(select count(*) dataguard from sys.v$archive_dest where target = 'STANDBY' and rownum<2) dg,
(select count(*) rman_conf from sys.v$RMAN_STATUS where rownum<2) r,
(select count(*) cell_count from sys.v$cell where rownum<2) c,
(select count(*) al_count from sys.x$DBGALERTEXT where rownum<2) al
[TOC00003-END]

Also few other users encountered HIGH CPU consumption issue. This can happen when $DBGALERTEXT is populated from the XML alert log file situated in the ADR location. Note that this is an undocumented fixed table and when XML alert log is very large it takes time to access and also it can cause ORA-700 error.

You can query on X$DBGALERTEXT table to see high CPU taking a long time to complete.

SELECT count(*)
FROM X$DBGALERTEXT
WHERE to_date(to_char(originating_timestamp, 'dd-mon-yyyy hh24:mi'), 'dd-mon-yyyy hh24:mi') > to_date(to_char(systimestamp - .00694, 'dd-mon-yyyy hh24:mi'), 'dd-mon-yyyy hh24:mi') /* last 10 minutes */
AND (message_text = 'ORA-00600'
OR message_text LIKE 'útal%'
OR message_text LIKE '%error%'
OR message_text LIKE '%ORA-%'
OR message_text LIKE '%terminating the instance%');

It’s a good practice to purge the table regularly and it can be purged using ADRCI utility.

--First make sure you check the count
SQL> select count(*) from X$DBGALERTEXT;

--Connect to ADRCI utility

$adrci> show home
ADR Homes:
diag/rdbms/orcl/ORCL
diag/tnslsnr/server1/listener_orcl
$adrci> SET HOMEPATH /oracle/diag/rdbms/orcl/ORCL

--Run below command to purse logs older than 1 day.
adrci> purge -age 1440 -type alert
adrci> exit

If you still see the error from Monitoring tools like TOAD, FOGLIGHT contact the vendor. If the issue is not from monitoring tools then contact Oracle

Refer  Metalink note 2056666.1  for more info.

Regards

Satishbabu G, Oracle ACE

http://oracleracepxert.com

RENAME Table or Column or Index in Oracle

RENAME Table
You can rename an existing table in any schema except the schema SYS. To rename a table you must be either DB owner or Table owner.

Use RENAME TABLE to rename a table.

Syntax: RENAME TABLE table-Name TO new-Table-Name
Ex:- RENAME TABLE EMPLOYEE TO EMPLOYEE_ACT

If you have a view or foreign key that references the table, attempts to rename it will generate an error. Also if there are any check constraints or triggers on the table, attempts to rename it will also generate an error.

RENAME Column: Use the RENAME COLUMN to rename a column in a table.

ex: RENAME COLUMN EMPLOYEE.Employee_ID TO EMP_ID;

You can use ALTER TABLE and RENAME COLUMN to modify Column data type

ALTER TABLE EMPLOYEE ADD COLUMN EMP_ID NEWTYPE
UPDATE EMPLOYEE SET EMP_ID = Employee_ID
ALTER TABLE EMPLOYEE DROP COLUMN Employee_ID
RENAME COLUMN t. Employee_ID TO EMP_ID

If a view, trigger, check constraint, foreign key constraint then attempt to rename it will generate an error. Also RENAME COLUMN is not allowed if you have any open cursors that reference the column that is being altered.

If there is an index defined on the column then you can still rename. The index will update automatically to refer by its new name

RENAME Index: Use the RENAME Index to rename an index, you cannot rename indexes in SYS schema

Ex:- RENAME INDEX EMPLOYEE_ID_INDEX TO EMP_ID_INDEX

In case if there are any open cursors that reference the index being renamed then RENAME INDEX is not allowed .

Thanks

Satishbabu G, Oracle ACE

http://oracleracepxert.com

How to enable Oracle SQL*Net Client, Server and Listener Tracing

To enable tracing make sure TNS_ADMIN environment variable should be set and add below tracing parameters in sqlnet.ora. Pls note that any trace settings apply to all server processes so you need to careful when making changes on Prod, I would always suggest to test before applying changes on PRD.
Enable SQL*Net server and Client side tracing.
TRACE_LEVEL_CLIENT = SUPPORT
TRACE_UNIQUE_CLIENT = on
TRACE_LEVEL_SERVER = SUPPORT
TRACE_DIRECTORY_CLIENT = \tmp
TRACE_FILE_CLIENT = Client_Trace.log
TRACE_DIRECTORY_SERVER = \tmp
TRACE_FILE_SERVER = Server_Trace.log
TRACE_TIMESTAMP_ CLIENT = ON
DIAG_ADR_ENABLED= OFF

Starting from 11g and higher to enable client, server trace you need to add DIAG_ADR_ENABLED= OFF

SQL*Net Listener tracing
TRACE_FILE_LISTENER = Listener_Trace.log
TRACE_DIRECTORY_LISTENER = \tmp
TRACE_LEVEL_LISTENER = SUPPORT
DIAG_ADR_ENABLED_LISTENER_<LISTENER> = OFF

To start Listener Trace you need to set DIAG_ADR_ENABLED_<listener name> = OFF (must set to OFF)

To enable Tnsping Trace
TNSPING.TRACE_LEVEL = SUPPORT
TNSPING.TRACE_DIRECTORY = <Trace file path>

Enabling Dynamic Listener Tracing
LSNRCTL> set current_listener listener_ORCL
Current Listener is listener_ORCL
LSNRCTL> set trc_level 16
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "trc_level" set to support
The command completed successfully
LSNRCTL> set trc_directory /oracle/diag/tnslsnr/host1/listener_orcl/trace
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "trc_directory" set to /oracle/diag/tnslsnr/host1/listener_orcl/trace

Turn off dynamic tracing using:
LSNRCTL>set current_listener listener
LSNRCTL>set trc_level OFF


Thanks

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

java.net.SocketException: No buffer space available (maximum connections reached?)

I have come across an issue that Tableau server degraded suddenly. We had to restart the services to bring Tableau server online.

We see below errors from the log file and after research, we found that Tableau Server Port Exhaustion caused the issue

Errors in Server event log
TCP/IP failed to establish an outgoing connection because the selected local endpoint was recently used to connect to the same remote endpoint. This error typically occurs when outgoing connections are opened and closed at a high rate, causing all available local ports to be used and forcing TCP/IP to reuse a local port for an outgoing connection. To minimize the risk of data corruption, the TCP/IP standard requires a minimum time period to elapse between successive connections from a given local endpoint to a given remote endpoint.

Error in Tableau log files
2020-07-05 07:10:42.800 -0700 (,,,) StatusServerThread_dataserver_2 : INFO com.tableausoftware.service.thrift.win32.NamedPipeServerTransport - Awaiting client connection on \\.\pipe\dataserver_2-status, handle native@0x1a3c.
2020-07-05 07:10:49.990 -0700 (,,,) FilestoreHostLocatorThread : ERROR org.apache.curator.framework.imps.CuratorFrameworkImpl - Background exception was not retry-able or retry gave up
java.io.IOException: Couldn't instantiate org.apache.zookeeper.ClientCnxnSocketNIO
at org.apache.zookeeper.ZooKeeper.getClientCnxnSocket(ZooKeeper.java:1845)
at org.apache.zookeeper.ZooKeeper.<init>(ZooKeeper.java:452)
at org.apache.curator.utils.DefaultZookeeperFactory.newZooKeeper(DefaultZookeeperFactory.java:29)
at org.apache.curator.framework.imps.CuratorFrameworkImpl$2.newZooKeeper(CuratorFrameworkImpl.java:150)
at org.apache.curator.HandleHolder$1.getZooKeeper(HandleHolder.java:94)
at org.apache.curator.HandleHolder.getZooKeeper(HandleHolder.java:55)
at org.apache.curator.ConnectionState.reset(ConnectionState.java:262)
at org.apache.curator.ConnectionState.start(ConnectionState.java:109)
at org.apache.curator.CuratorZookeeperClient.start(CuratorZookeeperClient.java:191)
at org.apache.curator.framework.imps.CuratorFrameworkImpl.start(CuratorFrameworkImpl.java:259)
at com.tableausoftware.tdfs.common.ZkConnection.<init>(ZkConnection.java:67)
at com.tableausoftware.tdfs.common.ZkConnectionProvider.getConnection(ZkConnectionProvider.java:50)
at com.tableausoftware.tdfs.common.HostLocator.lambda$initialize$1(HostLocator.java:146)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.GeneratedConstructorAccessor105.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at org.apache.zookeeper.ZooKeeper.getClientCnxnSocket(ZooKeeper.java:1843)
... 19 more
Caused by: java.io.IOException: Unable to establish loopback connection at sun.nio.ch.PipeImpl$Initializer.run(PipeImpl.java:94)
at sun.nio.ch.PipeImpl$Initializer.run(PipeImpl.java:61)
at java.security.AccessController.doPrivileged(Native Method)
at sun.nio.ch.PipeImpl.<init>(PipeImpl.java:171)
at sun.nio.ch.SelectorProviderImpl.openPipe(SelectorProviderImpl.java:50)
at java.nio.channels.Pipe.open(Pipe.java:155)
at sun.nio.ch.WindowsSelectorImpl.<init>(WindowsSelectorImpl.java:127)
at sun.nio.ch.WindowsSelectorProvider.openSelector(WindowsSelectorProvider.java:44)
at java.nio.channels.Selector.open(Selector.java:227)
at org.apache.zookeeper.ClientCnxnSocketNIO.<init>(ClientCnxnSocketNIO.java:43)
... 23 more

Caused by: java.net.SocketException: No buffer space available (maximum connections reached?): bind
1. To Identify the dynamic port range

Open a command prompt as an administrator, and run the following command to determine the dynamic port range:

netsh int ipv4 show dynamicportrange tcp
or
netsh int ipv6 show dynamicportrange tcp

The command will display the port number of the first dynamic port, and the number of ports. You need to add the number of ports to the start port to determine the port range.

2. Run below command to view ports currently in use
netstat –n 

3. Run the following command to increase the dynamic port range:

netsh int <ipv4|ipv6> set dynamicportrange <tcp|udp> start=number num=range store=<active/persistent>

Note that port 65535 is the maximum port number . You can start by setting the range to include ports 32767 through 65535

Also refer below article To determine port exhaustion caused the issue.
https://kb.tableau.com/articles/issue/tableau-server-port-exhaustion-problems#:~:text=Increase%20the%20dynamic%20port%20range,is%20the%20maximum%20port%20number.

Refer below article identify port-exhaustion at server side.
https://support.microsoft.com/en-us/help/3123245/update-improves-port-exhaustion-identification-in-windows-server-2012
https://social.technet.microsoft.com/Forums/ie/en-US/b632acdc-a546-4014-a299-4c27781e6c5a/tcpip-failed-to-establish-an-outgoing-connection-event-id-4227?forum=winserverPN
https://technet.microsoft.com/en-us/library/cc938217.aspx 

Thanks & Regards,

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

Duplicating On-premise Database to Oracle Cloud in Oracle 18c

From Oracle 18c, by using DUPLICATE command you can duplicate an on-prem database to Oracle Could. Oracle databases on Oracle Cloud are always encrypted even if no encryption clause is specified during duplication.

Using Oracle RMAN you to perform two main types of database duplications.

• Backup-based duplication – In this method we will use pre-existing RMAN backups or copies of the source database. 

• Active database duplication - The database will be duplicated by copying the live source database over the network to the auxiliary instance. 

Follow the steps to migrate on-prem database to Cloud:

1. Ensure the prerequisites for the DUPLICATION technique are met, see Prerequisites for Duplicating a Database".

2. Configure Recovery Manager to use Oracle Database Backup Cloud Service as the backup destination. Use CONFIGURE command. Pls refer Oracle Cloud Using Oracle Database Backup Service for more details.

Syntax:-
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt
PARMS='SBT_LIBRARY= SBT-library-location-for-backup-module,
SBT_PARMS=(OPC_PFILE=location-of-the-configuration file)';
Ex:-
RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt
PARMS='SBT_LIBRARY=/oracle/18c/lib/libopc.so,
SBT_PARMS=(OPC_PFILE=/oracle/18c/dbs/emp.ora)';
3. Complete the planning tasks, as described in "Planning to Duplicate a Database" 

4. Prepare the auxiliary instance, as described in "Preparing the Auxiliary Instance"

• You must create auxiliary instance as CDB and start instance with enable_pluggable_database=TRUE in the initialization parameter file
• When instructed to create an initialization parameter file for the auxiliary instance, user must copy the file from the source database. This ensures that the auxiliary instance is also a CDB. After you copy the file you need to perform the following steps:
   – Modify the DB_NAME parameter
   – Modify the various destination/location parameters
• Start the auxiliary instance in NOMOUNT mode.

5. Start RMAN and connect to the root as a common user with the SYSBACKUP privilege or SYSDBA.

6. If the source CDB uses encryption, then open the Oracle keystore that contains the master key on the source CDB.

7. Configure RMAN channels, if necessary, as described in "Configuring RMAN Channels for Use in Duplication".

8. On the destination CDB, open the Oracle keystore from the source CDB. If the destination CDB uses a password-based software keystore, then you must specify the password used to open this keystore

SET DECRYPTION WALLET OPEN IDENTIFIED BY 'password';

9. Use the DUPLICATE command to duplicate the source CDB.

Use one of the following options of the DUPLICATE command:

• DUPLICATE DATABASE or DUPLICATE...ACTIVE DATABASE - Use this command for duplicating non-CDBs and CDBs.
• DUPLICATE DATABASE ... FOR STANDBY - Use this command create a standby database by duplicating the source.
• Use the DUPLICATE DATABASE ... FOR FARSYNC – Use this command to create an Oracle Data Guard far sync instance using duplication.
• DUPLICATE PLUGGABLE DATABASE – Use this command to duplicate one or more PDBs while connected to the root.

You can also use SET NEWNAME command to specify alternate names for duplicate database files,

Note that Using duplication to create a standby database to Oracle Cloud is not supported

Regards

Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

Relocated PDBs in Oracle Database 18c

Oracle 18c allows RMAN backups created before the non-CDB or PDB was migrated into a different target CDB can be used for recovery operations. The COMPATIBLE parameter of the source and Oracle Cloud must be set to 18.0.0 or higher

The RMAN commands used to backup and recovery CDBs and PDBs are the same as those used for non-CDBs, with few variations in the syntax.

The backup and recovery operations performed on non-CDBs can also be performed on CDBs and PDBs. This includes the following:
– Full and incremental backups
– Complete and point-in-time recovery (PITR)
– Reporting operations (such as listing and cross-checking backups)
– Flashback Database

We need take metadata for the existing backups and available to the destination CDB. To export metadata user needs to run DBMS_PDB.EXPORTRMANBACKUP procedure on the source database.

EXECUTE DBMS_PDB.exportrmanbackup();
Or
EXECUTE DBMS_PDB.exportrmanbackup('EMP_PDB');

In case if you are unplug you no need to run this command as unplug already includes the metadata.

Convert NON-CDB to PDB : As we have metadata, now we can covert the NON-CDB instance to PDB.

Step1: Open the non-CDB instance in read-only mode and describe and shutdown

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP OPEN READ ONLY;
SQL> BEGIN
DBMS_PDB.DESCRIBE( pdb_descr_file => '/oracle/empdb.xml');
END;
SQL> SHUTDOWN IMMEDIATE;

Step2: Create the new pluggable database using the non-CDB description file that we have taken in above step

SQL> CREATE PLUGGABLE DATABASE empdb_pdb USING '/oracle/empdb.xml' COPY;
SQL> ALTER SESSION SET CONTAINER= empdb_pdb;
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
SQL> ALTER PLUGGABLE DATABASE OPEN;
SQL> ALTER PLUGGABLE DATABASE SAVE STATE;

Step3:- Restore and recovery using pre-plugin backup

SQL> ALTER PLUGGABLE DATABASE empdb_pdb CLOSE IMMEDIATE;

RMAN> SET PREPLUGIN CONTAINER=db18cpdb;

RMAN> RESTORE PLUGGABLE DATABASE empdb_pdb FROM PREPLUGIN;
RMAN> RECOVER PLUGGABLE DATABASE empdb_pdb FROM PREPLUGIN;

Sometimes users may come across RMAN-06054 error in that case you need to CATALOG the missing archive log and start the recovery again.

RMAN-06054: media recovery requesting unknown archived log for thread 3 sequence 94983
RMAN>SET PREPLUGIN CONTAINER= empdb_pdb;
RMAN>CATALOG PREPLUGIN ARCHIVELOG 'oracle/archivelog/arc_empdb_pdb_3_94983.arc';
RMAN>RECOVER PLUGGABLE DATABASE empdb_pdb FROM PREPLUGIN;

Perform normal recovery and open the database

RMAN>RECOVER PLUGGABLE DATABASE empdb_pdb;
RMAN>ALTER PLUGGABLE DATABASE empdb_pdb OPEN;


Reference:
Check preplugin backups available to the CDB instance
RMAN> LIST PREPLUGIN BACKUP OF PLUGGABLE DATABASE empdb_pdb;

Preplugin backups are usable only on the destination CDB into which you plug in the source non-CDB or PDB

Regards

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

How to Register or Catalog Backups, Archive logs, data file copy, FRA...etc

When you backup using RMAN all backups  will be registered with catalog. If you have taken backup without catalog and you can also register manually.

Backup pieces copied manually to different location to catalog backup piece run below command.
RMAN> CATALOG BACKUPPIECE /oracle/backup/DB1_1978087_6446005710.bkp' ;

In case if you have taken backup using o/s command and to catalog a datafile copy use below command.
RMAN> CATALOG DATAFILECOPY '/oracle/backup/employee01.bak' LEVEL 0;

In case, archive log location full and you have moved backups to different location using o/s command then you can register the archive logs using below command

RMAN> CATALOG ARCHIVELOG ‘/oracle/backup2/db1_arch_99089_0876589.arc'
If you are using Flash recovery area and want to catalog all files in the FRA use below command.
RMAN> CATALOG recovery area;

This command will prompt to confirm that you want the found files to be cataloged. If you do not want to prompt use “noprompt” clause.
RMAN> CATALOG recovery area noprompt;

If you want to catalog only files Start with specific name or belongs to specific SID (db1) use below command
RMAN> CATALOG START WITH '/oracle/backup/db1'

If you run CROSSCHECK, it will go through the files that match criteria and will be marked as available, and files not found will be marked as EXPIRED.

RMAN> CROSSCHECK BACKUP ;
If you want to DELETE EXPIRED backups you can use below command, note that EXPIRED copy will no longer appear in RMAN. You have to be extra cautious to delete backups.

RMAN> DELETE EXPIRED BACKUP ;

If you find extra copies and want to delete those extra copies use DELETE BACKUPPIECE command:
RMAN> DELETE BACKUPPIECE 4555;
Hope this helps managing your backups taken outside of RMAN recovery catalog .

Thanks & Regards 

Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

ORA-19815: WARNING: db_recovery_file_dest_size of 128849010 bytes is 99.03% used, and has 14136528 remaining bytes available.

You will receive ORA-19815 is reaching maximum size of flash backup recovery area (FRA) and note that this limitation will be managed by db_recovery_file_dest_size parameter

If you got this error as one time then you might be running adhoc load or some batch job. Also you will get below message in alert.log file. Please follow one of the choice to fix the issue.

ORA-19815: WARNING: db_recovery_file_dest_size of 128849010 bytes is 99.03% used, and has 14136528 remaining bytes available.
2020-02-21T11:31:19.892420-07:00

************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.

In case if you archive logs are going to FRA then you should run RMAN backups and delete the unnecessary files to avoid Database hung or CRASH situation.

RMAN> backup archivelog all delete input;

If the Flash recovery area is 100% then you might receive below error when starting up the database

Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 9789
Session ID: 321 Serial number: 3

Pls make sure you follow below steps to add more space to flash recovery area (FRA)

SQL> startup mount;
SQL> alter system set db_recovery_file_dest_size=<size>G scope=both;
Replace <size> with a number larger than the current and ensure is sufficient space in the destination
SQL> alter database open;

To see what FRA has run below command

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID

-------------------- ------------------ ------------------------- --------------- ----------

CONTROL FILE                          0                         0               0          0

REDO LOG                              0                         0               0          0

ARCHIVED LOG                          0                         0               0          0

BACKUP PIECE                          0                         0               0          0

IMAGE COPY                            0                         0               0          0

FLASHBACK LOG                     99.76                         0             192          0

FOREIGN ARCHIVED LOG                  0                         0               0          0

AUXILIARY DATAFILE C                  0                         0               0          0

In my case I am using for FLASHBACK LOG, these logs are different from redo logs or undo. They are a separate, optional recording of database activity.

These logs are automatically deleted when FRA is our of space and logs is not needed for recvoery within the window specified in DB_FLASHBACK_RETENTION_TARGET parameter

To determine the disk quota and current disk usage in the flash recovery area, query the view
V$RECOVERY_FILE_DEST.

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

When using Data Guard broker Error: ORA-16627: operation disallowed since no member would remain to support protection mode

You will encounter this error when PROTECTION mode is not consistent betweeen PRIMARY and STANDBY. Make sure you are using DGMGRL to update the configurationn.

DGMGRL> show configuration;
Configuration - oradb_dg_fsfconf
Protection Mode: MaxPerformance
Members:
oradb - Primary database
oradb_stdby - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 13 seconds ago)

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Error: ORA-16627: operation disallowed since no member would remain to support protection mode Failed.

=============

Make sure you verify the entire configuration using verbose.

DGMGRL> show configuration verbose;
Configuration - oradb_dg_fsfconf
Protection Mode: MaxPerformance
Members:
oradb - Primary database
oradb_stdby - Physical standby database

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
bserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = 'ORADB_CFG'

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS

DGMGRL> show database verbose ORADB;
Database - oradb
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORADB

Properties:
DGConnectIdentifier = 'oradb'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'scllqa0078'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=seroradb-db)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORADB_DGMGRL)(INSTANCE_NAME=ORADB)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'

Log file locations:
Alert log : /home/oracle/diag/rdbms/oradb/ORADB/trace/alert_ORADB.log
Data Guard Broker log : /home/oracle/diag/rdbms/oradb/ORADB/trace/drcORADB.log

Database Status:
SUCCESS

DGMGRL> show database verbose ORADB_STDBY;

Database - oradb_stdby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 26.00 KByte/s
Active Apply Rate: 938.00 KByte/s
Maximum Apply Rate: 940.00 KByte/s
Real Time Query: OFF
Instance(s):
ORADB

Properties:
DGConnectIdentifier = 'oradb_stdby'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'scllqa0120'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraoradb-dg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORADB_STDBY_DGMGRL)(INSTANCE_NAME=ORADB)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'

Log file locations:
Alert log : /home/oracle/diag/rdbms/oradb_stdby/ORADB/trace/alert_ORADB.log
Data Guard Broker log : /home/oracle/diag/rdbms/oradb_stdby/ORADB/trace/drcORADB.log

Database Status:
SUCCESS
Make sure you update the LogXptMode and set the protection mode.

DGMGRL> EDIT DATABASE ORADB SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL> EDIT DATABASE ORADB_STDBY SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated

DGMGRL> show configuration;
Configuration - oradb_dg_fsfconf

Protection Mode: MaxPerformance
Members:
oradb - Primary database
oradb_stdby - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 32 seconds ago)

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.

DGMGRL> show configuration;

Configuration - oradb_dg_fsfconf
Protection Mode: MaxAvailability
Members:
oradb - Primary database
oradb_stdby - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 57 seconds ago) Regards,

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

ORA-00230: operation disallowed: snapshot control file enqueue unavailable

User might encounter below error, in case if RMAN backup is already running and user might have triggered another backup. The second backup must wait control file enqueuer to create a snapshot of the controlfile.

RMAN-03002: failure of backup command at 04/10/2020 16:40:33
ORA-00230: operation disallowed: snapshot control file enqueue unavailable
You can run below command to find out the backup that is running and causing the wait

SELECT S.SID, USERNAME , PROGRAM, MODULE,
ACTION, LOGON_TIME FROM V$SESSION s, V$ENQUEUE_LOCK l
WHERE l.SID = s.SID AND l.TYPE = 'CF' AND l.ID1 = 0 AND l.ID2 = 2;

To avoid this error make sure that backups are not overlapping each other.

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

ORA-01665: control file is not a standby control file

Users may see this error when trying to mount standby database after restore or during the switch over

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE MOUNT STANDBY DATABASE
*
ERROR at line 1:
ORA-01665: control file is not a standby control file

SQL> SELECT database_role FROM v$database;
DATABASE_ROLE
----------------------------------------------------------------
PRIMARY

In current scenario, we see the database role as primary and trying to convert as Physical Standby. You can convert when database not mounted, otherwise you will see below error.

SQL> Alter database convert to physical standby;
Alter database convert to physical standby
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> shutdown immediate
SQL> startup nomount;

SQL> Alter database convert to physical standby;
Database altered.
SQL> SELECT database_role FROM v$database;
DATABASE_ROLE
----------------------------------------------------------------
PHYSICAL STANDBY
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;
Database altered.

Make sure you start the recovery using below command

sql> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

To avoid any user mistakes I would advise to use Data Guard broker

Refer below presentation related to Data Guard.

1. “Why Oracle DataGuard? New Features in Oracle 18c,19c”
https://www.oracleracexpert.com/2019/12/why-oracle-dataguard-new-features-in.html

2. Data Guard Physical Standby Setup in Oracle?
https://www.oracleracexpert.com/2019/02/webinar-data-guard-physical-standby.html

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

All redo log files were not defined properly and Operation RENAME is not allowed

All redo log files were not defined properly and Operation RENAME is not allowed

We have created the Standby database using RMAN in Oracle 19c and received following error after standby DB restore.

RMAN-05535: warning: All redo log files were not defined properly.
Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

I have seen this error in the newer Oracle versions only and this warning can be ignored as parameter STANDBY_FILE_MANAGEMENT is set to AUTO

During recovery I also come across below error, this is because I have not allocated any auxiliary channel.

run{
allocate channel PR1 type disk;
allocate channel PR2 type disk;
duplicate target database for standby from active database dorecover nofilenamecheck
spfile
set db_unique_name='ORCLT_STDBY'
set standby_file_management='AUTO'
}

During the upgrade
RMAN-05501: aborting duplication of target database
RMAN-05503: at least one auxiliary channel must be allocated to execute this command

I have added below command in the script and restore worked fine.

allocate auxiliary channel STDBY type disk;

Check presentation on Data Guard Physical Standby Setup in Oracle?

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

ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

I have come across issue when adding Standby Database during Data Guard broker config

DGMGRL> create configuration 'ORCLT' as primary database is 'ORCLT' connect identifier is ORCLT;
Configuration "ORCLT" created with primary database "ORCLT"
DGMGRL> add database 'ORCLT_STDBY' as connect identifier is ORCLT_STDBY maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

I have not seen this issue on older versions, after research found that there are few changes from Oracle 12c. We have to unset LOG_ARCHIVE_DEST_2 parameter as DGMGRL will take care of updating this parameter.

I have followed below steps

1. Disable and Remove Data Guard configuration

DGMGRL> disable configuration;
DGMGRL> remove configuration;

2. Disable/Enable Data Guard broker on both Primary/standby

SQL> alter system set dg_broker_start=false scope=both;
SQL> alter system set dg_broker_start=true scope=both;

3. Disable log_archive_dest_2 on both Primary/standby
SQL> alter system set log_archive_dest_2'' scope=both;

4. Configure Data Guard Broker

DGMGRL> create configuration ‘orclt_dg’ as primary database is 'ORCLT' connect identifier is ORCLT;
Configuration "ORCLT" created with primary database "ORCLT"
DGMGRL> add database 'ORCLT_STDBY' as connect identifier is ORCLT_STDBY maintained as physical;
DGMGRL> Database 'ORCLT_STDBY' added
DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;
Configuration – orclt_dg
Protection Mode: MaxPerformance
Members:
orclt - Primary database
orclt_stdby - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 46 seconds ago)

Regards,

Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

Webinar: What’s New in Oracle Database 19c?

Oracle Database 19c has many exciting new features and in order to take advantage of these features you need to upgrade the databases from older versions to Oracle 19c

Join the Webinar to learn New Features of Oracle Database 19c

Date and time: Mar 31st 2018 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

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

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

For Presentation, link "Click here"

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

ORA-16416: No viable Physical Standby switchover targets available

You will see this error when you are trying to switchover from Primary to Standby, you will see the error when log_archive_dest_n not ready at standby or there is some issue with REDO log.

Also note that you will see this error if f there is an archive gap, that prevents switchover because of which the operation fails

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
*
ERROR at line 1:
ORA-16416: No viable Physical Standby switchover targets available

Error from standby alert.log file

.... (PID:24072): No viable Physical Standby switchover targets available
ORA-16416 signalled during: ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY... 2020-02-27T19:36:42.425842-08:00
rfs (PID:36824): Database mount ID mismatch [0x5a5dee6f:0x5a5d7a7b] (1516105327:1516075643)
rfs (PID:36824): Not using real application clusters
2020-02-27T19:38:17.144104-08:00
.... (PID:24072): Using STANDBY_ARCHIVE_DEST parameter default value as /oraarch/ORCLP krsd.c:18141]
2020-02-27T19:38:17.154204-08:00
ALTER SYSTEM SET log_archive_dest_2='SERVICE=ORCLP AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLP' SCOPE=BOTH;
2020-02-27T19:38:21.552799-08:00
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
2020-02-27T19:38:21.552940-08:00
TMI: adbdrv kcv_primary_switchover BEGIN 2020-02-27 19:38:21.552864
2020-02-27T19:38:21.553038-08:00
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 24072] (ORCLP)
.... (PID:24072): Waiting for target standby to receive all redo
.... (PID:24072): Waiting for target standby to receive all redo
2020-02-27T19:38:21.553572-08:00
.... (PID:24072): Waiting for all non-current ORLs to be archived
2020-02-27T19:38:21.553661-08:00
.... (PID:24072): All non-current ORLs have been archived
2020-02-27T19:38:21.553741-08:00
.... (PID:24072): Waiting for all FAL entries to be archived
2020-02-27T19:38:21.553820-08:00
.... (PID:24072): All FAL entries have been archived
2020-02-27T19:38:21.553924-08:00
.... (PID:24072): Waiting for potential Physical Standby switchover target to become synchronized
2020-02-27T19:38:21.598598-08:00
Errors in file /home/oracle/diag/rdbms/ORCLP_stdby/ORCLP/trace/ORCLP_tt00_16934.trc:
ORA-16009: invalid redo transport destination
2020-02-27T19:38:21.598892-08:00
TT00 (PID:16934): krsg_check_connection: Error 16009 connecting to standby 'ORCLP'
2020-02-27T19:38:22.554301-08:00

Run below command to find out the Archive Destination status errors..

SQL> select DEST_ID, STATUS, DESTINATION from V$ARCHIVE_DEST_STATUS;
SQL> select DEST_ID,DEST_NAME,STATUS,TARGET,DESTINATION,ERROR,ALTERNATE from v$archive_dest

SQL> select GROUP#,THREAD#,BYTES/1024/1024/1024,ARCHIVED,STATUS from v$standby_log;

Also run below command to identify the gap

SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

After running the above commands analyzing the logs I see that the error due to invalid REDO LOG destination. I have created the directories as same as Primary on Standby site.

I also come across issue with LOG_ARCHIVE_DEST_2, after fixing the parameter it worked fine. Also, make sure LOG_ARCHIVE_DEST_STATE_2 enabled.

ORA-16474: target_db_name not found in the LOG_ARCHIVE_DEST_n parameter

I would strongly advise to use Data Guard Broker, it minimizes the errors, and switchover is very easy.

Refer below Data Guard presentation
Webinar: Data Guard Physical Standby Setup in Oracle?

Regards

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

ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O

User may receive below error when connecting to Database, I have encountered this issue in various versions of Oracle such as 10g, 11g, 12c and 19c.

ERROR:
ORA-01017: invalid username/password; logon denied
ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Additional information: 128
Additional information: 2022083592

You may see below messages in alert.log

ORA-27090: Message 27090 not found; product=RDBMS; facility=ORA
Additional information: 3
Additional information: 128
Additional information: 65536
OR

ORA-27090: UNABLE TO RESERVE KERNEL RESOURCES FOR ASYNCHRONOUS DISK I/O
Additional information: 3
Additional information: 128
Additional information: 65536
OR

ORA-27090: Message 27090 not found; product=RDBMS; facility=ORA
Linux-x86_64 Error: 4: Interrupted system call
Additional information: 3
Additional information: 128
Additional information: 65536

DBWR trace
io_setup(4096, 0x66e1588) = -1 EAGAIN (Resource temporarily unavailable),

You will see above error when "aio-max-nr" kernel limit is too low.

Increasing the "aio-max-nr" kernel limit as per the Oracle recommendation. I have set the value as
fs.aio-max-nr= 3145728 in my environment.

Thanks

Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

ORA-00600, ORA-07445 and ORA-00020 errors and related bugs in 12c (12.2.0.1)

I have come across below error recently in 12c (12.2.0.1). It first started with ORA-00600 followed by ORA-07445 and ORA-00020. The ORA-00600, ORA-07445 looks like real culprit and which lead to ORA-00020 error.

ORA-00600: internal error code, arguments: [17126], [0x0B0D29528], [], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2020-01-13T23:20:30.493308-08:00
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x32000000170] [PC:0x10F9D3FD, kghfnd_in_free_lists()+717] [flags: 0x0, count: 1]
Errors in file /home/oracle/diag/rdbms/ORADB/ORADB/trace/ORADB_ora_62437.trc (incident=41538):
ORA-07445: exception encountered: core dump [kghfnd_in_free_lists()+717] [SIGSEGV] [ADDR:0x32000000170] [PC:0x10F9D3FD] [Address not mapped to object] []
Incident details in: /home/oracle/diag/rdbms/ORADB/ORADB/incident/incdir_41538/ORADB_ora_62437_i41538.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2020-01-13T23:20:31.182120-08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2020-01-13T23:20:31.182223-08:00
opidcl aborting process unknown ospid (48392) as a result of ORA-600
……

2020-01-14T01:29:37.222083-08:00
ORA-00020: maximum number of processes (500) exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
After investigation and troubleshooting I found below bugs

1. There is a unpublished bug related to Unified memory KGH related crashes/errors. After discussing with support come to know that particular fix was found on an internal note to already be included in Oracle 12.2.0.1

Bug 30053838 - LNX-20-ATP: HIT ORA-7445 [KGHFND_IN_FREE_LISTS] AND ORA-600 [KSM_PGA_UM_EXT_FREE:INVALID_EXT_MAGIC], INST CRASH

2. There is a published bug 24596874 and the fix fix for 24596874 is first included in 20.1.0 . Also in term patches may be available for earlier versions.

For bug 24596874 , users notice Memory corruption when CDB environment is setup with star schema on one PDB. Redaction policies enabled on tables/view of the schema and query workload executed with 50 concurrent sessions. Multiple ORA 600 [kghfrh:ds] were seen in the alert log.

So both of the above bugs are ruled out as unpublished bug already part of 12.2.0.1 and published bug is related to Star schema on PDB.

After further investigation with Oracle support found another unpublished Bug 30448845 : DATABASE HANG WITH SIGNATURE: 'PMON TIMER'<='PRIOR SPAWNER CLEAN UP'<='LATCH FREE'<='RELIABLE MESSAGE'

As per Oracle support, this is fixed in 18.1 but the fix is not easy to backports. Oracle suggest to update parameter _pmon_slaves_arr_size=0 as workaround in Oracle 12.2

Users cannot update internal oracle parameters directly in SPFILE, please follow the steps mentioned in the below blog post
https://www.oracleracexpert.com/2020/01/ora-00800-soft-external-error-arguments.html

Note that every environment, errors, load, use cases are different, I would highly recommend users to open a ticket with Oracle support and get confirmation before making any changes to your databases.
Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com 

ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes]

When working with Oracle DB Version 19.5 recently noticed below errors in alert.log file

Starting background process VKTM
2020-02-04T17:05:58.711181-08:00
Errors in file /home/oracle/diag/rdbms/testdb/TESTDB/trace/TESTDB_vktm_43832.trc (incident=42521):
ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Incident details in: /home/oracle/diag/rdbms/testdb/TESTDB/incident/incdir_42521/TESTDB_vktm_43832_i42521.trc
2020-02-04T17:05:58.713567-08:00
Error attempting to elevate VKTM's priority: no further priority changes will be attempted for this process
VKTM started with pid=5, OS id=43832

After research found that parameter _high_priority_processes="VKTM" may help, but it is a internally parameter you cannot able to apply directly on SPFILE. I always raise a ticket with Oracle support to got the confirmation before making changes.

This issue has been reported in the internal bug - Bug 30664554 : ORA-00800: SOFT EXTERNAL ERROR, ARGUMENTS: [SET PRIORITY FAILED], [VKTM]
In order to update internal parameters on a DB using SPFILE you need to follow bellows steps.

1. Take a backup of SPFILE and PFILE to revert changes in case of any isuses.
2. Create PFILE from SPFILE
SQL> create pfile=’$ORACLE_HOME/dbs/initTESTDB.ora’ from spfile
3. Add/update the parameter in PFILE
4. Rename SPFILE and stop/start the DB to use PFILE that was created
5. Create SPFILE from PFILE
SQL> create SPFILE=’$ORACLE_HOME/dbs/spfileTESTDB.ora’ from pfile;
6. stop/start the DB to use SPFILE that was created
Now the changes are applied to SPFILE.

Note that every environment, errors, load, use cases are different, I would highly recommend users to open a ticket with Oracle support and get confirmation before making any changes to your databases.
Thanks
Satishbabu Gunukula,ORACLE ACE
https://oracleracexpert.com

ORA-00942: table or view does not exist

When calling a table directly or through procedure/function...etc user may receive error “ORA-00942: table or view does not exist”.

For ex:-
CREATE OR REPLACE FUNCTION PROC1
(Num IN NUMBER)
RETURN NUMBER
IS
BEGIN
INSERT INTO Table1 SELECT * FROM SCOTT.Table2 WHERE ID = Num;
END;

PL/SQL: ORA-00942: table or view does not exist

SQL> select * from table1;

select * from table1

              *

ERROR at line 1:

ORA-00942: table or view does not exist

Possible reasons:
1. The table name or view name spelled wrongly
2. The table or view doesn’t exist
3. The user doesn’t have required permissions

In some cases users have select access and able to query data but when running from a procedure they still receive “ORA-00942: table or view does not exist”

The reason for this error is the access granted trough a ROLE not directly. In order to access another user table from a procedure you need to have SELECT privilege granted directly.

Regards,

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

Webinar: What’s new in Oracle 19c & 18c Recovery Manager (RMAN)?

Oracle Database 19c & 18c offers new enhancements and additions in Recovery Manager (RMAN). Join the Webinar to learn New Features in Oracle RMAN and take advantage of new features for efficient backup & recovery.

Date and time: Jan 17th 2020 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

Join the Webinar to learn New Features in Oracle RMAN 19c & 18c

• Overview of RMAN
• PLUGGABLE DATABASE clause in GRANT and REVOKE commands
• Recovery catalog support for PDBs
• Duplicate PDBs to an existing CDB
• Duplicate databases to Oracle Cloud
• RMAN backups usable after migration
• Demo
• 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