Cost Based Optimizer (CBO) and Database Statistics

Cost Based Optimizer (CBO) and Database Statistics


The mechanisms and issues relating to maintenance of internal statistics are explained below:

* Analyze Statement
* DBMS_UTILITY
* DBMS_STATS
* Scheduling Stats
* Transfering Stats
* Issues


1) Analyze Statement
select 'ANALYZE TABLE '||Owner||'.'||table_name||' compute statistics;'
from sys.all_tables where table_name!='_default_auditing_options_'
/

select 'ANALYZE INDEX '||Owner||'.'||index_name||' compute statistics;'
from sys.all_indexes
/

2) DBMS_UTILITY

EXEC DBMS_UTILITY.ANALYZE_SCHEMA('LDBO','COMPUTE');

EXEC DBMS_UTILITY.ANALYZE_SCHEMA('LDBO', 'ESTIMATE')

3) DBMS_STATS
EXEC DBMS_STATS.gather_schema_stats('LDBO');

4) Scheduling Stats

SET SERVEROUTPUT ON
DECLARE
l_job NUMBER;
BEGIN

DBMS_JOB.submit(l_job,
'BEGIN DBMS_STATS.gather_schema_stats(''LDBO''); END;',
SYSDATE,
'SYSDATE + 1');
COMMIT;
DBMS_OUTPUT.put_line('Job: ' || l_job);
END;
/


-----EXEC DBMS_JOB.remove(X);
-----COMMIT;


5) Transfering Stats
It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA:

EXEC DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE');
EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');


6) Issues

* Exclude dataload tables from your regular stats gathering, unless you know they will be full at the time that stats are gathered.
* I've found gathering stats for the SYS schema can make the system run slower, not faster.
* Gathering statistics can be very resource intensive for the server so avoid peak workload times or gather stale stats only.
* Even if scheduled, it may be necessary to gather fresh statistics after database maintenance or large data loads.

7)

select table_name, avg_row_len, chain_cnt, num_rows,last_analyzed from dba_tables where owner ='LDBO' order by last_analyzed desc;

Explain Plan

SQL> @ORACLE_HOME\rdbms\admin\utlxplan.sql

This creates the PLAN_TABLE for the user executing the script.

Run EXPLAIN PLAN for the query to be optimized:

1)explain plan for
select * from accounts;

2)select * from PLAN_TABLE; ---to check output


3)explain plan
SET STATEMENT_ID = 'ACC' FOR
select * from accounts;



4)

SELECT cardinality "Rows",
lpad(' ',level-1)||operation||' '||
options||' '||object_name "Plan",cost "Cost by CBO",bytes/1024/1024 "MB",time "Time By CBO"
FROM PLAN_TABLE

CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
START WITH id = 0
AND statement_id = 'ACC'
ORDER BY id;



5)
SELECT LPAD(' ', 2 * (level - 1)) ||
DECODE (level,1,NULL,level-1 || '.' || pt.position || ' ') ||
INITCAP(pt.operation) ||
DECODE(pt.options,NULL,'',' (' || INITCAP(pt.options) || ')') plan,
pt.object_name,
pt.object_type,
pt.bytes,
pt.cost,
pt.partition_start,
pt.partition_stop
FROM plan_table pt
START WITH pt.id = 0
AND pt.statement_id = '&1'
CONNECT BY PRIOR pt.id = pt.parent_id
AND pt.statement_id = '&1';

The following diagram demonstrates the procedures for running TRACE versus EXPLAIN PLAN:

TRACE It takes four hours to TRACE a query that takes four hours to run. Set up Init.ora Parameters Create PLAN_TABLE table Run Query Statement is executed PLAN_TABLE is populated Run TKPROF Output shows disk and memory reads in addition to EXPLAIN PLAN output

EXPLAIN PLAN It takes less than a minute to EXPLAIN PLAN a query that takes four hours to run. Create PLAN_TABLE table Explain Query PLAN_TABLE is populated Query PLAN_TABLE Output shows EXPLAIN PLAN

ODBC Trace for ODBC Applications

check in Control Panel ->Administrative -> ODBC Manager -> Tracing(tab) -> click on Stop Tracing

Always remember to stop it

Note:ODBC Tracing to SQL.LOG Can Slow SQL or Consume All Space

SQL Trace / TKPROF

To start a SQL trace for the current session, execute:
ALTER SESSION SET sql_trace = true;

ALTER SESSION SET sql_trace = true;
or DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);


ALTER SESSION SET tracefile_identifier = mysqltrace;

DBA's can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to trace problematic database sessions. Steps:
select sid, serial# from sys.v_$session where .....


SID SERIAL#
---------- ----------
8 13607

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

Enable Timed Statistics – This parameter enables the collection of certain vital statistics such as CPU execution time, wait events, and elapsed times. The resulting trace output is more meaningful with these statistics. The command to enable timed statistics is:

SQL> ALTER SYSTEM SET timed_statistics = true;

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

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
SQL> execute dbms_system.set_sql_trace_in_session(8, 13607, true);


Ask user to run just the necessary to demonstrate his problem.
Disable tracing for your selected process:

execute dbms_system.set_sql_trace_in_session(8,13607, false);


Look for trace file in USER_DUMP_DEST


ALTER SYSTEM SET sql_trace = false SCOPE=MEMORY;


Identifying trace files

Trace output is written to the database's UDUMP directory.

The default name for a trace files is INSTANCE_PID_ora_TRACEID.trc where:

* INSTANCE is the name of the Oracle instance,
* PID is the operating system process ID (V$PROCESS.OSPID); and
* TRACEID is a character string of your choosing.


---http://www.ordba.net/Tutorials/OracleUtilities~TKPROF.htm---

Trace output is quite unreadable. However, Oracle provides a utility, called TKProf, that can be used to format trace output.


SET ORACLE_SID=SNS6


tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
[aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
[explain=user/password] [record=filename4] [width=n]

C:\> tkprof C:\oracle\product\10.2.0\admin\sns1011\udump\sns6_ora_976.trc C:\oracle\product\10.2.0\admin\sns1011\udump\sns6_ora_976.prf explain = ldbo/ldbo sys=no sort = (PRSDSK,EXEDSK,FCHDSK,EXECPU,FCHCPU)




Some of the things to look for in the TKPROF output are listed in this table:

Problems	Solutions
High numbers for the parsing	The SHARED_POOL_SIZE may need to be increased.
The disk reads are very high	Indexes are not used or may not exist.
The "query" and/or "current" (memory reads) are very high	Indexes may be on columns with high cardinality (columns where an individual value generally makes up a large percentage of the table). Removing or suppressing the index may increase performance.
The parse elapse time is high	There may be a problem with the number of open cursors.
The number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows	This could be a sign of an index with a poor distribution distinct keys (unique values for a column). Or this could also be a sign of a poorly written statement.
If the number of misses in the library cache during parse is greater than 1	This is an indication that the statement had to be reloaded. You may need to increase the SHARED_POOL_SIZE in the init.ora.

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

Date Format

mkdir c:\backupfolders\%date:~0,2%
mkdir c:\backupfolders\%date:~3,2%
mkdir c:\backupfolders\%date:~6,4%
mkdir c:\backupfolders\%date:~8,2%

mkdir c:\backupfolders\%date:~0,2%.%date:~3,2%.%date:~6,4%

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

Allow IP to sending mail (Relay on)

cat /etc/tcp.smtp



login as: root
root@10.100.0.77's password:
Last login: Wed Jul 14 16:28:50 2010 from 172.16.203.28
[root@mail ~]# cd /etc/tcp.smtp
-bash: cd: /etc/tcp.smtp: Not a directory
[root@mail ~]# cat /etc/tc
tcp.smtp tcp.smtp.cdb tcsd.conf
[root@mail ~]# cat /etc/tcp.smtp
127.:allow,RELAYCLIENT=""
10.100.0.91:allow,RELAYCLIENT=""
#Rachit#
172.16.203.15:allow,RELAYCLIENT=""
#Endosor Server - DailyReports#
10.100.0.26:allow,RELAYCLIENT=""
[root@mail ~]#
------------------------

Content Duplicacy for Multiple Domain with same website

multiple domains point to the one URL

redirect all your secondary domain names to your primary domain name is to do it at your domain name registrar level. Instead of setting the DNS of all your domains to your web hosting account, just set your primary domain name.
n. Log into your domain registrar's website, and look for either "URL Forwarding", "Forwarding", "Redirection" or something to that effect.


I suggest you three solutions that have different effects but are good for the scope:

*

Insert this rule in the robots.txt file only of the multiple domains:

User-agent: *

Disallow: /
*

Apply an permanent redirect from the multiple domains to the main site, the request www.youbusiness.fr (or other) will be redirected to the .COM domain
* Create a landing page to be published in the multiple domains
The difference between a 301 and a 302 is that a 301 status code means that a page has permanently moved to a new location, while a 302 status code means that a page has temporarily moved to a new location.

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

Move website to new Domain

Today Google Webmaster team has roll out new Webmaster Tool Interface for all. From today onwards when you login to Google Webmasters you will see a new Webmaster Interface. Along with new Google Webmaster Interface new feature in Google Webmaster Tools named as Change of Address.

This feature will be extremely useful for those who are planning to move site to new domain. Until today their was no way to notify Google of changes domain name but with this new feature of Google Webmaster you can notify Google to update the index to reflect your new URL.

When you login to your Google Webmaster account you will find change of address option under site configuration navigation links. Here are instructions displayed under Change of Address.

1. Setup a New website

2. Redirect all traffic from the old site with the help of 301 Permanent Redirect

3. Add your new site to Google Webmasters tools on same account of your Old domain

4. Update New URL for your old domain

Dynamic Title

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



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

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

Header Tag Optimization

Headings Tag in HTML:

Headings are defined with the

to

tags.

defines the largest heading and used for main heading

,

,

,

,

defines the smallest heading and used for sub-headings.
Heading Tags

to

are one of the important factors for On Page Optimization. Search engine give more importance for indexing and for ranking well in search results pages.

Header tag is important for visitors also, since heading tag tells both search engine and visitors what the content is all about.

Heading tags are represented as

to

.

is considered the most important tag by search engine and

, the smallest and the least important.

Example of Header tag is:
Head Tag

Head Tag

Head Tag

Head Tag

Head Tag

Head Tag

Tips for Optimizing the Header Tag

to

:

*
Header tags should contain your target keywords along with any other descriptive text relevant to the content of the page.
*
Search engines give more importance to Header tags to what a web page is all about.
*
The Google ranking algorithm things that if you're using a

tag, then the text in between this header tag must be more important than the content on the rest of the page.
*
Use your most target keyword phrases in heading tags on your webpage.
*
By default, H1 tags aren't formatting, so when we are using a CSS style to override the default



*
Use those keywords which are used in Title and Meta tags like description tag and keyword tag. Search engine preferred those keywords which are used in heading tags.
*
Use at least 2-4 heading tags such as

,

,

,

on each page of your website.
*
User those target keyword in header tag which describe the content of the webpage.
*
Add highly relevant keywords in

tag, as it is weighted most than other heading tags.
*
Analyze the relevancy of your keywords and place most important keyword in

tag, less important to

, further less important to

and ultimately least important keywords to

.

import job monitoring(How fast import running)

SELECT SUBSTR(sql_text, INSTR(sql_text,'INTO "'),30) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM sys.v_$sqlarea WHERE sql_text like 'INSERT %INTO "%'
AND command_type = 2
AND open_versions > 0;

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

Oracle Table Size Growth Monitoring

select
segment_name table_name,
sum(bytes)/(1024*1024) table_size_meg
from
user_extents
where
segment_type='TABLE'
and
segment_name = 'TBLDIGITALSIGNEDREPORTS'
group by segment_name;


-------------------TABLE SIZE GROWTH WITH TIME-------

select
to_char(begin_interval_time,'DD-MM-YYYY hh24:mm') TIME,
object_name TABLE_NAME,
space_used_total/1024/1024 SPACE_USED_MB
from
dba_hist_seg_stat s,
dba_hist_seg_stat_obj o,
dba_hist_snapshot sn
where
o.owner = 'LDBO'
and
s.obj# = o.obj#
and
sn.snap_id = s.snap_id
and
TRIM(object_name) LIKE 'TBLDIGITALSIGNEDREPORTS'
order by
begin_interval_time DESC;



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



select sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Schema Size (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and b.object_name ='TBLDIGITALSIGNEDREPORTS'
and c.owner = 'LDBO'
and space_used_delta > 0;

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

Bulk Updation in Address: Move one column data to another

Declare

cursor CUR_TAB is

SELECT rowid,a.*

FROM accountaddressdetail11 a WHERE CODE LIKE 'In%';

l_b number:=0;

Begin

for CUR_REC in CUR_TAB

loop

Begin

if CUR_REC.TEL2 is not null then

if CUR_REC.TEL1 is null or LENGTH(TRIM(CUR_REC.TEL1)) is null then

update accountaddressdetail11 set TEL1=CUR_REC.TEL2,TEL2=' ' where rowid=CUR_REC.rowid and TEL2=CUR_REC.TEL2 ;

elsif CUR_REC.TEL3 is null or LENGTH(TRIM(CUR_REC.TEL3)) is null then

update accountaddressdetail11 set TEL3=CUR_REC.TEL2,TEL2=' ' where rowid=CUR_REC.rowid and TEL2=CUR_REC.TEL2;

elsif CUR_REC.MOBILE is null or LENGTH(TRIM(CUR_REC.MOBILE)) is null then

update accountaddressdetail11 set MOBILE=SUBSTR(CUR_REC.TEL2,1,12),TEL2=' ' where rowid=CUR_REC.rowid and TEL2=CUR_REC.TEL2;

elsif CUR_REC.PAGER is null or LENGTH(TRIM(CUR_REC.PAGER)) is null then

update accountaddressdetail11 set PAGER=SUBSTR(CUR_REC.TEL2,1,12),TEL2=' ' where rowid=CUR_REC.rowid and TEL2=CUR_REC.TEL2;

end if;

end if;

Exception

when others then

null;

End;

end loop;

End;

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

Bulk Updation: update EmailCC column

Declare
cursor CUR_BULK_UPDATE is
SELECT rowid,a.* FROM ACCOUNTEMAILDETAIL a WHERE CODE NOT LIKE '%M';
l_b number:=0;
Begin
for CURSOR_RECURSION in CUR_BULK_UPDATE
loop
Begin
if CURSOR_RECURSION.EMAIL is not null then
update ACCOUNTEMAILDETAIL set CEMAILCC= (trim(LOWER(CURSOR_RECURSION.OOWNCODE))) ||'@uniconglobal.com' where rowid=CURSOR_RECURSION.rowid and

CURSOR_RECURSION.EMAIL ! = ' ' and CURSOR_RECURSION.EMAIL not like '%@uniconglobal.com%';
end if;
Exception
when others then
null;
End;
end loop;
End;
/
COMMIT;

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

Oracle 11g Standby Database

Oracle Standby database on same machine (for testing)

-----------------I. Before you get started:-------------------

1. Make sure the operating system and platform architecture on the primary and standby systems are the same;

2. Install Oracle database software without the starter database on the standby server and patch it if necessary. Make sure the same Oracle software release is used on the Primary and Standby databases, and Oracle home paths are identical.

---------II. On the Primary Database Side:---------------------

Enable forced logging on your primary database:
Select FORCE_LOGGING from V$DATABASE;
ALTER DATABASE FORCE LOGGING;


1) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:
SQL> select bytes from v$log;

BYTES
----------
52428800
52428800
52428800

2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile;

3) Create standby Redo log groups.
My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;

4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log;

5) Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;

6) Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.

A) Create pfile from spfile for the primary database:

SQL>create pfile='C:\app\kshitij\product\11.1.0\db_1\database\INITORCL.ORA' from spfile;

B) Edit INITORCL.ora to add the new primary and standby role parameters:


select * from v$parameter where name like '%log_archive_format%';
select * from v$parameter where name like '%standby%';
select * from v$parameter where name like '%remote_archive_enable%';
select * from v$parameter where name like '%log_archive_dest_state_%';
select * from v$parameter where name like '%convert%';

----------------------INITORCL.ORA------------------

orcl.__db_cache_size=536870912
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='c:\app\kshitij'
orcl.__pga_aggregate_target=301989888
orcl.__sga_target=939524096
orcl.__shared_pool_size=352321536
*.audit_file_dest='c:\app\kshitij\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='c:\app\kshitij\oradata\orcl\control01.ctl','c:\app\kshitij\oradata\orcl\control02.ctl','c:\app\kshitij\oradata\orcl\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='orcl'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclsby)'
*.db_recovery_file_dest='c:\app\kshitij\flash_recovery_area'
*.LOG_ARCHIVE_DEST_1='LOCATION=c:\app\kshitij\flash_recovery_area\orcl\onlineLOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_2='SERVICE=orclsby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclsby'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.FAL_SERVER=orclsby
*.FAL_CLIENT=orcl
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='C:\app\kshitij\oradata\orclsby','C:\app\kshitij\oradata\orcl'
*.LOG_FILE_NAME_CONVERT='e:\app\kshitij\oradata\orclsby','C:\app\kshitij\oradata\orcl','e:\app\kshitij\flash_recovery_area\orclsby\onlineLOG','c:\app\kshitij\flash_recovery_area\orcl\onlineLOG'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='c:\app\kshitij'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=1229979648
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
-------------------


C. Create spfile from pfile, and restart primary database using the new spfile.

SQL> shutdown immediate;
SQL> startup nomount pfile='C:\app\kshitij\product\11.1.0\db_1\database\INITORCL.ORA';
SQL>create spfile from pfile='C:\app\kshitij\product\11.1.0\db_1\database\INITORCL.ORA';
SQL>shutdown immediate;
SQL>Startup;


7) CREATE STANDBY CONTROLFILE
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database create standby controlfile as 'C:\SBY.ORA';
SQL>ALTER DATABASE OPEN;


8) take target db backup using rman and restore to standby
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\%F';

RMAN TARGET SYS/ORACLE@ORCL
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'C:\%U';


----------------III. On the Standby Database Site:---------------

1. CREATE STANDBY DATABASE WITHOUT STARTUP DATABASE

2. Create directory STRUCTURE SAME AS PRIMARY DATABASE for data files. ALSO Create directory (multiplexing) for online logs.
create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.

3. Copy the Primary DB pfile to Standby server and rename/edit the file.

1) Copy INITORCL.ora from Primary server to Standby server, to database folder E:\app\kshitij\product\11.1.0\db_1\database.

2) Rename it to INITORCLSBY.ORA, and modify the file as follows

NOTE: The db_name in the standby's init file should be the same as the primary database.
--------------------------INITORCLSBY.ORA----------------------
orcl.__db_cache_size=536870912
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='e:\app\kshitij'
orcl.__pga_aggregate_target=301989888
orcl.__sga_target=939524096
orcl.__shared_pool_size=352321536
*.audit_file_dest='e:\app\kshitij\admin\orclsby\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='e:\app\kshitij\oradata\orclsby\control01.ctl','e:\app\kshitij\oradata\orclsby\control02.ctl','e:\app\kshitij\oradata\orclsby\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='orclsby'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orclsby,orcl)'
*.db_recovery_file_dest='e:\app\kshitij\flash_recovery_area'
*.LOG_ARCHIVE_DEST_1='LOCATION=e:\app\kshitij\flash_recovery_area\orclsby\onlineLOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclsby'
*.LOG_ARCHIVE_DEST_2='SERVICE=orclsby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclsby'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.FAL_SERVER=orcl
*.FAL_CLIENT=orclsby
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='c:\app\kshitij\oradata\orcl','e:\app\kshitij\oradata\orclsby'
*.LOG_FILE_NAME_CONVERT='c:\app\kshitij\oradata\orcl','e:\app\kshitij\oradata\orclsby','c:\app\kshitij\flash_recovery_area\orcl\onlineLOG','e:\app\kshitij\flash_recovery_area\orclsby\onlineLOG'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='e:\app\kshitij'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclsbyXDB)'
*.memory_target=1229979648
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

--------------------------------------------------------
4. Copy the Primary password file to standby and rename it to pwdSBY.ora.
TO E:\app\kshitij\product\11.1.0\db_1\database.

5. Copy the standby control file 'SBY.ORA' from primary to standby destinations ;

6. For Windows, create a Windows-based services (optional):
$oradim –NEW –SID ORCLSBY –STARTMODE manual

7. Configure listeners for the primary and standby databases.

--------------TNSNAMES.ORA--PRIMARY---------
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Kshitij-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(Sid = orcl)
)
)
ORCLSBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Kshitij-PC)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(Sid = orclsby)
)
)

-----------------LISTENER.ORA----PRIMARY--------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = EXTPROC)
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\app\kshitij\product\11.1.0\db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = C:\app\kshitij\product\11.1.0\db_1)
(SID_NAME = orcl)
)

)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Kshitij-PC)(PORT = 1521))
)
)



--------------TNSNAMES.ORA--STANDBY---------


ORCLSBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kshitij-PC)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclsby)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kshitij-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)


-----------------LISTENER.ORA----STANDBY--------

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = EXTPROC1)
(SID_NAME = PLSExtProc1)
(ORACLE_HOME = e:\app\kshitij\product\11.1.0\db_1)
)
(SID_DESC =
(GLOBAL_DBNAME = orclsby)
(ORACLE_HOME = e:\app\kshitij\product\11.1.0\db_1)
(SID_NAME = orclsby)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Kshitij-PC)(PORT = 1522))
)
)



------------------------
8.
RESTART LISTENER ON PRIMARY AND STANDBY DATABASE
LSNRCTL>RELOAD


CHECK TNSPING ON PRIMARY AND STANDBY DATABASE
$tnsping ORCL
$tnsping ORCLSBY



9. On Standby server, setup the environment variables to point to the Standby database.

Set up ORACLE_HOME and ORACLE_SID.

10. Start up nomount the standby database and generate a spfile.

SQL>startup nomount pfile='E:\app\kshitij\product\11.1.0\db_1\database\INITORCLsby.ORA';
SQL>create spfile from pfile='E:\app\kshitij\product\11.1.0\db_1\database\INITORCLsby.ORA';
SQL>shutdown immediate;
SQL>startup mount;

11.
RMAN TARGET SYS/ORACLE@ORCLSBY
RESTORE CONTROLFILE FROM 'C:\SBY.ORA';
catalog backuppiece 'c:\05LICVI0';
restore database;

12. DUPLICATE DATABASE
NOTE: TARGET DB SHOULD BE MOUNT AND STANDBY SHOULD BE NOMOUNT STATE

rman target sys/oracle@orcl auxiliary sys/oracle@orclsby

RMAN>
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}


13. Start Redo apply
1) On the standby database, to start redo apply:
SQL>alter database recover managed standby database disconnect from session;
-----
If you ever need to stop log apply services:
SQL> alter database recover managed standby database cancel;
-------
14. Verify the standby database is performing properly:
A) On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;

B) On Primary, force a logfile switch:
SQL>alter system switch logfile;

C) On Standby, verify the archived redo log files were applied:
SQL>

15. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.

If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.

To start real-time apply:

on standby database

shut immediate;
startup mount
alter database recover managed standby database disconnect;
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect;
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect;

on primary only one time

alter system switch logfile;
alter system switch logfile;

16. To create multiple standby databases, repeat this procedure.

17) Failover

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;


ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

---------IV. Maintenance:-------

1. Check the alert log files of Primary and Standby databases frequently to monitor the database operations in a Data Guard environment.

2. Cleanup the archive logs on Primary and Standby servers.

I scheduled weekly Hot Whole database backup against my primary database that also backs up and delete the archived logs on Primary.

For the standby database, I run RMAN to backup and delete the archive logs once per week.
$rman target /@ORCLSBY;
RMAN>backup archivelog all delete input;

To delete the archivelog backup files on the standby server, I run the following once a month:
RMAN>delete backupset;

3. Password management
The password for the SYS user must be identical on every system for the redo data transmission to succeed. If you change the password for SYS on Primary database, you will have to update the password file for Standby database accordingly, otherwise the logs won't be shipped to the standby server.

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

Oracle Database Growth Report

select sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Schema Size (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner = 'SYS'
and space_used_delta > 0;



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


SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY tsname desc,days desc;




-------------------Increase Snap Shot time


BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.
END;
/

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

Database Growth Monitoring Script

Step : 1 Calculate total Size of tablespace
select sum(bytes)/1024/1024 "TOTAL SIZE (MB)" from dba_Data_files
Step : 2 Calculate Free Space in Tablespace
select sum(bytes)/1024/1024 "FREE SPACE (MB)" from dba_free_space
Step : 3 Calculate total size , free space and used space in tablespace
select t2.total "TOTAL SIZE",t1.free "FREE SPACE",(t1.free/t2.total)*100 "FREE (%)" ,(1-t1.free/t2.total)*100 "USED (%)"
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 , (select sum(bytes)/1024/1024 total from dba_Data_files) t2
Step : 4 Create table which is store all free/use space related information of tablespace
create table db_growth
as select *
from (
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE% "
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
Step : 5 Insert free space information in DB_GROWTH table (if you want to populate data Manually)
insert into db_growth
select *
from (
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
Step : 6 Create View on DB_GROWTH based table ( This Steps is Required if you want to populate data automatically)
create view v_db_growth
as select *
from
(
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)

Step : 7 Insert data into DB_GROWTH table from V_DD_GROWTH view
insert into db_growth select *
from v_db_growth
Step : 8 Check everything goes fine.
select * from db_growth;
Check Result
Step : 9 Execute following SQL for more time stamp information
alter session set nls_date_format ='dd-mon-yyyy hh24:mi:ss' ;
Session altered.
Step : 10 Create a DBMS jobs which execute after 24 hours
declare
jobno number;
begin
dbms_job.submit(
jobno, 'begin insert into db_growth select * from v_db_growth;commit;end;', sysdate, 'SYSDATE+ 24', TRUE);
commit;
end;

PL/SQL procedure successfully completed.
Step: 11 View your dbms jobs and it's other information
select * from user_jobs;
TIPS: If you want to execute dbms jobs manually execute following command other wise jobs is executing automatically
exec dbms_job.run(ENTER_JOB_NUMBER)
PL/SQL procedure successfully completed.
Step: 13 Finally all data populated in db_growth table
select * from db_growth;



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

Oracle User Login Lock Wrong Attempt details

select username,osuser,machine,blocking_session from v$session where username='LDBO';

select username,os_username,to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",terminal,
---UTL_INADDR.get_host_address(terminal) "IP",
decode(returncode,0,'Successful',1017,'WrongAttempt',28000,'Locked',28009,'SYS Login',1005,'Fail_NULL',28001,'EXPIRED',28031,'Roles_Exceeded',returncode) Login_Status
from dba_audit_session where trim(Timestamp) > trunc(sysdate-1)
and username='AHE'
-----and os_username like '%UNICON\%'
order by timestamp desc;

select username,password,account_status,to_char(lock_date, 'Dy DD-Mon-YYYY HH24:MI:SS') lock_date,expiry_date
from dba_users where account_status like '%LOCKED%' order by 3 desc,lock_date desc;

select distinct username "USER ID",osuser,machine,UTL_INADDR.get_host_address(terminal)"System IP", decode(username,'USSB3409','ANILKUMAR','USSB0065','GAURAVSINGH','USSB0737','KAVITA', 'USSB0580','MAHINDERSINGH', 'USSB0624','RAJENDERSINGH','USSB0625','RAKESHKUMAR','UIPL6716','RAKESHLAL','USSB0500','RUPINDERKAUR','UIPL6713','SHASHINATH','USSB0502','SURENDERKUMAR','UIPL6715','VIRENDERSINGH','UIPL6714','SANJAYKUMARSISODIA','UIPL7249','BHAVYASINGH','UFIPL00152','GOVINDGUPTA','UIPL7244','INDIRARAWAT','USSB2693','JYOTI','UIPL7258','MANTUKUMARSINGH','USSB0080','PRIYADAS','USSB2720','RAHUL','UIPL7210','ROBINTYAGI','UFIPL00154','ROHITGUPTA','USSB1740','SHAKSHI','UIPL7259','VINODKUMAR','USSB0991','ARJUNSINGH','UFPL00058','DHEERAJTANEJA','UFIPL00151','PAWANKUMARJINDAL','UIPL7759','PRABHAKARSINGH','USSB0948','PRIYANKARANA','USSB6549','RAJESHSHARMA.','USSB6788','RAMKRISHANKUMAR','USSB6790','ROSHANKUMAR','USSB0550','SACHINSUGANDHA','UIPL7261','SHOBHITAGARWAL','USSB1470','SIDDHARTHBHATIA','USSB6996','KANIKAKHURANA','UFIPL00153','DEVESH','UIPL10283','SWATISALUJA','UIPL10287','NITINGOYAL','UIPL9767','GAURAV','UFIPL00156','AMITKUMARSINGHAL','UIPL10305','PRANJEETKAUSHIK','UFPL00173','PUNEETSHARMA','UIPL10672','SUMITRAJORA','UFPL00188','DEEPAKGUPTA','UFPL00195','VINEETSABHLOK','USSB0019','PRASHANTUPADHYAY','USSB0022','NEERAJGROVER','USSB0109','SAURABHAATRE','USSB0626','SHAHABUDDIN','USSB2701','NITESH','UCPL0491','POONAMSINGH','UCPL0116','DURGESHKESHRI','USSB0211','SAURABHCHAUDHARY','USSB0431','VIRENDERSINGHCHANDEL','USSB5498','AKANSHARAWAL','USSB7030','SATYANARAYANA','UIPL6793','JAGDEEPSINGH','USSB3700','JAYAACHAR','UIPL6778','NEERAJSHARMA','USSB6047','NEHAGOEL','UIPL6798','ROHANSHARMA','UIPL6828','SACHINTYAGI','USSB4173','SEEMAGAMBHIR','USSB4766','SHAKSHIARORA','UCPL0117','SHAMPAMUKHERJEE','UIPL6736','SONIABENWAL','USSB6621','TANUSHREESOMANI','USSB1236','ASHIMAARORA','UIPL6771','SHELLY','USSB5708','KULDEEPKAUR','UIPL6801','SHWETA','UIPL6800','ROHITLUTHRA','USSB7428','AMRESHKUMAR','UIPL10160','SONIASABHARWAL','USSB7444','ANILSHARMA','UIPL10515','SAPNAGUPTA','UIPL10402','KHUSHALRAZDAN','UIPL6742','GITTUKATARIA','UIPL6746','MUNNASINGH','USSB0013','AMITTYAGI','UIPL6722','DEEPANSHUMALHOTRA','USSB5695','HARENDRASHARMA','UIPL6727','MANISHVERMA','USSB1112','PRATIMASINGH','USSB5374','PULKIT','USSB1253','RAHULTYAGI','USSB5274','VARUNKANT','UFPL00178','SUBHASHCHANDRAGEHLOT','UFPL00028','SUBHASHSHARMA','USSB6901','KSHITIJR.GUPTA','UCPL0154','RACHITJAIN','USSB6097','SHEKHARSAXENA','UIPL6712','YESHPALTHAKUR','USSB6002','SANDHYASHARMA','UFIPL00130','ANJALIMUKHIJA','UCPL0111','AMITUPADHYAY','USSB6045','RAJAN','UFIPL00136','SANDEEPSIR','USSB0064','AMITJAIN','UIPL6831','ANKITASRIVASTAVA','USSB6805','SHASHIBHUSHAN','USSB6950','SUNIIGABA','USSB7222','NILESHTRIPATHI','USSB0849','OMPRAKASHBANDERWAL','USSB0458','ROHTASAGARWAL','RMS','RMS','USSB1366','NEETARASTOGI','USSB0256','ANANDJOSHI','USSB1032','SATISHSHARMA','USSB1113','SUNILKUMARSINGHAL','UIPL6721','VISHALGUPTA','UIPL6724','YATENDRASINGHBISHT','USSB6241','KULDEEPSHARMA','USSB5495','RAGHIBHUSAIN','USSB1031','TARUNCHHOKRA','UIPL6717','NEERAJKAUSHIK','USSB6508','ACHINANAND','USSB7530','KHYATIADLAKHA','USSB7565','BHUWANSHARMA','USSB7550','NEHASINGH','LDBO','DBA') Name
from v$session where program ='ld.exe' order by 5;

select username,password,account_status,lock_date, to_char(expiry_date, 'Dy DD-Mon-YYYY HH24:MI:SS') expir_date
from dba_users where account_status like '%EXPIRED%' order by expiry_date desc;

select username,os_username,terminal "System IP",to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",obj_name, decode(ses_actions,'---------S------','SELECT','----------S-----','UPDATE',ses_actions) action,action_name
from dba_audit_trail
where trim(Timestamp)=trunc(sysdate)
order by 4 desc;


select username,os_username,terminal,to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",obj_name,action_name
,decode(returncode,1917,'Grant Revoke',1918,'ALter',1920,'Creation',returncode) Status
from dba_audit_exists
order by timestamp desc;

select firmnumber,oowncode as username,cclientlevelpassword as password from clemaildetail where oowncode='DP122';
SELECT username, terminal, to_char (timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS'),
decode(returncode,0,'Successful',1017,'WrongAttempt',28000,'Locked',28009,'SYS Login',1005,'Fail_NULL',28001,'EXPIRED',28031,'Roles_Exceeded',returncode) Login_Status
FROM dba_audit_session
WHERE returncode <> 0 AND not exists (SELECT 'x' FROM dba_users WHERE dba_users.username=dba_audit_session.username)
and trim(Timestamp)=trunc(sysdate)
order by timestamp desc;


select username,os_username,terminal "System IP",to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",obj_name,action_name from dba_audit_object where trim(Timestamp)=trunc(sysdate) order by timestamp desc;

select username,os_username,
UTL_INADDR.get_host_address(terminal)"System IP",to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",
obj_name,action_name from dba_audit_statement
--where
----trim(Timestamp)=trunc(sysdate)
--obj_name='UIPL6713'
order by timestamp desc;

select * from dba_stmt_audit_opts;

select * from dba_priv_audit_opts;
select * from dba_audit_object where obj_name='LDBO' order by timestamp desc;


select distinct * from TBLAUDITUSERLOGONDETAILS
where cusername not in ('SYS','CLLVL')
and cusername='LDBO'
and trim(dlogonday)=trunc(SYSDATE)
order by dlogonday desc,clogontime desc;

Select Substr(Upper(User_audit_trail.Os_Username),1,30) as cOS_Username, Rpad(' ',10) as Oowncode, Substr(User_audit_trail.Username,1,30) as cUsername,User_audit_trail.Userhost as cUserhost, Upper(Substr(User_audit_trail.Terminal,1,30)) as cTerminal,User_audit_trail.Timestamp as dTimeStamp, nvl(User_audit_trail.Obj_name,' ') as cObjectName,User_audit_trail.Action_name as cActionname, nvl(User_audit_trail.Sys_Privilege,' ') as cSysPrivilege,nvl(User_audit_trail.Grantee,' ') as cGrantee, nvl(User_audit_trail.Priv_Used,' ') as cPrivUsed From User_audit_trail User_audit_trail
Where User_audit_trail.Os_Username not in ('SYSTEM','SYSMAN','DBSNMP')
and User_audit_trail.Username='LDBO'
and trim(User_audit_trail.Timestamp)=trunc(sysdate)
order by timestamp;


Select Dba_Users.Username as cUsername,Dba_Users.Profile as cProfile, Dba_Users.Account_status as cAccountstatus,Dba_Users.Lock_date as dLockDate,Dba_Users.Expiry_Date as dExpiryDate, Dba_users.Created as dCreationDate
From Dba_Users Dba_Users
Where Dba_users.Username not in ('SYS', 'SYSTEM', 'DBSNMP', 'TSMSYS', 'OUTLN', 'ORACLE_OCM','MGMT_VIEW','SYSMAN', 'WMSYS', 'DIP')
and Dba_Users.Created>='13-MAR-09'
--and trim(dlockdate)!=' '
---and trim(dexpirydate)!=' '
and Dba_Users.Created<=trunc(sysdate) order by 5 desc, 4 desc;

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

Oracle Password Policy

-- This script sets the default password resource parameters
-- This script needs to be run to enable the password features.
-- However the default resource parameters can be changed based
-- on the need.
-- A default password complexity function is also provided.
-- This function makes the minimum complexity checks like
-- the minimum length of the password, password not same as the
-- username, etc. The user may enhance this function according to
-- the need.
-- This function must be created in SYS schema.
-- connect sys/ as sysdba before running the script

CREATE OR REPLACE FUNCTION verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);

BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!#$%^&*()_+|~-=\`{}[]:"'<>?,./';

-- Check if the password is same as the username
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;

-- Check for the minimum length of the password
IF length(password) < isdigit =" FALSE">>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO findpunct;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation except @');
END IF;
-- 3. Check for the punctuation
<>
ispunct:=FALSE;
FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ispunct = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation except @');
END IF;

<>
-- Check if the password differs from the previous password by at least 3 letters

IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);

IF abs(differ) < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;

differ := abs(differ);
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;

IF differ < 3 THEN
raise_application_error(-20004, 'Password should differ by at least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/

-- This script alters the default parameters for Password Management
-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.

ALTER PROFILE LD_BACKOFFICE LIMIT
PASSWORD_LIFE_TIME 30
PASSWORD_GRACE_TIME 5
PASSWORD_REUSE_TIME 30
PASSWORD_REUSE_MAX 3
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME Unlimited
PASSWORD_VERIFY_FUNCTION verify_function;




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

Restore Database when source database(E:) and destination database location(D:) is different

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\archive1011\sns1011\%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D:\archive1011\sns1011\rmanbackup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 900 M; --if more than one backuppiece

RMAN> list backup of database;
RMAN> CROSSCHECK backup of database;
RMAN> delete expired backup;

RMAN> catalog backuppiece 'D:\archive1011\sns1011\RMANBACKUP_DB_SNS1011_S_149_P_1_T_720717287','D:\archive1011\sns1011\RMANBACKUP_DB_SNS1011_S_149_P_2_T_720717287';-----if more than one backpupiece

SQL> alter database rename file 'E:\SNSD1011\REDO01.ORA' TO 'D:\SNSD1011\REDO01.ORA' ;
SQL> alter database rename file 'E:\SNSD1011\REDO02.ORA' TO 'D:\SNSD1011\REDO02.ORA' ;
SQL> alter database rename file 'E:\SNSD1011\REDO03.ORA' TO 'D:\SNSD1011\REDO03.ORA' ;
SQL> alter database rename file 'E:\SNSD1011\REDO04.ORA' TO 'D:\SNSD1011\REDO04.ORA' ;
SQL> alter database rename file 'E:\SNSD1011\REDO05.ORA' TO 'D:\SNSD1011\REDO05.ORA' ;



run { set until sequence
set newname for datafile 1 to 'D:\SNSD1011\SYSTEM01.ORA' ;
set newname for datafile 1 to 'D:\SNSD1011\UNDOTBS01.ORA' ;
set newname for datafile 1 to 'D:\SNSD1011\SYSAUX01.ORA' ;
set newname for datafile 1 to 'D:\SNSD1011\INDX01.ORA' ;
set newname for datafile 1 to 'D:\SNSD1011\USERS01.ORA' ;


restore database;
switch datafile all;
recover database;
alter database open resetlogs; }

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

Oracle 10g 32 bit at Window 7 (64 bit)

go to this folder “E:\Installers\Oracle_10g_1023\Disk1\stage\prereq\db”

locate the file “refhost.xml”, open it with notepad

now add the following:
(write click see in view source)

meta tags format

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
























normally recommend that you cut and paste your META Description into the Abstract and Subject META Tags. This ensures that the search engine spiders fully understand what the page is about. One is not enough!
----------------
ou should cut and paste your ">
----------------------

Oracle Standby Database / Diaster recovery Solution

-----------------I. Before you get started:-------------------

1. Make sure the operating system and platform architecture on the primary and standby systems are the same;

2. Install Oracle database software without the starter database on the standby server and patch it if necessary. Make sure the same Oracle software release is used on the Primary and Standby databases, and Oracle home paths are identical.

3. Test the Standby Database creation on a test environment first before working on the Production database.

---------II. On the Primary Database Side:---------------------

1. Enable forced logging on your primary database:
SQL> ALTER DATABASE FORCE LOGGING;

2. Create a password file if it doesn't exist.
1) To check if a password file already exists, run the following command:
SQL> select * from v$pwfile_users;

2) If it doesn't exist, use the following command to create one:

$cd %ORACLE_HOME%\database
$orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with the password for the SYS user.)

3. Configure a Standby Redo log.
1) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:
SQL> select bytes from v$log;

BYTES
----------
52428800
52428800
52428800

2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile;

3) Create standby Redo log groups.
My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;

4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log;

4. Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;

5. Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.

1) Create pfile from spfile for the primary database:

SQL>create pfile='d:\oracle\product\10.2.0\db_1\database\pfilePRIM.ora' from spfile;


2) Edit pfilePRIM.ora to add the new primary and standby role parameters:


select * from v$parameter where name like '%log_archive_format%';
select * from v$parameter where name like '%standby%';
select * from v$parameter where name like '%remote_archive_enable%';
select * from v$parameter where name like '%log_archive_dest_state_%';
select * from v$parameter where name like '%convert%';




db_name=PRIM
db_unique_name=PRIM
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAN)'
LOG_ARCHIVE_DEST_1='LOCATION=F:\Oracle\flash_recovery_area\PRIM\ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIM'
LOG_ARCHIVE_DEST_2='SERVICE=STAN LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STAN'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=STAN
FAL_CLIENT=PRIM
STANDBY_FILE_MANAGEMENT=AUTO
# Specify the location of the standby DB datafiles followed by the primary location;
DB_FILE_NAME_CONVERT='E:\STAN\DATAFILE','E:\PRIM\DATAFILE'
# Specify the location of the standby DB online redo log files followed by the primary location LOG_FILE_NAME_CONVERT='E:\STAN\ONLINELOG','E:\PRIM\ONLINELOG','F:\Oracle\flash_recovery_area\STAN\ONLINELOG','F:\Oracle\flash_recovery_area\PRIM\ONLINELOG'

6. Create spfile from pfile, and restart primary database using the new spfile.

SQL> shutdown immediate;
SQL> startup nomount pfile='\database\pfilePRIM.ora';
SQL>create spfile from pfile='d:\oracle\product\10.2.0\db_1\database\pfilePRIM.ora';
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;


----------------III. On the Standby Database Site:---------------

1. Create a copy of Primary database data files on the Standby Server:
On Primary DB:
SQL>shutdown immediate;

On Standby Server (While the Primary database is shut down):
1) Create directory for data files, E:\STAN\DATAFILE.


2) Copy the data files and temp files over.

3) Create directory (multiplexing) for online logs, for example, on Windows, E:\STAN\ONLINELOG and F:\Oracle\flash_recovery_area\STAN\ONLINELOG.


4) Copy the online logs over.

2. Create a Control File for the standby database:
On Primary DB, create a control file for the standby to use:
SQL>startup mount;
SQL>alter database create standby controlfile as 'STAN.ctl;
SQL>ALTER DATABASE OPEN;

3. Copy the Primary DB pfile to Standby server and rename/edit the file.

1) Copy pfilePRIM.ora from Primary server to Standby server, to database folder on Windows or dbs folder.

2) Rename it to pfileSTAN.ora, and modify the file as follows


*.audit_file_dest='E:\oracle\product\10.2.0\admin\STAN\adump'
*.background_dump_dest='E:\oracle\product\10.2.0\admin\STAN\bdump'
*.core_dump_dest='E:\oracle\product\10.2.0\admin\STAN\cdump'
*.user_dump_dest='E:\oracle\product\10.2.0\admin\STAN\udump'
*.compatible='10.2.0.3.0'
control_files='E:\STAN\CONTROLFILE\STAN.CTL','F:\ORACLE\FLASH_RECOVERY_AREA\STAN\CONTROLFILE\STAN.CTL'
db_name='PRIM'
db_unique_name=STAN
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAN)'
LOG_ARCHIVE_DEST_1='LOCATION=F:\Oracle\flash_recovery_area\STAN\ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STAN'
LOG_ARCHIVE_DEST_2= 'SERVICE=PRIM LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIM'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=PRIM
FAL_CLIENT=STAN
remote_login_passwordfile='EXCLUSIVE'
# Specify the location of the primary DB datafiles followed by the standby location
DB_FILE_NAME_CONVERT='E:\PRIM\DATAFILE','E:\STAN\DATAFILE'
# Specify the location of the primary DB online redo log files followed by the standby location
LOG_FILE_NAME_CONVERT='E:\PRIM\ONLINELOG','E:\STAN\ONLINELOG','F:\Oracle\flash_recovery_area\PRIM\ONLINELOG','F:\Oracle\flash_recovery_area\STAN\ONLINELOG'
STANDBY_FILE_MANAGEMENT=AUTO



4. On Standby server, create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.

5. Copy the standby control file 'STAN.ctl' from primary to standby destinations ;

6. Copy the Primary password file to standby and rename it to pwdSTAN.ora.
On Windows copy it to \database folder And then rename the password file.

7. For Windows, create a Windows-based services (optional):
$oradim –NEW –SID STAN –STARTMODE manual

8. Configure listeners for the primary and standby databases.

1) On Primary system: use Oracle Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
$lsnrctl stop
$lsnrctl start

2) On Standby server: use Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
$lsnrctl stop
$lsnrctl start

9. Create Oracle Net service names.
1) On Primary system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN

2) On Standby system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN

10. On Standby server, setup the environment variables to point to the Standby database.

Set up ORACLE_HOME and ORACLE_SID.

11. Start up nomount the standby database and generate a spfile.

SQL>startup nomount pfile='\database\pfileSTAN.ora';
SQL>create spfile from pfile='\database\pfileSTAN.ora';
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;

12. Start Redo apply
1) On the standby database, to start redo apply:
SQL>alter database recover managed standby database disconnect from session;

If you ever need to stop log apply services:
SQL> alter database recover managed standby database cancel;

13. Verify the standby database is performing properly:
1) On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;

2) On Primary, force a logfile switch:
SQL>alter system switch logfile;

3) On Standby, verify the archived redo log files were applied:
SQL>select sequence#, applied from v$archived_log order by sequence#;

14. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.

To start real-time apply:
SQL> alter database recover managed standby database using current logfile disconnect;

15. To create multiple standby databases, repeat this procedure.

---------IV. Maintenance:-------

1. Check the alert log files of Primary and Standby databases frequently to monitor the database operations in a Data Guard environment.

2. Cleanup the archive logs on Primary and Standby servers.

I scheduled weekly Hot Whole database backup against my primary database that also backs up and delete the archived logs on Primary.

For the standby database, I run RMAN to backup and delete the archive logs once per week.
$rman target /@STAN;
RMAN>backup archivelog all delete input;

To delete the archivelog backup files on the standby server, I run the following once a month:
RMAN>delete backupset;

3. Password management
The password for the SYS user must be identical on every system for the redo data transmission to succeed. If you change the password for SYS on Primary database, you will have to update the password file for Standby database accordingly, otherwise the logs won't be shipped to the standby server.

Refer to section II.2, step 2 to update/recreate password file for the Standby database.

Oracle RMAN Duplicate Database / Clone Database

Configure The Network
We need to configure the network so that there is connectivity to the target
(source) database and to the database we want to create (the clone)
Listener file:
# listener.ora Network Configuration File:
c:\oracle\product\10.2.0\client_1/network/admin/listener.
ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = snsdup)
(ORACLE_HOME = c:\oracle\product\10.2.0\client_1)
(SID_NAME = snsdup)
)
(SID_DESC =
(GLOBAL_DBNAME = sns6)
(ORACLE_HOME = c:\oracle\product\10.2.0\client_1)
(SID_NAME = sns6)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = snsdup))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = sns6))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.32)(PORT = 1521))
)
)

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

Tnsnames file:


(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.32)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = snsdup)
)
)
sns6 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.32)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sns6)
)
)

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

Create A Password File For The New Database

[oracle@10.100.0.32 dbs]$ orapwd file=orapwdsnsdup password=oracle

-----------------
Create An Init.Ora For The New Database
Create the pfile using create pfile from spfile from the source database, then
edit it changing all occurrences of old database name to new name

*.audit_file_dest='/oracle/app/admin/snsdup/adump'
*.audit_trail='db'
*.compatible='10.0.2.3.0.0'
#*.control_files='’
*.db_block_size=8192
*.db_create_file_dest='/oracle/app/oradata'
*.db_domain=''
*.db_name='snsdup'
*.db_recovery_file_dest='/oracle/app/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=snsdupXDB)'
*.memory_target=262144000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
-----------------

Create The Admin Directory For The New Database
mkdir –p ../snsdup/adump

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

Shutdown And Startup Mount The Source Database
This is required if the source database is in no archivelog mode.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

------------------------
Startup Nomount The New Database

SQL> startup nomount
ORACLE instance started.

--------------
Connect To The Target (Source) And Auxiliary (New Clone) Databases Using Rman


[oracle@10.100.0.32 dbs]$ Rman target sys/oracle@sns6 auxiliary sys/oracle@snsdup

--------------
Execute The Duplicate Command

RMAN> DUPLICATE TARGET DATABASE TO snsdup FROM ACTIVE DATABASE;

--------

Remove The Old Pfile
The duplicate database created an spfile that has the new controlfile names in it, the old pfile, without controlfiles can be removed

[oracle@10.100.0.32 dbs]$ rm initsnsdup.ora


--------

Check The New Database
ORACLE_SID=snsdup
ORACLE_HOME=c:\oracle\product\10.2.0\client_1
[oracle@10.100.0.32 dbs]$ sqlplus / as sysdba


SQL> archive log list

SQL> select name from v$datafile;

SQL> show sga



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

Oracle Export Backup Job Email Notification

---------------------------------------------Export.bat---------------------------


@ECHO off
SETLOCAL
IF [%1]==[] goto s_start

ECHO GETDATE.cmd
ECHO Returns the date independent of regional settings
ECHO Creates the environment variables %v_year% %v_month% %v_day%
ECHO.
ECHO SYNTAX
ECHO GETDATE
ECHO.
ECHO.
GOTO :eof

:s_start

FOR /f "tokens=2-4 skip=1 delims=(-)" %%G IN ('echo.^|date') DO (
FOR /f "tokens=2 delims= " %%A IN ('date /t') DO (
SET v_first=%%G
SET v_second=%%H
SET v_third=%%I
SET v_all=%%A
)
)

SET %v_first%=%v_all:~0,2%
SET %v_second%=%v_all:~3,2%
SET %v_third%=%v_all:~8,2%

rem ECHO Today is Year: [%yy%] Month: [%mm%] Day: [%dd%]

ENDLOCAL & SET v_year=%yy%& SET v_month=%mm%& SET v_day=%dd%
ren C:\00\1.txt USED66665_%v_month%%v_day%%v_year%.txt

exp snsexport/snsexp@sns1011srv owner=(ldbo) file=G:\EXPORT\sns1011ora_%v_day%%v_month%%v_year%.DMP LOG=G:\EXPORT\sns1011ora_%v_day%%v_month%%v_year%.LOG

if exist G:\EXPORT\sns1011ora_%v_day%%v_month%%v_year%.DMP GOTO COMPLETED

if not exist G:\EXPORT\sns1011ora_%v_day%%v_month%%v_year%.DMP GOTO FAILED

:COMPLETED
Cscript.exe C:\Email_export.vbs
exit
:FAILED
Cscript.exe C:\Emailfail_export.vbs

---------------------------------------------Email_export.vbs---------------------------


Set wshShell = WScript.CreateObject( "WScript.Shell" )
strComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
'WScript.Echo "Computer Name: " & strComputerName
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "LD Oracle Export Backup Job Completed"
objMessage.From = "dbanotification@uniconindia.in"
objMessage.To = "dbamonitoring@uniconindia.in"
objMessage.TextBody = "LD Oracle Export Backup Job Completed Successfully on " & strComputerName & " at " & FormatDateTime(Date,1) & " " & Time

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.100.0.94"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send


---------------------------------------------Emailfail_export.vbs---------------------------


Set objMessage = CreateObject("CDO.Message")
strComputer = "." ' Name of the computer
objMessage.Subject = "LD Oracle Job Failed ORANOP02P"
objMessage.From = "dbanotification@uniconindia.in"
objMessage.To = "kgupta2@uniconindia.in"
objMessage.TextBody = "LD Oracle Job Failed on ORANOP02P !" & strComputer

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.100.0.94"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send

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

Oracler RMAN Job Email Notification

------------------------------------------------RMAN.bat--------------------


@ECHO off
SETLOCAL
IF [%1]==[] goto s_start

ECHO GETDATE.cmd
ECHO Returns the date independent of regional settings
ECHO Creates the environment variables %v_year% %v_month% %v_day%
ECHO.
ECHO SYNTAX
ECHO GETDATE
ECHO.
ECHO.
GOTO :eof

:s_start

FOR /f "tokens=2-4 skip=1 delims=(-)" %%G IN ('echo.^|date') DO (
FOR /f "tokens=2 delims= " %%A IN ('date /t') DO (
SET v_first=%%G
SET v_second=%%H
SET v_third=%%I
SET v_all=%%A
)
)

SET %v_first%=%v_all:~0,2%
SET %v_second%=%v_all:~3,2%
SET %v_third%=%v_all:~6,4%

rem ECHO Today is Year: [%yy%] Month: [%mm%] Day: [%dd%]

ENDLOCAL & SET v_year=%yy%& SET v_month=%mm%& SET v_day=%dd%
ren C:\00\1.txt USED66665_%v_month%%v_day%%v_year%.txt


set oracle_sid=sns6



RMAN TARGET sys/oracle@sns1011srv cmdfile='c:\rman.sql'




if exist D:\archive0910\sns1011\RMANBACKUP_DB_SNS1011_%v_day%%v_month%%v_year% GOTO COMPLETED

if not exist D:\archive0910\sns1011\RMANBACKUP_DB_SNS1011_%v_day%%v_month%%v_year% GOTO FAILED

:COMPLETED
Cscript.exe C:\Email_rman.vbs
exit
:FAILED
Cscript.exe C:\Emailfail_rman.vbs


-----------------------------rman.sql----------------------------
show all;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'D:\archive0910\sns1011\rmanbackup_db_%d_%D%M%Y';
exit;


---------------------------------Email_rman.vbs-----------------------------------

Set wshShell = WScript.CreateObject( "WScript.Shell" )
strComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
'WScript.Echo "Computer Name: " & strComputerName
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "LD Oracle RMAN Job Completed Successfully"
objMessage.From = "rjain@uniconindia.in"
objMessage.To = "kgupta2@uniconindia.in"
objMessage.TextBody = "LD Oracle RMAN Backup Job Completed Successfully on " & strComputerName & " at " & FormatDateTime(Date,1) & " " & Time

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.100.0.94"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send


----------------------------------Emaildail_rman.vbs--------------------------------

Set objMessage = CreateObject("CDO.Message")
strComputer = "." ' Name of the computer
objMessage.Subject = "LD Oracle RMAN Job Failed ORANOP02P"
objMessage.From = "dbanotification@uniconindia.in"
objMessage.To = "kgupta2@uniconindia.in"
objMessage.TextBody = "LD Oracle RMAN Job Failed on ORANOP02P !" & strComputer

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.100.0.94"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send

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

Reuse statements, cursor_sharing=force

Reuse statements

select * from emp where ename = :EMPNAME


select * from v$parameter where name like '%cursor_sharing%';

cursor_sharing=force

The kernel parameter cursor_sharing defaults to EXACT, but can be set to FORCE or SIMILAR in order to have the database convert literals to bind variables before parsing the statement.

Setting cursor_sharing =force greatly reduced the contention on the library cache and reduced CPU consumption. The end users reported a 75 percent improvement in overall performance.

Our queries use a lot of bind variables, by design. In recent benchmarks, CURSOR_SHARING=FORCE was helpful for benchmark runs reducing query execution time by several times.
However, for a specific query involving bind variables, using CURSOR_SHARING=FORCE was very much slower than if executed while CURSOR_SHARING=EXACT.


This is probably a bug. Cursor_sharing = force is not really reliable. We hash the value of the statement and then go looking for a matching hash, regardless of how many users are on the system or how many times the statement has been issued or even what the statement looks like. Force means force, regardless of what is going on, we are going to force the
sharing of cursors. This leads to problems like what you are seeing and the use of suboptimal plans. You can attempt to tune around the sub-optimal plan but you may find that there are a number of queries with that problem. The other possibility is to set cursor_sharing = exact. That will prevent this problem from occuring.

Oracle Flushing-Pinning PL/SQL packages, FLUSH SHARED POOL

Oracle Flushing-Pinning PL/SQL packages, FLUSH SHARED POOL

http://www.remote-dba.cc/oracle_tips_flushing_secrets.htm
1. Pinning all commonly used PL/SQL packages.

The initial call to a package causes the whole package to be loaded into the shared pool. For large packages this may represent an unacceptable delay for two reasons. First the size of the package causes a delay in load time. Second if the shared pool is already full, several smaller objects may need to be aged out to make room for it. In these circumstances, performance can be improved by pinning large packages in the shared pool.

Under normal circumstances, objects in the shared pool are placed on a least recently used (LRU) list. If the shared pool is full and a new object must be loaded, items on the LRU list are aged out. Subsequent calls to objects that have been aged out result in them being reloaded into the shared pool.

The processes of pinning objects in the shared pool removes them from the LRU list so they are no longer available to be aged out, regardless of usage. The process of pinning objects in the shared pool is achieved using the dbms_shared_pool package.

http://www.dba-oracle.com/plsql/t_plsql_pinning_pkgs.htm
http://www.oracle-training.cc/oracle_tips_pinning_packages.htm



SQL> conn sys/password as sysdba
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql


Pinning and unpinning objects in the shared pool is achieved using the keep and unkeep procedures, both of which accept the same case-insensitive parameters.

PROCEDURE keep (
name VARCHAR2,
flag CHAR DEFAULT 'P'
)

PROCEDURE unkeep (
name VARCHAR2,
flag CHAR DEFAULT 'P'
)

P - Package, Procedure or Function. This is the default value.
T - Type.
R - Trigger.
Q - Sequence.
C – Cursor.


SQL> conn sys/password as sysdba
Connected.
SQL> SELECT address || ',' || hash_value FROM v$open_cursor WHERE rownum = 1;



SQL> EXEC DBMS_SHARED_POOL.keep(‘

,2. FLUSH SHARED POOL

Periodically issuing the--
ALTER SYSTEM FLUSH SHARED POOL;
alter system flush buffer_pool;
----------
This will "flush" out, or clear, all SQL statements that are in the Shared Pool Area. Oracle keeps track of each SQL statement that users execute.
It is stored parsed in memory so that if a SQL statement already has been executed then Oracle does not need to re-parse it. The exception is if the shared pool area is not large enough, then the least recently used SQL statements (except for pinned packages) will be removed from memory.

By flushing the shared pool, all SQL statements are removed from memory.

----------

RMAN Block Corruption Detection & Recovery

Block Media Recovery with RMAN


sql>select file# block# from v$database_block_corruption; --corrupted block



Rman>> block recover datafile block ; ------recover corrupted block


Rman>blockrecover corruption list;

RMAN> backup validate check logical database;

RMAN recover & open database if archive log missing

RMAN recover and open the database if the archive log required for recovery is missing


1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) recover database until cancel using backup controlfile;
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”

RMAN recovery from loss of all online redo log files

RMAN recovery from loss of all online redo log files


SQL> select thread#,sequence#,status from v$Log;


set oracle_sid=sns6
rman Target SYS/linux@sns1011srv

RUN
{
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
set until sequence 2021 thread 1;
ALLOCATE CHANNEL ch1 TYPE Disk;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

Uninstall Oracle from Server Completely

Uninstall Oracle

# Stop any Oracle services that have been left running.
Start->Settings->Control Panel->Services
Look for any services with names starting with ‘Oracle’ and stop them.
* Uninstall all Oracle components using the Oracle Universal Installer (OUI).
* Run regedit.exe and delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE key. This contains registry entires for all Oracle products.
* Delete any references to Oracle services left behind in the following part of the registry:
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Ora*
It should be pretty obvious which ones relate to Oracle.
* Reboot your machine.
* Delete the "C:\Oracle" directory, or whatever directory is your ORACLE_BASE.
* Delete the "C:\Program Files\Oracle" directory.
* Delete the C:\Documents and Settings\All Users\Start Menu\Programs\Oracle*
* Empty the contents of your "c:\temp" directory.
* Empty your recycle bin.

# Delete the Oracle Start Menu shortcuts directory:
Start->Settings->Control Panel->System->Advanced->Environment Variables
Edit both of the environment variables user PATH and system PATH. Remove any Oracle references in them.
# Remove Oracle refereces from the path. To edit your path go to:
# Remove Oracle.DataAccess and any Polic.Oracle files from the GAC which is at:
C:\Windows\assembly\

Create a Oracle Database Server Using RMAN backup from another Server

Create a Oracle Database Server Using RMAN backup from another Server


1) install Oracle server 10.2.0.3 without create startup database

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

2) Apply patch

Before you apply the patchset you have to check whether the Oracle Version is 32 bit or 64 bit Version, because
the patchsets are different.
For 32 Bit Version, Patch no is p6810189_10204_Win32_patchset
For 64 Bit Version, Patch no is p6810189_10204_MSWIN-x86-64.

1. Shut down the database:
SQL> SHUTDOWN IMMEDIATE.
Stop all the Oracle Related Services.
Ex:- Oracle Listener, Oracle DB Console,Oracle JobScheduler,
Distrubed Transaction Co-ordinator.
Run the Patch Setup.exe in the same Oracle Home.
For Example: If Existing Oracle is installed in c:\Oracle\product\10.2.0\db_1 then you
have to select the same path When you run the Setup.exe. After Successful installation
start the Listener & Db Console etc.,
Enter the following SQL*Plus commands:
SQL> SET ORACLE_SID=sns6
SQL> SQLPLUS/NOLOG
SQL> CONNECT SYS/LINUX@sns1011SRV AS SYSDBA
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sql
SQL> SPOOL OFF
Review the patch.log file for errors and inspect the list of components that is displayed at the end of
catupgrd.sql script.
This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems.


SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are
accessed for the first time. This step is optional but recommended.

SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql

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

3)

mkdir c:\oracle\product\10.2.0\admin\sns1011\adump
mkdir c:\oracle\product\10.2.0\admin\sns1011\bdump
mkdir c:\oracle\product\10.2.0\admin\sns1011\cdump
mkdir c:\oracle\product\10.2.0\admin\sns1011\dpdump
mkdir c:\oracle\product\10.2.0\admin\sns1011\pfile
mkdir c:\oracle\product\10.2.0\admin\sns1011\udump
mkdir c:\oracle\product\10.2.0\db_1\cfgtoollogs\dbca\sns1011
mkdir c:\oracle\product\10.2.0\db_1\database
mkdir c:\oracle\product\10.2.0\oradata
mkdir e:\snsd1011
mkdir e:\archive1011\sns1011

copy /y M:\backup\tnsnames.ora C:\oracle\product\10.2.0\client_1\network\ADMIN\
copy /y M:\backup\initsns6.ora C:\oracle\product\10.2.0\db_1\database\
copy /y M:\backup\SNCFSNS6.ORA C:\oracle\product\10.2.0\db_1\database\


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

4)

Step 1: Create Database Instance (SID)

C:\>set ORACLE_SID=sns6

Step 2: Establish the Database Administrator Authentication Method

c:\>oradim -delete -sid sns6 --if already exist, delete it

C:\>oradim -new -sid sns6 -SRVC OracleServicesns6 -intpwd linux -MAXUSERS 5 -STARTMODE auto -PFILE C:\oracle\product\10.2.0\db_1\database\initsns6.ora

----------not neccessary
C:\>oradim -new -sid sns6 -SYSPWD linux -STARTMODE auto -PFILE C:\oracle\product\10.2.0\db_1\database\initsns6.ora

C:\oracle\product\10.2.0\db_1\database\PWDsns6.ora created (orapwd FILE=PWDsns6.ora ENTRIES=5)
-------------


Step 3: Create the Initialization Parameter File or place the backup on C:\oracle\product\10.2.0\db_1\database

create initsns6.ora file(C:\oracle\product\10.2.0\db_1\database)

SQL> create spfile from pfile;

Step 4: Connect to the Instance

sqlplus /nolog

SQL> connect sys/linux@sns1011srv as sysdba

SQL>startup nomount pfile='C:\oracle\product\10.2.0\db_1\database\initsns6.ora';

Step 5: Create a Server Parameter File (Recommended)

CREATE SPFILE='C:\ORACLE\PRODUCT\10.2.0\db_1\database\spfilesns6.ora’ from

Pfile=’C:\ORACLE\PRODUCT\10.2.0\ADMIN\orcl\pfile\initsns6.ora’;

SHUTDOWN

Step 6

STARTUP NOMOUNT

Step 7: Create database using RMAN


C:\>SET ORACLE_SID=sns6
C:\>RMAN TARGET SYS@SNS1011SRV

c:\>RMAN>SHOW ALL;

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'E:\archive1011\sns1011\F%';
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'E:\archive1011\sns1011\%F';
RMAN> BACKUP DATABASE FORMAT 'E:\archive1011\sns1011\F%';
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'E:\archive1011\sns1011\U%';

RMAN> RESTORE CONTROLFILE FROM 'E:\archieve1011\SNS1011\C-1560435174-20081003-00';
SQL> alter database MOUNT;


RMAN> list backup of database;
RMAN> CROSSCHECK backup of database;
RMAN> delete expired backup;

------not neccessary----------------------
RMAN> CROSSCHECK backup of controlfile;
RMAN> CROSSCHECK archivelog all;
RMAN> delete force obsolete;
RMAN> delete expired archivelog all;

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

RMAN> catalog backuppiece 'E:\archive1011\sns1011\0ULAP4RC_1_1';

RMAN> list backup of database;



RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> recover database using backup controlfile until cancel;

RECOVER THE DATABASE IN DIFFERENT SERVER ON DIFFERENT DIRECTORY FROM ACTUAL BACKUP LOCATION DIRECTORY

SCENIRIO IS I HAVE TAKEN RMAN BACKUP ON A SERVER IN D:\ARCHIVE0910\SNS1011\ LOCATION AND COPY THE BACKUP SET TO B SERVER IN DIFF. DIRECTORY E:\archive1011\sns1011\ LOCATION AND WANT TO RECOVER THE DATABASE ON B SERVER BUT IT IS GIVING ERROR "ORA-19505 failed to identify file "


RMAN> restore database;

Starting restore at 09-APR-10
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to E:\SNSD1011\SYSTEM01.ORA
restoring datafile 00002 to E:\SNSD1011\UNDOTBS01.ORA
restoring datafile 00003 to E:\SNSD1011\SYSAUX01.ORA
restoring datafile 00004 to E:\SNSD1011\INDX01.ORA
restoring datafile 00005 to E:\SNSD1011\USERS01.ORA
channel ORA_DISK_1: reading from backup piece D:\ARCHIVE0910\SNS1011\0OLAHIRK_1_
1
ORA-19870: error reading backup piece D:\ARCHIVE0910\SNS1011\0OLAHIRK_1_1
ORA-19505: failed to identify file "D:\ARCHIVE0910\SNS1011\0OLAHIRK_1_1"
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
failover to previous backup

creating datafile fno=1 name=E:\SNSD1011\SYSTEM01.ORA
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/09/2010 11:33:59
ORA-01180: can not create datafile 1
ORA-01110: data file 1: 'E:\SNSD1011\SYSTEM01.ORA'



Steps: check directory path in init.ora and control.ora but every path is fine as per new server


solution:

if recovery catalog exist then

crosscheck copy of archivelog all;
crosscheck archivelog all;
resync catalog;
delete force obsolete;
delete expired archivelog all;

if not recovery catalog

delete force obsolete;
delete expired archivelog all;


change archivelog all crosscheck;

crosscheck backup of database;
delete expired backup;


----NOW TRY TO RESTORE DATABASE BUT FOLLOWING ERROR----


RMAN> restore database;

Starting restore at 10-APR-10
using channel ORA_DISK_1

creating datafile fno=1 name=E:\SNSD1011\SYSTEM01.ORA
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/10/2010 13:12:43
ORA-01180: can not create datafile 1
ORA-01110: data file 1: 'E:\SNSD1011\SYSTEM01.ORA'





===========================FINAL SOLUTION========



RMAN> list backup of database;

-----NOW DELETE ALL EXPIRED BACKUP

RMAN> CROSSCHECK backup of database;
RMAN> delete expired backup;


----NOT NECCESSARY----
RMAN> CROSSCHECK backup of controlfile;
RMAN> CROSSCHECK archivelog all;

RMAN> delete force obsolete;
RMAN> delete expired archivelog all;

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


RMAN> list backup of database;


SHOWING NO BACKUP

RMAN> catalog backuppiece 'E:\archive1011\sns1011\0ULAP4RC_1_1';


RMAN> list backup of database;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29 Full 4.72G DISK 00:20:28 10-APR-10
BP Key: 30 Status: AVAILABLE Compressed: YES Tag: TAG20100410T115004

Piece Name: E:\ARCHIVE1011\SNS1011\0ULAP4RC_1_1
List of Datafiles in backup set 29
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 385085943 10-APR-10 E:\SNSD1011\SYSTEM01.ORA
2 Full 385085943 10-APR-10 E:\SNSD1011\UNDOTBS01.ORA
3 Full 385085943 10-APR-10 E:\SNSD1011\SYSAUX01.ORA
4 Full 385085943 10-APR-10 E:\SNSD1011\INDX01.ORA
5 Full 385085943 10-APR-10 E:\SNSD1011\USERS01.ORA

RMAN> restore database;
RMAN> recover database;
SQL> alter database open resetlogs;