DBID

Find DBID ?

1. select name, dbid from v$database
2. strings file_name |grep MAXVALUE, (In case of SYSTEM datafile)
3. strings file_name |grep MAXVALUE (In case of UNDO datafile)
4. strings file_name |grep 'database id'
5. when you login to rman


==================
Example
======================================================================
SQL> select name, dbid from v$database;
NAME            DBID
--------- ----------
AON       3575888991
======================================================================
[oracle@rusk BACKUP]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 27 11:52:10 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: AON (DBID=3575888991)
======================================================================
UNDO BACKUP PIECE
----------------------
RMAN> backup as copy datafile 2;
Starting backup at 27-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u02/oradata/AON/undotbs01.dbf
output file name=/u04/app/oracle/product/11.2.0/dbs/data_D-AON_I-3575888991_TS-UNDOTBS_FNO-2_08o5io5m tag=TAG20130327T111710 RECID=1 STAMP=811163885
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
Finished backup at 27-MAR-13
RMAN> exit
[oracle@rusk BACKUP]$ strings /u04/app/oracle/product/11.2.0/dbs/data_D-AON_I-3575888991_TS-UNDOTBS_FNO-2_08o5io5m |grep MAXVALUE
3575888991, MAXVALUE
3575888991, MAXVALUE
======================================================================
SYSTEM BACKUP PIECE
---------------------------
[oracle@rusk BACKUP]$ strings backup01o59nur | grep MAXVALUE,
3575888991, MAXVALUE,
======================================================================
SYSAUX BACKUP PIECE
---------------------------
strings /u04/app/oracle/product/11.2.0/dbs/09o5iqh9_1_1 | grep 'database id'
ADDM:3575888991_1_26GADDM auto run: snapshots [25, 26],  instance 1,  database id 3575888991
ADDM:3575888991_1_21GADDM auto run: snapshots [20, 21],  instance 1,  database id 3575888991
======================================================================

what is exrc

exrc is just like profile setting. whenever vi editor is opened the first thing editor does is read exrc file and apply the setting. These setting or customization last  only till the file is open  .exrc is stored in home directory of the user. (/home/.exrc)

common setting in exrc
set command : set command like vi like set number, set list
Abbrevation abbrevate widely/commonly used word
mapping key : mapping is nothing but hot keys

$vi home/.exrc
set number
set list
ab kr@ krishna@yahoo.com         
map Q :q!
save and exit
--------------
Note :
ab kr@ krishna@yahoo.com when ever you type kr@ it will automaticall recorrect as krishna@yahoo.com
map Q :q!  -> to quite just type Q

Vi Cheat Sheet

Today we will talk about Vi editor one of the essential text editor for oracle dba. There are many text editor which are available however most of us use Vi predominately. Some of the other well know alternative editors are Vim and emacs. Vi was originally written by Bill Joy along with Chuck Haley original called as ex after undergoing different stage it transformed to know as Vi (Visual ex)

Create Vi text file
All we have to do is run command vi

$ vi file.txt
Editor open with ~ tilde character indicating there is no text. to insert the text and save. To save you have . File is created, verify by listing the file ls cat or more command.

Okey now we will list out the command on how to edit the vi file

================
OPEN file
================
1. View file.txt open the file in read only mode
2. vi -R                                   again to open file in read only mode
3. vi +n file.txt                        n is number, open the file at nth line
4. vi +                                     open file at last line
5. vi /oracle file.txt                 open file at first oracle occurance

================
SAVE File
================
1. ZZ Save and quit
2. :wq save and quit
3. :x save and quit
4. :w save without quiting
5. :q quit without saving
6. :q! quite, overriding file protection
7. :wq! save and quitem, overriding the file protection

================
Insert
================
1. i insert text in front of cursor
2. I insert text at the beggining of line
3. a insert text after the cursor
4. A insert text at the end of the line
5. o insert text below current line
6. O insert text above current line

================
Moving
================
1. j (down arrow) move down line
2. k (up arrow) move up
3. h (left arrow) move left
4. l (right arrow) move right
5. 1G go to first line
6. 10G go to 10th line
7. $G go to last line
8. 0 go to start of line
9. $ go to end of line

================
copy and paste
================
1. yy copy current line (yy - copy or yank)
2. 3yy 3 lines copied
3. p paste the copied lines below current line
4. P paste the copied lines above current line

================
Deleting
================
1. dd delete current line
2. x delete character that cursor is on
3. X deleter character to the left of cursor
4. 5dd delete 5 line from the current line
5. D delete to the end of line from the cursor point

================
Manipulate
================
1. r Replace the character that the curser is on with the next character you type.
2. ~ Change the case of a character.
3. cc Delete the current line and insert text.
4. C Delete to the end of the line and insert text.
5. c$ Delete to the end of the line and insert text. (same as D with insert option)
6. cw Delete to the end of the word and insert text.
7. R Type over the characters in the current line.
8. s Delete the current character and insert text.
9. S Delete the current line and insert text.

================
Search
================
1. ESC KEY + /oracle serach oracle, n to serach next occurance of sting, N to search backward for previous occurance
2. :%s/oracle/mysql/g all oracle string are replaced by mysql in the file

================
undo
================
1. u undo the previous action
2. :e! undo all the edis to the file (instead of quiting and opening)

================
Display Line number
================
1. set number display line number
2. set nu same as above - display line number
3. set nonumber do not display number
4. set nonu same as above

================
Repeat
================
1. . period will repeat the previous action, like you have deleted 10 line previously it will do same action on typing period

================
Joining
================
1. J Join the line
2. 4J Join 4 lines

================
Default Editor
================
Suppose you want to edit wrong typed query under SQL prompt
1. sql> set default_EDITOR=vi
2. For permanent change define variable _EDITOR=Vi in glogin.sql ($ORACLE_HOME/sqlplus/admin/glogin or login)
3 then type ed to open vi editor

================
Vi Setting
================
Configure Vi setting. Create file .exrc under home directory this will ensure vi is opened with setting incorporated in exrc file

create file .exrc under home directory
insert
set number
save and exit
now every time you open file using vi, file will be displayed with line number

Characterset Convertion

1.Shutdown database
2.Backup the database
3. startup
4. run csscan -
C:\Documents and Settings\lingappk>csscan system/oracle full=y tochar=AL32UTF8 array=1024000 process=10


Character Set Scanner v2.0 : Release 10.1.0.4.0 - Production on Wed Nov 11 03:10:14 2009

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
CSS-00107: Character set migration utility schema not installed

Scanner terminated unsuccessfully.

Error occured due to csmig schema is not present in the datbase. Create csmig by running default script available in oracle home folder

5.
SQL > @%oracle_home%\rdbms\admin\csminst.sql
(This script will drop csmig schema if exist and recreate will all the required privileges)

6.
csscan ”/sys as sysdba\” LOG=charconv.log FULL=Y CAPTURE=Y TOCHAR=AL32UTF8 ARRAY=1024000 PROCESS=10

OR

csscan "/sys as sysdb \" FULL=Y
(provide values when prompted)

. process 2 scanning SYS.TYPED_VIEW$[AAAAA/AABAAAAHRAAA]
. process 7 scanning SYS.UGROUP$[AAAAA5AABAAAAGpAAA]
. process 1 scanning SYS.USER$[AAAAAKAABAAAABZAAA]
. process 6 scanning SYS.FILE$[AAAAARAABAAAABxAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

7.
Run following script for the convertion
SQL> shutdown immediate
SQL> startup restrict
SQL> @%oracle_home%\rdbms\admin\csalter.plb

OR

SQL> shutdown immediate
SQL> startup mount
SQL> alter system enable restricted session;
SQL> alter system set JOB_QUEUE_PROCESSES=0 scope=memory;
SQL> alter system set AQ_TM_PROCESS=0 scope=memory;
SQL> alter database open;
SQL> alter database character set AL32UTF8;
alter database character set AL32UTF8*ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

if u want to change the characterset then the new characterset should be a superset of the older one otherwise create a new database with different characterset

SQL> SELECT * FROM nls_database_parameters WHERE parameter like '%CHARACTERSET';
PARAMETER VALUE
NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16

Metadata of Table/Index

1. Using the import/export is one method of getting the metadata where while exporting row=n will be defined and while importing show=y should be provide but we need to lot of editing to get formated metadata but most easiest and quick one is using DMBS_METADATA

Export/Import
c:\>exp file=motoexp.dmp log=motoexp.log rows=n buffer=1000000
c:\>imp file=motoexp.dmp log=motoim.log show=y rows=n indexfile=moto.sql buffer=1000000

DBMS_METADATA
SELECT DBMS_METADATA.get_ddl('object_type','object_name','owner') from dual;

SQL> set serveroutput on
SQL> set long 10000
SQL> set pages 0
SQL> select DBMS_METADATA.get_ddl ('TABLE','JOBS','HR') from dual;

CREATE TABLE "HR"."JOBS" ( "JOB_ID" VARCHAR2(10),
"JOB_TITLE" VARCHAR2(35) CONSTRAINT "JOB_TITLE_NN" NOT NULL ENABLE, "MIN_SALARY" NUMBER(6,0),
"MAX_SALARY" NUMBER(6,0),
CONSTRAINT "JOB_ID_PK" PRIMARY KEY ("JOB_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXAMPLE" ENABLE )
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXAMPLE"

Find all indexes on a particular table using DBMS_METADATA

SQL> select DBMS_METADATA.get_dependent_ddl('INDEX','JOBS','HR') from dual;

CREATE UNIQUE INDEX "HR"."JOB_ID_PK" ON "HR"."JOBS" ("JOB_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXAMPLE"

Find all referencial constraint on a table

SQL> select DBMS_METADATA.get_dependent_dd ('REF_CONSTRAINT','EMPLOYEES','HR') from dual;

ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE

Find granted privileges for a particual schema using DBMS_METADATA

SQL> select DBMS_METADATA.get_granted_ddl('SYSTEM_GRANT','HR') from dual;
GRANT UNLIMITED TABLESPACE TO "HR"

SQL> select DBMS_METADATA.get_granted_ddl('ROLE_GRANT','HR') from dual;
GRANT "CONNECT" TO "HR"
GRANT "RESOURCE" TO "HR"

SQL> select DBMS_METADATA.get_granted_ddl('OBJECT_GRANT','HR') from dual;
GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "HR"

ORA-00604: error occurred at recursive SQL level 1

check out if the next error on the stack can be corrected

ORA-00020: maximum number of processes (%s) exceeded

User & Application users were unable to connect to database due to error ORA-00020 max number of processes. No new connection could be establised.

check for following
1. Process parameter setting in init file :
$ORACLE_HOME/dbs/initfile
processes = 200

2. Number of database connections
ps -aef grep oracle grep LOCAL grep wc -l
192

3. Number of database connections
ps -ef grep wc -l
203

Workaround 1:
kill some of the connections
Unix
kill -9

window
orakill

4. shutdown immediate / shutdown abort / startup force

Note 1:
When instance is out of processes, non-SYSDBA will get error with ORA-0020, and a SYSDBA connection will error out with "Connected to idle instance."

Note 2:
if there is no prior connection before ORA-00020 error one migh face problem in shutting down the instance.
SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-00020: maximum number of processes (%s) exceeded
SQL> exit

Workaround 2:

1.Kill couple of old connections at OS level
2.Connect to database and determine which user is consuming more connections and kill the same.
Query
select s.osuser,p.spid,s.username,s.sid,s.serial#,to_char(s.logon_time,'Dy dd Mon HH24:MI:SS') start_time,s.status,s.machine,s.MODULEfrom V$PROCESS p,V$SESSION s where s.paddr = p.addr and s.username is not null;
3. alter system kill session 'SID, SERAL#';

Increasing the Process Parameter 1:

show parameter processes;
create pfile from spfile;
shut immediate;
startup mount;
alter system set processes=500 scope=spfile;
create pfile from spfile;
alter database open;
shutdown immediate;
startup;
show parameter processes;

Increasing the Process Parameter 2:

alter system set processes=500 scope=spfile;
shutdown immediate;
startup;