How does one select the LAST N rows from a table?
From Oracle 9i onwards, the RANK() and DENSE_RANK() functions can be used to determine the LAST N or BOTTOM N rows.
E
Examples:
Get the bottom 10 employees based on their salary
SELECT ename, sal FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal) sal_rank FROM emp ) WHERE sal_rank <= 10;
Select the employees getting the lowest 10 salaries
SELECT ename, sal FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal) sal_dense_rank FROM emp ) WHERE sal_dense_rank <= 10;
For Oracle 8i and above, one can get the bottom N rows using an inner-query with an ORDER BY clause:
SELECT * FROM (SELECT * FROM my_table ORDER BY col_name_1) WHERE ROWNUM < 10;
Use this workaround for older (8.0 and prior) releases:
SELECT * FROM my_table a WHERE 10 >= (SELECT COUNT(DISTINCT maxcol) FROM my_table b WHERE b.maxcol <= a.maxcol)
viernes, 13 de febrero de 2009
How Select the TOP N rows from a table?
TOP N rows from a table?
After Oracle 9i there is the RANK() and DENSE_RANK() functions which can be used to determine TOP N rows.
Below is the examples to find the top 5 employees based on their salary.
Using RANK()
SELECT employee_name, salary FROM ( SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) salary_rankFROM employee ) WHERE salary_rank <= 5;
Using Dense_Rank()
SELECT employee_name, salary FROM ( SELECT employee_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) salary_dense_rankFROM employee ) WHERE salary_dense_rank <= 5;
Using inner queryThis is an example of using an inner-query with an ORDER BY clause:
SELECT *FROM (SELECT * FROM employee ORDER BY salary DESC)WHERE ROWNUM < 5;
Using count distinct combination
SELECT *FROM employee eWHERE 5 >= (SELECT COUNT(DISTINCT salary)FROM employee bWHERE b.salary >= e.salary)ORDER BY salary DESC;
EJEMPLO DE SQL
select tabla,registros,fecha from (select tabla,registros,fecha,rank() OVER (order by registros desc) registros_rank
FROM CTM.AUD_REGISTRO_TABLAS
where to_date(fecha,'dd-mm-yyyy')='08-09-2009')
where registros_rank <=10
/
~
After Oracle 9i there is the RANK() and DENSE_RANK() functions which can be used to determine TOP N rows.
Below is the examples to find the top 5 employees based on their salary.
Using RANK()
SELECT employee_name, salary FROM ( SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) salary_rankFROM employee ) WHERE salary_rank <= 5;
Using Dense_Rank()
SELECT employee_name, salary FROM ( SELECT employee_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) salary_dense_rankFROM employee ) WHERE salary_dense_rank <= 5;
Using inner queryThis is an example of using an inner-query with an ORDER BY clause:
SELECT *FROM (SELECT * FROM employee ORDER BY salary DESC)WHERE ROWNUM < 5;
Using count distinct combination
SELECT *FROM employee eWHERE 5 >= (SELECT COUNT(DISTINCT salary)FROM employee bWHERE b.salary >= e.salary)ORDER BY salary DESC;
EJEMPLO DE SQL
select tabla,registros,fecha from (select tabla,registros,fecha,rank() OVER (order by registros desc) registros_rank
FROM CTM.AUD_REGISTRO_TABLAS
where to_date(fecha,'dd-mm-yyyy')='08-09-2009')
where registros_rank <=10
/
~
Restrict DDL on a Schema
Restrict DDL on a Schema
SQL> conn faruk/farukConnected.
SQL> create table before_trigger(a number);Table created.
SQL>conn system/aConnected.
SQL> CREATE OR REPLACE
2 TRIGGER BEFORE_DDL_FARUK
3 BEFORE DDL
4 ON FARUK.SCHEMA
5 BEGIN
6 RAISE_APPLICATION_ERROR(-30900,'DDL Operation is not Permitted.' );
7 END;
8 /Trigger created.
SQL> conn faruk/farukConnected.
SQL> create table after_trigger(a number);
create table after_trigger(a number)
*
ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-21000: error number argument to raise_application_error of -30900 is out ofrangeORA-06512: at line 2
SQL> conn faruk/farukConnected.
SQL> create table before_trigger(a number);Table created.
SQL>conn system/aConnected.
SQL> CREATE OR REPLACE
2 TRIGGER BEFORE_DDL_FARUK
3 BEFORE DDL
4 ON FARUK.SCHEMA
5 BEGIN
6 RAISE_APPLICATION_ERROR(-30900,'DDL Operation is not Permitted.' );
7 END;
8 /Trigger created.
SQL> conn faruk/farukConnected.
SQL> create table after_trigger(a number);
create table after_trigger(a number)
*
ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-21000: error number argument to raise_application_error of -30900 is out ofrangeORA-06512: at line 2
History of Oracle Corporation.
History of Oracle Corporation.
1)June 16, 1977: Oracle Corporation was incorporated in Redwood Shores, California as Software Development Laboratories (SDL) by Larry Ellison, Bob Miner and Ed Oates.
2)June 1979: SDL is renamed to Relational Software Inc. (RSI), and relocates to Sand Hill Road, Menlo Park, California. Oracle 2, the first version of the Oracle database software runs on PDP-11 and is sold to Wright-Patterson Air Force Base. The company decides to name the first version of its flagship product version 2 rather than version 1 because it believes companies may hesitate to buy the initial release of its product (and, or IBM would not divulge to render the original take).
3)October 1979: RSI actively promotes Oracle on the VAX platform (the software runs on the VAX in PDP-11 emulator mode)
4)1981 Umang Gupta joined Oracle Corporation where he wrote the first business plan for the company, and served as Vice President and General Manager of the Microcomputer Products.
5)February 1981: RSI begins developing tools for Oracle, including the Interactive Application Facility (IAF), a predecessor to Oracle*Forms.
6)Bruce Scott was one of the first employees at Oracle (then Software Development Laboratories). He co-founded Gupta Technologies (which later became Centura Software) in 1984 with Umang Gupta, and later became CEO and founder of PointBase, Inc. Bruce was co-author and co-architect of Oracle V1, V2 and V3. He created the sample schema "SCOTT" (containing tables like EMP and DEPT) with the password defaulted to TIGER (apparently named after his cat).
7)March 1983: RSI rewrites Oracle in C for portability and Oracle version 3 is released. RSI is renamed to Oracle to more closely align with its primary product. The word Oracle was the code name of a CIA project which the founders had all worked on while at the Ampex Corporation.
8)April 1984: Received additional funding from Sequoia Capital.
9)October 1984: Oracle version 4 released, introducing read consistency.
10)November 1984: Oracle ports the database software to the PC platform. The MS-DOS version (4.1.4) of Oracle runs in only 512K of memory. Oracle for MSDOS version 5 was released in 1986 running in Protected Mode on 286 machines using a technique invented by Mike Roberts, among the first products to do so.
11)April 1985: Oracle version 5 released. It is one of the first RDBMSs to operate in client-server mode.
12)1986: Oracle version 5.1 released with support for distributed queries. Investigations into clustering begin.
13)March 12, 1986: Oracle goes public with revenues of $55 million USD.
14)August 1987: Oracle founds its Applications division, building business management software closely integrated with its database software. Oracle acquires TCI for its project management software.
15)1988: Oracle version 6 is released with support for row-level locking and hot backups. The PL/SQL procedural language engine was embedded in the database but no provision was made to store program blocks such as procedures and triggers in the database - this capability was added in v7. PL/SQL blocks could be submitted for immediate execution in the server from an environment such as SQL*Plus, or via SQL statements embedded in a host program. Separate PL/SQL engines were included in various client tools (SQL*Forms, Reports).
16)1989: Oracle moves world headquarters to Redwood Shores, California. Revenues reach US$584 million.
17)1990: In the third quarter, Oracle reports its first ever loss, hundreds of employees are laid off. Ellison hires Jeffrey O. Henley as CFO and Raymond Lane as COO.
18)June 1992: Oracle 7 released with performance enhancements, administrative utilities, application development tools, security features, the ability to persist PL/SQL program units in the database as stored procedures and triggers, and support for declarative referential integrity.
19)1993: Releases Oracle's Cooperative Development Environment (CDE) which bundles Oracle Forms, Reports, Graphics, Book.
20)1994: Oracle acquired the database-product DEC Rdb (now called Oracle Rdb) from Digital Equipment Corporation (DEC) and development is still going on. Oracle Rdb is only available on the OpenVMS platform (also a former product of DEC).
21)June 21, 1995: Oracle announces new data warehousing facilities, including parallel queries.
22)November 1995: Oracle is one of the first large software companies to announce an internet strategy when Ellison introduces the network computer concept at an IDC conference in Paris.
23)April 1997: Oracle releases the first version of Discoverer, an ad-hoc query tool for business intelligence.
24)June 1997: Oracle 8 is released with SQL object technology, internet technology and support for terabytes of data.
25)September 1997: Oracle announces its commitment to the Java platform, and introduces Oracle's Java integrated development environment, which will come to be known as Oracle JDeveloper.
26)January 1998: Oracle releases Oracle Applications 10.7 NCA. All the applications in the business software now run across the web in a standard web browser.
27)May 1998: Oracle Applications 11 is released.
28)April 1998: Oracle announces that it will integrate a Java virtual machine with Oracle Database.
29)September 1998: Oracle 8i is released.
30)October 1998: Oracle 8 and Oracle Application Server 4.0 are released on the Linux platform.
31)May 1999: Oracle releases JDeveloper 2.0, showcasing Business Components for Java (BC4J), a set of libraries and development tools for building database aware applications.
32)2000: OracleMobile subsidiary founded. Oracle 9i released.
33)May 2000: Oracle announces the Internet File System (iFS), later rebranded as Oracle Content Management SDK.
34)June 2000: Oracle9i Application Server released with support for building portals.
35)2001: Ellison announces that Oracle saved $1 billion implementing and using its own business applications.
36)2004: Oracle 10g released.
37)December 13, 2004: After a long battle over the control of PeopleSoft, Oracle announces that it has signed an agreement to acquire PeopleSoft for $26.50 per share (approximately $10.3 billion).
38)January 14, 2005: Oracle announces that it will reduce its combined workforce to 50,000, a reduction of approximately 5,000 following the PeopleSoft take over. 90% of PeopleSoft product development and product support staff will be retained.
39)March, 2005: Oracle extends its Middle East operations by opening a regional office in Amman, Jordan.
40)September 2005: Oracles announces that it has agreed to acquire Global Logistics Technologies, Inc. (private company), a global provider of logistics and transportation managements software (TMS) solutions through a cash offer.
41)September 12, 2005: Oracle announces it had agreed to buy Siebel Systems, the global leader in CRM technologies and a key player in the BI realm, for $5.8 billion.
42)October 25, 2006: Oracle announces Unbreakable Linux.
43)November 2, 2006: Oracles announces that it has agreed to acquire Stellent, Inc. (NASDAQ: STEL), a global provider of enterprise content management (ECM) software solutions, through a cash tender offer for $13.50 per share, or approximately $440 million.
44)December 15, 2006, a majority of MetaSolv stockholders approved Oracle’s acquisition of MetaSolv Software, a Leading Provider of Operations Support Systems (OSS) Software for the Communications Industry.
45)March 1, 2007: Oracle announced that it has agreed to buy Hyperion Solutions Corporation (Nasdaq: HYSL), a leading global provider of performance management software solutions, through a cash tender offer for $52.00 per share, or approximately $3.3 billion. The transaction is subject to customary conditions and is expected to close in April 2007.
46)March 22, 2007: Oracle filed a court case against its major competitor SAP AG in the Californian courts for malpractice and unfair competition. The full text of the filing can be found on the claimants web site under the heading newsroom.
47)October 12, 2007: Oracle announced that it had made a bid to buy BEA Systems for a price of $17/share, an offer that was rejected by the BEA board who felt that the company was worth more than that.
48)October 16, 2007: Oracle confirms impending departure of John Wookey, senior vice president for application development and head of its applications strategy, raising questions in the planned release and future of Oracle's Fusion Applications strategy.
49)January 16, 2008: Oracle announces it is buying BEA Systems for $19.375/Share in cash for a total of '$7.2 billion net of cash.'
H.Tonguç Yılmaz - Oracle BlogHistory of OraclePosted in Oracle Other by H.Tonguç Yılmaz on December 27th, 2006
1978 -> Oracle V1; first commercial SQL relational database management system (RDBMS), Main architect Bob Miner, ran on pdp-11 under rsx; 128Kb memory, written in assembly, separated oracle and user codesto overcome the memory limitations
1979 -> Oracle V2; written in pdp-11 assembly language, ran on vax/vms in compatibility mode
1980 -> Oracle V3; written in C, soptable source code, introduced Transactions
1984 -> Oracle V4; introduced read consistency, ported to many plathforms, first interopability between PC and server
1986 -> Oracle V5; true client-sever, vax cluster support, distributed queries
1989 -> Oracle V6; OLTP performance enhancements, online backup/recovery, row level locking, plsql language, parallel server
1993 -> Oracle V7; declarative referential integrity, stored procedures and triggers, shared SQL, parallel execution, Advanced replication
1997 -> Oracle V8; Object-relational database, three-tier architecture, partitioning
1999 -> Oracle V8i; Java in database and native java support, XML support, Oracle Internet Directory, Summary management interMedia, Data warehousing enhancements, ported to Linux, Business components for java(BC4J), WebDB introduced(eventually mature into Portal and ApEx)
2001 -> Oracle V9i; Automatic segment space management, Real Apllication Clusters, Internet security enhancements, Data Guard, Advanced globalization support, record-breaking TPC-C benchmark results, 1st to complete 3 terabyte TPC-H world record
2003 -> Oracle V10g; Enterprise Grid Computing, 64-bit Linux with IPF
2005 -> Oracle VXE; free Oracle 10gR2 database
2007 -> Oracle V11g; as announced at Openworld 2006
In a nutshell, Version Date Release Name
OracleRelease Date2 June 19793 March 19834 October 19845.0 April 19856.0 July 19887.0 June 19927.1 May 19947.2 May 19957.3 February 19968.0 June 1997 Oracle 88.1.5 February 1999 Oracle 8i Release 18.1.6 November 1999 Oracle 8i Release 28.1.7 August 2000 Oracle 8i Release 39.0.1 June 2001 Oracle 9i Release 19.2 May 2002 Oracle 9i Release 210.1 January 2004 Oracle 10g Release 110.2 July 2005 Oracle 10g Release
2007 Oracle 11g
1)June 16, 1977: Oracle Corporation was incorporated in Redwood Shores, California as Software Development Laboratories (SDL) by Larry Ellison, Bob Miner and Ed Oates.
2)June 1979: SDL is renamed to Relational Software Inc. (RSI), and relocates to Sand Hill Road, Menlo Park, California. Oracle 2, the first version of the Oracle database software runs on PDP-11 and is sold to Wright-Patterson Air Force Base. The company decides to name the first version of its flagship product version 2 rather than version 1 because it believes companies may hesitate to buy the initial release of its product (and, or IBM would not divulge to render the original take).
3)October 1979: RSI actively promotes Oracle on the VAX platform (the software runs on the VAX in PDP-11 emulator mode)
4)1981 Umang Gupta joined Oracle Corporation where he wrote the first business plan for the company, and served as Vice President and General Manager of the Microcomputer Products.
5)February 1981: RSI begins developing tools for Oracle, including the Interactive Application Facility (IAF), a predecessor to Oracle*Forms.
6)Bruce Scott was one of the first employees at Oracle (then Software Development Laboratories). He co-founded Gupta Technologies (which later became Centura Software) in 1984 with Umang Gupta, and later became CEO and founder of PointBase, Inc. Bruce was co-author and co-architect of Oracle V1, V2 and V3. He created the sample schema "SCOTT" (containing tables like EMP and DEPT) with the password defaulted to TIGER (apparently named after his cat).
7)March 1983: RSI rewrites Oracle in C for portability and Oracle version 3 is released. RSI is renamed to Oracle to more closely align with its primary product. The word Oracle was the code name of a CIA project which the founders had all worked on while at the Ampex Corporation.
8)April 1984: Received additional funding from Sequoia Capital.
9)October 1984: Oracle version 4 released, introducing read consistency.
10)November 1984: Oracle ports the database software to the PC platform. The MS-DOS version (4.1.4) of Oracle runs in only 512K of memory. Oracle for MSDOS version 5 was released in 1986 running in Protected Mode on 286 machines using a technique invented by Mike Roberts, among the first products to do so.
11)April 1985: Oracle version 5 released. It is one of the first RDBMSs to operate in client-server mode.
12)1986: Oracle version 5.1 released with support for distributed queries. Investigations into clustering begin.
13)March 12, 1986: Oracle goes public with revenues of $55 million USD.
14)August 1987: Oracle founds its Applications division, building business management software closely integrated with its database software. Oracle acquires TCI for its project management software.
15)1988: Oracle version 6 is released with support for row-level locking and hot backups. The PL/SQL procedural language engine was embedded in the database but no provision was made to store program blocks such as procedures and triggers in the database - this capability was added in v7. PL/SQL blocks could be submitted for immediate execution in the server from an environment such as SQL*Plus, or via SQL statements embedded in a host program. Separate PL/SQL engines were included in various client tools (SQL*Forms, Reports).
16)1989: Oracle moves world headquarters to Redwood Shores, California. Revenues reach US$584 million.
17)1990: In the third quarter, Oracle reports its first ever loss, hundreds of employees are laid off. Ellison hires Jeffrey O. Henley as CFO and Raymond Lane as COO.
18)June 1992: Oracle 7 released with performance enhancements, administrative utilities, application development tools, security features, the ability to persist PL/SQL program units in the database as stored procedures and triggers, and support for declarative referential integrity.
19)1993: Releases Oracle's Cooperative Development Environment (CDE) which bundles Oracle Forms, Reports, Graphics, Book.
20)1994: Oracle acquired the database-product DEC Rdb (now called Oracle Rdb) from Digital Equipment Corporation (DEC) and development is still going on. Oracle Rdb is only available on the OpenVMS platform (also a former product of DEC).
21)June 21, 1995: Oracle announces new data warehousing facilities, including parallel queries.
22)November 1995: Oracle is one of the first large software companies to announce an internet strategy when Ellison introduces the network computer concept at an IDC conference in Paris.
23)April 1997: Oracle releases the first version of Discoverer, an ad-hoc query tool for business intelligence.
24)June 1997: Oracle 8 is released with SQL object technology, internet technology and support for terabytes of data.
25)September 1997: Oracle announces its commitment to the Java platform, and introduces Oracle's Java integrated development environment, which will come to be known as Oracle JDeveloper.
26)January 1998: Oracle releases Oracle Applications 10.7 NCA. All the applications in the business software now run across the web in a standard web browser.
27)May 1998: Oracle Applications 11 is released.
28)April 1998: Oracle announces that it will integrate a Java virtual machine with Oracle Database.
29)September 1998: Oracle 8i is released.
30)October 1998: Oracle 8 and Oracle Application Server 4.0 are released on the Linux platform.
31)May 1999: Oracle releases JDeveloper 2.0, showcasing Business Components for Java (BC4J), a set of libraries and development tools for building database aware applications.
32)2000: OracleMobile subsidiary founded. Oracle 9i released.
33)May 2000: Oracle announces the Internet File System (iFS), later rebranded as Oracle Content Management SDK.
34)June 2000: Oracle9i Application Server released with support for building portals.
35)2001: Ellison announces that Oracle saved $1 billion implementing and using its own business applications.
36)2004: Oracle 10g released.
37)December 13, 2004: After a long battle over the control of PeopleSoft, Oracle announces that it has signed an agreement to acquire PeopleSoft for $26.50 per share (approximately $10.3 billion).
38)January 14, 2005: Oracle announces that it will reduce its combined workforce to 50,000, a reduction of approximately 5,000 following the PeopleSoft take over. 90% of PeopleSoft product development and product support staff will be retained.
39)March, 2005: Oracle extends its Middle East operations by opening a regional office in Amman, Jordan.
40)September 2005: Oracles announces that it has agreed to acquire Global Logistics Technologies, Inc. (private company), a global provider of logistics and transportation managements software (TMS) solutions through a cash offer.
41)September 12, 2005: Oracle announces it had agreed to buy Siebel Systems, the global leader in CRM technologies and a key player in the BI realm, for $5.8 billion.
42)October 25, 2006: Oracle announces Unbreakable Linux.
43)November 2, 2006: Oracles announces that it has agreed to acquire Stellent, Inc. (NASDAQ: STEL), a global provider of enterprise content management (ECM) software solutions, through a cash tender offer for $13.50 per share, or approximately $440 million.
44)December 15, 2006, a majority of MetaSolv stockholders approved Oracle’s acquisition of MetaSolv Software, a Leading Provider of Operations Support Systems (OSS) Software for the Communications Industry.
45)March 1, 2007: Oracle announced that it has agreed to buy Hyperion Solutions Corporation (Nasdaq: HYSL), a leading global provider of performance management software solutions, through a cash tender offer for $52.00 per share, or approximately $3.3 billion. The transaction is subject to customary conditions and is expected to close in April 2007.
46)March 22, 2007: Oracle filed a court case against its major competitor SAP AG in the Californian courts for malpractice and unfair competition. The full text of the filing can be found on the claimants web site under the heading newsroom.
47)October 12, 2007: Oracle announced that it had made a bid to buy BEA Systems for a price of $17/share, an offer that was rejected by the BEA board who felt that the company was worth more than that.
48)October 16, 2007: Oracle confirms impending departure of John Wookey, senior vice president for application development and head of its applications strategy, raising questions in the planned release and future of Oracle's Fusion Applications strategy.
49)January 16, 2008: Oracle announces it is buying BEA Systems for $19.375/Share in cash for a total of '$7.2 billion net of cash.'
H.Tonguç Yılmaz - Oracle BlogHistory of OraclePosted in Oracle Other by H.Tonguç Yılmaz on December 27th, 2006
1978 -> Oracle V1; first commercial SQL relational database management system (RDBMS), Main architect Bob Miner, ran on pdp-11 under rsx; 128Kb memory, written in assembly, separated oracle and user codesto overcome the memory limitations
1979 -> Oracle V2; written in pdp-11 assembly language, ran on vax/vms in compatibility mode
1980 -> Oracle V3; written in C, soptable source code, introduced Transactions
1984 -> Oracle V4; introduced read consistency, ported to many plathforms, first interopability between PC and server
1986 -> Oracle V5; true client-sever, vax cluster support, distributed queries
1989 -> Oracle V6; OLTP performance enhancements, online backup/recovery, row level locking, plsql language, parallel server
1993 -> Oracle V7; declarative referential integrity, stored procedures and triggers, shared SQL, parallel execution, Advanced replication
1997 -> Oracle V8; Object-relational database, three-tier architecture, partitioning
1999 -> Oracle V8i; Java in database and native java support, XML support, Oracle Internet Directory, Summary management interMedia, Data warehousing enhancements, ported to Linux, Business components for java(BC4J), WebDB introduced(eventually mature into Portal and ApEx)
2001 -> Oracle V9i; Automatic segment space management, Real Apllication Clusters, Internet security enhancements, Data Guard, Advanced globalization support, record-breaking TPC-C benchmark results, 1st to complete 3 terabyte TPC-H world record
2003 -> Oracle V10g; Enterprise Grid Computing, 64-bit Linux with IPF
2005 -> Oracle VXE; free Oracle 10gR2 database
2007 -> Oracle V11g; as announced at Openworld 2006
In a nutshell, Version Date Release Name
OracleRelease Date2 June 19793 March 19834 October 19845.0 April 19856.0 July 19887.0 June 19927.1 May 19947.2 May 19957.3 February 19968.0 June 1997 Oracle 88.1.5 February 1999 Oracle 8i Release 18.1.6 November 1999 Oracle 8i Release 28.1.7 August 2000 Oracle 8i Release 39.0.1 June 2001 Oracle 9i Release 19.2 May 2002 Oracle 9i Release 210.1 January 2004 Oracle 10g Release 110.2 July 2005 Oracle 10g Release
2007 Oracle 11g
Restore and Recover database to a new host
Restore and Recover database to a new host
In this post I will show how I can restore and recover a database to another host. In this case DBID of the database will be the same as of original database. But whenever your purpose is to create a new copy of the database then use RMAN DUPLICATE; this will change the DBID of the new database.
In this example my database name dbase1 and the database dbase1 is running on neptune machine. I like to take a backup on neptune machine, transfer the backup to saturn machine and perform restore and recover in saturn machine.
1) Machine(Source)
RMAN> backup database;
Starting backup at 06-MAY-08using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/oradata2/data1/dbase1/system01.dbfinput datafile fno=00003 name=/oradata2/data1/dbase1/sysaux01.dbfinput datafile fno=00005 name=/oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbfinput datafile fno=00006 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbfinput datafile fno=00002 name=/oradata2/data1/dbase1/undotbs01.dbfinput datafile fno=00004 name=/oradata2/data1/dbase1/users01.dbfinput datafile fno=00007 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbfinput datafile fno=00008 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbfchannel ORA_DISK_1: starting piece 1 at 06-MAY-08channel ORA_DISK_1: finished piece 1 at 06-MAY-08piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp tag=TAG20080506T150716 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:35Finished backup at 06-MAY-08
Starting Control File and SPFILE Autobackup at 06-MAY-08piece handle=/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654016132_421c64vl_.bkp comment=NONEFinished Control File and SPFILE Autobackup at 06-MAY-08
2)Transfer this two backup pieces to target machine(From Neptune) bash-3.00$
scp /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp oracle@saturn:/oradata2/Password:o1_mf_nnndf_TAG20080 100% *********************************************** 525 MB 00:59bash-3.00
$ scp /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654016132_421c64vl_.bkp oracle@saturn:/oradata2/Password:o1_mf_s_654016132_42 100% *********************************************** 6976 KB 00:00
3)Determine the DBID of source machine()
SQL> select dbid from v$database;DBID----------3386862614
4)Now perform task on target machine(Saturn here).First set ORACLE_SID,-bash-3.00
$export ORACLE_SID=dbase1
Then connect to rman,-bash-3.00
$ rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 05:22:39 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database (not started)
5)Set DBID and restore spfile to pfile.
RMAN> set dbid 3386862614
executing command: SET DBID
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'
starting Oracle instance without parameter file for retrival of spfileOracle instance started
Total System Global Area 159383552 bytesFixed Size 2019224 bytesVariable Size 67108968 bytesDatabase Buffers 83886080 bytesRedo Buffers 6369280 bytes
RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora' from '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';
Starting restore at 06-MAY-08using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /oradata2/o1_mf_s_654016132_421c64vl_.bkpchannel ORA_DISK_1: SPFILE restore from autobackup completeFinished restore at 06-MAY-08
Open the pfile with an editor file and if you wish change the location 6)start the instance with pfile.
RMAN> STARTUP FORCE NOMOUNT PFILE='/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora';
Oracle instance started
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytesVariable Size 109055720 bytesDatabase Buffers 92274688 bytesRedo Buffers 6365184 bytes
7)Restore controlfile and mount the database.
RMAN> RESTORE CONTROLFILE FROM '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';
Starting restore at 06-MAY-08using channel ORA_DISK_1
channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:02output filename=/oradata2/DBase1/control01.ctloutput filename=/oradata2/DBase1/control02.ctloutput filename=/oradata2/DBase1/control03.ctlFinished restore at 06-MAY-08
RMAN> ALTER DATABASE MOUNT;
database mountedreleased channel: ORA_DISK_1
8)From SQL*Plus determine the data file and redo log file name.
SQL> COLUMN NAME FORMAT a70SQL> 1 SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE2 UNION3* SELECT GROUP#,MEMBER FROM V$LOGFILE
File/Grp# NAME---------- ------------------------------------------------------------1 /oradata2/data1/dbase1/redo01.log1 /oradata2/data1/dbase1/system01.dbf2 /oradata2/data1/dbase1/redo02.log2 /oradata2/data1/dbase1/undotbs01.dbf3 /oradata2/data1/dbase1/redo03.log3 /oradata2/data1/dbase1/sysaux01.dbf4 /oradata2/data1/dbase1/users01.dbf5 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf6 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf7 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf8 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
11 rows selected.
9)Catalog your backuppiece.
RMAN> catalog backuppiece '/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp';
cataloged backuppiecebackup piece handle=/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp recid=33 stamp=65398295
RMAN> list backup;
List of Backup Sets===================
BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------32 Full 525.67M DISK 00:01:31 06-MAY-08BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20080506T150716Piece Name: /oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkpList of Datafiles in backup set 32File LV Type Ckp SCN Ckp Time Name---- -- ---- ---------- --------- ----1 Full 745212 06-MAY-08 /oradata2/data1/dbase1/system01.dbf2 Full 745212 06-MAY-08 /oradata2/data1/dbase1/undotbs01.dbf3 Full 745212 06-MAY-08 /oradata2/data1/dbase1/sysaux01.dbf4 Full 745212 06-MAY-08 /oradata2/data1/dbase1/users01.dbf5 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf6 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf7 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf8 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
10)Make a script by issuing SET NEWNAME if you want different file name other than source.
In the script issue SET UNTIL clause and restore and recover database.
RMAN> @/export/home/oracle/rman
RMAN> run{
2> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
3> set newname for datafile 2 to '/oradata2/DBase1/undotbs01.dbf';
4> set newname for datafile 3 to '/oradata2/DBase1/sysaux01.dbf';
5> set newname for datafile 4 to '/oradata2/DBase1/users01.dbf';
6> set newname for datafile 5 to '/oradata2/DBase1/tbs201.dbf';
7> set newname for datafile 6 to '/oradata2/DBase1/after_01.dbf';
8> set newname for datafile 7 to '/oradata2/DBase1/after_02.dbf';
9> set newname for datafile 8 to '/oradata2/DBase1/after_03.dbf';
10> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
11>
12> SET UNTIL SCN 745212;
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }
11)Open the Database resetlogs option.
RMAN> alter database open resetlogs;
In this post I will show how I can restore and recover a database to another host. In this case DBID of the database will be the same as of original database. But whenever your purpose is to create a new copy of the database then use RMAN DUPLICATE; this will change the DBID of the new database.
In this example my database name dbase1 and the database dbase1 is running on neptune machine. I like to take a backup on neptune machine, transfer the backup to saturn machine and perform restore and recover in saturn machine.
1) Machine(Source)
RMAN> backup database;
Starting backup at 06-MAY-08using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/oradata2/data1/dbase1/system01.dbfinput datafile fno=00003 name=/oradata2/data1/dbase1/sysaux01.dbfinput datafile fno=00005 name=/oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbfinput datafile fno=00006 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbfinput datafile fno=00002 name=/oradata2/data1/dbase1/undotbs01.dbfinput datafile fno=00004 name=/oradata2/data1/dbase1/users01.dbfinput datafile fno=00007 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbfinput datafile fno=00008 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbfchannel ORA_DISK_1: starting piece 1 at 06-MAY-08channel ORA_DISK_1: finished piece 1 at 06-MAY-08piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp tag=TAG20080506T150716 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:35Finished backup at 06-MAY-08
Starting Control File and SPFILE Autobackup at 06-MAY-08piece handle=/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654016132_421c64vl_.bkp comment=NONEFinished Control File and SPFILE Autobackup at 06-MAY-08
2)Transfer this two backup pieces to target machine(From Neptune) bash-3.00$
scp /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp oracle@saturn:/oradata2/Password:o1_mf_nnndf_TAG20080 100% *********************************************** 525 MB 00:59bash-3.00
$ scp /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/o1_mf_s_654016132_421c64vl_.bkp oracle@saturn:/oradata2/Password:o1_mf_s_654016132_42 100% *********************************************** 6976 KB 00:00
3)Determine the DBID of source machine()
SQL> select dbid from v$database;DBID----------3386862614
4)Now perform task on target machine(Saturn here).First set ORACLE_SID,-bash-3.00
$export ORACLE_SID=dbase1
Then connect to rman,-bash-3.00
$ rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 05:22:39 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database (not started)
5)Set DBID and restore spfile to pfile.
RMAN> set dbid 3386862614
executing command: SET DBID
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'
starting Oracle instance without parameter file for retrival of spfileOracle instance started
Total System Global Area 159383552 bytesFixed Size 2019224 bytesVariable Size 67108968 bytesDatabase Buffers 83886080 bytesRedo Buffers 6369280 bytes
RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora' from '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';
Starting restore at 06-MAY-08using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /oradata2/o1_mf_s_654016132_421c64vl_.bkpchannel ORA_DISK_1: SPFILE restore from autobackup completeFinished restore at 06-MAY-08
Open the pfile with an editor file and if you wish change the location 6)start the instance with pfile.
RMAN> STARTUP FORCE NOMOUNT PFILE='/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora';
Oracle instance started
Total System Global Area 209715200 bytes
Fixed Size 2019608 bytesVariable Size 109055720 bytesDatabase Buffers 92274688 bytesRedo Buffers 6365184 bytes
7)Restore controlfile and mount the database.
RMAN> RESTORE CONTROLFILE FROM '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';
Starting restore at 06-MAY-08using channel ORA_DISK_1
channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:02output filename=/oradata2/DBase1/control01.ctloutput filename=/oradata2/DBase1/control02.ctloutput filename=/oradata2/DBase1/control03.ctlFinished restore at 06-MAY-08
RMAN> ALTER DATABASE MOUNT;
database mountedreleased channel: ORA_DISK_1
8)From SQL*Plus determine the data file and redo log file name.
SQL> COLUMN NAME FORMAT a70SQL> 1 SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE2 UNION3* SELECT GROUP#,MEMBER FROM V$LOGFILE
File/Grp# NAME---------- ------------------------------------------------------------1 /oradata2/data1/dbase1/redo01.log1 /oradata2/data1/dbase1/system01.dbf2 /oradata2/data1/dbase1/redo02.log2 /oradata2/data1/dbase1/undotbs01.dbf3 /oradata2/data1/dbase1/redo03.log3 /oradata2/data1/dbase1/sysaux01.dbf4 /oradata2/data1/dbase1/users01.dbf5 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf6 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf7 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf8 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
11 rows selected.
9)Catalog your backuppiece.
RMAN> catalog backuppiece '/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp';
cataloged backuppiecebackup piece handle=/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp recid=33 stamp=65398295
RMAN> list backup;
List of Backup Sets===================
BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------32 Full 525.67M DISK 00:01:31 06-MAY-08BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20080506T150716Piece Name: /oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkpList of Datafiles in backup set 32File LV Type Ckp SCN Ckp Time Name---- -- ---- ---------- --------- ----1 Full 745212 06-MAY-08 /oradata2/data1/dbase1/system01.dbf2 Full 745212 06-MAY-08 /oradata2/data1/dbase1/undotbs01.dbf3 Full 745212 06-MAY-08 /oradata2/data1/dbase1/sysaux01.dbf4 Full 745212 06-MAY-08 /oradata2/data1/dbase1/users01.dbf5 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf6 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf7 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf8 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
10)Make a script by issuing SET NEWNAME if you want different file name other than source.
In the script issue SET UNTIL clause and restore and recover database.
RMAN> @/export/home/oracle/rman
RMAN> run{
2> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
3> set newname for datafile 2 to '/oradata2/DBase1/undotbs01.dbf';
4> set newname for datafile 3 to '/oradata2/DBase1/sysaux01.dbf';
5> set newname for datafile 4 to '/oradata2/DBase1/users01.dbf';
6> set newname for datafile 5 to '/oradata2/DBase1/tbs201.dbf';
7> set newname for datafile 6 to '/oradata2/DBase1/after_01.dbf';
8> set newname for datafile 7 to '/oradata2/DBase1/after_02.dbf';
9> set newname for datafile 8 to '/oradata2/DBase1/after_03.dbf';
10> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
11>
12> SET UNTIL SCN 745212;
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }
11)Open the Database resetlogs option.
RMAN> alter database open resetlogs;
Suscribirse a:
Entradas (Atom)
