Oracle Technology Stack

Friday, March 13, 2020

Steps to Add ASM disk to diskgroup

1. Check existing diskgroup details and redundancy

[oracle@orahost01 ~]$ . oraenv
ORACLE_SID = [+ASM] ?
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@orahost01 ~]$ asmcmd

ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 1817119 499770 0 499770 0 N DATA/
MOUNTED NORMAL N 512 4096 1048576 550160 543956 137540 203208 0 N FRA/
MOUNTED NORMAL N 512 4096 1048576 18435 18256 6145 6055 0 N OCR/

[oracle@orahost01 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 6 04:45:11 2019

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option

SQL> select state, name, type from v$asm_diskgroup;

STATE NAME TYPE
----------- ------------------------------ ------
MOUNTED DATA EXTERN
MOUNTED FRA NORMAL
MOUNTED OCR NORMAL

2. Check existing disk details

SQL> select GROUP_NUMBER,MOUNT_STATUS,STATE,REDUNDANCY,NAME,PATH from v$asm_disk;
GROUP_NUMBER MOUNT_S STATE REDUNDA NAME PATH
------------ ------- -------- ------- --------- ------------
0 CLOSED NORMAL UNKNOWN /dev/oracleasm/disks/ASMDATA06
1 CACHED NORMAL UNKNOWN DATA05 /dev/oracleasm/disks/ASMDATA05
1 CACHED NORMAL UNKNOWN DATA04 /dev/oracleasm/disks/ASMDATA04
1 CACHED NORMAL UNKNOWN DATA03 /dev/oracleasm/disks/ASMDATA03
1 CACHED NORMAL UNKNOWN DATA02 /dev/oracleasm/disks/ASMDATA02
1 CACHED NORMAL UNKNOWN DATA01 /dev/oracleasm/disks/ASMDATA01
3 CACHED NORMAL UNKNOWN OCR_00 /dev/oracleasm/disks/OCR_01
2 CACHED NORMAL UNKNOWN FRA_01 /dev/oracleasm/disks/FRA_01
2 CACHED NORMAL UNKNOWN FRA_02 /dev/oracleasm/disks/FRA_02

9 rows selected.

3. Add the disk into diskgroup with same naming convention

SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/ASMDATA06' name DATA06;

Diskgroup altered.

4. Check the current size of diskgroup after adding the disk

SQL> select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB,FREE_MB from v$asm_diskgroup
order by 1;

GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB
------------ --------- --------- ------ ---------- ----------
1 DATA MOUNTED EXTERN 2329103 1011748
2 FRA MOUNTED NORMAL 550160 543956
3 OCR MOUNTED NORMAL 18435 18256

5. You can check the status of the process from below sql statement

SQL> select operation, state, sofar, est_work, est_minutes from v$asm_operation where group_number=1;

OPERATION STATE SOFAR EST_WORK EST_MINUTES
----- ---- ---------- ---------- -----------
REBAL RUN 3346 361739 107

SQL> select operation, state,power, actual, sofar, est_work, est_minutes from v$asm_operation where group_number=1;

OPERATION STATE POWER ACTUAL SOFAR EST_WORK EST_MINUTES
----- ----- ---- ---- ------- ------ ---------
REBAL RUN 1 1 3456 361740 115

5. You can speed up the re-balancing process by changing power limit for diskgroup

SQL> Alter diskgroup DATA rebalance power 4;

SQL> select operation, state,power, actual, sofar, est_work, est_minutes from v$asm_operation where group_number=1;

OPERATION STATE POWER ACTUAL SOFAR EST_WORK EST_MINUTES
----- ----- ---- ---- ------- ------ ---------
REBAL RUN 4 4 1200 324740 205

Thursday, March 12, 2020

Solution for ORA-03113: end-of-file on communication channel

Problem

Following Error occur while restart the database after the unclean shutdown

SQL> startup
ORACLE instance started.
Total System Global Area 4943876096 bytes
Fixed Size 2261688 bytes
Variable Size 989859144 bytes
Database Buffers 3942645760 bytes
Redo Buffers 9109504 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 18379
Session ID: 191 Serial number: 3

Solution

Redo log file might become corrupted while the database is open,Need to clear the corrupted redo logs to open the database.

SQL> startup mount;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 11 18:35:04 2019

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 4943876096 bytes
Fixed Size 2261688 bytes
Variable Size 989859144 bytes
Database Buffers 3942645760 bytes
Redo Buffers 9109504 bytes
Database mounted.

SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 4943876096 bytes
Fixed Size 2261688 bytes
Variable Size 989859144 bytes
Database Buffers 3942645760 bytes
Redo Buffers 9109504 bytes
Database mounted.
Database opened.

Wednesday, December 25, 2019

Resize Redo log files

Oracle Database did not allow to resize redo log files since we need to drop and re-create to overcome the situation.

Following points need to be considered when we drop the redo log files

Can not drop redo log files if it's in Current or Active status
Database requires at least two redo log groups regardless number of members
OS files are wont delete when we drop redo log files. So delete from OS to create redo log files with same name

Steps

1. Check the redo log status

SQL> select group#,bytes,archived,status,members from v$log;

GROUP# BYTES ARC STATUS MEMBERS
---------- ---------- --- ---------------- ----------
1 209715200 YES INACTIVE 2
2 209715200 YES INACTIVE 2
3 209715200 NO CURRENT 2

In this case we are try to resize group#3, but we can not drop as the status is CURRENT

2. Switch CURRENT redo log group

SQL> alter system switch logfile;

SQL> select group#,bytes,archived,status,members from v$log;

GROUP# BYTES ARC STATUS MEMBERS
---------- ---------- --- ---------------- ----------
1 209715200 NO CURRENT 2
2 209715200 YES INACTIVE 2
3 209715200 YES ACTIVE 2

Need to change status from ACTIVE to INACTIVE for drop redo log file

3. Change the status of redo log group. checkpoint force to write all changes from the database buffers to data files.

SQL> alter system checkpoint global;

SQL> select group#,bytes,archived,status,members from v$log;

GROUP# BYTES ARC STATUS MEMBERS
---------- ---------- --- ---------------- ----------
1 209715200 NO CURRENT 2
2 209715200 YES INACTIVE 2
3 209715200 YES INACTIVE 2

4. Check the members of group and drop the redo log group

SQL> select member from v$logfile where group#=3;
MEMBER
--------------------------------
C:\ORADATA\DEVCDB\REDO030.LOG
C:\ORADATA\DEVCDB\REDO031.LOG

SQL> alter database drop logfile group 3;

5. Delete specific redo log files from Operating system

6. Create the redo log files with changes

For Multiple File

SQL> alter database add logfile group 3 ('C:\ORADATA\DEVCDB\REDO030.LOG','C:\ORADATA\DEVCDB\REDO031.LOG') size 500M;

For Single File

SQL> alter database add logfile group 3 'C:\ORADATA\DEVCDB\REDO031.LOG' size 500M;

7. Check whether added redo log files are available

SQL> select member from v$logfile where group#=3;
MEMBER
--------------------------------
C:\ORADATA\DEVCDB\REDO030.LOG
C:\ORADATA\DEVCDB\REDO031.LOG

SQL> select group#,bytes,archived,status,members from v$log;

GROUP# BYTES ARC STATUS MEMBERS
---------- ---------- --- ---------------- ----------
1 209715200 NO CURRENT 2
2 209715200 YES INACTIVE 2
3 209715200 YES UNUSED 2

Tuesday, July 23, 2019

Migrate database across platform-12c (Different Endian)

We can use either 'TO PLATFORM' or 'FOR TRANSPORT' clause during the backup and restoration.

TO PLATFORM - Perform conversion on the source database.

FOR TRANSPORT - Performs conversion on the target database.

1. Find the Platform details of both source and target database

Source DB

SQL> select name,platform_id,platform_name from v$database;

NAME PLATFORM_ID PLATFORM_NAME
--------- ----------- ------------------------------
TESTCDB 12 Microsoft Windows x86 64-bit

Target DB

SQL> select name,platform_id,platform_name from v$database;

NAME PLATFORM_ID PLATFORM_NAME
--------- ----------- ------------------------------
UATCDB 2 Solaris[tm] OE (64-bit)

2. Make sure both source and target databases are in the Different endian format

SQL> select PLATFORM_ID,PLATFORM_NAME,ENDIAN_FORMAT from v$transportable_platform order by platform_id;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
5 HP Tru64 UNIX Little
6 AIX-Based Systems (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
12 Microsoft Windows x86 64-bit Little
13 Linux x86 64-bit Little
15 HP Open VMS Little
16 Apple Mac OS Big
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
21 Apple Mac OS (x86-64) Little

20 rows selected.

3. Connect to Source database(Windows) and make the tablespace read only which need to transportable

RMAN target sys/manager@TESTPDB

RMAN> Alter tablespace USERS read only;

4. Backup tablespace. Make sure Database directory is available on given path

RMAN> BACKUP
TO PLATFORM 'Solaris[tm] OE (64-bit)'
FORMAT 'C:\Build\Backups\to_solaris\new\users.bkp'
DATAPUMP FORMAT 'C:\Build\Backups\to_solaris\new\users_dmp.bkp'
TABLESPACE USERS;

5. Copy both backup and datapump files to target server

6. connect to target DB (Solaris) and drop specific Tablespace which need to restore

sqlplus sys/manager@UATPDB as sysdba

SQL> DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES;

7. Restore Tablespace

RMAN target sys/manager@UATPDB

RMAN> RESTORE
FOREIGN TABLESPACE USERS
format '/u02/oradata/UAT10CDB/UATPDB/%U'
FROM BACKUPSET '/u01/scripts/to_solaris/users.BKP'
DUMP FILE FROM BACKUPSET '/u01/scripts/to_solaris/users_dmp.BKP';

8. Check the tablespace status

sqlplus sys/manager@UATPDB as sysdba

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';
TABLESPACE_NAME STATUS
--------------- -------
USERS READ ONLY

9. Make the tablespace online

SQL> alter tablespace USERS read write;

Solution for 'ORA-12919: Can not drop the default permanent tablespace' while drop the USERS tablespace

1. Add the USERS temporary tablespace and make it dafault permanent

sqlplus sys/manager@UATPDB as sysdba

SQL> CREATE TABLESPACE USERS_TEMP DATAFILE '/u02/oradata/UAT10CDB/UATPDB/users_temp_01.dbf' size 100M;

SQL> alter database default tablespace USERS_TEMP;

2. Make sure Dafault permanent Tablespace changed to newly created tablespace

sqlplus sys/manager@UATPDB as sysdba

SQL> SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE
-------------------------------------
USERS_TEMP

3. Then drop the USERS tablespace

sqlplus sys/manager@UATPDB as sysdba

SQL> DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES;

Monday, June 17, 2019

Rename Pluggable Database

1. Find the Connected Container

SQL> sho con_name;

CON_NAME
----------------------
CDB$ROOT

2. Find the List of Pluggable Databases

SQL> sho pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
------- --------- --------- ----------
2 PDB$SEED READ ONLY NO
3 UATPDB READ WRITE NO

3. Connect to Specific Pluggable DB which need to rename

SQL> alter session set container=UATPDB;

Session altered.

4. Shutdown the pluggable Database

SQL> alter pluggable database UATPDB close;

Pluggable database altered.

5. Startup in Restricted Mode

SQL> alter pluggable database UATPDB open restricted;

Pluggable database altered.

6. Rename the pluggable database

SQL> alter pluggable database UATPDB rename global_name to MIGPDB;

Pluggable database altered.

7. Shutdown and start the pluggable database

SQL> shutdown immediate;

Pluggable Database closed.

SQL> startup;

Pluggable Database opened.

8. Check the Pluggable Database name

SQL> sho con_name;

CON_NAME
----------------------------
MIGPDB

9. The directory does not get renamed according to the new pluggable Database name. Have to create appropriate directory and move the datafile. From 12c onward we can move the datafile online

SQL> alter database move datafile '/u02/oradata/MIG10CDB/UATPDB/system01.dbf' to '/u02/oradata/MIG10CDB/MIGPDB/system01.dbf';

Database altered.

Monday, May 21, 2018

Maintenance of OCR and Voting Disk

OCR

1. Stop Oracle Clusterware

[root@ol5-112-rac1 ~]# crsctl stop crs

2. Start Oracle Clusterware

[root@ol5-112-rac1 ~]# crsctl start crs

3. Start Oracle Clusterware

[root@ol5-112-rac1 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS 4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

4. To find the backup

[root@ol5-112-rac1 ~]# ocrconfig -showbackup

ol5-112-rac2 2018/05/12 07:33:50 /u01/app/11.2.0/grid/cdata/ol5-112-scan/backup00.ocr

5. To Perform manual backup

[root@ol5-112-rac1 ~]# ocrconfig -manualbackup

6. To find the Manual backup only

[root@ol5-112-rac1 ~]# ocrconfig -showbackup manual

ol5-112-rac2 2018/05/12 07:33:50 /u01/app/11.2.0/grid/cdata/ol5-112-scan/backup_183340.ocr

7. Change OCR backup location

[root@ol5-112-rac1 ~]# ocrconfig -backuploc /home/oracle

8. Review its contents

[root@ol5-112-rac1 bin]# ocrdump -backupfile backup00.ocr

9. Restore the physical OCR backup

[root@ol5-112-rac1 ~]# crsctl stop crs
[root@ol5-112-rac1 ~]# ocrconfig -restore /u01/app/11.2.0/grid/cdata/ol5-112-scan/backup00.ocr
[root@ol5-112-rac1 ~]# crsctl start crs

10. Check Existing OCR Detail

[root@ol5-112-rac1 ol5-112-scan]# ocrcheck

Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2788
Available space (kbytes) : 259332
ID : 1488879956
Device/File Name : +DATA
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded

11. Check OCR integrity

[oracle@ol5-112-rac1 ~]$ cluvfy comp ocr -n all

Verifying OCR integrity
Checking OCR integrity...
Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations
ASM Running check passed. ASM is running on all cluster nodes
Checking OCR config file "/etc/oracle/ocr.loc"...
OCR config file "/etc/oracle/ocr.loc" check successful
Disk group for ocr location "+DATA" available on all the nodes
Checking size of the OCR location "+DATA" ...
Size check for OCR location "+DATA" successful...
OCR integrity check passed
Verification of OCR integrity was unsuccessful on all the specified nodes.

Voting Disk

1. Check existing Voting Disk

[root@ol5-112-rac1 ~]# crsctl query css votedisk

## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 5d89f9b9a2d24f6cbfed2335d94877ed (/dev/oracleasm/disks/DISK1) [DATA]

2. Add voting disk

[root@ol5-112-rac1 ~]# crsctl add votedisk /dev/oracleasm/disks/DISK2

3. Delete voting disk

[root@ol5-112-rac1 ~]# crsctl add votedisk /dev/oracleasm/disks/DISK2

Tuesday, July 25, 2017

Pre Upgrade Error : Oracle Packages and Types [upgrade] INVALID

Problem
Oracle Packages and Types Invalid from pre-upgrade tool for intend to upgrade oracle 11.2.0.1 to 11.2.0.4.

SQL> @<ORACLE_HOME>/rdbms/admin/utlu112i.sql

--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] INVALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle Application Express [upgrade] VALID

Solution

1. Check the status of Registry components

SQL> SELECT comp_name,version,status FROM dba_registry;

Oracle Database Catalog Views 11.2.0.1.0 VALID
Oracle Database Packages and Types 11.2.0.1.0 INVALID
Oracle Workspace Manager 11.2.0.1.0 VALID
JServer JAVA Virtual Machine 11.2.0.1.0 VALID
Oracle XDK 11.2.0.1.0 VALID
Oracle Database Java Packages 11.2.0.1.0 VALID
Oracle Expression Filter 11.2.0.1.0 VALID
Oracle Text 11.2.0.1.0 VALID
Oracle XML Database 11.2.0.1.0 VALID
Oracle Rules Manager 11.2.0.1.0 VALID
Oracle Multimedia 11.2.0.1.0 VALID
Oracle Application Express 3.2.1.00.10 VALID
Oracle Enterprise Manager 11.2.0.1.0 VALID

2. log as SYSDBA and restart database in upgrade mode

$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup upgrade;

3. Execute Following scripts

SQL> @<ORACLE_HOME>/rdbms/admin/catalog.sql --Recreate Oracle database Catalog Views components
SQL> @<ORACLE_HOME>/rdbms/admin/catproc.sql --Recreate Oracle database Packages and Types component

4. Recompile all invalid objects

SQL> @<ORACLE_HOME>/rdbms/admin/utlrp.sql

5. Shutdown database and startup in normal mode

SQL> shutdown immediate;
SQL> startup;

6. Check the status of Registry components Again

SQL> SELECT comp_name,version,status FROM dba_registry;

Oracle Database Catalog Views 11.2.0.1.0 VALID
Oracle Database Packages and Types 11.2.0.1.0 VALID
Oracle Workspace Manager 11.2.0.1.0 VALID
JServer JAVA Virtual Machine 11.2.0.1.0 VALID
Oracle XDK 11.2.0.1.0 VALID
Oracle Database Java Packages 11.2.0.1.0 VALID
Oracle Expression Filter 11.2.0.1.0 VALID
Oracle Text 11.2.0.1.0 VALID
Oracle XML Database 11.2.0.1.0 VALID
Oracle Rules Manager 11.2.0.1.0 VALID
Oracle Multimedia 11.2.0.1.0 VALID
Oracle Application Express 3.2.1.00.10 VALID
Oracle Enterprise Manager 11.2.0.1.0 VALID