OEM Vulnerability : SSL Certificate Chain Contains RSA Keys Less Than 2048 bits

Increasing Key strength to 2048 from 1024 and signature algorithm of certificates with the OMS.

Check the OMS status

[em@OMSHOST805 bin]$ ./emctl status oms
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation. All rights reserved.
WebTier is Up
Oracle Management Server is Up
JVMD Engine is Up
BI Publisher Server is Up
[em@OMSHOST805 bin]$

Login to OMS using sysman user

[em@OMSHOST805 bin]$ ./emcli login -username=sysman
Enter password :

Get the current certificate details:

[em@OMSHOST805 bin]$ ./emcli get_ca_info -details

Info about CA with ID: 1
CA is not configured
Signature algorithm : sha512
Key strength : 1024
DN: CN=OMSHOST805.xxx.com, C=US, ST=CA, L=EnterpriseManager on OMSHOST805.xxx.com, OU=EnterpriseManager on OMSHOST805.xxx.com, O=EnterpriseManager on OMSHOST805.xxx.com
Serial# : 15353633977450860890
Valid From: Sun Dec 13 12:54:41 AST 2020
Valid Till: Thu Dec 12 12:54:41 AST 2030
Number of Agents registered with CA ID CA ID 1 is 31
xxxxxxxx
xxxxxxxx
xxxxxxxx

Run the command below to create a new Certificate Authority with 2048 bit keystrength:

[em@OMSHOST805 bin]$ ./emctl secure createca -key_strength 2048
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation. All rights reserved.
Creating CA… Started.
Enter Enterprise Manager Root (SYSMAN) Password :
Successfully created CA with ID 3
[em@OMSHOST805 bin]$

View the details of new Certificate Authority that was just created using the command below:

[em@OMSHOST805 bin]$ ./emcli get_ca_info -details

Info about CA with ID: 2
CA is not configured
Signature algorithm : sha512
Key strength : 2048
DN: CN=OMSHOST805.xxx.com, C=US, ST=CA, L=CA2, OU=EnterpriseManager on OMSHOST805.xxx.com, O=EnterpriseManager on OMSHOST805.xxx.com
Serial# : -7562746367185428360
Valid From: Tue Apr 06 14:59:29 AST 2021
Valid Till: Sat Apr 05 14:59:29 AST 2031
Number of Agents registered with CA ID CA ID 2 is 5
xxxxxxxx
xxxxxxxx
xxxxxxxx

Secure all the Agents so they will use a certificate with 2048 bit keystrength

em@s0client01:/em1/agent_inst/bin$ ./emctl secure agent
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation. All rights reserved.
Agent successfully stopped… Done.
Securing agent… Started.
Enter Agent Registration Password :
Agent successfully restarted… Done.
Securing agent… Successful.
em@s0client01:/em1/agent_inst/bin$

Secure the OMS after securing all the Agents

[em@OMSHOST805 bin]$ ./emctl secure oms -console -protocol TLSv1.2
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation. All rights reserved.
Securing OMS… Started.
Enter Enterprise Manager Root (SYSMAN) Password :
Enter Agent Registration Password :
[em@OMSHOST805 bin]$
[em@OMSHOST805 bin]$ ./emcli get_ca_info -details

Restart the OMS

[em@OMSHOST805 bin]$ ./emctl stop oms -all
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation. All rights reserved.
Stopping Oracle Management Server…
WebTier Successfully Stopped
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
JVMD Engine is Down
Stopping BI Publisher Server…
BI Publisher Server Successfully Stopped
AdminServer Successfully Stopped
BI Publisher Server is Down

[em@OMSHOST805 bin]$ ./emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 4
Copyright (c) 1996, 2020 Oracle Corporation. All rights reserved.
Starting Oracle Management Server…
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
JVMD Engine is Up
Starting BI Publisher Server …
BI Publisher Server Successfully Started
BI Publisher Server is Up
[em@OMSHOST805 bin]$

0 Comments Leave a comment

Replace/Change diskgroup for OCR and Voting disk on 12.1.0.2

grid@AP00D13902:/u01/app/12.1.0.2/gi_home/bin$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1568
Available space (kbytes) : 408000
ID : 838747642
Device/File Name : +OCR
Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check bypassed due to non-privileged user

grid@AP00D13902:/u01/app/12.1.0.2/gi_home/bin$

grid@AP00D14002:/u01/app/12.1.0.2/gi_home/bin$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
— —– —————– ——— ———
1. ONLINE 9cb1527c7e7e4f48bfe37a28e4ef87ec (/dev/rdsk/OCR1) [OCR]
2. ONLINE 13a5a9a7ed354f99bf7a2a0d69ee1316 (/dev/rdsk/OCR2) [OCR]
3. ONLINE f4210769933f4f8abf498a76701c835d (/dev/rdsk/OCR3) [OCR]
Located 3 voting disk(s).
grid@AP00D14002:/u01/app/12.1.0.2/gi_home/bin$

SQL> col DISK_FILE_PATH for a30
SQL> set line 2000
SQL> SELECT
2 NVL(a.name, ‘[CANDIDATE]’) disk_group_name
3 , b.path disk_file_path
4 , b.name disk_file_name
5 , b.failgroup disk_file_fail_group
6 FROM
7 v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
8 ORDER BY
9 a.name;

DISK_GROUP_NAME DISK_FILE_PATH DISK_FILE_NAME DISK_FILE_FAIL_GROUP
—————————— —————————— —————————— ——————————
ARCH /dev/rdsk/OCR
ARCH /dev/rdsk/ARC1
ARCH /dev/rdsk/OCR8
ARCH /dev/rdsk/DATA1
ARCH /dev/rdsk/OCR7
ARCH /dev/rdsk/OCR6
DATA /dev/rdsk/OCR8
DATA /dev/rdsk/DATA1
DATA /dev/rdsk/OCR
DATA /dev/rdsk/OCR6
DATA /dev/rdsk/OCR7

DISK_GROUP_NAME DISK_FILE_PATH DISK_FILE_NAME DISK_FILE_FAIL_GROUP
—————————— —————————— —————————— ——————————
DATA /dev/rdsk/ARC1
OCR /dev/rdsk/OCR1 OCR_0000 OCR_0000
OCR /dev/rdsk/OCR2 OCR_0001 OCR_0001
OCR /dev/rdsk/OCR3 OCR_0002 OCR_0002
OCR /dev/rdsk/OCR4 OCR_0003 OCR_0003
OCR /dev/rdsk/OCR5 OCR_0004 OCR_0004

17 rows selected.

SQL> SQL>
SQL>

SQL> CREATE DISKGROUP OCRVOT DISK ‘/dev/rdsk/OCR6′,’/dev/rdsk/OCR7′,’/dev/rdsk/OCR8’
ATTRIBUTE ‘compatible.asm’ = ‘12.1’, ‘compatible.rdbms’ = ‘11.2’,
‘compatible.advm’ = ‘12.1’; 2 3

Diskgroup created.

SQL> select name,state from v$asm_diskgroup;

NAME STATE
—————————— ———–
OCR MOUNTED
OCRVOT MOUNTED
DATA DISMOUNTED
ARCH DISMOUNTED

Steps to carry out
===================

root@AP00D13902:/u01/app/12.1.0.2/gi_home/bin# ./ocrconfig -add +OCRVOT

root@AP00D13902:/u01/app/12.1.0.2/gi_home/bin# ./ocrconfig -delete +OCR

root@AP00D13902:/u01/app/12.1.0.2/gi_home/bin# ./crsctl replace votedisk +OCRVOT

Successful addition of voting disk 5789834376434f9fbf811e9e6493e372.
Successful addition of voting disk c22ba913fb694fdabf7c0ae8c3733a42.
Successful addition of voting disk 5db2d1ea33704f82bfe458a9a01e8954.
Successful deletion of voting disk 9cb1527c7e7e4f48bfe37a28e4ef87ec.
Successful deletion of voting disk 13a5a9a7ed354f99bf7a2a0d69ee1316.
Successful deletion of voting disk f4210769933f4f8abf498a76701c835d.
Successfully replaced voting disk group with +OCRVOT.
CRS-4266: Voting file(s) successfully replaced
root@AP00D13902:/u01/app/12.1.0.2/gi_home/bin#

grid@AP00D14002:/u01/app/12.1.0.2/gi_home/bin$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1568
Available space (kbytes) : 408000
ID : 838747642
Device/File Name : +OCRVOT
Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check bypassed due to non-privileged user

grid@AP00D14002:/u01/app/12.1.0.2/gi_home/bin$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
— —– —————– ——— ———
1. ONLINE 5789834376434f9fbf811e9e6493e372 (/dev/rdsk/OCR6) [OCRVOT]
2. ONLINE c22ba913fb694fdabf7c0ae8c3733a42 (/dev/rdsk/OCR7) [OCRVOT]
3. ONLINE 5db2d1ea33704f82bfe458a9a01e8954 (/dev/rdsk/OCR8) [OCRVOT]
Located 3 voting disk(s).
grid@AP00D14002:/u01/app/12.1.0.2/gi_home/bin$
grid@AP00D14002:/u01/app/12.1.0.2/gi_home/bin$

grid@AP00D14002:/u01/app/12.1.0.2/gi_home/bin$ gpnptool get
Warning: some command line parameters were defaulted. Resulting command line:
/u01/app/12.1.0.2/gi_home/bin/gpnptool.bin get -o-

<orcl:ASM-Profile id="asm" DiscoveryString="" SPFile=”+OCR/test-clust/ASMPARAMETERFILE/registry.253.979636087″ Mode=”legacy”/> JrmnNYdmM2897uNjJVhHN2CJE9E=rNZDJAld5otVpywQJ0OAv/KVjzQWofMyDsA192BGLgGZsO+yicmTRk01/4LTADCSCMF+Q4T5aMI03zMB1dqt9ZgFqbrTGAEGYivP8O0PFcc63ExJ6uLoMilRN3hG+HNDo8gO09zAE1wNBDGjOjvW7uYGj6d23AQtgFQlxkHkoT0=
Success.
grid@AP00D14002:/u01/app/12.1.0.2/gi_home/bin$

SQL> create pfile=’/tmp/pfile.ora’ from spfile;

File created.

SQL>

SQL> create spfile=’+OCRVOT’ from pfile=’/tmp/pfile.ora’;

File created.

SQL>

grid@AP00D14002:/u01/app/12.1.0.2/gi_home/bin$ gpnptool get
Warning: some command line parameters were defaulted. Resulting command line:
/u01/app/12.1.0.2/gi_home/bin/gpnptool.bin get -o-

<orcl:ASM-Profile id="asm" DiscoveryString="" SPFile=”+OCRVOT/test-clust/ASMPARAMETERFILE/registry.253.979919131″ Mode=”legacy”/> /sHKVwcaE9tE+W1jDTCpc+Hd/ts=Jom3INB91bsrWNZQvws5+4WR7aH4TZcuTFUjTsWzgwO1DNDWmXAKo/9imhJB20QonAR7PMySABESXLsg1qIo7LdLKmfqipRPA6omBrArTKrQmS1WL1hqEGA63v/MQBMdpT2OStsaoIcR/2hykUdOOezyO2UZvkC2yS229mRavBg=
Success.
grid@AP00D14002:/u01/app/12.1.0.2/gi_home/bin$

0 Comments Leave a comment

RMAN Duplicate database using Backup

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2365587456 bytes
Fixed Size 2916728 bytes
Variable Size 754982536 bytes
Database Buffers 1593835520 bytes
Redo Buffers 13852672 bytes
SQL>

oracle@zdbcnt020102:/BKP/TMKSADV/RMANBKP_100718$ rman auxiliary /

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Jul 10 12:30:23 2018

connected to auxiliary database: tmkssit (not mounted)

RMAN> duplicate database to “tmkssit” nofilenamecheck
backup location ‘/BKP/TMKSADV/RMANBKP_100718’ NOREDO;2>

Starting Duplicate Db at 10-JUL-18

contents of Memory Script:
{
sql clone “alter system set db_name =
”TMKSADV” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”tmkssit” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from ‘/BKP/TMKSADV/RMANBKP_100718/TMKSADV_ctl_20180710_dbid4007731637_06t7ks0o_1_1.rman’;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ”TMKSADV” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set db_unique_name = ”tmkssit” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 2365587456 bytes

Fixed Size 2916728 bytes
Variable Size 754982536 bytes
Database Buffers 1593835520 bytes
Redo Buffers 13852672 bytes

Starting restore at 10-JUL-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=587 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=ATA_D10/tmkssit/CONTROLFILE/current01.ctl
output file name=+RCO_D10/tmkssit/CONTROLFILE/current02.ctl
Finished restore at 10-JUL-18

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=587 device type=DISK
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
set newname for datafile 1 to
“ATA_D10”;
set newname for datafile 2 to
“ATA_D10”;
set newname for datafile 3 to
“ATA_D10”;
set newname for datafile 4 to
“ATA_D10”;
set newname for datafile 5 to
“ATA_D10”;
set newname for datafile 6 to
“ATA_D10/tmkssit/datafile/st_tbs_01.dbf”;
set newname for datafile 7 to
“ATA_D10/tmkssit/datafile/txdb01.dbf”;
set newname for datafile 8 to
“ATA_D10/tmkssit/datafile/ortexsec_tbs_01.dbf”;
restore
clone database
;
}
executing Memory Script

executing command: SET NEWNAME

Starting restore at 10-JUL-18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to ATA_D10
channel ORA_AUX_DISK_1: restoring datafile 00002 to ATA_D10
channel ORA_AUX_DISK_1: restoring datafile 00006 to ATA_D10/tmkssit/datafile/st_tbs_01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /BKP/TMKSADV/RMANBKP_100718/TMKSADV_db_lvl0_20180710_03t7krok_1_1.rman
channel ORA_AUX_DISK_1: piece handle=/BKP/TMKSADV/RMANBKP_100718/TMKSADV_db_lvl0_20180710_03t7krok_1_1.rman tag=TMKSADV FULL DB/CONTROLFILE
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to ATA_D10
channel ORA_AUX_DISK_1: restoring datafile 00007 to ATA_D10/tmkssit/datafile/txdb01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /BKP/TMKSADV/RMANBKP_100718/TMKSADV_db_lvl0_20180710_01t7krok_1_1.rman
channel ORA_AUX_DISK_1: piece handle=/BKP/TMKSADV/RMANBKP_100718/TMKSADV_db_lvl0_20180710_01t7krok_1_1.rman tag=TMKSADV FULL DB/CONTROLFILE
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to ATA_D10
channel ORA_AUX_DISK_1: restoring datafile 00005 to ATA_D10
channel ORA_AUX_DISK_1: restoring datafile 00008 to ATA_D10/tmkssit/datafile/ortexsec_tbs_01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /BKP/TMKSADV/RMANBKP_100718/TMKSADV_db_lvl0_20180710_02t7krok_1_1.rman
channel ORA_AUX_DISK_1: piece handle=/BKP/TMKSADV/RMANBKP_100718/TMKSADV_db_lvl0_20180710_02t7krok_1_1.rman tag=TMKSADV FULL DB/CONTROLFILE
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 10-JUL-18

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

datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=981117446 file name=ATA_D10/tmkssit/DATAFILE/system.439.981117081
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=981117446 file name=ATA_D10/tmkssit/DATAFILE/sysaux.440.981117081
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=981117446 file name=ATA_D10/tmkssit/DATAFILE/undotbs1.437.981117167
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=981117446 file name=ATA_D10/tmkssit/DATAFILE/undotbs2.435.981117421
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=981117446 file name=ATA_D10/tmkssit/DATAFILE/users.434.981117421
datafile 6 switched to datafile copy
input datafile copy RECID=14 STAMP=981117446 file name=ATA_D10/tmkssit/datafile/st_tbs_01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=15 STAMP=981117446 file name=ATA_D10/tmkssit/datafile/txdb01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=16 STAMP=981117446 file name=ATA_D10/tmkssit/datafile/ortexsec_tbs_01.dbf

contents of Memory Script:
{
recover
clone database
noredo
delete archivelog
;
}
executing Memory Script

Starting recover at 10-JUL-18
using channel ORA_AUX_DISK_1

Finished recover at 10-JUL-18
Oracle instance started

Total System Global Area 2365587456 bytes

Fixed Size 2916728 bytes
Variable Size 754982536 bytes
Database Buffers 1593835520 bytes
Redo Buffers 13852672 bytes

contents of Memory Script:
{
sql clone “alter system set db_name =
”tmkssit” 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 = ”tmkssit” 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 2365587456 bytes

Fixed Size 2916728 bytes
Variable Size 754982536 bytes
Database Buffers 1593835520 bytes
Redo Buffers 13852672 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “tmkssit” RESETLOGS NOARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘ATA_D10’, ‘+RCO_D10’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘ATA_D10’, ‘+RCO_D10’ ) SIZE 50 M REUSE
DATAFILE
‘ATA_D10/tmkssit/DATAFILE/system.439.981117081’
CHARACTER SET AR8MSWIN1256

sql statement: ALTER DATABASE ADD LOGFILE

INSTANCE ‘i2’
GROUP 3 ( ‘ATA_D10’, ‘+RCO_D10’ ) SIZE 50 M REUSE,
GROUP 4 ( ‘ATA_D10’, ‘+RCO_D10’ ) SIZE 50 M REUSE

contents of Memory Script:
{
set newname for tempfile 1 to
“ATA_D10”;
switch clone tempfile all;
catalog clone datafilecopy “ATA_D10/tmkssit/DATAFILE/sysaux.440.981117081”,
“ATA_D10/tmkssit/DATAFILE/undotbs1.437.981117167”,
“ATA_D10/tmkssit/DATAFILE/undotbs2.435.981117421”,
“ATA_D10/tmkssit/DATAFILE/users.434.981117421”,
“ATA_D10/tmkssit/datafile/st_tbs_01.dbf”,
“ATA_D10/tmkssit/datafile/txdb01.dbf”,
“ATA_D10/tmkssit/datafile/ortexsec_tbs_01.dbf”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to ATA_D10 in control file

cataloged datafile copy
datafile copy file name=ATA_D10/tmkssit/DATAFILE/sysaux.440.981117081 RECID=1 STAMP=981117484
cataloged datafile copy
datafile copy file name=ATA_D10/tmkssit/DATAFILE/undotbs1.437.981117167 RECID=2 STAMP=981117484
cataloged datafile copy
datafile copy file name=ATA_D10/tmkssit/DATAFILE/undotbs2.435.981117421 RECID=3 STAMP=981117484
cataloged datafile copy
datafile copy file name=ATA_D10/tmkssit/DATAFILE/users.434.981117421 RECID=4 STAMP=981117484
cataloged datafile copy
datafile copy file name=ATA_D10/tmkssit/datafile/st_tbs_01.dbf RECID=5 STAMP=981117484
cataloged datafile copy
datafile copy file name=ATA_D10/tmkssit/datafile/txdb01.dbf RECID=6 STAMP=981117484
cataloged datafile copy
datafile copy file name=ATA_D10/tmkssit/datafile/ortexsec_tbs_01.dbf RECID=7 STAMP=981117484

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=981117484 file name=ATA_D10/tmkssit/DATAFILE/sysaux.440.981117081
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=981117484 file name=ATA_D10/tmkssit/DATAFILE/undotbs1.437.981117167
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=981117484 file name=ATA_D10/tmkssit/DATAFILE/undotbs2.435.981117421
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=981117484 file name=ATA_D10/tmkssit/DATAFILE/users.434.981117421
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=981117484 file name=ATA_D10/tmkssit/datafile/st_tbs_01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=981117484 file name=ATA_D10/tmkssit/datafile/txdb01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=981117484 file name=ATA_D10/tmkssit/datafile/ortexsec_tbs_01.dbf

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

database opened
Finished Duplicate Db at 10-JUL-18

RMAN> exit

Recovery Manager complete.
oracle@zdbcnt020102:/BKP/TMKSADV/RMANBKP_100718$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 10 12:44:41 2018

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

SQL>

SQL> create spfile from pfile;

File created.

SQL>

**Restart the database to pickup from spfile.

0 Comments Leave a comment

Manual Steps to Apply Patch on two node RAC 12.1.0.2 on SunOS 5.11 Patch # 21255373: CSSD : DUPLICATE RESPONSE IN GROUP DATA UPDATE

On NODE 01

1. Stop the CRS managed resources running from DB homes.
In GI home environment, as the database home owner execute:

$ $GI_HOME/bin/srvctl stop home -o /oradb/app/oracle/product/12.1.0/dbhome_1 -s /dbinfo/PR00D13902-dbstas -n PR00D13902
2. in GI Home, as the root user execute:

root@PR00D13902:/u01/app/12.1.0/grid_1# /u01/app/12.1.0/grid_1/crs/install/rootcrs.pl -prepatch Using configuration parameter file: /u01/app/12.1.0/grid_1/crs/install/crsconfig_params Oracle Clusterware active version on the cluster is [12.1.0.2.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [0]. CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'PR00d13902' CRS-2673: Attempting to stop 'ora.crsd' on 'PR00d13902' CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'PR00d13902' CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'PR00d13902' CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'PR00d13902' CRS-2673: Attempting to stop 'ora.OCR_VOT.dg' on 'PR00d13902' CRS-2677: Stop of 'ora.OCR_VOT.dg' on 'PR00d13902' succeeded CRS-2673: Attempting to stop 'ora.asm' on 'PR00d13902' CRS-2677: Stop of 'ora.asm' on 'PR00d13902' succeeded CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'PR00d13902' succeeded CRS-2673: Attempting to stop 'ora.PR00d13902.vip' on 'PR00d13902' CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'PR00d13902' succeeded CRS-2673: Attempting to stop 'ora.scan1.vip' on 'PR00d13902' CRS-2677: Stop of 'ora.PR00d13902.vip' on 'PR00d13902' succeeded CRS-2672: Attempting to start 'ora.PR00d13902.vip' on 'PR00d14002' CRS-2677: Stop of 'ora.scan1.vip' on 'PR00d13902' succeeded CRS-2672: Attempting to start 'ora.scan1.vip' on 'PR00d14002' CRS-2676: Start of 'ora.PR00d13902.vip' on 'PR00d14002' succeeded CRS-2676: Start of 'ora.scan1.vip' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'PR00d14002' CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'PR00d14002' succeeded CRS-2673: Attempting to stop 'ora.ons' on 'PR00d13902' CRS-2677: Stop of 'ora.ons' on 'PR00d13902' succeeded CRS-2673: Attempting to stop 'ora.net1.network' on 'PR00d13902' CRS-2677: Stop of 'ora.net1.network' on 'PR00d13902' succeeded CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'PR00d13902' has completed CRS-2677: Stop of 'ora.crsd' on 'PR00d13902' succeeded CRS-2673: Attempting to stop 'ora.ctssd' on 'PR00d13902' CRS-2673: Attempting to stop 'ora.evmd' on 'PR00d13902' CRS-2673: Attempting to stop 'ora.storage' on 'PR00d13902' CRS-2673: Attempting to stop 'ora.crf' on 'PR00d13902' CRS-2673: Attempting to stop 'ora.gpnpd' on 'PR00d13902' CRS-2673: Attempting to stop 'ora.mdnsd' on 'PR00d13902' CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'PR00d13902' CRS-2677: Stop of 'ora.storage' on 'PR00d13902' succeeded CRS-2673: Attempting to stop 'ora.asm' on 'PR00d13902' CRS-2677: Stop of 'ora.drivers.acfs' on 'PR00d13902' succeeded CRS-2677: Stop of 'ora.ctssd' on 'PR00d13902' succeeded CRS-2677: Stop of 'ora.evmd' on 'PR00d13902' succeeded CRS-2677: Stop of 'ora.crf' on 'PR00d13902' succeeded CRS-2677: Stop of 'ora.gpnpd' on 'PR00d13902' succeeded CRS-2677: Stop of 'ora.mdnsd' on 'PR00d13902' succeeded CRS-2677: Stop of 'ora.asm' on 'PR00d13902' succeeded CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'PR00d13902' CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'PR00d13902' succeeded CRS-2673: Attempting to stop 'ora.cssd' on 'PR00d13902' CRS-2677: Stop of 'ora.cssd' on 'PR00d13902' succeeded CRS-2673: Attempting to stop 'ora.gipcd' on 'PR00d13902' CRS-2677: Stop of 'ora.gipcd' on 'PR00d13902' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'PR00d13902' has completed CRS-4133: Oracle High Availability Services has been stopped. 2018/07/04 13:30:54 CLSRSC-4012: Shutting down Oracle Trace File Analyzer (TFA) Collector.


2018/07/04 13:31:28 CLSRSC-4013: Successfully shut down Oracle Trace File Analyzer (TFA) Collector.
2018/07/04 13:31:28 CLSRSC-347: Successfully unlock /u01/app/12.1.0/grid_1

root@PR00D13902:/u01/app/12.1.0/grid_1#

3. Check the patch Conflicts

grid@PR00D13902:~$ cd /u01/software/grid_soft/12.1/patch/ grid@PR00D13902:/u01/software/grid_soft/12.1/patch$ ls 21255373 p21255373_121020_SOLARIS64.zip p6880880_122010_SOLARIS64.zip grid@PR00D13902:/u01/software/grid_soft/12.1/patch$ cd 21255373 grid@PR00D13902:/u01/software/grid_soft/12.1/patch/21255373$ ls 21255373 automation bundle.xml README.html README.txt grid@PR00D13902:/u01/software/grid_soft/12.1/patch/21255373$ $GRID_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./21255373 Oracle Interim Patch Installer version 12.2.0.1.13 Copyright (c) 2018, Oracle Corporation. All rights reserved.


PREREQ session
Oracle Home       : /u01/app/12.1.0/grid_1

Central Inventory : /u01/app/oraInventory

   from           : /u01/app/12.1.0/grid_1/oraInst.loc

OPatch version    : 12.2.0.1.13

OUI version       : 12.1.0.2.0

Log file location : /u01/app/12.1.0/grid_1/cfgtoollogs/opatch/opatch2018-07-04_13-10-34PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.

grid@PR00D13902:/u01/software/grid_soft/12.1/patch/21255373$

grid@PR00D13902:~$

4. Patch GI home [As GI home owner]

grid@PR00D13902:~$ /u01/app/12.1.0/grid_1/OPatch/opatch apply -oh /u01/app/12.1.0/grid_1 -local /u01/software/grid_soft/12.1/patch/21255373/21255373 Oracle Interim Patch Installer version 12.2.0.1.13 Copyright (c) 2018, Oracle Corporation. All rights reserved.


Oracle Home       : /u01/app/12.1.0/grid_1

Central Inventory : /u01/app/oraInventory

   from           : /u01/app/12.1.0/grid_1/oraInst.loc

OPatch version    : 12.2.0.1.13

OUI version       : 12.1.0.2.0

Log file location : /u01/app/12.1.0/grid_1/cfgtoollogs/opatch/opatch2018-07-04_13-33-29PM_1.log
Verifying environment and performing prerequisite checks...

OPatch continues with these patches:   21255373
Do you want to proceed? [y|n]

y

User Responded with: Y

All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/app/12.1.0/grid_1')
Is the local system ready for patching? [y|n]

y

User Responded with: Y

Backing up files...

Applying interim patch '21255373' to OH '/u01/app/12.1.0/grid_1'
Patching component oracle.crs, 12.1.0.2.0...
Patching component oracle.has.db, 12.1.0.2.0...
Patching component oracle.has.common, 12.1.0.2.0...

Patch 21255373 successfully applied.

Log file location: /u01/app/12.1.0/grid_1/cfgtoollogs/opatch/opatch2018-07-04_13-33-29PM_1.log

OPatch succeeded. grid@PR00D13902:~$

5. Run the post script [As the root user]

root@PR00D13902:/u01/app/12.1.0/grid_1# /u01/app/12.1.0/grid_1/rdbms/install/rootadd_rdbms.sh root@PR00D13902:/u01/app/12.1.0/grid_1#


root@PR00D13902:/u01/app/12.1.0/grid_1# /u01/app/12.1.0/grid_1/crs/install/rootcrs.pl -postpatch

Using configuration parameter file: /u01/app/12.1.0/grid_1/crs/install/crsconfig_params

2018/07/04 14:13:44 CLSRSC-4015: Performing install or upgrade action for Oracle Trace File Analyzer (TFA) Collector.
2018/07/04 14:13:57 CLSRSC-4003: Successfully patched Oracle Trace File Analyzer (TFA) Collector.
CRS-4123: Starting Oracle High Availability Services-managed resources

CRS-2672: Attempting to start 'ora.mdnsd' on 'PR00d13902'

CRS-2672: Attempting to start 'ora.evmd' on 'PR00d13902'

CRS-2676: Start of 'ora.mdnsd' on 'PR00d13902' succeeded

CRS-2676: Start of 'ora.evmd' on 'PR00d13902' succeeded

CRS-2672: Attempting to start 'ora.gpnpd' on 'PR00d13902'

CRS-2676: Start of 'ora.gpnpd' on 'PR00d13902' succeeded

CRS-2672: Attempting to start 'ora.gipcd' on 'PR00d13902'

CRS-2676: Start of 'ora.gipcd' on 'PR00d13902' succeeded

CRS-2672: Attempting to start 'ora.cssdmonitor' on 'PR00d13902'

CRS-2676: Start of 'ora.cssdmonitor' on 'PR00d13902' succeeded

CRS-2672: Attempting to start 'ora.cssd' on 'PR00d13902'

CRS-2672: Attempting to start 'ora.diskmon' on 'PR00d13902'

CRS-2676: Start of 'ora.diskmon' on 'PR00d13902' succeeded

CRS-2676: Start of 'ora.cssd' on 'PR00d13902' succeeded

CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'PR00d13902'

CRS-2672: Attempting to start 'ora.ctssd' on 'PR00d13902'

CRS-2676: Start of 'ora.ctssd' on 'PR00d13902' succeeded

CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'PR00d13902' succeeded

CRS-2672: Attempting to start 'ora.asm' on 'PR00d13902'

CRS-2676: Start of 'ora.asm' on 'PR00d13902' succeeded

CRS-2672: Attempting to start 'ora.storage' on 'PR00d13902'

CRS-2676: Start of 'ora.storage' on 'PR00d13902' succeeded

CRS-2672: Attempting to start 'ora.crf' on 'PR00d13902'

CRS-2676: Start of 'ora.crf' on 'PR00d13902' succeeded

CRS-2672: Attempting to start 'ora.crsd' on 'PR00d13902'

CRS-2676: Start of 'ora.crsd' on 'PR00d13902' succeeded

CRS-6017: Processing resource auto-start for servers: PR00d13902

CRS-2672: Attempting to start 'ora.net1.network' on 'PR00d13902'

CRS-2676: Start of 'ora.net1.network' on 'PR00d13902' succeeded

CRS-2672: Attempting to start 'ora.ons' on 'PR00d13902'

CRS-2673: Attempting to stop 'ora.PR00d13902.vip' on 'PR00d14002'

CRS-2677: Stop of 'ora.PR00d13902.vip' on 'PR00d14002' succeeded

CRS-2672: Attempting to start 'ora.PR00d13902.vip' on 'PR00d13902'

CRS-2676: Start of 'ora.PR00d13902.vip' on 'PR00d13902' succeeded

CRS-2676: Start of 'ora.ons' on 'PR00d13902' succeeded

CRS-2672: Attempting to start 'ora.LISTENER.lsnr' on 'PR00d13902'

CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'PR00d14002'

CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'PR00d14002' succeeded

CRS-2673: Attempting to stop 'ora.scan1.vip' on 'PR00d14002'

CRS-2677: Stop of 'ora.scan1.vip' on 'PR00d14002' succeeded

CRS-2672: Attempting to start 'ora.scan1.vip' on 'PR00d13902'

CRS-2676: Start of 'ora.LISTENER.lsnr' on 'PR00d13902' succeeded

CRS-2676: Start of 'ora.scan1.vip' on 'PR00d13902' succeeded

CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'PR00d13902'

CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'PR00d13902' succeeded

CRS-6016: Resource auto-start has completed for server PR00d13902

CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources

CRS-4123: Oracle High Availability Services has been started.

Oracle Clusterware active version on the cluster is [12.1.0.2.0]. The cluster upgrade state is [ROLLING PATCH]. The cluster active patch level is [0].

SQL Patching tool version 12.2.0.0.0 on Wed Jul  4 14:17:16 2018

Copyright (c) 2014, Oracle.  All rights reserved.
Connecting to database...OK

Note:  Datapatch will only apply or rollback SQL fixes for PDBs

       that are in an open state, no patches will be applied to closed PDBs.

       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation

       (Doc ID 1585822.1)

Determining current state...done

Adding patches to installation queue and performing prereq checks...done

Installation queue:

  For the following PDBs: CDB$ROOT PDB$SEED TEST_CLUST

    Nothing to roll back

    Nothing to apply

SQL Patching tool complete on Wed Jul 4 14:17:30 2018 root@PR00D13902:/u01/app/12.1.0/grid_1#

6. Start the CRS managed resources that were running prior from DB homes.
In GI home environment, as the database home owner execute:

$ $GI_HOME/bin/srvctl start home -o /oradb/app/oracle/product/12.1.0/dbhome_1 -s /dbinfo/PR00D13902-dbstas -n PR00D13902

ON NODE 02</strong

7. Stop the CRS managed resources running from DB homes.
In GI home environment, as the database home owner execute:

$GI_HOME/bin/srvctl stop home -o /oradb/app/oracle/product/12.1.0/dbhome_1 -s /dbinfo/PR00D14002-dbstas -n PR00D14002

8. In GI Home, as the root user execute:

root@PR00D14002:/u01/app/12.1.0/grid_1/crs# /u01/app/12.1.0/grid_1/crs/install/rootcrs.pl -prepatch Using configuration parameter file: /u01/app/12.1.0/grid_1/crs/install/crsconfig_params Oracle Clusterware active version on the cluster is [12.1.0.2.0]. The cluster upgrade state is [ROLLING PATCH]. The cluster active patch level is [0]. CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'PR00d14002' CRS-2673: Attempting to stop 'ora.crsd' on 'PR00d14002' CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'PR00d14002' CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'PR00d14002' CRS-2673: Attempting to stop 'ora.cvu' on 'PR00d14002' CRS-2673: Attempting to stop 'ora.OCR_VOT.dg' on 'PR00d14002' CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'PR00d14002' CRS-2673: Attempting to stop 'ora.oc4j' on 'PR00d14002' CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'PR00d14002' CRS-2677: Stop of 'ora.cvu' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.cvu' on 'PR00d13902' CRS-2677: Stop of 'ora.OCR_VOT.dg' on 'PR00d14002' succeeded CRS-2673: Attempting to stop 'ora.asm' on 'PR00d14002' CRS-2676: Start of 'ora.cvu' on 'PR00d13902' succeeded CRS-2677: Stop of 'ora.asm' on 'PR00d14002' succeeded CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'PR00d14002' succeeded CRS-2673: Attempting to stop 'ora.scan3.vip' on 'PR00d14002' CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'PR00d14002' succeeded CRS-2673: Attempting to stop 'ora.PR00d14002.vip' on 'PR00d14002' CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'PR00d14002' succeeded CRS-2673: Attempting to stop 'ora.scan2.vip' on 'PR00d14002' CRS-2677: Stop of 'ora.scan3.vip' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.scan3.vip' on 'PR00d13902' CRS-2677: Stop of 'ora.scan2.vip' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.scan2.vip' on 'PR00d13902' CRS-2677: Stop of 'ora.PR00d14002.vip' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.PR00d14002.vip' on 'PR00d13902' CRS-2677: Stop of 'ora.oc4j' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.oc4j' on 'PR00d13902' CRS-2676: Start of 'ora.scan3.vip' on 'PR00d13902' succeeded CRS-2672: Attempting to start 'ora.LISTENER_SCAN3.lsnr' on 'PR00d13902' CRS-2676: Start of 'ora.scan2.vip' on 'PR00d13902' succeeded CRS-2672: Attempting to start 'ora.LISTENER_SCAN2.lsnr' on 'PR00d13902' CRS-2676: Start of 'ora.PR00d14002.vip' on 'PR00d13902' succeeded CRS-2676: Start of 'ora.LISTENER_SCAN3.lsnr' on 'PR00d13902' succeeded CRS-2676: Start of 'ora.LISTENER_SCAN2.lsnr' on 'PR00d13902' succeeded CRS-2676: Start of 'ora.oc4j' on 'PR00d13902' succeeded CRS-2673: Attempting to stop 'ora.ons' on 'PR00d14002' CRS-2677: Stop of 'ora.ons' on 'PR00d14002' succeeded CRS-2673: Attempting to stop 'ora.net1.network' on 'PR00d14002' CRS-2677: Stop of 'ora.net1.network' on 'PR00d14002' succeeded CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'PR00d14002' has completed CRS-2677: Stop of 'ora.crsd' on 'PR00d14002' succeeded CRS-2673: Attempting to stop 'ora.storage' on 'PR00d14002' CRS-2673: Attempting to stop 'ora.ctssd' on 'PR00d14002' CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'PR00d14002' CRS-2673: Attempting to stop 'ora.gpnpd' on 'PR00d14002' CRS-2673: Attempting to stop 'ora.mdnsd' on 'PR00d14002' CRS-2677: Stop of 'ora.storage' on 'PR00d14002' succeeded CRS-2677: Stop of 'ora.drivers.acfs' on 'PR00d14002' succeeded CRS-2673: Attempting to stop 'ora.asm' on 'PR00d14002' CRS-2677: Stop of 'ora.ctssd' on 'PR00d14002' succeeded CRS-2677: Stop of 'ora.gpnpd' on 'PR00d14002' succeeded CRS-2677: Stop of 'ora.mdnsd' on 'PR00d14002' succeeded CRS-2677: Stop of 'ora.asm' on 'PR00d14002' succeeded CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'PR00d14002' CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'PR00d14002' succeeded CRS-2673: Attempting to stop 'ora.crf' on 'PR00d14002' CRS-2673: Attempting to stop 'ora.evmd' on 'PR00d14002' CRS-2677: Stop of 'ora.crf' on 'PR00d14002' succeeded CRS-2677: Stop of 'ora.evmd' on 'PR00d14002' succeeded CRS-2673: Attempting to stop 'ora.cssd' on 'PR00d14002' CRS-2677: Stop of 'ora.cssd' on 'PR00d14002' succeeded CRS-2673: Attempting to stop 'ora.gipcd' on 'PR00d14002' CRS-2677: Stop of 'ora.gipcd' on 'PR00d14002' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'PR00d14002' has completed CRS-4133: Oracle High Availability Services has been stopped. 2018/07/04 15:04:13 CLSRSC-4012: Shutting down Oracle Trace File Analyzer (TFA) Collector.
2018/07/04 15:04:46 CLSRSC-4013: Successfully shut down Oracle Trace File Analyzer (TFA) Collector. 2018/07/04 15:04:46 CLSRSC-347: Successfully unlock /u01/app/12.1.0/grid_1
root@PR00D14002:/u01/app/12.1.0/grid_1/crs#

9. Patch GI home [As GI home owner]

grid@PR00D14002:~$ /u01/app/12.1.0/grid_1/OPatch/opatch apply -oh /u01/app/12.1.0/grid_1 -local /u01/software/grid_soft/12.1/patch/21255373/21255373 Oracle Interim Patch Installer version 12.2.0.1.13 Copyright (c) 2018, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/12.1.0/grid_1 Central Inventory : /u01/app/oraInventory from : /u01/app/12.1.0/grid_1/oraInst.loc OPatch version : 12.2.0.1.13 OUI version : 12.1.0.2.0 Log file location : /u01/app/12.1.0/grid_1/cfgtoollogs/opatch/opatch2018-07-04_15-05-12PM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 21255373 Do you want to proceed? [y|n] y User Responded with: Y All checks passed. Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/u01/app/12.1.0/grid_1') Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files... Applying interim patch '21255373' to OH '/u01/app/12.1.0/grid_1' Patching component oracle.crs, 12.1.0.2.0... Patching component oracle.has.db, 12.1.0.2.0... Patching component oracle.has.common, 12.1.0.2.0... Patch 21255373 successfully applied. Log file location: /u01/app/12.1.0/grid_1/cfgtoollogs/opatch/opatch2018-07-04_15-05-12PM_1.log
OPatch succeeded. grid@PR00D14002:~$

10. Run the post script [As the root user]

root@PR00D14002:/u01/app/12.1.0/grid_1/crs# /u01/app/12.1.0/grid_1/rdbms/install/rootadd_rdbms.sh root@PR00D14002:/u01/app/12.1.0/grid_1/crs#
root@PR00D14002:/u01/app/12.1.0/grid_1/crs# /u01/app/12.1.0/grid_1/crs/install/rootcrs.pl -postpatch Using configuration parameter file: /u01/app/12.1.0/grid_1/crs/install/crsconfig_params 2018/07/04 15:11:54 CLSRSC-4015: Performing install or upgrade action for Oracle Trace File Analyzer (TFA) Collector. 2018/07/04 15:12:07 CLSRSC-4003: Successfully patched Oracle Trace File Analyzer (TFA) Collector. CRS-4123: Starting Oracle High Availability Services-managed resources CRS-2672: Attempting to start 'ora.evmd' on 'PR00d14002' CRS-2672: Attempting to start 'ora.mdnsd' on 'PR00d14002' CRS-2676: Start of 'ora.mdnsd' on 'PR00d14002' succeeded CRS-2676: Start of 'ora.evmd' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.gpnpd' on 'PR00d14002' CRS-2676: Start of 'ora.gpnpd' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.gipcd' on 'PR00d14002' CRS-2676: Start of 'ora.gipcd' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.cssdmonitor' on 'PR00d14002' CRS-2676: Start of 'ora.cssdmonitor' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.cssd' on 'PR00d14002' CRS-2672: Attempting to start 'ora.diskmon' on 'PR00d14002' CRS-2676: Start of 'ora.diskmon' on 'PR00d14002' succeeded CRS-2676: Start of 'ora.cssd' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'PR00d14002' CRS-2672: Attempting to start 'ora.ctssd' on 'PR00d14002' CRS-2676: Start of 'ora.ctssd' on 'PR00d14002' succeeded CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.asm' on 'PR00d14002' CRS-2676: Start of 'ora.asm' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.storage' on 'PR00d14002' CRS-2676: Start of 'ora.storage' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.crf' on 'PR00d14002' CRS-2676: Start of 'ora.crf' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.crsd' on 'PR00d14002' CRS-2676: Start of 'ora.crsd' on 'PR00d14002' succeeded CRS-6023: Starting Oracle Cluster Ready Services-managed resources CRS-6017: Processing resource auto-start for servers: PR00d14002 CRS-2672: Attempting to start 'ora.net1.network' on 'PR00d14002' CRS-2676: Start of 'ora.net1.network' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.ons' on 'PR00d14002' CRS-2673: Attempting to stop 'ora.PR00d14002.vip' on 'PR00d13902' CRS-2677: Stop of 'ora.PR00d14002.vip' on 'PR00d13902' succeeded CRS-2672: Attempting to start 'ora.PR00d14002.vip' on 'PR00d14002' CRS-2676: Start of 'ora.PR00d14002.vip' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.LISTENER.lsnr' on 'PR00d14002' CRS-2676: Start of 'ora.ons' on 'PR00d14002' succeeded CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'PR00d13902' CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'PR00d13902' succeeded CRS-2673: Attempting to stop 'ora.scan1.vip' on 'PR00d13902' CRS-2677: Stop of 'ora.scan1.vip' on 'PR00d13902' succeeded CRS-2672: Attempting to start 'ora.scan1.vip' on 'PR00d14002' CRS-2676: Start of 'ora.LISTENER.lsnr' on 'PR00d14002' succeeded CRS-2676: Start of 'ora.scan1.vip' on 'PR00d14002' succeeded CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'PR00d14002' CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'PR00d14002' succeeded CRS-6016: Resource auto-start has completed for server PR00d14002 CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources CRS-4123: Oracle High Availability Services has been started. Oracle Clusterware active version on the cluster is [12.1.0.2.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [3999327575]. root@PR00D14002:/u01/app/12.1.0/grid_1/crs# root@PR00D14002:/u01/app/12.1.0/grid_1/crs#

11. Start the CRS managed resources that were running prior from DB homes.
In GI home environment, as the database home owner execute:

$GI_HOME/bin/srvctl start home -o /oradb/app/oracle/product/12.1.0/dbhome_1 -s /dbinfo/PR00D14002-dbstas -n PR00D14002

Note: This patch#21255373 is specific to binaries only and doesn’t have anything to be applied at the database level.

Read the patch readme to understand the patching instructions, general procedure to apply the database level patch 12.1.0 onwards is using the datapatch utility:

Following is the command:

oracle@:~$ export ORACLE_SID=testdb1
oracle@:~$ cd $ORACLE_HOME/OPatch
oracle@:/oradb/app/oracle/product/12.1.0.2/dbhome_1/OPatch$ ./datapatch -verbose

July 31, 2018
iammudasirhakak

Oracle Database

Applying April PSU Patch 27338041(12.1.0.2.180417) on 12.1.0.2 binary and database on solaris sparc

1. Download the patch using the following MOS document or click on the link below
Patch Set Updates for Oracle Products (Doc ID 854428.1)

Open the ReadMe and check the Prerequisites [OPatch Utility version] needed for the patch
In this case the patch I’m going to apply requires opatch version 12.1.0.1.7 or above
2. Check the opatch version available in the database

oracle@PR0D2021:~$ $ORACLE_HOME/OPatch/opatch version OPatch Version: 12.1.0.1.3
OPatch succeeded. oracle@PR0D2021:~$

The opatch version installed is bit lower than needed, therefore download the lattest opatch from Oracle Support.
3. Copy the patch and the opatch to the database host.
4. Shut down the database and listener

oracle@PR0D2021:~$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue May 29 12:45:55 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> oracle@PR0D2021:~$ lsnrctl stop LISTENER LSNRCTL for Solaris: Version 12.1.0.2.0 - Production on 29-MAY-2018 13:16:43 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PR0D2021)(PORT=2021))) The command completed successfully oracle@PR0D2021:~$

5. Move the existing OPatch directory and unzip the new opatch downloaded from oracle support.
oracle@PR0D2021:~$ cd $ORACLE_HOME oracle@PR0D2021:/oradb/app/oracle/product/12.1.0.2/dbhome_1$ mv OPatch Opatch.29may18 oracle@PR0D2021:/oradb/app/oracle/product/12.1.0.2/dbhome_1$
oracle@PR0D2021:/oradb/app/oracle/product/12.1.0.2/dbhome_1$ unzip /oradb/software p6880880_122010_SOLARIS64.zip oracle@PR0D2021:/oradb/app/oracle/product/12.1.0.2/dbhome_1$
6. Check the opatch version now
oracle@PR0D2021:~$ $ORACLE_HOME/OPatch/opatch version OPatch Version: 12.2.0.1.13
OPatch succeeded. oracle@PR0D2021:~$
Opatch version looks fine now, proceed to the next step of patch apply process.

7. Go to the location where patch software is copied in the database server
– Run the patch pre-requisites check and ensure there are no conflicts between the already applied patch es [Existing patches on the Home] and the new patch [Patch to be applied]
oracle@PR0D2021:~$ cd /oradb/software oracle@PR0D2021:/oradb/software$ oracle@PR0D2021:/oradb/software$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -invPtrLoc $ORACLE_HOME/oraInst.loc -phBaseDir ./27338041 Oracle Interim Patch Installer version 12.2.0.1.13 Copyright (c) 2018, Oracle Corporation. All rights reserved.
PREREQ session Oracle Home : /oradb/app/oracle/product/12.1.0.2/dbhome_1 Central Inventory : /oradb/oraInventory from : /oradb/app/oracle/product/12.1.0.2/dbhome_1/oraInst.loc OPatch version : 12.2.0.1.13 OUI version : 12.1.0.2.0 Log file location : /oradb/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/opatch/opatch2018-05-29_14-10-17PM_1.log Invoking prereq "checkconflictagainstohwithdetail" Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded. oracle@PR0D2021:/oradb/software$
Since there are no conflicts , we can proceed with the patching:
Note: If there are conflicts with the existing patches opatch will rollback the patches that are in conflict with the to-be-applied patch, In that case oracle support needs to be involved to see f there is any merge patch available and apply it once PSU patch apply is completed.

8. Start the patch apply on the database binary

oracle@PR0D2021:/oradb/software/27338041$ $ORACLE_HOME/OPatch/opatch apply Oracle Interim Patch Installer version 12.2.0.1.13 Copyright (c) 2018, Oracle Corporation. All rights reserved.
Oracle Home : /oradb/app/oracle/product/12.1.0.2/dbhome_1 Central Inventory : /oradb/oraInventory from : /oradb/app/oracle/product/12.1.0.2/dbhome_1/oraInst.loc OPatch version : 12.2.0.1.13 OUI version : 12.1.0.2.0 Log file location : /oradb/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/opatch/opatch2018-05-29_14-11-26PM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 19769480 20299023 20831110 21359755 21948354 22291127 23054246 24006101 24732082 25171037 25755742 26609783 26713565 26925311 27338041 Do you want to proceed? [y|n] y User Responded with: Y All checks passed. Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/oradb/app/oracle/product/12.1.0.2/dbhome_1') Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files... Applying sub-patch '19769480' to OH '/oradb/app/oracle/product/12.1.0.2/dbhome_1' Patching component oracle.rdbms.deconfig, 12.1.0.2.0... Patching component oracle.xdk, 12.1.0.2.0... Patching component oracle.tfa, 12.1.0.2.0... . . . OPatch succeeded.
oracle@PR0D2021:/oradb/software/27338041$

Patch apply completed on the binary, proceed to apply the patch on the database:

9. Apply the patch at the database level using datapatch
– Export the database sid
– Start the database first as it was brought down as part of patch apply at the binary level.

oracle@PR0D2021:~$ export ORACLE_SID=testdb oracle@PR0D2021:~$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue May 29 14:30:40 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 3154116608 bytes Fixed Size 3705416 bytes Variable Size 822089144 bytes Database Buffers 2315255808 bytes Redo Buffers 13066240 bytes Database mounted. Database opened. SQL> exit oracle@PR0D2021:~$ cd $ORACLE_HOME/OPatch oracle@PR0D2021:/oradb/app/oracle/product/12.1.0.2/dbhome_1/OPatch$ ./datapatch -verbose SQL Patching tool version 12.1.0.2.0 Production on Tue May 29 14:32:30 2018 Copyright (c) 2012, 2016, Oracle. All rights reserved. Log file for this invocation: /oradb/app/cfgtoollogs/sqlpatch/sqlpatch_6116_2018_05_29_14_32_30/sqlpatch_invocation.log Connecting to database...OK Bootstrapping registry and package to current versions...done Determining current state...done Current state of SQL patches: Bundle series PSU: ID 180417 in the binary registry and not installed in the SQL registry Adding patches to installation queue and performing prereq checks... Installation queue: Nothing to roll back The following patches will be applied: 27338041 (DATABASE PATCH SET UPDATE 12.1.0.2.180417) Installing patches... Patch installation complete. Total patches installed: 1
Validating logfiles... Patch 27338041 apply: SUCCESS logfile: /oradb/app/cfgtoollogs/sqlpatch/27338041/22040405/27338041_apply_UBS12C_2018May29_14_32_55.log (no errors) SQL Patching tool complete on Tue May 29 14:33:46 2018 oracle@PR0D2021:/oradb/app/oracle/product/12.1.0.2/dbhome_1/OPatch$

10. Start the listener

oracle@PR0D2021:~$ lsnrctl start LISTENER The command completed successfully oracle@PR0D2021:~$

**This completes the patching procedure**

0 Comments Leave a comment

July 31, 2018
iammudasirhakak

Oracle Database

Connecting to MSSQL from Oracle using Oracle Gateway…

Introduction:
Oracle offers Database Gateways for synchronously sharing information within an
enterprise. Synchronous access uses Oracle’s Distributed SQL features to
consolidate data on the fly, masking the location of data from the application or
user by making it appear as a local table.
Database Gateways provide the flexibility, power, and scalability to transparently
access any number of non-Oracle systems, including SQL Server, DB2, Teradata,
Sybase, IMS, VSAM, Adabas and WebSphere MQ from an Oracle environment.
Application developers can focus their efforts on developing business solutions
instead of trying to develop custom solutions to access non-Oracle systems.
As a result of the highly transparent nature of Database Gateways, access to both
local and remote data is achieved using Oracle SQL and procedure call interfaces,
even if the remote data is stored in multiple vendors’ databases. This transparency
eliminates the need for application developers to customize their applications to
access data from different non-Oracle systems, thus decreasing development efforts
and increasing the mobility of the application.

In this document following are the environment details used to configure gateway to access the Microsoft SQL database

Environment Details:

Gateway software download
Download the gateway software from Here .

Installation of Oracle Database gateway:

1. Copy the downloaded software to the server and invoke the run installer.
And follow the on-screen instructions.

2. Enter the oracle base and software location where the gateway software will be installed.

3. Select “Oracle Database Gateway for Microsoft Server”

4. Enter the SQL server details [Hostname, port, Database/instance name etc]

5. Press the install button.

6. Create a listener for the GATEWAY by following the screen or create the listener manually (shown below in this document)

7. Copy the root.sh script location and open a session with “root login” and execute root.sh

root@dbclient106:~# /u01/app/oracle/sqlgateway/root.sh Performing root user operation. The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /u01/app/oracle/sqlgateway Enter the full pathname of the local bin directory: [/usr/local/bin]: The contents of "dbhome" have not changed. No need to overwrite. The contents of "oraenv" have not changed. No need to overwrite. The contents of "coraenv" have not changed. No need to overwrite. Entries will be added to the /var/opt/oracle/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root script. Now product-specific root actions will be performed. root@dbclient106:~#

8. Go back to the installler screen and press the OK button

Post installation Gateway configuration:

9 . Manual Listener configuration for in Gateway.

Gateway home in this scenario is /u01/app/oracle/sqlgateway

cd /u01/app/oracle/product/sqlgateway/network/admin

listener.ora entry looks somewhat like this.

listener.ora file in the Gateway home looks like below. LISTENER_GATEWAY = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbclient106)(PORT = 3570)) ) ) SID_LIST_LISTENER_GATEWAY = (SID_LIST = (SID_DESC = (SID_NAME=Core) (ORACLE_HOME=/u01/app/oracle/sqlgateway) (ENV="LD_LIBRARY_PATH=/u01/app/oracle/sqlgateway/dg4msql/driver/lib:/u01/app/oracle/sqlgateway/lib") (PROGRAM=dg4msql) ) (SID_DESC = (SID_NAME=Core_t9) (ORACLE_HOME=/u01/app/oracle/sqlgateway) (ENV="LD_LIBRARY_PATH=/u01/app/oracle/sqlgateway/dg4msql/driver/lib:/u01/app/oracle/sqlgateway/lib") (PROGRAM=dg4msql) ) )

10. Create an init file under GATEWAY_HOME/dg4msql/admin

In this scenario SQL database name is EiCore so the init file name is initEiCore.ora

SQL Server Hostname = txdbc1105

SQL database/instance port= 58587

SQL database/instance name= EiCore

cd $GATEWAY_HOME/dg4msql/admin

initEiCore.ora would look something like this.

# HS init parameters # HS_FDS_CONNECT_INFO=[txdbc1105]:58587/EiCore # alternate connect format is hostname/serverinstance/databasename HS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER

11. Go to database home and update the tnsnames.ora with the below entry

Whereas EICORE is the SQL server database/instance name, 3570 is the gateway listener port and dbclient106 is the hostname where listener is running.

In this scenario ORACLE_HOME= /u01/app/oracle/product/12.1.0.2/dbhome_1

cd $ORACLE_HOME/network/admin

tnsnames.ora entry looks something like this.

EICORE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbclient106)(PORT = 3570)) (CONNECT_DATA = (SID = EiCore) ) (HS = OK) )

12. Restart the GATEWAY_LISTENER by setting the gateway home env variables.

]$ export ORACLE_HOME=/u01/app/oracle/product/sqlgateway ]$ export PATH=/u01/app/oracle/product/sqlgateway/bin:$PATH ]$ export TNS_ADMIN=/u01/app/oracle/product/sqlgateway/network/admin ]$ lsnrctl stop LISTENER_GATEWAY LSNRCTL for Solaris: Version 12.1.0.2.0 - Production on 16-JAN-2018 13:26:50 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbclient106)(PORT=3570))) The command completed successfully ]$

]$ lsnrctl start LISTENER_GATEWAY LSNRCTL for Solaris: Version 12.1.0.2.0 - Production on 16-JAN-2018 13:26:59 Copyright (c) 1991, 2014, Oracle. All rights reserved. Starting /u01/app/oracle/product/sqlgateway/bin/tnslsnr: please wait... TNSLSNR for Solaris: Version 12.1.0.2.0 - Production System parameter file is /u01/app/oracle/product/sqlgateway/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/dbclient106/listener_gateway/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbclient106)(PORT=3570))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbclient106)(PORT=3570))) STATUS of the LISTENER ------------------------ Alias                     LISTENER_GATEWAY Version                   TNSLSNR for Solaris: Version 12.1.0.2.0 - Production Start Date                16-JAN-2018 13:26:59 Uptime                    0 days 0 hr. 0 min. 0 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /u01/app/oracle/product/sqlgateway/network/admin/listener.ora Listener Log File         /u01/app/oracle/diag/tnslsnr/dbclient106/listener_gateway/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbclient106)(PORT=3570))) Services Summary... Service "EiCore" has 1 instance(s). Instance "EiCore", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@dbclient106 dbhome_1]$

13. Create DB link from oracle database to MS SQL database.

SQL> create database link EiCore_DB_Link connect to txuser identified by xxxxxxxx using ‘EiCore’;

Database link created.

Let’s now try to access the MS SQL table “test_tbl” from Oracle database.

SQL> select count(1) from test_tbl@EiCore; COUNT(1) ---------- 14

* Further more incase of RAC database.

I) Create a similar initEiCore.ora under GATEWAY_HOME/dg4msql/admin on the other nodes of the oracle RAC cluster.

II) Create a local listener on each node as mentioned above in step 9 [change the hostname accordingly]

III) Update the tnsnames.ora as mentioned in step 11 [change the hostname accordingly]

0 Comments Leave a comment

January 17, 2018
iammudasirhakak

Oracle Database

RMAN active database duplication usin 12c on the same database machine

RMAN active database duplication allows to create a duplicate database without using additional diskspace or tape for rman backups.

This Blog will demonstrate how to do active database duplication using RMAN.

Using RMAN, active database duplication can be done on the same database machine[where Source database and the new Target database share the same machine) or it can done on different database machines.

Source and Target ASM diskgroups/Filesystems and directory structures can be similar or different.

This blog will demonstrate RMAN active duplication on the same database machine.

Database/Machine Details are:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
Microsoft Windows 8.1
Source Database has database files under Drive “E:\” with Archive log enabled with fast recovery area (FRA). The destination database is also using Drive “E:\” as storage with a FRA but with Oracle managed files (OMF).

SQL> select name from v$datafile;

NAME
—————————————————————-
D:\INSTALLED_SOFTWARES\ORADATA\ACCOUNTS\ACCOUNTS\SYSTEM01.DBF
D:\INSTALLED_SOFTWARES\ORADATA\ACCOUNTS\ACCOUNTS\SYSAUX01.DBF
D:\INSTALLED_SOFTWARES\ORADATA\ACCOUNTS\ACCOUNTS\UNDOTBS01.DBF
D:\INSTALLED_SOFTWARES\ORADATA\ACCOUNTS\ACCOUNTS\USERS01.DBF

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Next log sequence to archive   12
Current log sequence           12
SQL>

Prerequisite

RMAN command is executed using the OS used who is part of DBA group
SQL statements are with SYSDBA privileges.
Source database must be in ARCHIVELOG mode.

SQL>select log_mode from v$database;

LOG_MODE
————
ARCHIVELOG

Source database DB_NAME is ACCOUNTS and the target database DB_NAME is FINANCE

Setup Oracle Net configuration files

RMAN active duplication is using the network and requires Oracle Net connections: so you must make sure that there is one Oracle Net alias for the source database and the target database in tnsnames.ora:

RMAN requires to connect to the auxiliary database started in NOMOUNT state, Hence listener.ora file needs to be updated with the static target database entry.

SID_LIST_LISTENER= (SID_LIST=     (SID_DESC=       (GLOBAL_DBNAME=FINANCE)       (ORACLE_HOME=D:\Installed_Softwares\product\12.1.0\dbhome_1)       (SID_NAME=FINANCE) )     (SID_DESC=       (GLOBAL_DBNAME=ACCOUNTS)       (ORACLE_HOME=D:\Installed_Softwares\product\12.1.0\dbhome_1)       (SID_NAME=ACCOUNTS)    ) )

LISTENER = (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = Leo)(PORT = 1539))       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1539))     ) )

Put the Target database entries in the tnsnames.ora

ACCOUNTS = (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = Leo)(PORT = 1539))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = accounts)     ) ) FINANCE = (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = Leo)(PORT = 1539))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = finance)     ) )

Password file must exist to allow remote SYS connections, Hence Create database password file using orapwd utility on the source database [Accounts].

e:\> orapwd file=%ORACLE_HOME%\database\orapwACCOUNTS password=oracle

Copy this password file to the target database machine and rename as per the target database name in this case [Finance].

Since this blog uses the same machine for source and target databases, Either make a copy of source (orapwACCOUNTS) password file and rename the copied file to orapwFINANCE or use the orapwd utility to create a password file for the target database [FINANCE]

e:\> orapwd file=%ORACLE_HOME%\database\orapwFINANCE password=oracle

Prepare file system

Create the directory structure for the target database [FINANCE] as
D:\INSTALLED_SOFTWARES\ORADATA\FINANCE

Create the PFILE for auxiliary instance under %ORACLE_HOME%\database\\initFINANCE.ora with a minimum with following content:
*.db_name=finance

Start the auxiliary instance in NOMOUNT mode

C:\Users\Leo> set ORACLE_SID=NEWDB
C:\Users\Leo>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 20:46:52 2016

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  3045168 bytes
Variable Size             838863056 bytes
Database Buffers          402653184 bytes
Redo Buffers               13729792 bytes
SQL>

create a rman script as DB_Act_duplicate.rman

duplicate target database to FINANCE from active database password file spfile set control_files 'D:\Installed_Softwares\oradata\finance\control01.ctl','D:\Installed_Softwares\oradata\finance\control02.ctl' set db_recovery_file_dest_size '20G' set db_recovery_file_dest 'D:\Installed_Softwares\oradata\finance\fra' set db_create_file_dest 'D:\Installed_Softwares\oradata\finance' set db_create_online_log_dest_1 'D:\Installed_Softwares\oradata\finance' set db_create_online_log_dest_2 'D:\Installed_Softwares\oradata\finance\fra' nofilenamecheck;

Start the database duplication as follows:

C:\Users\Leo> rman target sys/oracle@ACCOUNTS auxiliary sys/oracle@FINANCE cmdfile=C:\Dup_DB\DB_Act_duplicate.rman

Let the rman run and wait for its completion with the message “database opened”, Ensure there is no error and RMAN completes with below output:
[RMAN output has been trimed to keep the blog contents short]

contents of Memory Script: {    set newname for clone tempfile 1 to new;    switch clone tempfile all;    catalog clone datafilecopy "D:\INSTALLED_SOFTWARES\ORADATA\FINANCE\FINANCE\DATAFILE\O1_MF_SYSAUX_CRZLR176_.DBF", "D:\INSTALLED_SOFTWARES\ORADATA\FINANCE\FINANCE\DATAFILE\O1_MF_UNDOTBS1_CRZLRTMF_.DBF", "D:\INSTALLED_SOFTWARES\ORADATA\FINANCE\FINANCE\DATAFILE\O1_MF_USERS_CRZLS9S8_.DBF";    switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to D:\INSTALLED_SOFTWARES\ORADATA\FINANCE\FINANCE\DATAFILE\O1_MF_TEMP_%U_.TMP in control file cataloged datafile copy datafile copy file name=D:\INSTALLED_SOFTWARES\ORADATA\FINANCE\FINANCE\DATAFILE\O1_MF_SYSAUX_CRZLR176_.DBF RECID=1 STAMP=917733937 cataloged datafile copy datafile copy file name=D:\INSTALLED_SOFTWARES\ORADATA\FINANCE\FINANCE\DATAFILE\O1_MF_UNDOTBS1_CRZLRTMF_.DBF RECID=2 STAMP=917733937 cataloged datafile copy datafile copy file name=D:\INSTALLED_SOFTWARES\ORADATA\FINANCE\FINANCE\DATAFILE\O1_MF_USERS_CRZLS9S8_.DBF RECID=3 STAMP=917733937 datafile 3 switched to datafile copy input datafile copy RECID=1 STAMP=917733937 file name=D:\INSTALLED_SOFTWARES\ORADATA\FINANCE\FINANCE\DATAFILE\O1_MF_SYSAUX_CRZLR176_.DBF datafile 5 switched to datafile copy input datafile copy RECID=2 STAMP=917733937 file name=D:\INSTALLED_SOFTWARES\ORADATA\FINANCE\FINANCE\DATAFILE\O1_MF_UNDOTBS1_CRZLRTMF_.DBF datafile 6 switched to datafile copy input datafile copy RECID=3 STAMP=917733937 file name=D:\INSTALLED_SOFTWARES\ORADATA\FINANCE\FINANCE\DATAFILE\O1_MF_USERS_CRZLS9S8_.DBF contents of Memory Script: {    Alter clone database open resetlogs; } executing Memory Script database opened Finished Duplicate Db at 20-JUL-16 Recovery Manager complete. C:\Users\Leo>

=>Database duplication is completed and target database [FINANCE] is ready for use.

0 Comments Leave a comment

July 20, 2016
iammudasirhakak

Oracle Database