Tuesday 15 June 2021

ORA-23515: Materialized Views and/or Their Indices Exist in the Tablespace

While dropping any Tablespace, you might get the errror

ORA-23515: Materialized Views and/or Their Indices Exist in the Tablespace

This is due to the fact that the tablespace contains the indexes or Materialized views

You may use the following queries to find out which materialized views and/or which indeces of materialized view container tables are on the tablespace you intend to drop.

Once these objects are found you can move the tables, rebuild indeces in other tablespace.

 select s.sowner MV_OWNER, s.tname MV_NAME, p.name TABLESPACE
  from sys.obj$ o, sys.tab$ t, sys.user$ u, sys.snap$ s , sys.ts$ p
  where t.ts#=p.ts# and p.name='<tablespace_name>'
  and t.obj#=o.obj# and o.owner#=u.user# and u.name=s.sowner and o.name=s.tname
  order by 1,2;

select o.owner, o.object_name index_name, o.object_type, sq.mv_owner,sq.mv_name,sq.tablespace
  from dba_objects o,
  (select i.obj#,s.sowner MV_OWNER, s.tname MV_NAME, p.name TABLESPACE
  from sys.obj$ o, sys.tab$ t, sys.ind$ i, sys.user$ u, sys.snap$ s,sys.ts$ p where i.ts#=p.ts# and p.name='<tablespace_name>'
  and i.bo#=t.obj# and t.obj#=o.obj# and o.owner#=u.user# and u.name=s.sowner and o.name=s.tname ) sq
  where sq.obj#=o.object_id
  order by 1,2,3;

An example output listing mviews and their owner where the container table of the mview is on the tablespace <tablespace_name>

SQL>  select s.sowner MV_OWNER, s.tname MV_NAME, p.name TABLESPACE
  2    from sys.obj$ o, sys.tab$ t, sys.user$ u, sys.snap$ s , sys.ts$ p
  3    where t.ts#=p.ts# and p.name='<tablespace_name>'
  4    and t.obj#=o.obj# and o.owner#=u.user# and u.name=s.sowner and o.name=s.tname
  5    order by 1,2;

MV_OWNER                       MV_NAME                        TABLESPACE
------------------------------ ------------------------------ ------------------------------
<user_name>                    TEST_MV                        <tablespace_name>
<user_name>                    CUST_MV                        <tablespace_name>

An example output listing indeces of mviews container tables, where the index is on tablespace <tablespace_name>

SQL> select o.owner, o.object_name index_name, o.object_type, sq.mv_owner,sq.mv_name,sq.tablespace
  2    from dba_objects o,
  3    (select i.obj#,s.sowner MV_OWNER, s.tname MV_NAME, p.name TABLESPACE
  4    from sys.obj$ o, sys.tab$ t, sys.ind$ i, sys.user$ u, sys.snap$ s,sys.ts$ p where i.ts#=p.ts# and p.name='<tablespace_name>'
  5    and i.bo#=t.obj# and t.obj#=o.obj# and o.owner#=u.user# and u.name=s.sowner and o.name=s.tname ) sq
  6    where sq.obj#=o.object_id
  7    order by 1,2,3;

OWNER      INDEX_NAME                     OBJECT_TYPE         MV_OWNER                       MV_NAME                         TABLESPACE
---------- ------------------------------ ------------------- ------------------------------ ------------------------------ ------------------------------
<user_name>  I_SNAP$_TEST_MV                INDEX              <user_name>                             TEST_MV                        <tablespace_name>
<user_name>  I_SNAP$_CUST_MV                INDEX              <user_name>                             CUST_MV                        <tablespace_name>

YOU MAY DELETE THE OBJECTS or MOVE Them to other tablespaces and then try the delete Tablespace command

Wednesday 19 May 2021

ORA-20000: Unable to set values for index does not exist or insufficient privileges while importing schema from 12c to 19c

ORA-20000: Unable to set values for index <INDEX_NAME> does not exist or insufficient privileges while importing schema from 12c to 19c

Importing statistics failed for 1 object(s);

While importing Schema objects from 12c Oracle Database to 19c Oracle Database, we face the above issue

CAUSE

This problem is fixed by unpublished BUG 27761685.

SOLUTION

To avoid the issue use the workaround:

- Add include=index while executing expdp

and

- Add include=statistics while executing impdp

expdp USER_A/USER_A directory=test_dir dumpfile=exp_data.dmp include=statistics,index tables=btree_tbl reuse_dumpfiles=y
impdp USER_B/USER_B directory=test_dir dumpfile=exp_data.dmp remap_schema=user_a:user_b include=statistics

Reference:

ORA-20000 Unable to Set Values for Index XX: Does Not Exist or Insufficient Priv is Raised While Executing Impdp (Doc ID 2176364.1)

Friday 14 May 2021

Errors while Installing 12c EM Cloud Control

This blog will list the errors faced during Installation of Enterprise Manager 12c Cloud Control

1. :Error in invoking target 'install' of makefile '/u01/app/oracle/oms12cr3/oms/sqlplus/lib/ins_sqlplus.mk'

INFO: End output from spawned process.

INFO: ----------------------------------

INFO: Exception thrown from action: make

Exception Name: MakefileException

Exception String: Error in invoking target 'install' of makefile '/u01/app/oracle/oms12cr3/oms/sqlplus/lib/ins_sqlplus.mk'. See '/u01/app/oraInventory/logs/installActions2021-05-14_12-14-33-AM.log' for details.

Exception Severity: 1

INFO: POPUP WARNING:Error in invoking target 'install' of makefile '/u01/app/oracle/oms12cr3/oms/sqlplus/lib/ins_sqlplus.mk'. See '/u01/app/oraInventory/logs/installActions2021-05-14_12-14-33-AM.log' for details.

INFO: /usr/bin/ld: cannot find crt1.o: No such file or directory

/usr/bin/ld: cannot find crti.o: No such file or directory

/usr/bin/ld: cannot find -lclntsh

/usr/bin/ld: cannot find -lc

/usr/bin/ld: cannot find crtn.o: No such file or directory

Solution:

Install glibc-devel package

[root@oel8a ~]# yum install glibc-devel*

Oracle Linux 8 EPEL Packages for Development (x86_64) 1.9 kB/s | 2.5 kB 00:01

Oracle Linux 8 EPEL Packages for Development (x86_64) 601 kB/s | 16 MB 00:27

Last metadata expiration check: 0:00:15 ago on Fri 14 May 2021 12:30:16 AM +03.

Package glibc-devel-2.28-42.0.1.el8.x86_64 is already installed.

Dependencies resolved.

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

Package Arch Version Repository Size

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

Upgrading:

glibc-devel x86_64 2.28-127.0.3.el8_3.2 ol8_baseos_latest 1.0 M

glibc-langpack-en x86_64 2.28-127.0.3.el8_3.2 ol8_baseos_latest 825 k

glibc-headers x86_64 2.28-127.0.3.el8_3.2 ol8_baseos_latest 477 k

libnsl x86_64 2.28-127.0.3.el8_3.2 ol8_baseos_latest 100 k

glibc-common x86_64 2.28-127.0.3.el8_3.2 ol8_baseos_latest 1.3 M

glibc x86_64 2.28-127.0.3.el8_3.2 ol8_baseos_latest 3.6 M

Transaction Summary

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

Upgrade 6 Packages

Total download size: 7.3 M

Is this ok [y/N]: y

Downloading Packages:

(1/6): glibc-headers-2.28-127.0.3.el8_3.2.x86_64.rpm 120 kB/s | 477 kB 00:03

(2/6): libnsl-2.28-127.0.3.el8_3.2.x86_64.rpm 238 kB/s | 100 kB 00:00

(3/6): glibc-langpack-en-2.28-127.0.3.el8_3.2.x86_64.rpm 165 kB/s | 825 kB 00:05

(4/6): glibc-devel-2.28-127.0.3.el8_3.2.x86_64.rpm 199 kB/s | 1.0 MB 00:05

(5/6): glibc-common-2.28-127.0.3.el8_3.2.x86_64.rpm 303 kB/s | 1.3 MB 00:04

(6/6): glibc-2.28-127.0.3.el8_3.2.x86_64.rpm 430 kB/s | 3.6 MB 00:08

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

Total 549 kB/s | 7.3 MB 00:13

Running transaction check

Transaction check succeeded.

Running transaction test

Transaction test succeeded.

Running transaction

Preparing : 1/1

Upgrading : glibc-common-2.28-127.0.3.el8_3.2.x86_64 1/12

Running scriptlet: glibc-2.28-127.0.3.el8_3.2.x86_64 2/12

Upgrading : glibc-2.28-127.0.3.el8_3.2.x86_64 2/12

Running scriptlet: glibc-2.28-127.0.3.el8_3.2.x86_64 2/12

warning: /etc/nsswitch.conf created as /etc/nsswitch.conf.rpmnew

Upgrading : glibc-langpack-en-2.28-127.0.3.el8_3.2.x86_64 3/12

Running scriptlet: glibc-headers-2.28-127.0.3.el8_3.2.x86_64 4/12

Upgrading : glibc-headers-2.28-127.0.3.el8_3.2.x86_64 4/12

Upgrading : glibc-devel-2.28-127.0.3.el8_3.2.x86_64 5/12

Running scriptlet: glibc-devel-2.28-127.0.3.el8_3.2.x86_64 5/12

Upgrading : libnsl-2.28-127.0.3.el8_3.2.x86_64 6/12

Running scriptlet: glibc-devel-2.28-42.0.1.el8.x86_64 7/12

Cleanup : glibc-devel-2.28-42.0.1.el8.x86_64 7/12

Cleanup : libnsl-2.28-42.0.1.el8.x86_64 8/12

Cleanup : glibc-headers-2.28-42.0.1.el8.x86_64 9/12

Cleanup : glibc-common-2.28-42.0.1.el8.x86_64 10/12

Cleanup : glibc-langpack-en-2.28-42.0.1.el8.x86_64 11/12

Cleanup : glibc-2.28-42.0.1.el8.x86_64 12/12

Running scriptlet: glibc-2.28-42.0.1.el8.x86_64 12/12

Running scriptlet: glibc-common-2.28-127.0.3.el8_3.2.x86_64 12/12

Verifying : glibc-devel-2.28-127.0.3.el8_3.2.x86_64 1/12

Verifying : glibc-devel-2.28-42.0.1.el8.x86_64 2/12

Verifying : glibc-langpack-en-2.28-127.0.3.el8_3.2.x86_64 3/12

Verifying : glibc-langpack-en-2.28-42.0.1.el8.x86_64 4/12

Verifying : glibc-headers-2.28-127.0.3.el8_3.2.x86_64 5/12

Verifying : glibc-headers-2.28-42.0.1.el8.x86_64 6/12

Verifying : libnsl-2.28-127.0.3.el8_3.2.x86_64 7/12

Verifying : libnsl-2.28-42.0.1.el8.x86_64 8/12

Verifying : glibc-common-2.28-127.0.3.el8_3.2.x86_64 9/12

Verifying : glibc-common-2.28-42.0.1.el8.x86_64 10/12

Verifying : glibc-2.28-127.0.3.el8_3.2.x86_64 11/12

Verifying : glibc-2.28-42.0.1.el8.x86_64 12/12

Upgraded:

glibc-devel-2.28-127.0.3.el8_3.2.x86_64 glibc-langpack-en-2.28-127.0.3.el8_3.2.x86_64 glibc-headers-2.28-127.0.3.el8_3.2.x86_64 libnsl-2.28-127.0.3.el8_3.2.x86_64

glibc-common-2.28-127.0.3.el8_3.2.x86_64 glibc-2.28-127.0.3.el8_3.2.x86_64

Complete!

[root@oel8a ~]#

Reference:

EM 12c: Enterprise Manager 12c Cloud Control OMS Installation on SUSE Linux 11 Fails with Message: Error in invoking target 'install' of makefile (Doc ID 1503554.1)

I have installed the glibc-devel and also ignored the errors. I was able to successfully install the OEM 12c Cloud Control

Wednesday 17 March 2021

RW-50010: Error: - script has returned an error: 2 (Executing cr9idata.pl) During R12.2 Installation on Oracle Linux 8

Problem

Faced below error while Installing 12.2 on Linux 8.

Unable to execute cr9idata.pl

Executing cr9idata.pl

Executing: perl /u01/oracle/DEV/12.1.0/nls/data/old/cr9idata.pl

Unable to execute cr9idata.pl

RW-50010: Error: - script has returned an error: 2

RW-50004: Error code received when running external process. Check log file for details.

Running Database Install Driver for DEV instance

Solution:

I have installed the following rpms:

Install the following rpm

[root@dev02 DEV_dev2]#yum list perl-File-CheckTree*

Available Packages

perl-File-CheckTree.noarch 4.42-303.el8 ol8_appstream

perl-File-CheckTree.src 4.42-303.el8 ol8_appstream

[root@dev02 DEV_dev2]# yum install perl-File-CheckTree*

[root@dev02 DEV_dev2]#yum list perl-File-CheckTree*

Last metadata expiration check: 0:10:24 ago on Sun 21 Feb 2021 11:39:26 PM +03.

Installed Packages

perl-File-CheckTree.noarch 4.42-303.el8 @ol8_appstream

Available Packages

perl-File-CheckTree.src 4.42-303.el8 ol8_appstream

[root@dev02 DEV_dev2]#

The above is after installing the rpm.

Grid Patching fails to start CRS

Latest CPU Patching on the 19c Grid home fails with below errors:

OPATCHAUTO-68061: The orchestration engine failed.

OPATCHAUTO-68061: The orchestration engine failed with return code 1

OPATCHAUTO-68061: Check the log for more details.

OPatchAuto failed.

Also it mentions to solve the issue and do opathauto resume.

Solution:

Verify the patches have been applied on both the nodes:

cd /u01/app/19.3.0/grid/bin

[root@DC2-GTDB-V-01 bin]# ./kfod op=patches

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

List of Patches

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

29340594

32218454

32218663

32222571

32240590

[root@DC2-GTDB-V-01 bin]#

[root@DC2-GTDB-V-02 bin]# ./kfod op=patches

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

List of Patches

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

29340594

32218454

32218663

32222571

32240590

[root@DC2-GTDB-V-02 bin]#

The perform the following steps

as root

cd $GI_HOME/bin

clscfg -localpatch

clscfg -patch

After this restart the CRS and verify if its started properly.

TNS-12518: TNS:listener could not hand off client connection

While Connecting to oracle RAC database using sqlplus gives the following errors:

TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe

Solution:

Check the permissions on the oracle executable in the RDBMS Home

cd $ORACLE_HOME/bin

ls -ltr oracle

It should have 6751 permission

chmod 6751 oracle

Thursday 11 March 2021

How to Change Private IP in RAC Cluster (19c) on OEL 8

In this post, we will see how to change the Private IP of a 2-Node Oracle.

The purpose of this note is to describe how to change or update the private network (cluster_interconnect) information in Oracle Clusterware.

./oifcfg getif

ens192 10.92.27.0 global public

ens224 192.168.221.0 global cluster_interconnect,asm

ens161 192.168.27.8 global cluster_interconnect,asm

[root@DC2-GTDB-V-01 bin]#

To set any interface

./oifcfg setif -global ens161/192.168.27.0:cluster_interconnect,asm

To Delete any interface

./oifcfg delif -global ens224/192.168.221.0

Ref:

How to Modify Private Network Information in Oracle Clusterware (Doc ID 283684.1)

Saturday 20 February 2021

/jre/Linux_x64/1.6.0/bin/java: No such file or directory

Java Error while launching Rapidwiz

./buildStage.sh: line 142: /u01/software/ebs/1220/startCD/Disk1/rapidwiz/bin/../jre/Linux_x64/1.6.0/bin/java: No such file or directory

eror unzipping shiphome ...

Rapidwiz is expecting a 32-bit version of the JDK, and the error occurs because the only version available is 64-bit.

The issue is discussed in the following unpublished defect:

Bug 24386615 – STARTCD SHOULD BE UPDATED TO USE 64 BIT VERSION OF THE JDK/JRE .

Solution:

Copy a 64 bit version of the JDK from any 12c Oracle Home or download it from internet.

1. Backup the existing JDK located in startCD/Disk1/rapidwiz/jre/Linux_x64/

mv 1.6.0 1.6.0_old

mkdir 1.6.0

2. Copy the contents of jre directory unded the 1.6.0 directory

3. Retest Rapidwiz and confirm the error is resolved.

Thursday 11 February 2021

Rolling Forward a Physical Standby Database using RMAN Incremental Backup

There is a huge gap around 5k between the archive logs of Standby Database and Primary Database.

Instead of waiting for the Redo Apply to sync the archive logs, we follow the below steps to sync the Standby database.

1. Cancel the Managed Recovery

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Find the SCN of the Standby Database

SELECT CURRENT_SCN FROM V$DATABASE;

11878709541860

select min(checkpoint_change#) from v$datafile_header;

11878709541861

We have to use the lower of the two scn values. 11878709541860

3. Connect to Primary and Identify the datafile needed for backup

SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 11878709541860;

322 +DATA/PRODNEW/DATAFILE/data.593.1063685263

323 +DATA/PRODNEW/DATAFILE/data.594.1063879113

324 +DATA/PRODNEW/DATAFILE/data.595.1063879183

325 +DATA/PRODNEW/DATAFILE/data.596.1063879207

326 +DATA/PRODNEW/DATAFILE/data.597.1063902133

327 +DATA/PRODNEW/DATAFILE/data.598.1063965125

328 +DATA/PRODNEW/DATAFILE/data.599.1063965193

4. Connect to Primary and take the backup.

RMAN> backup datafile 322,323,324,325,326,327,328 format '/u01/backup/ForStandby_%U' tag 'FORSTANDBY';

RMAN> backup incremental from SCN 11878709541860 database format '/u01/backup/ForStandby_%U' tag 'FORSTANDBY';

RMAN> backup current controlfile for standby format '/u01/backup/ForStandbyCTRL.bck';

5. Copy the Backup to the standby server using scp or cp.

6. Catalog the backup files as shown below:

RMAN> catalog start with '/u01/backup/';

using target database control file instead of recovery catalog

searching for all files that match the pattern /u01/backup/

List of Files Unknown to the Database

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

File Name: /u01/backup/ForStandby_5svmrkto_1_1

File Name: /u01/backup/ForStandby_5tvmrkto_1_1

File Name: /u01/backup/ForStandby_5uvmrkto_1_1

File Name: /u01/backup/ForStandby_5vvmrkto_1_1

File Name: /u01/backup/ForStandby_60vmrktp_1_1

File Name: /u01/backup/ForStandby_61vmrktp_1_1

File Name: /u01/backup/ForStandby_62vmrktp_1_1

File Name: /u01/backup/ForStandby_63vmrktp_1_1

File Name: /u01/backup/ForStandby_64vmrktp_1_1

File Name: /u01/backup/ForStandby_65vmrktp_1_1

File Name: /u01/backup/ForStandby_66vmrlmr_1_1

File Name: /u01/backup/ForStandby_67vmrlms_1_1

File Name: /u01/backup/ForStandby_68vmrlms_1_1

File Name: /u01/backup/ForStandby_69vmrlmt_1_1

File Name: /u01/backup/ForStandby_6avmrln5_1_1

File Name: /u01/backup/ForStandby_6bvmrlnj_1_1

File Name: /u01/backup/ForStandby_6cvmrlo4_1_1

File Name: /u01/backup/ForStandby_6dvmrlon_1_1

File Name: /u01/backup/ForStandby_6evmrlpd_1_1

File Name: /u01/backup/ForStandby_6fvmrlq3_1_1

File Name: /u01/backup/ForStandby_6gvmrlr8_1_1

File Name: /u01/backup/ForStandby_6hvmrlsk_1_1

File Name: /u01/backup/ForStandby_6ivmrlu0_1_1

File Name: /u01/backup/ForStandby_6jvmrlva_1_1

File Name: /u01/backup/ForStandby_6kvmrm0o_1_1

File Name: /u01/backup/ForStandby_6lvmrm2n_1_1

File Name: /u01/backup/ForStandbyCTRL.bck

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files...

List of Cataloged Files

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

File Name: /u01/backup/ForStandby_5svmrkto_1_1

File Name: /u01/backup/ForStandby_5tvmrkto_1_1

File Name: /u01/backup/ForStandby_5uvmrkto_1_1

File Name: /u01/backup/ForStandby_5vvmrkto_1_1

File Name: /u01/backup/ForStandby_60vmrktp_1_1

File Name: /u01/backup/ForStandby_61vmrktp_1_1

File Name: /u01/backup/ForStandby_62vmrktp_1_1

File Name: /u01/backup/ForStandby_63vmrktp_1_1

File Name: /u01/backup/ForStandby_64vmrktp_1_1

File Name: /u01/backup/ForStandby_65vmrktp_1_1

File Name: /u01/backup/ForStandby_66vmrlmr_1_1

File Name: /u01/backup/ForStandby_67vmrlms_1_1

File Name: /u01/backup/ForStandby_68vmrlms_1_1

File Name: /u01/backup/ForStandby_69vmrlmt_1_1

File Name: /u01/backup/ForStandby_6avmrln5_1_1

File Name: /u01/backup/ForStandby_6bvmrlnj_1_1

File Name: /u01/backup/ForStandby_6cvmrlo4_1_1

File Name: /u01/backup/ForStandby_6dvmrlon_1_1

File Name: /u01/backup/ForStandby_6evmrlpd_1_1

File Name: /u01/backup/ForStandby_6fvmrlq3_1_1

File Name: /u01/backup/ForStandby_6gvmrlr8_1_1

File Name: /u01/backup/ForStandby_6hvmrlsk_1_1

File Name: /u01/backup/ForStandby_6ivmrlu0_1_1

File Name: /u01/backup/ForStandby_6jvmrlva_1_1

File Name: /u01/backup/ForStandby_6kvmrm0o_1_1

File Name: /u01/backup/ForStandby_6lvmrm2n_1_1

File Name: /u01/backup/ForStandbyCTRL.bck

7. Recover the Database using recover database command with noredo option

RMAN> recover database noredo;

I faced the below error in this command:

channel ORA_DISK_1: reading from backup piece /u01/backup/ForStandby_66vmrlmr_1_1

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

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

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

RMAN-03002: failure of recover command at 02/10/2021 20:19:21

ORA-19870: error while restoring backup piece /u01/backup/ForStandby_66vmrlmr_1_1

ORA-19573: cannot obtain exclusive enqueue for datafile 10

DGMGRL> edit database mwprod set state='APPLY-OFF';

Succeeded.

DGMGRL> EXIT

8. Rerun the recover database command:

RMAN> recover database noredo;

Starting recover at 10-FEB-21

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00010: +DATA_PRD/PROD/DATA/wmmwsdata.274.975865083

destination for restore of datafile 00015: +DATA_MWPRD/MWPROD/DATAFILE/sysaux.279.990873289

destination for restore of datafile 00028: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.292.1017879367

destination for restore of datafile 00045: +DATA_MWPRD/MWPROD/DATAFILE/bpmmws.310.1023630077

destination for restore of datafile 00063: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.328.1030733483

destination for restore of datafile 00081: +DATA_MWPRD/MWPROD/DATAFILE/bpmis.346.1036576923

destination for restore of datafile 00098: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.364.1040497895

destination for restore of datafile 00114: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.380.1043660485

destination for restore of datafile 00130: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.396.1046338377

destination for restore of datafile 00148: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.414.1050082413

destination for restore of datafile 00164: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.430.1052014897

destination for restore of datafile 00181: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.447.1053768029

destination for restore of datafile 00197: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.463.1055320021

destination for restore of datafile 00213: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.479.1057249435

destination for restore of datafile 00230: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.496.1058435243

destination for restore of datafile 00248: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.514.1059662621

destination for restore of datafile 00264: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.530.1060506509

destination for restore of datafile 00280: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.546.1061628269

destination for restore of datafile 00296: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.567.1062408681

destination for restore of datafile 00312: +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.583.1063167545

channel ORA_DISK_1: reading from backup piece /u01/backup/ForStandby_66vmrlmr_1_1

channel ORA_DISK_1: piece handle=/u01/backup/ForStandby_66vmrlmr_1_1 tag=FORSTANDBY

As it progresses you will see the restoration of files in the alert log

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

Incremental restore complete of datafile 314 +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.585.1063275209

checkpoint is 11879707151160

last deallocation scn is 11879558491811

Incremental restore complete of datafile 298 +DATA_MWPRD/MWPROD/DATAFILE/wmmwsdata.569.1062485497

checkpoint is 11879707151160

last deallocation scn is 11877341663346

Wed Feb 10 21:22:45 2021

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

In case there are files added at primary, you need to restore the control file at standby first and then mount the database

RMAN> startup nomount;

RMAN> restore standby controlfile from '/u01/backup/ForStandbyCTRL.bck';

RMAN> alter database mount;

RMAN> CATALOG START WITH '/tmp/ForStandby';

The restore the datafiles using below script

run

{

set newname for datafile 322 to '+DATA_MWPRD';

set newname for datafile 323 to '+DATA_MWPRD';

set newname for datafile 324 to '+DATA_MWPRD';

set newname for datafile 325 to '+DATA_MWPRD';

set newname for datafile 326 to '+DATA_MWPRD';

set newname for datafile 327 to '+DATA_MWPRD';

set newname for datafile 328 to '+DATA_MWPRD';

set newname for datafile 329 to '+DATA_MWPRD';

set newname for datafile 330 to '+DATA_MWPRD';

set newname for datafile 331 to '+DATA_MWPRD';

set newname for datafile 332 to '+DATA_MWPRD';

set newname for datafile 333 to '+DATA_MWPRD';

restore datafile 322,323,324,325,326,327,328,329,330,331,332,333;

}

8) Rename the datafiles in new standby controlfile

Since the controlfile is restored from PRIMARY the datafile locations in STANDBY controlfile will be same as PRIMARY database, so catalog datafiles in STANDBY will do the necessary rename operations.

Perform the below step in STANDBY for each diskgroup where the datafile directory structure between primary and standby are different.

Once all files have been cataloged, switch the database to copy:

RMAN> CATALOG START WITH '+DATA_MWPRD/MWPROD/DATAFILE';

RMAN> SWITCH DATABASE TO COPY;

RMAN> RECOVER DATABASE NOREDO;

channel ORA_DISK_7: piece handle=/u01/backup/ForStandby_9gvmu25p_1_1 tag=FORSTANDBY

channel ORA_DISK_7: restored backup piece 1

channel ORA_DISK_7: restore complete, elapsed time: 00:40:03

channel ORA_DISK_13: piece handle=/u01/backup/ForStandby_9nvmu2e4_1_1 tag=FORSTANDBY

channel ORA_DISK_13: restored backup piece 1

channel ORA_DISK_13: restore complete, elapsed time: 00:50:27

Finished recover at 11-FEB-21