export PG_HOME=/Postgres/pg13
export PATH=$PG_HOME/bin:$PATH
export PGDATABASE=postgres
export PGDATA=/Postgres/Data/

  1. Download Postgres binary(source code) from https://www.postgresql.org/ftp/source/
    you can download (postgresql-13.0.tar.gz) or (postgresql-13.0.tar.bz2)
  2. Copy downloded binary on server and unzip (root access required for Postgres installtion)
    #tar -xvzf postgresql-13.0.tar.gz
    New folder(postgresql-13.0) will be created
  3. Install Required package
    #yum install readline* =====>up and down DB command usage on pgsql prompt
    #yum install zlib* ========>Database compres
  4. Run installtion of postgress using root access
    #cd postgresql-13.0
    #./configure -prefix=/Postgres/pg13
    #make
    #make install
    #cd contrib/
    #make
    #make install

    Note:-we want to install postgres binary on /Postgres/pg13 directory so we must include -prefix option in configure else postgres will install on defalt /usr/local/pgsql directory.more option -help

./configure…

Most of the time after migration of database on new server we get following error in rman backup job

RMAN-00571: ========================================
RMAN-00569: = ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on t1 channel at 11/13/2020 01:18:40
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library

To resolve this issue we have to create soft link on new server in $ORACLE_HOME/lib directory.

To quickly resolve this issue crosscheck with your old server soft link with new host sever

To find all soft link in directory:-

find . -type l

In my case following link is missing in $ORACLE_HOME/lib directory

ln -s /usr/lib/libnsrora.so libobk.so

[oracle@newhost /oracle/base/product/db11204_h0/lib ] $ ls -l libobk.so
lrwxrwxrwx 1 oracle dba 21 Nov 13 01:24 libobk.so -> /usr/lib/libnsrora.so

Following are the environment variable we can set in .profile or .bash_profile in the Linux-based system.

you can customize this environment variable based on your project.

export PG_HOME=/Postgres/pg12
export PATH=$PG_HOME/bin:$PATH
export PGDATABASE=postgres
export PGDATA=/Postgres/Data/

Note:-

PG_HOME= location ofPostgres binary installed

PATH=postgres executable path location

PGDATABASE=database name which you want to connect after login on server.

PGDATA=data directory from Postgres cluster(instance) is running.

When we dropping user with “CASCADE” option oracle will not allow us to drop that user because he’s session is present in database no matter weather its ACTIVE or INACTIVE .We need to kill all user session and the proceed with our drop user activity .

SQL> drop user SCOTT cascade;
drop user SCOTT cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

use below query to generate kill command of dropping user.

SQL>select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='&user';Enter value for user: SCOTT
old 1: select 'alter…

Below is the script to Generate DDL of Index

Note:- Please make the appropriate change in script Like Owner_name, object name which you looking for DDL


SET PAGESIZE 0
SET LONG 90000
SET FEEDBACK OFF
SET ECHO OFF
SET HEADING OFF
SET LINES 100
SET LINESIZE 200
SET MARKUP HTML OFF SPOOL OFF
SET TRIMSPOOL ON
Col STATEMENT format a10000
SPOOL hrtrg_cindexs.sql REPLACE
SELECT
DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME,'HRTRG') || ';' AS STATEMENT
FROM DBA_INDEXES U
WHERE OWNER = 'HRTRG'
AND TABLE_NAME IN ( 'JOB_HISTORY','EMPLOYEES','JOBS','DEPARTMENTS','LOCATIONS','REGIONS');
SPOOL OFF

Below is the script for dropping Index from database

SET PAGESIZE 0
SET LONG 90000
SET FEEDBACK OFF
SET ECHO OFF
SET HEADING OFF
SET LINES 100
SET MARKUP HTML OFF SPOOL OFF
SET TRIMSPOOL ON
SPOOL hrtrg_dindexs.sql REPLACE
SELECT
'DROP INDEX HRTRG.' || INDEX_NAME || ';' AS STATEMENT
FROM DBA_INDEXES U
WHERE OWNER = 'HRTRG'
AND TABLE_NAME IN ( 'JOB_HISTORY','EMPLOYEES','JOBS','DEPARTMENTS','LOCATIONS','REGIONS');
SPOOL OFF

Connecting through sqlplus with os authentication (sqlplus / as sysdba) didn’t work due to the same reason

At that time, the database had to become available to the users again ASAP.

Solution

sqlplus -prelim "/as sysdba"
SYS@PMSB >shutdown abort
ORACLE instance shut down.
SYS@PMSB >exit
Disconnected from ORACLE

Kill all OS process

for a in $(ps -ef |grep $ORACLE_SID | grep -v grep | awk '{ print $2}'); do 
>kill -9 $a;
>done

Start Instance

(PMSB)oracle@hostname:/oracle/base/home> sqlplus / as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Fri May 22 16:01:45 2020Copyright (c) 1982, 2014, Oracle.  All rights reserved.Connected to…

All Diskgroup status

 col name for a30
select group_number, name, state, type from v$asm_diskgroup;

ASM disk group disks

SET LINESIZE 130
SET PAGESIZE 200
COL NAME FORMAT A30 HEADING 'ASM Diskgroup'
COL DISK_NUMBER FORMAT 99 HEADING 'DISK#'
COL MODE_STATUS FORMAT A11
COL PATH FORMAT A45
BREAK ON NAME

SELECT g.name
, disk_number
, path
, d.mode_status
, d.total_mb
, d.free_mb
FROM v$asm_disk d
, v$asm_diskgroup g
WHERE d.group_number(+) = g.group_number
ORDER BY name;

ASM disk group disk and their status

SET LINESIZE 130
COL STATE FOR A10
COL NAME FOR A30 HEADING 'ASM Diskgroup'
COL DISK_NUMBER FOR 99 HEADING 'Disk#'
COL VOTING_FILE FOR A6 HEADING 'Voting'
COL PATH FOR A45
COL MOUNT_STATUS FOR A12 HEADING 'Mount Status'
COL…

Run RMAN job in the background

  1. with the help of vi editor create rman script file (.scr)
$cat rman_restore.scrconnect target /
run {
allocate channel pri1 type disk;
allocate channel pri2 type disk;
allocate channel pri3 type disk;
allocate channel pri4 type disk;
allocate channel pri5 type disk;
restore database;
recover database;
}

2. Make sure the environment variable set properly

3.Run rman script in nohup

nohup rman cmdfile=' rman_restore.scr' > rman.log 2>&1 &

Run Datapump (expdp and impdp)job in the background

  1. Create parfile of expdp or impdp (depending on what activity you performing)
$cat expdp_DBNAME_SCHEMA.par
userid="/as sysdba"
DIRECTORY=DATA_PUMP_DIR
dumpfile=expdp_PRD_%U_31jan.dmp
logfile=expdp_PRD.log
schemas=shema
PARALLEL=10
$cat impdp_DBNAME_SCHEMA.par
userid="/ as sysdba"
DIRECTORY=DATA_PUMP_DIR
dumpfile=expdp_PRD_%U_31jan.dmp
logfile=impdp_PRD.log
schemas=schema
PARALLEL=10

How to find SCN from timestamp

select timestamp_to_scn( to_date('2012-01-30 04:00:00', 'YYYY-MM-DD HH24:MI:SS')) from dual ;

How to find timestamp from SCN

SELECT SCN_TO_TIMESTAMP(2501136215) from dual;

Arun Shinde

Oracle Apps DBA

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store