Thinking Out Loud

August 27, 2013

12c Grid Infrastructure Standalone Silent Upgrade

Filed under: 12c,upgrade — mdinh @ 1:17 pm

This is what insomnia will do to you.

After Apply PSU 11.2.0.3.7 for Grid Infrastructure Standalone and DB with Oracle Restart, I was curious to see if I can upgrade just the Grid Infrastructure to 12c.

I came across something new that I have not seen before. Uh oh! Time to go stealing.

Successfully Setup Software.
As install user, execute the following script to complete the configuration.
        1. /u01/app/12.1.0/grid/cfgtoollogs/configToolAllCommands RESPONSE_FILE=<response_file>

        Note:
        1. This script must be run on the same host from where installer was run.
        2. This script needs a small password properties file for configuration assistants that require passwords (refer to install guide documentation).

There are not many posts with silent operation. Luckily for me, look at what I found: How to install Grid Infrastructure 12.1 in silent mode

Can you believe it? Upgrade completed successfully.

12c_grid_silent_upgrade log
grid12c_upgrade.rsp

Should I shorten the response file to make it cleaner or keep it that way for clarity?

RMAN Transportable Tablespace

Filed under: 11g,RMAN — mdinh @ 3:25 am

How many of you perform RMAN transportable tablespace on a regular basis and can do this from the top of your head?
What makes this post different from any other RMAN transportable tablespace post? I show you how to create objects not exported from transportable tablespace.
Did you know that transportable tablespace export does not include procedures, sequences, synonyms, etc …?
Check the document which I handily provided the link to or try searching for it.

The demo I did was to perform RMAN transportable export, eliminating the need to put production tablespace in READ ONLY.
HR was exported and imported to the same database using:
REMAP_SCHEMA=hr:hr2
REMAP_TABLESPACE=hrdata:hrdata2

August 25, 2013

Apply PSU 11.2.0.3.7 for Grid Infrastructure Standalone and DB with Oracle Restart

Filed under: 11g,oracle,PSU — mdinh @ 2:15 pm

So there I was, moving through a maze trying to figure out how to apply 11.2.0.3.7 for Grid and Database Standalone.

Now I understand why my former manager storage guy does not like ASM in preference of EMC SAN.

Why would anyone introduced the added convoluted complexity to the environment and are the added benefits justified?

Here’s the maze.

From README – Oracle Grid Infrastructure Patch Set Update 11.2.0.3.7 (Includes Database PSU 11.2.0.3.7) – 16742216 >>>

For other configurations listed below, see My Oracle Support Document 1494646.1  (Oracle Grid Infrastructure 11.2.0.3.x Patch Set Update SUPPLEMENTAL README) >>>

Doc ID 1089476.1 – Patch 11gR2 Grid Infrastructure Standalone (Oracle Restart) >>> documentation has wrong instructions

If you can figure this out, then please let me know.

If you don’t want to waste your time, here’s the solution I have Apply PSU 11.2.0.3.7 for Grid Infrastructure Standalone and DB with Oracle Restart

Happy Patching.

August 23, 2013

dbms_metadata and consumer_group Feature or Bug ???

Filed under: 11g — mdinh @ 2:24 am

So there I was, working a simple request. Drop user and recreate which sound simple enough. Use dbms_metadata.

Stole this from asktom.oracle.com site by the way.

$ cat extractuser.sql

set echo off head off verify off feedb off pages 0 long 10000 longchunk 10000 trimspool on lines 2000 timing off term off
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',TRUE);
define _filename='cr_user_&1..sql'
spool &_filename
select (case
when ((select count(*)
from dba_users
where username = UPPER('&&1')) > 0)
then dbms_metadata.get_ddl ('USER', UPPER('&&1'))
else to_clob ('--')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_ts_quotas
where username = UPPER('&&1')) > 0)
then dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', UPPER('&&1'))
else to_clob ('--')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = UPPER('&&1')) > 0)
then dbms_metadata.get_granted_ddl ('ROLE_GRANT', UPPER('&&1'))
else to_clob ('--')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = UPPER('&&1')) > 0)
then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', UPPER('&&1'))
else to_clob ('--')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = UPPER('&&1')) > 0)
then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', UPPER('&&1'))
else to_clob ('--')
end ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl ('DEFAULT_ROLE', UPPER('&&1')) from dual;
spool off
set termout on
prompt ******************************
prompt Execute: &_filename
prompt ******************************
exit

My suggestion, test run the script before you drop the user.
Run everything but the create user.

LAX:(SYS@db01)> set echo on
LAX:(SYS@db01)> @gr_user_hr.sql
LAX:(SYS@db01)>    GRANT "CONNECT" TO "HR";

Grant succeeded.

LAX:(SYS@db01)>    GRANT "RESOURCE" TO "HR";

Grant succeeded.

LAX:(SYS@db01)>    GRANT "DBA" TO "HR";

Grant succeeded.

LAX:(SYS@db01)>
LAX:(SYS@db01)>
LAX:(SYS@db01)>   GRANT UNLIMITED TABLESPACE TO "HR";

Grant succeeded.

LAX:(SYS@db01)>
LAX:(SYS@db01)>
LAX:(SYS@db01)>   GRANT SELECT ON "SYS"."V_$INSTANCE" TO "HR";

Grant succeeded.

LAX:(SYS@db01)>   GRANT SELECT ON "SYS"."V_$TABLESPACE" TO "HR";

Grant succeeded.

LAX:(SYS@db01)>   GRANT EXECUTE ON "SYS"."ETL_GROUP" TO "HR";
  GRANT EXECUTE ON "SYS"."ETL_GROUP" TO "HR"
                         *
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist

LAX:(SYS@db01)>   GRANT WRITE ON DIRECTORY "DATA_PUMP_DIR" TO "HR";

Grant succeeded.

LAX:(SYS@db01)>   GRANT READ ON DIRECTORY "DATA_PUMP_DIR" TO "HR";

Grant succeeded.

LAX:(SYS@db01)>   GRANT EXECUTE ON "SYS"."DEPTREE_FILL" TO "HR";

Grant succeeded.

LAX:(SYS@db01)>
LAX:(SYS@db01)>
LAX:(SYS@db01)>    ALTER USER "HR" DEFAULT ROLE ALL;

User altered.

LAX:(SYS@db01)>

What is that error!

You must have guess it by now from the subject of the post.

LAX:(SYS@db01)> @t.sql
LAX:(SYS@db01)> set echo on
LAX:(SYS@db01)> select object_type from dba_objects where object_name='ETL_GROUP';

OBJECT_TYPE
-------------------
CONSUMER GROUP

LAX:(SYS@db01)> select consumer_group from dba_rsrc_consumer_groups where consumer_group='ETL_GROUP';

CONSUMER_GROUP
------------------------------
ETL_GROUP

LAX:(SYS@db01)> select * from dba_rsrc_consumer_group_privs;

GRANTEE                        GRANTED_GROUP                  GRA INI
------------------------------ ------------------------------ --- ---
PUBLIC                         DEFAULT_CONSUMER_GROUP         YES YES
SYSTEM                         SYS_GROUP                      NO  YES
PUBLIC                         LOW_GROUP                      NO  NO
HR                             ETL_GROUP                      NO  YES

LAX:(SYS@db01)> select initial_rsrc_consumer_group from dba_users where username='HR';

INITIAL_RSRC_CONSUMER_GROUP
------------------------------
ETL_GROUP

LAX:(SYS@db01)> -- REVOKE
LAX:(SYS@db01)> exec dbms_resource_manager_privs.REVOKE_SWITCH_CONSUMER_GROUP('HR','ETL_GROUP');

PL/SQL procedure successfully completed.

LAX:(SYS@db01)> select * from dba_rsrc_consumer_group_privs;

GRANTEE                        GRANTED_GROUP                  GRA INI
------------------------------ ------------------------------ --- ---
PUBLIC                         DEFAULT_CONSUMER_GROUP         YES YES
SYSTEM                         SYS_GROUP                      NO  YES
PUBLIC                         LOW_GROUP                      NO  NO

LAX:(SYS@db01)> -- GRANT
LAX:(SYS@db01)> exec dbms_resource_manager_privs.GRANT_SWITCH_CONSUMER_GROUP(GRANTEE_NAME=>'HR',CONSUMER_GROUP=>'ETL_GROUP',GRANT_OPTION=>FALSE);

PL/SQL procedure successfully completed.

LAX:(SYS@db01)> select * from dba_rsrc_consumer_group_privs;

GRANTEE                        GRANTED_GROUP                  GRA INI
------------------------------ ------------------------------ --- ---
PUBLIC                         DEFAULT_CONSUMER_GROUP         YES YES
SYSTEM                         SYS_GROUP                      NO  YES
PUBLIC                         LOW_GROUP                      NO  NO
HR                             ETL_GROUP                      NO  YES

LAX:(SYS@db01)> -- set initial group
LAX:(SYS@db01)> exec dbms_resource_manager.SET_INITIAL_CONSUMER_GROUP('HR','ETL_GROUP');

PL/SQL procedure successfully completed.

LAX:(SYS@db01)> select initial_rsrc_consumer_group from dba_users where username='HR';

INITIAL_RSRC_CONSUMER_GROUP
------------------------------
ETL_GROUP

LAX:(SYS@db01)>

SQL used to investigate and grant privileges.

select * from dba_rsrc_consumer_group_privs;
exec dbms_resource_manager_privs.REVOKE_SWITCH_CONSUMER_GROUP('HR','ETL_GROUP');
exec dbms_resource_manager_privs.GRANT_SWITCH_CONSUMER_GROUP(GRANTEE_NAME=>'HR',CONSUMER_GROUP=>'ETL_GROUP',GRANT_OPTION=>FALSE);
exec dbms_resource_manager.SET_INITIAL_CONSUMER_GROUP('HR','ETL_GROUP');
select initial_rsrc_consumer_group from dba_users where username='HR';

I tried datapump export and import as well and it was missing consumer group.

How would you have resolved this?

August 17, 2013

[FATAL] [INS-13013] Target environment do not meet some mandatory requirements

Filed under: 11g,oracle — mdinh @ 4:16 pm

./runInstaller -silent -executePrereqs -showProgress -waitforcompletion -responseFile /home/oracle/grid_crs_swonly.rsp

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 36880 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 8188 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-08-17_09-00-13AM. Please wait ...[FATAL] [INS-13013] Target environment do not meet some mandatory requirements.
   CAUSE: Some of the mandatory prerequisites are not met. See logs for details. /u01/app/oraInventory/logs/installActions2013-08-17_09-00-13AM.log
   ACTION: Identify the list of failed prerequisite checks from the log: /u01/app/oraInventory/logs/installActions2013-08-17_09-00-13AM.log. 
   Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.

tail -50 /u01/app/oraInventory/logs/installActions2013-08-17_09-00-13AM.log

INFO: -----------------------------------------------
INFO: Verification Result for Node:san
WARNING: Result values are not available for this verification task
INFO: *********************************************
INFO: Same core file name pattern: This task checks the consistency of core file name pattern across systems.
INFO: Severity:IGNORABLE
INFO: OverallStatus:SUCCESSFUL
INFO: -----------------------------------------------
INFO: Verification Result for Node:san
WARNING: Result values are not available for this verification task
INFO: *********************************************
INFO: User Not In Group: oracle: This is a prerequisite condition to make sure user "oracle" is not part of "root" group.
INFO: Severity:CRITICAL
INFO: OverallStatus:SUCCESSFUL
INFO: -----------------------------------------------
INFO: Verification Result for Node:san
WARNING: Result values are not available for this verification task
INFO: *********************************************
INFO: User Mask: This is a prerequisite condition to make sure the user file creation mask (umask) is "0022".
INFO: Severity:CRITICAL
INFO: OverallStatus:SUCCESSFUL
INFO: -----------------------------------------------
INFO: Verification Result for Node:san
INFO: Expected Value:0022
INFO: Actual Value:0022
INFO: -----------------------------------------------
INFO: *********************************************
INFO: Task resolv.conf Integrity: This task checks consistency of file /etc/resolv.conf file across nodes
INFO: Severity:CRITICAL
INFO: OverallStatus:OPERATION_FAILED
INFO: -----------------------------------------------
INFO: Verification Result for Node:san
WARNING: Result values are not available for this verification task
INFO: *********************************************
INFO: Time zone consistency: This task checks for the consistency of time zones across systems.
INFO: Severity:IGNORABLE
INFO: OverallStatus:SUCCESSFUL
INFO: -----------------------------------------------
INFO: Verification Result for Node:san
WARNING: Result values are not available for this verification task
INFO: All forked task are completed at state init
INFO: Adding ExitStatus PREREQUISITES_NOT_MET to the exit status set
SEVERE: [FATAL] [INS-13013] Target environment do not meet some mandatory requirements.
   CAUSE: Some of the mandatory prerequisites are not met. See logs for details. /u01/app/oraInventory/logs/installActions2013-08-17_09-00-13AM.log
   ACTION: Identify the list of failed prerequisite checks from the log: /u01/app/oraInventory/logs/installActions2013-08-17_09-00-13AM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
INFO: Advice is ABORT
INFO: Finding the most appropriate exit status for the current application
INFO: Exit Status is -3
INFO: Shutdown Oracle Grid Infrastructure
INFO: Unloading Setup Driver

Do you know what this error is?
INS-13013

I tried to be PCU and had to ask and my TL provided the answer.

wtf

 

WTF Oracle! Why not put detailed errors in the log for silent install?

Datapump REMAP_TABLE and Statistics Feature or Bug ???

Filed under: 11g,DataPump,oracle — mdinh @ 3:53 pm

Got a request from a friend:

One more thing , maybe for next post . I’ve noticed that during table_remap ie remap_table=gg.t:t1 there are no stats on t1 even if t has them and are included in dmp file . Bug or feature ?
Regards
Greg

What do you think? Is it a feature or a bug?

expdp_schema.par

directory=DATA_PUMP_DIR
userid='/ as sysdba'
metrics=Y
dumpfile=hr.dmp
tables=HR.EMPLOYEES
reuse_dumpfiles=Y

impdp_schema.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=hr.dmp
REMAP_SCHEMA=HR:DINH
REMAP_TABLE=EMPLOYEES:EMPS
TABLE_EXISTS_ACTION=REPLACE

expdp parfile=expdp_schema.par

Export: Release 11.2.0.3.0 - Production on Sat Aug 17 07:51:51 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA parfile=expdp_schema.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
     Completed 1 TABLE objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 1 seconds
Processing object type TABLE_EXPORT/TABLE/COMMENT
     Completed 12 COMMENT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
     Completed 6 INDEX objects in 1 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 4 CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 6 INDEX_STATISTICS objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 3 REF_CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/TRIGGER
     Completed 2 TRIGGER objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 1 TABLE_STATISTICS objects in 0 seconds
. . exported "HR"."EMPLOYEES"                            16.80 KB     107 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/pdb01/dpdump/hr.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 22:51:56

impdp parfile=impdp_schema.par

Import: Release 11.2.0.3.0 - Production on Sat Aug 17 07:53:02 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA parfile=impdp_schema.par
Processing object type TABLE_EXPORT/TABLE/TABLE
     Completed 1 TABLE objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DINH"."EMPS"                               16.80 KB     107 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/COMMENT
     Completed 12 COMMENT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
     Completed 6 INDEX objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 4 CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 6 INDEX_STATISTICS objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "DINH"."EMPS" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "DINH"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "DINH"."EMPS" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "DINH"."JOBS" ("JOB_ID") ENABLE
     Completed 3 REF_CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/TRIGGER
ORA-39083: Object type TRIGGER failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE TRIGGER update_job_history
  AFTER UPDATE OF job_id, department_id ON employees
  FOR EACH ROW
BEGIN
  add_job_history(:old.employee_id, :old.hire_date, sysdate,
                  :old.job_id, :old.department_id);
END;
ORA-39083: Object type TRIGGER failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE TRIGGER secure_employees
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  secure_dml;
END secure_employees;
     Completed 2 TRIGGER objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39083: Object type TABLE_STATISTICS failed to create with error:
ORA-20000: TABLE "DINH"."EMPLOYEES" does not exist or insufficient privileges
Failing sql is:
DECLARE   c varchar2(60);   nv varchar2(1);   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';   s varchar2(60) := 'DINH';   t varchar2(60) := 'EMPLOYEES';   p varchar2(1);   sp varchar2(1);   stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3
     Completed 1 TABLE_STATISTICS objects in 0 seconds
Job "SYS"."SYS_IMPORT_FULL_01" completed with 5 error(s) at 22:53:04

See the underlined error above? Looks like impdp is trying to import statistics to the original table.

Create table EMPLOYEES

SAN:(DINH@pdb01):PRIMARY> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMPS                           TABLE

SAN:(DINH@pdb01):PRIMARY> select count(*) from emps;

  COUNT(*)
----------
       107

SAN:(DINH@pdb01):PRIMARY> create table employees as select * from emps where 1=0;

Table created.

SAN:(DINH@pdb01):PRIMARY> exit

impdp parfile=impdp_schema.par

Import: Release 11.2.0.3.0 - Production on Sat Aug 17 07:54:05 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA parfile=impdp_schema.par
Processing object type TABLE_EXPORT/TABLE/TABLE
     Completed 1 TABLE objects in 1 seconds
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DINH"."EMPS"                               16.80 KB     107 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/COMMENT
     Completed 12 COMMENT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
     Completed 6 INDEX objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 4 CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 6 INDEX_STATISTICS objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "DINH"."EMPS" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "DINH"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "DINH"."EMPS" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "DINH"."JOBS" ("JOB_ID") ENABLE
     Completed 3 REF_CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"DINH"."UPDATE_JOB_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"DINH"."UPDATE_JOB_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"DINH"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"DINH"."SECURE_EMPLOYEES" created with compilation warnings
     Completed 2 TRIGGER objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 1 TABLE_STATISTICS objects in 0 seconds
Job "SYS"."SYS_IMPORT_FULL_01" completed with 6 error(s) at 22:54:06

Statistics imported and where did it go?

SAN:(DINH@pdb01):PRIMARY> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMPLOYEES                      TABLE
EMPS                           TABLE

SAN:(DINH@pdb01):PRIMARY> select count(*) from emps;

  COUNT(*)
----------
       107

SAN:(DINH@pdb01):PRIMARY> select count(*) from employees;

  COUNT(*)
----------
         0

SAN:(DINH@pdb01):PRIMARY> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
EMPS
EMPLOYEES                             107

SAN:(DINH@pdb01):PRIMARY>

August 15, 2013

How to rebuild table when you don’t have TOAD

Filed under: 11g,DataPump,oracle — mdinh @ 5:24 am

I will demonstrate how to rebuild table DEPARTMENTS using datapump.

Find FK to DEPARTMENTS

select constraint_type, constraint_name ref_constraint, table_name fk_table from user_constraints
where r_constraint_name in (select constraint_name from user_constraints where table_name=’DEPARTMENTS’);

C REF_CONSTRAINT                 FK_TABLE
- ------------------------------ ------------------------------
R EMP_DEPT_FK                    EMPLOYEES
R JHIST_DEPT_FK                  JOB_HISTORY
Add DEPARTMENTS and FK_TABLE to expdp_metadata.par

$ cat expdp_metadata.par

directory=DATA_PUMP_DIR
userid='/ as sysdba'
metrics=Y
dumpfile=hr.dmp
content=METADATA_ONLY
schemas=HR
include=TABLE:"IN ('DEPARTMENTS','EMPLOYEES','JOB_HISTORY')"
reuse_dumpfiles=Y

Add FK_TABLE to impdp_rebuild_tab.par

$ cat impdp_rebuild_tab.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=hr.dmp
schemas=HR
exclude=TABLE:"IN ('EMPLOYEES','JOB_HISTORY')"
exclude=STATISTICS
sqlfile=rebuild_tab.sql

Since include and exclude cannot be used together, exclude is used to not import statistics.

Add REF_CONSTRAINT to impdp_rebuild_ref.par

$ cat impdp_rebuild_ref.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=hr.dmp
schemas=HR
include=REF_CONSTRAINT:"IN ('EMP_DEPT_FK','JHIST_DEPT_FK')"
sqlfile=rebuild_ref.sql

Datapump it

$ cat rebuild.sh
expdp parfile=expdp_metadata.par
impdp parfile=impdp_rebuild_tab.par
impdp parfile=impdp_rebuild_ref.par

[oracle@lax:db01]/home/oracle
$ ./rebuild.sh

Export: Release 11.2.0.3.0 - Production on Wed Aug 14 21:23:56 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA parfile=expdp_metadata.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 3 TABLE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
     Completed 23 COMMENT objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     Completed 12 INDEX objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 6 CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 12 INDEX_STATISTICS objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 8 REF_CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
     Completed 2 TRIGGER objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 3 TABLE_STATISTICS objects in 0 seconds
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/hr.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:24:03

Import: Release 11.2.0.3.0 - Production on Wed Aug 14 21:24:05 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_SCHEMA_01":  /******** AS SYSDBA parfile=impdp_rebuild_tab.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 1 TABLE objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
     Completed 5 COMMENT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     Completed 2 INDEX objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 1 CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 2 REF_CONSTRAINT objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 21:24:07

Import: Release 11.2.0.3.0 - Production on Wed Aug 14 21:24:08 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_SCHEMA_01":  /******** AS SYSDBA parfile=impdp_rebuild_ref.par
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 2 REF_CONSTRAINT objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 21:24:10

[oracle@lax:db01]/home/oracle
$
Edit and Run rebuild_tab.sql

Add: set echo on

Edit made to rebuild_tab.sql will be underlined in run output.

nohup sqlplus "/ as sysdba" @rebuild_tab.sql > rebuild_tab.log 2>&1 &

$ cat rebuild_tab.log

nohup: ignoring input

SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 14 21:44:16 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected.
LAX:(SYS@db01)> alter session set current_schema=hr;

Session altered.

LAX:(SYS@db01)> -- CONNECT SYS
LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

Session altered.

LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/TABLE
LAX:(SYS@db01)> /*
LAX:(SYS@db01)> CREATE TABLE "HR"."DEPARTMENTS"
LAX:(SYS@db01)>    (    "DEPARTMENT_ID" NUMBER(4,0),
LAX:(SYS@db01)>         "DEPARTMENT_NAME" VARCHAR2(30 BYTE) CONSTRAINT "DEPT_NAME_NN" NOT NULL ENABLE,
LAX:(SYS@db01)>         "MANAGER_ID" NUMBER(6,0),
LAX:(SYS@db01)>         "LOCATION_ID" NUMBER(4,0),
LAX:(SYS@db01)>         "LAST_UPDATE" DATE DEFAULT sysdate NOT NULL ENABLE
LAX:(SYS@db01)>    ) SEGMENT CREATION IMMEDIATE
LAX:(SYS@db01)>   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
LAX:(SYS@db01)>  NOCOMPRESS LOGGING
LAX:(SYS@db01)>   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
LAX:(SYS@db01)>   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
LAX:(SYS@db01)>   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
LAX:(SYS@db01)>   TABLESPACE "USER_DATA" ;
LAX:(SYS@db01)> */
LAX:(SYS@db01)> -- MANUAL EDIT: drop and recreate table
LAX:(SYS@db01)> create table departments_old compress as select * from departments;

Table created.

LAX:(SYS@db01)> drop table departments cascade constraints purge;

Table dropped.

LAX:(SYS@db01)> create table departments as select * from departments_old;

Table created.

LAX:(SYS@db01)> -- MANUAL EDIT: add DEFAULT values
LAX:(SYS@db01)> alter table departments modify(last_update default sysdate);

Table altered.

LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/COMMENT
LAX:(SYS@db01)>  COMMENT ON COLUMN "HR"."DEPARTMENTS"."DEPARTMENT_ID" IS 'Primary key column of departments table.';

Comment created.

LAX:(SYS@db01)>  COMMENT ON COLUMN "HR"."DEPARTMENTS"."DEPARTMENT_NAME" IS 'A not null column that shows name of a department. Administration,
  2  Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
  3  Relations, Sales, Finance, and Accounting. ';

Comment created.

LAX:(SYS@db01)>  COMMENT ON COLUMN "HR"."DEPARTMENTS"."MANAGER_ID" IS 'Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.';

Comment created.

LAX:(SYS@db01)>  COMMENT ON COLUMN "HR"."DEPARTMENTS"."LOCATION_ID" IS 'Location id where a department is located. Foreign key to location_id column of locations table.';

Comment created.

LAX:(SYS@db01)>  COMMENT ON TABLE "HR"."DEPARTMENTS"  IS 'Departments table that shows details of departments where employees
  2  work. Contains 27 rows; references with locations, employees, and job_history tables.';

Comment created.

LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
LAX:(SYS@db01)> -- CONNECT HR
LAX:(SYS@db01)> CREATE UNIQUE INDEX "HR"."DEPT_ID_PK" ON "HR"."DEPARTMENTS" ("DEPARTMENT_ID")
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  5    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  6    TABLESPACE "USER_DATA" PARALLEL 1 ;

Index created.

LAX:(SYS@db01)>
LAX:(SYS@db01)>   ALTER INDEX "HR"."DEPT_ID_PK" NOPARALLEL;

Index altered.

LAX:(SYS@db01)> CREATE INDEX "HR"."DEPT_LOCATION_IX" ON "HR"."DEPARTMENTS" ("LOCATION_ID")
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  5    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  6    TABLESPACE "USER_DATA" PARALLEL 1 ;

Index created.

LAX:(SYS@db01)>
LAX:(SYS@db01)>   ALTER INDEX "HR"."DEPT_LOCATION_IX" NOPARALLEL;

Index altered.

LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
LAX:(SYS@db01)> -- CONNECT SYS
LAX:(SYS@db01)> ALTER TABLE "HR"."DEPARTMENTS" ADD CONSTRAINT "DEPT_ID_PK" PRIMARY KEY ("DEPARTMENT_ID")
  2    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  5    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  6    TABLESPACE "USER_DATA"  ENABLE;

Table altered.

LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
LAX:(SYS@db01)> ALTER TABLE "HR"."DEPARTMENTS" ADD CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID")
  2            REFERENCES "HR"."LOCATIONS" ("LOCATION_ID") ENABLE;

Table altered.

LAX:(SYS@db01)> ALTER TABLE "HR"."DEPARTMENTS" ADD CONSTRAINT "DEPT_MGR_FK" FOREIGN KEY ("MANAGER_ID")
  2            REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") DISABLE;

Table altered.

LAX:(SYS@db01)> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lax:db01]/home/oracle
$
Edit and Run rebuild_ref.sql

Add: set echo on

nohup sqlplus "/ as sysdba" @rebuild_ref.sql > rebuild_ref.log 2>&1 &

$ cat rebuild_ref.log

nohup: ignoring input

SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 14 21:55:51 2013

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, OLAP, Data Mining and Real Application Testing options

LAX:(SYS@db01)> -- CONNECT SYS
LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

Session altered.

LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
LAX:(SYS@db01)> ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
  2            REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE;

Table altered.

LAX:(SYS@db01)> ALTER TABLE "HR"."JOB_HISTORY" ADD CONSTRAINT "JHIST_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
  2            REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE;

Table altered.

LAX:(SYS@db01)> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lax:db01]/home/oracle
$

Don’t forget to gather statistics.

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HR',tabname=>'DEPARTMENTS',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>dbms_stats.auto_sample_size,degree=>4);

August 14, 2013

ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

Filed under: 11g,oracle — mdinh @ 3:57 am

Does having the code make troubleshooting easier?

Here is an example code:

create table t (
  id int,
  name varchar2(30)
)
partition by range (id) (
partition p0 values less than (10),
partition pmax values less than (maxvalue)
);
alter table t add constraint pk_t primary key(id);
insert into t values(1,'one');
insert into t values(11,'eleven');

create table t_stage (
  id int,
  name varchar2(30)
);
alter table t_stage add constraint pk_t_stag primary key(id);
insert into t_stage values(2,'two');
commit;

alter table t exchange partition pmax with table t_stage including indexes without validation update global indexes;

LAX:(HR@db01)> alter table t exchange partition pmax with table t_stage including indexes without validation update global indexes;
alter table t exchange partition pmax with table t_stage including indexes without validation update global indexes
                                                 *
ERROR at line 1:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

What do you think is wrong? No peeking!

 

 

 

dallas-cowboys-cheerleaders

 

 

 

FOCUS! Let’s look at the data.

LAX:(HR@db01)> select * from t;

        ID NAME
---------- ------------------------------
         1 one
        11 eleven

LAX:(HR@db01)> select * from t_stage;

        ID NAME
---------- ------------------------------
         2 two

Table Partition

select t.table_name, p.partition_name, partition_position, high_value
from user_tab_partitions p, user_tables t
where t.table_name in (‘T’,’T_STAGE’)
and t.table_name=p.table_name(+)
order by partition_position asc;

TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION HIGH_VALUE
------------------------------ ------------------------------ ------------------ ------------------------------
T                              P0                                              1 10
T                              PMAX                                            2 MAXVALUE
T_STAGE

Index Partition

select i.table_name, i.index_name ix, p.index_name pix, p.partition_name, partition_position, high_value
from user_ind_partitions p, user_indexes i
where i.table_name in (‘T’,’T_STAGE’)
and i.index_name=p.index_name(+)
order by partition_position asc;

There is no index partition.

TABLE_NAME                     IX                             PIX                            PARTITION_NAME                 PARTITION_POSITION HIGH_VALUE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------------------
T                              PK_T
T_STAGE                        PK_T_STAG

Check for FK on table:

select constraint_name, constraint_type, table_name, r_constraint_name, status, bad, rely, validated, index_name, delete_rule from user_constraints
where r_constraint_name in (select constraint_name from user_constraints where table_name=’T’);

Drop PK and recreate with local index:

alter table T drop constraint PK_T;
alter table T add constraint PK_T primary key(ID) using index local;

Exchange partition:
alter table t exchange partition pmax with table t_stage including indexes without validation update global indexes;

LAX:(HR@db01)> select * from t;

        ID NAME
---------- ------------------------------
         1 one
         2 two

LAX:(HR@db01)> select * from t_stage;

        ID NAME
---------- ------------------------------
        11 eleven

select i.table_name, i.index_name ix, p.index_name pix, p.partition_name, partition_position, high_value
from user_ind_partitions p, user_indexes i
where i.table_name in (‘T’,’T_STAGE’)
and i.index_name=p.index_name(+)
order by partition_position asc;

Index now partitioned:

TABLE_NAME                     IX                             PIX                            PARTITION_NAME                 PARTITION_POSITION HIGH_VALUE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------------------
T                              PK_T                           PK_T                           P0                                              1 10
T                              PK_T                           PK_T                           PMAX                                            2 MAXVALUE
T_STAGE                        PK_T_STAG

Any INVALID index?

select index_name, table_name, status from user_indexes where status <> ‘VALID’;

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
PK_T                           T                              N/A

August 12, 2013

Oracle Golden Gate 11.2.1.0.7 on OEL 6.4 Install and not using 12c

Filed under: 11g,GoldenGate — mdinh @ 3:33 am

Should have known better to check the certification matrix first.

OGG is not supported on 12c database. Tried hacking as much I as I could and decided it was not worth the effort.

Here are some errors and warning encountered.

ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, ebidiaa.prm: Opening file +DATA1/CDB01/ONLINELOG/group_3.264.822253565 in DBLOGREADER mode: (1031) ORA-01031: insufficient privileges

ERROR OGG-00665 Oracle GoldenGate Capture for Oracle, ebidiaa.prm: OCI Error describe for query (status = 1031-ORA-01031: insufficient privileges), 
SQL<SELECT o.obj# FROM sys.obj$ o, sys.user$ u, sys.tab$ t WHERE o.name = UPPER('OBJ$') AND o.owner# = u.user# AND u.name = UPPER('SYS') AND o.obj# = t.obj#>.

ERROR OGG-01771 Oracle GoldenGate Capture for Oracle, ebidiaa.prm: DBOPTIONS DECRYPTPASSWORD must be used to decrypt data that is encrypted with Transparent Data Encryption. 
Otherwise, contact Oracle Support to use TRANLOGOPTIONS with _IGNORETSERECORDS to skip the capture of tables in an encrypted tablespace.

WARNING OGG-01760 Oracle GoldenGate Capture for Oracle, ebidiaa.prm: Ignoring REDO records for encrypted tablespace. This could cause data integrity issues.

Instead, I started to test bidirectional replication for 11.2.0.3 on OEL 6.4 using ASM.

Prerequisites

Archive Mode:
ALTER DATABASE ARCHIVELOG;

Force Logging:
ALTER DATABASE FORCE LOGGING;

Supplemental Logging:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Note: there were no database activities during the configuration.

Golden Gate Setup

(e)xtract, (p)ump extract, (r)eplicat group name is limited to 8 characters

#%%%%%@@
# – group type
% – group name (bidi for bidirectional, call it anything)
@ – Site to site notation, i.e. a->b (ab) and b->a (ba)

SITE a: /u01/app/ggs01 (OGG install)
— Primary Extract to local trail aa
ADD EXTRACT ebidiaa, TRANLOG, BEGIN NOW
ADD EXTTRAIL /u01/app/ggs01/dirdat/aa, EXTRACT ebidiaa, MEGABYTES 500

— DataPump Extract reads from local trail aa; pump to remote trail ab
ADD EXTRACT pbidiab, EXTTRAILSOURCE /u01/app/ggs01/dirdat/aa
ADD RMTTRAIL /u01/app/ggs02/dirdat/ab, EXTRACT pbidiab, MEGABYTES 500

— Replicat from pump pbidiab
ADD REPLICAT rbidiba, EXTTRAIL /u01/app/ggs01/dirdat/ba

SITE b: /u01/app/ggs02 (OGG install)
— Primary Extract to local trail bb
ADD EXTRACT ebidibb, TRANLOG, BEGIN NOW
ADD EXTTRAIL /u01/app/ggs02/dirdat/bb, EXTRACT ebidibb, MEGABYTES 500

— DataPump Extract reads from local trail bb; pump to remote trail ba
ADD EXTRACT pbidiba, EXTTRAILSOURCE /u01/app/ggs02/dirdat/bb
ADD RMTTRAIL /u01/app/ggs01/dirdat/ba, EXTRACT pbidiba, MEGABYTES 500

— Replicat from pump pbidiab
ADD REPLICAT rbidiab, EXTTRAIL /u01/app/ggs02/dirdat/ab

The easy way to remember this is that pump extract from one site and replicat to another site
will always have the same group name, i.e. pbidiab->rbidiab and pbidiba->rbidiab

Alternatively, think of pump PK (primary key) and replicat as FK (referential key)

See Bidirectional Configurations for more details.

Create Users

Create users ggs (system), gge (extract), ggr (replicat) for both sites. using ggs_setup.sql

Output log ggs_setup.log

Configure Site a

Configure site a, using config_site_a.oby (in OGG, obey script is synonymous to SQL script).

Output log config_site_a.log

Configure Site b

Configure site b, using config_site_b.oby
Output log config_site_b.log

Demo

Create table and insert data at each site to verify replication.

demo.log

Update

Oracle GoldenGate database Schema Profile check script for Oracle DB (Doc ID 1296168.1)

Might be a good idea to run check against schema being replicated.
schemaCheckOracle.sh.out

August 6, 2013

12c Deinstall

Filed under: 12c — mdinh @ 1:39 pm

After all the hacking I did trying to configure OGG 11.2.1.0.7 on 12c, I wanted to start anew.
Might I add, OGG 11.2.1.0.7 is not certified on 12c. So much for the ZERO downtime upgrade.

Caveat, I dropped database and ASM diskgroup before deinstall

DEINSTALL DATABASE

oracle@san:cdb01:/home/oracle
> ps -ef|grep oracle

oracle    2728     1  0 05:40 ?        00:00:02 /u01/app/oracle/product/12.1.0/grid/bin/ohasd.bin reboot
root      2935  2549  0 05:40 ?        00:00:00 sshd: oracle [priv]
oracle    2937  2935  0 05:41 ?        00:00:00 sshd: oracle@pts/0
oracle    2938  2937  0 05:41 pts/0    00:00:00 -bash
root      2980  2549  0 05:41 ?        00:00:00 sshd: oracle [priv]
oracle    2982  2980  0 05:41 ?        00:00:00 sshd: oracle@pts/1
oracle    2983  2982  0 05:41 pts/1    00:00:00 -bash
oracle    3294  2983  0 05:52 pts/1    00:00:00 ps -ef
oracle    3295  2983  0 05:52 pts/1    00:00:00 grep oracle

oracle@san:cdb01:/home/oracle
> echo $ORACLE_HOME

/u01/app/oracle/product/12.1.0/dbhome_1

oracle@san:cdb01:/home/oracle
> /u01/app/oracle/product/12.1.0/dbhome_1/deinstall/deinstall

Checking for required files and bootstrapping ...
Please wait ...
Location of logs /u01/app/oraInventory/logs/

############ ORACLE DEINSTALL & DECONFIG TOOL START ############

######################### CHECK OPERATION START #########################
## [START] Install check configuration ##

Checking for existence of the Oracle home location /u01/app/oracle/product/12.1.0/dbhome_1
Oracle Home type selected for deinstall is: Oracle Single Instance Database
Oracle Base selected for deinstall is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory
Checking for sufficient temp space availability on node(s) : 'san.localdomain'

## [END] Install check configuration ##

Network Configuration check config START

Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_check2013-08-06_05-53-45-AM.log

Network Configuration check config END

Database Check Configuration START

Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_check2013-08-06_05-53-46-AM.log

Use comma as separator when specifying list of values as input

Specify the list of database names that are configured in this Oracle home [cdb01]:

###### For Database 'cdb01' ######

Specify the type of this database (1.Single Instance Database|2.Oracle Restart Enabled Database) [2]:
Specify the list of nodes on which this database has instances [san]:
Specify the diagnostic destination location of the database [/u01/app/oracle/diag/rdbms/cdb01]:
Specify the storage type used by the Database ASM|FS []: ASM

Database Check Configuration END
Oracle Configuration Manager check START
OCM check log file location : /u01/app/oraInventory/logs//ocm_check4193.log
Oracle Configuration Manager check END

######################### CHECK OPERATION END #########################

####################### CHECK OPERATION SUMMARY #######################
Oracle Home selected for deinstall is: /u01/app/oracle/product/12.1.0/dbhome_1
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
The following databases were selected for de-configuration : cdb01
Database unique name : cdb01
Storage used : ASM
Checking the config status for CCR
Oracle Home exists with CCR directory, but CCR is not configured
CCR check is finished
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2013-08-06_05-53-44-AM.out'
Any error messages from this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2013-08-06_05-53-44-AM.err'

######################## CLEAN OPERATION START ########################
Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_clean2013-08-06_05-55-09-AM.log
Database Clean Configuration START cdb01
This operation may take few minutes.
Database Clean Configuration END cdb01

Network Configuration clean config START

Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_clean2013-08-06_05-56-44-AM.log

De-configuring Naming Methods configuration file...
Naming Methods configuration file de-configured successfully.

De-configuring Local Net Service Names configuration file...
Local Net Service Names configuration file de-configured successfully.

De-configuring backup files...
Backup files de-configured successfully.

The network configuration has been cleaned up successfully.

Network Configuration clean config END

Oracle Configuration Manager clean START
OCM clean log file location : /u01/app/oraInventory/logs//ocm_clean4193.log
Oracle Configuration Manager clean END
Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START

Detach Oracle home '/u01/app/oracle/product/12.1.0/dbhome_1' from the central inventory on the local node : Done

Delete directory '/u01/app/oracle/product/12.1.0/dbhome_1' on the local node : Done

The Oracle Base directory '/u01/app/oracle' will not be removed on local node. The directory is in use by Oracle Home '/u01/app/oracle/product/12.1.0/grid'.

Oracle Universal Installer cleanup was successful.

Oracle Universal Installer clean END

## [START] Oracle install clean ##

Clean install operation removing temporary directory '/tmp/deinstall2013-08-06_05-53-05AM' on node 'san'

## [END] Oracle install clean ##

######################### CLEAN OPERATION END #########################

####################### CLEAN OPERATION SUMMARY #######################
Successfully de-configured the following database instances : cdb01
Cleaning the config for CCR
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
Successfully detached Oracle home '/u01/app/oracle/product/12.1.0/dbhome_1' from the central inventory on the local node.
Successfully deleted directory '/u01/app/oracle/product/12.1.0/dbhome_1' on the local node.
Oracle Universal Installer cleanup was successful.

Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################

############# ORACLE DEINSTALL & DECONFIG TOOL END #############

DEINSTALL GRID

oracle@san:+ASM:/home/oracle
> ps -ef|grep oracle

oracle    2728     1  0 05:40 ?        00:00:04 /u01/app/oracle/product/12.1.0/grid/bin/ohasd.bin reboot
root      2935  2549  0 05:40 ?        00:00:00 sshd: oracle [priv]
oracle    2937  2935  0 05:41 ?        00:00:00 sshd: oracle@pts/0
oracle    2938  2937  0 05:41 pts/0    00:00:00 -bash
root      2980  2549  0 05:41 ?        00:00:00 sshd: oracle [priv]
oracle    2982  2980  0 05:41 ?        00:00:00 sshd: oracle@pts/1
oracle    2983  2982  0 05:41 pts/1    00:00:00 -bash
oracle   10219  2938  0 05:58 pts/0    00:00:00 ps -ef
oracle   10220  2938  0 05:58 pts/0    00:00:00 grep oracle

oracle@san:+ASM:/home/oracle
> echo $ORACLE_HOME

/u01/app/oracle/product/12.1.0/grid

oracle@san:+ASM:/home/oracle
> /u01/app/oracle/product/12.1.0/grid/deinstall/deinstall

Checking for required files and bootstrapping ...
Please wait ...
Location of logs /tmp/deinstall2013-08-06_05-58-34AM/logs/
############ ORACLE DEINSTALL & DECONFIG TOOL START ############

######################### CHECK OPERATION START #########################
## [START] Install check configuration ##

Checking for existence of the Oracle home location /u01/app/oracle/product/12.1.0/grid
Oracle Home type selected for deinstall is: Oracle Grid Infrastructure for a Standalone Server
Oracle Base selected for deinstall is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory
Checking for existence of the Oracle Grid Infrastructure home /u01/app/oracle/product/12.1.0/grid
Checking for sufficient temp space availability on node(s) : 'san.localdomain'
## [END] Install check configuration ##
Traces log file: /tmp/deinstall2013-08-06_05-58-34AM/logs//crsdc_2013-08-06_05-59-16AM.log
Network Configuration check config START
Network de-configuration trace file location: /tmp/deinstall2013-08-06_05-58-34AM/logs/netdc_check2013-08-06_05-59-16-AM.log
Specify all Oracle Restart enabled listeners that are to be de-configured [LISTENER]:
Network Configuration check config END
Asm Check Configuration START
ASM de-configuration trace file location: /tmp/deinstall2013-08-06_05-58-34AM/logs/asmcadc_check2013-08-06_06-00-30-AM.log
Automatic Storage Management (ASM) instance is detected in this Oracle home /u01/app/oracle/product/12.1.0/grid.
ASM Diagnostic Destination : /u01/app/oracle
ASM Diskgroups : +DATA1
ASM diskstring : <Default>
Diskgroups will be dropped
De-configuring ASM will drop all the diskgroups and their contents at cleanup time. This will affect all of the databases and ACFS that use this ASM instance(s).
 If you want to retain the existing diskgroups or if any of the information detected is incorrect, you can modify by entering 'y'. Do you want to modify above information (y|n) [n]: y
Specify the ASM Diagnostic Destination [/u01/app/oracle]:
Specify the diskstring []:
Specify the diskgroups that are managed by this ASM instance [+DATA1]:
De-configuring ASM will drop the diskgroups at cleanup time. Do you want deconfig tool to drop the diskgroups y|n [y]:
Database Check Configuration START
Database de-configuration trace file location: /tmp/deinstall2013-08-06_05-58-34AM/logs/databasedc_check2013-08-06_06-00-56-AM.log
Database Check Configuration END
######################### CHECK OPERATION END #########################

####################### CHECK OPERATION SUMMARY #######################
Oracle Grid Infrastructure Home is: /u01/app/oracle/product/12.1.0/grid
The cluster node(s) on which the Oracle home deinstallation will be performed are:null
Oracle Home selected for deinstall is: /u01/app/oracle/product/12.1.0/grid
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
Following Oracle Restart enabled listener(s) will be de-configured: LISTENER
ASM instance will be de-configured from this Oracle home
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/tmp/deinstall2013-08-06_05-58-34AM/logs/deinstall_deconfig2013-08-06_05-59-15-AM.out'
Any error messages from this session will be written to: '/tmp/deinstall2013-08-06_05-58-34AM/logs/deinstall_deconfig2013-08-06_05-59-15-AM.err'
######################## CLEAN OPERATION START ########################
Database de-configuration trace file location: /tmp/deinstall2013-08-06_05-58-34AM/logs/databasedc_clean2013-08-06_06-00-58-AM.log
ASM de-configuration trace file location: /tmp/deinstall2013-08-06_05-58-34AM/logs/asmcadc_clean2013-08-06_06-00-58-AM.log
ASM Clean Configuration START
ASM Clean Configuration END
Network Configuration clean config START
Network de-configuration trace file location: /tmp/deinstall2013-08-06_05-58-34AM/logs/netdc_clean2013-08-06_06-01-43-AM.log
De-configuring Oracle Restart enabled listener(s): LISTENER
De-configuring listener: LISTENER
 Stopping listener: LISTENER
 Listener stopped successfully.
 Unregistering listener: LISTENER
 Listener unregistered successfully.
 Deleting listener: LISTENER
 Listener may not exist.
Listener de-configured successfully.
De-configuring Listener configuration file...
Listener configuration file de-configured successfully.
De-configuring Naming Methods configuration file...
Naming Methods configuration file de-configured successfully.
De-configuring Local Net Service Names configuration file...
Local Net Service Names configuration file de-configured successfully.
De-configuring backup files...
Backup files de-configured successfully.
The network configuration has been cleaned up successfully.
Network Configuration clean config END

---------------------------------------->
Run the following command as the root user or the administrator on node "san".
/tmp/deinstall2013-08-06_05-58-34AM/perl/bin/perl -I/tmp/deinstall2013-08-06_05-58-34AM/perl/lib -I/tmp/deinstall2013-08-06_05-58-34AM/crs/install /tmp/deinstall2013-08-06_05-58-34AM/crs/install/roothas.pl -force -deconfig -paramfile "/tmp/deinstall2013-08-06_05-58-34AM/response/deinstall_OraGI12Home1.rsp"
Press Enter after you finish running the above commands
<----------------------------------------
Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START
Detach Oracle home '/u01/app/oracle/product/12.1.0/grid' from the central inventory on the local node : Done
Delete directory '/u01/app/oracle/product/12.1.0/grid' on the local node : Done
Delete directory '/u01/app/oraInventory' on the local node : Done
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/log/debug'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/log/test'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/incpkg'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata_dgif'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/incident'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata_pv'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/cdump'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/sweep'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/alert'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/stage'. The directory is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/trace/ora_24887_140444747064864.trc'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/trace/ora_24887_140444747064864.trm'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/trace/ora_24930_139873769408064.trm'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/trace/ora_24930_139873769408064.trc'. The file is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/trace'. The directory is not empty.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/log/debug'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/log/test'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/log'. The directory is not empty.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/incpkg'. The directory is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/INC_METER_INFO.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/IPS_PACKAGE.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/HM_INFO.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/HM_RUN.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/EM_DIAG_JOB.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/DDE_USER_INCIDENT_ACTION_MAP.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/IPS_PROGRESS_LOG.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/PICKLEERR.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/EM_USER_ACTIVITY.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/VIEWCOL.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/PROBLEM.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/INCIDENT_FILE.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/INC_METER_IMPT_DEF.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/INC_METER_PK_IMPTS.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/DFW_CONFIG_CAPTURE.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/DDE_USER_ACTION_PARAMETER.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/SWEEPERR.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/INCIDENT.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/IPS_PACKAGE_INCIDENT.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/IPS_PACKAGE_HISTORY.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/DDE_USER_ACTION_DEF.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/ADR_INVALIDATION.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/DFW_CONFIG_ITEM.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/DDE_USER_ACTION.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/INC_METER_SUMMARY.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/INCCKEY.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/DDE_USER_ACTION_PARAMETER_DEF.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/INC_METER_CONFIG.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/IPS_FILE_COPY_LOG.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/IPS_REMOTE_PACKAGE.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/IPS_CONFIGURATION.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/HM_MESSAGE.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/ADR_CONTROL.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/IPS_FILE_METADATA.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/VIEW.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/HM_FDG_SET.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/HM_RECOMMENDATION.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/IPS_PACKAGE_FILE.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/EM_TARGET_INFO.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/DDE_USER_INCIDENT_TYPE.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/HM_FINDING.ams'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata/AMS_XACTION.ams'. The file is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata'. The directory is not empty.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata_dgif'. The directory is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_4294531585_3603614071.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1919660852_3403801080.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_4123867812_464947195.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_3172236303_1050790107.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_58174112_2445060518.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1_2089646048.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_2087871662_4269132395.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1096102262_3454819329.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1618_3044626670.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_2020575435_1685934094.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_497148109_3359240142.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1485693922_2937733126.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_53421_2401899358.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_117463809_1252689677.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_423666793_1876373544.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_2087871759_1054152112.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1479503217_1058909787.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1919660854_3704060414.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_53419_3606358678.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_53417_2985279723.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_3216766131_2188903675.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1762885_3826749810.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_2876149918_855262320.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1506961005_3222638125.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_994187642_3287667720.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_3218196182_2691928421.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1919660852_3200803573.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_3063883707_458774509.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1317892465_764739657.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_3219651335_1369860572.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1477507928_3848095858.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1096102193_3488045378.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_2904361128_3186785985.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1744845641_3861997533.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_3216668543_3129272988.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_2569157681_2857696958.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1737460353_941080040.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_2099277876_66242055.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1521063874_363843526.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_1762783_4031814035.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_2485072945_164397129.lck'. The file is in use.
Failed to delete the file '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck/AM_53417_1688101061.lck'. The file is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/lck'. The directory is not empty.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/incident'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/metadata_pv'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/cdump'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/sweep'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/alert'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80/stage'. The directory is in use.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root/host_420771725_80'. The directory is not empty.
Failed to delete the directory '/u01/app/oracle/diag/asmtool/user_root'. The directory is not empty.
Failed to delete the directory '/u01/app/oracle/diag/asmtool'. The directory is not empty.
Failed to delete the directory '/u01/app/oracle/diag'. The directory is not empty.
The Oracle Base directory '/u01/app/oracle' will not be removed on local node. The directory is not empty.
Oracle Universal Installer cleanup was successful.
Oracle Universal Installer clean END

## [START] Oracle install clean ##
Clean install operation removing temporary directory '/tmp/deinstall2013-08-06_05-58-34AM' on node 'san'
## [END] Oracle install clean ##

######################### CLEAN OPERATION END #########################

####################### CLEAN OPERATION SUMMARY #######################
ASM instance was de-configured successfully from the Oracle home
Following Oracle Restart enabled listener(s) were de-configured successfully: LISTENER
Oracle Restart was already stopped and de-configured on node "san"
Oracle Restart is stopped and de-configured successfully.
Successfully detached Oracle home '/u01/app/oracle/product/12.1.0/grid' from the central inventory on the local node.
Successfully deleted directory '/u01/app/oracle/product/12.1.0/grid' on the local node.
Successfully deleted directory '/u01/app/oraInventory' on the local node.
Oracle Universal Installer cleanup was successful.

Run 'rm -rf /etc/oraInst.loc' as root on node(s) 'san' at the end of the session.
Run 'rm -rf /opt/ORCLfmap' as root on node(s) 'san' at the end of the session.
Run 'rm -rf /etc/oratab' as root on node(s) 'san' at the end of the session.
Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################

############# ORACLE DEINSTALL & DECONFIG TOOL END #############

RUN AS ROOT

[root@san ~]# /tmp/deinstall2013-08-06_05-58-34AM/perl/bin/perl -I/tmp/deinstall2013-08-06_05-58-34AM/perl/lib -I/tmp/deinstall2013-08-06_05-58-34AM/crs/install /tmp/deinstall2013-08-06_05-58-34AM/crs/install/roothas.pl -force -deconfig -paramfile “/tmp/deinstall2013-08-06_05-58-34AM/response/deinstall_OraGI12Home1.rsp”
Using configuration parameter file: /tmp/deinstall2013-08-06_05-58-34AM/response/deinstall_OraGI12Home1.rsp

PRKO-2573 : ONS daemon is already stopped.
CRS-4133: Oracle High Availability Services has been stopped.
2013/08/06 06:03:21 CLSRSC-337: Successfully deconfigured Oracle Restart stack

[root@san ~]# rm -rf /opt/ORCLfmap
[root@san ~]# rm -rf /u01/app/oracle/diag

Next Page »

Create a free website or blog at WordPress.com.