Archives September 2021

HOW TO SELECT COUNT(*) FROM MULTIPLE TABLES IN ORACLE SQLs

Try this query to count total rows from multiple tables at once

kish@exdbx<>select count(*) from TABX
union all
select count(*) from SALES_TAB
union all
select count(*) from AFTERDROP
union all
select count(*) from SALES_TAB_COPY_TEMP
union all
select count(*) from SALES_TAB_COPY
union all
select count(*) from SALES_MV
union all
select count(*) from DEMO_DUP
union all
select count(*) from SALES_T
union all
select count(*) from SALES;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17

                                COUNT(*)
----------------------------------------
                                 1048576
                                 1398102
                                    4000
                                  349526
                                 1398102
                                 1048576
                                 2000001
                                 1048576
                                 4000002

9 rows selected.
FileNotFoundError: [Errno 2] File does not exist Jupyter notebook

Cause:

The path or file you specified does not exist 

df=pn.read_csv(r"C:\kisha\Downloads\owid-covid-data.csv")

FileNotFoundError: [Errno 2] File C:\kisha\Downloads\owid-covid-data.csv does not exist: 'C:\\kisha\\Downloads\\owid-covid-data.csv'

Workaround:

Get the current directory and specify the right path to the dataset

import os
print(os.getcwd())

SQL QUERY TO FIND FUZZINESS IN DATAFILE HEADER

Use the below query to find the consistency between controlfile header and datafile header!

set lines 200 pages 1000
col file# for 9999999
select
c_h.file#,
case 
when c_h.CHECKPOINT_CHANGE# > d_h.CHECKPOINT_CHANGE# then 'Fuzzy header!'
when c_h.CHECKPOINT_CHANGE# = d_h.CHECKPOINT_CHANGE# then 'ACID complaint!'
when c_h.CHECKPOINT_CHANGE# < d_h.CHECKPOINT_CHANGE# then 'Media recovery required!'
end Consistency_check
from
v$datafile c_h
inner join v$datafile_header d_h on (c_h.FILE#=d_h.FILE#)
order by 1;
ORA-01609: log 1 is the current log for thread 1 – cannot drop membersORA-00312: online log 1 thread 1:’/apps01/base/oradata/dbx01/onlinelog/group_1.262.1083714911’ORA-00312: online log 1 thread 1:’/apps01/FRA/DBX01/onlinelog/o1_mf_1_jnl5w6gs_.log’

Cause:

I was trying to drop the duplicate logfiles in standby database and get error during drop due to current active thread.

SQL> select GROUP#, MEMBER from V$LOGFILE;

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
         4
/apps01/base/oradata/dbx01/onlinelog/group_4.265.1083714917

         4
/apps01/FRA/DBX01/onlinelog/o1_mf_4_jnl5ww8k_.log

         3
/apps01/base/oradata/dbx01/onlinelog/group_3.264.1083714915


    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
         3
/apps01/FRA/DBX01/onlinelog/o1_mf_3_jnl5wllv_.log

         2
/apps01/base/oradata/dbx01/onlinelog/group_2.263.1083714913

         2
/apps01/FRA/DBX01/onlinelog/o1_mf_2_jnl5wd63_.log


    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
         1
/apps01/base/oradata/dbx01/onlinelog/group_1.262.1083714911

         1
/apps01/FRA/DBX01/onlinelog/o1_mf_1_jnl5w6gs_.log


8 rows selected.

Now i drop the duplicate redolog file but it fail due to active recovery purpose

SQL> alter database drop logfile member '/apps01/base/oradata/dbx01/onlinelog/group_1.262.1083714911';
alter database drop logfile member '/apps01/base/oradata/dbx01/onlinelog/group_1.262.1083714911'
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
ORA-00312: online log 1 thread 1:
'/apps01/base/oradata/dbx01/onlinelog/group_1.262.1083714911'
ORA-00312: online log 1 thread 1:
'/apps01/FRA/DBX01/onlinelog/o1_mf_1_jnl5w6gs_.log'

Workaround:

As this is a standby database, i cannot open it due to incomplete recovery. Drop the remaining logfiles which are not required and status is ‘not current‘ first 

SQL> alter database drop logfile member '/apps01/base/oradata/dbx01/onlinelog/group_2.263.1083714913';

Database altered.

SQL> alter database drop logfile member '/apps01/base/oradata/dbx01/onlinelog/group_4.265.1083714917';

Database altered.

SQL> alter database drop logfile member '/apps01/base/oradata/dbx01/onlinelog/group_3.264.1083714915';

Database altered.

Now check the status of the duplicate logs

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         1          1          0   52428800        512          2 NO
CURRENT                1967801 21-SEP-21   2.8147E+14                    0

         4          1          0   52428800        512          1 YES
UNUSED                 1936695 21-SEP-21      1967801 21-SEP-21          0

         3          1          0   52428800        512          1 YES
UNUSED                 1905993 20-SEP-21      1936695 21-SEP-21          0


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         2          1          0   52428800        512          1 YES
UNUSED                 1898210 20-SEP-21      1905993 20-SEP-21          0


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
progress


SQL>  alter database recover managed standby database cancel;

Database altered.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01666: control file is for a standby database


SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

On primary:

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      OPENING              26
ARCH      CONNECTED             0
ARCH      CLOSING              27
ARCH      OPENING              27
LNS       OPENING              27

On standby:

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
MRP0      WAIT_FOR_LOG         25

After the sequence match the primary scn, stop MRP or defer log_archive_dest_state_enable=false in primary and open the standby database.

Now you can drop the duplicate redo member !

SQL> startup;
ORACLE instance started.

Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             989859744 bytes
Database Buffers          503316480 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.
SQL> alter database drop logfile member '/apps01/base/oradata/dbx01/onlinelog/group_1.262.1083714911';

Database altered.
RMAN DUPLICATION METHOD TO CREATE STANDBY DATABASE IN ORACLE

Configure network files on both primary and standby database. Create a static listener both in primary and standby databases for consistent connectivity

Listener.ora

-bash-4.2$ cat /apps01/product/12.1.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /apps01/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl19x1)
      (ORACLE_HOME = /data01/product/19.0.0)
      (SID_NAME = orcl19x1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = dbx01)
      (ORACLE_HOME = /apps01/product/12.1.0/dbhome_1)
      (SID_NAME = dbx01)
    )
  )
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.123)(PORT = 1521))
  )

ADR_BASE_LISTENER = /apps01/base

Tnsnames.ora

DBX00 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.112)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbx00)
    )
  )
DBX01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.123)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbx01)(UR = A)
    )
  )

Verify the tnsping from both primary and standby database

Primary database:

[oracle@dc01x02 admin]$ tnsping DBX01

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 21-SEP-2021 05:14:22

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/apps01/12.1.0/grid/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.123)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DBX01)))
OK (10 msec)

Standby database:
-bash-4.2$ tnsping DBX00

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 21-SEP-2021 05:13:05

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/apps01/product/12.1.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.112)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbx00)))
OK (0 msec)

Set the password for sys account and scp the password file from primary to standby $ORACLE_HOME/dbs location

SQL> alter user sys identified by password;

[oracle@dc01x02 dbs]$ scp orapwdbx00_1 oracle@192.168.56.123:/apps01/product/12.1.0/dbhome_1/dbs
oracle@192.168.56.123's password:
orapwdbx00_1                                                                                         100%   18KB   4.3MB/s   00:00

Check space on standby database

-bash-4.2$ df -h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs        1.8G     0  1.8G   0% /dev
tmpfs           1.9G  835M 1012M  46% /dev/shm
tmpfs           1.9G  8.9M  1.8G   1% /run
tmpfs           1.9G     0  1.9G   0% /sys/fs/cgroup
/dev/sda2        20G   19G  735M  97% /
/dev/sda5        28G   26G  1.8G  94% /data01
/dev/sda1        30G   18G   13G  59% /apps01
tmpfs           370M   16K  370M   1% /run/user/42
tmpfs           370M     0  370M   0% /run/user/1001

Create a dummy pfile in standby database by copying the parameters from pfile of primary database.

Specify db_file_name_convert and log_file_name_convert , if source datafile and logfile location are different between primary and standby database. If multiple directories needs to be changed for each datafiles as per your size capacity in standby, then you have to specify set newname clause in the command.

Eg: Source datafile location : 12 datafiles from +DATA should be stored in destination datafile location : +DATA,+RECO,+REDO with each location containing 4 datafiles (3*4=12)

-bash-4.2$ cat /apps01/product/12.1.0/dbhome_1/dbs/initdbx01.ora
*.db_name='dbx00'
*.db_unique_name='dbx01'
*.db_recovery_file_dest='/apps01/FRA'
*.db_recovery_file_dest_size=5000m
*.diagnostic_dest='/apps01/base'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbx01XDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_DBX01'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1440m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='+DATAC1/DBX00','/apps01/base/oradata/dbx01'
*.log_file_name_convert='+DATAC1/DBX00','/apps01/base/oradata/dbx01'
*.compatible='12.1.0.2.0'

Start the standby database in nomount state using the pfile 

SQL> startup nomount pfile='/apps01/product/12.1.0/dbhome_1/dbs/initdbx01.ora';
ORACLE instance started.
Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             973082528 bytes
Database Buffers          520093696 bytes
Redo Buffers               13848576 bytes

Create the required directories from the source datafiles

-bash-4.2$ mkdir /apps01/base/oradata/dbx01/onlinelog/
-bash-4.2$ mkdir -p /apps01/base/oradata/dbx01/CC5E4A28ABF115B5E053CD38A8C001EC/datafile/
-bash-4.2$ mkdir -p /apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/
-bash-4.2$ mkdir -p /apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/
-bash-4.2$ mkdir -p /apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile

Start RMAN duplication from standby database. Connect to target as primary and auxiliary as standby database. As this is 12c version, backupset duplication method is used for duplication.

-bash-4.2$ rman target sys/password@dbx00 auxiliary sys/password@dbx01

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Sep 21 04:43:54 2021

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

connected to target database: DBX00 (DBID=2195240283)
connected to auxiliary database: DBX00 (not mounted)

RMAN> run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate auxiliary channel c4 device type disk;
duplicate target database for standby from active database using backupset section size 300M;
}2> 3> 4> 5> 6> 7> 8>

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=76 instance=dbx00_1 device type=DISK

allocated channel: c2
channel c2: SID=87 instance=dbx00_1 device type=DISK

allocated channel: c3
channel c3: SID=81 instance=dbx00_1 device type=DISK

allocated channel: c4
channel c4: SID=1 device type=DISK

Starting Duplicate Db at 21-SEP-21

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '+DATAC1/DBX00/PASSWORD/pwddbx00.256.1083714689' auxiliary format
 '/apps01/product/12.1.0/dbhome_1/dbs/orapwdbx01'   ;
}
executing Memory Script

Starting backup at 21-SEP-21
Finished backup at 21-SEP-21

contents of Memory Script:
{
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   restore clone from service  'dbx00' standby controlfile;
}
executing Memory Script

sql statement: create spfile from memory

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1509949440 bytes

Fixed Size                     2924640 bytes
Variable Size                989859744 bytes
Database Buffers             503316480 bytes
Redo Buffers                  13848576 bytes
allocated channel: c4
channel c4: SID=21 device type=DISK

Starting restore at 21-SEP-21

channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: restoring control file
channel c4: restore complete, elapsed time: 00:00:03
output file name=/apps01/FRA/DBX01/controlfile/o1_mf_jnl5gscz_.ctl
Finished restore at 21-SEP-21

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
 "/apps01/base/oradata/dbx01/tempfile/temp.258.1083714929";
   set newname for tempfile  2 to
 "/apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/pdbseed_temp012021-09-19_11-56-29-pm.dbf";
   set newname for tempfile  3 to
 "/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/tempfile/temp.276.1083716869";
   set newname for tempfile  4 to
 "/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/tempfile/temp.261.1083716969";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/apps01/base/oradata/dbx01/datafile/system.268.1083714775";
   set newname for datafile  3 to
 "/apps01/base/oradata/dbx01/datafile/sysaux.273.1083714719";
   set newname for datafile  4 to
 "/apps01/base/oradata/dbx01/datafile/undotbs1.266.1083714851";
   set newname for datafile  5 to
 "/apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/system.260.1083714939";
   set newname for datafile  6 to
 "/apps01/base/oradata/dbx01/datafile/users.257.1083714849";
   set newname for datafile  7 to
 "/apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/sysaux.267.1083714939";
   set newname for datafile  8 to
 "/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/system.270.1083716807";
   set newname for datafile  9 to
 "/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/sysaux.277.1083716807";
   set newname for datafile  10 to
 "/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/users.274.1083716893";
   set newname for datafile  11 to
 "/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/system.269.1083716919";
   set newname for datafile  12 to
 "/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/sysaux.272.1083716919";
   set newname for datafile  13 to
 "/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/users.278.1083717009";
   restore
   from service  'dbx00'   section size
 300 m   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /apps01/base/oradata/dbx01/tempfile/temp.258.1083714929 in control file
renamed tempfile 2 to /apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/pdbseed_temp012021-09-19_11-56-29-pm.dbf in control file
renamed tempfile 3 to /apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/tempfile/temp.276.1083716869 in control file
renamed tempfile 4 to /apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/tempfile/temp.261.1083716969 in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 21-SEP-21

skipping datafile 5; already restored to SCN 1744270
skipping datafile 7; already restored to SCN 1744270
skipping datafile 8; already restored to SCN 1857203
skipping datafile 9; already restored to SCN 1857203
skipping datafile 10; already restored to SCN 1857203
skipping datafile 11; already restored to SCN 1857214
skipping datafile 12; already restored to SCN 1857214
skipping datafile 13; already restored to SCN 1857214
channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00001 to /apps01/base/oradata/dbx01/datafile/system.268.1083714775
channel c4: restoring section 1 of 3
channel c4: restore complete, elapsed time: 00:00:36
channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00001 to /apps01/base/oradata/dbx01/datafile/system.268.1083714775
channel c4: restoring section 2 of 3
channel c4: restore complete, elapsed time: 00:00:26
channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00001 to /apps01/base/oradata/dbx01/datafile/system.268.1083714775
channel c4: restoring section 3 of 3
channel c4: restore complete, elapsed time: 00:00:26
channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00003 to /apps01/base/oradata/dbx01/datafile/sysaux.273.1083714719
channel c4: restoring section 1 of 3
channel c4: restore complete, elapsed time: 00:00:26
channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00003 to /apps01/base/oradata/dbx01/datafile/sysaux.273.1083714719
channel c4: restoring section 2 of 3
channel c4: restore complete, elapsed time: 00:00:36
channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00003 to /apps01/base/oradata/dbx01/datafile/sysaux.273.1083714719
channel c4: restoring section 3 of 3
channel c4: restore complete, elapsed time: 00:00:25
channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00004 to /apps01/base/oradata/dbx01/datafile/undotbs1.266.1083714851
channel c4: restoring section 1 of 1
channel c4: restore complete, elapsed time: 00:00:46
channel c4: starting datafile backup set restore
channel c4: using network backup set from service dbx00
channel c4: specifying datafile(s) to restore from backup set
channel c4: restoring datafile 00006 to /apps01/base/oradata/dbx01/datafile/users.257.1083714849
channel c4: restoring section 1 of 1
channel c4: restore complete, elapsed time: 00:00:26
Finished restore at 21-SEP-21

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=3 STAMP=1083818974 file name=/apps01/base/oradata/dbx01/datafile/system.268.1083714775
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=1083819034 file name=/apps01/base/oradata/dbx01/datafile/sysaux.273.1083714719
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/datafile/undotbs1.266.1083714851
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/system.260.1083714939
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/datafile/users.257.1083714849
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/sysaux.267.1083714939
datafile 8 switched to datafile copy
input datafile copy RECID=9 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/system.270.1083716807
datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/sysaux.277.1083716807
datafile 10 switched to datafile copy
input datafile copy RECID=11 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/cc5eb83e86c4281fe053cd38a8c0db1b/datafile/users.274.1083716893
datafile 11 switched to datafile copy
input datafile copy RECID=12 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/system.269.1083716919
datafile 12 switched to datafile copy
input datafile copy RECID=13 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/sysaux.272.1083716919
datafile 13 switched to datafile copy
input datafile copy RECID=14 STAMP=1083819095 file name=/apps01/base/oradata/dbx01/cc5ebf37a8122a8be053cd38a8c0efdf/datafile/users.278.1083717009
Finished Duplicate Db at 21-SEP-21
released channel: c1
released channel: c2
released channel: c3
released channel: c4
RMAN-03002: failure of Duplicate Db command at 06/18/2021 18:58:07-RMAN-05501: aborting duplication of target database-RMAN-03015: error occurred in stored script Memory Script-RMAN-06136: ORACLE error from auxiliary database: ORA-17629: Cannot connect to the remote database server-ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified-ORA-17629: Cannot connect to the remote database server

Cause:

RMAN duplication fails with the error stating tns unable to resolve error

RMAN> duplicate target database for standby from active database using backupset section size 300M nofilenamecheck;

Starting Duplicate Db at 18-JUN-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/oracle/base/product/12.1.0/dbhome_1/dbs/orapworcl' auxiliary format
 '/oracle/base/product/12.1.0/dbhome_1/dbs/orapworcldgp'   ;
}
executing Memory Script

Starting backup at 18-JUN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK
Finished backup at 18-JUN-21

contents of Memory Script:
{
   restore clone from service  'orcl' standby controlfile;
}
executing Memory Script

Starting restore at 18-JUN-21
using channel ORA_AUX_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/18/2021 18:58:07
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server

Workaround:

I connected and run duplication from auxiliary database instead of running duplicate from target database

RMAN-05503: at least one auxiliary channel must be allocated to execute this command

Cause:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/21/2021 02:31:10
RMAN-05501: aborting duplication of target database
RMAN-05503: at least one auxiliary channel must be allocated to execute this command
RMAN-05503: at least one auxiliary channel must be allocated to execute this command

Workaround:

Allocate atleast one auxiliary channel in duplicate command

RMAN> run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate auxiliary channel c4 device type disk;
2> 3> 4> duplicate target database for standby from active database using backupset section size 300M;
}
RMAN DUPLICATION 12C EXPLAINED IN ORACLE

Let us observe keenly on the step by step execution of rman duplication for database cloning.

Step 1: Connected the database to target and auxiliary

Step 2: Channels allocated from storage device (disk)

[oracle@orcl19x ~]$ rman target sys/password@orcl12x auxiliary sys/password@orcl19x

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Dec 25 19:33:39 2020

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

connected to target database: ORCL12X (DBID=1955551678)
connected to auxiliary database: ORCL19X (not mounted)

RMAN> run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate auxiliary channel ch3 type disk;
allocate auxiliary channel ch4 type disk;
allocate auxiliary channel ch5 type disk;
duplicate database to orcl19x from active database nofilenamecheck ;
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
release channel ch5;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14>

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

allocated channel: ch2
channel ch2: SID=46 device type=DISK

allocated channel: ch3
channel ch3: SID=13 device type=DISK

allocated channel: ch4
channel ch4: SID=173 device type=DISK

allocated channel: ch5
channel ch5: SID=14 device type=DISK

Step 3: We can observe a script with set of SQL commands.

  • First the db name and db unique name is set as source name.
  • Post that a clone instance is bounced
  • Controlfile restore is performed with the service name of source and database is also mounted.
Starting Duplicate Db at 25-DEC-20
current log archived

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ORCL12X'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''ORCL19X'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone from service  'orcl12x' primary controlfile;
   alter clone database mount;
}
executing Memory Script

Script is executed for clone as simple as that!

sql statement: alter system set  db_name =  ''ORCL12X'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''ORCL19X'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     234881024 bytes

Fixed Size                     2922904 bytes
Variable Size                176162408 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5464064 bytes
allocated channel: ch3
channel ch3: SID=12 device type=DISK
allocated channel: ch4
channel ch4: SID=173 device type=DISK
allocated channel: ch5
channel ch5: SID=14 device type=DISK

Starting restore at 25-DEC-20

channel ch3: starting datafile backup set restore
channel ch3: using network backup set from service orcl12x
channel ch3: restoring control file
channel ch3: restore complete, elapsed time: 00:00:02
output file name=/apps01/product/12.1.0/dbhome_1/dbs/cntrlorcl19x.dbf
Finished restore at 25-DEC-20

database mounted

After the database is mounted, again a new script is prepared automatically by RMAN. This script contains,

  • For all the datafiles, set newname command is generated and post that clone database is restored from service. Also a log switch is performed.
contents of Memory Script:
{
   set newname for datafile  1 to
 "/apps01/oradata/orcl19xsystem01.dbf";
   set newname for datafile  2 to
 "//apps01/oradata/orcl19x/EXAMPLE6345.dbf";
   set newname for datafile  3 to
 "/apps01/oradata/orcl19xsysaux01.dbf";
   set newname for datafile  4 to
 "/apps01/oradata/orcl19xundotbs01.dbf";
   set newname for datafile  5 to
 "/apps01/oradata/orcl19xexample01.dbf";
   set newname for datafile  6 to
 "/apps01/oradata/orcl19xusers01.dbf";
   set newname for datafile  7 to
 "//apps01/oradata/orcl19x/EXAMPLE12625.dbf";
   set newname for datafile  8 to
 "//apps01/oradata/orcl19x/SYSAUX18237.dbf";
   set newname for datafile  9 to
 "//apps01/oradata/orcl19x/SYSTEM9302.dbf";
   set newname for datafile  10 to
 "//apps01/oradata/orcl19x/EXAMPLE7187.dbf";
   restore
   from service  'orcl12x'   clone database
   ;
   sql 'alter system archive log current';
}

The clone database restore script is executed

executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 25-DEC-20

channel ch3: starting datafile backup set restore
channel ch3: using network backup set from service orcl12x
channel ch3: specifying datafile(s) to restore from backup set
channel ch3: restoring datafile 00001 to /apps01/oradata/orcl19xsystem01.dbf
channel ch4: starting datafile backup set restore
channel ch4: using network backup set from service orcl12x
channel ch4: specifying datafile(s) to restore from backup set
channel ch4: restoring datafile 00002 to //apps01/oradata/orcl19x/EXAMPLE6345.dbf
channel ch5: starting datafile backup set restore
channel ch5: using network backup set from service orcl12x
channel ch5: specifying datafile(s) to restore from backup set
channel ch5: restoring datafile 00003 to /apps01/oradata/orcl19xsysaux01.dbf
channel ch4: restore complete, elapsed time: 00:00:01
channel ch4: starting datafile backup set restore
channel ch4: using network backup set from service orcl12x
channel ch4: specifying datafile(s) to restore from backup set
channel ch4: restoring datafile 00004 to /apps01/oradata/orcl19xundotbs01.dbf
channel ch4: restore complete, elapsed time: 00:00:08
channel ch4: starting datafile backup set restore
channel ch4: using network backup set from service orcl12x
channel ch4: specifying datafile(s) to restore from backup set
channel ch4: restoring datafile 00005 to /apps01/oradata/orcl19xexample01.dbf
channel ch3: restore complete, elapsed time: 00:01:04
channel ch3: starting datafile backup set restore
channel ch3: using network backup set from service orcl12x
channel ch3: specifying datafile(s) to restore from backup set
channel ch3: restoring datafile 00006 to /apps01/oradata/orcl19xusers01.dbf
channel ch5: restore complete, elapsed time: 00:01:08
channel ch5: starting datafile backup set restore
channel ch5: using network backup set from service orcl12x
channel ch5: specifying datafile(s) to restore from backup set
channel ch5: restoring datafile 00007 to //apps01/oradata/orcl19x/EXAMPLE12625.dbf
channel ch5: restore complete, elapsed time: 00:00:01
channel ch5: starting datafile backup set restore
channel ch5: using network backup set from service orcl12x
channel ch5: specifying datafile(s) to restore from backup set
channel ch5: restoring datafile 00008 to //apps01/oradata/orcl19x/SYSAUX18237.dbf
channel ch5: restore complete, elapsed time: 00:00:01
channel ch5: starting datafile backup set restore
channel ch5: using network backup set from service orcl12x
channel ch5: specifying datafile(s) to restore from backup set
channel ch5: restoring datafile 00009 to //apps01/oradata/orcl19x/SYSTEM9302.dbf
channel ch5: restore complete, elapsed time: 00:00:01
channel ch5: starting datafile backup set restore
channel ch5: using network backup set from service orcl12x
channel ch5: specifying datafile(s) to restore from backup set
channel ch5: restoring datafile 00010 to //apps01/oradata/orcl19x/EXAMPLE7187.dbf
channel ch5: restore complete, elapsed time: 00:00:02
channel ch4: restore complete, elapsed time: 00:01:38
channel ch3: restore complete, elapsed time: 00:00:53
Finished restore at 25-DEC-20

Now its time to restore the archivelogs and switch the datafile name of clone database

  • Restore clone database from scn using service
  • Switch the clone datafiles 
sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  'orcl12x'
           archivelog from scn  9397504;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 25-DEC-20

channel ch3: starting archived log restore to default destination
channel ch3: using network backup set from service orcl12x
channel ch3: restoring archived log
archived log thread=1 sequence=94
channel ch4: starting archived log restore to default destination
channel ch4: using network backup set from service orcl12x
channel ch4: restoring archived log
archived log thread=1 sequence=95
channel ch3: restore complete, elapsed time: 00:00:01
channel ch4: restore complete, elapsed time: 00:00:01
Finished restore at 25-DEC-20

Datafile location is switched automatically or to say automagically 😉

datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=1060112186 file name=/apps01/oradata/orcl19xsystem01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=1060112186 file name=/apps01/oradata/orcl19x/EXAMPLE6345.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=1060112186 file name=/apps01/oradata/orcl19xsysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=1060112186 file name=/apps01/oradata/orcl19xundotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=15 STAMP=1060112186 file name=/apps01/oradata/orcl19xexample01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=16 STAMP=1060112186 file name=/apps01/oradata/orcl19xusers01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=17 STAMP=1060112187 file name=/apps01/oradata/orcl19x/EXAMPLE12625.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=18 STAMP=1060112187 file name=/apps01/oradata/orcl19x/SYSAUX18237.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=19 STAMP=1060112187 file name=/apps01/oradata/orcl19x/SYSTEM9302.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=20 STAMP=1060112187 file name=/apps01/oradata/orcl19x/EXAMPLE7187.dbf

Now database recovery is started for clone of database

  • set until clause is used with scn number
  • recover the database and delete the cloned archives
contents of Memory Script:
{
   set until scn  9397656;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 25-DEC-20

Media recovery starts

starting media recovery

archived log for thread 1 with sequence 94 is already on disk as file /apps01/product/12.1.0/dbhome_1/dbs/arch1_94_1058323394.dbf
archived log for thread 1 with sequence 95 is already on disk as file /apps01/product/12.1.0/dbhome_1/dbs/arch1_95_1058323394.dbf
archived log file name=/apps01/product/12.1.0/dbhome_1/dbs/arch1_94_1058323394.dbf thread=1 sequence=94
archived log file name=/apps01/product/12.1.0/dbhome_1/dbs/arch1_95_1058323394.dbf thread=1 sequence=95
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-DEC-20

Now,

  • Controlfile is created with reuse option and database name is changed in controlfile header
  • Datafile clone is cataloged
  • tempfiles are renamed
  • Database is opened with resetlogs
Oracle instance started

Total System Global Area     234881024 bytes

Fixed Size                     2922904 bytes
Variable Size                176162408 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5464064 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ORCL19X'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
}
executing Memory Script

sql statement: alter system set  db_name =  ''ORCL19X'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance started

Total System Global Area     234881024 bytes

Fixed Size                     2922904 bytes
Variable Size                176162408 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5464064 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL19X" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/apps01/oradata/orcl19xredo01.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '/apps01/oradata/orcl19xredo02.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '/apps01/oradata/orcl19xredo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/apps01/oradata/orcl19xsystem01.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/apps01/oradata/orcl19xtemp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/apps01/oradata/orcl19x/EXAMPLE6345.dbf",
 "/apps01/oradata/orcl19xsysaux01.dbf",
 "/apps01/oradata/orcl19xundotbs01.dbf",
 "/apps01/oradata/orcl19xexample01.dbf",
 "/apps01/oradata/orcl19xusers01.dbf",
 "/apps01/oradata/orcl19x/EXAMPLE12625.dbf",
 "/apps01/oradata/orcl19x/SYSAUX18237.dbf",
 "/apps01/oradata/orcl19x/SYSTEM9302.dbf",
 "/apps01/oradata/orcl19x/EXAMPLE7187.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /apps01/oradata/orcl19xtemp01.dbf in control file

cataloged datafile copy
datafile copy file name=/apps01/oradata/orcl19x/EXAMPLE6345.dbf RECID=1 STAMP=1060112210
cataloged datafile copy
datafile copy file name=/apps01/oradata/orcl19xsysaux01.dbf RECID=2 STAMP=1060112210
cataloged datafile copy
datafile copy file name=/apps01/oradata/orcl19xundotbs01.dbf RECID=3 STAMP=1060112210
cataloged datafile copy
datafile copy file name=/apps01/oradata/orcl19xexample01.dbf RECID=4 STAMP=1060112210
cataloged datafile copy
datafile copy file name=/apps01/oradata/orcl19xusers01.dbf RECID=5 STAMP=1060112210
cataloged datafile copy
datafile copy file name=/apps01/oradata/orcl19x/EXAMPLE12625.dbf RECID=6 STAMP=1060112210
cataloged datafile copy
datafile copy file name=/apps01/oradata/orcl19x/SYSAUX18237.dbf RECID=7 STAMP=1060112210
cataloged datafile copy
datafile copy file name=/apps01/oradata/orcl19x/SYSTEM9302.dbf RECID=8 STAMP=1060112210
cataloged datafile copy
datafile copy file name=/apps01/oradata/orcl19x/EXAMPLE7187.dbf RECID=9 STAMP=1060112210

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=1060112210 file name=/apps01/oradata/orcl19x/EXAMPLE6345.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1060112210 file name=/apps01/oradata/orcl19xsysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1060112210 file name=/apps01/oradata/orcl19xundotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=1060112210 file name=/apps01/oradata/orcl19xexample01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=1060112210 file name=/apps01/oradata/orcl19xusers01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=1060112210 file name=/apps01/oradata/orcl19x/EXAMPLE12625.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=1060112210 file name=/apps01/oradata/orcl19x/SYSAUX18237.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=1060112210 file name=/apps01/oradata/orcl19x/SYSTEM9302.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=1060112210 file name=/apps01/oradata/orcl19x/EXAMPLE7187.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 25-DEC-20

released channel: ch1

released channel: ch2
ASM DISKS ARE NOT VISIBLE IN GRID INSTALLATION 12C

Cause:

When i am installing grid software for clusterware, ASM disks are not visible.This looks pretty weird whenever i face this issue.

Workaround:

There are multiple reasons for the ASM disks to be not visible

Permission issue:

Grant the required permissions to the logical disks as per you project security policies

[root@dc01x01 ~]# ls -lHL /dev/oracleasm/
total 0
drwxrwxrwx 1 oracle oinstall 0 Sep 17 17:59 disks
drwxrwxrwx 1 oracle oinstall 0 Sep 17 17:59 iid

[root@dc01x01 ~]# ls -lHL /dev/oracleasm/disks/
total 0
brwxrwxrwx 1 oracle oinstall 8,  1 Sep 17 17:59 DATAC1
brwxrwxrwx 1 oracle oinstall 8, 17 Sep 17 17:59 DATAC2
brwxrwxrwx 1 oracle oinstall 8, 49 Sep 17 17:59 RECOC1
brwxrwxrwx 1 oracle oinstall 8, 33 Sep 17 17:59 RECOC2
brwxrwxrwx 1 oracle oinstall 8, 65 Sep 17 17:59 REDO01
brwxrwxrwx 1 oracle oinstall 8, 81 Sep 17 17:59 REDO02
brwxrwxrwx 1 oracle oinstall 8, 97 Sep 17 17:59 VODOCR

Give rw or rwx permission and proper ownership of the group in the disks

[root@dc01x01 ~]# chmod -R 777 /dev/
[root@dc01x01 ~]# chown -R oracle:oinstall /dev/oracleasm/

Disk path not specified properly:

Specify the right disk path for the oracleasm driver to read the ASM disk header.

Former disks:

If the ASM disk is already a former disk which was used and dropped previously, then wipe out the metadata information of the disk using dd command to reuse the disk for new installation

Protected: SHELL SCRIPT TO ADD DATAFILE TO TABLESPACE AUTOMATICALLY IN ORACLE

This content is password protected. To view it please enter your password below: