Announcements

Oracle training based on real-time expertise as per industry standards. Upcoming batch details are here.

View batch schedule

Free Oracle mock interview. Register using the link.

Register

#DBAchallenge: submit your questions.

Ask questions
Swipe for more →
Browse by Topic
Latest Posts

    Tuesday, May 13, 2025

    oratop and orachk powerful tool to monitor and troubleshoot the database/cluster issues

    oratop and orachk powerful tool to monitor and troubleshoot the database/cluster issues


    oratop:

    -------
    Provides near real-time database monitoring.
    For more information, see My Oracle Support note 1500864.1.

    • oratop is an interface that takes data from top and oracle databases on a host.
    • The utility combines the data to OS data and session data.
    • It allows the viewer to see how multiple databases are interacting with the host and identify high CPU consumers, memory consumers, and IO consumers.

    orachk:

    -------
    Health Checks For The Oracle Stack Using ORAchk

    Oracle ORAchk and Oracle EXAchk provide a lightweight and non-intrusive health check framework for the Oracle stack of software and hardware components.

    Oracle ORAchk and Oracle EXAchk:
    • Automates risk identification and proactive notification before your business is impacted
    • Runs health checks based on critical and reoccurring problems
    • Presents high-level reports about your system health risks and vulnerabilities to known issues
    • Enables you to drill-down specific problems and understand their resolutions
    • Enables you to schedule recurring health checks at regular intervals
    • Sends email notifications and diff reports while running in daemon mode
    • Integrates the findings into Oracle Health Check Collections Manager and other tools of your choice
    • Runs in your environment with no need to send anything to Oracle
     
    $ export ORACLE_HOME=<path>
    $ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
    $ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/suptools/oratop:$PATH
    /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/oratop/oratop / as sysdba
    /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/orachk/orachk

    More details are available on this Oracle Documentation


    Installation of oratop:

    Download the oratop utility:
    Use the metalink Doc ID 1500864.1 for download:

    Rename to downloaded file to proper name
    cd /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools
    mv oratop* oratop

    provide execute permission:
    chmod 755 oratop

    Installation of oracheck:

    [root@node1 ~]# cp -r /home/oracle/Desktop/orachk.zip /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/orachk
    [root@node1 ~]# unzip orachk.zip
    [root@node1 ~]# cd /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/
    [root@node1 ~]# chwon oracle:oinstall oracheck
    [oracle@node1 ~]$ ./orachk -v
    [oracle@node1 ~]$ ./orachk -debug 
    [root@node1 ~]#  ./orachk

    Example 1: Running the oratop 


    [oracle@node1 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/oratop/oratop / as sysdba

    oratop: Release 15.0.0 Production on Mon May 12 19:47:24 2025
    Copyright (c) 2011, Oracle.  All rights reserved.

    Connecting ..
    Processing ...
    Oracle 19c - DEV 01:17:17 up:  24d,  2 ins,    0 sn,   0 us, 3.4G sga,     0%db
    ID %CPU %DCP LOAD  AAS  ASC  ASI  ASW  IDL  MBPS  %FR  PGA UTPS  RT/X DCTR DWTR
     2  6.5  0.0  0.4  0.0    0    0    0    0   0.1    5 1.2G    0  3.2m  108    0
     1  6.8    0  0.1    0    0    0    0    0   0.1    8 1.2G    0     0    0    0

    EVENT (C)                        TOT WAITS   TIME(s)  AVG_MS  PCT    WAIT_CLASS
    DB CPU                                         30418           75
    control file sequential read      19574633      5909     0.3   15    System I/O
    IMR slave acknowledgement msg     12738390      2899     0.2    7         Other
    control file parallel write        1575061       691     0.4    2    System I/O
    ASM file metadata operation        3841939       690     0.2    2         Other

    ID   SID     SPID USR PROG S  PGA SQLID/BLOCKER OPN  E/T STA STE EVENT/*LA  W/T

    [oracle@node1 ~]$

    Example 2: Running the orachk


    [oracle@node1 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/orachk/orachk

    Running orachk
    ----------------------------------------------------------
    PATH                             : /u01/app/oracle/product/19.0.0.0/dbhome_1/suptools/orachk
    VERSION                          : 18.4.0_20181129
    COLLECTIONS DATA LOCATION        : /u01/app/oracle/orachk/
    ----------------------------------------------------------

    This version of orachk was released on 29-Nov-2018 and its older than 180 days. No new version of orachk is available in RAT_UPGRADE_LOC. It is highly recommended that you download the latest version of orachk from my oracle support to ensure the highest level of accuracy of the data contained within the report.

    Do you want to download latest version from my oracle support? [y/n] [y] n

    orachk cannot be use as its older than a year.
    Exiting...
    [oracle@node1 ~]$

    Posted by at 2 comments:
    Labels: ,

    Query to get object DDL in Oracle database

    Query to get object DDL in Oracle database: 

    1. DBA will most commonly get request to provide the metadata information of a object in a database


    2. DBA will most commonly get a request a create object on development database similar to object which are in PROD database.
     

    These requests may be for various purpose like

    auditing,

    migration,

    backup purposes etc

     

    Query to GET DDL structure:

    set long 9999999

    set lines 200 pages 2000

    col METADATA format a180 word_wrapped

    select dbms_metadata.get_ddl(upper('&Object_type'),upper('&Object_name'),upper('&Owner')) "METADATA" from dual;

     

    Provide input:

    Enter value for object_type: INDEX/TABLE/VIEW etc 

    Enter value for object_name: NAME_OF_OBJECT 

    Enter value for owner: OWNER_OF_OBJECT

     

    Example:

    1. Connect to database as mallik user and create an dummy table T1

     

    [oracle@node1 ~]$ env |grep ORA

    ORACLE_SID=DEVDB1

    ORACLE_BASE=/u01/app/oracle

    ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

    [oracle@node1 ~]$

    [oracle@node1 ~]$ sqlplus mallik/mallik

     

    SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 13 00:18:05 2025

    Version 19.3.0.0.0

    Copyright (c) 1982, 2019, Oracle.  All rights reserved.

    Last Successful login time: Tue May 13 2025 00:15:56 +05:30

    Connected to:

    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

    Version 19.3.0.0.0

     

    SQL> select * from T1;

     

          STNO STNAME          DOA             FEES

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

             1 MALLIK          12-SEP-25        300

             1 MALLIK          12-SEP-25        300

     

    SQL> describe T1;

     Name                                      Null?    Type

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

     STNO                                               NUMBER(3)

     STNAME                                             VARCHAR2(15)

     DOA                                                DATE

     FEES                                               NUMBER(3)

     

    SQL>

     

    2. Connect to sys user and get the DDL definition of that dummy table T1 belongs to mallik user

     

    [oracle@node1 ~]$ sqlplus / as sysdba

     

    SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 13 00:18:38 2025

    Version 19.3.0.0.0

     

    Copyright (c) 1982, 2019, Oracle.  All rights reserved.

     

    Connected to:

    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

    Version 19.3.0.0.0

     

    SQL> set long 9999999

    SQL> set lines 200 pages 2000

    SQL> col METADATA format a180 word_wrapped

    SQL> select dbms_metadata.get_ddl(upper('&Object_type'),upper('&Object_name'),upper('&Owner')) "METADATA" from dual;

     

    Enter value for object_type: TABLE

    Enter value for object_name: T1

    Enter value for owner: MALLIK

    old   1: select dbms_metadata.get_ddl(upper('&Object_type'),upper('&Object_name'),upper('&Owner')) "METADATA" from dual

    new   1: select dbms_metadata.get_ddl(upper('TABLE'),upper('T1'),upper('MALLIK')) "METADATA" from dual

     

    METADATA

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

    CREATE TABLE "MALLIK"."T1"

    (       "STNO" NUMBER(3,0),

    "STNAME" VARCHAR2(15),

    "DOA" DATE,

    "FEES" NUMBER(3,0)

    ) SEGMENT CREATION IMMEDIATE

    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

    NOCOMPRESS LOGGING

    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

      TABLESPACE "USERS"

     

    SQL> exit

    Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

    Version 19.3.0.0.0

    [oracle@node1 ~]$

    Posted by at No comments:
    Labels:

    Friday, May 9, 2025

    19c autoupgrade prechecks fails with error DISK_SPACE_FOR_RECOVERY_AREA

    19c autoupgrade prechecks fails with error DISK_SPACE_FOR_RECOVERY_AREA


    Issue:

    19c autoupgrade prechecks fails with error DISK_SPACE_FOR_RECOVERY_AREA

    Error Message:

    Check failed for UAT1, manual intervention needed for the below checks
    [DISK_SPACE_FOR_RECOVERY_AREA]

    Cause:

    1. Enough Physical space is not available for FRA location either of at OS file system level or at ASM diskgroup level
    2. Does not have enough FRA free space inside the database 

    Fix:

    1. Make enough physical space is available for FRA location either OS file system or ASM diskgroups
    2. Increase the FRA free space inside the database 

    Error Logs and commands output:

    1. Create config file using auto-upgrade jar file.

    [oracle@oraclelab1 19c-autoupg]$ env |grep ORA
    ORACLE_SID=UAT1
    ORACLE_BASE=/u01/app/oracle
    ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1

    [oracle@oraclelab1 19c-autoupg]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/autoupgrade.jar -create_sample_file config
    Created sample configuration file /u01/patches/19c-autoupg/sample_config.cfg
    [oracle@oraclelab1 19c-autoupg]$ 

    2. Modify the config file according to the target database which are planning to upgrade

    [oracle@oraclelab1 19c-autoupg]$ cp sample_config.cfg UAT_config.cfg
    [oracle@oraclelab1 19c-autoupg]$ vi UAT_config.cfg

    3. Run the auto-upgrade prechks in ANALYZE mode which reported the failure

    [oracle@oraclelab1 19c-autoupg]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/autoupgrade.jar -config UAT_config.cfg -mode ANALYZE
    AutoUpgrade 24.8.241119 launched with default internal options
    Processing config file ...
    +--------------------------------+
    | Starting AutoUpgrade execution |
    +--------------------------------+
    1 Non-CDB(s) will be analyzed
    Type 'help' to list console commands
    upg> upg> upg>
    upg> lsj
    +----+-------+---------+---------+-------+----------+-------+----------------------------+
    |Job#|DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|                     MESSAGE|
    +----+-------+---------+---------+-------+----------+-------+----------------------------+
    | 100|   UAT1|PRECHECKS|EXECUTING|RUNNING|  10:28:06| 2s ago|Loading database information|
    +----+-------+---------+---------+-------+----------+-------+----------------------------+
    Total jobs 1

    upg>
    upg> lsj
    +----+-------+---------+---------+-------+----------+-------+----------------+
    |Job#|DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|         MESSAGE|
    +----+-------+---------+---------+-------+----------+-------+----------------+
    | 100|   UAT1|PRECHECKS|EXECUTING|RUNNING|  10:28:06| 0s ago|Executing Checks|
    +----+-------+---------+---------+-------+----------+-------+----------------+
    Total jobs 1

    upg> Job 100 completed
    ------------------- Final Summary --------------------
    Number of databases            [ 1 ]

    Jobs finished                  [1]
    Jobs failed                    [0]

    Please check the summary report at:
    /u01/patches/19c-autoupg/cfgtoollogs/upgrade/auto/status/status.html
    /u01/patches/19c-autoupg/cfgtoollogs/upgrade/auto/status/status.log
    [oracle@oraclelab1 19c-autoupg]$

    4. Error message reported on the prechecks log

    [oracle@oraclelab1 19c-autoupg]$ more /u01/patches/19c-autoupg/cfgtoollogs/upgrade/auto/status/status.log
    ==========================================
              Autoupgrade Summary Report
    ==========================================
    [Date]           Thu May 08 10:28:43 IST 2025
    [Number of Jobs] 1
    ==========================================
    [Job ID] 100
    ==========================================
    [DB Name]                UAT
    [Version Before Upgrade] 12.2.0.1.0
    [Version After Upgrade]  19.17.0.0.0
    ------------------------------------------
    [Stage Name]    PRECHECKS
    [Status]        FAILURE
    [Start Time]    2025-05-08 10:28:06
    [Duration]      0:00:36
    [Log Directory] /u01/patches/19c-autoupg/UAT/UAT1/100/prechecks
    [Detail]        /u01/patches/19c-autoupg/UAT/UAT1/100/prechecks/uat_preupgrade.log
                    Check failed for UAT1, manual intervention needed for the below checks
                    [DISK_SPACE_FOR_RECOVERY_AREA]
    Cause:The following checks have ERROR severity and no auto fixup is available or
    the fixup failed to resolve the issue. Fix them before continuing:
    UAT1 DISK_SPACE_FOR_RECOVERY_AREA
    Reason:Database Checks has Failed details in /u01/patches/19c-autoupg/UAT/UAT1/100/prechecks
    Action:[MANUAL]
    Info:Return status is ERROR
    ExecutionError:No
    Error Message:The following checks have ERROR severity and no auto fixup is available or
    the fixup failed to resolve the issue. Fix them before continuing:
    UAT1 DISK_SPACE_FOR_RECOVERY_AREA
    ------------------------------------------
    [oracle@oraclelab1 19c-autoupg]$

    5. Increase the physical free space inside the ASM diskgroup since the database is resides inside ASM diskgroup and FRA a location is set to +RECO diskgroup

    [oracle@oraclelab1 dbhome_1]$ . oraenv
    ORACLE_SID = [oracle] ? +ASM1
    The Oracle base has been set to /u01/app/oracle
    [oracle@oraclelab1 dbhome_1]$
    [oracle@oraclelab1 dbhome_1]$ asmcmd -p
    ASMCMD [+] > lsdg
    State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
    MOUNTED  EXTERN  N         512             512   4096  4194304     20476     4888                0            4888              0             N  DATA/
    MOUNTED  EXTERN  N         512             512   4096  4194304      3068     2668                0            2668              0             Y  OCR/
    MOUNTED  EXTERN  N         512             512   4096  4194304     20472     2388                0            2388              0             N  RECO/
    ASMCMD [+] > cd RECO/DEV/ARCHIVELOG/
    ASMCMD [+RECO/DEV/ARCHIVELOG] > rm -rf *
    ASMCMD [+RECO/DEV/ARCHIVELOG] > lsdg
    State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
    MOUNTED  EXTERN  N         512             512   4096  4194304     20476     4888                0            4888              0             N  DATA/
    MOUNTED  EXTERN  N         512             512   4096  4194304      3068     2668                0            2668              0             Y  OCR/
    MOUNTED  EXTERN  N         512             512   4096  4194304     20472     6884                0            6884              0             N  RECO/

    ASMCMD [+RECO/DEV] > cd +RECO/TEST/ARCHIVELOG]
    ASMCMD [+RECO/TEST/ARCHIVELOG] > rm -rf *
    ASMCMD [+RECO/TEST/ARCHIVELOG] > lsdg
    State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
    MOUNTED  EXTERN  N         512             512   4096  4194304     20476     4888                0            4888              0             N  DATA/
    MOUNTED  EXTERN  N         512             512   4096  4194304      3068     2668                0            2668              0             Y  OCR/
    MOUNTED  EXTERN  N         512             512   4096  4194304     20472    11376                0           11376              0             N  RECO/
    ASMCMD [+RECO/TEST/ARCHIVELOG] >
    ASMCMD [+RECO/TEST/ARCHIVELOG] > exit
    [oracle@oraclelab1 dbhome_1]$

    6. Increase the FRA free space inside the database.

    [oracle@oraclelab1 19c-autoupg]$ . oraenv
    ORACLE_SID = [oracle] ? UAT1
    The Oracle base has been set to /u01/app/oracle
    [oracle@oraclelab1 19c-autoupg]

    [oracle@oraclelab1 19c-autoupg]$sqlplus / as sysdba

    SQL*Plus: Release 12.2.0.1.0 Production on Thu May 8 10:31:09 2025

    Copyright (c) 1982, 2016, Oracle.  All rights reserved.

    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

    SQL> show parameter recovery

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_recovery_file_dest                string      +RECO
    db_recovery_file_dest_size           big integer 8016M
    recovery_parallelism                 integer     0
    remote_recovery_file_dest            string

    SQL> alter system set db_recovery_file_dest_size=20G;

    System altered.

    SQL> show parameter recovery

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_recovery_file_dest                string      +RECO
    db_recovery_file_dest_size           big integer 20G
    recovery_parallelism                 integer     0
    remote_recovery_file_dest            string
    SQL> exit
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    [oracle@oraclelab1 19c-autoupg]$

    7. Rerun the auto-upgrade prechks in ANALYZE mode which got completed successfully 

    [oracle@oraclelab1 19c-autoupg]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/autoupgrade.jar -config UAT_config.cfg -mode ANALYZE
    AutoUpgrade 24.8.241119 launched with default internal options
    Processing config file ...
    +--------------------------------+
    | Starting AutoUpgrade execution |
    +--------------------------------+
    1 Non-CDB(s) will be analyzed
    Type 'help' to list console commands
    upg> lsj
    +----+-------+---------+---------+-------+----------+-------+----------------+
    |Job#|DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|         MESSAGE|
    +----+-------+---------+---------+-------+----------+-------+----------------+
    | 102|   UAT1|PRECHECKS|EXECUTING|RUNNING|  10:41:22| 0s ago|Executing Checks|
    +----+-------+---------+---------+-------+----------+-------+----------------+
    Total jobs 1

    upg> Job 102 completed
    ------------------- Final Summary --------------------
    Number of databases            [ 1 ]

    Jobs finished                  [1]
    Jobs failed                    [0]

    Please check the summary report at:
    /u01/patches/19c-autoupg/cfgtoollogs/upgrade/auto/status/status.html
    /u01/patches/19c-autoupg/cfgtoollogs/upgrade/auto/status/status.log
    [oracle@oraclelab1 19c-autoupg]$ more /u01/patches/19c-autoupg/cfgtoollogs/upgrade/auto/status/status.log
    ==========================================
              Autoupgrade Summary Report
    ==========================================
    [Date]           Thu May 08 10:41:42 IST 2025
    [Number of Jobs] 1
    ==========================================
    [Job ID] 102
    ==========================================
    [DB Name]                UAT
    [Version Before Upgrade] 12.2.0.1.0
    [Version After Upgrade]  19.17.0.0.0
    ------------------------------------------
    [Stage Name]    PRECHECKS
    [Status]        SUCCESS
    [Start Time]    2025-05-08 10:41:22
    [Duration]      0:00:20
    [Log Directory] /u01/patches/19c-autoupg/UAT/UAT1/102/prechecks
    [Detail]        /u01/patches/19c-autoupg/UAT/UAT1/102/prechecks/uat_preupgrade.log
                    Check passed and no manual intervention needed
    ------------------------------------------
    [oracle@oraclelab1 19c-autoupg]$
    Posted by at No comments:
    Labels: ,

    Wednesday, May 7, 2025

    ORA-01156: recovery or flashback in progress may need access to files

    ORA-01156: recovery or flashback in progress may need access to files


    Issue:

    Standby redo log file creation on Standby database or DR database is failing 

    Error Message: 

    ORA-01156: recovery or flashback in progress may need access to files

    Cause: 

    MRP process is running on standby database which is preventing to create standby redo logs

    Fix:

    Stop MPR and create standby redo logs and start the MRP process

    Error Logs and commands output:

    1. Standby redo log file creation on Standby database or DR database is failing 

    [oracle@oraclelab3 2025_05_07]$ sqlplus / as sysdba

    SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 7 10:25:58 2025
    Version 19.3.0.0.0

    Copyright (c) 1982, 2019, Oracle.  All rights reserved.

    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.3.0.0.0

    SQL> set pages 1000
    SQL> set lines 1000
    SQL> col DBID for a10
    SQL> select * from v$standby_log;

    no rows selected

    SQL>

    SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo01.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo01_1.log') SIZE 200M;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo01.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo01_1.log') SIZE 200M
    *
    ERROR at line 1:
    ORA-01156: recovery or flashback in progress may need access to files


    SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo02.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo02_2.log') SIZE 200M;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo02.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo02_2.log') SIZE 200M
    *
    ERROR at line 1:
    ORA-01156: recovery or flashback in progress may need access to files

    SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo03.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo03_3.log') SIZE 200M;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo03.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo03_3.log') SIZE 200M
    *
    ERROR at line 1:
    ORA-01156: recovery or flashback in progress may need access to files

    SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo04.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo04_4.log') SIZE 200M;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo04.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo04_4.log') SIZE 200M
    *
    ERROR at line 1:
    ORA-01156: recovery or flashback in progress may need access to files

    SQL>


    2. MRP process was up and running which is preventing us to create a standby redo logs on the standby database. Stop the MPR and create the standby redo logs

    SQL> alter database recover managed standby database cancel;

    Database altered.

    SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo01.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo01_1.log') SIZE 200M;

    Database altered.

    SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo02.log','/u01/app/oracle/fast_re covery_area/DRDB/onlinelog/standby_redo02_2.log') SIZE 200M;

    Database altered.

    SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo03.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo03_3.log') SIZE 200M;

    Database altered.

    SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('/u01/app/oracle/oradata/DRDB/onlinelog/standby_redo04.log','/u01/app/oracle/fast_recover y_area/DRDB/onlinelog/standby_redo04_4.log') SIZE 200M;

    Database altered.

    SQL> set pages 1000
    SQL> set lines 1000
    SQL> col DBID for a10
    SQL> select * from v$standby_log;

        GROUP# DBID          THREAD#  SEQUENCE#      BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAS T_CHANGE# LAST_TIME     CON_ID
    ---------- ---------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ --------- --- --------- --------- ----------
             4 UNASSIGNED          1          0  209715200        512          0 YES UNASSIGNED                                                      0
             5 UNASSIGNED          1          0  209715200        512          0 YES UNASSIGNED                                                      0
             6 UNASSIGNED          1          0  209715200        512          0 YES UNASSIGNED                                                      0
             7 UNASSIGNED          1          0  209715200        512          0 YES UNASSIGNED                                                      0

    SQL>

    3. Start the MPR process once the after the standby redo log files are created successfully

    SQL> alter database recover managed standby database disconnect from session;

    Database altered.

    SQL> exit
    Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.3.0.0.0
    [oracle@oraclelab3 2025_05_07]$

    Posted by at No comments:
    Labels: ,

    ORA-19660: some files in the backup set could not be verified

    ORA-19660: some files in the backup set could not be verified


    Issue:

    Standby database build from the Active database duplicate failed with "ORA-19660: some files in the backup set could not be verified"

    RMAN Error Message: 

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 05/07/2025 09:51:08
    RMAN-05501: aborting duplication of target database
    RMAN-03015: error occurred in stored script Memory Script
    ORA-06510: PL/SQL: unhandled user-defined exception
    ORA-19660: some files in the backup set could not be verified
    ORA-19661: datafile 7 could not be verified

    Cause: 

    Few datafiles on the Primary/PROD database has block corruption 

    Fix:

    Fix this corrupted block PROD database and retry the standby build using active database duplicate  

    Error Logs and commands output:

    1. Standby database build from the Active database duplicate failed with "ORA-19660: some files in the backup set could not be verified"

    [oracle@oraclelab3 dbs]$ sqlplus / as sysdba

    SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 7 09:49:33 2025
    Version 19.3.0.0.0

    Copyright (c) 1982, 2019, Oracle.  All rights reserved.

    Connected to an idle instance.

    SQL> startup nomount pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initDRDB.ora';
    ORACLE instance started.

    Total System Global Area  268434280 bytes
    Fixed Size                  8895336 bytes
    Variable Size             201326592 bytes
    Database Buffers           50331648 bytes
    Redo Buffers                7880704 bytes
    SQL> exit
    Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.3.0.0.0
    [oracle@oraclelab3 dbs]$ rman target sys/Mallik123@DEVDB

    Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 7 09:49:57 2025
    Version 19.3.0.0.0

    Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

    connected to target database: DEVDB (DBID=1101319147)

    RMAN> connect auxiliary sys/Mallik123@DRDB

    connected to auxiliary database: DEVDB (not mounted)

    RMAN> run
    {
    allocate channel p1 type disk;
    allocate channel p2 type disk;
    allocate auxiliary channel s1 type disk;
    allocate auxiliary channel s2 type disk;
    duplicate target database for standby from active database
    2> 3> 4> 5> 6> 7> 8> spfile
    parameter_value_convert 'DEVDB','DRDB'
    set db_name='DEVDB'
    set db_unique_name='DRDB'
    set db_file_name_convert '/u01/app/oracle/oradata/DEVDB/datafile','/u01/app/oracle/oradata/DRDB/datafile'
    set log_file_name_convert='/u01/app/oracle/oradata/DEVDB/onlinelog','/u01/app/oracle/oradata/DRDB/onlinelog','/u01/app/oracle/fast_recovery_ar ea/DEVDB/onlinelog','/u01/app/oracle/fast_recovery_area/DRDB/onlinelog'
    ;
    }9> 10> 11> 12> 13> 14> 15>

    using target database control file instead of recovery catalog
    allocated channel: p1
    channel p1: SID=38 device type=DISK

    allocated channel: p2
    channel p2: SID=18 device type=DISK

    allocated channel: s1
    channel s1: SID=21 device type=DISK

    allocated channel: s2
    channel s2: SID=181 device type=DISK

    Starting Duplicate Db at 07-MAY-25

    contents of Memory Script:
    {
       backup as copy reuse
       passwordfile auxiliary format  '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwDRDB'   ;
       restore clone from service  'DEVDB' spfile to
     '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDRDB.ora';
       sql clone "alter system set spfile= ''/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDRDB.ora''";
    }
    executing Memory Script

    Starting backup at 07-MAY-25
    Finished backup at 07-MAY-25

    Starting restore at 07-MAY-25

    channel s1: starting datafile backup set restore
    channel s1: using network backup set from service DEVDB
    channel s1: restoring SPFILE
    output file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDRDB.ora
    channel s1: restore complete, elapsed time: 00:00:01
    Finished restore at 07-MAY-25

    sql statement: alter system set spfile= ''/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDRDB.ora''

    contents of Memory Script:
    {
       sql clone "alter system set  audit_file_dest =
     ''/u01/app/oracle/admin/DRDB/adump'' comment=
     '''' scope=spfile";
       sql clone "alter system set  control_files =
     ''/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl'', ''/u01/app/oracle/fast_recovery_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl''  comment=
     '''' scope=spfile";
       sql clone "alter system set  dispatchers =
     ''(PROTOCOL=TCP) (SERVICE=DRDBXDB)'' comment=
     '''' scope=spfile";
       sql clone "alter system set  fal_client =
     ''DRDB'' comment=
     '''' scope=spfile";
       sql clone "alter system set  db_name =
     ''DEVDB'' comment=
     '''' scope=spfile";
       sql clone "alter system set  db_unique_name =
     ''DRDB'' comment=
     '''' scope=spfile";
       sql clone "alter system set  db_file_name_convert =
     ''/u01/app/oracle/oradata/DEVDB/datafile'', ''/u01/app/oracle/oradata/DRDB/datafile'' comment=
     '''' scope=spfile";
       sql clone "alter system set  log_file_name_convert =
     ''/u01/app/oracle/oradata/DEVDB/onlinelog'', ''/u01/app/oracle/oradata/DRDB/onlinelog'', ''/u01/app/oracle/fast_recovery_area/DEVDB/onlinelog '', ''/u01/app/oracle/fast_recovery_area/DRDB/onlinelog'' comment=
     '''' scope=spfile";
       shutdown clone immediate;
       startup clone nomount;
    }
    executing Memory Script

    sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/DRDB/adump'' comment= '''' scope=spfile

    sql statement: alter system set  control_files =  ''/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl'', ''/u01/app/oracle/fast_rec overy_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl'' comment= '''' scope=spfile

    sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=DRDBXDB)'' comment= '''' scope=spfile

    sql statement: alter system set  fal_client =  ''DRDB'' comment= '''' scope=spfile

    sql statement: alter system set  db_name =  ''DEVDB'' comment= '''' scope=spfile

    sql statement: alter system set  db_unique_name =  ''DRDB'' comment= '''' scope=spfile

    sql statement: alter system set  db_file_name_convert =  ''/u01/app/oracle/oradata/DEVDB/datafile'', ''/u01/app/oracle/oradata/DRDB/datafile''  comment= '''' scope=spfile

    sql statement: alter system set  log_file_name_convert =  ''/u01/app/oracle/oradata/DEVDB/onlinelog'', ''/u01/app/oracle/oradata/DRDB/onlinelo g'', ''/u01/app/oracle/fast_recovery_area/DEVDB/onlinelog'', ''/u01/app/oracle/fast_recovery_area/DRDB/onlinelog'' comment= '''' scope=spfile

    Oracle instance shut down

    connected to auxiliary database (not started)
    Oracle instance started

    Total System Global Area    3690985856 bytes

    Fixed Size                     8903040 bytes
    Variable Size                721420288 bytes
    Database Buffers            2952790016 bytes
    Redo Buffers                   7872512 bytes
    allocated channel: s1
    channel s1: SID=4 device type=DISK
    allocated channel: s2
    channel s2: SID=20 device type=DISK

    contents of Memory Script:
    {
       sql clone "alter system set  control_files =
      ''/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl'', ''/u01/app/oracle/fast_recovery_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl' ' comment=
     ''Set by RMAN'' scope=spfile";
       restore clone from service  'DEVDB' standby controlfile;
    }
    executing Memory Script

    sql statement: alter system set  control_files =   ''/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl'', ''/u01/app/oracle/fast_re covery_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl'' comment= ''Set by RMAN'' scope=spfile

    Starting restore at 07-MAY-25

    channel s1: starting datafile backup set restore
    channel s1: using network backup set from service DEVDB
    channel s1: restoring control file
    channel s1: restore complete, elapsed time: 00:00:01
    output file name=/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl
    output file name=/u01/app/oracle/fast_recovery_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl
    Finished restore at 07-MAY-25

    contents of Memory Script:
    {
       sql clone 'alter database mount standby database';
    }
    executing Memory Script

    sql statement: alter database mount standby database

    contents of Memory Script:
    {
       set newname for tempfile  1 to
     "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_temp_my1qjssl_.tmp";
       switch clone tempfile all;
       set newname for datafile  1 to
     "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_system_my1qfcqq_.dbf";
       set newname for datafile  3 to
     "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_sysaux_my1qggv3_.dbf";
       set newname for datafile  4 to
     "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_undotbs1_my1qh7xp_.dbf";
       set newname for datafile  7 to
     "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_users_my1qh90h_.dbf";
       restore
       from  nonsparse   from service
     'DEVDB'   clone database
       ;
       sql 'alter system archive log current';
    }
    executing Memory Script

    executing command: SET NEWNAME

    renamed tempfile 1 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_temp_my1qjssl_.tmp in control file

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    Starting restore at 07-MAY-25

    channel s1: starting datafile backup set restore
    channel s1: using network backup set from service DEVDB
    channel s1: specifying datafile(s) to restore from backup set
    channel s1: restoring datafile 00001 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_system_my1qfcqq_.dbf
    channel s2: starting datafile backup set restore
    channel s2: using network backup set from service DEVDB
    channel s2: specifying datafile(s) to restore from backup set
    channel s2: restoring datafile 00003 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_sysaux_my1qggv3_.dbf
    channel s1: restore complete, elapsed time: 00:00:04
    channel s1: starting datafile backup set restore
    channel s1: using network backup set from service DEVDB
    channel s1: specifying datafile(s) to restore from backup set
    channel s1: restoring datafile 00004 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_undotbs1_my1qh7xp_.dbf
    channel s1: restore complete, elapsed time: 00:00:01
    channel s1: starting datafile backup set restore
    channel s1: using network backup set from service DEVDB
    channel s1: specifying datafile(s) to restore from backup set
    channel s1: restoring datafile 00007 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_users_my1qh90h_.dbf
    channel s2: restore complete, elapsed time: 00:00:04
    restore not complete
    released channel: p1
    released channel: p2
    released channel: s1
    released channel: s2
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 05/07/2025 09:51:08
    RMAN-05501: aborting duplication of target database
    RMAN-03015: error occurred in stored script Memory Script
    ORA-06510: PL/SQL: unhandled user-defined exception
    ORA-19660: some files in the backup set could not be verified
    ORA-19661: datafile 7 could not be verified

    RMAN> exit

    Recovery Manager complete.
    [oracle@oraclelab3 dbs]$

    2. Here on the standby server side datafile number 7 which o1_mf_users_my1qh90h_.dbf not getting restored 

    [oracle@oraclelab3 ~]$ ls -l /u01/app/oracle/oradata/DRDB/datafile/o1_mf_users_my1qh90h_.dbf
    ls: cannot access /u01/app/oracle/oradata/DRDB/datafile/o1_mf_users_my1qh90h_.dbf: No such file or directory
    [oracle@oraclelab3 ~]$ cd /u01/app/oracle/oradata/DRDB/datafile/
    [oracle@oraclelab3 datafile]$ ls -l
    total 2913304
    -rw-r-----. 1 oracle oinstall 1436557312 May  7 09:40 o1_mf_sysaux_n1oqb7bg_.dbf
    -rw-r-----. 1 oracle oinstall 1142956032 May  7 09:40 o1_mf_system_n1oqb7b5_.dbf
    -rw-r-----. 1 oracle oinstall  403709952 May  7 09:40 o1_mf_undotbs1_n1oqbgg5_.dbf
    [oracle@oraclelab3 datafile]$


    3. On the production database there is a block corruption on this o1_mf_users_my1qh90h_.dbf

    [oracle@oraclelab1 DEVDB]$ cd /u01/app/oracle/oradata/DEVDB/datafile/
    [oracle@oraclelab1 datafile]$ ll
    total 3042000
    -rw-r-----. 1 oracle oinstall 1436557312 May  7 09:50 o1_mf_sysaux_my1qggv3_.dbf
    -rw-r-----. 1 oracle oinstall 1142956032 May  7 09:50 o1_mf_system_my1qfcqq_.dbf
    -rw-r-----. 1 oracle oinstall  136323072 May  5 22:01 o1_mf_temp_my1qjssl_.tmp
    -rw-r-----. 1 oracle oinstall  403709952 May  7 09:50 o1_mf_undotbs1_my1qh7xp_.dbf
    -rw-r-----. 1 oracle oinstall    5251072 May  7 09:50 o1_mf_users_my1qh90h_.dbf

    4. Verify the block corruption using dbv tool 

    [oracle@oraclelab1 datafile]$ dbv FILE=o1_mf_users_my1qh90h_.dbf

    DBVERIFY: Release 19.0.0.0.0 - Production on Wed May 7 09:53:06 2025

    Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

    DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_my1qh90h_.dbf
    Page 354 is marked corrupt
    Invalid temporary block relative dba: 0x01c00162 (file 7, block 354)
    Bad header found during dbv:
    Data in bad block:
     type: 99 format: 7 rdba: 0x69747075
     last change scn: 0x7272.7365.74206e6f seq: 0x74 flg: 0x0a
     spare3: 0x0
     consistency value in tail: 0x86df2303
     check value in block header: 0x8240
     block checksum disabled

    DBVERIFY - Verification complete

    Total Pages Examined         : 640
    Total Pages Processed (Data) : 95
    Total Pages Failing   (Data) : 0
    Total Pages Processed (Index): 34
    Total Pages Failing   (Index): 0
    Total Pages Processed (Other): 441
    Total Pages Processed (Seg)  : 0
    Total Pages Failing   (Seg)  : 0
    Total Pages Empty            : 69
    Total Pages Marked Corrupt   : 1 >>>>>>>>>>>>> Here is the corruption
    Total Pages Influx           : 0
    Total Pages Encrypted        : 0
    Highest block SCN            : 4294661 (0.4294661)
    [oracle@oraclelab1 datafile]$

    5. Verify the block corruption using v$database_block_corruption command inside PROD database

    [oracle@oraclelab1 datafile]$ sqlplus / as sysdba

    SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 7 09:53:54 2025
    Version 19.17.0.0.0

    Copyright (c) 1982, 2022, Oracle.  All rights reserved.


    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.17.0.0.0

    SQL> select * from v$database_block_corruption;

         FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID
    ---------- ---------- ---------- ------------------ --------- ----------
             7        354          1                  0 CORRUPT            0

    SQL> exit
    Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.17.0.0.0
    [oracle@oraclelab1 datafile]$

    6. We have to fix this corrupted block on the datafile number 7 or users datafile using valid RMAN backups. 
    Once this corruption is fixed then the RMAN active database duplicate for standby proceeded further. 

    In this case here we have dropped that USERS tablespace including the datafile and created new USERS1 tablespace since we don't had any valid backup to recover this corrupted block. 


    SQL> drop tablespace users including contents and datafiles;
    drop tablespace users including contents and datafiles
    *
    ERROR at line 1:
    ORA-12919: Can not drop the default permanent tablespace

    SQL> create tablespace USERS1;

    Tablespace created.

    SQL> alter database default tablespace USERS1;

    Database altered.

    SQL> drop tablespace users including contents and datafiles;

    Tablespace dropped.

    SQL>
    SQL> select NAME from v$tablespace;

    NAME
    ------------------------------
    SYSAUX
    SYSTEM
    UNDOTBS1
    TEMP
    USERS1

    SQL>

    7. Reran the Standby database build from the Active database duplicate which completed successfully 

    [oracle@oraclelab3 DRDB]$ sqlplus / as sysdba

    SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 7 10:00:41 2025
    Version 19.3.0.0.0

    Copyright (c) 1982, 2019, Oracle.  All rights reserved.

    Connected to an idle instance.

    SQL> startup nomount pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initDRDB.ora';
    ORACLE instance started.

    Total System Global Area  268434280 bytes
    Fixed Size                  8895336 bytes
    Variable Size             201326592 bytes
    Database Buffers           50331648 bytes
    Redo Buffers                7880704 bytes
    SQL> exit
    Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.3.0.0.0
    [oracle@oraclelab3 DRDB]$ cd /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/
    [oracle@oraclelab3 dbs]$ ll *DRDB*
    -rw-rw----. 1 oracle oinstall 1544 May  7 10:00 hc_DRDB.dat
    -rw-r--r--. 1 oracle oinstall   14 May  7 09:24 initDRDB.ora
    -rw-r-----. 1 oracle oinstall   24 May  7 09:50 lkDRDB
    -rw-r-----. 1 oracle oinstall 2048 May  7 09:50 orapwDRDB
    -rw-r--r--. 1 oracle oinstall 1632 May  7 09:51 _rm_dup_DRDB_DRDB.dat
    -rw-r-----. 1 oracle oinstall 4608 May  7 09:51 spfileDRDB.ora
    [oracle@oraclelab3 dbs]$ rm spfileDRDB.ora _rm_dup_DRDB_DRDB.dat
    [oracle@oraclelab3 dbs]$ ll *DRDB*
    -rw-rw----. 1 oracle oinstall 1544 May  7 10:00 hc_DRDB.dat
    -rw-r--r--. 1 oracle oinstall   14 May  7 09:24 initDRDB.ora
    -rw-r-----. 1 oracle oinstall   24 May  7 09:50 lkDRDB
    -rw-r-----. 1 oracle oinstall 2048 May  7 09:50 orapwDRDB
    [oracle@oraclelab3 dbs]$
    [oracle@oraclelab3 dbs]$ rman target sys/Mallik123@DEVDB

    Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 7 10:01:26 2025
    Version 19.3.0.0.0

    Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

    connected to target database: DEVDB (DBID=1101319147)

    RMAN> connect auxiliary sys/Mallik123@DRDB

    connected to auxiliary database: DEVDB (not mounted)

    RMAN> run
    2> {
    3> allocate channel p1 type disk;
    4> allocate channel p2 type disk;
    allocate auxiliary channel s1 type disk;
    5> 6> allocate auxiliary channel s2 type disk;
    7> duplicate target database for standby from active database
    spfile
    parameter_value_convert 'DEVDB','DRDB'
    8> 9> 10> set db_name='DEVDB'
    11> set db_unique_name='DRDB'
    12> set db_file_name_convert '/u01/app/oracle/oradata/DEVDB/datafile','/u01/app/oracle/oradata/DRDB/datafile'
    13> set log_file_name_convert='/u01/app/oracle/oradata/DEVDB/onlinelog','/u01/app/oracle/oradata/DRDB/onlinelog','/u01/app/oracle/fast_recover y_area/DEVDB/onlinelog','/u01/app/oracle/fast_recovery_area/DRDB/onlinelog'
    ;
    }14> 15>

    using target database control file instead of recovery catalog
    allocated channel: p1
    channel p1: SID=35 device type=DISK

    allocated channel: p2
    channel p2: SID=4 device type=DISK

    allocated channel: s1
    channel s1: SID=21 device type=DISK

    allocated channel: s2
    channel s2: SID=181 device type=DISK

    Starting Duplicate Db at 07-MAY-25

    contents of Memory Script:
    {
       backup as copy reuse
       passwordfile auxiliary format  '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwDRDB'   ;
       restore clone from service  'DEVDB' spfile to
     '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDRDB.ora';
       sql clone "alter system set spfile= ''/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDRDB.ora''";
    }
    executing Memory Script

    Starting backup at 07-MAY-25
    Finished backup at 07-MAY-25

    Starting restore at 07-MAY-25

    channel s1: starting datafile backup set restore
    channel s1: using network backup set from service DEVDB
    channel s1: restoring SPFILE
    output file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDRDB.ora
    channel s1: restore complete, elapsed time: 00:00:01
    Finished restore at 07-MAY-25

    sql statement: alter system set spfile= ''/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDRDB.ora''

    contents of Memory Script:
    {
       sql clone "alter system set  audit_file_dest =
     ''/u01/app/oracle/admin/DRDB/adump'' comment=
     '''' scope=spfile";
       sql clone "alter system set  control_files =
     ''/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl'', ''/u01/app/oracle/fast_recovery_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl''  comment=
     '''' scope=spfile";
       sql clone "alter system set  dispatchers =
     ''(PROTOCOL=TCP) (SERVICE=DRDBXDB)'' comment=
     '''' scope=spfile";
       sql clone "alter system set  fal_client =
     ''DRDB'' comment=
     '''' scope=spfile";
       sql clone "alter system set  db_name =
     ''DEVDB'' comment=
     '''' scope=spfile";
       sql clone "alter system set  db_unique_name =
     ''DRDB'' comment=
     '''' scope=spfile";
       sql clone "alter system set  db_file_name_convert =
     ''/u01/app/oracle/oradata/DEVDB/datafile'', ''/u01/app/oracle/oradata/DRDB/datafile'' comment=
     '''' scope=spfile";
       sql clone "alter system set  log_file_name_convert =
     ''/u01/app/oracle/oradata/DEVDB/onlinelog'', ''/u01/app/oracle/oradata/DRDB/onlinelog'', ''/u01/app/oracle/fast_recovery_area/DEVDB/onlinelog '', ''/u01/app/oracle/fast_recovery_area/DRDB/onlinelog'' comment=
     '''' scope=spfile";
       shutdown clone immediate;
       startup clone nomount;
    }
    executing Memory Script

    sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/DRDB/adump'' comment= '''' scope=spfile

    sql statement: alter system set  control_files =  ''/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl'', ''/u01/app/oracle/fast_rec overy_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl'' comment= '''' scope=spfile

    sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=DRDBXDB)'' comment= '''' scope=spfile

    sql statement: alter system set  fal_client =  ''DRDB'' comment= '''' scope=spfile

    sql statement: alter system set  db_name =  ''DEVDB'' comment= '''' scope=spfile

    sql statement: alter system set  db_unique_name =  ''DRDB'' comment= '''' scope=spfile

    sql statement: alter system set  db_file_name_convert =  ''/u01/app/oracle/oradata/DEVDB/datafile'', ''/u01/app/oracle/oradata/DRDB/datafile''  comment= '''' scope=spfile

    sql statement: alter system set  log_file_name_convert =  ''/u01/app/oracle/oradata/DEVDB/onlinelog'', ''/u01/app/oracle/oradata/DRDB/onlinelo g'', ''/u01/app/oracle/fast_recovery_area/DEVDB/onlinelog'', ''/u01/app/oracle/fast_recovery_area/DRDB/onlinelog'' comment= '''' scope=spfile

    Oracle instance shut down

    connected to auxiliary database (not started)
    Oracle instance started

    Total System Global Area    3690985856 bytes

    Fixed Size                     8903040 bytes
    Variable Size                721420288 bytes
    Database Buffers            2952790016 bytes
    Redo Buffers                   7872512 bytes
    allocated channel: s1
    channel s1: SID=4 device type=DISK
    allocated channel: s2
    channel s2: SID=19 device type=DISK

    contents of Memory Script:
    {
       sql clone "alter system set  control_files =
      ''/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl'', ''/u01/app/oracle/fast_recovery_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl' ' comment=
     ''Set by RMAN'' scope=spfile";
       restore clone from service  'DEVDB' standby controlfile;
    }
    executing Memory Script

    sql statement: alter system set  control_files =   ''/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl'', ''/u01/app/oracle/fast_re covery_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl'' comment= ''Set by RMAN'' scope=spfile

    Starting restore at 07-MAY-25

    channel s1: starting datafile backup set restore
    channel s1: using network backup set from service DEVDB
    channel s1: restoring control file
    channel s1: restore complete, elapsed time: 00:00:01
    output file name=/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl
    output file name=/u01/app/oracle/fast_recovery_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl
    Finished restore at 07-MAY-25

    contents of Memory Script:
    {
       sql clone 'alter database mount standby database';
    }
    executing Memory Script

    sql statement: alter database mount standby database

    contents of Memory Script:
    {
       set newname for tempfile  1 to
     "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_temp_my1qjssl_.tmp";
       switch clone tempfile all;
       set newname for datafile  1 to
     "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_system_my1qfcqq_.dbf";
       set newname for datafile  3 to
     "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_sysaux_my1qggv3_.dbf";
       set newname for datafile  4 to
     "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_undotbs1_my1qh7xp_.dbf";
       set newname for datafile  5 to
     "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_users1_n1orby75_.dbf";
       restore
       from  nonsparse   from service
     'DEVDB'   clone database
       ;
       sql 'alter system archive log current';
    }
    executing Memory Script

    executing command: SET NEWNAME

    renamed tempfile 1 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_temp_my1qjssl_.tmp in control file

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    Starting restore at 07-MAY-25

    channel s1: starting datafile backup set restore
    channel s1: using network backup set from service DEVDB
    channel s1: specifying datafile(s) to restore from backup set
    channel s1: restoring datafile 00001 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_system_my1qfcqq_.dbf
    channel s2: starting datafile backup set restore
    channel s2: using network backup set from service DEVDB
    channel s2: specifying datafile(s) to restore from backup set
    channel s2: restoring datafile 00003 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_sysaux_my1qggv3_.dbf
    channel s1: restore complete, elapsed time: 00:00:03
    channel s1: starting datafile backup set restore
    channel s1: using network backup set from service DEVDB
    channel s1: specifying datafile(s) to restore from backup set
    channel s1: restoring datafile 00004 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_undotbs1_my1qh7xp_.dbf
    channel s1: restore complete, elapsed time: 00:00:01
    channel s1: starting datafile backup set restore
    channel s1: using network backup set from service DEVDB
    channel s1: specifying datafile(s) to restore from backup set
    channel s1: restoring datafile 00005 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_users1_n1orby75_.dbf
    channel s2: restore complete, elapsed time: 00:00:04
    channel s1: restore complete, elapsed time: 00:00:01
    Finished restore at 07-MAY-25

    sql statement: alter system archive log current

    contents of Memory Script:
    {
       switch clone datafile all;
    }
    executing Memory Script

    datafile 1 switched to datafile copy
    input datafile copy RECID=1 STAMP=1200477745 file name=/u01/app/oracle/oradata/DRDB/datafile/o1_mf_system_n1orlngp_.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=2 STAMP=1200477745 file name=/u01/app/oracle/oradata/DRDB/datafile/o1_mf_sysaux_n1orlngr_.dbf
    datafile 4 switched to datafile copy
    input datafile copy RECID=3 STAMP=1200477745 file name=/u01/app/oracle/oradata/DRDB/datafile/o1_mf_undotbs1_n1orlqjl_.dbf
    datafile 5 switched to datafile copy
    input datafile copy RECID=4 STAMP=1200477745 file name=/u01/app/oracle/oradata/DRDB/datafile/o1_mf_users1_n1orlrkw_.dbf
    Finished Duplicate Db at 07-MAY-25
    released channel: p1
    released channel: p2
    released channel: s1
    released channel: s2

    RMAN> exit

    Recovery Manager complete.
    [oracle@oraclelab3 dbs]$

    8. Further proceed with the post validation and post standby configuration steps. 
    Posted by at No comments:
    Labels:

    ORA-12919: Can not drop the default permanent tablespace

    ORA-12919: Can not drop the default permanent tablespace


    Issue:

    Not able to drop the USERS tablespace

    Error Message: 

    ORA-12919: Can not drop the default permanent tablespace

    Cause: 

    USERS tablespace is default permanent tablespace which can not be dropped without assign this default permanent tablespace to another tablespace

    Fix:

    Change the default permanent tablespace to another tablespace and drop this USERS tablespace

    Error Logs and commands output:

    1. trying to drop the default permanent tablespace USERS which is erroring out 

    [oracle@oraclelab1 datafile]$ sqlplus / as sysdba

    SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 7 09:56:31 2025
    Version 19.17.0.0.0

    Copyright (c) 1982, 2022, Oracle.  All rights reserved.

    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.17.0.0.0

    SQL> select NAME from v$tablespace;

    NAME
    ------------------------------
    SYSAUX
    SYSTEM
    UNDOTBS1
    USERS
    TEMP

    SQL> drop tablespace users including contents and datafiles;
    drop tablespace users including contents and datafiles
    *
    ERROR at line 1:
    ORA-12919: Can not drop the default permanent tablespace

    2. Find or How to Find Default Permanent Tablespace? 

    SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
    WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

    PROPERTY_VALUE
    --------------------
    USERS

    3. Create new tablespace and make it as Default Permanent Tablespace

    SQL> create tablespace USERS1;

    Tablespace created.

    SQL> alter database default tablespace USERS1;

    Database altered.

    4. Drop USERS tablespace 

    SQL> drop tablespace users including contents and datafiles;

    Tablespace dropped.

    SQL>
    SQL> select NAME from v$tablespace;

    NAME
    ------------------------------
    SYSAUX
    SYSTEM
    UNDOTBS1
    TEMP
    USERS1

    SQL>
    Posted by at No comments:
    Labels:

    ORA-02449: unique/primary keys in table referenced by foreign keys

    ORA-02449: unique/primary keys in table referenced by foreign keys


    Issue:

    Not able to drop the USERS tablespace

    Error Message: 

    ORA-02449: unique/primary keys in table referenced by foreign keys

    Cause: 

    USERS tablespace has some schemas and objects inside the schemas which has unique and primary keys in table referenced by foreign keys

    Fix:

    Drop those users or objects before dropping the tablespace

    Error Logs and commands output:

    1. Verify the tablespace and while trying to drop USERS tablespace which is not allowing us due to "ORA-02449: unique/primary keys in table referenced by foreign keys"

    SQL> select NAME from v$tablespace;

    NAME
    ------------------------------
    SYSAUX
    SYSTEM
    UNDOTBS1
    USERS
    USERS1
    TEMP

    SQL> drop tablespace users including contents and datafiles;
    drop tablespace users including contents and datafiles
    *
    ERROR at line 1:
    ORA-02449: unique/primary keys in table referenced by foreign keys

    2. Verify the users and default tablespaces 

    SQL> set pages 1000 lines 1000
    SQL> col USERNAME for a20
    SQL> select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE from dba_users where DEFAULT_TABLESPACE='USERS';

    USERNAME             ACCOUNT_STATUS                   DEFAULT_TABLESPACE
    -------------------- -------------------------------- ------------------------------
    HR                   OPEN                             USERS

    1 rows selected.
    SQL>

    3. Drop the HR user including the cascade constraints, after dropping the user including cascade constraints all the unique and primary keys in table referenced by foreign keys will be removed 

    SQL> drop user hr cascade;

    User dropped.

    4. Drop the USERS tablespaces

    SQL> drop tablespace users including contents and datafiles;

    Tablespace dropped.

    SQL>
    SQL> select NAME from v$tablespace;

    NAME
    ------------------------------
    SYSAUX
    SYSTEM
    UNDOTBS1
    TEMP
    USERS1

    SQL>
    Posted by at No comments:
    Labels:

    Monday, May 5, 2025

    [DBT-10328] Specified GDB Name may have a potential conflict with an already existing database

    [DBT-10328] Specified GDB Name may have a potential conflict with an already existing database

     

    Issue:

    dbca is not allowing to create DEV database throwing error message “DBT-10328 the specified GDB name may have potential conflicts”

    Cause:

    We have the DEV database entries on the /etc/oratab which is causing this issue

     

    Fix/Solution:

    Edit the /etc/oratab file and remove the DEV entry from /etc/oratab file

     

    1. No database or database instance is running with name DEV

    [root@oraclelab1 ~]# ps -ef|grep smon

    oracle   13942     1  0 Apr30 ?        00:00:05 ora_smon_DEVDB

    root     27910 27801  0 13:59 pts/3    00:00:00 grep --color=auto smon

    [root@oraclelab1 ~]#

     

    2. Old DEV entry exist on /etc/oratab file

     

    [root@oraclelab1 ~]# cat /etc/oratab

    #

    # This file is used by ORACLE utilities.  It is created by root.sh

    # and updated by either Database Configuration Assistant while creating

    # a database or ASM Configuration Assistant while creating ASM instance.

     

    # A colon, ':', is used as the field terminator.  A new line terminates

    # the entry.  Lines beginning with a pound sign, '#', are comments.

    #

    # Entries are of the form:

    #   $ORACLE_SID:$ORACLE_HOME:<N|Y>:

    #

    # The first and second fields are the system identifier and home

    # directory of the database respectively.  The third field indicates

    # to the dbstart utility that the database should , "Y", or should not,

    # "N", be brought up at system boot time.

    #

    # Multiple entries with the same $ORACLE_SID are not allowed.

    #

    #

    DEVDB:/u01/app/oracle/product/19.0.0.0/dbhome_1:N

    DEV:/u01/app/oracle/product/19.0.0.0/dbhome_1:N

    [root@oraclelab1 ~]#

     

    After we removed the old DEV entry from /etc/oratafile file

    [root@oraclelab1 ~]# vi /etc/oratab

    [root@oraclelab1 ~]# cat /etc/oratab

    #

    # This file is used by ORACLE utilities.  It is created by root.sh

    # and updated by either Database Configuration Assistant while creating

    # a database or ASM Configuration Assistant while creating ASM instance.

     

    # A colon, ':', is used as the field terminator.  A new line terminates

    # the entry.  Lines beginning with a pound sign, '#', are comments.

    #

    # Entries are of the form:

    #   $ORACLE_SID:$ORACLE_HOME:<N|Y>:

    #

    # The first and second fields are the system identifier and home

    # directory of the database respectively.  The third field indicates

    # to the dbstart utility that the database should , "Y", or should not,

    # "N", be brought up at system boot time.

    #

    # Multiple entries with the same $ORACLE_SID are not allowed.

    #

    #

    DEVDB:/u01/app/oracle/product/19.0.0.0/dbhome_1:N

    [root@oraclelab1 ~]

        

     

     

     

    Posted by at No comments:
    Labels:
    Subscribe to: Posts (Atom)

    🌟 Proud to be an Oracle ACE Pro! 🚀

    🌟 Proud to be an Oracle ACE Pro! 🚀 I’m excited to share my official Oracle ACE Profile with the Oracle community. 👨‍💻 Mallikarjun ...