Online DBA

<<Back to DB Administration Main Page DBUA does not list the  database to upgrade Cause :  in correct /etc/oratab entry cat /etc/oratab ORCL1P:/u01/product/11.2.0.4/dbp1 Solution : modify the /etc/oratab as shown below ORCL1P:/u01/product/11.2.0.4/dbp1 :Y

<<Back to Oracle RAC Main Page CLSRSC-101: Could not copy OCR locations Problem Summary Encountered below error while running the root.sh. I was trying to add a node to RAC cluster. econfigured the server and tried to add to CLUSTER01 cluster using addNode.sh script. root.sh is now failing with the errros: Using configuration parameter file: /grid_home/oracle/12.1.0.2/grid/crs/install/crsconfig_params 2020/11/21 10:15:30 CLSRSC-4001: Installing Oracle Trace File Analyzer (TFA) Collector. 2020/11/21 10:15:30 CLSRSC-4002: Successfully installed Oracle Trace File Analyzer (TFA) Collector. 2020/11/21 10:15:36 CLSRSC-101: Could not copy OCR locations 2020/11/21 10:15:36 CLSRSC-293: Error: validation of OCR location 'NO_VAL' failed Died at /grid_home/oracle/12.1.0.2/grid/crs/install/crsinstall.pm line 1696. The command '/grid_home/oracle/12.1.0.2/grid/perl/bin/perl -I/grid_home/oracle/12.1.0.2/grid/perl/lib -I/grid_home/oracle/12.1.0.2/grid/crs/install /grid_home/oracle/12....

<<Back to DB Administration Main Page Installing oracle client in silent mode Step1> download oracle client software Step2> Transfer the software on server you want to install Step3> unzip the software Step4> Adjust response file ?/response/client_install.rsp UNIX_GROUP_NAME=dba INVENTORY_LOCATION=/ora_app/oraInventory ORACLE_HOME=/ora_app/product/12.1.0.2/client_1 ORACLE_BASE=/ora_app oracle.install.client.installTyp e=Administrator Step5> Run installation in silent mode ./runInstaller -ignoreSysPrereqs -showProgress -silent -responseFile /ora_app/client/response/client_install.rsp Starting Oracle Universal Installer... Checking Temp space: must be greater than 415 MB.   Actual 4442 MB    Passed Checking swap space: must be greater than 150 MB.   Actual 2047 MB    Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-11-26_09-57-24AM. Please wait ... [WARNING] [INS-13001] Environment does no...

<<Back to Oracle Backup & Recovery Main Page RMAN-06808: SECTION SIZE cannot be used when piece limit is in effect Cause:  MAXPIECESIZE and SECTION SIZE Parameter can not be used together. Solution: Use either MAXPIECESIZE or SECTION SIZE  

<<Back to Oracle Backup & Recovery Main Page RMAN-20005: target database name is ambiguous RMAN> RESTORE CONTROLFILE FROM TAG 'PR_WEEKLY_INCR0'; Starting restore at 26-OCT-20 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 10/26/2020 09:43:38 RMAN-12010: automatic channel allocation initialization failed RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous Cause: Multiple registration of the database in catalog. Run the query below against catalog database to find out the duplicate entry  SQL>SELECT DB.DB_KEY,DB.DB_ID, DB.CURR_DBINC_KEY, DBINC.DB_NAME         FROM DB, DBINC        WHERE DB.CURR_DBINC_KEY = DBINC.DBINC_KEY     ...

<<Back to Oracle DATAPUMP Main Page DataPump Export (EXPDP) Error ORA-39095 Dump File Space Has Been Exhausted ORA - 39095 : " dump file space has been exhausted. Unable to allocate 8192 bytes" job system.sys_export_full_02 stops due to fatal error. cause: Not enough number of dump files defined expdp can create maximum 99 dumpfiles so if you are exporting very large database ensure to define filesize big enough so the total export can fit in <=99 dumpfiles The issue can also occur if you specify the dumpfiles manually for example if your parfile looks  like this full=Y directory=<DIRECTORY_NAME> dumpfile=full_1.dmp,full2_2.dmp,full_3.dmp filesize=10 if export did not finish in 3 dumpfiles and need to created 4th one you will receive the error ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes Job "SYSTEM"."sys_export_full_02" stopped due to fatal error at Wed Oct 7 21:13:55 2020 elapsed 0 10:09:02 Job sys_export_fu...

<<Back to Oracle DATAPUMP Main Page How to find if the export job is running or Hanging  SET PAUSE ON SET PAUSE 'Press Return to Continue' SET PAGESIZE 60 SET LINESIZE 300 col MODULE for a30 col EVENT for a50 col SQL_ID for a15 col STATUS for a10 SELECT   v.status,   v.SID,   v.serial#,   io.block_changes,   event, module,   v.sql_id FROM   v$sess_io io,   v$session v WHERE   io.SID=v.SID AND   v.saddr IN (SELECT saddr FROM dba_datapump_sessions)  ORDER BY   io.BLOCK_CHANGES / Observe the BLOCK_CHANGES column if the value keeps increasing , your  job is running and not hanging

<<Back to Oracle DATAPUMP Main Page Is there any limit on number of files expdp can create Yes there is a limit.  The total number of dump files that can be created by expdp is  99. so if you specify dumpfile parameter like below and your export did not finish within 99 dumpfiles export will stop therefore if you are exporting big database specify bigger filesize  dumpfile=exp_full_%U.dmp 

<<Back to DB Administration Main Page How to Convert Classical Database in Multitenant Architecture Database Source DB (Classical Architecture) : ORCL1P Target DB (Multitenant Architecture) : CDB01DB ORCL1P to be plugged as PDB01P into  CDB01DB Container DB ( CDB01DB ) should already be installed and present NOTE: Demonstration in this post assumes the conversion on same host Execute on  ORCL1P Step1>Place the non-CDB in read-only mode SQL>select name from v$database; SQL>shutdown immediate; SQL>startup open read only; SQL> select name,open_mode from v$database; Step2> Prepare Conversion BEGIN  DBMS_PDB.DESCRIBE(  pdb_descr_file => '/ora_temp/rman/ORCL1P/PDB01P.xml'); END; / Step3> shutdown the NON-CDB database ( NONCDB) SQL>shutdown immediate; Execute on  CDB01DB Step4> Check the compatibility. Now connect to the container database, where it need to be plugged. SET SERVEROUTPUT ON DECLARE  compatible CONSTANT VARCHAR2(3)...

<<Back to DB Administration Main Page How to Upgrade from 11g,12c,18c to 19c DB Upgrade Compatibility Matrix Below is minimum version of the database that can be directly upgraded to Oracle 19c. Source Database Target Database 11.2.0.4 19c 12.1.0.2 19c 12.2.0.1 19c 18.1.0.0 19c NOTE: In this post I will demonstrate the upgrade from 12.1.0.2 to 19c. The steps are same for any other version to 19c upgrade. Upgrading the database from 12.1.0.2 to 19c I will be using DBUA its really easy to use and self explanatory as well as recommended options. It automates many things including upgrading Time zone, gathering dictionary statistics , taking restore RMAN backup or creating restore point , starting the listener with new OH etc. etc. Although DBUA can do many thing its recommended to everything possible that can be done upfront to minimize the overall downtime. General Requirement Ensure All DB Components and objects are valid....

<<Back to Oracle Data Guard Broker Page Oracle Dataguard BystandersFollowRoleChange Property  BystandersFollowRoleChange Valid Values:         ALL| NONE Broker Default:     ALL Lets first look at What is Bystander Database ? The concept of bystander comes only if you have multiple standby databases. Bystander standby database is the standby database that is not directly involved in switchover or failover. In other words all standby databases except failover target database is considered as bystander standby. v$database.fs_failover_status = 'BYSTANDER' can we queried to find the list of bystander databases. The BystandersFollowRoleChange configuration property establishes whether bystander standby databases are evaluated during failover (value = ALL) or after failover (value = NONE). Descriptions of the ALL and NONE options are as follows: ALL - During the failover process, the broker determines whether the bystander standby databases are ahe...

<<Back to Oracle Data Guard Broker Page Oracle Dataguard FastStartFailoverPmyShutdown Property  FastStartFailoverPmyShutdown Valid Values:       TRUE| FALSE Broker Default:    TRUE The FastStartFailoverPmyShutdown configuration property causes the primary database to shut down if fast-start failover is enabled and V$DATABASE.FS_FAILOVER_STATUS indicates the primary has been STALLED for longer than FastStartFailoverThreshold seconds. In such a situation, it is likely that the primary has been isolated and a fast-start failover has already occurred. A value of TRUE helps to ensure that an isolated primary database cannot satisfy user queries. DGMGRL> edit configuration set property FastStartFailoverPmyShutdown=TRUE; Property "FastStartFailoverPmyShutdown" updated

<<Back to Oracle Data Guard Broker Page Oracle Dataguard FastStartFailoverAutoReinstate Property  FastStartFailoverAutoReinstate Valid Values:      TRUE| FALSE Broker Default:   TRUE If set to true this property caused the former primary database to automatically re-instantiate and become Standby in case failover occurs. If you want to prevent the automatic re-instantiation set the property value FALSE NOTE: Automatic re-instantiate will only succeed if the database is already configured in flashback mode and necessary archive logs are still present.   DGMGRL> edit configuration set property FastStartFailoverAutoReinstate=FALSE; Property "FastStartFailoverAutoReinstate" updated