Copying Oracle Home Directories using rsync

Copying Oracle Home Directories using rsync:

rsync -auvzpogl -e ssh /u01/app/oracle/product/10.2.0/db_1/ 172.22.9.13:/u01/app/oracle/product/10.2.0/db_1/

Copying Oracle Base Directories using rsync:

rsync -auvzpogl -e ssh /u01/app/oracle/ 172.22.9.13:/u01/app/oracle/

Copying oratab, oraInst.loc files using rsync:

rsync -auvzpogl -e ssh /etc/oratab 172.22.9.13:/etc
rsync -auvzpogl -e ssh /etc/oraInst.loc 172.22.9.13:/etc

Oracle import from 10.2.0.2.0 to 10.1.0.1.0

1) Export from the 10.2.0.2.0 version database with below command:

$expdp system/********* directory=data_pump_dir dumpfile=full_db_exp.dmp logfile=full_db_exp.log full=y version=10.1.0.1.0

2) Import the dumpfile (10.2.0.2.0 version) to the 10.1.0.1.0 database:

$impdp system/******** directory=data_pump_dir dumpfile=full_db_exp.dmp logfile=full_db_imp.log full=y

How to Recreate the Database Control Repository

Create Database Console: -
To create the configuration files and repository for Database Console, run:
$ emca -config dbcontrol db -repos create

Drop Database Console: -
To drop (remove) the configuration files and repository for Database Console, run:
$ emca -deconfig dbcontrol db -repos drop

Recreate Database Console: -
To recreate the configuration files and repository for Database Console, run:
$ emca -config dbcontrol db -repos recreate
_______________________________________________________________________________
EM reinstalling procedure: -

After changing of hostname IP address, EM dbconsole port, instance SID, or even TZ settings, EM is going to fail. Using "emca" you can reconfigure eg. the port number to 1150:

emca -reconfig ports -DBCONTROL_HTTP_PORT 1150

but in more serious cases there's only one solution - reinstallation. I tried many approaches (fixing output errors and so on) but this one is effective. Later I've found that Oracle says exactly the same ;-) ... and better do it off rush hours due to its high resource consumption.

Check the status: -
emctl status dbconsole

1st way: -
emca -deconfig dbcontrol db -repos drop
# then
emca -config dbcontrol db -repos create

2nd way: -
emca -deconfig dbcontrol db
# then
emca -config dbcontrol db -repos recreate

When something went wrong, delete DB Control Repository Objects manually with SQLPlus: -
drop user sysman cascade;
drop role MGMT_USER;
drop user MGMT_VIEW cascade;
drop public synonym MGMT_TARGET_BLACKOUTS;
drop public synonym SETEMVIEWUSERCONTEXT;

Now, try again "emca" with create or recreate option.

Example of using an input file for "emca" parameters - dropping EM repository (don't worry, not database, as some may think) with required parameters, for RAC, silent mode:

emca -deconfig dbcontrol db -repos drop -cluster -silent -respFile emca.par

# where emca.par content:
DB_UNIQUE_NAME=[name]
PORT=1521
SYS_PWD=[pwd]
SYSMAN_PWD=[pwd2]
DBSNMP_PWD=[pwd3]
HOST=[hostname]
SID=[sid]
ORACLE_HOSTNAME=[hostname]

Checking which port is correct:

cat $ORACLE_HOME/install/portlist.ini
# eg.
# Enterprise Manager Console HTTP Port (orcl) = 1158

Calling EM in browser: -
http://[hostname]:1158/em

isqlplus: -

isqlplussvc -uninstall
isqlplusctl start
isqlplusctl stop
__________________________________________________________________________________
emca fails on 10.2.0.4 with ORA-04042 and ORA-06512: -

“emca -deconfig dbcontrol db -repos create” it failed with error stack below.

Jan 19, 2009 5:11:53 PM oracle.sysman.emcp.EMReposConfig invoke SEVERE: Error creating the repository
Jan 19, 2009 5:11:53 PM oracle.sysman.emcp.EMReposConfig invoke INFO: Refer to the log file at /oracle/product/ora10204/cfgtoollogs/emca/*****\emca_repos_create_.log for more details.
Jan 19, 2009 5:11:53 PM oracle.sysman.emcp.EMConfig perform SEVERE: Error creating the repository

Then I check the error log, and I found the stack below

Create SYSMAN user.

No errors.
DECLARE
*
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist
ORA-06512: at line 11

While it creates the sysman user it cannot find a procedure and fails. To find out which procedure it was looking for I applied metalink and quick Search on metalink took me the note ID 331938.1

The note says problem is caused by the missing procedure dbms_shared_pool (no idea why it was missing on a brand new 10.2.0.2 db)

To solve the problem

1- Create the package

@$ORACLE_HOME/rdbms/admin/dbmspool.sql

2- Give permission to package

grant execute on dbms_shared_pool to sysman;
grant execute on dbms_shared_pool to dba;

3- follow the re-creation steps from the beginning including dropping sysman role and droppping the existing configurations

4- re run the command “emca -config dbcontrol db -repos create”

Compiling invalid objects

1. DBMS_DDL
2. DBMS_UTILITY
3. UTL_RECOMP
4. utlrp.sql
5. Manually Recompile

DBMS_DDL.ALTER_COMPILE
Syntax: -
exec dbms_ddl.alter_compile ( type , schema, name);
Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER.
Schema : Database Username
Name : Objects name
Example: -
SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');
PL/SQL procedure successfully completed.

DBMS_UTILITY.COMPILE_SCHEMA
Syntax: -
exec dbms_utility.compile_schema ( schema,compile all)
Schema : Database Username
Compile All : Object type ( procedure, function, packages,trigger)
Example: -
SQL> exec dbms_utility.compile_schema('SCOTT');
PL/SQL procedure successfully completed.

UTL_RECOMP
Syntax: -
exec UTL_RECOMP.RECOMP_SERIAL ();
Example: -
SQL> Exec UTL_RECOMP.RECOMP_SERIAL ();
PL/SQL procedure successfully completed.
Note: -Required SYS user to run this package.

UTLRP.SQL scripts
Located: $ORACLE_HOME/rdbms/admin
Example: -
SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\UTLRP.SQL
TIMESTAMP
-----------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2007-08-04 12:47:21

PL/SQL procedure successfully completed.
TIMESTAMP
-----------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2007-08-04 12:47:26
PL/SQL procedure successfully completed.
Note: Required SYS user to run this script.
Recommended: -After upgrade or migrate database.

Best Approach is manually recompiling all Invalid Objects

Spool recompile.sql

Select ‘alter ‘object_type’ ’object_name’ compile;’
From user_objects
Where status <> ‘VALID’
And object_type IN (‘VIEW’,’SYNONYM’,
‘PROCEDURE’,’FUNCTION’,
‘PACKAGE’,’TRIGGER’);

Spool off
@recompile.sql

Note: VIEW,SYNONYM,PROCEDURE,PACKAGE,FUNCTION,TRIGGER

Spool pkg_body.sql

Select ‘alter package ’object_name’ compile body;’
From user_objects
where status <> ‘VALID’
And object_type = ‘PACKAGE BODY’;

Spool off
@pkg_body.sql

Spool undefined.sql

select ‘alter materizlized view ’object_name’ compile;’
From user_objects
where status <> ‘VALID’
And object_type =‘UNDEFINED’;

Spool off
@undefined.sql

Spool javaclass.sql

Select ‘alter java class ’object_name’ resolve;’
from user_objects
where status <> ‘VALID’
And object_type =‘JAVA CLASS’;

Spool off
@javaclass.sql

Spool typebody.sql

Select ‘alter type ‘object_name’ compile body;’
From user_objects
where status <> ‘VALID’
And object_type =‘TYPE BODY’;

Spool off
@typebody.sql

Spool public_synonym.sql

Select ‘alter public synonym ‘object_name’ compile;’
From user_objects
Where status <> ‘VALID’
And owner = ‘PUBLIC’
And object_type = ‘SYNONYM’;

Spool off
@public_synonym.sql
_______________________________________________________________

Simple query for compelling all objects in a database: -

SELECT 'alter '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type) ||' '||owner||
'."'||object_name||'" '||decode(object_type,'PACKAGE BODY','COMPILE BODY','COMPILE')||';'
FROM all_objects WHERE owner like '%'
AND owner not in ('SYS', 'SYSTEM')
AND object_type IN ('PACKAGE','PACKAGE BODY','VIEW','PROCEDURE','TRIGGER','FUNCTION')
AND status='INVALID';
________________________________________________________________

External Tables

create directory emp_tab as '/home/oracle';
grant read, write on directory emp_tab to scott;

CREATE TABLE &Table_name
(&Column1_name &datatype_col1,
&Column2_name &datatype_col2,
&Column3_name &datatype_col3)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY &Directory_name
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ‘,’
MISSING FIELD VALUES ARE NULL)
LOCATION (’&type_text_file_name’))
REJECT LIMIT UNLIMITED;

Silent DBCA

dbca -silent -createDatabase -templateName General_Purpose.dbc
-gdbname ora11g -sid ora11g -responseFile NO_VALUE -characterSet AL32UTF8
-memoryPercentage 30 -emConfiguration LOCAL