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



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