Online DBA

<<Back to Oracle DataGuard Main Page To use sqlplus instead broker follow the steps on this blog HOW TO CONVERT PHYSICAL STANDBY DATABASE TO SNAPSHOT STANDBY DATABASE USING SQLPLUS HOW TO CONVERT PHYSICAL STANDBY DATABASE TO SNAPSHOT STANDBY DATABASE USING DGMGRL- Dataguard Broker Utility Current Configuration Primary DB: ORCL1PP Standby DB: ORCL1PS Connect to primary database  over listener dgmgrl sys/xxxxx@ORCL1PP.ORACLE.COM Disable Fast Start Failover if enabled DGMGRL> DISABLE FAST_START FAILOVER; Change to maximum performance: DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE; Convert to snapshot standby DGMGRL> CONVERT DATABASE ORCL1PS TO SNAPSHOT STANDBY; CONVERING BACK THE SNAPSHOT STANDBY TO PHYSICAL STANDBY USING DGMGRL- Dataguard Broker Utility Connect to primary database  over listener dgmgrl sys/xxxxx@ORCL1PP.ORACLE.COM Convert to standby DGMGRL> CONVERT DATABASE ORCL1PS TO PHYSICAL STANDBY; Change back to maxim

<<Back to Oracle DataGuard Main Page HOW TO CONVERT PHYSICAL STANDBY DATABASE TO SNAPSHOT STANDBY DATABASE MANUALLY USING SQLPLUS To open the physical standby database as snapshot standby, Flashback must be on. If not already set, set db_recovery_file_dest and db_recovery_file_dest_size parameter to enable the flashback. SQL> alter system set db_recovery_file_dest='/ora_data/orcl/flash'; System altered. SQL>alter system set db_recovery_file_dest_size=30G; System altered. SQL> recover managed standby database cancel; Media recovery complete. SQL> shutdown immediate; SQL> startup mount; SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; Database altered. SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database; NAME      OPEN_MODE            GUARD_S DATABASE_ROLE --------- -----

<<Back to Oracle DataGuard Main Page HOW TO ACTIVATE PHYSICAL STANDBY DATABASE WHEN PRIMARY IS NO MORE AVAILABLE If you have lot the primary database and you want to activate the physical standby database as primary you can follow the steps below. SQL> RECOVER MANAGED STANDBY DATABASE CANCEL; Media recovery complete. SQL> SHUT IMMEDIATE SQL> STARTUP MOUNT SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE; OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE -------------------- -------------------- ---------------- MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; Database altered. SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE; Database altered. SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE; OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE -------------------- -------------------- ---------------- MOUNTED         

<<Back to DB Administration Main Page ORA-00068: invalid value 0 for parameter _query_execution_time_limit, must be between 1920170603 and 7041645 looking into the alertlog found below errors Wed Feb 19 14:47:27 2020 WARNING: Oracle executable binary mismatch detected.  Binary of new process does not match binary which started instance issue alter system set "_disable_image_check" = true to disable these messages Wed Feb 19 14:47:27 2020 WARNING: Oracle executable binary mismatch detected.  Binary of new process does not match binary which started instance issue alter system set "_disable_image_check" = true to disable these messages Wed Feb 19 14:47:49 2020 Tried to check the rman config also ended with errors RMAN> connected to recovery catalog database recovery catalog schema release 19.04.00.00. is newer than RMAN release RMAN> echo set on RMAN> show all; RMAN-00571: ========================================================

<<Back to DB Administration Main Page The program can't start because perl510.dll is missing from your computer. While Patching Oracle 12.1.0.2 on windows Solution:  Step1> Note down the value of  PERL5LIB   echo %PERL5LIB% Step2> unset PERL5LIB and retry  SET PERL5LIB= set PATH=%ORACLE_HOME%\perl\bin;%PATH% Step3>  Set the path of  PERL5LIB variable , as it was before.

<<Back to PT Main Page Finding the Bind Variable Value for a Given SQLID in Oracle COLUMN sql_text FORMAT A120 COLUMN sql_id FORMAT A13 COLUMN bind_name FORMAT A10 COLUMN bind_value FORMAT A26 SELECT   sql_id,   t.sql_text sql_text,    b.name bind_name,   b.value_string bind_value,  b.LAST_CAPTURED FROM   v$sql t JOIN   v$sql_bind_capture b  using (sql_id) WHERE   b.value_string is not null  AND   sql_id='&sqlid' / Finding the Historical Bind Variable Value for a Given SQLID in Oracle COLUMN sql_text FORMAT A120 COLUMN sql_id FORMAT A13 COLUMN bind_name FORMAT A10 COLUMN bind_value FORMAT A26 SELECT   sql_id,   t.sql_text sql_text,    b.name bind_name,   b.value_string bind_value,   b.LAST_CAPTURED FROM   v$sql t JOIN   DBA_HIST_SQLBIND b  using (sql_id) WHERE   b.value_string is not null  AND   sql_id='&sqlid' /

<<Back to DB Administration Main Page ORA-00600: Interner Errorcode Argumente: [qctcte1], [0], [], [], [], [], [], [], [], [], [], [] Errors in file /ora_log/diag/rdbms/orcl1p/orcl1p/trace/orcl1p_ora_51422.trc  (incident=37128): ORA-00600: Interner Fehlercode, Argumente: [qctcte1], [0], [], [], [], [], [], [], [], [], [], [] Incident details in: /ora_log/diag/rdbms/orcl1pp/orcl1p/incident/incdir_37128/orcl1p_ora_51422_i37128.trc Use ADRCI or Support Workbench to package the incident. Oracle Support  (Doc ID 248095.1), telling more about this  Sno NB Prob Bug Fixed Description 1   - 29590666 20.1 ORA-600 [qctcte1] on xml query 2 E - 29320900 20.1 select query can fails with ORA-600 [qctcte1] on 12.1 3   II 28537481 20.1 ORA-600[qctcte1] when using inner join and bind variables 4   II 27421733 19.1 ORA-600 [qctcte1] with fix 26025681 presen

<<Back to Oracle DataGuard Main Page How to Use RMAN Recovery Catalog in a Data Guard Configuration If you decided to use recovery catalog, both databases must use the same recovery catalog. You just need to register the current Primary database into recovery catalog and perform backup from either Primary (Site A) or Standby (Site B) while connecting to Recovery catalog. Resync the controlfile with catalog to make backup (taken at Site A ) available at Site B and vice-versa That's said let us see how to do that and what RMAN configuration parameter we must use Step1> Connect to rman catalog and primary database rman target sys/xxxx@Primary_DB catalog rman/xxxx@CAT_DB Step2> Register the database in Recovery catalog RMAN> register database; Step3> Configure Connect Identifier for Primary and Standby database RMAN> configure db_unique_name ORCL1PP connect identifier 'ORCL1PP.oracle.com'; RMAN> configure db_unique_name ORCL1PS connect iden