ORACLE DBA

RMAN Database Restore from ASM (NON-RAC) TO File System (NON-RAC)

2021-11-12T01:08:00.002-08:00

RMAN Database Restore from ASM to File System RMAN Database Restore from ASM (NON-RAC) TO File System (NON-RAC) 0. Environment On Source Server 1. Backup database 2. Create pfile from spfile 3. Push backup files/pfile to target server On Target Server 4. Edit pfile 5. Create Required Folders 6. Add the entry in oratab 7. Startup Nomount 8. Restore controlfile 9. Mount database 10. Catalog backup pieces 11. Restore/recover database 12. Change dbname/DBID uisng NID 13. Modify DB_NAME in init file and start the database 14. Add TNS entry 15. Verify 0. Environment Source: DB: SBPROD, Diskgroup: DATA Node: YesBrac1, DB Version: 11.2.0.3 Target: DB: YESBTEST, File System: /u01 Node: YesBrac2, DB Version: 11.2.0.3 On Source SERVER ================== Step 1: Backup database w148p – source [oracle@YesBrac1 ~]$ cat open0.rcv run { allocate channel t1 type disk; backup incremental level 0 database format '/u01/share/backup/database_%d_%u_%s'; release channel t1; } sql 'alter system archive log current'; run { allocate channel a1 type disk; backup archivelog all format '/u01/share/backup/arch_%d_%u_%s'; release channel a1; } run { allocate channel c1 type disk; backup current controlfile format '/u01/share/backup/Control_%d_%u_%s'; release channel c1; } exit [oracle@YesBrac1 ~]$ [oracle@YesBrac1 ~]$ . oraenv ORACLE_SID = [SBPROD] ? [oracle@YesBrac1 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Thu Oct 6 17:11:07 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: SBPROD (DBID=3870074951) RMAN> @open0.rcv RMAN> run { 2> allocate channel t1 type disk; 3> backup incremental level 0 database format '/u01/share/backup/database_%d_%u_%s'; 4> release channel t1; 5> } using target database control file instead of recovery catalog allocated channel: t1 channel t1: SID=36 device type=DISK Starting backup at 06-OCT-16 channel t1: starting incremental level 0 datafile backup set channel t1: specifying datafile(s) in backup set input datafile file number=00001 name=+DATA1/SBPROD/datafile/system.258.924538565 input datafile file number=00002 name=+DATA1/SBPROD/datafile/sysaux.257.924538565 input datafile file number=00005 name=+DATA1/SBPROD/datafile/example.256.924538565 input datafile file number=00003 name=+DATA1/SBPROD/datafile/undotbs1.260.924538565 input datafile file number=00004 name=+DATA1/SBPROD/datafile/users.263.924538565 channel t1: starting piece 1 at 06-OCT-16 channel t1: finished piece 1 at 06-OCT-16 piece handle=/u01/share/backup/database_SBPROD_0drhmou2_13 tag=TAG20161006T171130 comment=NONE channel t1: backup set complete, elapsed time: 00:00:25 channel t1: starting incremental level 0 datafile backup set channel t1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel t1: starting piece 1 at 06-OCT-16 channel t1: finished piece 1 at 06-OCT-16 piece handle=/u01/share/backup/database_SBPROD_0erhmour_14 tag=TAG20161006T171130 comment=NONE channel t1: backup set complete, elapsed time: 00:00:01 Finished backup at 06-OCT-16 released channel: t1 RMAN> sql 'alter system archive log current'; sql statement: alter system archive log current RMAN> run { 2> allocate channel a1 type disk; 3> backup archivelog all format '/u01/share/backup/arch_%d_%u_%s'; 4> release channel a1; 5> } allocated channel: a1 channel a1: SID=36 device type=DISK Starting backup at 06-OCT-16 current log archived channel a1: starting archived log backup set channel a1: specifying archived log(s) in backup set input archived log thread=1 sequence=1 RECID=1 STAMP=924541640 input archived log thread=1 sequence=2 RECID=2 STAMP=924541917 input archived log thread=1 sequence=3 RECID=3 STAMP=924541917 channel a1: starting piece 1 at 06-OCT-16 channel a1: finished piece 1 at 06-OCT-16 piece handle=/u01/share/backup/arch_SBPROD_0frhmouu_15 tag=TAG20161006T171157 comment=NONE channel a1: backup set complete, elapsed time: 00:00:01 Finished backup at 06-OCT-16 released channel: a1 RMAN> run { 2> allocate channel c1 type disk; 3> backup current controlfile format '/u01/share/backup/Control_%d_%u_%s'; 4> release channel c1; 5> } allocated channel: c1 channel c1: SID=36 device type=DISK Starting backup at 06-OCT-16 channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set including current control file in backup set channel c1: starting piece 1 at 06-OCT-16 channel c1: finished piece 1 at 06-OCT-16 piece handle=/u01/share/backup/Control_SBPROD_0grhmouv_16 tag=TAG20161006T171159 comment=NONE channel c1: backup set complete, elapsed time: 00:00:01 Finished backup at 06-OCT-16 released channel: c1 RMAN> exit Recovery Manager complete. [oracle@YesBrac1 ~]$ Step 2: Create pfile from spfile SQL> select name, open_mode from v$database; NAME OPEN_MODE --------- -------------------- SBPROD READ WRITE SQL> create pfile='/home/oracle/initYESBTEST.ora' from spfile; File created. SQL> Step 3: Push backup files/pfile to target server [oracle@YesBrac1 ~]$ cd /u01/share/backup/ [oracle@YesBrac1 backup]$ scp * oracle@YesBrac2:/u01/share/backup/SBPROD arch_SBPROD_0frhmouu_15 100% 2002KB 2.0MB/s 00:00 Control_SBPROD_0grhmouv_16 100% 9568KB 9.3MB/s 00:00 database_SBPROD_0drhmou2_13 100% 1082MB 40.1MB/s 00:27 database_SBPROD_0erhmour_14 100% 9600KB 9.4MB/s 00:00 [oracle@YesBrac1 backup]$ [oracle@YesBrac1 ~]$ scp initYESBTEST.ora oracle@YesBrac2:/home/oracle initYESBTEST.ora 100% 877 0.9KB/s 00:00 [oracle@YesBrac1 ~]$ On Target Server ================= Step 4: Edit pfile initYESBTEST.ora [oracle@YesBrac2 ~]$ cat initYESBTEST.ora YESBTEST.__db_cache_size=419430400 YESBTEST.__java_pool_size=16777216 YESBTEST.__large_pool_size=16777216 YESBTEST.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment YESBTEST.__pga_aggregate_target=520093696 YESBTEST.__sga_target=754974720 YESBTEST.__shared_io_pool_size=0 YESBTEST.__shared_pool_size=251658240 YESBTEST.__streams_pool_size=33554432 *.audit_file_dest='/u01/app/oracle/admin/YESBTEST/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/YESBTEST/control01.ctl','/u01/app/oracle/oradata/YESBTEST/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='SBPROD' <--- -i="" -p="" .="" 11.2.0.3.0="" 17:43:53="" 1982="" 2011="" 2016="" 5:="" 6:="" 6="" 7:="" add="" admin="" adump="" all="" an="" app="" as="" base="" c="" cat="" connected="" copyright="" create="" db_1:n="" diagnostic_dest="/u01/app/oracle" dispatchers="(PROTOCOL=TCP) (SERVICE=YESBTESTXDB)" entry="" esbrac2="" etc="" folders="" grep="" idle="" in="" instance.="" log_archive_dest_1="LOCATION=+FRA" log_archive_format="%t_%s_%r.dbf" lus:="" memory_target="1262485504" mkdir="" nomount="" oct="" on="" open_cursors="300" oracle.="" oracle="" oracle_sid="[cat]" oradata="" oraenv="" oratab="" processes="150" product="" production="" release="" remains="" remote_login_passwordfile="EXCLUSIVE" required="" reserved.="" rights="" sql="" sqlplus="" startup="" step="" sysdba="" the="" thu="" to="" u01="" unchanged="" undo_tablespace="UNDOTBS1" value="" with="" yesbtest:="" yesbtest=""> startup nomount pfile='/home/oracle/initYESBTEST.ora'; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2227984 bytes Variable Size 838861040 bytes Database Buffers 419430400 bytes Redo Buffers 8847360 bytes SQL> SQL> create spfile from pfile='/home/oracle/initYESBTEST.ora'; File created. SQL> shut immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup nomount; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2227984 bytes Variable Size 838861040 bytes Database Buffers 419430400 bytes Redo Buffers 8847360 bytes SQL> SQL> show parameter pfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfileYESBTEST.ora SQL> [oracle@YesBrac2 ~]$ ps -ef | grep pmon oracle 3687 1 0 13:39 ? 00:00:01 asm_pmon_+ASM oracle 3874 1 0 17:45 ? 00:00:00 ora_pmon_YESBTEST oracle 4006 32731 0 17:46 pts/1 00:00:00 grep pmon [oracle@YesBrac2 ~]$ Step 8: Restore controlfile [oracle@YesBrac2 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Thu Oct 6 17:46:55 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: SBPROD (not mounted) RMAN> restore controlfile from '/u01/share/backup/SBPROD/Control_SBPROD_0grhmouv_16'; Starting restore at 06-OCT-16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=24 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/YESBTEST/control01.ctl output file name=/u01/app/oracle/oradata/YESBTEST/control02.ctl Finished restore at 06-OCT-16 RMAN> Step 9: Mount database RMAN> sql 'alter database mount'; sql statement: alter database mount released channel: ORA_DISK_1 RMAN> SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- SBPROD MOUNTED SQL> Step 10: Catalog the backup pieces RMAN> catalog start with '/u01/share/backup/SBPROD/'; searching for all files that match the pattern /u01/share/backup/SBPROD/ List of Files Unknown to the Database ===================================== File Name: /u01/share/backup/SBPROD/database_SBPROD_0drhmou2_13 File Name: /u01/share/backup/SBPROD/arch_SBPROD_0frhmouu_15 File Name: /u01/share/backup/SBPROD/database_SBPROD_0erhmour_14 File Name: /u01/share/backup/SBPROD/Control_SBPROD_0grhmouv_16 Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u01/share/backup/SBPROD/database_SBPROD_0drhmou2_13 File Name: /u01/share/backup/SBPROD/arch_SBPROD_0frhmouu_15 File Name: /u01/share/backup/SBPROD/database_SBPROD_0erhmour_14 File Name: /u01/share/backup/SBPROD/Control_SBPROD_0grhmouv_16 RMAN> Step 11: Restore database RMAN> list backup of archivelog all; List of Backup Sets =================== BS Key Size ------- ---------- 3 1.95M List of Archived Logs in backup set 3 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 1 1176811 06-OCT-16 1199528 06-OCT-16 .. 1 3 1200733 06-OCT-16 1200748 06-OCT-16 <-- 11g="" 1="" 3="" 4="" a="" all="" allocate="" and="" app="" b="" cat="" channel="" d1="" d2="" database="" datafile="" datafiles="" device="" disk="" do="" fewture="" file="" for="" group_1.262.924539537="" group_2.261.924539539="" group_3.264.924539539="" logs="" names="" newname="" of="" online="" onlinelog="" oracle="" oradata="" prevent="" recover="" recovery="" redo="" rename="" restore="" rman="" rman_recovery.rcv="" run="" sequence="" set="" sql="" switch="" tape="" tempfile="" the="" to="" type="" u01="" until=""> @rman_recovery.rcv RMAN> run { 2> # allocate a channel to the tape device 3> ALLOCATE CHANNEL d1 DEVICE TYPE disk; 4> ALLOCATE CHANNEL d2 DEVICE TYPE disk; 5> # rename the datafiles and online redo logs 6> SET NEWNAME FOR DATABASE TO '/u01/app/oracle/oradata/YESBTEST/%b'; 7> SET NEWNAME FOR tempfile 1 TO '/u01/app/oracle/oradata/YESBTEST/%b'; 8> SQL "ALTER DATABASE RENAME FILE ''+DATA1/SBPROD/onlinelog/group_3.264.924539539'' 9> to ''/u01/app/oracle/oradata/YESBTEST/group_3.264.924539539''" ; 10> SQL "ALTER DATABASE RENAME FILE ''+DATA1/SBPROD/onlinelog/group_2.261.924539539'' 11> to ''/u01/app/oracle/oradata/YESBTEST/group_2.261.924539539''" ; 12> SQL "ALTER DATABASE RENAME FILE ''+DATA1/SBPROD/onlinelog/group_1.262.924539537'' 13> to ''/u01/app/oracle/oradata/YESBTEST/group_1.262.924539537''" ; 14> # Do a SET UNTIL to prevent recovery of the online logs 15> SET UNTIL SEQUENCE 4; 16> # restore the database and switch the datafile names 17> restore database; 18> switch datafile all; 19> switch tempfile all; 20> # recover the database 21> RECOVER DATABASE; 22> } allocated channel: d1 channel d1: SID=26 device type=DISK allocated channel: d2 channel d2: SID=24 device type=DISK executing command: SET NEWNAME executing command: SET NEWNAME sql statement: ALTER DATABASE RENAME FILE ''+DATA1/SBPROD/onlinelog/group_3.264.924539539''to ''/u01/app/oracle/oradata/YESBTEST/group_3.264.924539539'' sql statement: ALTER DATABASE RENAME FILE ''+DATA1/SBPROD/onlinelog/group_2.261.924539539''to ''/u01/app/oracle/oradata/YESBTEST/group_2.261.924539539'' sql statement: ALTER DATABASE RENAME FILE ''+DATA1/SBPROD/onlinelog/group_1.262.924539537''to ''/u01/app/oracle/oradata/YESBTEST/group_1.262.924539537'' executing command: SET until clause Starting restore at 06-OCT-16 channel d1: starting datafile backup set restore channel d1: specifying datafile(s) to restore from backup set channel d1: restoring datafile 00001 to /u01/app/oracle/oradata/YESBTEST/system.258.924538565 channel d1: restoring datafile 00002 to /u01/app/oracle/oradata/YESBTEST/sysaux.257.924538565 channel d1: restoring datafile 00003 to /u01/app/oracle/oradata/YESBTEST/undotbs1.260.924538565 channel d1: restoring datafile 00004 to /u01/app/oracle/oradata/YESBTEST/users.263.924538565 channel d1: restoring datafile 00005 to /u01/app/oracle/oradata/YESBTEST/example.256.924538565 channel d1: reading from backup piece /u01/share/backup/database_SBPROD_0drhmou2_13 channel d1: errors found reading piece handle=/u01/share/backup/database_SBPROD_0drhmou2_13 channel d1: failover to piece handle=/u01/share/backup/SBPROD/database_SBPROD_0drhmou2_13 tag=TAG20161006T171130 channel d1: restored backup piece 1 channel d1: restore complete, elapsed time: 00:01:25 Finished restore at 06-OCT-16 datafile 1 switched to datafile copy input datafile copy RECID=6 STAMP=924545382 file name=/u01/app/oracle/oradata/YESBTEST/system.258.924538565 datafile 2 switched to datafile copy input datafile copy RECID=7 STAMP=924545382 file name=/u01/app/oracle/oradata/YESBTEST/sysaux.257.924538565 datafile 3 switched to datafile copy input datafile copy RECID=8 STAMP=924545382 file name=/u01/app/oracle/oradata/YESBTEST/undotbs1.260.924538565 datafile 4 switched to datafile copy input datafile copy RECID=9 STAMP=924545382 file name=/u01/app/oracle/oradata/YESBTEST/users.263.924538565 datafile 5 switched to datafile copy input datafile copy RECID=10 STAMP=924545382 file name=/u01/app/oracle/oradata/YESBTEST/example.256.924538565 renamed tempfile 1 to /u01/app/oracle/oradata/YESBTEST/temp.291.924538685 in control file Starting recover at 06-OCT-16 starting media recovery channel d1: starting archived log restore to default destination channel d1: restoring archived log archived log thread=1 sequence=2 channel d1: restoring archived log archived log thread=1 sequence=3 channel d1: reading from backup piece /u01/share/backup/arch_SBPROD_0frhmouu_15 channel d1: errors found reading piece handle=/u01/share/backup/arch_SBPROD_0frhmouu_15 channel d1: failover to piece handle=/u01/share/backup/SBPROD/arch_SBPROD_0frhmouu_15 tag=TAG20161006T171157 channel d1: restored backup piece 1 channel d1: restore complete, elapsed time: 00:00:01 archived log file name=+FRA/SBPROD/archivelog/2016_10_06/thread_1_seq_2.450.924545383 thread=1 sequence=2 archived log file name=+FRA/SBPROD/archivelog/2016_10_06/thread_1_seq_3.451.924545383 thread=1 sequence=3 media recovery complete, elapsed time: 00:00:00 Finished recover at 06-OCT-16 released channel: d1 released channel: d2 RMAN> **end-of-file** RMAN> sql 'alter database open'; sql statement: alter database open RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of sql command on default channel at 10/06/2016 18:10:27 RMAN-11003: failure during parse/execution of SQL statement: alter database open ORA-01589: must use RESETLOGS or NORESETLOGS option for database open RMAN> sql 'alter database open RESETLOGS'; sql statement: alter database open RESETLOGS RMAN> exit Recovery Manager complete. [oracle@YesBrac2 ~]$ SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/YESBTEST/group_3.264.924539539 /u01/app/oracle/oradata/YESBTEST/group_2.261.924539539 /u01/app/oracle/oradata/YESBTEST/group_1.262.924539537 SQL> SQL> select name, open_mode, db_unique_name, dbid from v$database; NAME OPEN_MODE DB_UNIQUE_NAME DBID --------- -------------------- ------------------------------ ---------- SBPROD READ WRITE SBPROD 3870074951 SQL> Step 12: Change Database name and DB ID SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2227984 bytes Variable Size 838861040 bytes Database Buffers 419430400 bytes Redo Buffers 8847360 bytes Database mounted. SQL> [oracle@YesBrac2 ~]$ . oraenv ORACLE_SID = [YESBTEST] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@YesBrac2 ~]$ nid target=sys dbname=YESBTEST DBNEWID: Release 11.2.0.3.0 - Production on Thu Oct 6 18:16:38 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: Connected to database SBPROD (DBID=3870074951) Connected to server version 11.2.0 Control Files in database: /u01/app/oracle/oradata/YESBTEST/control01.ctl /u01/app/oracle/oradata/YESBTEST/control02.ctl Change database ID and database name SBPROD to YESBTEST? (Y/[N]) => Y Proceeding with operation Changing database ID from 3870074951 to 2222709898 Changing database name from SBPROD to YESBTEST Control File /u01/app/oracle/oradata/YESBTEST/control01.ctl - modified Control File /u01/app/oracle/oradata/YESBTEST/control02.ctl - modified Datafile /u01/app/oracle/oradata/YESBTEST/system.258.92453856 - dbid changed, wrote new name Datafile /u01/app/oracle/oradata/YESBTEST/sysaux.257.92453856 - dbid changed, wrote new name Datafile /u01/app/oracle/oradata/YESBTEST/undotbs1.260.92453856 - dbid changed, wrote new name Datafile /u01/app/oracle/oradata/YESBTEST/users.263.92453856 - dbid changed, wrote new name Datafile /u01/app/oracle/oradata/YESBTEST/example.256.92453856 - dbid changed, wrote new name Datafile /u01/app/oracle/oradata/YESBTEST/temp.291.92453868 - dbid changed, wrote new name Control File /u01/app/oracle/oradata/YESBTEST/control01.ctl - dbid changed, wrote new name Control File /u01/app/oracle/oradata/YESBTEST/control02.ctl - dbid changed, wrote new name Instance shut down Database name changed to YESBTEST. Modify parameter file and generate a new password file before restarting. Database ID for database YESBTEST changed to 2222709898. All previous backups and archived redo logs for this database are unusable. Database has been shutdown, open database with RESETLOGS option. Succesfully changed database name and ID. DBNEWID - Completed succesfully. [oracle@YesBrac2 ~]$ Please note database will go down automatically Step 13: Change DB_NAME and start the database [oracle@YesBrac2 ~]$ cat initYESBTEST.ora YESBTEST.__db_cache_size=419430400 YESBTEST.__java_pool_size=16777216 YESBTEST.__large_pool_size=16777216 YESBTEST.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment YESBTEST.__pga_aggregate_target=520093696 YESBTEST.__sga_target=754974720 YESBTEST.__shared_io_pool_size=0 YESBTEST.__shared_pool_size=251658240 YESBTEST.__streams_pool_size=33554432 *.audit_file_dest='/u01/app/oracle/admin/YESBTEST/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/YESBTEST/control01.ctl','/u01/app/oracle/oradata/YESBTEST/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='YESBTEST' <--- diagnostic_dest="/u01/app/oracle" dispatchers="(PROTOCOL=TCP) (SERVICE=YESBTESTXDB)" esbrac2="" log_archive_dest_1="LOCATION=+FRA" log_archive_format="%t_%s_%r.dbf" memory_target="1262485504" open_cursors="300" oracle="" processes="150" remote_login_passwordfile="EXCLUSIVE" sql="" undo_tablespace="UNDOTBS1"> startup nomount pfile='/home/oracle/initYESBTEST.ora'; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2227984 bytes Variable Size 838861040 bytes Database Buffers 419430400 bytes Redo Buffers 8847360 bytes SQL> SQL> create spfile from pfile='/home/oracle/initYESBTEST.ora'; File created. SQL> shut immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> SQL> startup; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2227984 bytes Variable Size 838861040 bytes Database Buffers 419430400 bytes Redo Buffers 8847360 bytes Database mounted. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> alter database open resetlogs; Database altered. SQL> select name, open_mode, db_unique_name, dbid from v$database; NAME OPEN_MODE DB_UNIQUE_NAME DBID --------- -------------------- ------------------------------ ---------- YESBTEST READ WRITE YESBTEST 2222709898 SQL> SELECT NAME FROM V$TEMPFILE; NAME ------------------------------------------------------------------------- /u01/app/oracle/oradata/YESBTEST/temp.291.924538685 SQL> Step 14: Add TNS entry YESBTEST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = YESBTEST) ) ) [oracle@YesBrac2 admin]$ tnsping YESBTEST TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 06-OCT-2016 18:21:42 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = YESBTEST))) OK (60 msec) [oracle@YesBrac2 admin]$ Step 15: Verify [oracle@YesBrac2 ~]$ ps -ef | grep tns root 9 2 0 13:36 ? 00:00:00 [netns] oracle 3421 1 0 13:38 ? 00:00:00 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit oracle 5018 3554 0 18:22 pts/2 00:00:00 grep tns [oracle@YesBrac2 ~]$ lsnrctl status LISTENER | grep -i YESBTEST Service "YESBTEST" has 1 instance(s). Instance "YESBTEST", status READY, has 1 handler(s) for this service... Service "YESBTESTXDB" has 1 instance(s). Instance "YESBTEST", status READY, has 1 handler(s) for this service... [oracle@YesBrac2 ~]$ [oracle@YesBrac2 ~]$ sqlplus scott/tiger@YESBTEST SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 6 18:22:52 2016 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application YESBTESTing options SQL> Please create password file in $ORACLE_HOME/dbs/ for this database.

ASMCA utility Features in 19C

2021-05-06T19:26:00.000-07:00

Welcome to ASM Configuration Assistant Oracle ASM Configuration Assistant (ASMCA) supports installing and configuring Oracle ASM instances, Oracle ASM disk groups, Oracle ASM Dynamic Volume Manager (Oracle ADVM) volumes and Oracle Automatic Storage Management Cluster File System (Oracle ACFS) file systems. In addition, you can use the ASMCA command line-line interface. Oracle ASM Configuration Assistant enables you to: Manage disk groups Create, configure or drop an Oracle ASM disk group Add disks to and drop disks from the disk group Edit the disk group attributes Manage templates for the disk group Create an Oracle ACFS file system on the disk group for database use Mount or dismount the disk group Add, list or drop quota groups Add, move or drop file groups Manage Oracle ADVM volumes Create or configure an Oracle ADVM volume Enable or disable Oracle ADVM volume View status details Resize or delete volumes Manage Oracle ACFS file systems Create or configure an Oracle ACFS file system View status details Mount or dismount the file system Resize the file system Delete the file system Display or execute the security commands Create or delete snapshots Hint: To perform an operation on an item, right click on the item.

Generic - Exploration of ERP Modules in Oracle E-Business Suite

2021-04-24T18:35:00.001-07:00

Exploration of ERP Modules in Oracle E-Business Suite ERP stands for enterprise resource planning. It refers to the systems and software packages used by organizations to manage day-to-day business. Oracle E-Business Suite is one of the most reputed ERP product which is currently available in the market – developed by Oracle. In the world of enterprise resource planning, very few software products have really made to the top ranks when it comes to worldwide implementations. Based on market share – SAP, Microsoft Dynamics are the close vendors who try to get the ERP share in almost all the regions. What is Oracle E-Business Suite? Oracle’s E-Business Suite (also known as Applications/Apps or EB-Suite/EBS) consists of a collection of enterprise resource planning (ERP), customer relationship management (CRM), and supply-chain management (SCM) computer applications either developed or acquired by Oracle. Top Vendors in the market Based on market share – SAP, Oracle, and Microsoft Dynamics are the key vendors who are the leaders in major international markets like North America and Europe. Many industry experts / Chief executive officers who wish to implement new ERP software in Fortune 500 companies will most probably end up considering industry’s two biggest leaders: SAP or Oracle. While both ERP providers are clear market share dominators and have very well-established product lines, the strengths, weaknesses, risks and product roadmaps of these two vendors couldn’t be more different. Either of the product is likely to get the job done! so the question becomes: Which will help you get the job done for your organization?. Marketing Vs Services Generally, we have manufacturing and services industries which have two diverse requirements. SAP has a strong grip in the manufacturing sector and European markets for a long while. When it comes to Oracle, it has a strong hold on services industry. The market has evolved a lot and in the same way, even the software has changed drastically. Thanks to their upgrades and version changes. If you look at Oracle ERP suite, it started off with Oracle D2K, Oracle 11i, then Oracle E-Business Suite and currently Fusion software. Why Oracle? Oracle Corporation (Oracle) provides a comprehensive range of IT solutions. Wide product portfolio and cloud business and strategic acquisitions are likely to offer growth opportunities to the company. Oracle R12 (a version name) has a history of improving global decision making by supporting various types of Reporting/Analysis. Also, transaction processing happens in Financials, Supply Chain, Projects, PLM, Sales & Marketing, HR, Services. What do you mean by Oracle Apps? Oracle Applications means software that helps you run your enterprise businesses such as Accounting, Humans Resource, or Financials. However, Oracle Applications Server is the technology that, mostly installs on the server, integrates with others software’s to work together. It can be called Middleware. Why so Many Modules? Every business enterprise has their own requirements and needs. A high-tech consulting company is not going to have the same procurement needs as that of a high-tech manufacturing firm. That is why EBS has a lot of features which are both manual and automated. Since, no solutions fit all problems, in the same way – no particular product can help you all. There is a practice of mix and match of software vendors bundled together to provide a most suitable customized product for your organization. In this regard, it is to be noted that – each application is licensed separately so companies can select the combination that is suitable for their business processes. Let us go through some of the major Modules – Customer Relationship Management The Oracle CRM (Customer Relationship Management) is a set of applications that gives you information-driven sales, service, and marketing. Oracle CRM is built on an open, standards-based architecture that streamlines business processes, improves data quality, and allows all your key divisions to draw from the same source of data. With Oracle CRM, your company owns the single best tool for customer success—accurate information. Services Oracle’s E-Business Suite family of Service Applications provides true information-driven customer service. It enables an organization to meet and exceed customer expectations by empowering them with information that is consistent, accurate, and actionable. Financial The Oracle Financials application includes General Ledger, Cash Management, Payables, Receivables, Fixed Assets, Treasury, Property Management, Financial Analyser and a self-service expenses function. Oracle Human Resource Management The Oracle Human Resource Management System (HMRS) module helps companies manage the entire recruitment process and provides a real-time view of all HR activities such as recruitment, training, benefits, and payroll. The HRMS suite fully integrates with all E-Business Suite applications and offers users an analytics package that allows for easy extraction of HR data. Oracle Project Oracle Projects Portfolio of applications supports the full lifecycle of project and portfolio management. It provides a single, accurate view of all project-related activities. It allows users to select the best portfolio of initiatives, execute projects in adherence with methodologies, assign the right global resources, proactively streamline project delivery, and track profitability via accurate budgeting, forecasting, and Billings/charge-backs. Supply Chain Management The Oracle Supply Chain applications allow organizations to predict market requirements, innovate in response to volatile market conditions, and align operations across global networks. There are several industry-specific solutions available that incorporate demand management, sales, and operations planning, transportation management etc. Procurement Oracle Advanced Procurement is an integrated suite of applications that dramatically cuts all supply management costs. The solution helps you to reduce spending on goods and services, streamline procure-to-pay processes, and drive policy compliance. Value Chain and Planning Oracle’s Value Chain Planning solution enables companies to become more information-driven with best-in-class supply chain planning applications built around a core of optimization, sales and operations planning, and performance management. Oracle Transportation Management Transportation management (TMS) provides transportation planning and execution capabilities to shippers and third party logistics providers. It integrates and streamlines transportation planning, execution, and freight payment. The TMS function delivers functionality for all modes of transportation, from full truckload to complex air, ocean, and rail shipments. The benefits of the TMS function include reduced transportation costs, improved customer service, and greater asset utilization. Oracle Warehouse Management Systems Oracle’s Warehouse Management System allows coordinated movement of goods and information throughout the extended distribution process. The module provides business processes that can deliver efficient utilization of employees, equipment, and space in the distribution process. Benefits include an acceleration of the flow of products through the supply chain while reducing lead times and releasing working capital, real-time inventory management, cross-docking, pick-by-line, advanced ship notices (ASN), inbound planning and yard management. Configure, Don’t Customize Highly configurable global system lets you tailor the system without programming. List of some of the key applications are: Oracle HRMS Oracle Payroll Oracle CRM Oracle Financials Oracle Mobile Supply Chain Management Oracle Order Management Oracle Procurement Oracle Project Portfolio Management Oracle Quotes Oracle Transportation Management Oracle Enterprise Asset Management Product Life Cycle Management Manufacturing Strengths of Oracle compared to its Competitors Oracle is very good in database and backend. It does have a good market in middle-east and a decent presence in North American markets. Due to its reputation in financials module, it has won several accolades from its clientele which has given scope for newer modules. How has Oracle been building its Empire? The company has been in the buying spree in the last decade due to which many related technologies are into its mold. For example, PeopleSoft is one product which was good for a Human resource which is now a part of Oracle. In the same way, Siebel CRM is a part of Oracle. In this way, Oracle Corporation has got a sizeable market presence with an extremely good acquisition strategy. This has given good dividends for their existing clients and for prospective customers who are looking to buy Oracle suite of products.

ORA2PG Demo

2021-04-20T04:46:00.004-07:00

-------------------------At Oracle--------------------------------------------------- PREREQUEST: 1.In a database all schema size: set linesize 150 set pagesize 5000 col owner for a15 col segment_name for a30 col segment_type for a20 col TABLESPACE_NAME for a30 clear breaks clear computes compute sum of SIZE_IN_GB on report break on report select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments group by owner order by owner; 2.issuing below script you will extract the size & path of datafiles along with tablespace name,allocated space in GB, used space in GB & free space in GB. set linesize 200 set pagesize 2000 COLUMN tablespace_name format a10 COLUMN file_name format a45 COLUMN free% format a7 SELECT df.tablespace_name,SUBSTR (df.file_name, 1, 60) file_name, df.bytes/1024/1024/1024 allocated_GB, round(((df.bytes/1024 /1024/1024) – NVL(SUM(dfs.bytes)/1024/1024 /1024, 0)),1) used_GB, round(NVL(SUM (dfs.bytes)/1024/1024/1024, 0),2) free_space_GB FROM dba_data_files df, dba_free_space dfs WHERE df.FILE_ID = dfs.file_id(+) GROUP BY df.tablespace_name, dfs.file_id, df.file_id , df.bytes, df.file_NAME ORDER BY df.tablespace_name; From SQL> conn sys/oracle@testdb as sysdba Connected. SQL> @tsutil TsUtil_TESTDB_200924212645 !!!!! Table Space Utilization !!!!! 24/09/2020 21:26:45 Tablespace Name AUT Total Space Used apace Free Space Max Size Free % ------------------------------ --- ----------- ---------- ---------- ---------- ---------- SYSAUX YES 570 535 35 32767.9844 98.37 SYSTEM YES 900 890.5 9.5 32767.9844 97.28 UNDOTBS1 YES 270 54.125 215.875 32767.9844 99.83 USERS YES 1355 1289.6875 65.3125 32767.9844 96.06 !!!!! END !!!!! SQL> @datafiles DataFiles_TESTDB_200924212654 PL/SQL procedure successfully completed. 24/09/2020 21:26:54 TABLESPACE_NAME FILE_NAME USER_BYTES_GB BYTES_GB MAX_GB AUT STATUS ONLINE_ ------------------------------ ---------------------------------------------------------------------- ------------- -------- -------- --- --------- ------- SYSAUX /u01/oradata/TESTDB/sysaux01.dbf .56 .56 32.00 YES AVAILABLE ONLINE SYSTEM /u01/oradata/TESTDB/system01.dbf .88 .88 32.00 YES AVAILABLE SYSTEM UNDOTBS1 /u01/oradata/TESTDB/undotbs01.dbf .26 .26 32.00 YES AVAILABLE ONLINE USERS /u01/oradata/TESTDB/users01.dbf 1.32 1.32 32.00 YES AVAILABLE ONLINE !!!!! END !!!!! POSTREQUEST: ------------------------- AT PostgreSQL----------------------------------------------- 1.CHECK PostgreSQL all schema Size But size will be varried compare to destination, event though check the schema's table count also: Edit: just noticed the workaround with summing up all tables to get the database size is not necessary: From AT POSTGRES SIDE PRECHECKS: SELECT schema_name, pg_size_pretty(sum(table_size)::bigint), (sum(table_size) / pg_database_size(current_database())) * 100 FROM ( SELECT pg_catalog.pg_namespace.nspname as schema_name, pg_relation_size(pg_catalog.pg_class.oid) as table_size FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid ) t GROUP BY schema_name ORDER BY schema_name schema_name | pg_size_pretty | ?column? --------------------+----------------+------------------------- information_schema | 96 kB | 1.24304042650610727800 pg_catalog | 5992 kB | 77.58643995442286261700 pg_toast | 600 kB | 7.76900266566317048900 (3 rows) 2.All tablespace size for refernece only not mandatory: SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) FROM pg_tablespace; postgres=# SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) FROM pg_tablespace; spcname | pg_size_pretty ------------+---------------- pg_default | 45 MB pg_global | 574 kB (2 rows) [root@localhost DBD-Oracle-1.75_2]# perl Makefile.PL Using DBI 1.627 (for perl 5.016003 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/vendor_perl/auto/DBI/ Configuring DBD::Oracle for perl 5.016003 on linux (x86_64-linux-thread-multi) If you encounter any problem, a collection of troubleshooting guides are available under lib/DBD/Oracle/Troubleshooting. 'DBD::Oracle::Troubleshooting' is the general troubleshooting guide, while platform-specific troubleshooting hints live in their labelled sub-document (e.g., Win32 hints are gathered in 'lib/DBD/Oracle/Troubleshooting/Win32.pod'). Installing on a linux, Ver#2.6 Using Oracle in /u01/app/oracle/product/19.0.0/dbhome_1 DEFINE _SQLPLUS_RELEASE = "1903000000" (CHAR) Oracle version 19.3.0.0 (19.3) Found direct-link candidates: libclntsh.so Oracle sysliblist: -ldl -lm -lpthread -lnsl -lirc -limf -lirc -lrt -laio -lresolv -lsvml Found header files in /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/public. Your LD_LIBRARY_PATH env var is set to '/u01/app/oracle/product/19.0.0/dbhome_1/lib' client_version=19.3 DEFINE= -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"19.3.0.0\" -DORA_OCI_102 -DORA_OCI_112 Checking for functioning wait.ph System: perl5.016003 linux x86-021.build.eng.bos.redhat.com 2.6.32-431.4.1.el6.x86_64 #1 smp thu dec 19 10:26:41 est 2013 x86_64 x86_64 x86_64 gnulinux Compiler: gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -fstack-protector -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 Linker: /usr/bin/ld Sysliblist: -ldl -lm -lpthread -lnsl -lirc -limf -lirc -lrt -laio -lresolv -lsvml Linking with -lclntsh. Checking if your kit is complete... Looks good LD_RUN_PATH=/u01/app/oracle/product/19.0.0/dbhome_1/lib:/lib64 Using DBD::Oracle 1.75_2. Using DBD::Oracle 1.75_2. Using DBI 1.627 (for perl 5.016003 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/vendor_perl/auto/DBI/ Writing Makefile for DBD::Oracle Writing MYMETA.yml and MYMETA.json [root@localhost DBD-Oracle-1.75_2]#

# Migration # From Oracle to PostgreSQL By Using ora2pg open source tool

2021-04-12T01:51:00.008-07:00

@ Oracle

PREREQUEST:

1.In a database all schema size:

set linesize 150

set pagesize 5000

col owner for a15

col segment_name for a30

col segment_type for a20

col TABLESPACE_NAME for a30

clear breaks

clear computes

compute sum of SIZE_IN_GB on report

break on report

select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments group by owner order by owner;

2.issuing below script you will extract the size & path of datafiles

along with tablespace name,allocated space in GB, used space in GB & free space in GB.

set linesize 200

set pagesize 2000

COLUMN tablespace_name format a10

COLUMN file_name format a45

COLUMN free% format a7

SELECT df.tablespace_name,SUBSTR (df.file_name, 1, 60) file_name, df.bytes/1024/1024/1024 allocated_GB,

round(((df.bytes/1024 /1024/1024) – NVL(SUM(dfs.bytes)/1024/1024 /1024, 0)),1) used_GB,

round(NVL(SUM (dfs.bytes)/1024/1024/1024, 0),2) free_space_GB

FROM dba_data_files df, dba_free_space dfs

WHERE df.FILE_ID = dfs.file_id(+)

GROUP BY df.tablespace_name, dfs.file_id, df.file_id , df.bytes, df.file_NAME

ORDER BY df.tablespace_name;

3.Check the tablecount & row count for data validation

select count(*) from countries; #for table's row count

For table count:

select owner, count(*) from dba_tables;

select table_name,to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name)) ,'/ROWSET/ROW/C')) count from user_tables;

@ PostgreSQL

1.CHECK PostgreSQL all schema Size But size will be varried compare to destination, event though check the schema's table count also:

Edit: just noticed the workaround with summing up all tables to get the database size is not necessary:

SELECT schema_name,

pg_size_pretty(sum(table_size)::bigint),

(sum(table_size) / pg_database_size(current_database())) * 100

FROM (

SELECT pg_catalog.pg_namespace.nspname as schema_name,

pg_relation_size(pg_catalog.pg_class.oid) as table_size

FROM pg_catalog.pg_class

JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid

) t

GROUP BY schema_name

ORDER BY schema_name

2.All tablespace size for refernece only not mandatory:

SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) FROM pg_tablespace;

3.Check the tablecount & row count of some sample table if possible at postgresql for data validation

select count(*) from countries; #for table's row count

For table count:

select count(*) from information_schema.tables where table_schema = 'scott';

===========================

NOW WE HAVE TO GO CONFIGURATION PART:

I.FOR INSTALL ORA2PG WE NEED FOLLOWING DEPENDENCIES:

1.DBD-Oracle-1.75_2.tar.gz : Oracle database driver for the DBI module

Oracle Database Drivers Link: http://search.cpan.org/~mjevans/DBD-Oracle-1.75_2/lib/DBD/Oracle/Troubleshooting.pod

2.DBD-Pg-3.6.0.tar.gz : PostgreSQL database driver for the DBI module

Postgres Database Drivers Link: http://download.openpkg.org/components/cache/perl-dbi/

3.DBI-1.636.tar.gz : Database independent interface for Perl(DBI requires one or more 'driver' modules to talk to databases,used for 'perldoc DBI' command)

Perl Database Module Link: https://www.cpan.org/modules/by-module/DBI

4.ora2pg-18.1.tar.gz :This is the Migration free tool

ORA2PG TOOLS LINK : https://sourceforge.net/projects/ora2pg/

II.After Download the above mentioned file then install this ora2pg drivers following method

1.installing Oracle database driver:

[root@YesBolisetty ~]# tar xvzf DBD-Oracle-1.75_2.tar.gz

[root@YesBolisetty ~]# cd DBD-Oracle-1.75_2/

[root@YesBolisetty DBD-Oracle-1.75_2]# export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1

[root@YesBolisetty DBD-Oracle-1.75_2]# export LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/db_1/lib

[root@YesBolisetty DBD-Oracle-1.75_2]# perl Makefile.PL

[root@YesBolisetty DBD-Oracle-1.75_2]# make

[root@YesBolisetty DBD-Oracle-1.75_2]# make install

2.Installing postgres database driver:

[root@YesBolisetty]# tar xvzf DBD-Pg-3.6.0.tar.gz

[root@YesBolisetty]# cd DBD-Pg-3.6.0

[root@YesBolisetty DBD-Pg-3.6.0]# perl Makefile.PL

Configuring DBD::Pg 3.6.0

[root@YesBolisetty DBD-Pg-3.6.0]# make

[root@YesBolisetty DBD-Pg-3.6.0]# make install

[root@YesBolisetty DBD-Pg-3.6.0]# perl Makefile.PL

3.Installing Perl modules:

[root@YesBolisetty]#tar xvzf DBI-1.636.tar.gz

[root@YesBolisetty]#cd DBI-1.636

[root@YesBolisetty]#perl Makefile.Pl

[root@YesBolisetty]#make

[root@YesBolisetty]#make install

I got some error at the time of perl installing this is other server I noted this i sharing this error just for knowledge

[root@serveroracle DBD-Pg-3.6.0]# perl Makefile.PL

Configuring DBD::Pg 3.6.0

PostgreSQL version: 90602 (default port: 5432)

POSTGRES_HOME: /u01/app/postgres/product/96/db_2

POSTGRES_INCLUDE: /u01/app/postgres/product/96/db_2/include

POSTGRES_LIB: /u01/app/postgres/product/96/db_2/lib

OS: linux

Warning: prerequisite version 0 not found.

Could not eval '

package ExtUtils::MakeMaker::_version;

no strict;

BEGIN { eval {

# Ensure any version() routine which might have leaked

# into this package has been deleted. Interferes with

# version->import()

undef *version;

require version;

"version"->import;

} }

local $VERSION;

$VERSION=undef;

do {

use version; our $VERSION = qv('3.6.0');

};

$VERSION;

' in Pg.pm: Can't locate version.pm in @INC (@INC contains: t/lib /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at (eval 11) line 16, line 19.

BEGIN failed--compilation aborted at (eval 11) line 16, line 19.

WARNING: Setting VERSION via file 'Pg.pm' failed

at /usr/share/perl5/vendor_perl/ExtUtils/MakeMaker.pm line 619.

Using DBI 1.636 (for perl 5.016003 on x86_64-linux-thread-multi) installed in /usr/local/lib64/perl5/auto/DBI/

Writing Makefile for DBD::Pg

We correct errors by running the command below.

[root@serveroracle DBD-Pg-3.6.0]# yum -y install 'perl(version)'

Loaded plugins: langpacks, ulninfo

Resolving Dependencies

--> Running transaction check

---> Package perl-version.x86_64 3:0.99.07-2.el7 will be installed

--> Finished Dependency Resolution

4.Installing ora2pg tools:

tar xvzf ora2pg-18.1.tar.gz

[root@YesBolisetty]# cd ora2pg-18.1/

[root@YesBolisetty]# perl Makefile.PL

[root@YesBolisetty]# make

[root@YesBolisetty]# make install

III.After Installed These Above Three Modules check the following status of oracle:

1.Check Oracle Tns and Listener status using "lsnrctl status" if not listener up just start it,if not in lister tns file just create using "netca" utiluity

2.And check the Oracle Database up and running using ps -ef|grep pmon

3.check the oracle user account status locked or not using

select username,account_status from dba_users where username='system';

4.check the port and hostname using "tnsping sid" & "hostname"

5.check the size of schema and table row count i already told in prerequest and postrequest

IV.You have note the following configuration file and utility path:

/usr/local/bin/ora2pg => This is the ora2pg utility Path

/usr/local/bin/ora2pg_scanner =>This is the scanner path =>

/etc/ora2pg/ora2pg.conf =>This is the configuration file path

[root@YesBolisetty]#ora2pg -help => for help command

Now that ora2pg is installed, we can proceed with the migration. The first step is to configure the ora2pg.conf file. We can do a copy of the default template and then modify the file. In our case the configuration file is located in /etc/ora2pg directory.

[root@YesBolisetty ora2pg]# cp ora2pg.conf.dist ora2pg.conf

[root@YesBolisetty ora2pg]# vi ora2pg.conf

V.Now we are going to migrate the schema:

open the Configuration file and put a below parameter's entries

ora2pg works by exporting and importing schemas. We can only export one schema at a time if we use the option SCHEMA. If we want to export all schemas we can just comment the option SCHEMA. In this case all non-oracle users will be extracted.

ORACLE_DSN dbi:Oracle:host=production.localdomain;sid=test;port=1521

ORACLE_USER system

ORACLE_PWD manager

SCHEMA scott

TYPE TABLE PACKAGE COPY VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TABLESPACE TYPE PARTITION

OUTPUT oracleMigrationscript.sql

USER_GRANTS 1 #0 FOR DISABLE WE WANTS GRANTS ALSO IF YOU GET ERRORS SET IT 0 FOR WE WILL GIVE GRANTS PERSMISSION MANULLY(IF NOT USERS ERRORS WILL BE OCCURS BETTER DISABLE IT)

then save and exit using " :wq! "

VI.Then run ORA2PG the utility,it automatically taken config file and scanning then going to migrate.

this scanning will take more time its depend on database size

[root@YesBolisetty ora2pg]# ora2pg

[========================>] 9/9 tables (100.0%) end of scanning.

[> ] 0/9 tables (0.0%) end of scanning.

[========================>] 9/9 tables (100.0%) end of table export.

[> ] 0/1 rows (0.0%) Table AIRTEL_OTPS (0 recs/sec)

[> ] 0/9 total rows (0.0%) - (1 sec., avg: 0 recs/sec).

[> ] 0/1 rows (0.0%) Table BONUS (0 recs/sec)

[> ] 0/9 total rows (0.0%) - (2 sec., avg: 0 recs/sec).

[========================>] 4/1 rows (400.0%) Table DEPT (4 recs/sec)

[==========> ] 4/9 total rows (44.4%) - (2 sec., avg: 2 recs/sec).

[========================>] 14/1 rows (1400.0%) Table EMP (14 recs/sec)

[========================>] 18/9 total rows (200.0%) - (3 sec., avg: 6 recs/sec).

[> ] 0/1 rows (0.0%) Table IMAGES (0 recs/sec)

[========================>] 18/9 total rows (200.0%) - (4 sec., avg: 4 recs/sec).

[========================>] 1/1 rows (100.0%) Table P1 (1 recs/sec)

[========================>] 19/9 total rows (211.1%) - (4 sec., avg: 4 recs/sec).

[> ] 0/1 rows (0.0%) Table PBLOB (0 recs/sec)

[========================>] 19/9 total rows (211.1%) - (5 sec., avg: 3 recs/sec).

[> ] 0/1 rows (0.0%) Table P4 (0 recs/sec)