set heading off; set echo off; Set pages 999; set long 90000;
-------TO GET THE DDLS OF A PARTICULAR TABLE OR A INDEX FROM A USER---------
SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','ORACLE9I') FROM DUAL;
CREATE TABLE "ORACLE9I"."EMP" ( "NO" NUMBER, "NAME" VARCHAR2(10) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
--------------TO GET THE DDLS OF ALL THE TABLES AND INDEXS IN A USER---
select dbms_metadata.get_ddl('TABLE',u.table_name)from user_tables u;
SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME)FROM USER_INDEXES U;
----------TO GET THE DDLS OF A PARTICULAR TABLESPACE-----------------------
select dbms_metadata.get_ddl('TABLESPACE','USERS')FROM DUAL;
CREATE TABLESPACE "USERS" DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS1.DBF' SIZE 5242880 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL
-----TO GET THE DDLS OF A ALL TABLESPACES IN THE DATABASE-------------
select dbms_metadata.get_ddl('TABLESPACE',u.tablespace_name) FROM USER_tablespaces U;
Tuesday, October 13, 2009
Upgrade Oracle 10.1.0.1 to 10.2.0.1
Upgrade Oracle 10.1.0.1 to 10.2.0.1
1. Install software 10.1.0.1 in 1st ORACLE_HOME
Install oracle 10.1.0.1 before upgrading this to 10.2.0.1.
You must have 10.1.0.1 version installed along with database.2. RequirementsOracle DB: Oracle 10.1.0.1 (later) Operating System: Windows 2000 (Service Pack 2 or higher) 3. Pre - Installation Tasks Identify prior installationBefore upgrading to new version you must have installed oracle 10.1.0.1 and database created.
5. Download 10.2.0.1 softwareDownload and extract the oracle_10201_WINNT.zip software set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.
6. Backup your database.Oracle recommends that you create a backup of the database before you start upgradation.
I.e., (datafile, redologfile)
7. Check SYSTEM Tablespace Size Ensure that there is at least 50 MB of free space allocated to the SYSTEM tablespace.
query to check tablespace size and freespace
col "Tablespace" for a22col "Used MB" for 99,999,999col "Free MB" for 99,999,999col "Total MB" for 99,999,999select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free"from(select tablespace_name,round(sum(bytes) / 1048576) TotalSpacefrom dba_data_files group by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_namefrom dba_segments group by tablespace_name) tuwhere df.tablespace_name = tu.tablespace_name ;
Resize the system datafile if free space below 50 MB
ALTER DATABASE DATAFILE 'c:\oracle\oradata\orabase\system01.dbf' RESIZE 150M;
Check for free space from the above query again.
8. Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters to minimum 150 MB.
Users who have JVM (Java enabled) or JVM and XDB installed on their 10.1.0.1 databases should make sure that the init.ora parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE are each 150 MB or more before running the catpatch.sql upgrade script. Failure to do so could result in an unrecoverable memory failure while running of the script. Please note that JVM and XML DB was shipped as part of the default 10.1.0.1 seed database, and will be present unless the user explicitly installed a 10.1.0.1 instance without them. Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
Note: If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).
If the database uses sp-file use the below command.
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=150M SCOPE=SPFILE; System altered.SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=150M SCOPE=SPFILE; System altered.
9. Shutdown oracle database.C:\>set oracle_sid=orclC:\>sqlplus /nologSQL*plus: Release 10.1.0.1.0 - Production on Tue Nov 13 10:49:26 2007Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> connect sys@orcl as sysdbaEnter password: Connected.SQL> SHUTDOWNDatabase closed.Database dismounted.ORACLE instance shut down.
10. Stop all servicesC:\>set oracle_sid=orclC:\>net stop OracleDBConsoleORCLThe OracleDBConsoleORCLvice is stopping................The OracleDBConsoleORCLservice was stopped successfully.C:\>net stops OracleServiceORCLThe OracleServiceORCL is stopping.The OracleServiceORCLservice was stopped successfully.C:\>net stop OracleOraDB10g_Home1iSQL*Plus...The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.C:\>net stop OracleOraDB10g_Home1TNSListenerThe OracleOraDb10g_home1TNSListener service is stopping.The OracleOraDb10g_home1TNSListener service was stopped successfully.
C:\>net stop msdtcThe Distributed Transaction Coordinator service is stopping.The Distributed Transaction Coordinator service was stopped successfully
OR CAN STOP ALL THE ORACLE SERVICES FROM SERVICES.MSC FROM THE RUN CONSOLE ON THE START BUTTON.
11. Install 10.2.0.1 software in new ORACLE_HOME.
On welcome screen click Next1) On Specify file location screen, select the Name field and Path where you wish to Install new software (If you have multiple database installed on your machine you will see drop down menu select new database oracle_home)
2) On summay screen, click Install
After the software is installed on the new ORACLE_HOME
12. Upgrade the Database After you install the software in new ORACLE_HOME, you must perform the following steps on every database associated with the old ORACLE_HOME
1. Copy the p-file from old ORACLE_HOME and paste it into new ORACLE_HOME and rename it to associate the new SID (ie, 10.2.0.1) and change the parameter to compatible=10.2.0.1.0 in the p-file.
2. Create and Start NEW oracle services
oradim -new -sid orcl2 -intpwd orcl2 -pfile 'E:\oracle\product\10.2.0\db_2\database\INITorcl2.ora' -startmode auto
3 set ORACLE_SID=ORCL2
4. Connect sys userC :\> sqlplus /NOLOGSQL> CONNECT SYS/SYS_password AS SYSDBA5. Enter the following SQL*Plus commands:SQL> STARTUP UPGRADE pfile=’New path where p-file is located\p-file.ora’
SQL> SPOOL upgrade.logSQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sqlSQL> SPOOL OFFReview the upgrade.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script. This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems. 6. Restart the database: SQL> SHUTDOWNSQL> STARTUP7. Compile Invalid ObjectsRun the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended. SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sqlSQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.1.0 - ProductionPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for 32-bit Windows: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production
8. Clone the Database associated old ORACLE_HOME to new ORACLE_HOME if you want to remove the old ORACLE_HOME.
9. Delete the old oracle services by using the following command
oradim -delete –sid SID (associated with the previous database ie,10.1.0.1)
10. Remove the old Installation of oracle software (i.e., 10.1.0.1)
------END OF ORACLE UPGRADATION FROM 10.1.0.1 TO 10.2.0.2-----
1. Install software 10.1.0.1 in 1st ORACLE_HOME
Install oracle 10.1.0.1 before upgrading this to 10.2.0.1.
You must have 10.1.0.1 version installed along with database.2. RequirementsOracle DB: Oracle 10.1.0.1 (later) Operating System: Windows 2000 (Service Pack 2 or higher) 3. Pre - Installation Tasks Identify prior installationBefore upgrading to new version you must have installed oracle 10.1.0.1 and database created.
5. Download 10.2.0.1 softwareDownload and extract the oracle_10201_WINNT.zip software set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.
6. Backup your database.Oracle recommends that you create a backup of the database before you start upgradation.
I.e., (datafile, redologfile)
7. Check SYSTEM Tablespace Size Ensure that there is at least 50 MB of free space allocated to the SYSTEM tablespace.
query to check tablespace size and freespace
col "Tablespace" for a22col "Used MB" for 99,999,999col "Free MB" for 99,999,999col "Total MB" for 99,999,999select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free"from(select tablespace_name,round(sum(bytes) / 1048576) TotalSpacefrom dba_data_files group by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_namefrom dba_segments group by tablespace_name) tuwhere df.tablespace_name = tu.tablespace_name ;
Resize the system datafile if free space below 50 MB
ALTER DATABASE DATAFILE 'c:\oracle\oradata\orabase\system01.dbf' RESIZE 150M;
Check for free space from the above query again.
8. Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters to minimum 150 MB.
Users who have JVM (Java enabled) or JVM and XDB installed on their 10.1.0.1 databases should make sure that the init.ora parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE are each 150 MB or more before running the catpatch.sql upgrade script. Failure to do so could result in an unrecoverable memory failure while running of the script. Please note that JVM and XML DB was shipped as part of the default 10.1.0.1 seed database, and will be present unless the user explicitly installed a 10.1.0.1 instance without them. Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
Note: If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).
If the database uses sp-file use the below command.
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=150M SCOPE=SPFILE; System altered.SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=150M SCOPE=SPFILE; System altered.
9. Shutdown oracle database.C:\>set oracle_sid=orclC:\>sqlplus /nologSQL*plus: Release 10.1.0.1.0 - Production on Tue Nov 13 10:49:26 2007Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> connect sys@orcl as sysdbaEnter password: Connected.SQL> SHUTDOWNDatabase closed.Database dismounted.ORACLE instance shut down.
10. Stop all servicesC:\>set oracle_sid=orclC:\>net stop OracleDBConsoleORCLThe OracleDBConsoleORCLvice is stopping................The OracleDBConsoleORCLservice was stopped successfully.C:\>net stops OracleServiceORCLThe OracleServiceORCL is stopping.The OracleServiceORCLservice was stopped successfully.C:\>net stop OracleOraDB10g_Home1iSQL*Plus...The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.C:\>net stop OracleOraDB10g_Home1TNSListenerThe OracleOraDb10g_home1TNSListener service is stopping.The OracleOraDb10g_home1TNSListener service was stopped successfully.
C:\>net stop msdtcThe Distributed Transaction Coordinator service is stopping.The Distributed Transaction Coordinator service was stopped successfully
OR CAN STOP ALL THE ORACLE SERVICES FROM SERVICES.MSC FROM THE RUN CONSOLE ON THE START BUTTON.
11. Install 10.2.0.1 software in new ORACLE_HOME.
On welcome screen click Next1) On Specify file location screen, select the Name field and Path where you wish to Install new software (If you have multiple database installed on your machine you will see drop down menu select new database oracle_home)
2) On summay screen, click Install
After the software is installed on the new ORACLE_HOME
12. Upgrade the Database After you install the software in new ORACLE_HOME, you must perform the following steps on every database associated with the old ORACLE_HOME
1. Copy the p-file from old ORACLE_HOME and paste it into new ORACLE_HOME and rename it to associate the new SID (ie, 10.2.0.1) and change the parameter to compatible=10.2.0.1.0 in the p-file.
2. Create and Start NEW oracle services
oradim -new -sid orcl2 -intpwd orcl2 -pfile 'E:\oracle\product\10.2.0\db_2\database\INITorcl2.ora' -startmode auto
3 set ORACLE_SID=ORCL2
4. Connect sys userC :\> sqlplus /NOLOGSQL> CONNECT SYS/SYS_password AS SYSDBA5. Enter the following SQL*Plus commands:SQL> STARTUP UPGRADE pfile=’New path where p-file is located\p-file.ora’
SQL> SPOOL upgrade.logSQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sqlSQL> SPOOL OFFReview the upgrade.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script. This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems. 6. Restart the database: SQL> SHUTDOWNSQL> STARTUP7. Compile Invalid ObjectsRun the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended. SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sqlSQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.1.0 - ProductionPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for 32-bit Windows: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production
8. Clone the Database associated old ORACLE_HOME to new ORACLE_HOME if you want to remove the old ORACLE_HOME.
9. Delete the old oracle services by using the following command
oradim -delete –sid SID (associated with the previous database ie,10.1.0.1)
10. Remove the old Installation of oracle software (i.e., 10.1.0.1)
------END OF ORACLE UPGRADATION FROM 10.1.0.1 TO 10.2.0.2-----
Upgrade Oracle 10.2.0.1 to 10.2.0.2
Upgrade Oracle 10.2.0.1 to 10.2.0.2
1. Patch Set OverviewPatch set release 10.2.0.2. Before installing this patch set you must be need 10.2.0.1 version.2. RequirementsOracle DB: Oracle 10.2.0.1 (later)Operating System: Windows 2000 (Service Pack 2 or higher)3. Pre - Installation Tasks4. Identify prior installationBefore installing this patch you must install oracle 10.2.0.1 (or later version)5. Download Patch setDownload and extract the p4547817_10202_WINNT.zip patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.
6. Backup your database.Oracle recommends that you create a backup of the installed Oracle 10g software before you install the patch set and the database.
I.e., (datafile, redologfile)
7. Check SYSTEM Tablespace Size Ensure that there is at least 50 MB of free space allocated to the SYSTEM tablespace.
query to check tablespace size and freespace
col "Tablespace" for a22col "Used MB" for 99,999,999col "Free MB" for 99,999,999col "Total MB" for 99,999,999select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free"from(select tablespace_name,round(sum(bytes) / 1048576) TotalSpacefrom dba_data_files group by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_namefrom dba_segments group by tablespace_name) tuwhere df.tablespace_name = tu.tablespace_name ;
Resize the system datafile if free space below 50 MB
ALTER DATABASE DATAFILE 'c:\oracle\oradata\orabase\system01.dbf' RESIZE 150M;
Check for free space from the above query again.
8. Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters to minimum 150 MB.
Users who have JVM (Java enabled) or JVM and XDB installed on their 10.2.0.1 databases should make sure that the init.ora parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE are each 150 MB or more before running the catpatch.sql upgrade script. Failure to do so could result in an unrecoverable memory failure while running of the script. Please note that JVM and XML DB was shipped as part of the default 10.2.0.1 seed database, and will be present unless the user explicitly installed a 10.2.0.1 instance without them. Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
Note: If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).
If the system uses sp-file use the below command.
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=150M SCOPE=SPFILE; System altered.SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=150M SCOPE=SPFILE; System altered.
9. Shutdown oracle database.C:\>set oracle_sid=orclC:\>sqlplus /nologSQL*plus: Release 10.2.0.1.0 - Production on Tue Nov 13 10:49:26 2007Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> connect sys@orcl as sysdbaEnter password: Connected.SQL> SHUTDOWNDatabase closed.Database dismounted.ORACLE instance shut down.
10. Stop all servicesC:\>set oracle_sid=orclC:\>net stop OracleDBConsoleORCLThe OracleDBConsoleORCLvice is stopping................The OracleDBConsoleORCLservice was stopped successfully.C:\>net stops OracleServiceORCLThe OracleServiceORCL is stopping.The OracleServiceORCLservice was stopped successfully.C:\>net stop OracleOraDB10g_Home1iSQL*Plus...The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.C:\>net stop OracleOraDB10g_Home1TNSListenerThe OracleOraDb10g_home1TNSListener service is stopping.The OracleOraDb10g_home1TNSListener service was stopped successfully.
Note: while patch installation time if you face Distributed Transaction Coordinator still running. You should be stop this serviceC:\>net stop msdtcThe Distributed Transaction Coordinator service is stopping.The Distributed Transaction Coordinator service was stopped successfully
OR CAN STOP ALL THE ORACLE SERVICES FROM SERVICES.MSC FROM THE RUN CONSOLE ON THE START BUTTON.
11. Apply the patch after extracting the zip file (ie, p4547817_10202_WINNT.zip ) to the ORACLE_HOME where 10.2.0.1 is installed.
On welcome screen click Next1) On Specify file location screen, in Name field and Path select Database on which you wish to apply this patch (If you have multiple database installed on your machine you will see drop down menu to select database oracle_home)
2) On summay screen, click Install
Arter the patch is installed on the ORACLE_HOME
12. Upgrade the Database After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:1. Start all oracle services by commands or by services.msc from run console.2. Connect sys userC :\> sqlplus /NOLOGSQL> CONNECT SYS/SYS_password AS SYSDBA3. Enter the following SQL*Plus commands:SQL> STARTUP UPGRADESQL> SPOOL patch. logSQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sqlSQL> SPOOL OFFReview the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script. This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems. 4. Restart the database: SQL> SHUTDOWNSQL> STARTUP5. Compile Invalid ObjectsRun the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended. SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sqlSQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.3.0 - ProductionPL/SQL Release 10.2.0.3.0 - ProductionCORE 10.2.0.3.0 ProductionTNS for 32-bit Windows: Version 10.2.0.3.0 - ProductionNLSRTL Version 10.2.0.3.0 - Production
------END OF ORACLE UPGRADATION FROM 10.2.0.1 TO 10.2.0.2-----
Thanks
Harshavardhan_srinivas
Oracle Database administrator
MindTree Consulting Pvt. Ltd.
1. Patch Set OverviewPatch set release 10.2.0.2. Before installing this patch set you must be need 10.2.0.1 version.2. RequirementsOracle DB: Oracle 10.2.0.1 (later)Operating System: Windows 2000 (Service Pack 2 or higher)3. Pre - Installation Tasks4. Identify prior installationBefore installing this patch you must install oracle 10.2.0.1 (or later version)5. Download Patch setDownload and extract the p4547817_10202_WINNT.zip patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.
6. Backup your database.Oracle recommends that you create a backup of the installed Oracle 10g software before you install the patch set and the database.
I.e., (datafile, redologfile)
7. Check SYSTEM Tablespace Size Ensure that there is at least 50 MB of free space allocated to the SYSTEM tablespace.
query to check tablespace size and freespace
col "Tablespace" for a22col "Used MB" for 99,999,999col "Free MB" for 99,999,999col "Total MB" for 99,999,999select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free"from(select tablespace_name,round(sum(bytes) / 1048576) TotalSpacefrom dba_data_files group by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_namefrom dba_segments group by tablespace_name) tuwhere df.tablespace_name = tu.tablespace_name ;
Resize the system datafile if free space below 50 MB
ALTER DATABASE DATAFILE 'c:\oracle\oradata\orabase\system01.dbf' RESIZE 150M;
Check for free space from the above query again.
8. Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters to minimum 150 MB.
Users who have JVM (Java enabled) or JVM and XDB installed on their 10.2.0.1 databases should make sure that the init.ora parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE are each 150 MB or more before running the catpatch.sql upgrade script. Failure to do so could result in an unrecoverable memory failure while running of the script. Please note that JVM and XML DB was shipped as part of the default 10.2.0.1 seed database, and will be present unless the user explicitly installed a 10.2.0.1 instance without them. Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
Note: If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).
If the system uses sp-file use the below command.
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=150M SCOPE=SPFILE; System altered.SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=150M SCOPE=SPFILE; System altered.
9. Shutdown oracle database.C:\>set oracle_sid=orclC:\>sqlplus /nologSQL*plus: Release 10.2.0.1.0 - Production on Tue Nov 13 10:49:26 2007Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> connect sys@orcl as sysdbaEnter password: Connected.SQL> SHUTDOWNDatabase closed.Database dismounted.ORACLE instance shut down.
10. Stop all servicesC:\>set oracle_sid=orclC:\>net stop OracleDBConsoleORCLThe OracleDBConsoleORCLvice is stopping................The OracleDBConsoleORCLservice was stopped successfully.C:\>net stops OracleServiceORCLThe OracleServiceORCL is stopping.The OracleServiceORCLservice was stopped successfully.C:\>net stop OracleOraDB10g_Home1iSQL*Plus...The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.C:\>net stop OracleOraDB10g_Home1TNSListenerThe OracleOraDb10g_home1TNSListener service is stopping.The OracleOraDb10g_home1TNSListener service was stopped successfully.
Note: while patch installation time if you face Distributed Transaction Coordinator still running. You should be stop this serviceC:\>net stop msdtcThe Distributed Transaction Coordinator service is stopping.The Distributed Transaction Coordinator service was stopped successfully
OR CAN STOP ALL THE ORACLE SERVICES FROM SERVICES.MSC FROM THE RUN CONSOLE ON THE START BUTTON.
11. Apply the patch after extracting the zip file (ie, p4547817_10202_WINNT.zip ) to the ORACLE_HOME where 10.2.0.1 is installed.
On welcome screen click Next1) On Specify file location screen, in Name field and Path select Database on which you wish to apply this patch (If you have multiple database installed on your machine you will see drop down menu to select database oracle_home)
2) On summay screen, click Install
Arter the patch is installed on the ORACLE_HOME
12. Upgrade the Database After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:1. Start all oracle services by commands or by services.msc from run console.2. Connect sys userC :\> sqlplus /NOLOGSQL> CONNECT SYS/SYS_password AS SYSDBA3. Enter the following SQL*Plus commands:SQL> STARTUP UPGRADESQL> SPOOL patch. logSQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sqlSQL> SPOOL OFFReview the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script. This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems. 4. Restart the database: SQL> SHUTDOWNSQL> STARTUP5. Compile Invalid ObjectsRun the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended. SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sqlSQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.3.0 - ProductionPL/SQL Release 10.2.0.3.0 - ProductionCORE 10.2.0.3.0 ProductionTNS for 32-bit Windows: Version 10.2.0.3.0 - ProductionNLSRTL Version 10.2.0.3.0 - Production
------END OF ORACLE UPGRADATION FROM 10.2.0.1 TO 10.2.0.2-----
Thanks
Harshavardhan_srinivas
Oracle Database administrator
MindTree Consulting Pvt. Ltd.
Sql statement tracing using tkprof utility
How to get queries, which is executed by particular user:-
1.Change the timed_statistics parameter to TRUE:-
SQL> alter system set TIMED_STATISTICS=TRUE;
2.Turn tracing on user for session level:-
SQL> alter session set SQL_TRACE=TRUE;
Or
3.As a DBA, execute the below command to enable sql trace for particular user:-
Get the sid and serial# from the V$session view.
eg:-
select sid,serial#,username from v$session
where machine='A4MD08060';
select sid,serial#,username from v$session
where username='NAVPROD';
select sid,serial#,username from v$session
where username not in ('SYS');
Enabling trace:
SQL> exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
eg:-
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(93, 24638, true);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(81,64882, true);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(96,20312,true);
Disabling trace:
SQL> exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,false);
eg:-
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(93, 24638, false);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(81,64882, false);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(96,20312,false);
3.Then, see the user dump destination for trace file.
sqlplus sys as sysdba
sho parameter dump;
4.Execute the below command to analyze and to create insert script for generating sql statements from trace file:-
$ tkprof insert=tkprof_table.sql sys=no
eg:-
tkprof orap_ora_12523.trc orap_ora_12523.txt insert= orap_ora_12523.sql sys=no
tkprof orap_ora_19752.trc orap_ora_19752.txt insert= orap_ora_19752.sql sys=no
It will create in text file and script for to create tkprof_table.
5.Execute the tkprof_table.sql file in any user schema.
6.It will create tkprof_table, with contents.
7.find out the user_id for that particular user from dba_users
eg:-select user_id from dba_users
where username='NAVPROD';
8.Execute the below query to get the queries of particular user.
eg:-
SQL>set long 9999
set heading off
set pages 0
spool sql_statements.txt
select sql_statement from tkprof_table where user_id =;
Note:-
if you want it in .cvs or .xls use dbvisualiser or other tools and if you dont have those tools you can use command line to do the same.
For more info on command line check the posting "Oracle tables to Excel".
1.Change the timed_statistics parameter to TRUE:-
SQL> alter system set TIMED_STATISTICS=TRUE;
2.Turn tracing on user for session level:-
SQL> alter session set SQL_TRACE=TRUE;
Or
3.As a DBA, execute the below command to enable sql trace for particular user:-
Get the sid and serial# from the V$session view.
eg:-
select sid,serial#,username from v$session
where machine='A4MD08060';
select sid,serial#,username from v$session
where username='NAVPROD';
select sid,serial#,username from v$session
where username not in ('SYS');
Enabling trace:
SQL> exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
eg:-
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(93, 24638, true);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(81,64882, true);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(96,20312,true);
Disabling trace:
SQL> exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,false);
eg:-
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(93, 24638, false);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(81,64882, false);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(96,20312,false);
3.Then, see the user dump destination for trace file.
sqlplus sys as sysdba
sho parameter dump;
4.Execute the below command to analyze and to create insert script for generating sql statements from trace file:-
$ tkprof
eg:-
tkprof orap_ora_12523.trc orap_ora_12523.txt insert= orap_ora_12523.sql sys=no
tkprof orap_ora_19752.trc orap_ora_19752.txt insert= orap_ora_19752.sql sys=no
It will create in text file and script for to create tkprof_table.
5.Execute the tkprof_table.sql file in any user schema.
6.It will create tkprof_table, with contents.
7.find out the user_id for that particular user from dba_users
eg:-select user_id from dba_users
where username='NAVPROD';
8.Execute the below query to get the queries of particular user.
eg:-
SQL>set long 9999
set heading off
set pages 0
spool sql_statements.txt
select sql_statement from tkprof_table where user_id =
Note:-
if you want it in .cvs or .xls use dbvisualiser or other tools and if you dont have those tools you can use command line to do the same.
For more info on command line check the posting "Oracle tables to Excel".
Cloning Database From Hot Backup
Cloning Database From Hot Backup
To clone a db on another machine from hot backup, follow these steps:
1. Install Oracle server on new machine
2. Create directories, init p-file, data files, redo log files, trace of control file, archive from the original database on to second server where you need to clone
3. Add database name to tnsnames.ora, listener.ora on second server
4. Create database service with ORADIM (if OS is Windows)
Eg: oradim -new -sid-intpwd -pfile -startmode auto
5. On original db:
ALTER DATABASE BACKUP CONTROL FILE TO TRACE RESETLOGS;
6. Rename trace file to create_control.sql, edit the contents as follows:
STARTUP NOMOUNT CREATE CONTROLFILE SET DATABASE "" RESETLOGS ARCHIVELOG ... ;
7. On original db Then do:
ALTER SYSTEM ARCHIVE LOG CURRENT;
8. Copy the ORADATA directory including archived logs to second server
9. Go to second server, set SID, and use sqlplus to connect as SYSDBA
10. Delete the control files already copied over using OS commands
11.set the SID
If oracle: set $ORACLE_SID=
If windows: set ORACLE_SID=
12. Run the CREATE CONTROL file script shown above
create_control.sql
13. Issue: RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
---------------------------------------------------------------------
select ' alter tablespace ' tablespace_name ' begin backup ;' from dba_tablespaces;
select ' alter tablespace ' tablespace_name ' end backup ;' from dba_tablespaces;
To clone a db on another machine from hot backup, follow these steps:
1. Install Oracle server on new machine
2. Create directories, init p-file, data files, redo log files, trace of control file, archive from the original database on to second server where you need to clone
3. Add database name to tnsnames.ora, listener.ora on second server
4. Create database service with ORADIM (if OS is Windows)
Eg: oradim -new -sid
5. On original db:
ALTER DATABASE BACKUP CONTROL FILE TO TRACE RESETLOGS;
6. Rename trace file to create_control.sql, edit the contents as follows:
STARTUP NOMOUNT CREATE CONTROLFILE SET DATABASE "
7. On original db Then do:
ALTER SYSTEM ARCHIVE LOG CURRENT;
8. Copy the ORADATA directory including archived logs to second server
9. Go to second server, set SID, and use sqlplus to connect as SYSDBA
10. Delete the control files already copied over using OS commands
11.set the SID
If oracle: set $ORACLE_SID=
If windows: set ORACLE_SID=
12. Run the CREATE CONTROL file script shown above
create_control.sql
13. Issue: RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
---------------------------------------------------------------------
select ' alter tablespace ' tablespace_name ' begin backup ;' from dba_tablespaces;
select ' alter tablespace ' tablespace_name ' end backup ;' from dba_tablespaces;
Using Network_Link in Datapump
Using Network_Link in Datapump
===============================
Oracle's export and import utilities have historically used a disk file as intermediate storage when unloading or reloading the database. For large databases, this "dump file" was an issue because operating system limits on file size could be exceeded, making export impossible.
Creative DBAs have used file compression utilities, such as compress on UNIX, to get the most capacity from the dump file. Later versions of import and export allowed the use of multiple dump files to get around the limits.
In Oracle 10g, the Data Pump version of import can eliminate the dump file entirely by importing directly from another database instance.
Example:
--------
1. Create tnsnames for remote database from where the database has to be imported.[Target]
conn_151 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wisedba.com)
)
)
2. Create a directory for user to access during Datapump job[Target]
SYS> create directory defdir as '/home/ez10g/wisedba';
SYS> grant read, write on directory defdir to scott;
3. Grant user to create database link[Target}
SYS> grant create database link to scott;
4. Create database link as Scott user (to the remote database)[Target]
SCOTT> Create database link conn_151
2 connect to scott identified by tiger
3 using 'conn_151';
5. Checking tables in scott user (in local database)[Target]
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
6. Import 'EMP' table from remote database (without creating any dump file)[Target]
$ impdp scott/tiger tables=emp directory=defdir network_link=conn_151
Import: Release 10.2.0.1.0 - Production on Monday, 02 April, 2007 18:04:53
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** tables=emp directory=defdir network_link=conn_151
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."EMP" 114688 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 18:05:12
7. Check whether the table has been imported to local database
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP TABLE <<-- Table has been imported
BONUS TABLE
-----------------------------------FULL LEVEL IMPORT---------------------------------------
Example:
--------
1. Create tnsnames for remote database from where the database has to be imported.[Target]
qadb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = qadb)
)
)
2. Create a directory for user to access during Datapump job[Target]
SYS> create directory data_pump_dir as '/home/ez10g/wisedba';
SYS> grant read, write on directory defdir to system;
3. Grant user to create database link[Target}
SYS> grant create database link to system;
4. Create database link as Scott user (to the remote database)[Target]
SCOTT> Create database link qadb
2 connect to system identified by qadb
3 using 'qadb';
5. Checking users in the DB (in local database)
SYS> select * from all_users;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
CTXSYS 36 03-JUL-08
EXFSYS 34 03-JUL-08
WMSYS 25 03-JUL-08
DBSNMP 24 03-JUL-08
TSMSYS 21 03-JUL-08
DMSYS 35 03-JUL-08
DIP 19 03-JUL-08
OUTLN 11 03-JUL-08
SYSTEM 5 03-JUL-08
SYS 0 03-JUL-08
6. Import 'EMP' table from remote database (without creating any dump file)[Target]
$ impdp system/esbgui2008 directory=data_pump_dir network_link=qadb full=y
NOTE: The tablespace should be existing as same in the target DB (local db from where u need to import).
-----------------SCHEMA LEVEL-----------------------
1. Create tnsnames for remote database from where the database has to be imported.[Target]
qadb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = qadb)
)
)
2. Create a directory for user to access during Datapump job[Target]
SYS> create directory data_pump_dir as '/home/ez10g/wisedba';
SYS> grant read, write on directory defdir to scott;
3. Grant user to create database link [Target}
SYS> grant create database link to scott;
4. Create database link as Scott user (to the remote database)[Target]
SCOTT> Create database link qadb
2 connect to scott identified by tiger
3 using 'qadb';
5. Checking tables in scott schema(in local database)
SCOTT> select * from tab;
6. Import all table from remote database (without creating any dump file)[Target]
$ impdp scott/tiger directory=data_pump_dir network_link=qadb remap_schema=scott:scott
View
Purpose
DBA_DB_LINKS
Lists all database links in the database.
ALL_DB_LINKS
Lists all database links accessible to the connected user.
USER_DB_LINKS
Lists all database links owned by the connected user
===============================
Oracle's export and import utilities have historically used a disk file as intermediate storage when unloading or reloading the database. For large databases, this "dump file" was an issue because operating system limits on file size could be exceeded, making export impossible.
Creative DBAs have used file compression utilities, such as compress on UNIX, to get the most capacity from the dump file. Later versions of import and export allowed the use of multiple dump files to get around the limits.
In Oracle 10g, the Data Pump version of import can eliminate the dump file entirely by importing directly from another database instance.
Example:
--------
1. Create tnsnames for remote database from where the database has to be imported.[Target]
conn_151 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wisedba.com)
)
)
2. Create a directory for user to access during Datapump job[Target]
SYS> create directory defdir as '/home/ez10g/wisedba';
SYS> grant read, write on directory defdir to scott;
3. Grant user to create database link[Target}
SYS> grant create database link to scott;
4. Create database link as Scott user (to the remote database)[Target]
SCOTT> Create database link conn_151
2 connect to scott identified by tiger
3 using 'conn_151';
5. Checking tables in scott user (in local database)[Target]
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
6. Import 'EMP' table from remote database (without creating any dump file)[Target]
$ impdp scott/tiger tables=emp directory=defdir network_link=conn_151
Import: Release 10.2.0.1.0 - Production on Monday, 02 April, 2007 18:04:53
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** tables=emp directory=defdir network_link=conn_151
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."EMP" 114688 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 18:05:12
7. Check whether the table has been imported to local database
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP TABLE <<-- Table has been imported
BONUS TABLE
-----------------------------------FULL LEVEL IMPORT---------------------------------------
Example:
--------
1. Create tnsnames for remote database from where the database has to be imported.[Target]
qadb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = qadb)
)
)
2. Create a directory for user to access during Datapump job[Target]
SYS> create directory data_pump_dir as '/home/ez10g/wisedba';
SYS> grant read, write on directory defdir to system;
3. Grant user to create database link[Target}
SYS> grant create database link to system;
4. Create database link as Scott user (to the remote database)[Target]
SCOTT> Create database link qadb
2 connect to system identified by qadb
3 using 'qadb';
5. Checking users in the DB (in local database)
SYS> select * from all_users;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
CTXSYS 36 03-JUL-08
EXFSYS 34 03-JUL-08
WMSYS 25 03-JUL-08
DBSNMP 24 03-JUL-08
TSMSYS 21 03-JUL-08
DMSYS 35 03-JUL-08
DIP 19 03-JUL-08
OUTLN 11 03-JUL-08
SYSTEM 5 03-JUL-08
SYS 0 03-JUL-08
6. Import 'EMP' table from remote database (without creating any dump file)[Target]
$ impdp system/esbgui2008 directory=data_pump_dir network_link=qadb full=y
NOTE: The tablespace should be existing as same in the target DB (local db from where u need to import).
-----------------SCHEMA LEVEL-----------------------
1. Create tnsnames for remote database from where the database has to be imported.[Target]
qadb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = qadb)
)
)
2. Create a directory for user to access during Datapump job[Target]
SYS> create directory data_pump_dir as '/home/ez10g/wisedba';
SYS> grant read, write on directory defdir to scott;
3. Grant user to create database link [Target}
SYS> grant create database link to scott;
4. Create database link as Scott user (to the remote database)[Target]
SCOTT> Create database link qadb
2 connect to scott identified by tiger
3 using 'qadb';
5. Checking tables in scott schema(in local database)
SCOTT> select * from tab;
6. Import all table from remote database (without creating any dump file)[Target]
$ impdp scott/tiger directory=data_pump_dir network_link=qadb remap_schema=scott:scott
View
Purpose
DBA_DB_LINKS
Lists all database links in the database.
ALL_DB_LINKS
Lists all database links accessible to the connected user.
USER_DB_LINKS
Lists all database links owned by the connected user
Rman cloning
Introduction
Duplicate Database/Cloning Database using RMAN
You can use the RMAN DUPLICATE command create a duplicate database from target database backups while still retaining the original target database. A duplicate database is a copy of the target database that you can run independently for a variety of purposes. For example, you can use it to:
Test backup and recovery procedures
Export data such as a table that was inadvertently dropped from the production database, and then importing it back into the production database.
A duplicate database is distinct from a standby database, although both types of databases are created with the DUPLICATE command. A standby database is a copy of the primary database that you can update continually or periodically by using archived logs from the primary database. If the primary database is damaged or destroyed, then you can perform failover to the standby database and effectively transform it into the new primary database. A duplicate database, on the other hand, cannot be used in this way: it is not intended for failover scenarios and does not support the various standby recovery and failover options.
Note: Configuration and Backup must be understood prior to starting duplication using RMAN.
Steps for duplicating database using RMAN.
01. From the command prompt create a password file for the AUX database.
orapwd file=d:\oracle\ora92\database\PWDaux.ora password=oracle entries=5
2. In SQL*Plus connect as SYS create a parameter file from the spfile of the target database for the AUX database.
connect sys/oracle@orcl.world as sysdba
create pfile='d:\oracle\ora92\database\INITaux.ORA' from spfile;
3. Edit the file INITaux.ORA, found in the D:\ORALCE\ORA92\DATABASE;
change all occurrences of \orcl\ to \auxorcl\.
Next change db_name and instance_name to aux. Then add the following two lines to the parameter file:
db_file_name_convert=('D:\ORACLE\ORADATA\ORCL','D:\ORACLE\ORADATA\AUXORCL')
log_file_name_convert=('D:\ORACLE\ORADATA\ORCL','D:\ORACLE\ORADATA\AUXORCL'
4. Run script cre_dir from the command prompt to create the subdirectories for the duplicate database.
cre_dir
or create directories (bdump,cdump,udump,archive,adump) manually as path specified in the pfile.
5. Add an entry in the tnsnames.ora and listener.ora for the AUX database. Use Net Manager.
Start > Programs > Oracle - OraHome92 > Configuration and Migration Tools > Net Manager
TNSNAMES.ORA
Select Oracle Net Configuration > Local > Service Name. Then from the menu select Edit > Create. Use the following values to create the entry.
Net Service Name: aux.world
Protocol: TCP/IP (Internet Protocol)
Host:
Port: 1521
Service Name: aux.world
Since the database has not been created you can not successfully test the configuration.
LISTENER.ORA
Select Oracle Net Configuration > Local > Listeners > LISTENER. From the drop down menu on the right and side change Listener Location to Database Services. Click Add Database at the bottom. Use the following values:
Global Database Name: aux.net
Oracle Home: D:\oracle\ora92
SID: aux
Select from the top menu: File > Save Network Configuration followed by File > Exit.
6. Shutdown and startup the listener.ora. From the command prompt type the follow:
lsnrctl stop
lsnrctl start
7. Create a service for the database. From the command prompt type:
oradim -new -sid AUX -startmode m –pfile d:\oracle\ora92\database\INITaux.ORA
8. From RMAN, type the following commands.
RMAN
CONNECT AUXILIARY SYS/ORACLE@AUX.WORLD
CONNECT TARGET SYS/ORACLE@ORCL.WORLD
STARTUP CLONE NOMOUNT FORCE;
DUPLICATE TARGET DATABASE TO AUX;
9.Connect to the new database
connect hr/hr@aux.world
SELECT first_name
FROM employees;
Duplicate Database/Cloning Database using RMAN
You can use the RMAN DUPLICATE command create a duplicate database from target database backups while still retaining the original target database. A duplicate database is a copy of the target database that you can run independently for a variety of purposes. For example, you can use it to:
Test backup and recovery procedures
Export data such as a table that was inadvertently dropped from the production database, and then importing it back into the production database.
A duplicate database is distinct from a standby database, although both types of databases are created with the DUPLICATE command. A standby database is a copy of the primary database that you can update continually or periodically by using archived logs from the primary database. If the primary database is damaged or destroyed, then you can perform failover to the standby database and effectively transform it into the new primary database. A duplicate database, on the other hand, cannot be used in this way: it is not intended for failover scenarios and does not support the various standby recovery and failover options.
Note: Configuration and Backup must be understood prior to starting duplication using RMAN.
Steps for duplicating database using RMAN.
01. From the command prompt create a password file for the AUX database.
orapwd file=d:\oracle\ora92\database\PWDaux.ora password=oracle entries=5
2. In SQL*Plus connect as SYS create a parameter file from the spfile of the target database for the AUX database.
connect sys/oracle@orcl.world as sysdba
create pfile='d:\oracle\ora92\database\INITaux.ORA' from spfile;
3. Edit the file INITaux.ORA, found in the D:\ORALCE\ORA92\DATABASE;
change all occurrences of \orcl\ to \auxorcl\.
Next change db_name and instance_name to aux. Then add the following two lines to the parameter file:
db_file_name_convert=('D:\ORACLE\ORADATA\ORCL','D:\ORACLE\ORADATA\AUXORCL')
log_file_name_convert=('D:\ORACLE\ORADATA\ORCL','D:\ORACLE\ORADATA\AUXORCL'
4. Run script cre_dir from the command prompt to create the subdirectories for the duplicate database.
cre_dir
or create directories (bdump,cdump,udump,archive,adump) manually as path specified in the pfile.
5. Add an entry in the tnsnames.ora and listener.ora for the AUX database. Use Net Manager.
Start > Programs > Oracle - OraHome92 > Configuration and Migration Tools > Net Manager
TNSNAMES.ORA
Select Oracle Net Configuration > Local > Service Name. Then from the menu select Edit > Create. Use the following values to create the entry.
Net Service Name: aux.world
Protocol: TCP/IP (Internet Protocol)
Host:
Port: 1521
Service Name: aux.world
Since the database has not been created you can not successfully test the configuration.
LISTENER.ORA
Select Oracle Net Configuration > Local > Listeners > LISTENER. From the drop down menu on the right and side change Listener Location to Database Services. Click Add Database at the bottom. Use the following values:
Global Database Name: aux.net
Oracle Home: D:\oracle\ora92
SID: aux
Select from the top menu: File > Save Network Configuration followed by File > Exit.
6. Shutdown and startup the listener.ora. From the command prompt type the follow:
lsnrctl stop
lsnrctl start
7. Create a service for the database. From the command prompt type:
oradim -new -sid AUX -startmode m –pfile d:\oracle\ora92\database\INITaux.ORA
8. From RMAN, type the following commands.
RMAN
CONNECT AUXILIARY SYS/ORACLE@AUX.WORLD
CONNECT TARGET SYS/ORACLE@ORCL.WORLD
STARTUP CLONE NOMOUNT FORCE;
DUPLICATE TARGET DATABASE TO AUX;
9.Connect to the new database
connect hr/hr@aux.world
SELECT first_name
FROM employees;
Subscribe to:
Posts (Atom)