Thursday, 21 June 2012
ASM,LUN & SAN STORAGE
LUN management at the heart of SAN configuration.
Disk drives are the foundation of modern data storage, but operating systems cannot use physical storage directly. The platters, heads, tracks and sectors of a physical drive must be translated into a logical space that an operating system sees as a linear address space comprised of fixed-size blocks. This translation process creates a logical entity that allows operating systems to read/write files. Storage networks must also partition their physical disks into logical entities so that host servers can access storage area network (SAN) storage, and each logical portion is called a logical unit number (LUN). This article explains essential LUN concepts and the role of LUNs in SAN technology.
What is LUN?
If suppose we got a large storage array, and requirement is to not allow one server to use all storage spaces, so it need to divided into logical units as LUN(Logical Unit Number). So LUN allow us slice storage array into usable storage chunks and present same to server. LUN basically refer to either a entire physical volume or subset of larger physical disk or volume. LUN represent logical abstraction or you can say virtual layer between physical disk and application. A LUN is scsi concept.
Preparing Disks for ASM
You can create an ASM disk group using one of the following storage resources:
Raw disk partition—A raw partition can be the entire disk drive or a section of a disk drive. However, the ASM disk cannot be in a partition that includes the partition table because the partition table can be overwritten.
Logical unit numbers (LUNs)—Using hardware RAID functionality to create LUNs is a recommended approach. Storage hardware RAID 0+1 or RAID5, and other RAID configurations, can be provided to ASM as ASM disks.
Raw logical volumes (LVM)—LVMs are supported in less complicated configurations where an LVM is mapped to a LUN, or an LVM uses disks or raw partitions. LVM configurations are not recommended by Oracle because they create a duplication of functionality. Oracle also does not recommended using LVMs for mirroring because ASM already provides mirroring.
NFS files—ASM supports NFS files as ASM disks. Oracle Database has built-in support for the network file system (NFS) and does not depend on OS support for NFS. Although NFS and ASM have overlapping functionality, ASM can load balance or mirror across NFS files.
The procedures for preparing storage resources for ASM are:
Identify or create the storage devices for ASM by identifying all of the storage resource device names that you can use to create an ASM disk group. For example, on Linux systems, device names are typically presented from the /dev directory with the /dev/device_name_identifier name syntax.
Change the ownership and the permissions on storage device resources. For example, the following steps are required on Linux systems:
Change the user and group ownership of devices to oracle:dba
Change the device permissions to read/write
On older Linux versions, you must configure raw device binding
After you have configured ASM, ensure that disk discovery has been configured correctly by setting the ASM_DISKSTRING initialization parameter.
Disk drives are the foundation of modern data storage, but operating systems cannot use physical storage directly. The platters, heads, tracks and sectors of a physical drive must be translated into a logical space that an operating system sees as a linear address space comprised of fixed-size blocks. This translation process creates a logical entity that allows operating systems to read/write files. Storage networks must also partition their physical disks into logical entities so that host servers can access storage area network (SAN) storage, and each logical portion is called a logical unit number (LUN). This article explains essential LUN concepts and the role of LUNs in SAN technology.
What is LUN?
If suppose we got a large storage array, and requirement is to not allow one server to use all storage spaces, so it need to divided into logical units as LUN(Logical Unit Number). So LUN allow us slice storage array into usable storage chunks and present same to server. LUN basically refer to either a entire physical volume or subset of larger physical disk or volume. LUN represent logical abstraction or you can say virtual layer between physical disk and application. A LUN is scsi concept.
Preparing Disks for ASM
You can create an ASM disk group using one of the following storage resources:
Raw disk partition—A raw partition can be the entire disk drive or a section of a disk drive. However, the ASM disk cannot be in a partition that includes the partition table because the partition table can be overwritten.
Logical unit numbers (LUNs)—Using hardware RAID functionality to create LUNs is a recommended approach. Storage hardware RAID 0+1 or RAID5, and other RAID configurations, can be provided to ASM as ASM disks.
Raw logical volumes (LVM)—LVMs are supported in less complicated configurations where an LVM is mapped to a LUN, or an LVM uses disks or raw partitions. LVM configurations are not recommended by Oracle because they create a duplication of functionality. Oracle also does not recommended using LVMs for mirroring because ASM already provides mirroring.
NFS files—ASM supports NFS files as ASM disks. Oracle Database has built-in support for the network file system (NFS) and does not depend on OS support for NFS. Although NFS and ASM have overlapping functionality, ASM can load balance or mirror across NFS files.
The procedures for preparing storage resources for ASM are:
Identify or create the storage devices for ASM by identifying all of the storage resource device names that you can use to create an ASM disk group. For example, on Linux systems, device names are typically presented from the /dev directory with the /dev/device_name_identifier name syntax.
Change the ownership and the permissions on storage device resources. For example, the following steps are required on Linux systems:
Change the user and group ownership of devices to oracle:dba
Change the device permissions to read/write
On older Linux versions, you must configure raw device binding
After you have configured ASM, ensure that disk discovery has been configured correctly by setting the ASM_DISKSTRING initialization parameter.
Tuesday, 19 June 2012
PATCH UPGRADE & DOWNGRADE
ORACLE PATCH UPGRADE…?
1.Method
Fresh Oralce Software Installed (not create any database) then after apply patch .
2.Method
Shutdown all Oracle Windows services
Install 10.2.04 patchset (run setup)
RUN > setup > change home path (ORACLE_HOME _2 to ORACLE_HOME_1)
Startup all Oracle Windows services
NOTE:- When the the database not created then normaly upgrade patch ,But when the database exist then be carefully updated patch see following steps:-
from CMD prompt: sqlplus /nolog
from SQL prompt: connect sys/sys@db as sysdba
after connect:
idel instance show
shutdown immediate
after shutdown:
startup upgrade
after startup:
@ D:\oracle\product\10.2.0\db_1\rdbms\admin\catupgrd.sql
after script finishes (it could take a while)
(Ques)..DOWNGRADE PATCH..........?
For more Info read doc:- Doc ID 1151427.1
https://blogs.oracle.com/db/entry/master_note_for_oracle_database_downgrade_doc_id_11514271
12.1 Removing the Patch Set Software for Single Instance Installation
Perform the following steps for removing the patch set from the 10.2.0.4 patch release:
Use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:
$ sqlplus /NOLOG
SQL> CONNECT SYS AS SYSDBA
# Enter password:SYS_password
SQL> SHUTDOWN IMMEDIATE
SQL> EXIT
# Take a backup of the following file in 10.2.0.4 Oracle home directory:
D:\oracle\product\10.2.0\db_1\rdbms\admin\catrelod.sql
D:\oracle\product\10.2.0\db_1\rdbms\admin\tnsnames.ora
# Enter the following SQL*Plus commands:
$ cd $ORACLE_HOME/bin
$ ./sqlplus /NOLOG
SQL> CONNECT SYS AS SYSDBA
Enter password:SYS_password
SQL> STARTUP DOWNGRADE
SQL> SPOOL C:\downgrade\downgrade.out
SQL> @ D:\oracle\product\10.2.0\db_1\rdbms\admin\catdwgrd.sql
SQL> SPOOL OFF
SQL> SHUTDOWN IMMEDIATE
SQL> EXIT
Review the downgrade.out file for errors.
After restoring the original Oracle home, copy the saved version of catrelod.sql script into the restored Oracle home rdbms/admin directory.
Also copy the saved version of 10.2.0.4/network/admin/tnsnames.ora directory into the restored
Oracle home /network/admin/tnsnames.ora directory, and
then perform the following steps:
$ sqlplus /NOLOG
SQL> CONNECT SYS AS SYSDBA
Enter password:SYS_password
SQL> STARTUP DOWNGRADE
SQL> SPOOL C:\downgrade\catrelod.out
SQL> @ D:\oracle\product\10.2.0\db_1\rdbms\admin\catrelod.sql
SQL> SPOOL OFF
SQL> SHUTDOWN IMMEDIATE
Review the catrelod.out file for errors.
Start the database and recompile the remaining invalid objects:
SQL> STARTUP
SQL> @ D:\oracle\product\10.2.0\db_1\rdbms\admin\utlrp.sql
Cross-check the status of installed components with the following script:
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
Friday, 15 June 2012
FLASHBACK DATABASE
Assuming that you have prepared a flash recovery area for the database and enabled media recovery, enable database FLASHBACK mode and open the database with the following statements:
STARTUP MOUNT
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
With your database open for at least a day, you can flash back the database one day with the following statements:
SHUTDOWN DATABASE
STARTUP MOUNT
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1;
ALTER DATABASE OPEN RESETLOGS;
OR
STARTUP FORCE MOUNT
FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('24-11-2011 10:12:12','DD-MM-YYYY HH24:11:2011');
ALTER DATABASE OPEN RESETLOGS
FLASHBACK
FIND SCN NUMBER IN FLASHBACK (TIMEBASED FIND SCN QUERY)
SELECT timestamp_to_scn(to_timestamp('24-11-2011 02:02:00', 'DD-MM-YYYY HH12:MI:SS')) as scn from dual;
===================
FLASHBACK DROP:-
===================
alter system set log_archive_dest_1='location=c:\test_flash_arcc','mandatory';
shutdown immediate
startup mount
alter database archivelog;
alter database open;
* DB_RECOVERY_FILE_DEST_SIZE
* DB_RECOVERY_FILE_DEST
alter system set db_recovery_file_dest_size=4g;
alter system set db_recovery_file_dest='e:\ora_flashback';
shutdown immediate
startup mount
alter system set db_flashback_retention_target=4320;
alter database flashback on;
alter database open;
SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
SQL>SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
################################################################################
How to Enable Flashback Database
To enable flashback database the following operations is needed.
1)Configure the Database in archivelog mode.
To change archiving read, Change Archival Mode
2)Configure Flash Recovery Area.
To configure flash recovery area,
Set up Flash Recovery Area
3)Clean Shutdown and mount the database.
Alter Database Flashback ON;
Before running command you can check whether flashback was actually On or not.
select flashback_on from v$database;
Steps:
------
SQL> alter database flashback ON;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
4)Open the database and optionally you can set DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes. By default it is 1 day(1440 minutes).
To make it 3 days
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 4320
However you can disable Flashback Database for a tablespace.Then you must take its datafiles offline before running FLASHBACK DATABASE.
Like,
SQL> select file_name,file_id from dba_data_files where tablespace_name='TEST';
FILE_NAME FILE_ID
------------------------------ ----------
/oradata2/1.dbf 5
SQL> alter database datafile 5 offline;
Database altered.
SQL> ALTER TABLESPACE test flashback off;
Tablespace altered.
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
To disable flashback feature simply issue,
SQL>ALTER DATABASE FLASHBACK OFF;
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
SQL>ATER DATABASE FLASHBACK ON;
SQL>ALTER DATABASE OPEN;
SQL>SELECT * FROM V$BGPROCESS;
SQL>SELECT NAME FROM V$BGPROCESS WHERE NAME='RVWR';
SQL>CREATE TABLE SCOTT.EMP1 AS SELECT * FROM SCOTT.EMP;
SQL> CREATE TABLE SCOTT.EMP2 AS SELECT * FROM SCOTT.EMP;
SQL> CREATE TABLE SCOTT.EMP3 AS SELECT * FROM SCOTT.EMP;
SQL>DROP TABLE SCOTT.EMP1
SQL>SELECT * FROM V$FLASHBACK_DATABASE_LOG;
SQL>SHOW RECYCLEBIN
SQL>SELECT * FROM DBA_RECYCLEBIN;
SQL>FLSHBACK TABLE EMP1 TO BEFORE DROP;
SQL>SELECT * FROM TAB WHERE TNAME LIKE 'EMP1';
SQL>DROP TABLE SCOTT.EMP2;
SQL>DROP TABLE SCOTT.EMP3;
SQL>SHOW RECYCLEBIN
SQL>SELECT * FROM DBA_RECYCLEBIN;
SQL>FLASHBACK TABLE EMP2 TO BEFORE DROP RENAME TO EMP21;
SQL>FLASHBACK TABLE EMP3 TO BEFORE DROP RENAME TO EMP31;
SQL>SHOW RECYCLEBIN
SQL>SELECT * FROM DBA_RECYCLEBIN;
=================================================================================================
==================================================================================
FLASHBACK QUERY: QUERY ALL DATA AS IT EXISTED AT A SPECIFIC POINT IN TIME STEPS
==================================================================================
SQL> CREATE TABLE SCOTT.ABC (A NUMBER(10),B VARCHAR2(10)) ENABLE ROW MOVEMENT;
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1354136
SQL> INSERT INTO SCOTT.ABC VALUES(1,'AA');
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1354172
SQL> INSERT INTO SCOTT.ABC VALUES(2,'BB');
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1354204
SQL> INSERT INTO SCOTT.ABC VALUES(3,'CC');
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1354382
SQL> INSERT INTO SCOTT.ABC VALUES(4,'DD');
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1354410
SQL> INSERT INTO SCOTT.ABC VALUES(5,'EE');
SQL> COMMIT;
I. NOTE:- See all SCN or Timestamp are same because u'll commit the transaction value only one time after insert the data
SQL> SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='ABC' ORDER BY 5,11;
SQL> INSERT INTO SCOTT.ABC VALUES(6,'FF');
SQL> COMMIT;
SQL> INSERT INTO SCOTT.ABC VALUES(7,'GG');
SQL> COMMIT;
SQL> INSERT INTO SCOTT.ABC VALUES(8,'HH');
SQL> COMMIT;
SQL> INSERT INTO SCOTT.ABC VALUES(9,'II');
SQL> COMMIT;
SQL> INSERT INTO SCOTT.ABC VALUES(10,'JJ');
SQL> COMMIT;
II. NOTE:- See these 5 insert value SCN or Timestamp are different because every insertion after commit the value
SQL>SQL> SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='ABC' ORDER BY 5,11;
SQL> INSERT INTO SCOTT.ABC VALUES(11,'KK');
SQL> INSERT INTO SCOTT.ABC VALUES(12,'LL');
SQL> INSERT INTO SCOTT.ABC VALUES(13,'MM');
SQL> INSERT INTO SCOTT.ABC VALUES(14,'NN');
SQL> INSERT INTO SCOTT.ABC VALUES(15,'OO');
SQL> COMMIT;
III. NOTE:- See NOTE 1 again
SQL>SQL> SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='ABC' ORDER BY 5,11;
##NOTE:- SEE FLASHBACK QUERY DML PERFORM
============================================
SQL> SELECT COUNT(*) FROM SCOTT.ABC;
COUNT(*)
----------
15
SQL> SELECT * FROM SCOTT.ABC AS OF SCN 1354802;
A B
---------- ----------
1 AA
2 BB
3 CC
4 DD
5 EE
6 FF
7 GG
8 HH
9 II
10 JJ
10 rows selected.
SQL> SELECT COUNT(*) FROM SCOTT.ABC;
COUNT(*)
----------
15
####UPDATE
SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='ABC' ORDER BY 5,11;
SQL> UPDATE SCOTT.ABC SET B='TEST' WHERE A IN (5,6,7,8,9,10)
SQL> COMMIT;
SQL> SELECT * FROM SCOTT.ABC AS OF SCN 1354802;
A B
---------- ----------
1 AA
2 BB
3 CC
4 DD
5 EE
6 FF
7 GG
8 HH
9 II
10 JJ
SQL> SELECT * FROM SCOTT.ABC;
A B
---------- ----------
1 AA
2 BB
3 CC
4 DD
5 TEST
6 TEST
7 TEST
8 TEST
9 TEST
10 TEST
11 KK
12 LL
13 MM
14 NN
15 OO
SQL> SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='ABC' ORDER BY 5,11;
SQL> UPDATE SCOTT.ABC SET B=(SELECT B
FROM SCOTT.ABC AS OF SCN 1354802
WHERE A IN (5))
WHERE A IN (5,6,7,8,9,10);
SQL> COMMIT;
SQL> SELECT * FROM SCOTT.ABC;
A B
------ ----------
1 AA
2 BB
3 CC
4 DD
5 EE
6 EE
7 EE
8 EE
9 EE
10 EE
11 KK
12 LL
13 MM
14 NN
15 OO
## NOTE:- SAME AS TIMESTAMP BASE
SELECT * FROM SCOTT.ABC AS OF TIMESTAMP TO_TIMESTAMP('29-11-2011 11:05:00','DD-MM-YYYY HH12:MI:SS');
SELECT * FROM SCOTT.ACB AS OF SCN;
===============================================================================================
FLASHBACK TABLE (Flashback Transaction Query)
============================================================================================
CREATE TABLE ABC (A NUMBER(10),B VARCHAR(10));
INSERT INTO ABC VALUES(1,'A');
..after 2 minutes insert
INSERT INTO ABC VALUES(2,'B');
..after 2 minutes insert
INSERT INTO ABC VALUES(3,'C');
..after 2 minutes insert
INSERT INTO ABC VALUES(4,'D');
..after 2 minutes insert
INSERT INTO ABC VALUES(5,'E');
..after 2 minutes insert
COMMIT;
SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='ABC'
ALTER TABLE ABC ENABLE ROW MOVEMENT;
SELECT COUNT(*) FROM SCOTT.ABC;
SELECT * FROM SCOTT.ABC;
SELECT CURRENT_SCN FROM V$DATABASE;
SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='ABC'
UPDATE TABLE SCOTT.ABC SET B='VINAY' WHERE A=5;
(METHODS:-)
============
(SCN BASED)
1.FLASHBACK TABLE SCOTT.ACB TO SCN 1301875; //see the SCN no. in view (FLASHBACK_TRANSACTION_QUERY) to the limit of back word of data before the update command
(TIMESTAMP BASED)
2.FLASHBACK TABLE LDBO.ABC TO TIMESTAMP TO_TIMESTAMP('25-11-2011 04:01:37','DD-MM-YYYY HH12:MI:SS'); //timestampbase see the time in view (FLASHBACK_TRANSACTION_QUERY)
(TIMESTAMP WITH INTERVAL BASED)
3.FLASHBACK TABLE SCOTT.ABC TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);
(RESTORE POINT BASED)
4.FLASHBACK TABLE SCOTT.ACB TO RESTORE POINT THREE;
CREATE TABLE ABC (A NUMBER(10),B VARCHAR(10)) ENABLE ROW MOVEMENT;
CREATE RESTORE POINT ZERO;
INSERT INTO ABC VALUES(1,'A');
CREATE RESTORE POINT ONE;
INSERT INTO ABC VALUES(2,'B');
CREATE RESTORE POINT TWO;
INSERT INTO ABC VALUES(3,'C');
CREATE RESTORE POINT THREE;
INSERT INTO ABC VALUES(4,'D');
CREATE RESTORE POINT FOUR;
INSERT INTO ABC VALUES(5,'E');
CREATE RESTORE POINT FIVE;
COMMIT;
************************===========
PROBLEM:
===========************************
##run create table script & insert value with combine
CREATE TABLE ABC (A NUMBER(10),B VARCHAR(10));
INSERT INTO ABC VALUES(1,'A');
INSERT INTO ABC VALUES(2,'B');
INSERT INTO ABC VALUES(3,'C');
INSERT INTO ABC VALUES(4,'D');
INSERT INTO ABC VALUES(5,'E');
COMMIT;
SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='ABC'
Note:- See the all values SCN no. & Timestamp are same in this case i'll flashback show error
#######################################################
SQL> FLASHBACK TABLE LDBO.ABC TO TIMESTAMP TO_TIMESTAMP('25-11-2011 04:01:37','DD-MM-YYYY HH12:MI:SS');
FLASHBACK TABLE LDBO.ABC TO TIMESTAMP TO_TIMESTAMP('25-11-2011 04:01:37','DD-MM-YYYY HH12:MI:SS')
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
#######################################################
#######################################################
SQL> FLASHBACK TABLE LDBO.ABC TO SCN 1299644;
FLASHBACK TABLE LDBO.ABC TO 1299644
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
#######################################################
##I'LL TRY TO SOLVE THIS PROBLEM BUT NOT SOLVED
1.After getting SCN run two query for example flashback table and flashback query I ran and it failed.
SQL> select * from TEST_FLASHBACK_FEATURE as of scn 1371097;
select * from TEST_FLASHBACK_FEATURE as of scn 1371097
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
SQL> FLASHBACK TABLE TEST_FLASHBACK_FEATURE TO SCN 1371097;
FLASHBACK TABLE TEST_FLASHBACK_FEATURE TO SCN 1371097
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
2.Now I want to do flashback to the SCN of 6 seconds later. For that I converted the SCN to timestamp and manually added 6 seconds in to it.
SQL> SELECT SCN_TO_TIMESTAMP(1371097) FROM DUAL;
SCN_TO_TIMESTAMP(1371097)
---------------------------------------------------------------------------
17-MAY-08 07.20.44.000000000 AM
SQL> SELECT TIMESTAMP_TO_SCN('17-MAY-08 07.20.50 AM') FROM DUAL; --add 6 seconds.
TIMESTAMP_TO_SCN('17-MAY-0807.20.50AM')
---------------------------------------
1371098
3.Now perform flashback query with the 6 seconds later SCN.
SQL> SELECT * FROM TEST_FLASHBACK_FEATURE AS OF SCN 1371098;
A
----------
1
SQL> FLASHBACK TABLE TEST_FLASHBACK_FEATURE TO SCN 1371098;
flashback completed.
1.FLASHBACK TABLE ABC TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);
2.FLASHBACK TABLE LDBO.ABC TO TIMESTAMP TO_TIMESTAMP('24-11-2011 02:02:00', 'DD-MM-YYYY HH12:MI:SS');
3.FLASHBACK TABLE LDBO.ABC TO SCN 1371098;
============================================================================================
FLASHBACK TABLE (Flashback Version Query / RECORDE COMMIT) PURPOSE AUDIT & MANNUAL DML PERFORM
NOT WORK IN VIEW,FIXED TABLE,TEMPORARY TABLE,EXTERNAL TABLES
============================================================================================
CREATE TABLE SCOTT.ABC (A NUMBER(10),B VARCHAR(10));
INSERT INTO SCOTT.ABC VALUES(1,'A');
INSERT INTO SCOTT.ABC VALUES(2,'B');
INSERT INTO SCOTT.ABC VALUES(3,'C');
INSERT INTO SCOTT.ABC VALUES(4,'D');
INSERT INTO SCOTT.ABC VALUES(5,'E');
COMMIT;
SQL> select * from scott.abc;
A B
---------- ----------
1 AA
2 BB
3 CC
4 DD
5 EE
6 EE
7 EE
8 EE
9 EE
10 EE
11 KK
12 LL
13 MM
14 NN
15 OO
15 rows selected.
SQL> delete from scott.abc where a=10;
SQL> commit;
SQL> exec dbms_lock.sleep(10);
SQL> delete from scott.abc where a=11;
SQL> commit;
SQL> delete from scott.abc where a=12;
SQL> commit;
SQL> delete from scott.abc where a=13;
SQL> commit;
SQL> delete from scott.abc where a in (14,15);
SQL> commit;
SQL> select * from scott.abc;
A B
---------- ----------
1 AA
2 BB
3 CC
4 DD
5 EE
6 EE
7 EE
8 EE
9 EE
SQ> select versions_xid,a,b from scott.abc versions between timestamp minvalue and maxvalue;
VERSIONS_XID A B
---------------- ---------- ---
0900110070010000 15 OO
0900110070010000 14 NN
020012007B010000 13 MM
0500150063010000 12 LL
060014006B010000 11 KK
0A0002006F010000 10 EE
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation,A,B
from SCOTT.ABC versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME ;
SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = hextoraw('06002900AC040000') ; -- delete
======
NOTE:- SEE UNDO_SQL THEN COPY ANY PASTE SQLPLUS TO INSERT A VALUE AGAIN;
======
OR
create table flashback_test ( c1 number, c2 date ) nologging ;
insert into flashback_test values ( 1, sysdate ) ;
commit ;
update flashback_test set c1 = c1 * 2 ;
commit ;
exec dbms_lock.sleep(15);
update flashback_test set c1 = c1 * 2 ;
commit ;
exec dbms_lock.sleep(15);
delete flashback_test ;
commit ;
exec dbms_lock.sleep(15);
select versions_starttime, versions_endtime, versions_xid, versions_operation, c1
from flashback_test versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME ;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION C1
------------------------------------------------- ------------------------------------------------- ---------------- ------------------ ----------
08/10/2007 08:18:29 08/10/2007 08:18:44 08002200A6040000 U 2
08/10/2007 08:18:44 08/10/2007 08:18:59 03002300C7040000 U 4
08/10/2007 08:18:59 06002900AC040000 D 4
08/10/2007 08:18:29 1
SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = hextoraw('06002900AC040000') ; -- delete
UNDO_SQL
--------------------------------------------------------------------------------
insert into "HR"."FLASHBACK_TEST"("C1","C2") values ('4',TO_DATE('08/10/2007', '
SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = hextoraw('03002300C7040000') ; -- update 4
UNDO_SQL
--------------------------------------------------------------------------------
update "HR"."FLASHBACK_TEST" set "C1" = '2' where ROWID = 'AAAONoAAEAAAAGWAAA';
The maximum of there versions e can get are dependent on UNDO_RETENTION parameter of the database. TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP flashback functions are used in SQL or PL/SQL as needed. In this example we are looking for the sum of the salaries on the employees in time;
set serveroutput on
DECLARE
l_scn NUMBER;
l_timestamp TIMESTAMP;
BEGIN
l_scn := TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/48);
dbms_output.put_line('l_scn '||l_scn);
l_timestamp := SCN_TO_TIMESTAMP(l_scn);
dbms_output.put_line('l_timestamp '||l_timestamp);
END;
/
SELECT sum(salary) FROM employees
AS OF SCN TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
SELECT sum(salary) FROM employees -- TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/48)
AS OF TIMESTAMP SCN_TO_TIMESTAMP(1531264);
SQL>
l_scn 1531264
l_timestamp 04/01/2007 08:01:29,000000
PL/SQL procedure successfully completed
SUM(SALARY)
-----------
691400
SUM(SALARY)
-----------
691400
=============================================================
FLASHBACK TABLE (Drop Table Flashback)
=============================================================
## CREATE TABLE SCOTT.ABC (A NUMBER(10),B VARCHAR(10)) ENABLE ROW MOVEMENT;
INSERT INTO SCOTT.ABC VALUES(1,'A');
INSERT INTO SCOTT.ABC VALUES(2,'B');
INSERT INTO SCOTT.ABC VALUES(3,'C');
INSERT INTO SCOTT.ABC VALUES(4,'D');
INSERT INTO SCOTT.ABC VALUES(5,'E');
COMMIT;
DROP TABLE SCOTT.ABC;
SELECT COUNT(*) FROM SCOTT.ABC; //msg:- TABLE & VIEW DOESN'T EXIST
NOTE:- See these command not work error shown TABLE & VIEW Does not exist
FLASHBACK TABLE SCOTT.ABC TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);
FLASHBACK TABLE LDBO.ABC TO TIMESTAMP TO_TIMESTAMP('24-11-2011 02:02:00', 'DD-MM-YYYY HH12:MI:SS');
FLASHBACK TABLE LDBO.ABC TO SCN 1152563;
1. FLASHBACK TABLE LDBO.ABC TO BEFORE DROP;
************
2nd Method
************
WILL YOU DELETE A MULLTIPLE TABLES & YOU WILL FLASHBACK ONLY ONE TABLE THEN FOLLOWS STEPS
## CREATE TABLE SCOTT.ABC (A NUMBER(10),B VARCHAR(10)) ENABLE ROW MOVEMENT;
INSERT INTO SCOTT.ABC VALUES(1,'A');
INSERT INTO SCOTT.ABC VALUES(2,'B');
INSERT INTO SCOTT.ABC VALUES(3,'C');
INSERT INTO SCOTT.ABC VALUES(4,'D');
INSERT INTO SCOTT.ABC VALUES(5,'E');
COMMIT;
CREATE TABLE SCOTT.DEF (A NUMBER(10),B VARCHAR(10)) ENABLE ROW MOVEMENT;
INSERT INTO SCOTT.DEF VALUES(1,'A');
INSERT INTO SCOTT.DEF VALUES(2,'B');
INSERT INTO SCOTT.DEF VALUES(3,'C');
INSERT INTO SCOTT.DEF VALUES(4,'D');
INSERT INTO SCOTT.DEF VALUES(5,'E');
COMMIT;
CREATE TABLE SCOTT.GHI (A NUMBER(10),B VARCHAR(10)) ENABLE ROW MOVEMENT;
INSERT INTO SCOTT.GHI VALUES(1,'A');
INSERT INTO SCOTT.GHI VALUES(2,'B');
INSERT INTO SCOTT.GHI VALUES(3,'C');
INSERT INTO SCOTT.GHI VALUES(4,'D');
INSERT INTO SCOTT.GHI VALUES(5,'E');
COMMIT;
DROP TABLE SCOTT.ABC;
DROP TABLE SCOTT.DEF;
DROP TABLE SCOTT.GHI;
1. FLASHBACK TABLE LDBO.DEF TO BEFORE DROP;
2. FLASHBACK TABLE LDBO.DEF TO BEFORE DROP RENAME TO DEF1; /// WITH RENAME
SELECT * FROM DBA_RECYCLEBIN;
CONN SCOTT/TIGER@ORCL1011SRV
SELECT * FROM USER_RECYCLEBIN;
RENAME DEF1 TO DEF;
RELLOCATE DATABASE
=================================================
RELLOCATE DATABASE TO A NEW LOCATION WITH RMAN
=================================================
C:\>RMAN TARGET 'SYS/ORACLE@ORCL1112SRV AS SYSDBA'
Recovery Manager: Release 10.2.0.3.0 - Production on Fri Jul 29 12:11:58 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL1011 (DBID=2521181406, not open)
RMAN> RUN{
2> SET NEWNAME FOR DATAFILE 1 TO 'D:\ORCLD1112\SYSAUX01.DBF';
3> SET NEWNAME FOR DATAFILE 2 TO 'D:\ORCLD1112\SYSTEM01.DBF';
4> SET NEWNAME FOR DATAFILE 3 TO 'D:\ORCLD1112\UNDOTBS01.DBF';
5> SET NEWNAME FOR DATAFILE 4 TO 'D:\ORCLD1112\USERS01.DBF';
6> SET NEWNAME FOR DATAFILE 5 TO 'D:\ORCLD1112\RMAN.DBF';
7> RESTORE DATAFILE 1;
8> RESTORE DATAFILE 2;
9> RESTORE DATAFILE 3;
10> RESTORE DATAFILE 4;
11> RESTORE DATAFILE 5;
12> switch DATAFILE ALL;
13> }
executing command: SET NEWNAME
using target database control file instead of recovery catalog
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 29-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORCLD1112\SYSAUX01.DBF
channel ORA_DISK_1: reading from backup piece E:\RMANBACKUP\DB_ORCL1011_01MIL6OJ
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\RMANBACKUP\DB_ORCL1011_01MIL6OJ tag=TAG20110729T104850
channel ORA_DISK_1: restore complete, elapsed time: 00:01:07
Finished restore at 29-JUL-11
Starting restore at 29-JUL-11
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 00002 to D:\ORCLD1112\SYSTEM01.DBF
channel ORA_DISK_1: reading from backup piece E:\RMANBACKUP\DB_ORCL1011_01MIL6OJ
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\RMANBACKUP\DB_ORCL1011_01MIL6OJ tag=TAG20110729T104850
channel ORA_DISK_1: restore complete, elapsed time: 00:00:27
Finished restore at 29-JUL-11
Starting restore at 29-JUL-11
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 00003 to D:\ORCLD1112\UNDOTBS01.DBF
channel ORA_DISK_1: reading from backup piece E:\RMANBACKUP\DB_ORCL1011_01MIL6OJ
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\RMANBACKUP\DB_ORCL1011_01MIL6OJ tag=TAG20110729T104850
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 29-JUL-11
Starting restore at 29-JUL-11
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 00004 to D:\ORCLD1112\USERS01.DBF
channel ORA_DISK_1: reading from backup piece E:\RMANBACKUP\DB_ORCL1011_01MIL6OJ
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\RMANBACKUP\DB_ORCL1011_01MIL6OJ tag=TAG20110729T104850
channel ORA_DISK_1: restore complete, elapsed time: 00:00:17
Finished restore at 29-JUL-11
Starting restore at 29-JUL-11
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 00005 to D:\ORCLD1112\RMAN.DBF
channel ORA_DISK_1: reading from backup piece E:\RMANBACKUP\DB_ORCL1011_01MIL6OJ
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\RMANBACKUP\DB_ORCL1011_01MIL6OJ tag=TAG20110729T104850
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 29-JUL-11
datafile 1 switched to datafile copy
input datafile copy recid=6 stamp=757772139 filename=D:\ORCLD1112\SYSAUX01.DBF
datafile 2 switched to datafile copy
input datafile copy recid=7 stamp=757772140 filename=D:\ORCLD1112\SYSTEM01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=8 stamp=757772140 filename=D:\ORCLD1112\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=9 stamp=757772140 filename=D:\ORCLD1112\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy recid=10 stamp=757772141 filename=D:\ORCLD1112\RMAN.DBF
RMAN>
=======================================================================
RELLOCATE DATABASE TO A NEW LOCATION WITH USER-MANAGED PROCEDURES:-
=======================================================================
THROUGH THIS CLAUSE "RENAME" IT'S USES TO RENAME DATAFILES & RELOCATE THE LOCATION
*Shutdown
*Copy & Paste new location / USE O.S command to restore the datafiles to the new location
NOTE:- BUT NOTE IN CONTROL FILE.
or
*Rename the datafile & Else
*Startup Mount Exclusive
*ALTER DATABASE RENAME FILE 'D:\ALMD1011\SYSAUX01.DBF' TO 'E:\ALMD1011\SYSAUX01.DBF'
*ALTER DATABASE RENAME FILE 'D:\ALMD1011\INDEX01.DBF' TO 'E:\ALMD1011\INDEX01.DBF'
*ALTER DATABASE RENAME FILE 'D:\ALMD1011\UNDOTBST01.DBF' TO 'E:\ALMD1011\UNDOTBST01.DBF'
*ALTER DATABASE RENAME FILE 'D:\ALMD1011\TEMP01.DBF' TO 'E:\ALMD1011\TEMP01.DBF'
*ALTER DATABASE OPEN;
*SELECT * FROM V$TABLESPACE;
*SELECT * FROM V$DATAFILE;
*SELECT * FORM V$CONTROLFILE;
*ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
*ALTER SYSTEM SET CONTROL_FILES=''E:\ALMD1011\control01.ctl','E:\ALMD1011\control02.ctl','E:\ALMD1011\control03.ctl' SCOPE=SPFILE;
*SHUTDOWN IMMEDIATE
*STARTUP NOMOUNT
##CONTROLFILESCRIPT.SQL
CREATE CONTROLFILE SET DATABASE "ALM1011" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2920
LOGFILE
GROUP 1 'E:\ALMD1011\REDO01.LOG' SIZE 50M,
GROUP 2 'E:\ALMD1011\REDO02.LOG' SIZE 50M,
GROUP 3 'E:\ALMD1011\REDO03.LOG' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'E:\ALMD1011\SYSTEM01.ORA',
'E:\ALMD1011\UNDOTBS01.ORA',
'E:\ALMD1011\SYSAUX01.ORA',
'E:\ALMD1011\INDEX01.ORA',
'E:\ALMD1011\USERS01.ORA'
CHARACTER SET WE8MSWIN1252
;
*ALTER DATABASE OPEN RESETLOGS;
*SELECT * FROM V$TABLESPACE;
*SELECT * FROM V$DATAFILE;
*SELECT * FORM V$CONTROLFILE;
######ELSE PART
SHUTDOWN IMMEDIATE
COPY & PASTE NEW LOCATION
STARTUP MOUNT
ALTER DATABASE RENAME FILE 'D:\ALMD1011\SYSTEM01.ORA' TO 'E:\ALMD1011\SYSTEM01.ORA';
ALTER DATABASE OPEN;
ALTER DATABASE OPEN RESETLOGS;
SHUTDOWN IMMEDIATE
STARTUP
Database mounted.
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: 'E:\ALMD1011\SYSTEM01.ORA'
SQL> RECOVER DATABASE;
ORA-00279: change 932929863 generated at 02/25/2011 12:36:19 needed for thread 1
ORA-00289: suggestion : D:\ARCHIVE1011\ARC00001_0744038587.001
ORA-00280: change 932929863 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
Log applied.
Media recovery complete.
SQL> CANCLE;
SQL> ALTER DATABASE OPEN;
USER MANAGED DATABASE CLONING
================
USER MANAGED
================
##Steps to create a database CLONENING in window environment for 10g
1) Create required directories
E:\oracle\product\10.2.0\admin\VINY1011
adump
bdump
cdmp
dpdump
udump
pfile
2) Create datafile/Archive detination
E:\vinyd1011
E:\Archive_viny1011
3) SQLPLUS
SET ORACLE_SID=ORCL1
ECHO %ORACLE_SID%
SQLPLUS /NOLOG
CONN / AS SYSDBA
SET LINE 32000 PAGESIZE 32000
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
##Go E:\oracle\product\10.2.0\admin\udump (location controlfile trace file copy code & write script n save db_cntrolfile.sql)
##COPY OLD TRACE FILE
CREATE CONTROLFILE REUSE DATABASE "ORCL1011" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'E:\ORCLD1011\REDO01.LOG' SIZE 50M,
GROUP 2 'E:\ORCLD1011\REDO02.LOG' SIZE 50M,
GROUP 3 'E:\ORCLD1011\REDO03.LOG' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'E:\ORCLD1011\SYSTEM01.DBF',
'E:\ORCLD1011\UNDOTBS01.DBF',
'E:\ORCLD1011\SYSAUX01.DBF',
'E:\ORCLD1011\USR.DBF'
CHARACTER SET WE8MSWIN1252
;
##PASTE INTO NEW NOTEPAD FILE AND FEW CHANGES OF LOCATION SEE AND save (db_cntrolfile.sql in E:\ drive)
CREATE CONTROLFILE SET DATABASE "viny1011" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'E:\vinyd1011\REDO01.LOG' SIZE 50M,
GROUP 2 'E:\vinyd1011\REDO02.LOG' SIZE 50M,
GROUP 3 'E:\vinyd1011\REDO03.LOG' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'E:\vinyd1011\SYSTEM01.DBF',
'E:\vinyd1011\UNDOTBS01.DBF',
'E:\vinyd1011\SYSAUX01.DBF',
'E:\vinyd1011\USR.DBF'
CHARACTER SET WE8MSWIN1252
;
4) conn / as sysdba
create pfile from spfile;
5) Shutdown immediate
Copy all datafiles for the detination without any control file and Paste new destination.
COPY OLD DESTINATION
E:\ORCLD1011
LOGFILE
E:\ORCLD1011\REDO01.LOG
E:\ORCLD1011\REDO02.LOG
E:\ORCLD1011\REDO03.LOG
DATAFILE
E:\ORCLD1011\SYSTEM01.DBF
E:\ORCLD1011\UNDOTBS01.DBF
E:\ORCLD1011\SYSAUX01.DBF
E:\ORCLD1011\USR.DBF
PASTE NEW DESTINATION
E:\VINYD1011
LOGFILE
E:\vinyd1011\REDO01.LOG
E:\vinyd1011\REDO02.LOG
E:\vinyd1011\REDO03.LOG
DATAFILE
E:\vinyd1011\SYSTEM01.DBF
E:\vinyd1011\UNDOTBS01.DBF
E:\vinyd1011\SYSAUX01.DBF
E:\vinyd1011\USR.DBF
6) Go there location old pfile like these
E:\oracle\product\10.2.0\db_1\database\INITorcl2.ORA
##OLD LOCATION PFILE
orcl2.__db_cache_size=117440512
orcl2.__java_pool_size=4194304
orcl2.__large_pool_size=4194304
orcl2.__shared_pool_size=75497472
orcl2.__streams_pool_size=0
*.audit_file_dest='E:\oracle\product\10.2.0\admin\orcl1011\adump'
*.background_dump_dest='E:\oracle\product\10.2.0\admin\orcl1011\bdump'
*.compatible='10.2.0.3.0'
*.control_files='E:\ORCLD1011\control01.ctl','E:\ORCLD1011\control02.ctl','E:\ORCLD1011\control03.ctl'
*.core_dump_dest='E:\oracle\product\10.2.0\admin\orcl1011\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=8
*.db_name='orcl1011'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl2XDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=E:\Archive1011'
*.log_archive_format='ARC%S_%R.%T'
*.open_cursors=300
*.pga_aggregate_target=68157440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=204472320
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='E:\oracle\product\10.2.0\admin\orcl1011\udump'
Copy old pfile code open new notepad file and paste into notepad n save pfile.
open Pfile changes few locations and db_name & save ( initviny1.ora )like
## NEW LOCATION FILE
orcl2.__db_cache_size=117440512
orcl2.__java_pool_size=4194304
orcl2.__large_pool_size=4194304
orcl2.__shared_pool_size=75497472
orcl2.__streams_pool_size=0
*.audit_file_dest='E:\oracle\product\10.2.0\admin\viny1011\adump'
*.background_dump_dest='E:\oracle\product\10.2.0\admin\viny1011\bdump'
*.compatible='10.2.0.3.0'
*.control_files='E:\vinyd1011\control01.ctl','E:\vinyd1011\control02.ctl','E:\vinyd1011\control03.ctl'
*.core_dump_dest='E:\oracle\product\10.2.0\admin\viny1011\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=8
*.db_name='viny1011'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl2XDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=E:\Archive1011'
*.log_archive_format='ARC%S_%R.%T'
*.open_cursors=300
*.pga_aggregate_target=68157440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=204472320
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='E:\oracle\product\10.2.0\admin\viny1011\udump'
7) oradim -new -sid viny1 -startupmode auto -initpwd oracle
8) startup nomount pfile='E:\oracle\product\10.2.0\db_1\database\initviny1.ora'
9) @ D:\db_controlfile.sql; //database is automatic in mount stage
10) alter database open resetlogs;
11) create spfile from pfile;
12) Create viny1011srv service & listener setting
13) shutwon immeidate
14) startup
15) set oracle_sid=viny1
16) sqlplus /nolog
conn / as sysdba
show user
shutdown immediate
startup
17) Create database control console service
emca -config all db
sid=viny1
sys=oracle
---
---
---
---
---
Script Complete login viny1011 database open normal mode n enjoy Have a great Day....Gud N8....?
ORACLE FILE ZIP
zip
===
SQL> HO ZIP
SQL> SET TERM ON;
SQL> PRO generating zip file...
SQL> HO zip -mT PFILE INITorcl6.ORA // SQL> HO zip -mT <zip_name> <filesname>.<extention of file>
SQL> PRO PFILE.zip has been generated
SQL> PRO
SQL> HO zip e:\Pfile INIT*.ORA //zip location d drive file to e:\ drive into pfile directory
SQL> HO zip e:\pfile D:\try\try2Online_logfile\INIT*.ORA //
unzip
======
SQL> HO UNZIP
SQL> HO unzip -o pfile.zip // extract here same location
SQL> HO unzip -d e:\pfile -o D:\Pfile //unzipfile
OR
SQL> HO unzip e:\pfile -d D:\Pfile //unzipfile pfile e drive to D drive
NOT MORE THAN ,M NOT SURE 1GB FILE......?
CONSTRAINT ENABLE & DISABLE
Here sql command files to disable and enable all constraints:
Disable:
set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||
' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c, user_tables u
where c.table_name = u.table_name;
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;
exit
/
Enable:
set feedback off
set verify off
set wrap off
set echo off
prompt Finding constraints to enable...
set termout off
set lines 120
set heading off
spool tmp_enable.sql
select 'spool igen_enable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||
' ENABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c, user_tables u
where c.table_name = u.table_name;
/
select 'exit;' from dual;
set termout on
prompt Enabling constraints now...
set termout off
@tmp_enable;
!rm -i tmp_enable.sql;
exit
/
ORACE PROXY USER
PROXY USER
============
create user proxy identified by proxy Default tablespace "USR" temporary tablespace "TEMPORARY" account unlock profile "DEFAULT";
grant create session,resource to proxy;
create user vinay identified by vinay Default tablespace "USR" temporary tablespace "TEMPORARY" account unlock profile "DEFAULT";
grant create session, create table,resource to vinay;
grant firmpermission,remoteuser to vinay with admin option;
create user mayank identified by mayank Default tablespace "USR" temporary tablespace "TEMPORARY" account unlock profile "DEFAULT";
grant create session, create table,resource to vinay;
grant firmpermission,remoteuser to vinay with admin option;
alter user vinay grant connect through proxy;
alter user vinay quota unlimited on USR;
alter user mayank grant connect through proxy;
alter user mayank quota unlimited on USR;
create table end_user.tableX(col1 number);
connect proxy[vinay]/proxy@alm1112srv
select user from dual;
desc tableX
MULTIPLE DBWn PARAMETER
MULTIPLE DBWn PARAMETERE DEFINE
===================================
WHEN THE SGA SIZE IS MORE CONSUME AND AT A TIME ONE DBWn PROCESS ,ALL BUFFER CACHE DATA IS
WRITE ON DATAFILE BUT IN CASE YOU WILL EXECUTE SOME QUERIES BUT PROCESS IS NOT ALLOCATED
THE SPACE ON BUFFER CACHE ,SO MULTIPLE DBWn IS WORK FAST TWO OR MORE PROCESS IS WRITE A DATA
ON DATAFILE PARALLELY AND PROCESS ALLOCATE A SPACE ON BUFFER CACHE.
PARAMETER IS DEFINE ON THIS FILE:-
( Oracle I_O Slave Waits dbwr parallel DML )
SHOW PARAMETE DB_WRITER_%
################################################################################
DBWR_IO_SLAVES
Parameter type Integer
Default value 0
Parameter class Static
Range of values 0 to operating system-dependent
DBWR_IO_SLAVES is relevant only on systems with only one database writer process (DBW0). It specifies the number of I/O server processes used by the DBW0 process. The DBW0 process and its server processes always write to disk. By default, the value is 0 and I/O server processes are not used.
If you set DBWR_IO_SLAVES to a nonzero value, the number of I/O server processes used by the ARCH and LGWR processes is set to 4. However, the number of I/O server processes used by Recovery Manager is set to 4 only if asynchronous I/O is disabled (either your platform does not support asynchronous I/O or disk_asynch_io is set to false.
Typically, I/O server processes are used to simulate asynchronous I/O on platforms that do not support asynchronous I/O or that implement it inefficiently. However, you can use I/O server processes even when asynchronous I/O is being used. In that case the I/O server processes will use asynchronous I/O.
I/O server processes are also useful in database environments with very large I/O throughput, even if asynchronous I/O is enabled.
NOTE:-
======
IT MEANS THE "DBWR_IO_SLAVES" INITIAL 2 OR 3 PROCESS ARE EXCUTES PARALLELY
IF THE "DBWR_IO_SLAVES" NOT INITIALIZED THE PROCESS ARE EXECUTED IN A QUEUE
IN THIS WORK ON "DB_WRITER_PROCESS" PARAMETER INITIAL FIRST .
ORACLE JAZN
==========================
ORACLE JAZN ON WINDOWS
==========================
SET ORACLE_HOME=D:\oracle\product\10.2.0\db_1
CD D:\oracle\product\10.2.0\db_1\oc4j\j2ee\isqlplus\application-deployments\isqlplus
D:\oracle\product\10.2.0\db_1\jdk\bin\java -Djava.security.properties=D:\oracle\product\10.2.0\db_1\sqlplus\admin\iplus\provider -jar D:\oracle\product\10.2.0\db_1\oc4j\j2ee\home\jazn.jar -user "iSQL*Plus DBA/admin" -password welcome -shell
JAZN:> adduser "iSQL*Plus DBA" oracle oracle
listusers "iSQL*Plus DBA"
admin
taj
JAZN:> grantrole webDba "iSQL*Plus DBA" oracle
JAZN:> exit
C:\Oracle\product\10.1.0\Db_1\oc4j\j2ee\isqlplus\application-deployments\isqlplus>isqlplusctl stop
C:\Oracle\product\10.1.0\Db_1\BIN>isqlplusctl start
http://server4:5560/isqlplus/dba
user :taj
pwd:- oracle //these password enter you to sys account
and see normal login
=============================
JASS COMMANDS CONNECTION:-
=============================
set ORACLE_HOME=c:\oracle\product\10.2.0\db_1
CD D:\oracle\product\10.2.0\db_1\oc4j\j2ee\isqlplus\application-deployments\isqlplus
D:\oracle\product\10.2.0\db_1\jdk\bin\java -Djava.security.properties=D:\oracle\product\10.2.0\db_1\sqlplus\admin\iplus\provider -jar D:\oracle\product\10.2.0\db_1\oc4j\j2ee\home\jazn.jar -user "iSQL*Plus DBA/admin" -password welcome -shell
JAZN> HELP
*Create Users
JAZN> adduser "iSQL*Plus DBA" username password
*List Users
JAZN> listusers "iSQL*Plus DBA"
*Grant Users the webDba Role
JAZN>grantrole webDba "iSQL*Plus DBA" username
*Remove Users
JAZN>remuser "iSQL*Plus DBA" username
*Revoke the webDba Role
JAZN> revokerole webDba "iSQL*Plus DBA" username
*Change User Passwords
JAZN> setpasswd "iSQL*Plus DBA" username old_password new_password
*Test iSQL*Plus DBA Access
Test iSQL*Plus DBA access by entering the iSQL*Plus DBA URL in your web browser:
http://machine_name.domain:5560/isqlplus/dba
http://machine_name:5560/isqlplus/dba
http://192.168.21.238:5560/isqlplus/dba
Enjoy All of You..........................?
10:49 AM 4/15/2011
=====================
ERROR
##Delete OracleOraDb10g_home1iSQL*Plus
Service not started and when you will start show error msg follow these command &
when show error msg popup is (failed to start the service error 0,the operation completed successfully)
=====================
to delete it service :
Eg:- sc delete service name
sc delete OracleOraDb10g_home1iSQL*Plus
Basic ASM
Basic ASM Steps on Linux
===================
$ df -h
$ ll
$ vi /etc/oratab
$ su root
OR
All the steps is to be done in terminal.
*1. * Login as root user and create the /u01/asmdisks directories.
mkdir -p /u01/asmdisks
*2. * Create five 400MB files that will be used as ASM disks. Create
those files in /u01/asmdisks.
cd /u01/asmdisks
dd if=/dev/zero of=asm_disk1 bs=1024k count=1000
dd if=/dev/zero of=asm_disk2 bs=1024k count=1000
dd if=/dev/zero of=asm_disk3 bs=1024k count=1000
dd if=/dev/zero of=asm_disk4 bs=1024k count=1000
dd if=/dev/zero of=asm_disk5 bs=1024k count=1000
dd if=/dev/zero of=asm_disk6 bs=1024k count=1000
cd /u01/asmdisks
chmod 770 asm_disk*
cd /u01/asmdisks
losetup /dev/loop1 asm_disk1
losetup /dev/loop2 asm_disk2
losetup /dev/loop3 asm_disk3
losetup /dev/loop4 asm_disk4
losetup /dev/loop5 asm_disk5
losetup /dev/loop6 asm_disk6
cd
cd /dev/
ls -al | grep loop
cd /u01/asmdisks
raw /dev/raw/raw1 /dev/loop1
raw /dev/raw/raw2 /dev/loop2
raw /dev/raw/raw3 /dev/loop3
raw /dev/raw/raw4 /dev/loop4
raw /dev/raw/raw5 /dev/loop5
raw /dev/raw/raw6 /dev/loop6
[root@testserver]raw /dev/raw/raw1 /dev/loop1
/dev/raw/raw1: bound to major 7, minor 1
[root@testserver]raw /dev/raw/raw2 /dev/loop2
/dev/raw/raw2: bound to major 7, minor 2
[root@testserver]raw /dev/raw/raw3 /dev/loop3
/dev/raw/raw3: bound to major 7, minor 3
[root@testserver]raw /dev/raw/raw4 /dev/loop4
/dev/raw/raw4: bound to major 7, minor 4
[root@testserver]raw /dev/raw/raw5 /dev/loop5
/dev/raw/raw4: bound to major 7, minor 5
[root@testserver]raw /dev/raw/raw6 /dev/loop6
/dev/raw/raw4: bound to major 7, minor 6
[root@testserver]raw -qa
/dev/raw/raw1: bound to major 7, minor 1
/dev/raw/raw2: bound to major 7, minor 2
/dev/raw/raw3: bound to major 7, minor 3
/dev/raw/raw4: bound to major 7, minor 4
Assigb permission to oracle user :
raw -qa
ls /dev/raw/ -l
cd /u01/asmdisks
chown oracle:oinstall /dev/raw/raw1
chown oracle:oinstall /dev/raw/raw2
chown oracle:oinstall /dev/raw/raw3
chown oracle:oinstall /dev/raw/raw4
chown oracle:oinstall /dev/raw/raw5
chown oracle:oinstall /dev/raw/raw6
ls /dev/raw/ -l
vi /etc/oratab
*3. * Make sure that the asm_disk files are associated to raw devices,
and have the right permissions:
vi /etc/rc.local
chmod 770 asm_disk*
losetup /dev/loop1 asm_disk1
losetup /dev/loop2 asm_disk2
losetup /dev/loop3 asm_disk3
losetup /dev/loop4 asm_disk4
losetup /dev/loop5 asm_disk5
losetup /dev/loop6 asm_disk6
raw /dev/raw/raw1 /dev/loop1
raw /dev/raw/raw2 /dev/loop2
raw /dev/raw/raw3 /dev/loop3
raw /dev/raw/raw4 /dev/loop4
raw /dev/raw/raw5 /dev/loop5
raw /dev/raw/raw6 /dev/loop6
chown oracle:oinstall /dev/raw/raw1
chown oracle:oinstall /dev/raw/raw2
chown oracle:oinstall /dev/raw/raw3
chown oracle:oinstall /dev/raw/raw4
chown oracle:oinstall /dev/raw/raw5
chown oracle:oinstall /dev/raw/raw6
# raw -qa
# ls /dev/raw/ -l
/u01/oracle/product/10.2.0/db_1/bin/localconfig add
/u01/oracle/product/10.2.0/db_1/bin/localconfig delete
==============================
Show parameter spfile
show parameter limit% //rebalance
show parameter instance% //instance type
==============================
connect +ASM
export ORACLE_SID=+ASM
sqlplus '/ as sysdba'
==============================================
when restart the pc system not read ASM files so temporary work go
1. Run this commands on Linux Terminal Copy & paste on terminal
cd /u01/asmdisks
losetup /dev/loop1 asm_disk1
losetup /dev/loop2 asm_disk2
losetup /dev/loop3 asm_disk3
losetup /dev/loop4 asm_disk4
losetup /dev/loop5 asm_disk5
losetup /dev/loop6 asm_disk6
raw /dev/raw/raw1 /dev/loop1
raw /dev/raw/raw2 /dev/loop2
raw /dev/raw/raw3 /dev/loop3
raw /dev/raw/raw4 /dev/loop4
raw /dev/raw/raw5 /dev/loop5
raw /dev/raw/raw6 /dev/loop6
chown oracle:oinstall /dev/raw/raw1
chown oracle:oinstall /dev/raw/raw2
chown oracle:oinstall /dev/raw/raw3
chown oracle:oinstall /dev/raw/raw4
chown oracle:oinstall /dev/raw/raw5
chown oracle:oinstall /dev/raw/raw6
2.Then after Connect the +ASM instance & triger the startup command.
3. Connect database & startup database.
Keep goin............................!!!!
Linux Yum Server
How To Create Yum Server
====================
# cd /etc/yum.repos.d/
# ls
# cp -p rhel-debuginfo.repo server.repo
# ls
# vim server.repo
[rhel]
name=Red Hat Enterprise Linux
baseurl=file:///rhel5
enabled=1
:wq!
# cd
# yum clean all
# yum list all
Thursday, 14 June 2012
Physical Standby database Fully Switchover
-PRIMARY PFILE
orcl.__db_cache_size=306184192
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=125829120
orcl.__streams_pool_size=0
*.audit_file_dest='D:\oracle\product\10.2.0\admin\orcl\adump'
*.background_dump_dest='D:\oracle\product\10.2.0\admin\orcl\bdump'
*.user_dump_dest='D:\oracle\product\10.2.0\admin\orcl\udump'
*.core_dump_dest='D:\oracle\product\10.2.0\admin\orcl\cdump'
*.compatible='10.2.0.3.0'
*.control_files='D:\oracle\product\10.2.0\oradata\orcl\control01.ctl','D:\oracle\product\10.2.0\oradata\orcl\control02.ctl','D:\oracle\product\10.2.0\oradata\orcl\control03.ctl'
*.db_recovery_file_dest='D:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=148897792
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
#sessions=115
*.sga_target=447741952
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
#PARAMETER FOR STANDBY CONFIGURATION
db_name='ORCL'
db_unique_name='ORCL'
instance_name='ORCL'
service_names='ORCL'
dg_broker_start='TRUE'
log_archive_config='DG_CONFIG=(ORCL,STAN)'
log_archive_dest_1='LOCATION=D:\Archive\ MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL'
log_archive_dest_2='SERVICE=STAN LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=STAN'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
FAL_CLIENT='ORCL'
FAL_SERVER='STAN'
DB_FILE_NAME_CONVERT='C:\STAND\','D:\oracle\product\10.2.0\oradata\orcl\'
LOG_FILE_NAME_CONVERT='C:\STAND\','D:\oracle\product\10.2.0\oradata\orcl'
standby_file_management='AUTO'
standby_archive_dest='D:\standby_archivelog\'
log_archive_format='ARC_%t_%s_%r.dbf'
log_archive_max_processes=30
log_archive_trace=100
fast_start_mttr_target=300
-STANDBY PFILE
stan.__db_cache_size=306184192
stan.__java_pool_size=4194304
stan.__large_pool_size=4194304
stan.__shared_pool_size=125829120
stan.__streams_pool_size=0
*.audit_file_dest='C:\STAND\adump'
*.background_dump_dest='C:\STAND\bdump'
*.core_dump_dest='C:\STAND\cdump'
*.user_dump_dest='C:\STAND\udump'
*.compatible='10.2.0.3.0'
*.control_files='C:\STAND\control01.ctl'
*.db_recovery_file_dest='C:\STAND\flash_recovery_area\'
*.db_recovery_file_dest_size=2147483648
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=148897792
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
#sessions=115
*.sga_target=447741952
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
#PARAMETER FOR STANDBY CONFIGURATION
db_name='orcl'
db_unique_name='STAN'
instance_name='STAN'
service_names='STAN'
dg_broker_start='TRUE'
log_archive_config='DG_CONFIG=(orcl,STAN)'
log_archive_dest_1='LOCATION=C:\STANARCHIVE\ MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STAN'
log_archive_dest_2='SERVICE=orcl LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=orcl'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
FAL_CLIENT='STAN'
FAL_SERVER='orcl'
DB_FILE_NAME_CONVERT='D:\oracle\product\10.2.0\oradata\orcl','C:\STAND\'
LOG_FILE_NAME_CONVERT='D:\oracle\product\10.2.0\oradata\orcl','C:\STAND\'
standby_file_management='AUTO'
standby_archive_dest='D:\standby_archivelog\'
log_archive_format='ARC_%t_%s_%r.dbf'
log_archive_max_processes=30
log_archive_trace=100
fast_start_mttr_target=300
##Primary##
1. Enable forced logging on your primary database:
SET LINE 32000 PAGESIZE 32000
ALTER DATABASE FORCE LOGGING;
select * from v$pwfile_users;
col size format a15 heading "LOG SIZE"
SELECT ((BYTES)/1024/1024)||' MB' AS "SIZE" FROM V$LOG;
col member format a100 heading "REDO DESTINATION"
select group#, member from v$logfile ORDER BY 1;
=========================================================================
SWITCHOVER CASE
NOTE:-# both database (primary & standby )create different name of redolog files & its must becuase use current controlfile on the standby & if we are use same name
of redolog file so then switch the logfile hang problem and throw the error on ALERT.log ORA:-01577(so use different name of standby file)
same size but different name.
=========================================================================
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 100M;
select * from v$standby_log;
shu immediate
create spfile from pfile='C:\pfile\tt\INITORCL.ora';
###Copy the data files and temp files over.
host
D:
cd ORCLD1011
COPY *.dbf C:\STAND\
COPY *.log C:\STAND\
color f1
EXIT
STARTUP MOUNT
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'C:\STAND\control01.ctl';
ALTER DATABASE OPEN;
host
oradim -new -sid STAN -startmode manual -intpwd oracle
exit
exit
=========================================================================
##On the Standby Database Site:
. Configure listeners for the primary and standby databases.
1) On Primary system: use Oracle Net Manager to configure a listener for ORCL and STAN. Then restart the listener.
lsnrctl reload
lsnrctl stop
lsnrctl start
lsnrctl status
Set ORACLE_SID=STAN
sqlplus sys/oracle@stansrv as sysdba
create spfile from pfile='C:\pfile\tt\INITSTAN.ora';
startup mount
# Note:- when both primary & standby database log file name same then delete standy & check the following
# Both database tiger the command
col member format a100 heading "REDO DESTINATION"
select group#, member from v$logfile ORDER BY 1;
# When same name so delete standby log & recreate & check which you one delete logfile is active or current then check status
#select * from v$log;
ALTER SYSTEM CHECKPOINT;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 100M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 100M;
# Both database tiger the command
col member format a100 heading "REDO DESTINATION"
select group#, member from v$logfile ORDER BY 1;
=========================================================================
set line 32000 pagesize 32000
SELECT NAME,OPEN_MODE,SWITCHOVER_STATUS,PROTECTION_LEVEL,PROTECTION_MODE ,DATABASE_ROLE
FROM V$DATABASE;
SELECT SEQUENCE#,NEXT_TIME,FIRST_TIME,APPLIED,NAME FROM V$ARCHIVED_LOG ORDER BY 1;
=========================================================================
LOG APPLY
alter database recover managed standby database disconnect from session;
Now go to primary database prompt, switch 3-4 times
SQL> alter system switch logfile;
SQL> Archive log list
Verifying the Standby Database
On standby database query the V$ARCHIVED_LOG view to verify that redo log received.
SQL> SELECT SEQUENCE#, APPLIED, NAME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Now connect scott/tiger on primary database
create table or insert row in any table.
Now connect as sys on primary database and execute following SQL statement
SQL> alter system switch logfile;
On standby database execute following SQL statements
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> recover managed standby database cancel;
SQL> alter database open read only;
And check whether the changes applied on the standby database or not.
SQL> SELECT SEQUENCE#, APPLIED, NAME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Maximum Protection This mode offers the highest level of data protection. Data is synchronously transmitted to the standby database from the primary database and transactions are not committed on the primary database unless the redo data is available on at least one standby database configured in this mode. If the last standby database configured in this mode becomes unavailable, processing stops on the primary database. This mode ensures no-data-loss.
Maximum Availability This mode is similar to the maximum protection mode, including zero data loss. However, if a standby database becomes unavailable (for example, because of network connectivity problems), processing continues on the primary database. When the fault is corrected, the standby database is automatically resynchronized with the primary database.
Maximum Performance—This mode offers slightly less data protection on the primary database, but higher performance than maximum availability mode. In this mode, as the primary database processes transactions, redo data is asynchronously shipped to the standby database. The commit operation of the primary database does not wait for the standby database to acknowledge receipt of redo data before completing write operations on the primary database. If any standby destination becomes unavailable, processing continues on the primary database and there is little effect on primary database performance.
=========================================================================
-Maximum Protection.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;
=========================================================================
Quick Switchover Steps
-PRIMARY
1. Initiate the switchover on the primary database :
Your database should be open in READ WRITE mode
SQL> select open_mode, database_role , switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
READ WRITE PRIMARY SESSIONS ACTIVE
SQL> alter database switchover to STAN verify;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SQL> shu immediate
SQL> startup
SQL> select open_mode, database_role , switchover_status from v$database;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SQL> shu immediate
SQL> startup
SQL> select open_mode, database_role , switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
READ ONLY PHYSICAL STANDBY TO PRIMARY
-STANDBY
2. After step 1 finishes, Switch the original physical standby database to primary role;
SQL> select open_mode,database_role ,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
MOUNTED PHYSICAL STANDBY SWITCHOVER PENDING
Initiate Log apply if any log from Primary DB remain unapplied.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> Select open_mode,database_role ,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
MOUNTED PHYSICAL STANDBY TO PRIMARY
All log applied so stop the log apply.
SQL> Recover managed standby database cancel;
Now both database SWICHOVER_STATUS will show you TO PRIMARY means two standby database.
SQL> select open_mode,database_role ,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
MOUNTED PHYSICAL STANDBY TO PRIMARY
Now switch standby database to PRIMARY role.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SQL> shu immediate
SQL> startup
Now your standby database(STAN) becomes primary database and previous primary database(ORCL) becomes standby
STAN database location
SQL> select open_mode,database_role ,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
READ WRITE PRIMARY SESSIONS ACTIVE
ORCL database location
SQL> select open_mode,database_role ,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
READ ONLY PHYSICAL STANDBY TO PRIMARY
At STAN location
SQL> Alter system switch logfile;
SQL> Alter system switch logfile;
SQL> Archive log list
At ORCL location
SQL> Archive log list
Both location same Current log sequence 63
SELECT SEQUENCE#, APPLIED, NAME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APP NAME
--------- --- --------------------------------------------
60 NO D:\ORCL\STANDBY_LOG\ARC00060_0773345094.001
61 NO D:\ORCL\STANDBY_LOG\ARC00061_0773345094.001
62 NO D:\ORCL\STANDBY_LOG\ARC00062_0773345094.001
ORCL>select open_mode,database_role ,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
READ ONLY PHYSICAL STANDBY RECOVERY NEEDED
Initiate Log apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ORCL>select open_mode,database_role , switchover_status from v$database;
ORCL>select open_mode,database_role , switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
MOUNTED PHYSICAL STANDBY NOT ALLOWED
SELECT SEQUENCE#, APPLIED, NAME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APP NAME
--------- --- --------------------------------------------
60 YES D:\ORCL\STANDBY_LOG\ARC00060_0773345094.001
61 YES D:\ORCL\STANDBY_LOG\ARC00061_0773345094.001
62 YES D:\ORCL\STANDBY_LOG\ARC00062_0773345094.001
If you switch log at this moment from STAN location(primary db), it will be applied automatically at ORCL location. Log 63,64,65 will show you APPLIED YES.
To stop the log apply.
SQL> recover managed standby database cancel;
ORCL> select open_mode,database_role ,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
MOUNTED PHYSICAL STANDBY NOT ALLOWED
SQL> Alter database open;
ORCL>select open_mode,database_role ,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- --------------------
READ ONLY PHYSICAL STANDBY NOT ALLOWED
In READ ONLY mode if there is log switch on primary, new logs received but not applied.
SQL> SELECT SEQUENCE#, APPLIED, NAME FROM V$ARCHIVED_LOG WHERE SEQUENCE# > 60 ORDER BY SEQUENCE#;
SEQUENCE# APP NAME
---------- --- --------------------------------------------
71 NO D:\ORCL\STANDBY_LOG\ARC00071_0773345094.001
72 NO D:\ORCL\STANDBY_LOG\ARC00072_0773345094.001
If log file is not shipped to standby location then execute this command on both database. To register database with the listener.
SQL> alter system register;
Subscribe to:
Posts (Atom)