Effect of adding datafiles on primary when you don't have sufficient space left on standby database

Effect of adding datafiles on primary when you don't have sufficient space left on standby database

In this demonstration, I will show you how standby behaves when you add a datafile on primary database where standby don't have enough space to replicate that change.

Here are my configuration details.

Primary Server: orcldbvm01 IP :192.168.0.120 Primary CDB :SRCDB

Standby Server: orcldbvm02 IP :192.168.0.121 Standby CDB :TRCDB

Primary database details-

[oracle@orcldbvm01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 1 04:09:41 2021
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> select name,db_unique_name,open_mode,database_role,protection_mode from v$database;
NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
--------- ------------------------------ -------------------- ---------------
SRCDB     SRCDB                          READ WRITE           PRIMARY          MAXIMUM AVAILABILITY
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APP1_PRD_PDB                   READ WRITE NO
SQL>

Standby database details-

[oracle@orcldbvm02 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 1 04:10:25 2021

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> select name,db_unique_name,open_mode,database_role,protection_mode from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE

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

SRCDB     TRCDB                          READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM AVAILABILITY

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 APP1_PRD_PDB                   READ ONLY  NO

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE

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

standby_file_management              string      AUTO

SQL> select distinct substr ( name , 1,23) "datafile_location"  from v$datafile;

datafile_location

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

/u01/app/oracle/oradata

SQL> show parameter db_Create_file_dest

NAME                                 TYPE        VALUE

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

db_create_file_dest                  string      /u01/app/oracle/oradata

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /u02/app/oracle/orafra/fast_recovery_area/

db_recovery_file_dest_size           big integer 12918M

SQL> select SOURCE_DB_UNIQUE_NAME,NAME,VALUE,TIME_COMPUTED from v$dataguard_stats;

SOURCE_DB_UNI NAME                             VALUE            TIME_COMPUTED

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

SRCDB         transport lag                    +00 00:00:00     05/01/2021 04:13:36

SRCDB         apply lag                        +00 00:00:00     05/01/2021 04:13:36

SRCDB         apply finish time                +00 00:00:00.000 05/01/2021 04:13:36

              estimated startup time           186              05/01/2021 04:13:36

SQL>

As you can see, my datafiles have been configured under /u01, let's check the free space available.

[oracle@orcldbvm02 ~]$ df -h

Filesystem      Size  Used Avail Use% Mounted on

devtmpfs        967M     0  967M   0% /dev

tmpfs           982M  9.5M  973M   1% /run

tmpfs           982M     0  982M   0% /sys/fs/cgroup

/dev/sda3        25G  7.6G   16G  33% /

/dev/sdb1        30G   16G   14G  55% /u01

tmpfs           197M   28K  197M   1% /run/user/1000

tmpfs           197M  4.0K  197M   1% /run/user/42

/dev/sda1       499M  168M  332M  34% /boot

/dev/sdc1        15G   37M   15G   1% /u02

tmpfs           197M     0  197M   0% /run/user/0

[oracle@orcldbvm02 ~]$

Now using fallocate command, create a large dummy file to fill up the /u01 filesystem.

[oracle@orcldbvm02 ~]$ cd /u01

[oracle@orcldbvm02 u01]$ fallocate -l 13G test.img

[oracle@orcldbvm02 u01]$  df -h

Filesystem      Size  Used Avail Use% Mounted on

devtmpfs        967M     0  967M   0% /dev

tmpfs           982M  9.5M  973M   1% /run

tmpfs           982M     0  982M   0% /sys/fs/cgroup

/dev/sda3        25G  7.6G   16G  33% /

/dev/sdb1        30G   29G  457M  99% /u01

tmpfs           197M   28K  197M   1% /run/user/1000

tmpfs           197M  4.0K  197M   1% /run/user/42

/dev/sda1       499M  168M  332M  34% /boot

/dev/sdc1        15G   37M   15G   1% /u02

tmpfs           197M     0  197M   0% /run/user/0

[oracle@orcldbvm02 u01]$

Now we've only 457 MB left in /u01, lets try to add one datafile with size > 457 MB.

At Primary,

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='SYSAUX';

TABLESPACE_NAME                FILE_NAME

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

SYSAUX                         /u01/app/oracle/oradata/SRCDB/datafile/o1_mf_sysaux_j1tfb52y_.dbf

SQL>

At standby,

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='SYSAUX';

TABLESPACE_NAME                FILE_NAME

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

SYSAUX                         /u01/app/oracle/oradata/TRCDB/datafile/o1_mf_sysaux_j1ypkmx8_.dbf

SQL>

Add one datafile to SYSAUX tablespace.

SQL> alter tablespace SYSAUX add datafile size 600M autoextend on ;

Tablespace altered.

SQL>  select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='SYSAUX';

TABLESPACE_NAME                FILE_NAME

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

SYSAUX                         /u01/app/oracle/oradata/SRCDB/datafile/o1_mf_sysaux_j1tfb52y_.dbf

SYSAUX                         /u01/app/oracle/oradata/SRCDB/datafile/o1_mf_sysaux_j8wxgdyp_.dbf

SQL>

Standby alertlog file reported following errors.

File #33 added to control file as 'UNNAMED00033'.

Originally created as:

'/u01/app/oracle/oradata/SRCDB/datafile/o1_mf_sysaux_j8wxgdyp_.dbf'

Recovery was unable to create the file as a new OMF file.

PR00 (PID:18366): MRP0: Background Media Recovery terminated with error 1274

Non critical error ORA-48113 caught while writing to trace file "/u01/app/oracle/diag/rdbms/trcdb/TRCDB/trace/TRCDB_pr00_18366.trc"

Error message:

Writing to the above trace file is disabled for now...

2021-05-02T05:51:22.622641-04:00

Errors in file /u01/app/oracle/diag/rdbms/trcdb/TRCDB/trace/TRCDB_pr00_18366.trc:

ORA-01274: cannot add data file that was originally created as '/u01/app/oracle/oradata/SRCDB/datafile/o1_mf_sysaux_j8wxgdyp_.dbf'

PR00 (PID:18366): Managed Standby Recovery not using Real Time Apply

2021-05-02T05:51:35.464556-04:00

Recovery interrupted!

2021-05-02T05:51:37.129072-04:00

Non critical error ORA-48180 caught while writing to trace file "/u01/app/oracle/diag/rdbms/trcdb/TRCDB/trace/TRCDB_mz00_18939.trc"

Error message: Linux-x86_64 Error: 28: No space left on device

Additional information: 1

Writing to the above trace file is disabled for now...

2021-05-02T05:51:42.679478-04:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal

Recovered data files to a consistent state at change 4792358

stopping change tracking

2021-05-02T05:51:42.682605-04:00

Errors in file /u01/app/oracle/diag/rdbms/trcdb/TRCDB/trace/TRCDB_pr00_18366.trc:

ORA-01274: cannot add data file that was originally created as '/u01/app/oracle/oradata/SRCDB/datafile/o1_mf_sysaux_j8wxgdyp_.dbf'

2021-05-02T05:51:42.728424-04:00

Background Media Recovery process shutdown (TRCDB)

As you could see MRP has been crashed.

SQL> select name,db_unique_name,open_mode,database_role,protection_mode from v$database; NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE ---------- ---------------- -------------------- ---------------- ----------- SRCDB TRCDB READ ONLY PHYSICAL STANDBY MAXIMUM AVAILABILITY SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- DGRD ALLOCATED 0 0 0 0 ARCH CLOSING 1 60 28672 366 DGRD ALLOCATED 0 0 0 0 ARCH CLOSING 1 62 6144 395 ARCH CLOSING 1 59 1 32 ARCH CLOSING 1 63 8192 959 RFS IDLE 1 0 0 0 RFS IDLE 1 64 5682 1 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 10 rows selected. SQL> select file#,name from v$datafile where name like '%UNNAMED%'; FILE# NAME ---------- ------------------------------------------------------------------ 33 /u01/app/oracle/product/version/db_1/dbs/UNNAMED00033 SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='SYSAUX'; TABLESPACE_NAME FILE_NAME ------------------------------ ---------------------------------------------- SYSAUX /u01/app/oracle/oradata/TRCDB/datafile/o1_mf_sysaux_j1ypkmx8_.dbf SQL>

Let's recover the datafile which is created with name '%UNNAMED%'. For that remove the dummy file created.

[oracle@orcldbvm02 u01]$ rm -rf test.img

[oracle@orcldbvm02 u01]$ df -h

Filesystem      Size  Used Avail Use% Mounted on

devtmpfs        967M     0  967M   0% /dev

tmpfs           982M  9.6M  973M   1% /run

tmpfs           982M     0  982M   0% /sys/fs/cgroup

/dev/sda3        25G  7.6G   16G  33% /

/dev/sdb1        30G   17G   14G  56% /u01

tmpfs           197M   28K  197M   1% /run/user/1000

tmpfs           197M  4.0K  197M   1% /run/user/42

/dev/sda1       499M  168M  332M  34% /boot

/dev/sdc1        15G   37M   15G   1% /u02

tmpfs           197M     0  197M   0% /run/user/0

[oracle@orcldbvm02 u01]$

Now we've enough space in /u01, let's create the datafile.

[oracle@orcldbvm02 u01]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 2 06:02:25 2021

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> show parameter standby_file_management

NAME                                 TYPE        VALUE

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

standby_file_management              string      AUTO

SQL> alter system set standby_file_management='MANUAL';

System altered.

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE

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

standby_file_management              string      MANUAL

SQL> alter database create datafile '/u01/app/oracle/product/version/db_1/dbs/UNNAMED00033' as new;

Database altered.

SQL>  select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='SYSAUX';

TABLESPACE_NAME               FILE_NAME

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

SYSAUX                        /u01/app/oracle/oradata/TRCDB/datafile/o1_mf_sysaux_j1ypkmx8_.dbf

SQL>

Start managed recovery.

SQL> recover managed standby database disconnect from session;

Media recovery complete.

SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS

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

DGRD      ALLOCATED             0          0          0          0

ARCH      CLOSING               1         60      28672        366

DGRD      ALLOCATED             0          0          0          0

ARCH      CLOSING               1         62       6144        395

ARCH      CLOSING               1         59          1         32

ARCH      CLOSING               1         63       8192        959

RFS       IDLE                  1          0          0          0

RFS       IDLE                  1         64     111491          2

RFS       IDLE                  0          0          0          0

RFS       IDLE                  0          0          0          0

MRP0      APPLYING_LOG          1         64      18832     409600

11 rows selected.

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='SYSAUX';

TABLESPACE_NAME               FILE_NAME

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

SYSAUX                        /u01/app/oracle/oradata/TRCDB/datafile/o1_mf_sysaux_j1ypkmx8_.dbf

SYSAUX                        /u01/app/oracle/oradata/TRCDB/datafile/o1_mf_sysaux_j8wybm5n_.dbf

SQL> alter system set standby_file_management='AUTO';

System altered.

SQL>

Standby has successfully recovered the datafile which was originally created with 'UNNAMED%' name.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011

SQL Query is really hanged or not

 Monitoring Progress of a SQL Execution Plan / sql query is really hanged or not  

How to see the progress a query is making from within the execution plan used. Using this we can find sql query is really hanged or not.

1. Find the active sessions
2. Monitoring Progress of a SQL Execution Plan
3. DBMS_SQLTUNE.REPORT_SQL_MONITOR

1. Find the active seesions

select sid, serial#, username, status, sql_id,to_char(LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') AS LOGON, event from v$session where username='&username';

SELECT sql_id, status, sql_text FROM v$sql_monitor WHERE  username='&username' and status not like '%DONE%';

Output:
———–

SQL> select sid, serial#, username, status, sql_id, event from v$session where username='SH';

       SID    SERIAL# USERNAME                       STATUS   SQL_ID        EVENT

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

        24         23 SH                             ACTIVE   5mxdwvuf9j3vp direct path read

        27         61 SH                             ACTIVE   5mxdwvuf9j3vp direct path read

SQL>

2. Monitoring Progress of a SQL Execution Plan

column plan_line_id format 9999 heading 'LINE'

column plan_options format a10 heading 'OPTIONS'

column status format a10

column output_rows heading 'ROWS'

break on sid on sql_id on status

SELECT sid, sql_id, status, plan_line_id,

plan_operation || ' ' || plan_options operation, output_rows

FROM v$sql_plan_monitor

WHERE status not like '%DONE%' and sid='&sid' <---

ORDER BY 1,4;

-- OR ---

column plan_line_id format 9999 heading 'LINE'

column plan_options format a10 heading 'OPTIONS'

column status format a10

column output_rows heading 'ROWS'

break on sid on sql_id on status

SELECT sid, sql_id, status, plan_line_id,

plan_operation || ' ' || plan_options operation, output_rows

FROM v$sql_plan_monitor

WHERE status not like '%DONE%'

ORDER BY 1,4;

Ouput:

       SID SQL_ID        STATUS      LINE OPERATION                       ROWS

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

        24 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0

                                        1 SORT AGGREGATE                     0

                                        2 NESTED LOOPS                      10

                                        3 PARTITION RANGE ALL            23714 <---

                                        4 TABLE ACCESS FULL              23714 <---

                                        5 TABLE ACCESS FULL                 10

        27 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0

                                        1 SORT AGGREGATE                     0

                                        2 NESTED LOOPS                      10

                                        3 PARTITION RANGE ALL            23781 <---

                                        4 TABLE ACCESS FULL              23781 <---

                                        5 TABLE ACCESS FULL                 10

12 rows selected.

SQL> /

       SID SQL_ID        STATUS      LINE OPERATION                       ROWS

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

        24 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0

                                        1 SORT AGGREGATE                     0

                                        2 NESTED LOOPS                      10

                                        3 PARTITION RANGE ALL            23737 <-- see diff

                                        4 TABLE ACCESS FULL              23737 <-- see diff

                                        5 TABLE ACCESS FULL                 10

        27 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0

                                        1 SORT AGGREGATE                     0

                                        2 NESTED LOOPS                      10

                                        3 PARTITION RANGE ALL            23804 <--- see diff

                                        4 TABLE ACCESS FULL              23804 <--- see diff

                                        5 TABLE ACCESS FULL                 10

12 rows selected.

SQL> /

       SID SQL_ID        STATUS      LINE OPERATION                       ROWS

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

        24 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0

                                        1 SORT AGGREGATE                     0

                                        2 NESTED LOOPS                      10

                                        3 PARTITION RANGE ALL            23794 <-- see diff

                                        4 TABLE ACCESS FULL              23794 <-- see diff

                                        5 TABLE ACCESS FULL                 10

        27 5mxdwvuf9j3vp EXECUTING      0 SELECT STATEMENT                   0

                                        1 SORT AGGREGATE                     0

                                        2 NESTED LOOPS                      10

                                        3 PARTITION RANGE ALL            23861 <--- see diff

                                        4 TABLE ACCESS FULL              23861 <--- see diff

                                        5 TABLE ACCESS FULL                 10

12 rows selected.

SQL>

We can see the difference. Hence the query is not hanged

3. REPORT_SQL_MONITOR in HTML (OR) TEXT format

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SPOOL report_sql_monitor.txt

SELECT DBMS_SQLTUNE.report_sql_monitor(

  sql_id       => '5mxdwvuf9j3vp', <--- SQLID

  type         => 'TEXT',         <--- HTML

  report_level => 'ALL') AS report

FROM dual;

SPOOL OFF

Output
=======

SQL Monitoring Report

SQL Text

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

SELECT ......

Global Information

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

 Status              :  EXECUTING <----

 Instance ID         :  1

 Session             :  SH (27:61)

 SQL ID              :  5mxdwvuf9j3vp

 SQL Execution ID    :  16777216

 Execution Started   :  10/26/2016 20:19:01 <---

 First Refresh Time  :  10/26/2016 20:19:05

 Last Refresh Time   :  10/26/2016 20:38:25

 Duration            :  1164s <---

 Module/Action       :  SQL*Plus/-

 Service             :  SYS$USERS

 Program             :  sqlplus@rac2.amitpawardba.com (TNS V1-V3)

Global Stats

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

| Elapsed |   Cpu   |    IO    | Concurrency | Buffer | Read | Read  |

| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Gets  | Reqs | Bytes |

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

|    1172 |      77 |     1095 |        0.00 |    15M | 537K | 114GB |

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

SQL Plan Monitoring Details (Plan Hash Value=2043253752)

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

| Id   |        Operation        |   Name    |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |     Activity Detail     |

|      |                         |           | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |       (# samples)       |

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

|    0 | SELECT STATEMENT        |           |         |      |           |        |     1 |          |      |       |          |                         |

|    1 |   SORT AGGREGATE        |           |       1 |      |       811 |   +220 |     1 |        0 |      |       |          |                         |

| -> 2 |    NESTED LOOPS         |           |    5557 | 370M |      1167 |     +4 |     1 |        4 |      |       |          |                         |

| -> 3 |     PARTITION RANGE ALL |           |    919K |  499 |      1167 |     +4 |     1 |    10325 |      |       |          |                         |

| -> 4 |      TABLE ACCESS FULL  | SALES     |    919K |  499 |      1167 |     +4 |     5 |    10325 |    1 | 208KB |          |                         |

| -> 5 |     TABLE ACCESS FULL   | CUSTOMERS |       1 |  403 |      1170 |     +1 | 10326 |        4 | 535K | 114GB |   100.00 | Cpu (35)                |

|      |                         |           |         |      |           |        |       |          |      |       |          | direct path read (1128) |

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

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Check the last incarnation restore time of oracle database

Check the last incarnation restore time when the database is started with resetlogs command with following command.

Incarnation is the time when the database is started with resetlogs command. If you start the database is the incarnation, then all available archivelog and backup become invalid. If you want to restore old incarnation, then you need to set the incarnation level in RMAN to restore or recover old SCN.

Always take a fresh backup of the database when you start the database in resetlog. Because when the database is open with resetlogs all redo Sequence no is reset and start from one.

It will show you the last incarnation time when the database is up with resetlogs commands.

SELECT incarnation#,

resetlogs_change#,

TO_CHAR(resetlogs_time, 'dd-mon-yyyy hh24:mi:ss') db_restored_time ,

scn_to_timestamp(resetlogs_change#) db_recovery_till_time

FROM v$database_incarnation

WHERE resetlogs_change# !=

(SELECT MIN(resetlogs_change#) FROM v$database_incarnation

);

List of incarnation:

select incarnation#, resetlogs_change# from v$database_incarnation;

List incarnation from RMAN prompt:

RMAN> list incarnation of database;

When you open the database in resetlogs then a new incarnation is created at the database level. If you tried to restore the database before incarnation state, then you got the error:

RMAN-20208: until change is before resetlogs change

Then you need to check the SCN from incarnation commands:

Example:

RMAN> list incarnation of database;

 DB Key Inc Key DB Name DB ID         STATUS  Reset SCN Reset Time

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

1      1       ORCL     1235491512   PARENT  1         03-AUG-17

2      2       ORCL     1235491512   PARENT  543000    10-OCT-17

3      3       ORCL     1235491512   CURRENT 548000    10-OCT-17

If I want to restore the SCN no 54700 previous then the current incarnation number, then I will get the rman error:

RMAN-20208: until change is before resetlogs change

run{

set until scn=547000;

restore database;

recover database;

}

executing command: set until clause

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== error message stack follows ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of set command at 10/10/2017 9:10:11

RMAN-20208: until change is before resetlogs change

Solution:

to overcome this problem, we need to set the database to old incarnation:

RMAN> shutdown immediate

RMAN> startup mount

RMAN> reset database to incarnation 2;

database reset to incarnation 2

Then after restore, you can check incarnation status again:

RMAN> run

2> {

3> set until scn=547000;

4> restore database;

5> recover database;

6> }

RMAN> alter database open resetlogs;

database opened

RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations

DB Key Inc Key DB Name DB ID         STATUS  Reset SCN Reset Time

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

1      1       ORCL     1235491512   PARENT  1         03-AUG-17

2      2       ORCL     1235491512   PARENT  543000    10-OCT-17

4      4       ORCL     1235491512   CURRENT 547000    10-OCT-17

3      3       ORCL     1235491512   ORPHAN  548000    10-OCT-17

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Script to Drop a user's objects.

This script will produce the alter/drop commands that we can use to drop all the user objects from a database.

REM Script to drop all user objects

SET LINES 132 PAGES 59 FEEDBACK OFF ECHO OFF VERIFY OFF

BREAK ON username

TTITLE left _date center 'SQL To Drop A Users Objects' skip 2

PROMPT When prompted, enter the name of the user who''s objects you wish to drop

PROMPT Then cut and paste the commands presented to drop those objects.

SELECT 'alter table '

|| owner

|| '.'

|| table_name

|| ' drop constraint '

|| constraint_name

|| ' cascade;' code

FROM dba_constraints

WHERE (constraint_type = 'R') AND (owner = UPPER ('&&enter_user_name'))

UNION

SELECT DISTINCT 'DROP '

|| object_type

|| ' '

|| owner

|| '.'

|| object_name

|| ';' code

FROM dba_objects

WHERE (object_type NOT IN ('INDEX'))

AND (owner = UPPER ('&&enter_user_name'))

ORDER BY code DESC;

Sample Output

SQL> When prompted, enter the name of the user who’s objects you wish to drop

SQL> Then cut and paste the commands presented to drop those objects.

18-JUL-08 SQL To Drop A Users Objects

CODE

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

DROP TABLE SCOTT.UMCATEGORYCONVFACTOR;

DROP TABLE SCOTT.UOMCATEGORYCONVFACTOR;

DROP TABLE SCOTT.UDT_SOURCING;

DROP TABLE SCOTT.WEBSPRELOPTS;

DROP TABLE SCOTT.WEBSKUOPTS;

DROP TABLE SCOTT.WDDDATA;

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!