Oracle get column ddl from dba_tab_columns dictionary view

Query to generate alter table add column DDL from Oracle dictionary views:

There will be a situation when we might need to generate add column DDLs for a particular table or all tables in a schema.

Oracle data dictionary view dba_tab_columns/all_tab_columns stores column details such as

DATA_TYPE – column data type

DATA_LENGTH – Length of the column

DATA_PRECISION – Decimal precision for NUMBER type

DATA_SCALE – Digits to right side of decimal point in NUMBER type

Below example covers following data types: Read More

Cloning Oracle Grid Infrastructure home and ASM installation

perl clone Oracle GI and ASM installation

This tutorial is meant to work for RHEL/Centos environments. Covered few issues faced during grid software installation

  • TAR binaries and copy, make sure to bring down oracle services before creating tar.
[[email protected] product]# tar -czvf grid_home.tar.gz grid_home
[[email protected] product]$ scp grid_home.tar.gz node4:/u01/app/oracle/product/.
  • Partition disks required for ASM instance – follow procedure here
  • Create oracle OS user under dba group, this user will be owning the grid binaries.
[[email protected] tmp]# groupadd dba
[[email protected] tmp]# useradd -g dba oracle
  • Unzip binaries and setting few system environment variables.
[[email protected] disks]$ cd /u01/app/oracle/product
[[email protected] product]$ tar -xzvf grid_home.tar.gz
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/grid_home
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=+ASM
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
  • Running perl clone

Read More

fix dataguard broker configuration

Steps to rebuild dataguard broker configuration

Issue with dgmgrl configuration

DGMGRL> show configuration;

Configuration - apps

  Protection Mode: MaxPerformance
  Databases:
    appsnew_dca - Primary database
      Error: ORA-16810: multiple errors or warnings detected for the database

    appsnew_vgs - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the database

    apps_lax    - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

Viewing exact database error Read More

Copy oracle schema from one database to another

Using expdp and impdp tools to backup a user/schema data and restore to another database.

Create a backup directory which will be used by expdp tool

CREATE OR REPLACE DIRECTORY "BACKUP_DIR" AS '/u01/expdp'

Use expdp to take full schema backup

expdp \"/ as sysdba\" schemas=pin2 directory=backup_dir dumpfile=pin2.dmp logfile=pin2.log

Dump file set for SYS.SYS_EXPORT_SCHEMA_02 is:
  /u01/expdp/pin2.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at 07:08:14

[email protected][dev02]/u01/expdp>ls 
pin2.dmp         pin2.log

copy the dump file to another server where we need to restore. We need to create a backup_dir on that server and copy dump file to that directory.

CREATE OR REPLACE DIRECTORY "BACKUP_DIR" AS '/u01/expdp'
scp [email protected]:/u01/expdp/pin2.dmp /u01/expdp/.

Start importing the schema to pin3, we need to drop the existing schema if it exists. Read More

Migrate oracle database from Non ASM to ASM

 Steps to migrate database files from local disk/SAN to ASM :

First create ASM disks and diskgroup – here

Migrating all data (Datafiles, Controlfiles, logfiles) to DATA diskgroup.

Startup mount and restore SPFILE to ASM

SYS @ test > startup mount

[[email protected] dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Feb 8 11:15:51 2017

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

connected to target database: TEST (DBID=2104987387, not open)

RMAN> RESTORE SPFILE TO '+DATA/spfiletest.ora';

Starting restore at 08-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA/spfiletest.ora
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbs/01o1et80_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbs/01o1et80_1_1 tag=TAG20130208T111407
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 08-FEB-17

Copy and switch database files to ASM Read More

Difference between undo segment and redo logs

A point of confusion for a DBA would be the distinction between undo and redo.

–> Basically undo tablespace stores the before values of changed data blocks whenever we issue a insert, update or delete statement (DML operation) . When we issue any DML statement the changed blocks are stored in buffer cache and the before values for those changed blocks in UNDO segments. Whenever we issue a ROLLBACK command it uses the undo segment to rollback to previous value and even UNDO provides the read consistency to a user as he can only see the previous values until a transaction is committed, for example a user 1 may run select statement on a table being modified by user 2, while user 1 can only see the unchanged values until user 2 commits his DML statement.

How long a undo data stored in the database?
Oracle provides flexibility of how long should undo data be stored with the help of undo_retention parameter. We can set undo_management parameter to automatic for oracle to manage undo retention(default), or even set this value manually and it’s value should be greater than the time taken by the longest running query in your database. Read More

Create disk partitions and disk lables for ASM instance

Creating ASM disks and adding to existing diskgroups

As a root user, check for available disks :

fdisk -l
Disk /dev/vdb: 107.4 GB, 107374182400 bytes
16 heads, 63 sectors/track, 208050 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes

Create new partitions

fdisk /dev/vdb
Command (m for help): p
Command (m for help): n
Command action
 e extended
 p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-208050, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-208050, default 208050): +30G
Command (m for help): p
Command (m for help): n
Command action
 e extended
 p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (31-208050, default 31):
Using default value 31
Last cylinder, +cylinders or +size{K,M,G} (31-208050, default 208050): +30G
Command (m for help): w
The partition table has been altered!

Install following rpms : Read More

Cloning Oracle home and creating a new database

Oracle database Installation using binaries copied from other server (Perl clone on RHEL/Centos):

1. Make sure following rpms are installed :

binutils 
compat-libcap1 
compat-libstdc++-33
gcc 
gcc-c++ 
glibc 
glibc-devel 
ksh 
libgcc 
libstdc++ 
libstdc++-devel 
libaio 
libaio-devel 
make 
sysstat

2. Create a new user and group who will be the owner of oracle binaries.

groupadd dba
useradd -g dba oracle --> -g to add dba as primary group

3. Check ulimits (/etc/security/limits.conf) Read More

Oracle data dictionary view does not exist error

Error while accessing catalog view DBA_DATA_FILES.

SQL> desc DBA_DATA_FILES
ERROR:
ORA-04043: object DBA_DATA_FILES does not exist
SQL> desc DBA_segments
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 SEGMENT_SUBTYPE                                    VARCHAR2(10)
 TABLESPACE_NAME                                    VARCHAR2(30)
 HEADER_FILE                                        NUMBER
 HEADER_BLOCK                                       NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 EXTENTS                                            NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 MAX_SIZE                                           NUMBER
 RETENTION                                          VARCHAR2(7)
 MINRETENTION                                       NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 RELATIVE_FNO                                       NUMBER
 BUFFER_POOL                                        VARCHAR2(7)
 FLASH_CACHE                                        VARCHAR2(7)
 CELL_FLASH_CACHE                                   VARCHAR2(7)

While we are able to access all other dictionary views, unable to access only dba_data_files .

FIX :

Read More

Oracle db link usage

Way to find out db link usage by database objects :

 

  • Below query can be used to find all DB links in oracle database
SQL> select * from dba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
------------------------------ ------------------------------ ------------------------------ -------------------------------------------------- ---------
PUBLIC TESTDB.WORLD LINKUSER testdb 24-MAY-11

 

  • Find DB link usage in synonyms
SQL> SELECT * FROM dba_synonyms WHERE TABLE_OWNER NOT IN('SYSTEM','SYS') AND DB_LINK IS NOT NULL;

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------------------------------------
PRODUSER COUNTS APPDEV COUNTS TESTDB.WORLD
PRODUSER COUNTS_S APPDEV COUNTS_S TESTDB.WORLD

 

  • DB link usage in VIEWS

Oracle internal view ‘dba_views.text‘ column contains view ddl. Below pl/sql code is helpful in finding views which use this DB link.

declare
 cursor text is select text,view_name from dba_views where owner not in('SYS','SYSTEM');
 v clob;
 vn varchar2(30);
begin
 for c in text loop
 v:=c.text;
 vn:=c.view_name;
 if ( v like '%TESTDB.WORLD%') then
 dbms_output.put_line( 'view is '||vn);
 end if;
 end loop;
end;

 

  • DB link usage by PROCEDURES or FUNCTIONS:

We can verify db link usage in any procedures or package body using data dictionary view dba_source.

SQL> select OWNER,NAME,TYPE from dba_source where TEXT like '%@TESTDB%' or TEXT like '%@testdb%';

OWNER NAME TYPE
------------------------------ ------------------------------ ------------
KHAN ACTIVITY_PULL_TESTDB PACKAGE BODY