ramavenkatesa
asked on
oracle migration using export/import
I was doing export/import --- from 816 to 10202
i took the dump of the 816 database:
i installed the 10202 database.
1.. As a DBA user, perform a full export from the source database, for example:
> exp system/manager FULL=y FILE=myfullexp.dmp
3. Move the dump file to the target database server by scp
4. Create a database on the target server. -- create database [database name]
i have also run 6. Run catalog and catproc
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
5. Before importing the dump file, you must first create your tablespaces -- i did not create tablespace because the filesystem is same.
6. As a DBA user, perform a full import with the IGNORE parameter enabled:
> imp system/manager FULL=y IGNORE=y FILE=myfullexp.dmp
Using IGNORE=y instructs Oracle to ignore any creation errors during the import and permit the import to complete.
-- i got so many errors -- am i wrong at any step ?
i took the dump of the 816 database:
i installed the 10202 database.
1.. As a DBA user, perform a full export from the source database, for example:
> exp system/manager FULL=y FILE=myfullexp.dmp
3. Move the dump file to the target database server by scp
4. Create a database on the target server. -- create database [database name]
i have also run 6. Run catalog and catproc
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
5. Before importing the dump file, you must first create your tablespaces -- i did not create tablespace because the filesystem is same.
6. As a DBA user, perform a full import with the IGNORE parameter enabled:
> imp system/manager FULL=y IGNORE=y FILE=myfullexp.dmp
Using IGNORE=y instructs Oracle to ignore any creation errors during the import and permit the import to complete.
-- i got so many errors -- am i wrong at any step ?
Post the errors?
ASKER
errors file attacghed
errors.txt
errors.txt
When you do FULL import Oracle will reject to change the SYS and other system shemas. This is the main cause of thet errors. You can ignore them and begin to check the application.
But often there are also errors linked with thenonsystem users and these errors are really important
But often there are also errors linked with thenonsystem users and these errors are really important
>>ORA-01654: unable to extend index SYS.I_ACCESS1 by 13 in tablespace SYSTEM
Looks like you created too smal of a SYSTEM tablespace. You need to re-create your database, or add another datafile to SYSTEM, or enable auto-extend.
Looks like you created too smal of a SYSTEM tablespace. You need to re-create your database, or add another datafile to SYSTEM, or enable auto-extend.
ASKER
. importing SYSTEM's objects into SYSTEM
. importing VECTRDBA's objects into VECTRDBA
"ALTER SESSION SET CURRENT_SCHEMA= "VECTRDBA""
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
IMP-00000: Import terminated unsuccessfully
-- whole import was less than 10 minutes..
. importing VECTRDBA's objects into VECTRDBA
"ALTER SESSION SET CURRENT_SCHEMA= "VECTRDBA""
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
IMP-00000: Import terminated unsuccessfully
-- whole import was less than 10 minutes..
ASKER
i did not create any tablespace -- shall i create TS?
ASKER
which all TS shd i create?
Before retrying the export, you need to fix errors related to your SYSTEM tablespace.
Try:
alter database datafile '/oracle/oradata/ORA1/syst
Try:
alter database datafile '/oracle/oradata/ORA1/syst
ASKER
i re-ran the import -- now , i am having the problem in RBS.
in the 816 source db -- i have RBS
in the 10202 target db -- i have undo TS
i am unable to import the tables.
in the 816 source db -- i have RBS
in the 10202 target db -- i have undo TS
i am unable to import the tables.
ASKER
new errors are attached
errors.txt
errors.txt
Have you created an undo tablespace and enabled automatic undo?
ASKER
yes
ASKER
select TABLESPACE_NAME from dba_rollback_segs ;
TABLESPACE_NAME
--------------------------
SYSTEM
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
----------------
TABLESPACE_NAME
--------------------------
SYSTEM
SYSAUX
TOOLS
RBS
TEMP
USERS
UNDOTBS_01
TEMP1
TABLESPACE_NAME
--------------------------
SYSTEM
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
----------------
TABLESPACE_NAME
--------------------------
SYSTEM
SYSAUX
TOOLS
RBS
TEMP
USERS
UNDOTBS_01
TEMP1
show parameter undo_management
ASKER
show parameter undo_management;
NAME TYPE VALUE
--------------------------
undo_management string MANUAL
SQL>
NAME TYPE VALUE
--------------------------
undo_management string MANUAL
SQL>
Actually show all undo parameters
show parameter undo
show parameter undo
ASKER
show parameter undo
NAME TYPE VALUE
--------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string
SQL>
NAME TYPE VALUE
--------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string
SQL>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Make sure you have an spfile. If not, you have to do init.ora
After all that, you can drop your RBS tablespace, there is no need for it anymore with automatic UNDO. You cannot drop the system rollback segment, it is required.
Welcome to 10g and no more RBS management. :)
Welcome to 10g and no more RBS management. :)
ASKER
i started the database with -- startup upgrade
when i am trying to execute the command imp sample/sample FULL=y IGNORE=y FILE=/bu1/sample_1_050409.
i got this ....
IMP-00058: ORACLE error 39710 encountered
ORA-39710: only connect AS SYSDBA is allowed when OPEN in UPGRADE modeUsername:
i created one user -- called hello
i tried to grant sysdba priv to hello -- it is asking me to create passwd file. can u please help me how to create using orapwd utlity?
when i am trying to execute the command imp sample/sample FULL=y IGNORE=y FILE=/bu1/sample_1_050409.
i got this ....
IMP-00058: ORACLE error 39710 encountered
ORA-39710: only connect AS SYSDBA is allowed when OPEN in UPGRADE modeUsername:
i created one user -- called hello
i tried to grant sysdba priv to hello -- it is asking me to create passwd file. can u please help me how to create using orapwd utlity?
There is no need to use STARTUP UPGRADE in this case, you did not upgrade an existing database, you created a new one!
Shutdown and restart in normal mode.
Shutdown and restart in normal mode.
ASKER
startup
ORACLE instance started.
Total System Global Area 595591168 bytes
Fixed Size 2030104 bytes
Variable Size 509478376 bytes
Database Buffers 81920000 bytes
Redo Buffers 2162688 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
---------------
Successful open of redo thread 1
Tue May 5 11:47:46 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue May 5 11:47:46 2009
SMON: enabling cache recovery
Tue May 5 11:47:46 2009
Errors in file /BA/opt/oracle/admin/vectr
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Tue May 5 11:47:46 2009
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 6758
ORA-1092 signalled during: ALTER DATABASE OPEN...
ORACLE instance started.
Total System Global Area 595591168 bytes
Fixed Size 2030104 bytes
Variable Size 509478376 bytes
Database Buffers 81920000 bytes
Redo Buffers 2162688 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
---------------
Successful open of redo thread 1
Tue May 5 11:47:46 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue May 5 11:47:46 2009
SMON: enabling cache recovery
Tue May 5 11:47:46 2009
Errors in file /BA/opt/oracle/admin/vectr
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Tue May 5 11:47:46 2009
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 6758
ORA-1092 signalled during: ALTER DATABASE OPEN...
ASKER
startup
ORACLE instance started.
Total System Global Area 595591168 bytes
Fixed Size 2030104 bytes
Variable Size 509478376 bytes
Database Buffers 81920000 bytes
Redo Buffers 2162688 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
---------------
Successful open of redo thread 1
Tue May 5 11:47:46 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue May 5 11:47:46 2009
SMON: enabling cache recovery
Tue May 5 11:47:46 2009
Errors in file /BA/opt/oracle/admin/vectr
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Tue May 5 11:47:46 2009
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 6758
ORA-1092 signalled during: ALTER DATABASE OPEN...
ORACLE instance started.
Total System Global Area 595591168 bytes
Fixed Size 2030104 bytes
Variable Size 509478376 bytes
Database Buffers 81920000 bytes
Redo Buffers 2162688 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
---------------
Successful open of redo thread 1
Tue May 5 11:47:46 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue May 5 11:47:46 2009
SMON: enabling cache recovery
Tue May 5 11:47:46 2009
Errors in file /BA/opt/oracle/admin/vectr
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Tue May 5 11:47:46 2009
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 6758
ORA-1092 signalled during: ALTER DATABASE OPEN...
Can you explain what you did between the steps where we were discussing your UNDO configuration, and your latest problem? Obviously you did something?
Did you actually start an upgrade? It appears you did but did not finish it. If so, RESTART IN UPGRADE mode and finish running the upgrade scripts (catupgrd.sql) before continuing.
Did you actually start an upgrade? It appears you did but did not finish it. If so, RESTART IN UPGRADE mode and finish running the upgrade scripts (catupgrd.sql) before continuing.
ASKER
initially, i tried to import --- i was getting errors.
after changing the parameters in the init file , i tried to startup -- i could not. i started with upgrade option.
then i tried to import again,. then i am IMP-00058: ORACLE error 39710 encountered
ORA-39710: only connect AS SYSDBA is allowed when OPEN in UPGRADE modeUsername: -- other than this i did nothing ..
after changing the parameters in the init file , i tried to startup -- i could not. i started with upgrade option.
then i tried to import again,. then i am IMP-00058: ORACLE error 39710 encountered
ORA-39710: only connect AS SYSDBA is allowed when OPEN in UPGRADE modeUsername: -- other than this i did nothing ..
ASKER
previously, i could not complete the import properly - -- i mean , import was not completed.
ASKER
shall i delete the oracle software -- and do all over again ?
ASKER
when i ran catupgrd.sql -- i got this
No errors.
SELECT version_script AS file_name FROM DUAL
*
ERROR at line 1:
ORA-20000: Upgrade not supported from version
ORA-06512: at "SYS.VERSION_SCRIPT", line 52
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
No errors.
SELECT version_script AS file_name FROM DUAL
*
ERROR at line 1:
ORA-20000: Upgrade not supported from version
ORA-06512: at "SYS.VERSION_SCRIPT", line 52
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ASKER
can i use -- export dump in 816 and use the import in 10202 or 10204 ?
ASKER
i am using this script to give me the dump.
backup-db.txt
backup-db.txt
I am curious of 2 things:
1) What binary version is installed? Did you install a patch?
2) startup nomount, issue: select * from v$version
1) What binary version is installed? Did you install a patch?
2) startup nomount, issue: select * from v$version
ASKER
i installed 10202 .
startup nomount
ORACLE instance started.
Total System Global Area 595591168 bytes
Fixed Size 2030104 bytes
Variable Size 509478376 bytes
Database Buffers 81920000 bytes
Redo Buffers 2162688 bytes
SQL> select * from v$version;
BANNER
--------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
startup nomount
ORACLE instance started.
Total System Global Area 595591168 bytes
Fixed Size 2030104 bytes
Variable Size 509478376 bytes
Database Buffers 81920000 bytes
Redo Buffers 2162688 bytes
SQL> select * from v$version;
BANNER
--------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
ASKER
i did not install any patch
ASKER
i created orapwd file and granted sysdba privilge to that user -- when i am executing
imp raja/raja FULL=y IGNORE=y FILE=/bu1/sample_1_050409.
IMP-00058: ORACLE error 39710 encountered
ORA-39710: only connect AS SYSDBA is allowed when OPEN in UPGRADE mode
You should not have used startup upgrade, and I am unsure what else happened, so I recommend you drop the database and create a brand new one with DBCA, as it will take no longer than trying to fix this by running catalog.sql, etc.
During DBCA
1) Make sure automatic UNDO is enabled, per directions above (DBCA should let you select that option).
2) Make sure to create a large enough SYSTEM tablespace this time
After DBCA
I recommend only importing the custom schemas, not a full import.
During DBCA
1) Make sure automatic UNDO is enabled, per directions above (DBCA should let you select that option).
2) Make sure to create a large enough SYSTEM tablespace this time
After DBCA
I recommend only importing the custom schemas, not a full import.
ASKER
i can not use DBCA -- is this fine:
create database vectru
logfile GROUP 1 '/u01/oradata/vectru/redo01.log' SIZE 20M,
GROUP 2 '/u02/oradata/vectru/redo02.log' SIZE 20M,
GROUP 3 '/u03/oradata/vectru/redo03.log' SIZE 20M
character set US7ASCII
national character set utf8
datafile '/u01/oradata/vectru/system01.dbf', size 500M extent management local
sysaux datafile '/u01/oradata/vectru/sysaux.dbf' size 500M
undo tablespace undotbs1 datafile '/u04/oradata/vectru/undotbs1.dbf' size 800M
default temporary tablespace temp tempfile 'u03/oradata/vectru/temp01.dbf' size 1000M;
ASKER
CREATE DATABASE vectru
DATAFILE
'/u01/oradata/vectru/system_01.dbf' size 500M
SYSAUX DATAFILE
'/u01/oradata/vectru/sysaux_01.dbf' size 500M
UNDO TABLESPACE vectru_undo DATAFILE
'/u01/oradata/undo_01.dbf' size 500M
LOGFILE
'/u02/oradata/vectru/redo_01.dbf' size 20M,
'/u02/oradata/vectru/redo_02.dbf' size 20M,
'/u03/oradata/vectru/redo_03.dbf' size 20M
CHARACTER SET "WE8ISO8859P1"
NATIONAL CHARACTER SET "UTF8"
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE
'/u04/oradata/vectru/temp_01.dbf' SIZE 500M
NOARCHIVELOG
MAXDATAFILES 1000
MAXLOGFILES 10;
Yes, that looks good, unless this will be a production DB, then you should use log mirroring by adding a 2nd member to each logfile group.
ASKER
CREATE DATABASE vectru
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
-- i am getting this error
You need to check the trace files / alert log to determine what is going on.
Most likely you did not clean up the environment. Make sure there are no files left over from prior. Make sure no shared memory from unclean instance exit
(use ipcs -m to list, and ipcm to remove the orphaned segments if any)
Again, check the alert / trace
(use ipcs -m to list, and ipcm to remove the orphaned segments if any)
Again, check the alert / trace
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You have to use schemas (schemata) level Export/Import. -- can u please give me the command for this one?
Trying to load FULL 8i Db you also import obsolete structures, etc. -- can u please give me an example. i did not get u? -- the data will be the same right?
Trying to load FULL 8i Db you also import obsolete structures, etc. -- can u please give me an example. i did not get u? -- the data will be the same right?
Agreed.
And also I think it is a good idea to post your steps here if you are doing things in between posts, because I cannot track how you went from having undo tablespace issues to having a database in upgrade mode that will not open.
It is good that this is development, and you are learning, because you should not take this approach in production environments (try, get an ORA error, ask a question...) you really need to follow the upgrade documents and/or a specific set of steps that you create. I prefer tried and true scripts of the whole process.
And also I think it is a good idea to post your steps here if you are doing things in between posts, because I cannot track how you went from having undo tablespace issues to having a database in upgrade mode that will not open.
It is good that this is development, and you are learning, because you should not take this approach in production environments (try, get an ORA error, ask a question...) you really need to follow the upgrade documents and/or a specific set of steps that you create. I prefer tried and true scripts of the whole process.
schema level is per user. You _can_ take a full export with system, but don't use full import.
Use fromuser/touser on the import step to selectively import the schemas.
fromuser=(user1,user2,user
Make sure they are in the same order.
Use fromuser/touser on the import step to selectively import the schemas.
fromuser=(user1,user2,user
Make sure they are in the same order.
ASKER
what is did was --
installed the 10202 bianries
startup nomount
created a database -- i created the undo tablespace
created non-system tablespaces
created users
ftp'ed the dump -- and imported (all tables are imported)
now, both the undo & RBs are there
show parameter gives me -- show parameter undo ;
NAME TYPE VALUE
--------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO_1
select tablespace_name , status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------------------
SYSTEM ONLINE
UNDO_1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
TOOLS ONLINE
RBS ONLINE
-- now both undo_1 & RBS are online -- shd i drop the RBS?
it gave me one warning in 1 table -- ROWID, other than that everything is ok.
please correct me if i am wrong anywhere?
installed the 10202 bianries
startup nomount
created a database -- i created the undo tablespace
created non-system tablespaces
created users
ftp'ed the dump -- and imported (all tables are imported)
now, both the undo & RBs are there
show parameter gives me -- show parameter undo ;
NAME TYPE VALUE
--------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO_1
select tablespace_name , status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------------------
SYSTEM ONLINE
UNDO_1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
TOOLS ONLINE
RBS ONLINE
-- now both undo_1 & RBS are online -- shd i drop the RBS?
it gave me one warning in 1 table -- ROWID, other than that everything is ok.
please correct me if i am wrong anywhere?
>>- now both undo_1 & RBS are online -- shd i drop the RBS?
That is due to full import. So yes, take RBS offline / drop it. No need for it now.
Make sure to run verification scripts, verify all objects are valid / compiled (utlrp.sql). Those are listed in upgrade guides
That is due to full import. So yes, take RBS offline / drop it. No need for it now.
Make sure to run verification scripts, verify all objects are valid / compiled (utlrp.sql). Those are listed in upgrade guides
ASKER
shd i run utlrp.sql in open mode?
ASKER
@/apps/opt/oracle/product/
TIMESTAMP
--------------------------
COMP_TIMESTAMP UTLRP_BGN 2009-05-05 19:58:13
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------
COMP_TIMESTAMP UTLRP_END 2009-05-05 19:58:26
PL/SQL procedure successfully completed.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
11
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
--------------------------
0
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------
COMP_TIMESTAMP UTLRP_BGN 2009-05-05 19:58:13
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------
COMP_TIMESTAMP UTLRP_END 2009-05-05 19:58:26
PL/SQL procedure successfully completed.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
11
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
--------------------------
0
PL/SQL procedure successfully completed.
ASKER
am i fine? -- shd i run any other scripts?
utlrp must be run with database open, otherwise it cannot make any changes. Learn the difference between the Oracle modes (nomount, mount and open / restricted)
Have you an Oracle DBA guide or do you spend some time reading the online documentation? If not, you should spend some time, all of this is covered in the documentation and google.
I am happy helping you but I recommend you buy this book:
http://www.amazon.com/Oracle-Database-Certification-Guide-Handbook/dp/0072257903
And take a couple of chapters per week and actually read them and do the practice exam. It will help you for the future, although I might not get so many EE points afterwards. ;)
Have you an Oracle DBA guide or do you spend some time reading the online documentation? If not, you should spend some time, all of this is covered in the documentation and google.
I am happy helping you but I recommend you buy this book:
http://www.amazon.com/Oracle-Database-Certification-Guide-Handbook/dp/0072257903
And take a couple of chapters per week and actually read them and do the practice exam. It will help you for the future, although I might not get so many EE points afterwards. ;)
>>OBJECTS WITH ERRORS
>>-------------------
>> 11
You should check those and find out what they are. Surely they are invalid dependencies. Verify on your old database to see if those were also in error.
>>-------------------
>> 11
You should check those and find out what they are. Surely they are invalid dependencies. Verify on your old database to see if those were also in error.
ASKER
those errors are created, when i did not complie functions
ASKER
i am learning daily -- i am reading oracle 10g by Sam Alapati.
thanks a lot for all guys who are helping me
thanks a lot for all guys who are helping me
>>i am learning daily -- i am reading oracle 10g by Sam Alapati.
I have that one too. At least consider my recommendation on the OCP book, it is really about the best preparation you can get, because it presents it in classroom format, and if you take the quizzes, and check your wrong answers, you will learn. Good luck!
I have that one too. At least consider my recommendation on the OCP book, it is really about the best preparation you can get, because it presents it in classroom format, and if you take the quizzes, and check your wrong answers, you will learn. Good luck!
http://www.oracle-dba-online.com/export_and_import.htm