How to use “/ as sysdba” to export and import

1. For  windows  platforms
   
   exp userid=”"”/ as sysdba”"” file=export.dmp ...
   imp userid=”"”/ as sysdba”"” file=export.dmp ...
2. For  Unix  paltforms
   
   exp userid=\’/ as sysdba\’   file=export.dmp ...
   imp userid=\’/ as sysdba\’  file=export.dmp ...
   
    

PLS-00923: native compilation failed

Symptoms :
Attempting to implement Native Compilation on a 10g Database fails with an error :PLS-00923: native compilation failed: C compilation or linking failed

plsql_code_type parameter  was set  to   native and plsql_native_library_dir was  set 

Compiling  the  packages  give error : PLS-00923: native compilation failed: C compilation or linking failed:spdzj.c:Could not invoke program /opt/SunProd/SUNWspro8/bin/cc:No such file or directory

Cause:
The wrong/invalid compiler is being referenced within the spnc_commands file.
Take the compiler and path referenced in the error message and at the OS prompt execute the 'ls' command.
The error "No such file or directory" is returned confirming why it can not be invoked

ls  -lrt   /opt/SunProd/SUNWspro8/bin/cc

Solution:

1. Find a  compiler
   which cc
   /usr/bin/cc
2. Modify  $ORACLE_HOME/plsql/spnc_commands that uses  new  compiler.for this example  change  /opt/SunProd/SUNWspro8/bin/cc  with  /usr/bin/cc  in the  file
3. Backup to session and  recompile (It may  needed  to restart the database )
4. check   the  directory  that  referenced in plsql_native_library_dir   parameter  There will  be  some  compiled  c  files

what is TWO_TASK

TWO_TASK is an environment variable used to specify that connections should be made to a remote database (via SQL*Net) without specifying a service name (tnsnames.ora entry). This is equivalent to LOCAL registry entry on Windows platforms

Unix/Linux:
export TWO_TASK=DATABASE1


Windows:set TWO_TASK=DATABASE1

From command  prompt  sqlplus  username/passwd will connect  to  remote  DATABASE1  defined  in $ORACLE_HOME/network/admin/tnsnames.ora

TWO_TASK

How to recover from Extract ERROR 180 encountered commit SCN that is not greater than the highest SCN already processed

This is  the  summary of  Metalink Doc:987379.1

This error occurs in an Oracle RAC environment after a transaction is written to the idle node but does not yet appear in the redo log when the current transaction is processed. This transaction will have a higher SCN then the previous transaction
In order  to avoid  to live  this  problem  the steps in   (Encountered SCN That Is Not Greater Than The Highest SCN Already Processed )  articale  must be  done

But  If you have  encountered  this  problem  here  is  the solution 

1. Do an ETROLLOVER on Extract, and take note of the new sequence number of the trail file.
   ALTER EXTRACT [name], ETROLLOVER
2. Start extract
   START EXTRACT [name]
3. Send PUMP, LOGEND, to see if it's at the end of the previous trail.
   SEND EXTRACT [pump_name], LOGEND
4. Once it is at the end of the trail file, You must stop the pump, and do an ETROLLOVER for it too. Take note of the new trail file sequence number that is created from this step
   STOP EXTRACT [pump_name]
   ALTER EXTRACT [pump_name], ETROLLOVER
5. Alter the pump to SEQNO to the new trail file created from step #1.
   ALTER EXTRACT [pump_name], EXTSEQNO ##### EXTRBA 0
6. Restart pump
   START EXTRACT [pump_name]
7. Send Replicat, LOGEND to make sure it has processed all the remaining data, and stop Replicat.
   SEND REPLICAT [name], LOGEND
   STOP REPLICAT [name]
8. If replicat is not at end of trail, generate a report and forcestop replicat
   SEND REPLICAT [name], REPORT
   STOP REPLICAT [name]!
9. Add the following parameters to replicat parameter file to allow replicat to process each trail record as a single transaction, or set them to 1 if you have any of these parameters
   GROUPTRANSOPS 1
   MAXTRANSOPS 1
10. Restart replicat
    START REPLICAT [name]
11. Once replicat has completely processed the trail, stop the replicat
    STOP REPLICAT [name]
12. Edit the replicat parameter file:
    Add parameter HANDLECOLLISIONS to Replicat parameter file
    Remove or comment out GROUPTRANSOPS and MAXTRANSOPS or revert them back to their original values.
13. ALTER REPLICAT, SEQNO to the new trail file created in step #4.
    ALTER REPLICAT [name], EXTSEQNO ###### EXTRBA 0
14. Start Replicat
    START REPLICAT [name]
15. Once Replicat has processed the out of order SCN operations, disable HANDLECOLLISIONS. You could also look for the CSN and wait for Replicat to checkpoint past it.
    SEND REPLICAT [name], NOHANDLECOLLISIONS
16. Edit the replicat parameter and comment out the HANDLECOLLISIONS parameter. You do not need to stop/restart replicat. This will ensure that on any subsequent replicat restarts the parameter is disabled

See also  :
Encountered SCN That Is Not Greater Than The Highest SCN Already Processed

Gathering System Statistics

• System Statistics can be collected and displayed for CBO to use and apprehend CPU and system I/O information. For each plan candidate, the optimizer computes estimates for I/O and CPU costs.
• Invoke the dbms_stats.gather_system_stats procedure as an elapsed time capture, making sure to collect the statistics during a representative heavy workload
• How to collect  these  statistics.easist way  of  it  is
  
  execute dbms_stats.gather_system_stats('Start');
  -- one hour delay during high workload
  execute dbms_stats.gather_system_stats('Stop');
• 
  
   Here are the data items collected by dbms_stats.gather_system_stats: we  can query it  from  aux_stats$ system view
  
  select * from aux_stats$;
  No Workload (NW) stats::

  CPUSPEEDNW - CPU speed

  IOSEEKTIM - The I/O seek time in milliseconds

  IOTFRSPEED - I/O transfer speed in milliseconds

  Workload-related stats:

  SREADTIM - Single block read time in milliseconds

  MREADTIM - Multiblock read time in ms

  CPUSPEED - CPU speed

  MBRC - Average blocks read per multiblock read

  MAXTHR - Maximum I/O throughput

  SLAVETHR - OPQ Factotum (slave)

• If the hardware or workload has changed these  statistics must be  refreshed.
• If  the workload  differs  from day to  nigth  (run OLTP during the day and DSS at night) then  statistics can be  exported  to  a table and  One  job may be   scheculed  to  import  statistics  before  begining  of  night  and   day

See also:
Gather Optimizer Statistics For Sys

Gather Optimizer Statistics For Sys

Gathering statistics for sys objects

• It is recommended to run gather statistics reqularly, specifically if you are using Oracle APPS and also after upgrades or running catalog scripts
• To gather the dictionary stats run One of the following
  SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS;
  SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
  SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);

Gathering statistics for X$ tables (v$ views )

• Gather_fixed_objects_stats would gather statistics for dynamic tables e.g. the X$ tables which loaded in SGA during the startup. Gathering statistics for fixed objects would normally if we have poor performance in querying the dynamic views e.g. V$ views.
• Fixed objects record current database activity; statistics gathering should be done when database has representative activity.
• To gather the fixed objects stats
  EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

See also :
Gathering System Statistics