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;
-----------------------------
Thursday, June 24, 2010
Wednesday, June 9, 2010
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;
-----------------------------------------------------------------------------------------------------
-- 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/
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;
-----------------------------------------------------------------------------------------------------
Tuesday, June 8, 2010
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; }
------------------------------------------------------------------------
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
(write click see in view source)
locate the file “refhost.xml”, open it with notepad
(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.
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
-----------------------------------
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
-----------------------------------
Thursday, May 20, 2010
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
---------------------------------
@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
------------------------------------------
@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
------------------------------------------
Tuesday, April 20, 2010
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.
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.
----------
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(‘,
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.
----------
Tuesday, April 13, 2010
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;
sql>select file# block# from v$database_block_corruption; --corrupted block
Rman>> block recover datafile
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”
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;
}
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;
}
Monday, April 12, 2010
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\
# 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;
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;
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;
Friday, March 26, 2010
Linux lock unlock user account
----------------------------script.sh---------------
#! /bin/sh
### BEGIN Script INFO
#
# Befor Usering it please check the user name
# file /root/users.txt
#
case "$1" in
lock)
data=`cat /root/users.txt`
for n in $data; do
usermod -L $n && echo "$n is locked"
done
;;
unlock)
data=`cat /root/users.txt`
for n in $data; do
usermod -U $n && echo "$n is unlocked"
done
;;
*)
echo "Usage: $0 {lock|unlock}" >&2
exit 1
;;
esac
exit 0
------------------
users.txt contains users id which have to be locked or unlocked
----------------------------run that script------------
/.script.sh lock
/.script.sh unlock
#! /bin/sh
### BEGIN Script INFO
#
# Befor Usering it please check the user name
# file /root/users.txt
#
case "$1" in
lock)
data=`cat /root/users.txt`
for n in $data; do
usermod -L $n && echo "$n is locked"
done
;;
unlock)
data=`cat /root/users.txt`
for n in $data; do
usermod -U $n && echo "$n is unlocked"
done
;;
*)
echo "Usage: $0 {lock|unlock}" >&2
exit 1
;;
esac
exit 0
------------------
users.txt contains users id which have to be locked or unlocked
----------------------------run that script------------
/.script.sh lock
/.script.sh unlock
Wednesday, March 10, 2010
URL Rewriting - Create Search Engine Friendly URLs
URL Rewriting - Create Search Engine Friendly URLs
Add following line in web.config
mappedUrl="~/ProdAndServ/Commodity.aspx?id=2" />
mappedUrl="~/ProdAndServ/InternetTrading.aspx?id=1" />
Add following line in web.config
mappedUrl="~/ProdAndServ/Commodity.aspx?id=2" />
mappedUrl="~/ProdAndServ/InternetTrading.aspx?id=1" />
Tuesday, March 9, 2010
LD installation
@echo off
M:\3ldtransfer\oracle_10g_10.2.0.3_client\setup.exe
M:\3ldtransfer\VFP5.0\setup.exe
mkdir C:\LD
mkdir C:\ldoutput
copy M:\1SHARED\KshitijLD\LDfolder\*.* C:\LD\
copy m:\ld\ld.exe c:\ld\
copy "c:\ld\ld.exe.lnk" "C:\Documents and Settings\All Users\Desktop"
M:\3ldtransfer\idown\tv_enua.exe
M:\3ldtransfer\idown\spchapi.exe
copy M:\3ldtransfer\idown\BATANG.ttf C:\WINDOWS\Fonts\
copy M:\3ldtransfer\idown\dzc32e.exe C:\
C:\dzc32e.exe
REG ADD "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment" /v apex /t REG_SZ /d m12
REG ADD "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment" /v apexhome /t REG_SZ /d m:
REG ADD "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment" /v orahome /t REG_SZ /d m:
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v ConnectString /t REG_SZ /d sns0506srv
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v DSN /t REG_SZ /d sns0506odbc
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v Driver /t REG_SZ /d C:\WINDOWS\system32\MSORCL10.DLL
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0607odbc /v ConnectString /t REG_SZ /d sns0607srv
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0607odbc /v DSN /t REG_SZ /d sns0607odbc
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0607odbc /v Driver /t REG_SZ /d C:\WINDOWS\system32\MSORCL10.DLL
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0708odbc /v ConnectString /t REG_SZ /d sns0708srv
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0708odbc /v DSN /t REG_SZ /d sns0708odbc
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0708odbc /v Driver /t REG_SZ /d C:\WINDOWS\system32\MSORCL10.DLL
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0809odbc /v ConnectString /t REG_SZ /d sns0809srv
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0809odbc /v DSN /t REG_SZ /d sns0809odbc
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0809odbc /v Driver /t REG_SZ /d C:\WINDOWS\system32\MSORCL10.DLL
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0910odbc /v ConnectString /t REG_SZ /d sns0910srv
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0910odbc /v DSN /t REG_SZ /d sns0910odbc
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0910odbc /v Driver /t REG_SZ /d C:\WINDOWS\system32\MSORCL10.DLL
mkdir C:\oracle\product\10.2.0\client_1\network\ADMIN
copy M:\1SHARED\KshitijLD\tnsnames.ora C:\oracle\product\10.2.0\client_1\network\ADMIN
copy M:\1SHARED\KshitijLD\tnsnames.ora D:\oracle\product\10.2.0\client_1\network\ADMIN
M:\3ldtransfer\oracle_10g_10.2.0.3_client\setup.exe
M:\3ldtransfer\VFP5.0\setup.exe
mkdir C:\LD
mkdir C:\ldoutput
copy M:\1SHARED\KshitijLD\LDfolder\*.* C:\LD\
copy m:\ld\ld.exe c:\ld\
copy "c:\ld\ld.exe.lnk" "C:\Documents and Settings\All Users\Desktop"
M:\3ldtransfer\idown\tv_enua.exe
M:\3ldtransfer\idown\spchapi.exe
copy M:\3ldtransfer\idown\BATANG.ttf C:\WINDOWS\Fonts\
copy M:\3ldtransfer\idown\dzc32e.exe C:\
C:\dzc32e.exe
REG ADD "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment" /v apex /t REG_SZ /d m12
REG ADD "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment" /v apexhome /t REG_SZ /d m:
REG ADD "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment" /v orahome /t REG_SZ /d m:
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v ConnectString /t REG_SZ /d sns0506srv
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v DSN /t REG_SZ /d sns0506odbc
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v Driver /t REG_SZ /d C:\WINDOWS\system32\MSORCL10.DLL
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0607odbc /v ConnectString /t REG_SZ /d sns0607srv
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0607odbc /v DSN /t REG_SZ /d sns0607odbc
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0607odbc /v Driver /t REG_SZ /d C:\WINDOWS\system32\MSORCL10.DLL
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0708odbc /v ConnectString /t REG_SZ /d sns0708srv
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0708odbc /v DSN /t REG_SZ /d sns0708odbc
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0708odbc /v Driver /t REG_SZ /d C:\WINDOWS\system32\MSORCL10.DLL
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0809odbc /v ConnectString /t REG_SZ /d sns0809srv
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0809odbc /v DSN /t REG_SZ /d sns0809odbc
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0809odbc /v Driver /t REG_SZ /d C:\WINDOWS\system32\MSORCL10.DLL
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0910odbc /v ConnectString /t REG_SZ /d sns0910srv
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0910odbc /v DSN /t REG_SZ /d sns0910odbc
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0910odbc /v Driver /t REG_SZ /d C:\WINDOWS\system32\MSORCL10.DLL
mkdir C:\oracle\product\10.2.0\client_1\network\ADMIN
copy M:\1SHARED\KshitijLD\tnsnames.ora C:\oracle\product\10.2.0\client_1\network\ADMIN
copy M:\1SHARED\KshitijLD\tnsnames.ora D:\oracle\product\10.2.0\client_1\network\ADMIN
Wednesday, February 24, 2010
RMAN BACKUP TO NETWORK DRIVE
RMAN BACKUP TO NETWORK DRIVE
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'z:/network/backup_db_%d_S_%s_P_%p_T_%t';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'z:\network\%F';
RMAN> backup current controlfile;
------------------------------ERROR1
Starting backup at 22-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 22-FEB-10
channel ORA_DISK_1: finished piece 1 at 22-FEB-10
piece handle=E:\SNS0910\BACKUP_DB_SNS0910_S_580_P_1_T_711634422 tag=TAG20100222T
121342 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 22-FEB-10
Starting Control File Autobackup at 22-FEB-10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of Control File Autobackup command on ORA_DISK_1 channel at
02/22/2010 12:13:50
ORA-19504: failed to create file "Z:\C-45805873-20100222-02"
ORA-27056: could not delete file
OSD-04029: unable to get file attributes
O/S-Error: (OS 3) The system cannot find the path specified.
--------------------SOLUTION1
Basically all you need to do is update Oracle Services (both listener and Database) to run with Domain Account which has access on both servers with Administrator Privilege.
After you update the services ;
1- Restart the services
2-Use UNC naming instead of using drive letter on your script. (UNC naming part is not mentioned in the note)
3- Run the RMAN backup
Worked fine on my case.
------------------------------ERROR2
C:\>rman target sys@sns0910srv
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Feb 22 13:49:07 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12631: Username retrieval failed
--------------------SOLUTION2
file:///C:/oracle/product/10.2.0/db_1/network/ADMIN/sqlnet.ora
set to none
SQLNET.AUTHENTICATION_SERVICES= (NONE)
------------------------------ERROR3
ERROR IS STILL SAME
provide permission that shared folder or drive for that USER(KGUPTA2)
--------------------DONOT DO ABOVE PRACTICE JUST DO FOLLOWING------------
1) change rman configuration
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '\\ORANOUP01P.UNICON.COM\ksh\%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '\\ORANOUP01P.UNICON.COM\ksh\backup_db_%d_S_%s_P_%p_T_%t';
------Error----
RMAN> backup current controlfile;
Starting backup at 24-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 24-FEB-10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/24/2010 13:02:
14
ORA-19504: failed to create file "\\ORANOUP01P.UNICON.COM\KSH\BACKUP_DB_SNS0910_
S_637_P_1_T_711810132"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.
-------
The problem is at the OS level, not within Oracle. To troubleshoot ORA-27040 errors, you need to test from the OS. You need to check these disk issues
------Steps for correct solution
1) give that user permission to kgupta2 user but same error
2) give folder>properites>shared>permission to kgupta2 but same error
3) give everyone to full permission but same error
4) Just do one thing: give permission for that source computer on that shared folderr>properites>shared>permission on desination server
4) Now RMAN BACKUP TO NETWORK DRIVE work fine
------------------------Final Solution-----
1) change rman configuration
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '\\ORANOUP01P.UNICON.COM\ksh\%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '\\ORANOUP01P.UNICON.COM\ksh\backup_db_%d_S_%s_P_%p_T_%t';
2) for network drive
give permission for that source computer on that shared folderr>properites>shared>permission on desination server
3) Rman backup will work fine
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'z:/network/backup_db_%d_S_%s_P_%p_T_%t';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'z:\network\%F';
RMAN> backup current controlfile;
------------------------------ERROR1
Starting backup at 22-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 22-FEB-10
channel ORA_DISK_1: finished piece 1 at 22-FEB-10
piece handle=E:\SNS0910\BACKUP_DB_SNS0910_S_580_P_1_T_711634422 tag=TAG20100222T
121342 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 22-FEB-10
Starting Control File Autobackup at 22-FEB-10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of Control File Autobackup command on ORA_DISK_1 channel at
02/22/2010 12:13:50
ORA-19504: failed to create file "Z:\C-45805873-20100222-02"
ORA-27056: could not delete file
OSD-04029: unable to get file attributes
O/S-Error: (OS 3) The system cannot find the path specified.
--------------------SOLUTION1
Basically all you need to do is update Oracle Services (both listener and Database) to run with Domain Account which has access on both servers with Administrator Privilege.
After you update the services ;
1- Restart the services
2-Use UNC naming instead of using drive letter on your script. (UNC naming part is not mentioned in the note)
3- Run the RMAN backup
Worked fine on my case.
------------------------------ERROR2
C:\>rman target sys@sns0910srv
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Feb 22 13:49:07 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12631: Username retrieval failed
--------------------SOLUTION2
file:///C:/oracle/product/10.2.0/db_1/network/ADMIN/sqlnet.ora
set to none
SQLNET.AUTHENTICATION_SERVICES= (NONE)
------------------------------ERROR3
ERROR IS STILL SAME
provide permission that shared folder or drive for that USER(KGUPTA2)
--------------------DONOT DO ABOVE PRACTICE JUST DO FOLLOWING------------
1) change rman configuration
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '\\ORANOUP01P.UNICON.COM\ksh\%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '\\ORANOUP01P.UNICON.COM\ksh\backup_db_%d_S_%s_P_%p_T_%t';
------Error----
RMAN> backup current controlfile;
Starting backup at 24-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 24-FEB-10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/24/2010 13:02:
14
ORA-19504: failed to create file "\\ORANOUP01P.UNICON.COM\KSH\BACKUP_DB_SNS0910_
S_637_P_1_T_711810132"
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.
-------
The problem is at the OS level, not within Oracle. To troubleshoot ORA-27040 errors, you need to test from the OS. You need to check these disk issues
------Steps for correct solution
1) give that user permission to kgupta2 user but same error
2) give folder>properites>shared>permission to kgupta2 but same error
3) give everyone to full permission but same error
4) Just do one thing: give permission for that source computer on that shared folderr>properites>shared>permission on desination server
4) Now RMAN BACKUP TO NETWORK DRIVE work fine
------------------------Final Solution-----
1) change rman configuration
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '\\ORANOUP01P.UNICON.COM\ksh\%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '\\ORANOUP01P.UNICON.COM\ksh\backup_db_%d_S_%s_P_%p_T_%t';
2) for network drive
give permission for that source computer on that shared folderr>properites>shared>permission on desination server
3) Rman backup will work fine
Monday, February 8, 2010
USB drive Disable Enable
Set deny permissions for the user/s and/or group/s to:
%SystemRoot%\Inf\Usbstor.pnf
%SystemRoot%\Inf\Usbstor.inf
It is highly recommended to rename the file names before changing
permissions.
To disable the access to USB port, in windows XP and 2000, follow the steps below
1. Click Start, and then click Run.
2. In the Open box, type regedit, and then click OK.
3. Locate, and then click the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\UsbStor
4. In the right pane, double-click Start.
5. In the Value data box, type 4, click Hexadecimal (if it is not already selected), and then click OK.
6. Quit Registry Editor.
To re-enable a disabled port, follow these steps below
1. Click Start, and then click Run.
2. In the Open box, type regedit, and then click OK.
3. Locate, and then click the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\UsbStor
4. In the right pane, double-click Start.
5. In the Value data box, type 3, click Hexadecimal (if it is not already selected), and then click OK.
6. Quit Registry Editor.
%SystemRoot%\Inf\Usbstor.pnf
%SystemRoot%\Inf\Usbstor.inf
It is highly recommended to rename the file names before changing
permissions.
To disable the access to USB port, in windows XP and 2000, follow the steps below
1. Click Start, and then click Run.
2. In the Open box, type regedit, and then click OK.
3. Locate, and then click the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\UsbStor
4. In the right pane, double-click Start.
5. In the Value data box, type 4, click Hexadecimal (if it is not already selected), and then click OK.
6. Quit Registry Editor.
To re-enable a disabled port, follow these steps below
1. Click Start, and then click Run.
2. In the Open box, type regedit, and then click OK.
3. Locate, and then click the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\UsbStor
4. In the right pane, double-click Start.
5. In the Value data box, type 3, click Hexadecimal (if it is not already selected), and then click OK.
6. Quit Registry Editor.
Change ORACLE SID
select name from v$database;
select instance_name from v$instance;
shutdown immediate;
C:\>set ORACLE_SID=sidorcl
C:\>net stop oracleservicesidorcl
C:\>ORADIM -DELETE -SID sidorcl
C:\>set ORACLE_SID=sidorclnew
C:\>ORAPWD FILE=c:\oracle\product\10.1.0\db_1\database\pwdsidorclnew.ora PASSWORD=oracle ENTRIES=5 FORCE=y
C:\>ORADIM -new -sid sidorclnew -startmode manual -spfile
C:\>oradim -edit -sid sidorclnew -startmode auto
C:\>rename c:\oracle\product\10.1.0\db_1\database\initSIDORCL.ORA initsidorclnew.ORA
C:\>sqlplus/nolog
conn sys as sysdba
create spfile from pfile;
startup
select name from v$database;
select instance_name from v$instance;
select instance_name from v$instance;
shutdown immediate;
C:\>set ORACLE_SID=sidorcl
C:\>net stop oracleservicesidorcl
C:\>ORADIM -DELETE -SID sidorcl
C:\>set ORACLE_SID=sidorclnew
C:\>ORAPWD FILE=c:\oracle\product\10.1.0\db_1\database\pwdsidorclnew.ora PASSWORD=oracle ENTRIES=5 FORCE=y
C:\>ORADIM -new -sid sidorclnew -startmode manual -spfile
C:\>oradim -edit -sid sidorclnew -startmode auto
C:\>rename c:\oracle\product\10.1.0\db_1\database\initSIDORCL.ORA initsidorclnew.ORA
C:\>sqlplus/nolog
conn sys as sysdba
create spfile from pfile;
startup
select name from v$database;
select instance_name from v$instance;
Oracle Patch 10.20.3 to 10.2.0.4
Oracle Patch 10.20.3 to 10.2.0.4
How to Apply the Patchset ?
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=sns5
SQL> SQLPLUS/NOLOG
SQL>
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.
Restart the database:
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
Thursday, January 28, 2010
Disable right mouse click script
--
--var message="Sorry,right click is Disabled!";
---function clickIE4(){
--if (event.button==2){
--alert(message);
--return false;
--}
---}
--function clickNS4(e){
--if (document.layers||document.getElementById&&!document.all){
--if (e.which==2||e.which==3){
---alert(message);
--return false;
--}
---}
--}
---if (document.layers){
---document.captureEvents(Event.MOUSEDOWN);
---document.onmousedown=clickNS4;
---}
---else if (document.all&&!document.getElementById){
---document.onmousedown=clickIE4;
--}
----document.oncontextmenu=new Function("alert(message);return false")
----
--var message="Sorry,right click is Disabled!";
---function clickIE4(){
--if (event.button==2){
--alert(message);
--return false;
--}
---}
--function clickNS4(e){
--if (document.layers||document.getElementById&&!document.all){
--if (e.which==2||e.which==3){
---alert(message);
--return false;
--}
---}
--}
---if (document.layers){
---document.captureEvents(Event.MOUSEDOWN);
---document.onmousedown=clickNS4;
---}
---else if (document.all&&!document.getElementById){
---document.onmousedown=clickIE4;
--}
----document.oncontextmenu=new Function("alert(message);return false")
----
Friday, January 22, 2010
Regedit for Oracle
copy "c:\ld\ld.exe.lnk" "c:\documents and settings\%username%\desktop"
Set Environmental variable through regedit
For Machine
REG ADD "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment" /v apex /t REG_SZ /d m01
REG ADD "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment" /v apexhome /t REG_SZ /d m:
REG ADD "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment" /v orahome /t REG_SZ /d m:
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v ConnectString /t REG_SZ /d sns0506srv
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v DSN /t REG_SZ /d sns0506odbc
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v Driver /t REG_SZ /d C:\WINDOWS\system32\MSORCL10.DLL
For User
REG ADD HKCU\Environment /v apex /t REG_SZ /d m01
REG ADD HKCU\Environment /v apexhome /t REG_SZ /d m:
REG ADD HKCU\Environment /v orahome /t REG_SZ /d m:
REG ADD HKCU\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v ConnectString /t REG_SZ /d sns0506srv
REG ADD HKCU\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v DSN /t REG_SZ /d sns0506odbc
REG ADD HKCU\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v Driver /t REG_SZ /d C:\WINDOWS\system32\MSORCL10.DLL
Set Environmental variable through regedit
For Machine
REG ADD "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment" /v apex /t REG_SZ /d m01
REG ADD "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment" /v apexhome /t REG_SZ /d m:
REG ADD "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment" /v orahome /t REG_SZ /d m:
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v ConnectString /t REG_SZ /d sns0506srv
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v DSN /t REG_SZ /d sns0506odbc
REG ADD HKLM\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v Driver /t REG_SZ /d C:\WINDOWS\system32\MSORCL10.DLL
For User
REG ADD HKCU\Environment /v apex /t REG_SZ /d m01
REG ADD HKCU\Environment /v apexhome /t REG_SZ /d m:
REG ADD HKCU\Environment /v orahome /t REG_SZ /d m:
REG ADD HKCU\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v ConnectString /t REG_SZ /d sns0506srv
REG ADD HKCU\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v DSN /t REG_SZ /d sns0506odbc
REG ADD HKCU\SOFTWARE\ODBC\ODBC.INI\sns0506odbc /v Driver /t REG_SZ /d C:\WINDOWS\system32\MSORCL10.DLL
how to recover deleted file from network drive
Issue: how to recover deleted file from network drive
Solution:
settings on Server
1. go the properties of Share Network Drive
2. Shadow Copies
3. Setting as per your requirement
settings on Client
To recover a file that was accidentally deleted
1. Locate the folder where the deleted file was stored (on the network), right-click the folder, and click Properties. The Properties dialog box will appear.
2. On the Previous Versions tab, double-click the most recent version of the folder that contains the file that you want to recover. A list of files that are stored in that previous version will appear.
3. Right-click the file that was deleted and clicks Copy and paste.
Solution:
settings on Server
1. go the properties of Share Network Drive
2. Shadow Copies
3. Setting as per your requirement
settings on Client
To recover a file that was accidentally deleted
1. Locate the folder where the deleted file was stored (on the network), right-click the folder, and click Properties. The Properties dialog box will appear.
2. On the Previous Versions tab, double-click the most recent version of the folder that contains the file that you want to recover. A list of files that are stored in that previous version will appear.
3. Right-click the file that was deleted and clicks Copy and paste.
how to recover deleted file from network drive
Issue: how to recover deleted file from network drive
Solution:
settings on Server
1. go the properties of Share Network Drive
2. Shadow Copies
3. Setting as per your requirement
settings on Client
To recover a file that was accidentally deleted
1. Locate the folder where the deleted file was stored (on the network), right-click the folder, and click Properties. The Properties dialog box will appear.
2. On the Previous Versions tab, double-click the most recent version of the folder that contains the file that you want to recover. A list of files that are stored in that previous version will appear.
3. Right-click the file that was deleted and clicks Copy and paste.
Solution:
settings on Server
1. go the properties of Share Network Drive
2. Shadow Copies
3. Setting as per your requirement
settings on Client
To recover a file that was accidentally deleted
1. Locate the folder where the deleted file was stored (on the network), right-click the folder, and click Properties. The Properties dialog box will appear.
2. On the Previous Versions tab, double-click the most recent version of the folder that contains the file that you want to recover. A list of files that are stored in that previous version will appear.
3. Right-click the file that was deleted and clicks Copy and paste.
Connect Sql Server in SqlDeveloper Tool
1) download net.sourceforge.jtds.jdbc.Driver
http://sourceforge.net/projects/jtds/files/jtds/1.2.1/jtds-1.2.1-src.zip/download
2) In Oracle SQL Developer open Tools -> Preferences Database -> Third Party JDBC Drivers
Add Entry
Browse the unzipped driver and add the jtds-1.2.jar file
3) Error
I/O Error SSO failed Native SSPI library not loaded
check the java.library.path.system property
Copy the file 'jtds-1.2.2-dist\x86\SSO\ntlmauth.dll' from the unzipped JTDS -->\jdk\jre\bin.
4) Then restart SQL Developer.
http://sourceforge.net/projects/jtds/files/jtds/1.2.1/jtds-1.2.1-src.zip/download
2) In Oracle SQL Developer open Tools -> Preferences Database -> Third Party JDBC Drivers
Add Entry
Browse the unzipped driver and add the jtds-1.2.jar file
3) Error
I/O Error SSO failed Native SSPI library not loaded
check the java.library.path.system property
Copy the file 'jtds-1.2.2-dist\x86\SSO\ntlmauth.dll' from the unzipped JTDS -->
4) Then restart SQL Developer.
Wednesday, November 11, 2009
Oracle File Formats
-------------------Backup Format-----
1
%c The copy number of the backup piece within a set of duplexed
backup pieces. If you did not duplex a backup, then this variable
is 1 for backup sets and 0 for proxy copies.
If one of these commands is enabled, then the variable shows the
copy number. The maximum value for %c is 256.
%d The name of the database.
%D The current day of the month (in format DD)
%F Combination of DBID, day, month, year, and sequence into a unique
and repeatable generated name.
%M The month (format MM)
%n The name of the database, padded on the right with x characters
to a total length of eight characters. (AKA: Porn star alias name)
For example, if the scott is the database name, %n= scottxxx.
%p The piece number within the backup set. This value starts at 1
for each backup set and is incremented by 1 as each backup piece
is created. Note: If you specify PROXY, then the %p variable must
be included in the FORMAT string either explicitly or implicitly within %U.
%s The backup set number. This number is a counter in the control file that
is incremented for each backup set. The counter value starts at 1 and is
unique for the lifetime of the control file. If you restore a backup
control file, then duplicate values can result.
Also, CREATE CONTROLFILE initializes the counter back to 1.
%t The backup set time stamp, which is a 4-byte value derived as the
number of seconds elapsed since a fixed reference time.
The combination of %s and %t can be used to form a unique name for
the backup set.
%T The year, month, and day (YYYYMMDD)
%u An 8-character name constituted by compressed representations of
the backup set number and the time the backup set was created.
%U A convenient shorthand for %u_%p_%c that guarantees uniqueness in
generated backup filenames.
If you do not specify a format, RMAN uses %U by default.
%Y The year (YYYY)
%% Specifies the '%' character. e.g. %%Y translates to %Y.
------------ARCHIVELOG Format------
%s log sequence number
%S log sequence number, zero filled
%tthread number
%Tthread number, zero filled
%a activation ID
%d database ID
%R resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database
---------------------------
1
%c The copy number of the backup piece within a set of duplexed
backup pieces. If you did not duplex a backup, then this variable
is 1 for backup sets and 0 for proxy copies.
If one of these commands is enabled, then the variable shows the
copy number. The maximum value for %c is 256.
%d The name of the database.
%D The current day of the month (in format DD)
%F Combination of DBID, day, month, year, and sequence into a unique
and repeatable generated name.
%M The month (format MM)
%n The name of the database, padded on the right with x characters
to a total length of eight characters. (AKA: Porn star alias name)
For example, if the scott is the database name, %n= scottxxx.
%p The piece number within the backup set. This value starts at 1
for each backup set and is incremented by 1 as each backup piece
is created. Note: If you specify PROXY, then the %p variable must
be included in the FORMAT string either explicitly or implicitly within %U.
%s The backup set number. This number is a counter in the control file that
is incremented for each backup set. The counter value starts at 1 and is
unique for the lifetime of the control file. If you restore a backup
control file, then duplicate values can result.
Also, CREATE CONTROLFILE initializes the counter back to 1.
%t The backup set time stamp, which is a 4-byte value derived as the
number of seconds elapsed since a fixed reference time.
The combination of %s and %t can be used to form a unique name for
the backup set.
%T The year, month, and day (YYYYMMDD)
%u An 8-character name constituted by compressed representations of
the backup set number and the time the backup set was created.
%U A convenient shorthand for %u_%p_%c that guarantees uniqueness in
generated backup filenames.
If you do not specify a format, RMAN uses %U by default.
%Y The year (YYYY)
%% Specifies the '%' character. e.g. %%Y translates to %Y.
------------ARCHIVELOG Format------
%s log sequence number
%S log sequence number, zero filled
%tthread number
%Tthread number, zero filled
%a activation ID
%d database ID
%R resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database
---------------------------
Tuesday, October 6, 2009
how to recover deleted file from network drive
Issue: how to recover deleted file from network drive
Solution:
settings on Server
1. go the properties of Share Network Drive
2. Shadow Copies
3. Setting as per your requirement
Settings on Client
1. Locate the folder where the deleted file was stored (on the network), right-click the folder, and click Properties. The Properties dialog box will appear.
2. On the Previous Versions tab, double-click the most recent version of the folder that contains the file that you want to recover. A list of files that are stored in that previous version will appear.
3. Right-click the file that was deleted and clicks Copy and paste.
Solution:
settings on Server
1. go the properties of Share Network Drive
2. Shadow Copies
3. Setting as per your requirement
Settings on Client
1. Locate the folder where the deleted file was stored (on the network), right-click the folder, and click Properties. The Properties dialog box will appear.
2. On the Previous Versions tab, double-click the most recent version of the folder that contains the file that you want to recover. A list of files that are stored in that previous version will appear.
3. Right-click the file that was deleted and clicks Copy and paste.
Windows terminate process forcefully
Windows terminate process forcefully
taskkill /f /im process-name.exe
/f - Stands for that process be forcefully terminated.
/im - Stands for the image name of the process to be terminated
In order to kill all these process I made a batch file which contains the forcefull termination command for all these programs and then I added the batch file in windows startup.
1. open Notepad and paste the following commands one per each line by line
taskkill /f /im wmpnscfg.exe
taskkill /f /im ctfmon.exe
taskkill /f /im mobsync.exe.exe
Read more: http://www.technixupdate.com/terminate-programs-forcefully-in-windows/#ixzz0QE2O6QIR
2. Save the file as terminate.bat or with any other name but with .bat extension.
3. Now Drag and Drop terminate.bat file in All Programs >> StartUp
4. Now, restart windows all these useless programs which you used to kill manually will be automatically terminated.
Note: You can also place the terminate.bat file on desktop and run it manually and kill all the useless process.
Read more: http://www.technixupdate.com/terminate-programs-forcefully-in-windows/#ixzz0QE2POIGP
taskkill /f /im process-name.exe
/f - Stands for that process be forcefully terminated.
/im - Stands for the image name of the process to be terminated
In order to kill all these process I made a batch file which contains the forcefull termination command for all these programs and then I added the batch file in windows startup.
1. open Notepad and paste the following commands one per each line by line
taskkill /f /im wmpnscfg.exe
taskkill /f /im ctfmon.exe
taskkill /f /im mobsync.exe.exe
Read more: http://www.technixupdate.com/terminate-programs-forcefully-in-windows/#ixzz0QE2O6QIR
2. Save the file as terminate.bat or with any other name but with .bat extension.
3. Now Drag and Drop terminate.bat file in All Programs >> StartUp
4. Now, restart windows all these useless programs which you used to kill manually will be automatically terminated.
Note: You can also place the terminate.bat file on desktop and run it manually and kill all the useless process.
Read more: http://www.technixupdate.com/terminate-programs-forcefully-in-windows/#ixzz0QE2POIGP
Subscribe to:
Posts (Atom)