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;

ORA-27037: unable to obtain file status

- It could be a missing archivelog file
- It could be a file mentioned in your init.ora file .


In case 1 :
Try to switch log file and resolve the issue. If rman is missing crosscheck the archvielog and backup once again.

Free Space available in TEMP Segment

Error : ORA-1652: unable to extend temp segment by 128 in tablespace TEMP



In database there may be permanent and temporary tablespace. The view DBA_FREE_SPACE allows us to show about how much free space in a tablespace have but DBA_FREE_SPACE shows information only about permanent tablespace. It does not show information about temporary tablespace.

v$temp_space_header and v$sort_segment are not supposed to reconcile with each other. The blocks in temp_space_header are the total number of "initialized" blocks in the tempfiles. The blocks in sort_segment are the total number of allocated and usable blocks. Which is evident in following example.

V$TEMP_SPACE_HEADER
ODSPROD1> SELECT TABLESPACE_NAME, FILE_ID, BYTES_USED, BYTES_FREE FROM V$TEMP_SPACE_HEADER;

TABLESPACE_NAME FILE_ID BYTES_USED BYTES_FREE
---------------------------------------- ---------- ---------- ----------
TEMP 3 3145719808 0
TEMP 4 3145719808 0
TEMP 5 3145719808 0
TEMP 6 1073741824 0
TEMP 7 838860800 0
TEMP 1 3145719808 0
TEMP 2 3145719808 0

7 rows selected.

V$SORT_SEGMENT

SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
total_blocks*16/1024 as total_MB,
used_blocks*16/1024 as used_MB,
free_blocks*16/1024 as free_MB
FROM v$sort_segment;

TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS TOTAL_MB USED_MB FREE_MB
---------------------------------------- ------------ ----------- ----------- -------
TEMP 2152576 0 2152576 33634 0 33634


SEGMENT USED:
SELECT b.TABLESPACE,
b.segfile#,
b.segblk#,
b.blocks,
b.blocks*16/1024 as MB,
a.SID,
a.serial#,
a.status
FROM v$session a,
v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.TABLESPACE,
b.segfile#,
b.segblk#,
b.blocks;

Oracle 9i does not release allocated TEMP segments until you shutdown the database. While the TEMP segment is allocated, it does not mean that it is unavailable for use. When a user requests a disk sort, Oracle will allocate a TEMP segment. Once that sort is done, Oracle releases this TEMP segment for future use, but does not deallocate it. When the next user requests a disk sort, Oracle does not have to allocate a new TEMP segment. It uses the same one that no user is currently using. Oracle manages this for you in 9i. And there is really only one TEMP segment in the TEMP tablespace. Multiple users can utilitize this one segment.

There have been problems in 9i where users running a TEMPORARY TEMP tablespace with TEMPFILES that is Locally Managed where Oracle does not release the sort space once it is no longer being used. This is a known bug. The workaround has been to revert back to Dictionary Managed tablespace for TEMP.

Profile

How to execute .profile without logging in
. ~/.profile


ways to switch over to oracle
/usr/local/bin/sudo su - oracle
/usr/local/bin/ss -u oracle


Some time after sudo home will not be set
$ id
uid=1001(oracle) gid=5006(dba)
$ pwd
/home/ffxfzq


in that case just do like this & later run the profile script
cd ~oracle
. ./.profile

ORA-00230: operation disallowed: snapshot control file enqueue unavailable

To determine which job is holding the conflicting enqueue.

SELECT s.sid, username AS "User", program, module,
action, logon_time "Logon", l.*
FROM v$session s, v$enqueue_lock l
WHERE l.sid = s.sid
and l.type = 'CF'
and l.id1 = 0
and l.id2 = 2;

SID User Program Module Action Logon
--- ---- -------------------- ------------------------- ---------------- ---------

- Wait until the job creating the enqueue completes
- Cancel the current job and restart it once the job creating the enqueue completes
- Cancel the job creating the enqueue

Query Tuning

xml requests going to the golden weblogic servlet aren't reaching the crossworlds servers- packets are coming out of the servlet however was stick on waiting for a response from crossworlds.

Application team was worried as query which was suppose to take 2-3 minutes was taking more than 30 mins to get executed.

Last analyzed is more than 3 months old

SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from DBA_TABLES WHERE
TABLE_NAME='GD_INVOICE_HEADER';
OWNER TABLE_NAME LAST_ANAL------------------------------ ------------------------------ ---------GDCWODS GD_INVOICE_HEADER 12-JUL-09

SQL> select count(*) from GDCWODS.GD_INVOICE_DETAIL;
COUNT(*)
----------
1605055

SQL> select count(*) from GDCWODS.GD_INVOICE_HEADER;
COUNT(*)
----------
250578

Create Explain Plan Table:
SQL> @?/rdbms/admin/utlxplan.sql

Explain Plan for the query:
SQL> EXPLAIN PLAN SET STATEMENT_ID='GD_ISSUE' FOR
select * from GDCWODS.GD_INVOICE_HEADER IH where SYSTEM_ID='NA' AND BILL_TO_PARTY='500818'
AND EXISTS (SELECT MAT_NUMBER FROM GDCWODS.GD_INVOICE_DETAIL WHERE DOCNUM=IH.DOCNUM AND
MAT_NUMBER ='FA10003') ;

Explained.

SQL> @?/rdbms/admin/utlxpls GD_ISSUE
Plan Table
--------------------------------------------------------------------------------
Operation Name Rows Bytes Cost Pstart Pstop
--------------------------------------------------------------------------------
SELECT STATEMENT 47 20K 1632
FILTER
TABLE ACCESS FULL GD_INVOIC 47 20K 1632
TABLE ACCESS FULL GD_INVOIC 1 20 1769
--------------------------------------------------------------------------------

Columns which are Indexed earlier:

SQL> select INDEX_NAME, TABLE_NAME, COLUMN_NAME from dba_ind_columns where

TABLE_NAME='GD_INVOICE_HEADER' and TABLE_OWNER='GDCWODS';

INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------------------

---------
GD_INV_HDR_IDX1 GD_INVOICE_HEADER
SYSTEM_ID

GD_INV_HDR_IDX1 GD_INVOICE_HEADER
DOCNUM


SQL> select INDEX_NAME, TABLE_NAME, COLUMN_NAME from dba_ind_columns where

TABLE_NAME='GD_INVOICE_DETAIL' and TABLE_OWNER='GDCWODS';

INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------------------

---------
GD_INV_DET_IDX1 GD_INVOICE_DETAIL
SYSTEM_ID

GD_INV_DET_IDX1 GD_INVOICE_DETAIL
DOCNUM

GD_INV_DET_IDX1 GD_INVOICE_DETAIL
SEQNUM

EXPLAIN 2:( no change is cost)
SQL> EXPLAIN PLAN SET STATEMENT_ID='GD_ISSUE1' FOR
select * from GDCWODS.GD_INVOICE_HEADER IH
where SYSTEM_ID='NA' AND BILL_TO_PARTY='500818'
AND EXISTS (SELECT 1 FROM GDCWODS.GD_INVOICE_DETAIL WHERE DOCNUM=IH.DOCNUM AND MAT_NUMBER

='FA10003') ; 2 3 4

Explained.

SQL> @?/rdbms/admin/utlxpls GD_ISSUE1

Plan Table
--------------------------------------------------------------------------------
Operation Name Rows Bytes Cost Pstart Pstop
--------------------------------------------------------------------------------
SELECT STATEMENT 47 20K 1632
FILTER
TABLE ACCESS FULL GD_INVOIC 47 20K 1632
TABLE ACCESS FULL GD_INVOIC 1 20 1769
--------------------------------------------------------------------------------

7 rows selected.

EXPLAIN 3 using Hint: (cost got increased)
/// USING HINT ///
SQL> EXPLAIN PLAN SET STATEMENT_ID='GD_ISSUE2' FOR
2 select * from GDCWODS.GD_INVOICE_HEADER IH where SYSTEM_ID='NA' AND

BILL_TO_PARTY='500818' AND EXISTS (SELECT /*+ INDEX(GD_INVOICE_DETAIL GD_INV_DET_IDX1) */

MAT_NUMBER FROM GDCWODS.GD_INVOICE_DETAIL WHERE DOCNUM=IH.DOCNUM AND MAT_NUMBER ='FA10003')

;

Explained.

SQL> @?/rdbms/admin/utlxpls GD_ISSUE2

Plan Table
--------------------------------------------------------------------------------
Operation Name Rows Bytes Cost Pstart Pstop
--------------------------------------------------------------------------------
SELECT STATEMENT 47 20K 1632
FILTER
TABLE ACCESS FULL GD_INVOIC 47 20K 1632
TABLE ACCESS BY INDEX ROGD_INVOIC 1 20 5262
INDEX FULL SCAN GD_INV_DE 1 5261
--------------------------------------------------------------------------------

8 rows selected.

Created Index on MAT_NUMBER:
SQL> create index GD_INV_DET_MAT_NUM_IDX1 on GDCWODS.GD_INVOICE_DETAIL(MAT_NUMBER);

Index created.

EXPLAIN PLAN 4 : (after indexing cost reduced drastically)
SQL> EXPLAIN PLAN SET STATEMENT_ID='GD_ISSUE3' FOR
select * from GDCWODS.GD_INVOICE_HEADER IH where SYSTEM_ID='NA' AND BILL_TO_PARTY='500818'

AND EXISTS (SELECT MAT_NUMBER FROM GDCWODS.GD_INVOICE_DETAIL WHERE DOCNUM=IH.DOCNUM AND

MAT_NUMBER ='FA10003') ;
2
Explained.

SQL> @?/rdbms/admin/utlxpls GD_ISSUE3

Plan Table
--------------------------------------------------------------------------------
Operation Name Rows Bytes Cost Pstart Pstop
--------------------------------------------------------------------------------
SELECT STATEMENT 47 20K 1632
FILTER
TABLE ACCESS FULL GD_INVOIC 47 20K 1632
TABLE ACCESS BY INDEX ROGD_INVOIC 1 20 2
INDEX RANGE SCAN GD_INV_DE 1 1
--------------------------------------------------------------------------------

8 rows selected.

Note: For 16lakhs records will take at least 5-6hrs and 25thousand record will take 1-2hrs depeding on cpu, memory, colums, etc so analyze will be taken in off peak hours

coalesce

Coalescing the tablespace will NEVER increase the the amount of free space.

It is used on dictionary managed tablespaces to coalesce two or more adjacent FREE extents into one - it will NOT create new free ones.

DP RMAN Script for Archivelog backup

DP-RMAN Script for Archivelog backup

run
{allocate channel 'dev_0' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=Database,OB2BARLIST=Server_Database_RMAN_ARC_ST_RA)'; sql 'alter system switch logfile';
crosscheck archivelog all;
backup format 'Server_Database_RMAN_ARC_ST_RA.arc'
archivelog all delete input;
}

*Crosscheck archivelog all was added to avoid backup failure due to missing archive files.

Ora-1461 Encountered When Generating Server Alert Smg-3500

1. To fix these Bug 6085625 and unpublished bug 6452485, The fixes for these two bugs are now available as Patch 6602742 or Patch 6602482 .

2. Workaround
A. Flushing the shared pool can help remove the problem cursor from the shared pool.
B. Using SESSION_CACHED_CURSORS = 0

Snapshot Purging

Recently we had to purge the snapshot as we were getting threshold alert. But Purging did not brought down the usage. Inorder to reduce threshold we had to resize the datafile of the perfstat.



When sppurge.sql is executed, all available snapshots are displayed and prompts for losnapid (low snap id ) & hisnapid (hi snap id). All snapshots between these will be deleted.




Purging requires large rollback segment. Commit command or exiting from sql will automatically commit the purging.




You can set the transaction to use particular rollback segment. Also exporting perfstat schema will ideal before purging, truncation or dropping.



SQL> @$ORACLE_HOME/rdbms/admin/sppurge




SQL > delete from stats$snapshot where snap_id <>


SQL > delete from stats$sql_summary where snap_time <>


Batch file


#!/bin/ksh

ORACLE_SID=$1

export ORACLE_SID

ORACLE_HOME=`cat /etc/oratabgrep ^$ORACLE_SID:cut -f2 -d':'

`export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH

export PATH

$ORACLE_HOME/bin/sqlplus system/manager<

select * from v$database;

connect perfstat/perfstat

define losnapid=$2

define hisnapid=$3

@sppurge

exit!

DICTIONARY VIEWS

DBA Views
SQL> SELECT * FROM DICT WHERE TABLE_NAME LIKE 'DBA_%' ORDER BY TABLE_NAME;
Dynamic views
SELECT * FROM DICT WHERE TABLE_NAME LIKE 'V$%' ORDER BY TABLE_NAME
All views
SQL> SELECT * FROM DICT WHERE TABLE_NAME LIKE 'ALL_%' ORDER BY TABLE_NAME;
User views
SQL> SELECT * FROM DICT WHERE TABLE_NAME LIKE 'USER_%' ORDER BY TABLE_NAME;

ANALYZED

Analyzed failed with following error out of 2700 tables 2200 odd got analyzed
ERROR at line 1:
ORA-12801: error signaled in parallel query server P052
ORA-08103: object no longer exists
ORA-06512: at "SYS.DBMS_STATS", line 10070
ORA-06512: at "SYS.DBMS_STATS", line 10564
ORA-06512: at "SYS.DBMS_STATS", line 10751
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1

This is known issue in earlier version of oracle 9i when estimate was used instead of compute while gathering statistics using GATHER_SCHEMA_STATS/GATHER_DATABASE_STATS command. Although it was claimed to be fixed in 9.2.0.20 i faced problem in 9.2.0.6.0 while estimating whereas compute worked great.



Analyze query:



Select OWNER,TABLE_NAME,
to_char(LAST_ANALYZED,'MM/DD/YYYY HH24:MI:SS') "last_analyzed"
from dba_tab_columns
where OWNER not in ('SYS','SYSTEM')
and LAST_ANALYZED is not null
and COLUMN_ID=1
and (SYSDATE-LAST_ANALYZED) <>



Simple analyze job script :



#!/bin/sh# Script:

analyze_only.sh

#

# Description: Analyze Database.
# Set the variables

ORACLE_SID=

ORACLE_BASE=

ORACLE_HOME=

LD_LIBRARY_PATH=$ORACLE_HOME/lib

PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_SID ORACLE_HOME PATH LD_LIBRARY_PATH


date


# Analyze Databasesqlplus -s "/ as sysdba" <<>

set echo off

set feedback off

set heading off

set pagesi 0

set linesize 125

spool analyze_all_output.lst

execute DBMS_STATS.GATHER_SCHEMA_STATS ownname=>'',cascade=>TRUE);

select '-----------------------------------------------------------' from dual;

select ' TABLE_NAME LAST ANALYZED ' from dual;

select '-----------------------------------------------------------' from dual;

select table_name, to_char(last_analyzed, 'DD-MON-YY HH24:MI:SS') from dba_tables where OWNER='' ORDER by table_name;

select '---------------------End of List--------------------------------------' from dual;

select '-----------------------------------------------------------' from dual;

select ' INDEX_NAME LAST ANALYZED ' from dual;

select '-----------------------------------------------------------' from dual;

select index_name, to_char(last_analyzed, 'DD-MON-YY HH24:MI:SS') from dba_indexes where OWNER='' ORDER by index_name;

select '---------------------End of List--------------------------------------' from dual;

exit

EOF
ANALYZELOG="analyze_all_output.lst"

oraerr='cat $ANALYZELOGgrep ORA-wc -l'

if [ $oraerr -gt 0 ]
then echo "Status as on 'date' : Analyze Failed" >> "analyze_all_output.lst"

sqlplus /nolog <

conn aa/bb@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME= xxx)))

insert into bkpstats (BKPDATE,SERVER,SRVRIP,DBNAME,BKPTYPE,BKPSTATS) values (SYSDATE,'','','','DB ANALYZE','JOB FAILED');

commit;
EOF
exit;

else

echo "Status as on 'date' : Analyze Successfull" >> "analyze_all_output.lst"
sqlplus /nolog <<>aa/bb@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME= xxx)))

insert into bkpstats (BKPDATE,SERVER,SRVRIP,DBNAME,BKPTYPE,BKPSTATS) values (SYSDATE,'','','','DB ANALYZE','JOB SUCCESSFUL');

commit;
EOF
exit;

fi

mailx -s "$ORACLE_SID Analyze is complete." "xx@xx.com,yy@yy.com" < $ date

RMAN CROSSCHECK

CROSSCHECK command verifies if the object available or expired by examining the file in disk or sbt. CROSSCHECK does not delete files that it does not find. It only updates the repository.

Following error registered in log file while it failed to find the controlfile during scheduled backup

Error message:
RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
List of Mismatched objects
==========================
Object Type Filename/Handle
--------------- ---------------------------------------------------
Datafile Copy C:\PROGRAM FILES\OMNIBACK\TMP\CTRL_ODPI.DBF

Examples to crosscheck:

RMAN> CROSSCHECK CONTROFILE;
RMAN> CROSSCHECK BACKUP;
RMAN> CROSSCHECK COPY;
RMAN> CROSSCHECK backup of database;
RMAN> CROSSCHECK backup of controlfile;
RMAN> CROSSCHECK archivelog all;

Query media manager
SBT
RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt;
RMAN> CROSSCHECK BACKUP DEVICE TYPE sbt COMPLETED BETWEEN '01-MAY-09' AND '30-JUNE-09';

DISK
RMAN> CROSSCHECK BACKUP DEVICE TYPE DISK COMPLETED BETWEEN '01-MAY-09' AND '30-JUNE-09';

For default channel below command can be used
RMAN> CROSSCHECK BACKUP COMPLETED BETWEEN '01-MAY-09' AND '30-JUNE-09';

Listener

How to Check If the Password is Set to Listener:

From the listener control prompt, issue the command STATUS. If the Security setting is shown as ON, then the password is set for the listener.


LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ANANDA)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production
Start Date 05-JUL-2003 15:23:19
Uptime 0 days 6 hr. 24 min. 56 sec
Trace Level off
Security ON
SNMP OFF
Listener Parameter File d:\ora9\network\admin\listener.ora
Listener Log File d:\ora9\network\log\listener.log
Services Summary...
ANANDA has 1 service handler(s)
ANANDA has 2 service handler(s)
ANANDA has 2 service handler(s)
The command completed successfully
LSNRCTL>

Once password is set any administrative command apart from START, STATUS and HELP require password. It's the least security. Password is set by command change_password

LSNRCTL>change_password

Set password for administrative task

LSNRCTL> set password 607CB