Saturday, 19 May 2012

Oracle biggest Object size wise

Oracle Database 10 biggest Object size wise

set pagesize 200
col segment_name format a30
col tablespace_name format a30
select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, MB from
(select segment_name, segment_type, tablespace_name,bytes/1024/1024 MB, rank() over(order by bytes desc) srank from dba_segments) where srank <=10;
 

                                        Tablespace           Allocated
SEGMENT_NAME         SEGMENT_TYPE       Name                        MB
-------------------- ------------------ -------------------- ---------
IDL_UB1$             TABLE              SYSTEM                     240
SOURCE$              TABLE              SYSTEM                      64
SYS_LOB0000056487C00 LOBSEGMENT         SYSAUX                      57
025$$

IDL_UB2$             TABLE              SYSTEM                      30
SYS_LOB0000062550C00 LOBSEGMENT         SYSAUX                      26
006$$

C_TOID_VERSION#      CLUSTER            SYSTEM                      23
SYS_LOB0000064027C00 LOBSEGMENT         SYSAUX                      14
004$$

_SYSSMU5_538557934$  TYPE2 UNDO         UNDOTBS1                    14
JAVA$MC$             TABLE              SYSTEM                      13
CUSTOMERS            TABLE              EXAMPLE                     12
I_SOURCE1            INDEX              SYSTEM                      12

11 rows selected.

Oracle Temp Tablespace info

Oracle Temp Tablespace info
Oracle Temp Tablespace

Oracle Temp File usage Info ...

set lines 300
COLUMN mb_total HEADING 'Total|MB' FORMAT 99,999
COLUMN mb_used HEADING 'Used|MB' FORMAT 99,999
COLUMN mb_free HEADING 'Free|MB' FORMAT 99,999
column FILE_NAME Heading 'File|Name' FORMAT a30
column tablespace_name Heading 'Tablespace|Name' FORMAT a10
column TABLESPACE Heading 'Tablespace|Name' FORMAT a10
column MB Heading 'Allocated|MB' FORMAT 99,999
SELECT dbatf.FILE_NAME, dbatf.BYTES/1024/1024 MB, A.tablespace_name TABLESPACE, D.mb_total,SUM (A.used_blocks * D.block_size)/1024/1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) /1024/1024 mb_free FROM dba_temp_files dbatf, v$sort_segment A,
(SELECT B.name, C.block_size, SUM (C.bytes)/1024/ 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size ) D
WHERE A.tablespace_name = D.name
GROUP BY A.tablespace_name,
D.mb_total, dbatf.FILE_NAME, dbatf.BYTES/1024/1024
/

File Allocated Tablespace Total Used Free
Name MB Name MB MB MB
------------------------------ --------- ---------- ------- ------- -------
+DATA/orcl/temp01.dbf 29 TEMP 29 0 29

ORA-16649: possible failover to another database prevents this database from being opened

SQL> startup;
ORACLE instance started.

Total System Global Area 577511424 bytes
Fixed Size 1338000 bytes
Variable Size 461374832 bytes
Database Buffers 109051904 bytes
Redo Buffers 5746688 bytes
Database mounted.
ORA-16649: possible failover to another database prevents this database from
being opened

SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY

SQL> alter system set dg_broker_start=false scope=both sid='*'; ---- do this
System altered.

SQL> alter database open;
Database altered.

Tuesday, 15 May 2012

ASM Script to check .... ASM diskgroup size, ASM disk size , CANDIDATE Disk path..

ASM disk size

SET ECHO OFF FEEDBACK 6 HEADING ON LINESIZE 200 PAGESIZE 1000 TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL ON VERIFY OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name        FORMAT a12           HEAD 'Disk Group Name'
COLUMN disk_file_path         FORMAT a45           HEAD 'Path'
COLUMN disk_file_name         FORMAT a12           HEAD 'File Name'
COLUMN disk_file_fail_group   FORMAT a12           HEAD 'Fail Group'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'File Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL ""              OF total_mb used_mb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF total_mb used_mb ON report
SELECT
    NVL(a.name, '[CANDIDATE]')                       disk_group_name
  , b.path                                           disk_file_path
  , b.name                                           disk_file_name
  , b.failgroup                                      disk_file_fail_group
  , b.total_mb                                       total_mb
  , (b.total_mb - b.free_mb)                         used_mb
  , ROUND((1- (b.free_mb / b.total_mb))*100, 2)      pct_used
FROM v$asm_diskgroup a ,v$asm_disk b 
where a.group_number (+) =b.group_number 
ORDER BY a.name;
 

Disk Group N Path                                          File Name    Fail Group   File Size (MB) Used Size (MB) Pct. Used
------------ --------------------------------------------- ------------ ------------ -------------- -------------- ---------
DATA         /dev/vx/rdsk/oraasmdatatrn/oraasmdatatrn_v3   DATA_0002    DATA_0002            30,686         28,359     92.42
             /dev/vx/rdsk/oraasmdatatrn/oraasmdatatrn_v1   DATA_0000    DATA_0000            30,686         28,367     92.44
             /dev/vx/rdsk/oraasmdatatrn/oraasmdatatrn_v2   DATA_0001    DATA_0001            30,686         28,354     92.40
************                                                                         -------------- --------------
                                                                                             92,058         85,080

FLASH        /dev/vx/rdsk/oraasmflashtrn/oraasmflashtrn_v3 FLASH_0002   FLASH_0002           30,686          3,797     12.37
             /dev/vx/rdsk/oraasmflashtrn/oraasmflashtrn_v1 FLASH_0000   FLASH_0000           30,686          3,803     12.39
             /dev/vx/rdsk/oraasmflashtrn/oraasmflashtrn_v2 FLASH_0001   FLASH_0001           30,686          3,798     12.38
************                                                                         -------------- --------------
                                                                                             92,058         11,398

REDO1        /dev/vx/rdsk/oraasmredo1trn/oraasmredo1trn_v1 REDO1_0000   REDO1_0000            3,039            238      7.83
             /dev/vx/rdsk/oraasmredo1trn/oraasmredo1trn_v2 REDO1_0001   REDO1_0001            3,039            230      7.57
             /dev/vx/rdsk/oraasmredo1trn/oraasmredo1trn_v3 REDO1_0002   REDO1_0002            3,039            230      7.57
************                                                                         -------------- --------------
                                                                                              9,117            698

REDO2        /dev/vx/rdsk/oraasmredo2trn/oraasmredo2trn_v3 REDO2_0002   REDO2_0002            3,039            230      7.57
             /dev/vx/rdsk/oraasmredo2trn/oraasmredo2trn_v2 REDO2_0001   REDO2_0001            3,039            228      7.50
             /dev/vx/rdsk/oraasmredo2trn/oraasmredo2trn_v1 REDO2_0000   REDO2_0000            3,039            240      7.90
************                                                                         -------------- --------------
                                                                                              9,117            698

[CANDIDATE]  /dev/vx/rdsk/oraasmdatatrn/oraasmdatatrn_v4                                     30,686         30,686    100.00
************                                                                         -------------- --------------
                                                                                             30,686         30,686

                                                                                     -------------- --------------
Grand Total:                                                                                233,036        128,560

13 rows selected.
**********                                                                              -------------- --------------
                                                                                                30,686         30,686

                                                                                        -------------- --------------
Grand Tota                                                                                     233,036        128,560

13 rows selected.

Monday, 7 May 2012

ORA-29701: unable to connect to Cluster Synchronization Service

On Oracle single instance ASM

ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service

======================================================

[oracle@ ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid

[oracle@ ~]$ export ORACLE_SID=+ASM

[oracle@ ~]$ sqlplus "/as sysasm"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 5 11:14:29 2010

Connected to an idle instance.

SQL> startup

ORA-01078: failure in processing system parameters

ORA-29701: unable to connect to Cluster Synchronization Service

[oracle@oel5u4-+ASM bin] ./crsctl start resource ora.cssd

CRS-2672: Attempting to start 'ora.cssd' on 'oel5u4'

CRS-2679: Attempting to clean 'ora.diskmon' on 'oel5u4'

CRS-2681: Clean of 'ora.diskmon' on 'oel5u4' succeeded

CRS-2672: Attempting to start 'ora.diskmon' on 'oel5u4'

CRS-2676: Start of 'ora.diskmon' on 'oel5u4' succeeded

CRS-2676: Start of 'ora.cssd' on 'oel5u4' succeeded

[oracle@oel5u4-+ASM bin] ./crs_stat -t

Name Type Target State Host

------------------------------------------------------------

ora.DATA.dg ora....up.type OFFLINE OFFLINE

ora.FRA.dg ora....up.type OFFLINE OFFLINE

ora....ER.lsnr ora....er.type ONLINE ONLINE oel5u4

ora.asm ora.asm.type OFFLINE OFFLINE

ora.cssd ora.cssd.type ONLINE ONLINE oel5u4

ora.diskmon ora....on.type ONLINE ONLINE oel5u4

ora.orcl.db ora....se.type OFFLINE OFFLINE

[oracle@oel5u4-+ASM bin] sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 7 10:42:02 2012

Connected to an idle instance.

SQL> startup

Start the services

[oracle@oel5u4-+ASM bin] ./crsctl start resource ora.cssd

CRS-2672: Attempting to start 'ora.cssd' on 'oel5u4'

CRS-2679: Attempting to clean 'ora.diskmon' on 'oel5u4'

CRS-2681: Clean of 'ora.diskmon' on 'oel5u4' succeeded

CRS-2672: Attempting to start 'ora.diskmon' on 'oel5u4'

CRS-2676: Start of 'ora.diskmon' on 'oel5u4' succeeded

CRS-2676: Start of 'ora.cssd' on 'oel5u4' succeeded

[oracle@oel5u4-+ASM bin] ./crs_stat -t

Name Type Target State Host

------------------------------------------------------------

ora.DATA.dg ora....up.type OFFLINE OFFLINE

ora.FRA.dg ora....up.type OFFLINE OFFLINE

ora....ER.lsnr ora....er.type ONLINE ONLINE oel5u4

ora.asm ora.asm.type OFFLINE OFFLINE

ora.cssd ora.cssd.type ONLINE ONLINE oel5u4

ora.diskmon ora....on.type ONLINE ONLINE oel5u4

ora.orcl.db ora....se.type OFFLINE OFFLINE

[oracle@oel5u4-+ASM bin] sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 7 10:42:02 2012

Connected to an idle instance.

SQL> startup

ASM instance started

Total System Global Area 284565504 bytes

Fixed Size 1336036 bytes

Variable Size 258063644 bytes

ASM Cache 25165824 bytes

ASM diskgroups mounted

************************************************

-bash-4.2$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Sun Nov 11 17:54:34 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup;
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service

-bash-4.2$ ps -ef|grep -i asm
grid 4509 3833 0 17:50 pts/1 00:00:00 grep --color=auto -i asm

-bash-4.2$ ps -ef|grep -i d.bin
grid 4241 1 3 17:50 ? 00:00:01 /u01/app/12.2.0/grid/bin/ohasd.bin reboot
grid 4383 1 1 17:50 ? 00:00:00 /u01/app/12.2.0/grid/bin/oraagent.bin
grid 4403 1 0 17:50 ? 00:00:00 /u01/app/12.2.0/grid/bin/evmd.bin
grid 4415 1 0 17:50 ? 00:00:00 /u01/app/12.2.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
grid 4465 4403 0 17:50 ? 00:00:00 /u01/app/12.2.0/grid/bin/evmlogger.bin -o /u01/app/12.2.0/grid/log/[HOSTNAME]/evmd/evmlogger.info -l /u01/app/12.2.0/grid/log/[HOSTNAME]/evmd/evmlogger.log
grid 4516 3833 0 17:50 pts/1 00:00:00 grep --color=auto -i d.bin

-bash-4.2$ cd $ORACLE_HOME
-bash-4.2$ cd bin
-bash-4.2$ ./crsctl status resource ora.cssd
NAME=ora.cssd
TYPE=ora.cssd.type
TARGET=OFFLINE
STATE=OFFLINE

-bash-4.2$ ./crsctl status resource ora.asm
NAME=ora.asm
TYPE=ora.asm.type
TARGET=OFFLINE
STATE=OFFLINE

-bash-4.2$ ./crsctl start resource ora.asm
CRS-4995: The command 'Start resource' is invalid in crsctl. Use srvctl for this command.

-bash-4.2$ crsctl start resource -all <<<<<<<<<<<<<<<<< Or start all the resource
CRS-5702: Resource 'ora.LISTENER.lsnr' is already running on 'oel7-ora'
CRS-5702: Resource 'ora.evmd' is already running on 'oel7-ora'
CRS-2501: Resource 'ora.ons' is disabled
CRS-2672: Attempting to start 'ora.cssd' on 'oel7-ora'
CRS-2672: Attempting to start 'ora.diskmon' on 'oel7-ora'
CRS-2676: Start of 'ora.diskmon' on 'oel7-ora' succeeded
CRS-2676: Start of 'ora.cssd' on 'oel7-ora' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'oel7-ora'
CRS-2676: Start of 'ora.asm' on 'oel7-ora' succeeded
CRS-2672: Attempting to start 'ora.DATA.dg' on 'oel7-ora'
CRS-2676: Start of 'ora.DATA.dg' on 'oel7-ora' succeeded
CRS-4000: Command Start failed, or completed with errors.

[oracle@OEL7-ORA ~]$ ps -ef|grep -i smon
grid 5192 1 0 17:59 ? 00:00:00 asm_smon_+ASM

-bash-4.2$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Sun Nov 11 17:59:48 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from dual;

ADDR INDX INST_ID CON_ID D
---------------- ---------- ---------- ---------- -
0000000011272A1C 0 1 0 X

crsctl status res | grep -v "^$" | \

awk -F "=" 'BEGIN {print ""} {printf("%s", NR%4 ? $2 "|" : $2 "\n")}' | \

sed -e 's/ *, /,/g' -e 's/, /,/g' | \

awk -F "|" '

BEGIN {

printf "%-40s %-35s %-20s %-50s\n\n", \

"RESOURCE_NAME", "RESOURCE_TYPE", "TARGET", "STATE"

}

{

split($3, trg, ",")

split($4, st, ",")

}

{

for (i in trg) {

printf "%-40s %-35s %-20s %-50s\n", \

$1, $2, trg[i], st[i]

}

RESOURCE_NAME RESOURCE_TYPE TARGET STATE

ora.DATA.dg ora.diskgroup.type OFFLINE OFFLINE

ora.LISTENER.lsnr ora.listener.type ONLINE ONLINE on oragrid

ora.asm ora.asm.type ONLINE ONLINE on oragrid

ora.cssd ora.cssd.type ONLINE ONLINE on oragrid

ora.diskmon ora.diskmon.type OFFLINE OFFLINE

ora.evmd ora.evm.type ONLINE ONLINE on oragrid

ora.ons ora.ons.type OFFLINE OFFLINE

[grid@oragrid grid]$

Wednesday, 2 May 2012

Oracle DB LINKS Metadata

Oracle DB LINKS Metadata Info ....

set linesize 300
SET LONG 9000
SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) FROM dba_db_links a;

IDENTIFIED BY VALUES is not possible in dblink from Version 11.2.0.4 onward

CREATE DATABASE LINK ANUJ1 CONNECT TO ANUJ123 IDENTIFIED BY VALUES '07B98AF7604AFAF492AC70E41300D9D003064EDDE1C5E96075479AB5A8036AF0A2AA16183623A084811F6F18EB2245A43DE9DCE1E4B736924F7434DC98A84699DE9B6787A0EE65BDCC8EEA47755648C2ADF55478060A2F4A1B33A716A97747900DABACFAC1674D73CBD1A2D9D2EED0679ABEC40D0B589AE572BAB73D6611559D' using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.000.00.00)(PORT = 1521))) (CONNECT_DATA =(SERVICE_NAME = orcl )))'

ERROR at line 1:

ORA-02153: invalid VALUES password string

CREATE DATABASE LINK ANUJ2 CONNECT TO ANUJ123 IDENTIFIED BY vihaan123 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.000.00.00)(PORT = 1521))) (CONNECT_DATA =(SERVICE_NAME = orcl )))'

Database link created.

CREATE DATABASE LINK "CDB02" CONNECT TO C##PDBCLONEDBA IDENTIFIED BY "XXXXXXX" USING '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.0)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = CDBAUX02)))';

Session altered. SQL> select * from dual@CDBAUX02; D -- x

set long 1000000 linesize 1000 pagesize 0 feedback off trimspool on verify off serveroutput on
select dbms_metadata.get_ddl('DB_LINK','ANUJ2') from dual;

CREATE DATABASE LINK "ANUJ2"
CONNECT TO "ANUJ123" IDENTIFIED BY VALUES ':1'
USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.00

0.00.00)(PORT = 1521))) (CONNECT_DATA =(SERVICE_NAME = orcl )))'

Now you will get ':1' not password value

=============

set pagesize 300

col tname for a30

select * from tab@dblink ;

CREATE DATABASE LINK dblink CONNECT TO anuj IDENTIFIED BY vihaan123 USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=irac-scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=irac)))';

insert into sys.link$ (OWNER#, NAME, CTIME, HOST, USERID, PASSWORD, FLAG, AUTHUSR, AUTHPWD, PASSWORDX, AUTHPWDX)

select OWNER#, 'DBLINK1', CTIME, HOST, USERID, PASSWORD, FLAG, AUTHUSR, AUTHPWD, PASSWORDX, AUTHPWDX

from sys.link$ where name=upper('dblink');

insert new row !!!

insert into sys.link$ (OWNER#, NAME, CTIME, HOST, USERID, PASSWORD, FLAG, AUTHUSR, AUTHPWD, PASSWORDX, AUTHPWDX)

select OWNER#, 'DBLINK1', CTIME, HOST, USERID, PASSWORD, FLAG, AUTHUSR, AUTHPWD, PASSWORDX, AUTHPWDX

from sys.link$ where name=upper('dblink');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system flush shared_pool;

System altered.

set pagesize 300

col tname for a30

select * from tab@dblink1 ;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

ANUJ1 TABLE

ANUJ_BIG_TABLE TABLE

BIN$54ZgrWd1M/zgUyL1akAE8A==$0 TABLE

BIN$66UJVfKoHNjgUyL1akBIsA==$0 TABLE

BIN$ZaQvn3FuYKPgUyL1akA0wg==$0 TABLE

BIN$ZjFB7+oJfnbgUyL1akDZsg==$0 TABLE

BV_DEMO TABLE

DEPT TABLE

EMP TABLE

SPM_TEST_TAB TABLE

T TABLE

TEST TABLE

TEST9 TABLE

TEST_LOB TABLE

TT TABLE

T_INTERIM TABLE

16 rows selected.

========

How to Check if a Database Link is Private / Public or Shared / not Shared (Doc ID 237477.1)

If the value of the OWNER column of the view DBA_DB_LINKS is PUBLIC, it is a public database link.
All other values indicate it is a private database link.

SQL> select OWNER from dba_db_links where DB_LINK = 'MYLINK';

To check for shared database links, query sys.link$

SQL> select NAME from sys.link$ where AUTHUSR is not null;

from web --

set linesize 400 pagesize 300

col DB_UNIQUE_NAME for a20

col OWNER for a20

col USERNAME for a20

col HOST for a30

col TESTSQL for a15

col DB_LINK for a20

col TESTSQL for a100

select db.DB_UNIQUE_NAME, li.owner, li.db_link, li.username, li.host

,'prompt Link='||li.db_link||';' ||chr(10)

|| case when owner = 'PUBLIC' then -- Public link -> do simple select

' select ''ok'' from dual@'||li.db_link||';'

else -- non public link -> must be run as the link owner

'alter session set current_schema = '||owner||';' ||chr(10)

||'create or replace function dblinktester return varchar2 is '||chr(10)

||' v_result varchar2(5);'||chr(10)

||'begin'||chr(10)

||' select ''ok'' into v_result from dual@'||li.db_link||';'||chr(10)

||' return v_result; '||chr(10)

||'end;'||chr(10)

||'/'||chr(10)

||'select dblinktester as result from dual;'||chr(10)

||'drop function dblinktester;'||chr(10)

end as testsql

from v$database db

cross join dba_db_links li;

======

select

db_link,

in_transaction

from

v$dblink

set serveroutput on

begin

for open_db_link in (

select

db_link,

in_transaction

from v$dblink

) loop

if open_db_link.in_transaction <> 'NO' then

dbms_output.put_line('Cannot close db link ' || open_db_link.db_link || ' as it is in a transaction.');

else

-- execute immediate 'alter session close database link ' || open_db_link.db_link;

dbms_output.put_line('db link ' || open_db_link.db_link || ' was closed.');

end if;

end loop;

end;

Tuesday, 1 May 2012

Unix cut file from line no to line

Cut Oracle Alert logfile

from line no 46267 To line no 47000

sed -n '46267,47000p' alert_CAR1.log >alert-anuj

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Saturday, 19 May 2012

Oracle biggest Object size wise

Oracle Database 10 biggest Object size wise

Oracle Temp Tablespace info

Oracle Temp File usage Info ...

Tuesday, 15 May 2012

ASM Script to check .... ASM diskgroup size, ASM disk size , CANDIDATE Disk path..

ASM Script to check .... ASM diskgroup size, ASM disk size , CANDIDATE Disk path..

Monday, 7 May 2012

ORA-29701: unable to connect to Cluster Synchronization Service

Wednesday, 2 May 2012

Oracle DB LINKS Metadata

Oracle DB LINKS Metadata Info ....

Tuesday, 1 May 2012

Unix cut file from line no to line

Oracle DBA

anuj blog Archive