Venish VK (Sr.Oracle Database Administrator)

Friday, 6 February 2015

Tablespace Datafile wise free space:

Tablespace Datafile wise free space:

set lines 152

col TABLESPACE format a10

col FILE_NAME format a35

col BYTES format format a10

col MAXBYTES format a12

col AUT format a8

col STATUS format a12

select TABLESPACE_NAME,FILE_NAME,bytes/1024/1024,maxbytes/1024/1024,autoextensible,status from dba_data_files;

Particular Tablespace Usage Report:

select tablespace_name, tbs_mb, free_mb,

round(free_mb/tbs_mb*100,2) pct_free, round(tbs_mb*15/100-free_mb,2) Est_mb from

( select a.tablespace_name ,sum(bytes)/1024/1024 tbs_mb,

nvl((select sum(bytes)/1024/1024 from dba_free_space where tablespace_name=a.tablespace_name),0) free_mb

from dba_data_files a group by tablespace_name)

where free_mb/tbs_mb*100 <=10 and tablespace_name ='&tablespace_name';

Total Tablespace space usage – Scripts

select tbs.tbs,total_mb tbs_mb, nvl(free_mb,0) free_mb, round(nvl(free_mb,0)/total_mb*100,2) pct_free,

total_mb*25/100 -nvl(free_mb,0) Est_mb from

(select tablespace_name tbs,round(sum(bytes)/1024/1024,2) free_mb

from dba_free_space group by tablespace_name) free_tbs,

(select tablespace_name tbs,round(sum(bytes)/1024/1024,2) total_mb

from dba_data_files group by tablespace_name) tbs

where tbs.tbs=free_tbs.tbs(+) AND round(nvl(free_mb,0)/total_mb*100,2)<=10;

Tablespace Script:

clear columns

column tablespace format a20

column total_mb format 999,999,999,999.99

column used_mb format 999,999,999,999.99

column free_mb format 999,999,999.99

column pct_used format 999.99

column graph format a25 heading "GRAPH (X=5%)"

column status format a10

compute sum of total_mb on report

compute sum of used_mb on report

compute sum of free_mb on report

break on report

set lines 200 pages 100

select total.ts tablespace,

DECODE(total.mb,null,'OFFLINE',dbat.status) status,

total.mb total_mb,

NVL(total.mb - free.mb,total.mb) used_mb,

NVL(free.mb,0) free_mb,

DECODE(total.mb,NULL,0,NVL(ROUND((total.mb - free.mb)/(total.mb)*100,2),100)) pct_used,

CASE WHEN (total.mb IS NULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'

ELSE '['|| DECODE(free.mb,

null,'XXXXXXXXXXXXXXXXXXXX',

NVL(RPAD(LPAD('X',trunc((100-ROUND( (free.mb)/(total.mb) * 100, 2))/5),'X'),20,'-'),

'--------------------'))||']'

END as GRAPH

from

(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,

(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free,

dba_tablespaces dbat

where total.ts=free.ts(+) and

total.ts=dbat.tablespace_name

UNION ALL

select sh.tablespace_name,

'TEMP',

SUM(sh.bytes_used+sh.bytes_free)/1024/1024 total_mb,

SUM(sh.bytes_used)/1024/1024 used_mb,

SUM(sh.bytes_free)/1024/1024 free_mb,

ROUND(SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free)*100,2) pct_used,

'['||DECODE(SUM(sh.bytes_free),0,'XXXXXXXXXXXXXXXXXXXX',

NVL(RPAD(LPAD('X',(TRUNC(ROUND((SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free))*100,2)/5)),'X'),20,'-'),

'--------------------'))||']'

FROM v$temp_space_header sh

GROUP BY tablespace_name

order by 1

ttitle off

rem clear columns

Venish VK (Sr.Oracle DBA)

Thursday, 5 February 2015

ORA-00119: invalid specification for system parameter LOCAL_LISTENER, ORA-00132

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name 'LISTENER_ORCL'

Solutions:

Step 1: using spfile starting up your database, create a pfile from it.

Enter Username:sys as sysdba
Passwd:

connected to an idle instance.

SQL> create pfile from spfile;

File created.

SQL>exit

Step 2: Edit the pfile and remove the parameter LOCAL_LISTENER

*.local_listener='LISTENER_ORCL'

Step 3: Create spfile from the pfile

SQL> create Spfile from pfile;

File created.

Step 4: Startup the database

SQL>STARTUP

ORA - 06553: PLS-801: internal error [56327]

ORA - 06544: PL/SQL: internal error , arguments: [56327], [ ], [ ], [ ], [ ], [ ], [ ]
ORA - 06553: PLS-801: internal error [56327]

Solution:

Recompile the existing PL/SQL in the format required for new database.

SQL> shut immediate

SQL> startup upgrade;

SQL> @?/rdbms/admin/utlirp

SQL> shut immediate

SQL> startup

SQL> @?/rdbms/admin/utlrp

RMAN Active Database Cloning: 11g New feature

Target DB Auxiliary DB

192.168.1.100 192.168.1.101

PROD TEST

<--------------------------------

Auxiliary DB Server:

$ mkdir /u01/admin/test

$ cd /u01/admin/test

$mkdir pfile bdump udump cdump create

$mkdir /u01/oradata/test

Target DB Server:

$scp –r $ORACLE_HOME/dbs/initprod.ora oracle@192.168.1.101:/u01/admin/test/pfile

Target DB Server:

$mv /u01/admin/test/pfile/initprod.ora /u01/admin/test/pfile/inittest.ora

$ vi /u01/admin/test/pfile/inittest.ora

db_unique_name=test

db_name=test

control_files=’/u01/oradata/test/control01.ctl’

background_dump_dest=/u01/admin/test/bdump

user_dump_dest=/u01/admin/test/udump

core_dump_dest=/u01/admin/test/cdump

audit_dump_dest==/u01/admin/test/adump

db_file_name_convert=(‘/opt/oradata/prod’,’/u01/oradata/test’)

log_file_name_convert=(‘/opt/oradata/prod’,’/u01/oradata/test’)

$export ORACLE_SID=test

$orapwd file=$ORACLE_HOME/dbs/orapwtest password=sys

$ sqlplus “ / as sysdba”

SQL> startup nomount pfile=’/u01/admin/test/pfile/inittest.ora’;

SQL> create spfile from pfile=’/u01/admin/test/pfile/inittest.ora’;

SQL> shutdown immediate;

SQL> startup nomount;

$cat>> $ORACLE_HOME/network/admin/tnsnames.ora

test=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101)(PORT=1521))

(CONNECT_DATA=

(SERVICE_NAME=test)

)

Target DB SERVER:

$cat>> $ORACLE_HOME/network/admin/tnsnames.ora

test123=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101)(PORT=1521))

(CONNECT_DATA=

(SERVICE_NAME=test)

)

$ export ORACLE_SID=PROD

$ rman target /

RMAN> connect catalog rman/rman123@apps123

RMAN> connect auxiliary sys/sys@test123;

RMAN> duplicate target database to test from active database;

èDUPLICATE TARGET DATABASE TO test FROM ACTIVE DATABASE PASSWORD FILE SPFILE NOFILENAMECHECK;(Don’t check directory structure – what u mention in dbfilenameconvert that can be take)

èDUPLICATE DATABASE TO test UNTIL TIME “TO_DATE (‘11/01/2013 14:00:00’,’MM/DD/YYYY HH24:MI:SS’) SPFILE BACKUP LOCATION ‘/prod_backups’ NOFILENAMECHECK; (In target DB presented spfile to be cloned , and then testDB spfile backup)

RMAN>exit;