Container and Pluggable database Patching Process

Container and Pluggable database Patching steps in Oracle 12c

Note: Disable the backup job of RMAN Scheduler or rename the script.

1. Set ORACLE_HOME, PATH and ORACLE_SID

SET ORACLE_HOME=C:\oracle\12.1.0\dbhome_1
SET PATH=%ORACLE_HOME%\bin;%ORACLE_HOME%\opatch;%PATH%
SET ORACLE_SID=CDB
set PATH=%ORACLE_HOME%\perl\bin;%PATH%

2. Check the compatibility.
Go to patch location

Patch dircectory>opatch prereq CheckConflictAgainstOHWithDetail -ph .

F:\27162953>opatch prereq CheckConflictAgainstOHWithDetail -ph .
Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2018, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : C:\oracle\12.1.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
from : n/a
OPatch version : 12.1.0.1.3
OUI version : 12.1.0.2.0
Log file location : C:\oracle\12.1.0\dbhome_1\cfgtoollogs\opatch\opatch2018-02-1
5_00-52-40AM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.

3. Check the lsinventory

opatch lsinventory

There are no Interim patches installed in this Oracle Home.

4. Check the OPatch utility version 12.1.0.1.2 or later to apply this patch.

opatch version

F:\27162953>opatch version
OPatch Version: 12.1.0.1.3

OPatch succeeded.

5. Take a snapshot of services.msc of Oracle Services in Windows.
Note: it help to remember which services is started or stopped

6. Status of the listener

lsnrctl status

F:\27162953>lsnrctl status
LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 15-FEB-2018 00:55
:05
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Produ
ction
Start Date 15-FEB-2018 00:54:34
Uptime 0 days 0 hr. 0 min. 31 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File C:\oracle\diag\tnslsnr\WIN-R21P7VEDV43\listener\alert\
log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=WIN-R21P7VEDV43)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=WIN-R21P7VEDV43)(PORT=5500))(Securi
ty=(my_wallet_directory=C:\ORACLE\admin\CDB\xdb_wallet))(Presentation=HTTP)(Sess
ion=RAW))
Services Summary...
Service "CDB" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Service "CDBXDB" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Service "pdb3" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
The command completed successfully

7. Take the backup of IC Schema(optional)

run {
allocate channel ch00 type disk;
allocate channel ch01 type disk;
allocate channel ch02 type disk;
backup AS COMPRESSED BACKUPSET format 'C:\patchbackup\rman\full_db_%t_%sp%p' filesperset 5 database;
release channel ch00;
release channel ch01;
release channel ch02;
allocate channel ch00 type disk;
backup format 'C:\patchbackup\rman\cntrl_%s_%p_%t' CURRENT CONTROLFILE;
backup format 'C:\patchbackup\rman\spfile_%s_%p_%t' spfile;
release channel ch00;
}

8. Create PFILE from SPFILE

create pfile='C:\patchbackup\initpfile.ora' from spfile;

9. Check the status of all pluggable database which is open and closed

column name format a15
COLUMN "RESTRICTED" FORMAT A10
select d.con_id, v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
from v$PDBs v inner join dba_pdbs d using (GUID) order by v.create_scn;

    CON_ID NAME            OPEN_MODE  RESTRICTED STATUS
---------- --------------- ---------- ---------- --------
         2 PDB$SEED        READ ONLY  NO         NORMAL
         3 PDB1            READ WRITE NO         NORMAL
         4 PDB3            MOUNTED    n/a        NORMAL


10. Open all the pluggable database.

alter pluggable database all open;

column name format a15
COLUMN "RESTRICTED" FORMAT A10
select d.con_id, v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
from v$PDBs v inner join dba_pdbs d using (GUID) order by v.create_scn;

    CON_ID NAME            OPEN_MODE  RESTRICTED STATUS
---------- --------------- ---------- ---------- --------
         2 PDB$SEED        READ ONLY  NO         NORMAL
         3 PDB1            READ WRITE NO         NORMAL
         4 PDB3            READ WRITE NO        NORMAL


11. Check the invalid objects present in PDBs & CDB database

Note: CDB view only return value for open pdbs, so all pdb must be open.

select count(*) from cdb_objects where status='INVALID';

Select con_id, count(*) from cdb_objects where status='INVALID' group by con_id;

12. Check the SQL Patch view for patch history

Note: CDB view only return value for open pdbs, so all pdb must be open.

col action_time for a28
col action for a8
col version for a8
col status for a8
col comments for a30
set line 999 pages 999
select con_id, patch_id, version, status, Action, Action_time from cdb_registry_sqlpatch order by action_time;

13. Check the dba_registry components

col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select con_id,comp_id,comp_name,version,status from cdb_registry;

14. Check the database archive mode

archive log list;

15. Shutdown the CDB database and PDBs database

alter pluggable database all close immediate;

column name format a15
COLUMN "RESTRICTED" FORMAT A10
select d.con_id, v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
from v$PDBs v inner join dba_pdbs d using (GUID) order by v.create_scn;

shutdown immediate;

16. Take the services down from services.msc

17. Take the backup of Oracle home directory and Inventory

18. Apply the patch
Go to directory where patch is download from Oracle Support.
Unzip the patch and fire command to apply the patch

Patch directory>opatch apply

If you want to apply OJVM patch then apply the ojvm after window bundle patch:

Patch directory>opatch apply

19. Start the Oracle Service

-- Steps only for apply window bundle patch
startup
alter pluggable database all open;
OR
-- Steps apply for Window Pluse OJVM patch.
startup upgrade
alter pluggable database all open upgrade;

-- Check all pdb is in upgrade state
column name format a15
COLUMN "RESTRICTED" FORMAT A10
select d.con_id, v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status
from v$PDBs v inner join dba_pdbs d using (GUID) order by v.create_scn;

    CON_ID NAME            OPEN_MODE  RESTRICTED STATUS
---------- --------------- ---------- ---------- ---------
         2 PDB$SEED        MIGRATE    YES        NORMAL
         3 PDB1            MIGRATE    YES        NORMAL
         4 PDB3            MIGRATE    YES        NORMAL


20. Execute the datapatch verbose commands

cd %ORACLE_HOME%/OPatch
datapatch -verbose

---When you execute the command you got the following message: In our case all Pdb is in open state
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)
Bootstrapping registry and package to current versions...done
Determining current state...done

-- It will install patches on all cdb or pdb databases.
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB1 PDB3
Nothing to roll back
The following patches will be applied:
27162998 (WINDOWS ORACLE JAVAVM COMPONENT BUNDLE PATCH 12.1.0.2.180116 (64
bit): 27162998)
27162953 (WINDOWS DB BUNDLE PATCH 12.1.0.2.180116(64bit):27162953)

---Finally completed

Installing patches...
Patch installation complete. Total patches installed: 6

Validating logfiles...
Patch 27162998 apply (pdb CDB$ROOT): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\27162998\21845419/27162998_apply_CDB_C
DBROOT_2018Feb16_01_01_14.log (no errors)
Patch 27162953 apply (pdb CDB$ROOT): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\27162953\21845495/27162953_apply_CDB_C
DBROOT_2018Feb16_01_01_27.log (no errors)
Patch 27162998 apply (pdb PDB$SEED): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\27162998\21845419/27162998_apply_CDB_P
DBSEED_2018Feb16_01_04_55.log (no errors)
Patch 27162953 apply (pdb PDB$SEED): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\27162953\21845495/27162953_apply_CDB_P
DBSEED_2018Feb16_01_05_08.log (no errors)
Patch 27162998 apply (pdb PDB1): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\27162998\21845419/27162998_apply_CDB_P
DB1_2018Feb16_01_05_12.log (no errors)
Patch 27162953 apply (pdb PDB1): SUCCESS
logfile: C:\oracle\cfgtoollogs\sqlpatch\27162953\21845495/27162953_apply_CDB_P
DB1_2018Feb16_01_05_35.log (no errors)
SQL Patching tool complete on Fri Feb 16 01:16:22 2018

21. Check the log files for catbundle at following locations

%ORACLE_HOME%\cfgtoollogs\catbundle or %ORACLE_BASE%\cfgtoollogs\catbundle for any errors:

22. Open the CDB & PDB’s database in normal mode:

--CDB
Shutdown immediate
startup

--PDB
alter pluggable database all open;

23. If no warning in opening PDBS then go to next Step
Note: Warning may occurred during opening of PDB which may be closed at time of database verbose command

SQL> alter pluggable database all open;
Warning: PDB altered with errors.

column name format a15
COLUMN "RESTRICTED" FORMAT A10
select d.con_id, v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED"
, d.status from v$PDBs v inner join dba_pdbs d using (GUID) order by v.create_scn;

    CON_ID NAME            OPEN_MODE  RESTRICTED STATUS
---------- --------------- ---------- ---------- ---------
         2 PDB$SEED        READ ONLY  NO         NORMAL
         3 PDB1            READ WRITE NO         NORMAL
         4 PDB3            READ WRITE YES        NORMAL

Note: PDB3 is open in restricted mode

We can check with following view about error or action need to take:
select message,action from pdb_plug_in_violations;

Message Action
--------------------------------------------------------------------------------
PSU bundle patch 180116 (WINDOWS DB BUNDLE PATCH 12.1.0.2.180116(64bit):27162953
): Installed in the CDB but not in the PDB.
Call datapatch to install in the PDB or the CDB
SQL patch ID/UID 27162998/21845419 (WINDOWS ORACLE JAVAVM COMPONENT BUNDLE PATCH
12.1.0.2.180116 (64bit): 27162998): Installed in the CDB but not in the PDB.
Call datapatch to install in the PDB or the CDB

Note: Action output is in bold statement asking to rerun the datapatch verbose command for PDB3 database.
Action Taken for fixed pluggable database issue
1. Open the PDB3 in upgrade mode
2. Rerun the datapatch -verbose commands
3. open the PDB3 in normal mode.

24. Check the invalid objects in all PDBs & CDB and compile invalid objects

Note: CDB view only return value for open PDBs, so all PDBs must be open.
cd %ORACLE_HOME%\rdbms\admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql

select count(*) from cdb_objects where status='INVALID';
Select con_id, count(*) from cdb_objects where status='INVALID' group by con_id;

Note: From CDB execution it will fixed all CDB invalid objects, For pdb run it from any one pdb it will fixed all invalid objects of PDBs.

25. Check the database registry view

Note: CDB view only return value for open PDBs, so all PDBs must be open.

col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select con_id,comp_id,comp_name,version,status from cdb_registry;

26. Check the registry$history view

Note: CDB view only return value for open PDBs, so all PDBs must be open.

col action_time for a28
col action for a8
col version for a8
col status for a8
col comments for a30
set line 999 pages 999
select con_id, patch_id, version, status, Action, Action_time from cdb_registry_sqlpatch order by action_time;

select patch_id, patch_uid, version, status, description from cdb_registry_sqlpatch;
select patch_id, version, status, Action, Action_time from cdb_registry_sqlpatch order by action_time;

27. Check the services of oracle and windows is started.

28. Check the listener connectivity

sqlplus sys@cdb as sysdba
sqlplus sys@pdb1 as sysdba
sqlplus sys@pdb2 as sysdba

29. Check the Opatch lsinventor

30. Listener status checked blocked or open

lsnrctl status

Advertisements

Rename a datafile in Dataguard environment

Rename a datafile in dataguard environment

If you want to rename a data file on a standby database, you need to do it manually:

1. Cancel the Data Guard recovery (set dg_broker_start to false and restart the database in mount status if you use dataguard broker service).
2. Set the standby file management to manual
alter system set standby_file_management=manual.
3. Move the file with OS commands, just as you did on the primary database.
4. Rename the file with
alter database rename file ‘old’ to ‘new’.
Re-enable the automatic standby file management and dg broker and restart the recovery (start db in mount status if you use data guard)

Following are the steps:
On Primary:
1. Check the database role for verified it is primary server

select name,db_unique_name,database_role,switchover_status from v$database;

select database_role,switchover_status from v$database;

2. Change the parameter standby file management from auto to manual

show parameter STANDBY_FILE_MANAGEMENT

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

On Standby:
3. Check the database role and verified it is standby

select name,db_unique_name,database_role,switchover_status from v$database;

select database_role,switchover_status from v$database;

4. Check the Status of archive applied and data guard in sync

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

5. Change the parameter in Standby

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

On PRIMARY:
6. Check the file name and location which need to rename

select file_name from dba_data_files where tablespace_name='EXAMPLE';

7. Shutdown the primary database and move the file for rename or rename at same location

shutdown immediate

mv /u02/oradata/orcl/example01.dbf /u03/oradata/orcl/example01_temp.dbf

8. Startup the database at mount state and update the control file with new location or name.

Startup mount

alter tablespace example rename datafile '/u02/oradata/orcl/example01.dbf' to '/u03/oradata/orcl/example01_temp.dbf';

On Standby:
9. Check the location of file

select ts#,name from v$tablespace where name='EXAMPLE';

select name from v$datafile where ts#=6;

10. Cancel the recover of standby Server and clean shutdown the database.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

shutdown immediate

11. Move the datafile to new location or new name with OS commands.

mv /u02/oradata/orcl/example01.dbf /u03/oradata/orcl/example01_temp.dbf

12. Start the standby database at mount state and update control file.

startup mount

alter tablespace example rename datafile '/u02/oradata/orcl/example01.dbf' to '/u03/oradata/orcl/example01_temp.dbf';

13. Verify the location at standby

select name from v$datafile where ts#=6;

14. Start the standby recover process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

15. Make both the primary and standby parameter to auto

Primary:
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Standby :
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Oracle home is having large size due to Patching

Patch Storage Folder is having large size due to Oracle Patching

The Size of Oracle Home is increasing day by day. On checking the size of subfolder we found that patch_storage folder is consuming large amount of space.

Following is the location of Patch Storage folder:

$ORACLE_HOME/.patch_storage

First Option:(opatch util cleanup)
opatch util cleanup to reclaim space from $ORACLE_HOME/.patch_storage
Opatch util has the option for cleaning the patch storage folder. You can check with following commands:

opatch util -help

Clean the patch storage folder with following command:

opatch util cleanup

Note: After this command you can rollback patch, it does not affect.

Note: Command delete few unnecessary files but it does not help to release a lot of space. It only release space in KB’s.

Second option:(Manually Process)
$ORACLE_HOME/.patch_storage is having all the directories of previous version present. Cleaning can be done manually as follows:

1. Run command:

$ opatch lsinventory

2. Get the list of opatch number which present on inventory.

3. Remove the extra directories present in $ORACLE_HOME/.patch_storage that are not present in the list of installed patches in step 1.

Directory names would be prefaced with the patchid for example:

24453456_

Note: Delete the old patches which is not present in opatch lsinventory manually by Operating system commands

Configure Dnsmasq for Oracle Linux for RAC scan address Configured

Configure Dnsmasq for Oracle Linux for RAC scan address Configured

1. Install the DNSMASQ on Oracle Linux

yum install dnsmasq

2. Configured if for automatic start on Linux reboot

chkconfig dnsmasq on

3. For configuring for Setup SCAN address for RAC Instances:
Dnsmasq is configured by altering the file “/etc/dnsmasq.conf” and “/etc/hosts”.
We make entries in /etc/hosts file for configuring the IP addresses used by RAC Environment.

cat /etc/hosts
# Public
192.168.56.101 rac1.localdomain rac1
192.168.56.102 rac2.localdomain rac2
# Private
192.168.1.101 rac1-priv.localdomain rac1-priv
192.168.1.102 rac2-priv.localdomain rac2-priv
# Virtual
192.168.56.103 rac1-vip.localdomain rac1-vip
192.168.56.104 rac2-vip.localdomain rac2-vip
# SCAN
192.168.56.105 rac-scan.localdomain rac-scan
192.168.56.106 rac-scan.localdomain rac-scan
192.168.56.107 rac-scan.localdomain rac-scan

4. Now configure the /etc/resolv.conf file.

cat /etc/resolv.conf
#domain localdomain
search localdomain
nameserver 192.168.56.1

5. Edit the /etc/dnsmasq.conf file:

local=/localdomain/

6. Start the dnsmasq process

service dnsmasq stop
service dnsmasq start
service dnsmasq restart

7. Test by nslookup commands as follows:

nslookup rac-scan
;; connection timed out; no servers could be reached

8. If nslookup gives timeout error then you need to modify the vi/etc/resolv.conf file.
Add one entry of local address because dnsmasq is running by default on 127.0.0.1 address

cat /etc/resolv.conf
#domain localdomain
nameserver 127.0.0.1
search localdomain
nameserver 192.168.56.1

9. Protect and Restart the dnsmasq process.

--Changes its attribute for changing in reboot.
chattr +i /etc/resolv.conf
-- Start the Service.
service dnsmasq restart

10. Check again the nslookup command, it works.

nslookup rac-scan
[root@rac1 ~]# nslookup rac-scan
Server: 127.0.0.1
Address: 127.0.0.1#53

Name: rac-scan.localdomain
Address: 192.168.56.105
Name: rac-scan.localdomain
Address: 192.168.56.106
Name: rac-scan.localdomain
Address: 192.168.56.107

In upper case it is using the /etc/hosts file for resolve the name, if you want to configured own file instead for resolved the scan ip address in DNSMASQ

1. Install the dnsmasq and configured it for next reboot

yum install dnsmasq
chkconfig dnsmasq on

2. Create the new file having entry of SCAN ip address

cat /etc/scanipaddress
# SCAN
192.168.56.105 rac-scan.localdomain rac-scan
192.168.56.106 rac-scan.localdomain rac-scan
192.168.56.107 rac-scan.localdomain rac-scan

3. Modify one parameter addn-hosts in default configuration file ‘/etc/dnsmasq.conf’.

cat /etc/dnsmasq.conf | grep addn-hosts
addn-hosts=/etc/scanipaddress

4. Configure resolv.conf file as above steps:

cat /etc/resolv.conf
#domain localdomain
nameserver 127.0.0.1
search localdomain
nameserver 192.168.56.1

5. Protect file from overwritten by host reboot

[root@rac1 ~]# chattr +i /etc/resolv.conf

6. Start the Service of dnsmasq and check nslookup command

service dnsmasq restart
nslookup rac-scan

Configure yum manually on Oracle Linux Platform from Media

Configure yum manually on Oracle Linux Platform from Media

I am using Oracle Virtual box in which i installed oracle Linux platform.

Following are steps to configure yum manually through Media or ISO image:

Step 1: Transfer the complete iso image of Linux to you local drive.
Note: I am using linux 7.4 so making naming conventions as 7.4 for all folder below. So change according to your need.

mkdir /ISOs
cp /media/USB_Sticks/Linux7-4.iso /ISOs

Step 2. Create Folder for the mounting the DVD image on it:

mkdir -p /var/OSimage/OL7.4_x86_64

mount -o loop,ro /ISOs/Linux7-4.iso /var/OSimage/OL7.4_x86_64

Yum1

Step 3: On Restart, we need to fire command again in step 2. Avoid it make an entry in /etc/fstab

vi /etc/fstab
-- Insert entry into file /etc/fstab
/ISOs/Linux7-4.iso /var/OSimage/OL7.4_x86_64 iso9660 loop,ro 0 0

Step 4: In /etc/yum.repos.d directory, edit the existing file present public-yum-ol74.repo and disable all entries by setting enabled=0.

vi public-yum-ol74.repo
-- Make all the enabled parameter to value 0

Step 5: Create the new entries in a new repository file

For example, /etc/yum.repos.d/OL74.repo

-- Create new file
vi /etc/yum.repos.d/OL74.repo

-- Insert following entries in file and take care of fist parameter [OL74] as naming of the file name.
[OL74]
name=Oracle Linux 7.4 x86_64
baseurl=file:///var/OSimage/OL7.4_x86_64
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY
gpgcheck=1
enabled=1

Step 6: Clean up the yum cache from the system.

yum clean all

yum-clean-all

Step 7: Check yum to access the repository.

yum repolist

yum-repolist

Step 8: Verify yum is working properly.

yum list
yum install gcc

Copy & Paste and Shared folder between Oracle VirtualBox Host And Guest Machines

Copy & Paste and Shared folder between Oracle VirtualBox Host And Guest Machines

Following are the steps for configure the machine:

1. Open the Oracle Virtual Box Software.

2. Go to tab Setting for virtual machine or right click on specific virtual machine go to setting.

vm1

3. A Setting windows for virtual machine is open then go to general tab of left panel and select advanced tab on right panel.

vm2

4. Choose bidirectional option on both fields for enable the copy & paste option.

VM3.JPG

5. For Shared Folder Configuration select Shared Folder in left panel on Same setting windows.

Vm6

6. Then add the folder from right side + button and give it permission as needed.
Choose the path which need to shared from Host machine and assign permission or auto-mount option as shown below:

Shared Folder2.JPG

7. Then Start the Virtual Machine and you will see the upper option in virtual machine.
Note: If you are in Scaled Mode in View option you did not see upper tabs. Then Go to Virtual Machine(Guest Machine) press host key + Home ( Default Right CTRL + HOME ) then you will see the view option and click on Scaled or Full version.

Vm7.JPG

8. Go to the Devices tab and Select install Guest additional CD.

vm8.JPG

9. I am using Linux machine, So, Get the following popup to run it.

VM10

10. After successfully installation, Restart the Guest Virtual Machine. Then you got both copy & paste and Shared folder on your desktop.