Michael Dinh
GoldenGate 12.2 discard vs exception table
I have seen several blogs demonstrating using database exception table for error handling.
Truth be told, I have never used the exception table method and curious to know what’s the difference.
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
First, there’s manual work to create database exception table for error handling.
Let’ me know how long it takes you to configure database exception table for error handling.
Second, with new features, you may need to update database exception table for error handling.
Third, there is no values for bind variables.
Discard functionality is built-in and with OGG 12.2 and the missing delete can easily be converted into insert.
Which method will you be implementing and why?
$ cat dirrpt/r_hawk.dsc
Oracle GoldenGate Delivery for Oracle process started, group R_HAWK discard file opened: 2016-06-11 13:04:48.441744 Current time: 2016-06-11 13:09:07 OCI Error ORA-26787: The row with key ("CUST_CODE", "ORDER_DATE", "ORDER_ID", "PRODUCT_CODE") = (JANE, 1995-11-11:13:52:00, 256, PLANE) does not exist in table DEMO.TCUSTORD ORA-01403: no data found Operation failed at seqno 0 rba 190519 Discarding record on action DISCARD on error 1403 Problem replicating DEMO.TCUSTORD to DEMO.TCUSTORD Record not found Mapping problem with delete record (target format)... * CUST_CODE = JANE 000000: 4a 41 4e 45 |JANE | ORDER_DATE = 1995-11-11 13:52:00 000000: 31 39 39 35 2d 31 31 2d 31 31 20 31 33 3a 35 32 |1995-11-11 13:52| 000010: 3a 30 30 |:00 | PRODUCT_CODE = PLANE 000000: 50 4c 41 4e 45 |PLANE | ORDER_ID = 256 000000: 32 35 36 |256 | PRODUCT_PRICE = 133300.00 000000: 31 33 33 33 30 30 2e 30 30 |133300.00 | PRODUCT_AMOUNT = 1 000000: 31 |1 | TRANSACTION_ID = 100 000000: 31 30 30 |100 | * Current time: 2016-06-11 13:09:07 OCI Error ORA-26787: The row with key ("CUST_CODE", "ORDER_DATE", "ORDER_ID", "PRODUCT_CODE") = (WILL, 1994-09-30:15:33:00, 144, CAR) does not exist in table DEMO.TCUSTORD ORA-01403: no data found Operation failed at seqno 0 rba 190727 Discarding record on action DISCARD on error 1403 Problem replicating DEMO.TCUSTORD to DEMO.TCUSTORD Record not found Mapping problem with delete record (target format)... * CUST_CODE = WILL 000000: 57 49 4c 4c |WILL | ORDER_DATE = 1994-09-30 15:33:00 000000: 31 39 39 34 2d 30 39 2d 33 30 20 31 35 3a 33 33 |1994-09-30 15:33| 000010: 3a 30 30 |:00 | PRODUCT_CODE = CAR 000000: 43 41 52 |CAR | ORDER_ID = 144 000000: 31 34 34 |144 | PRODUCT_PRICE = 17520.00 000000: 31 37 35 32 30 2e 30 30 |17520.00 | PRODUCT_AMOUNT = 3 000000: 33 |3 | TRANSACTION_ID = 100 000000: 31 30 30 |100 | * Current time: 2016-06-11 13:09:07 OCI Error ORA-26787: The row with key ("CUST_CODE") = (JANE) does not exist in table DEMO.TCUSTMER ORA-01403: no data found Operation failed at seqno 0 rba 190905 Discarding record on action DISCARD on error 1403 Problem replicating DEMO.TCUSTMER to DEMO.TCUSTMER Record not found Mapping problem with delete record (target format)... * CUST_CODE = JANE 000000: 4a 41 4e 45 |JANE | NAME = ROCKY FLYER INC. 000000: 52 4f 43 4b 59 20 46 4c 59 45 52 20 49 4e 43 2e |ROCKY FLYER INC.| CITY = DENVER 000000: 44 45 4e 56 45 52 |DENVER | STATE = CO 000000: 43 4f |CO | * Current time: 2016-06-11 13:09:07 OCI Error ORA-26787: The row with key ("CUST_CODE") = (WILL) does not exist in table DEMO.TCUSTMER ORA-01403: no data found Operation failed at seqno 0 rba 191043 Discarding record on action DISCARD on error 1403 Problem replicating DEMO.TCUSTMER to DEMO.TCUSTMER Record not found Mapping problem with delete record (target format)... * CUST_CODE = WILL 000000: 57 49 4c 4c |WILL | NAME = BG SOFTWARE CO. 000000: 42 47 20 53 4f 46 54 57 41 52 45 20 43 4f 2e |BG SOFTWARE CO. | CITY = SEATTLE 000000: 53 45 41 54 54 4c 45 |SEATTLE | STATE = WA 000000: 57 41 |WA | * Process Abending : 2016-06-11 13:09:24
select * from exceptions
ARROW:(GGS_ADMIN@thor):PRIMARY> @pr "select * from exceptions"; REP_NAME : R_HAWK TABLE_NAME : DEMO.TCUSTORD ERRNO : 1403 DBERRMSG : OCI Error ORA-01403: no data found, SQL OPTYPE : DELETE ERRTYPE : DB LOGRBA : 52 LOGPOSITION : 25819152 COMMITTIMESTAMP : 11-JUN-16 01.17.37.000416 PM ------------------------- REP_NAME : R_HAWK TABLE_NAME : DEMO.TCUSTORD ERRNO : 1403 DBERRMSG : OCI Error ORA-01403: no data found, SQL OPTYPE : DELETE ERRTYPE : DB LOGRBA : 52 LOGPOSITION : 25819772 COMMITTIMESTAMP : 11-JUN-16 01.17.37.000416 PM ------------------------- REP_NAME : R_HAWK TABLE_NAME : DEMO.TCUSTMER ERRNO : 1403 DBERRMSG : OCI Error ORA-01403: no data found, SQL OPTYPE : DELETE ERRTYPE : DB LOGRBA : 52 LOGPOSITION : 25821712 COMMITTIMESTAMP : 11-JUN-16 01.17.37.000416 PM ------------------------- REP_NAME : R_HAWK TABLE_NAME : DEMO.TCUSTMER ERRNO : 1403 DBERRMSG : OCI Error ORA-01403: no data found, SQL OPTYPE : DELETE ERRTYPE : DB LOGRBA : 52 LOGPOSITION : 25822128 COMMITTIMESTAMP : 11-JUN-16 01.17.37.000416 PM ------------------------- PL/SQL procedure successfully completed.
Convert DELETE into INSERT
Not all the columns’ value are available and because I did not configured properly.
ARROW:(DEMO@thor):PRIMARY> desc DEMO.TCUSTORD
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
CUST_CODE NOT NULL VARCHAR2(4)
ORDER_DATE NOT NULL DATE
PRODUCT_CODE NOT NULL VARCHAR2(8)
ORDER_ID NOT NULL NUMBER
PRODUCT_PRICE NUMBER(8,2)
PRODUCT_AMOUNT NUMBER(6)
TRANSACTION_ID NUMBER
ARROW:(DEMO@thor):PRIMARY> select count(*) from DEMO.TCUSTORD
2
ARROW:(DEMO@thor):PRIMARY> select count(*) from DEMO.TCUSTORD;
COUNT(*)
----------
0
ARROW:(DEMO@thor):PRIMARY> insert into DEMO.TCUSTORD ("CUST_CODE", "ORDER_DATE", "ORDER_ID", "PRODUCT_CODE")
2 values ('JANE', '1995-11-11:13:52:00', 256, 'PLANE');
1 row created.
ARROW:(DEMO@thor):PRIMARY> select count(*) from DEMO.TCUSTORD;
COUNT(*)
----------
1
ARROW:(DEMO@thor):PRIMARY>
GoldenGate 12.2 checkprm and oggerr
At first, I was wondering what’s the true benefit of the new feature (checkprm) since there exists CHECKPARAMS which does this.
For someone lazy like me, it means not having to modify code to add and remove CHECKPARAMS from GoldenGate parameter files.
Once, there was oerr and now there is oggerr to look up error codes.
Reference: Oracle GoldenGate Release Notes for 12c (12.2.0.1)
$ checkprm dirprm/e_hawk.prm
2016-06-11 07:29:47 INFO OGG-02095 Successfully set environment variable ORACLE_SID=hawk.
2016-06-11 07:29:47 INFO OGG-02095 Successfully set environment variable ORACLE_HOME=/u01/app/oracle/product/11.2.0/se_1.
(e_hawk.prm) line 15: Parameter [UPDATEMETADATA] is not valid for this configuration.
2016-06-11 07:29:47 INFO OGG-10139 Parameter file dirprm/e_hawk.prm: Validity check: FAIL.
$ cat dirprm/e_hawk.prm
EXTRACT e_hawk
-- CHECKPARAMS
USERIDALIAS ggs_user
EXTTRAIL ./dirdat/aa
INCLUDE ./dirprm/global_ggenv_se.inc
-- max_sga_size 1G per IE or IR process then add 25%, not to exeed 3.5G
-- max_sga_size > 3.5G not recommended
-- parallelism 1 is for standard edition database
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 128, parallelism 1)
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
DDL INCLUDE ALL &
EXCLUDE OBJTYPE 'SEQUENCE' &
EXCLUDE UNMAPPED OBJNAME GGS_ADMIN.*
DDLOPTIONS UPDATEMETADATA, REPORT
WARNLONGTRANS 15m, CHECKINTERVAL 5m
TABLE DEMO.*;
$ checkprm dirprm/e_hawk.prm
2016-06-11 07:30:31 INFO OGG-02095 Successfully set environment variable ORACLE_SID=hawk. 2016-06-11 07:30:31 INFO OGG-02095 Successfully set environment variable ORACLE_HOME=/u01/app/oracle/product/11.2.0/se_1. 2016-06-11 07:30:31 INFO OGG-10139 Parameter file dirprm/e_hawk.prm: Validity check: PASS. Runtime parameter validation is not reflected in the above check.
$ oerr ora 01403
01403, 00000, "no data found" // *Cause: No data was found from the objects. // *Action: There was no data from the objects which may be due to end of fetch.
$ oggerr ogg 06439
06439, 00000, "No unique key is defined for table {0}. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key."
// *{0}: tableObjName (Object name)
// *Cause: No unique key is defined for the specified table.
// *Action: Use a KEYCOLS clause in TABLE or MAP to define a key that contains
// unique values. Using a defined key ensures uniqueness and improves
// performance.
$ oggerr ogg 01296
01296, 00000, "Error mapping from {0} to {1}"
// *{0}: sourceTable (Qualified table name)
// *{1}: targetTable (Qualified table name)
// *Cause: The mapping of the specified source and target tables failed.
// *Action: Examine the accompanying messages that provide details about the
// mapping failure, and resolve the problem based on those messages.
// If the problem persists, contact Oracle Support.
Oracle PSU Pain and Predicament
Here is a list of download options for APR2016 (11.2.0.4.160419) PSU:
OJVM PSU (Linux/Unix) 22674697
Combo OJVM + DB PSU 22738777
Combo OJVM + DB SPU 22738732
Combo OJVM + GI PSU 22738793
That’s not enough and there should be more (sarcasm).
FYI: GI PSU includes DB and DB PSU does not include GI.
There is a thread from MOS – OJVM PSU and RAC: What happened to high availability? and I was wondering the same.
One document says do A then B while another has do B then A.
Oracle Recommended Patches — “Oracle JavaVM Component Database PSU” (OJVM PSU) Patches (Doc ID 1929745.1)1. Shutdown databases and services on all nodes
2. Apply DB PSU (or equivalent) but DO NOT RUN DB PSU POST INSTALL STEPS
3. Apply OJVM PSU patch [see note-1 below]
4. October 2014 only for DB versions below 12.1.0.2: Apply the JDBC Patch [see note-2 below]
5. Run post install steps on all DBs in the patched home:
For 11.2.0.3 and 11.2.0.4 run the OJVM PSU post install steps followed by the DB PSU (or equivalent) post install steps.
6. Re-start any stopped databases / services running from this ORACLE_HOME
Patch 22738793 – Combo of OJVM Component 11.2.0.4.160419 DB PSU + GI PSU 11.2.0.4.160419 (Apr2016) README has the opposite.SQL> @catbundle.sql psu apply
SQL> @postinstall.sql
When opatch auto is used for Combo OJVM + GI PSU 22738793, database will be shutdown and may not start up.
PRCH-1061 Applying OCT2014 Grid PSU 19380115 With Opatch Auto (Doc ID 1946048.1)
srvctl stop/status home creates empty file after applying oct2014 psu 19380115 (Doc ID 1946050.1)
[root@arrow ~]# . oraenv <<< grid
ORACLE_SID = [root] ? The Oracle base has been set to /u01/app/oracle
[root@arrow ~]# which make ar ld nm
/usr/bin/make
/usr/bin/ar
/usr/bin/ld
/usr/bin/nm
[root@arrow ~]# export PATCH_TOP_DIR=/media/sf_linux_x64
[root@arrow ~]# $ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /tmp/ocm.rsp
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y
The OCM configuration response file (/tmp/ocm.rsp) was successfully created.
[root@arrow ~]# $ORACLE_HOME/OPatch/opatch auto $PATCH_TOP_DIR/22738793 -ocmrf /tmp/ocm.rsp
Executing /u01/app/11.2.0/grid/perl/bin/perl /u01/app/11.2.0/grid/OPatch/crs/patch11203.pl -patchdir /media/sf_linux_x64 -patchn 22738793 -ocmrf /tmp/ocm.rsp -paramfile /u01/app/11.2.0/grid/crs/install/crsconfig_params
This is the main log file: /u01/app/11.2.0/grid/cfgtoollogs/opatchauto2016-06-04_09-11-57.log
This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/app/11.2.0/grid/cfgtoollogs/opatchauto2016-06-04_09-11-57.report.log
2016-06-04 09:11:57: Starting Oracle Restart Patch Setup
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
Stopping RAC /u01/app/oracle/product/11.2.0/dbhome_1 ...
Stopped RAC /u01/app/oracle/product/11.2.0/dbhome_1 successfully
patch /media/sf_linux_x64/22738793/22502456 apply successful for home /u01/app/oracle/product/11.2.0/dbhome_1
patch /media/sf_linux_x64/22738793/22502549/custom/server/22502549 apply successful for home /u01/app/oracle/product/11.2.0/dbhome_1
patch /media/sf_linux_x64/22738793/22674697 apply successful for home /u01/app/oracle/product/11.2.0/dbhome_1
Stopping CRS...
Stopped CRS successfully
patch /media/sf_linux_x64/22738793/22502456 apply successful for home /u01/app/11.2.0/grid
patch /media/sf_linux_x64/22738793/22502549 apply successful for home /u01/app/11.2.0/grid
patch /media/sf_linux_x64/22738793/22502505 apply successful for home /u01/app/11.2.0/grid
Starting CRS...
CRS-4123: Oracle High Availability Services has been started.
Starting RAC /u01/app/oracle/product/11.2.0/dbhome_1 ...
Failed to start resources from database home /u01/app/oracle/product/11.2.0/dbhome_1
ERROR: Refer log file for more details.
opatch auto failed.
[root@arrow ~]# tail /u01/app/11.2.0/grid/cfgtoollogs/opatchauto2016-06-04_09-11-57.log
2016-06-04 09:30:23: Removing file /tmp/fileR3nOfE
2016-06-04 09:30:23: Successfully removed file: /tmp/fileR3nOfE
2016-06-04 09:30:23: /bin/su exited with rc=1
2016-06-04 09:30:23: /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl start home -o /u01/app/oracle/product/11.2.0/dbhome_1 -s
/u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/stophome.txt
output is PRCH-1001 : Failed to start resources in Oracle home /u01/app/oracle/product/11.2.0/dbhome_1
PRCH-1061 : The following resources specified in the state file do not exist: svc-hulk_svc, svc-thor_svc
2016-06-04 09:30:23: Failed to start resources from database home /u01/app/oracle/product/11.2.0/dbhome_1
2016-06-04 09:30:23: ERROR: Refer log file for more details.
[root@arrow ~]# tail /u01/app/11.2.0/grid/cfgtoollogs/opatchauto2016-06-04_09-11-57.report.log
33: /u01/app/11.2.0/grid/rdbms/install/rootadd_rdbms.sh : run as root
34: /u01/app/11.2.0/grid/crs/install/roothas.pl -patch : run as root
35: /u01/app/oracle/product/11.2.0/dbhome_1/bin/emctl start dbconsole
36: /u01/app/oracle/product/11.2.0/dbhome_1/bin/emctl start agent
37: /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl start home -o /u01/app/oracle/product/11.2.0/dbhome_1 -s /u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/stophome.txt
[root@arrow ~]# grep stop /u01/app/11.2.0/grid/cfgtoollogs/opatchauto2016-06-04_09-11-57.report.log
13: /u01/app/oracle/product/11.2.0/dbhome_1/bin/emctl stop dbconsole
14: /u01/app/oracle/product/11.2.0/dbhome_1/bin/emctl stop agent
18: /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl stop home -o /u01/app/oracle/product/11.2.0/dbhome_1 -s /u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/stophome.txt -f
37: /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl start home -o /u01/app/oracle/product/11.2.0/dbhome_1 -s /u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/stophome.txt
[root@arrow ~]#
Using DBMS_STREAMS_ADM To Cleanup GoldenGate
This is really messed up. I chose GoldenGate because I did not want to mess around with streams.
When using Integrated Capture or Delivery, then knowing streams is a prerequisites.
Apologies as the format is not pretty.
The QUEUE table was indeed missing and this is what I get for monkeying around.
To resolve the issue – exec DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
GGSCI (arrow.localdomain as ggs_admin@hawk) 3> unREGISTER EXTRACT e_hawk DATABASE 2016-05-23 19:16:32 ERROR OGG-08221 Cannot register or unregister EXTRACT E_HAWK because of the following SQL error: OCI Error ORA-24010: QUEUE "GGS_ADMIN"."OGG$Q_E_HAWK" does not exist ORA-06512: at "SYS.DBMS_APPLY_ADM_INTERNAL", line 468 ORA-06512: at "SYS.DBMS_APPLY_ADM", line 724 ORA-06512: at line 1 (status = 24010). GGSCI (arrow.localdomain as ggs_admin@hawk) 4> exit ARROW:(SYS@hawk):PRIMARY> select * from dba_capture; CAPTURE_NAME QUEUE_NAME QUEUE_OWNER ------------------------------ ------------------------------ ------------------------------ RULE_SET_NAME RULE_SET_OWNER CAPTURE_USER ------------------------------ ------------------------------ ------------------------------ START_SCN STATUS CAPTURED_SCN APPLIED_SCN USE FIRST_SCN ---------- -------- ------------ ----------- --- ---------- SOURCE_DATABASE ---------------------------------------------------------------------------------------------------- SOURCE_DBID SOURCE_RESETLOGS_SCN SOURCE_RESETLOGS_TIME LOGMINER_ID NEGATIVE_RULE_SET_NAME ----------- -------------------- --------------------- ----------- ------------------------------ NEGATIVE_RULE_SET_OWNER MAX_CHECKPOINT_SCN REQUIRED_CHECKPOINT_SCN LOGFILE_ STATUS_CH ------------------------------ ------------------ ----------------------- -------- --------- ERROR_NUMBER ------------ ERROR_MESSAGE ---------------------------------------------------------------------------------------------------- VERSION CAPTURE_TY LAST_ENQUEUED_SCN ---------------------------------------------------------------- ---------- ----------------- CHECKPOINT_RETENTION_TIME ------------------------- START_TIME PURPOSE --------------------------------------------------------------------------- ------------------- CLIENT_NAME ---------------------------------------------------------------------------------------------------- CLIENT_S OLDEST_SCN FILTERED_SCN -------- ---------- ------------ OGG$CAP_E_HAWK OGG$Q_E_HAWK GGS_ADMIN GGS_ADMIN 256229 DISABLED 346591 346586 NO 256229 HAWK 3171223736 1 912525304 3 346420 346586 IMPLICIT 23-MAY-16 11.2.0.4.0 LOCAL 0 22-MAY-16 04.21.31.000000 PM GoldenGate Capture E_HAWK DISABLED 346586 255600 ARROW:(SYS@hawk):PRIMARY> exec DBMS_CAPTURE_ADM.STOP_CAPTURE('OGG$CAP_E_HAWK'); PL/SQL procedure successfully completed. ARROW:(SYS@hawk):PRIMARY> select * from dba_capture; CAPTURE_NAME QUEUE_NAME QUEUE_OWNER ------------------------------ ------------------------------ ------------------------------ RULE_SET_NAME RULE_SET_OWNER CAPTURE_USER ------------------------------ ------------------------------ ------------------------------ START_SCN STATUS CAPTURED_SCN APPLIED_SCN USE FIRST_SCN ---------- -------- ------------ ----------- --- ---------- SOURCE_DATABASE ---------------------------------------------------------------------------------------------------- SOURCE_DBID SOURCE_RESETLOGS_SCN SOURCE_RESETLOGS_TIME LOGMINER_ID NEGATIVE_RULE_SET_NAME ----------- -------------------- --------------------- ----------- ------------------------------ NEGATIVE_RULE_SET_OWNER MAX_CHECKPOINT_SCN REQUIRED_CHECKPOINT_SCN LOGFILE_ STATUS_CH ------------------------------ ------------------ ----------------------- -------- --------- ERROR_NUMBER ------------ ERROR_MESSAGE ---------------------------------------------------------------------------------------------------- VERSION CAPTURE_TY LAST_ENQUEUED_SCN ---------------------------------------------------------------- ---------- ----------------- CHECKPOINT_RETENTION_TIME ------------------------- START_TIME PURPOSE --------------------------------------------------------------------------- ------------------- CLIENT_NAME ---------------------------------------------------------------------------------------------------- CLIENT_S OLDEST_SCN FILTERED_SCN -------- ---------- ------------ OGG$CAP_E_HAWK OGG$Q_E_HAWK GGS_ADMIN GGS_ADMIN 256229 DISABLED 346591 346586 NO 256229 HAWK 3171223736 1 912525304 3 346420 346586 IMPLICIT 23-MAY-16 11.2.0.4.0 LOCAL 0 22-MAY-16 04.21.31.000000 PM GoldenGate Capture E_HAWK DISABLED 346586 255600 ARROW:(SYS@hawk):PRIMARY> ARROW:(SYS@hawk):PRIMARY> exec DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION(); PL/SQL procedure successfully completed. ARROW:(SYS@hawk):PRIMARY> select * from dba_capture; no rows selected ARROW:(SYS@hawk):PRIMARY>
GoldenGate 12.2 Patch 17030189 required Integrated trail format RELEASE 12.2 or later
EXTRACT Abending With OGG-02912 (Doc ID 2091679.1)
Alternate script prvtlmpg.plb (included in the Oracle GoldenGate installation directory) to the mining database to work around this limitation.
oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$ ll prv*
-rw-r-----. 1 oracle oinstall 1272 Dec 28 2010 prvtclkm.plb
-rw-r-----. 1 oracle oinstall 9487 May 27 2015 prvtlmpg.plb
-rw-r-----. 1 oracle oinstall 3263 May 27 2015 prvtlmpg_uninstall.sql
oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$
The other option in this case would be to request a backport since patch is not available for all database 11g releases.
Implementing work around.
oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01 $ sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun May 22 15:23:27 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production ARROW:(SYS@hawk):PRIMARY> @prvtlmpg.plb Oracle GoldenGate Workaround prvtlmpg This script provides a temporary workaround for bug 17030189. It is strongly recommended that you apply the official Oracle Patch for bug 17030189 from My Oracle Support instead of using this workaround. This script must be executed in the mining database of Integrated Capture. You will be prompted for the username of the mining user. Use a double quoted identifier if the username is case sensitive or contains special characters. In a CDB environment, this script must be executed from the CDB$ROOT container and the mining user must be a common user. =========================== WARNING ========================== You MUST stop all Integrated Captures that belong to this mining user before proceeding! ================================================================ Enter Integrated Capture mining user: ggs_admin Installing workaround... No errors. No errors. No errors. Installation completed. ARROW:(SYS@hawk):PRIMARY> exit Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01 $ oracle@arrow:hawk:/u01/app/oracle/product/11.2.0/se_1/dbs $ opatch lsinventory Oracle Interim Patch Installer version 11.2.0.3.4 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0/se_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/11.2.0/se_1/oraInst.loc OPatch version : 11.2.0.3.4 OUI version : 11.2.0.4.0 Log file location : /u01/app/oracle/product/11.2.0/se_1/cfgtoollogs/opatch/opatch2016-05-22_15-26-10PM_1.log Lsinventory Output file location : /u01/app/oracle/product/11.2.0/se_1/cfgtoollogs/opatch/lsinv/lsinventory2016-05-22_15-26-10PM.txt -------------------------------------------------------------------------------- Installed Top-level Products (1): Oracle Database 11g 11.2.0.4.0 There are 1 products installed in this Oracle Home. There are no Interim patches installed in this Oracle Home. -------------------------------------------------------------------------------- OPatch succeeded. oracle@arrow:hawk:/u01/app/oracle/product/11.2.0/se_1/dbs $
Create GoldenGate 12.2 Manager
I typically don’t like to see WARNING if I can help it.
Goldengate 12c has some security features to allow/prevent unauthorized access.
Be careful. Incorrect IPADDR or PROG is used will prevent Pump Extract delivery to target server.
oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01 $ tail -100 ggserr.log 2016-05-22 12:25:07 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start mgr. 2016-05-22 12:25:07 WARNING OGG-01877 Oracle GoldenGate Manager for Oracle, mgr.prm: Missing explicit accessrule for server collector. 2016-05-22 12:25:07 INFO OGG-00960 Oracle GoldenGate Manager for Oracle, mgr.prm: Access granted (rule #7). 2016-05-22 12:25:07 INFO OGG-00983 Oracle GoldenGate Manager for Oracle, mgr.prm: Manager started (port 7901). 2016-05-22 12:25:09 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all. 2016-05-22 12:25:46 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all. 2016-05-22 12:25:51 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): stop mgr. 2016-05-22 12:25:51 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host [127.0.0.1]:39551 (STOP). 2016-05-22 12:25:51 INFO OGG-00960 Oracle GoldenGate Manager for Oracle, mgr.prm: Access granted (rule #7). 2016-05-22 12:25:51 WARNING OGG-00938 Oracle GoldenGate Manager for Oracle, mgr.prm: Manager is stopping at user request. 2016-05-22 12:26:00 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start mgr. 2016-05-22 12:26:00 INFO OGG-00960 Oracle GoldenGate Manager for Oracle, mgr.prm: Access granted (rule #2). 2016-05-22 12:26:00 INFO OGG-00983 Oracle GoldenGate Manager for Oracle, mgr.prm: Manager started (port 7901). oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01 $ cat dirprm/mgr.prm PORT 7901 DYNAMICPORTLIST 15100-15120 ACCESSRULE, PROG server, IPADDR *, ALLOW ACCESSRULE, PROG *, IPADDR *, ALLOW USERIDALIAS ggs_admin PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3 -- AUTOSTART ER * -- AUTORESTART ER *, RETRIES 5, WAITMINUTES 2, RESETMINUTES 60 CHECKMINUTES 5 LAGCRITICALMINUTES 15 oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01 $
Create GoldenGate 12.2 Wallet
So what’s different from this post versus other posts? I share my mistakes with you.
Did you know there was a DEFAULT domain? If you didn’t, neither did I and only found out by using
info credentialstore
alter credentialstore add user ggs_admin alias ggs_admin domain admin
USERIDALIAS ggs_admin DOMAIN admin
alter credentialstore add user ggs_admin alias ggs_admin
USERIDALIAS ggs_admin
oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (arrow.localdomain) 1> create wallet
Created wallet at location 'dirwlt'.
Opened wallet at location 'dirwlt'.
GGSCI (arrow.localdomain) 2> add credentialstore
Credential store created in ./dircrd/.
GGSCI (arrow.localdomain) 3> alter credentialstore add user ggs_admin alias ggs_admin domain admin
Password:
Credential store in ./dircrd/ altered.
GGSCI (arrow.localdomain) 4> info credentialstore
Reading from ./dircrd/:
No information found in default domain OracleGoldenGate.
Other domains:
admin
To view other domains, use INFO CREDENTIALSTORE DOMAIN <domain>
GGSCI (arrow.localdomain) 5> info credentialstore domain admin
Reading from ./dircrd/:
Domain: admin
Alias: ggs_admin
Userid: ggs_admin
GGSCI (arrow.localdomain) 6> exit
oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (arrow.localdomain) 1> alter credentialstore add user ggs_admin alias ggs_admin
Password:
Credential store in ./dircrd/ altered.
GGSCI (arrow.localdomain) 2> info credentialstore
Reading from ./dircrd/:
Default domain: OracleGoldenGate
Alias: ggs_admin
Userid: ggs_admin
Other domains:
admin
To view other domains, use INFO CREDENTIALSTORE DOMAIN
GGSCI (arrow.localdomain) 3> exit
oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$
Automating DG Broker
I have been applying PSU lately and what’s so hard out it?
Four+ databases running on Primary with DG Broker for standby.
There are no conventions, as some standby databases have dr appended to primary name while others have 2 appended to primary name.
I wanted to view the DG configuration for currently active instances and show_dg_config.sh will show me this.
Next, I want a faster way to shutdown DG by having syntax generated and gen_dg_cmd.sh does this.
Guess I could have taken it further by creating a shell script to create shell scripts to shutdown DG.
One day when I am really bore, I might OR may be you are so nice to complete my mission.
Tested on AIX 7.1
Note: the ps -ef syntax is for AIX and will not work with Linux.
See below for the Linux alternative.
$ ps -ef -o args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ
$ ps -eo args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'
thor
hulk
show_dg_config.sh
#!/bin/sh -e
ps -ef -o args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'
export ORAENV_ASK=NO
for SID in ps -ef -o args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'`
do
export ORACLE_SID=$SID
. /usr/local/bin/oraenv
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
echo "+++: " $ORACLE_SID $ORACLE_HOME
sysresv
dgmgrl -echo << END
connect /
show configuration
exit
END
done
exit
gen_dg_cmd.sh
#!/bin/sh -e
for XB in `egrep 'Primary|Physical' /tmp/dg.log |sort |awk -F" " '{print $3 $1}'`
do
#echo $XB
#echo $XB|awk '{print substr($1,1,7)}'
if [ "`echo $XB|awk '{print substr($1,1,7)}'`" == "Primary" ]
then
PRI=`echo $XB|awk '{print substr($1,8)}'`
echo "edit database $PRI set state='LOG-TRANSPORT-OFF';"
echo "show database $PRI"
echo "edit database $PRI set state='ONLINE';"
echo "show database $PRI"
fi
if [ "`echo $XB|awk '{print substr($1,1,8)}'`" == "Physical" ]
then
SBY=`echo $XB|awk '{print substr($1,9)}'`
echo "edit database $SBY set state='APPLY-OFF';"
echo "show database $SBY"
echo "edit database $SBY set state='APPLY-ON';"
echo "show database $SBY"
fi
done
exit
./show_dg_config.sh > /tmp/dg.log
egrep ‘Primary|Physical’ /tmp/dg.log |sort |awk -F” ” ‘{print $3 $1}’
Primarydb02 Physicaldb02dr Primarydb01 Physicaldb01dr Primarystageqa Physicalstageqa2 Primarytest Physicaltestdr
./gen_dg_cmd.sh
edit database db01 set state='LOG-TRANSPORT-OFF'; show database db01 edit database db01 set state='ONLINE'; show database db01 edit database db01dr set state='APPLY-OFF'; show database db01dr edit database db01dr set state='APPLY-ON'; show database db01dr edit database db02 set state='LOG-TRANSPORT-OFF'; show database db02 edit database db02 set state='ONLINE'; show database db02 edit database db02dr set state='APPLY-OFF'; show database db02dr edit database db02dr set state='APPLY-ON'; show database db02dr edit database stageqa set state='LOG-TRANSPORT-OFF'; show database stageqa edit database stageqa set state='ONLINE'; show database stageqa edit database stageqa2 set state='APPLY-OFF'; show database stageqa2 edit database stageqa2 set state='APPLY-ON'; show database stageqa2 edit database test set state='LOG-TRANSPORT-OFF'; show database test edit database test set state='ONLINE'; show database test edit database testdr set state='APPLY-OFF'; show database testdr edit database testdr set state='APPLY-ON'; show database testdr oracle:/home/oracle/working/dinh$
Create GoldenGate 12.2 Database User
Oracle GoldenGate for Windows and UNIX 12c (12.2.0.1)
First, I am disappointed that Oracle does not go above and beyond to provide SQL scripts to create GoldenGate users for the database.
There are different set of privileges depending on the version of the database:
4.1.4.2 Oracle 11.2.0.3 or Earlier Database Privileges 4.1.4.1 Oracle 11.2.0.4 or Later Database Privileges
PDB is not being used and it’s different for PDB.
Depending on whether you want to practice the least principle privileges, ggadmin user can be create with privileges for both extract (capture) and replicat (apply).
Please don’t forget to change the password from the script since it is hard coded to be same as username :=)
cr_ggadmin_12c.sql
-- 4.1.4.1 Oracle 11.2.0.4 or Later Database Privileges
set echo on lines 200 pages 1000 trimspool on tab off
define _username='GGADMIN'
-- grant privileges for capture
create user &_username identified by &_username default tablespace ggdata;
select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='&_username';
grant create session, connect, resource, alter any table, alter system, dba, select any transaction to &_username;
-- grant privileges for replicat
grant create table, lock any table to &_username;
-- grant both capture and apply
exec dbms_goldengate_auth.grant_admin_privilege('&_username')
-- grant capture
-- exec dbms_goldengate_auth.grant_admin_privilege('&_username','capture');
-- grant apply
-- exec dbms_goldengate_auth.grant_admin_privilege('&_username','apply');
Demo:
oracle@arrow:tiger:/media/sf_working/ggs
$ sysdba @cr_ggadmin_12c.sql
SQL*Plus: Release 11.2.0.4.0 Production on Sat Apr 9 07:06:41 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ARROW:(SYS@tiger):PRIMARY> define _username='GGADMIN'
ARROW:(SYS@tiger):PRIMARY> -- grant privileges for capture
ARROW:(SYS@tiger):PRIMARY> create user &_username identified by &_username default tablespace ggdata;
User created.
ARROW:(SYS@tiger):PRIMARY> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='&_username';
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------
GGDATA TEMP
ARROW:(SYS@tiger):PRIMARY> grant create session, connect, resource, alter any table, alter system, dba, select any transaction to &_username;
Grant succeeded.
ARROW:(SYS@tiger):PRIMARY> -- grant privileges for replicat
ARROW:(SYS@tiger):PRIMARY> grant create table, lock any table to &_username;
Grant succeeded.
ARROW:(SYS@tiger):PRIMARY> -- grant both capture and apply
ARROW:(SYS@tiger):PRIMARY> exec dbms_goldengate_auth.grant_admin_privilege('&_username')
PL/SQL procedure successfully completed.
ARROW:(SYS@tiger):PRIMARY> -- grant capture
ARROW:(SYS@tiger):PRIMARY> -- exec dbms_goldengate_auth.grant_admin_privilege('&_username','capture');
ARROW:(SYS@tiger):PRIMARY> -- grant apply
ARROW:(SYS@tiger):PRIMARY> -- exec dbms_goldengate_auth.grant_admin_privilege('&_username','apply');
ARROW:(SYS@tiger):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:tiger:/media/sf_working/ggs
$
Clone GoldenGate 12.2
This is what insomnia does to you.
Since GoldenGate installation is now using runInstaller, I would expect the same functionality as the database; hence, cloning GoldenGate Home.
oracle@arrow:pooh:/home/oracle $ cd /u01/app/12.2.0.1/ggs01/ oracle@arrow:pooh:/u01/app/12.2.0.1/ggs01 $ ls bcpfmt.tpl ddl_ora10.sql defgen extract libggnnzitp.so marker_status.sql role_setup.sql bcrypt.txt ddl_ora10upCommon.sql deinstall freeBSD.txt libggparam.so mgr sequence.sql cachefiledump ddl_ora11.sql demo_more_ora_create.sql ggcmd libggperf.so notices.txt server cfgtoollogs ddl_ora9.sql demo_more_ora_insert.sql ggMessage.dat libggrepo.so oggerr sqlldr.tpl checkprm ddl_pin.sql demo_ora_create.sql ggparam.dat libicudata.so.48 OPatch srvm chkpt_ora_create.sql ddl_remove.sql demo_ora_insert.sql ggsci libicudata.so.48.1 oraInst.loc tcperrs convchk ddl_session1.sql demo_ora_lob_create.sql help.txt libicui18n.so.48 oui ucharset.h convprm ddl_session.sql demo_ora_misc.sql install libicui18n.so.48.1 params.sql ulg.sql db2cntl.tpl ddl_setup.sql demo_ora_pk_befores_create.sql inventory libicuuc.so.48 prvtclkm.plb UserExitExamples ddl_cleartrace.sql ddl_status.sql demo_ora_pk_befores_insert.sql jdk libicuuc.so.48.1 prvtlmpg.plb usrdecs.h ddl_create.sql ddl_staymetadata_off.sql demo_ora_pk_befores_updates.sql keygen libxerces-c.so.28 prvtlmpg_uninstall.sql zlib.txt ddl_ddl2file.sql ddl_staymetadata_on.sql diagnostics label.sql libxml2.txt remove_seq.sql ddl_disable.sql ddl_tracelevel.sql dirout libantlr3c.so logdump replicat ddl_enable.sql ddl_trace_off.sql dirwww libdb-6.1.so marker_remove.sql retrace ddl_filter.sql ddl_trace_on.sql emsclnt libgglog.so marker_setup.sql reverse oracle@arrow:pooh:/u01/app/12.2.0.1/ggs01 $ nohup tar -cvpf /tmp/ggs12c_${USER}_`hostname -s`_clone.tar . > /tmp/clone_${USER}_`hostname -s`_ggs12c.log 2>&1 & [1] 10859 oracle@arrow:pooh:/u01/app/12.2.0.1/ggs01 $ [1]+ Done nohup tar -cvpf /tmp/ggs12c_${USER}_`hostname -s`_clone.tar . > /tmp/clone_${USER}_`hostname -s`_ggs12c.log 2>&1 oracle@arrow:pooh:/u01/app/12.2.0.1/ggs01 $ cd .. oracle@arrow:pooh:/u01/app/12.2.0.1 $ mkdir ggs02 oracle@arrow:pooh:/u01/app/12.2.0.1 $ cd ggs02/ oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02 $ ls oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02 $ nohup tar -xvf /tmp/ggs12c_oracle_arrow_clone.tar > /tmp/untar_${USER}_`hostname -s`_ggs12c.log 2>&1 & [1] 10897 oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02 $ [1]+ Done nohup tar -xvf /tmp/ggs12c_oracle_arrow_clone.tar > /tmp/untar_${USER}_`hostname -s`_ggs12c.log 2>&1 oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02 $ ll /tmp/*ggs* -rw-r--r--. 1 oracle oinstall 84131 Apr 4 01:36 /tmp/clone_oracle_arrow_ggs12c.log -rw-r--r--. 1 oracle oinstall 835788800 Apr 4 01:36 /tmp/ggs12c_oracle_arrow_clone.tar -rw-r--r--. 1 oracle oinstall 84131 Apr 4 01:45 /tmp/untar_oracle_arrow_ggs12c.log oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02 $ tail /tmp/clone_oracle_arrow_ggs12c.log ./jdk/bin/javadoc ./jdk/bin/jarsigner ./jdk/bin/pack200 ./jdk/bin/rmid ./jdk/bin/jrunscript ./jdk/bin/extcheck ./jdk/bin/keytool ./jdk/LICENSE ./jdk/.manifest ./ddl_setup.sql oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02 $ tail /tmp/untar_oracle_arrow_ggs12c.log ./jdk/bin/javadoc ./jdk/bin/jarsigner ./jdk/bin/pack200 ./jdk/bin/rmid ./jdk/bin/jrunscript ./jdk/bin/extcheck ./jdk/bin/keytool ./jdk/LICENSE ./jdk/.manifest ./ddl_setup.sql oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02 $ ls bcpfmt.tpl ddl_ora10.sql defgen extract libggnnzitp.so marker_status.sql role_setup.sql bcrypt.txt ddl_ora10upCommon.sql deinstall freeBSD.txt libggparam.so mgr sequence.sql cachefiledump ddl_ora11.sql demo_more_ora_create.sql ggcmd libggperf.so notices.txt server cfgtoollogs ddl_ora9.sql demo_more_ora_insert.sql ggMessage.dat libggrepo.so oggerr sqlldr.tpl checkprm ddl_pin.sql demo_ora_create.sql ggparam.dat libicudata.so.48 OPatch srvm chkpt_ora_create.sql ddl_remove.sql demo_ora_insert.sql ggsci libicudata.so.48.1 oraInst.loc tcperrs convchk ddl_session1.sql demo_ora_lob_create.sql help.txt libicui18n.so.48 oui ucharset.h convprm ddl_session.sql demo_ora_misc.sql install libicui18n.so.48.1 params.sql ulg.sql db2cntl.tpl ddl_setup.sql demo_ora_pk_befores_create.sql inventory libicuuc.so.48 prvtclkm.plb UserExitExamples ddl_cleartrace.sql ddl_status.sql demo_ora_pk_befores_insert.sql jdk libicuuc.so.48.1 prvtlmpg.plb usrdecs.h ddl_create.sql ddl_staymetadata_off.sql demo_ora_pk_befores_updates.sql keygen libxerces-c.so.28 prvtlmpg_uninstall.sql zlib.txt ddl_ddl2file.sql ddl_staymetadata_on.sql diagnostics label.sql libxml2.txt remove_seq.sql ddl_disable.sql ddl_tracelevel.sql dirout libantlr3c.so logdump replicat ddl_enable.sql ddl_trace_off.sql dirwww libdb-6.1.so marker_remove.sql retrace ddl_filter.sql ddl_trace_on.sql emsclnt libgglog.so marker_setup.sql reverse oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02 $ cd oui/bin/ oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02/oui/bin $ ls run* runConfig.sh runInstaller runInstaller.sh runSSHSetup.sh oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02/oui/bin $ ./runInstaller -clone -silent -noconfig -defaultHomeName ORACLE_HOME="/u01/app/12.2.0.1/ggs02" Starting Oracle Universal Installer... Checking swap space: must be greater than 500 MB. Actual 4072 MB Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-04-04_01-50-47AM. Please wait ...oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02/oui/bin $ Oracle Universal Installer, Version 11.2.0.3.0 Production Copyright (C) 1999, 2011, Oracle. All rights reserved. You can find the log of this install session at: /u01/app/oraInventory/logs/cloneActions2016-04-04_01-50-47AM.log .................................................................................................... 100% Done. Installation in progress (Monday, April 4, 2016 1:50:56 AM PDT) .......................................................................... 72% Done. Install successful Linking in progress (Monday, April 4, 2016 1:50:59 AM PDT) Link successful Setup in progress (Monday, April 4, 2016 1:50:59 AM PDT) ........... 100% Done. Setup successful End of install phases.(Monday, April 4, 2016 1:51:20 AM PDT) The cloning of OraHome3 was successful. Please check '/u01/app/oraInventory/logs/cloneActions2016-04-04_01-50-47AM.log' for more details. oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02/oui/bin $ grep -e '[[:upper:]]: ' /u01/app/oraInventory/logs/cloneActions2016-04-04_01-50-47AM.log|cut -d ":" -f1|sort -u INFO oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02/oui/bin $ cd ../../ oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02 $ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38 Operating system character set identified as UTF-8. Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved. GGSCI (arrow.localdomain) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED GGSCI (arrow.localdomain) 2> show Parameter settings: SET SUBDIRS ON SET DEBUG OFF Current directory: /u01/app/12.2.0.1/ggs02 Using subdirectories for all process files Editor: vi Reports (.rpt) /u01/app/12.2.0.1/ggs02/dirrpt -- does not yet exist Parameters (.prm) /u01/app/12.2.0.1/ggs02/dirprm -- does not yet exist Replicat Checkpoints (.cpr) /u01/app/12.2.0.1/ggs02/dirchk -- does not yet exist Extract Checkpoints (.cpe) /u01/app/12.2.0.1/ggs02/dirchk -- does not yet exist Process Status (.pcs) /u01/app/12.2.0.1/ggs02/dirpcs -- does not yet exist SQL Scripts (.sql) /u01/app/12.2.0.1/ggs02/dirsql -- does not yet exist Database Definitions (.def) /u01/app/12.2.0.1/ggs02/dirdef -- does not yet exist Dump files (.dmp) /u01/app/12.2.0.1/ggs02/dirdmp -- does not yet exist Masterkey wallet files (.wlt) /u01/app/12.2.0.1/ggs02/dirwlt -- does not yet exist Credential store files (.crd) /u01/app/12.2.0.1/ggs02/dircrd -- does not yet exist GGSCI (arrow.localdomain) 3> create subdirs Creating subdirectories under current directory /u01/app/12.2.0.1/ggs02 Parameter files /u01/app/12.2.0.1/ggs02/dirprm: created Report files /u01/app/12.2.0.1/ggs02/dirrpt: created Checkpoint files /u01/app/12.2.0.1/ggs02/dirchk: created Process status files /u01/app/12.2.0.1/ggs02/dirpcs: created SQL script files /u01/app/12.2.0.1/ggs02/dirsql: created Database definitions files /u01/app/12.2.0.1/ggs02/dirdef: created Extract data files /u01/app/12.2.0.1/ggs02/dirdat: created Temporary files /u01/app/12.2.0.1/ggs02/dirtmp: created Credential store files /u01/app/12.2.0.1/ggs02/dircrd: created Masterkey wallet files /u01/app/12.2.0.1/ggs02/dirwlt: created Dump files /u01/app/12.2.0.1/ggs02/dirdmp: created GGSCI (arrow.localdomain) 4> exit oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02 $ There is no clone.pl for GoldenGate. oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02 $ locate clone.pl /u01/app/oracle/product/11.2.0/dbhome_1/clone/bin/clone.pl /u01/app/oracle/product/11.2.0/dbhome_1/clone/bin/prepare_clone.pl /u01/app/oracle/product/11.2.0/dbhome_1/sysman/admin/scripts/db/dbclone/db_clone.pl /u01/app/oracle/product/12.2.0/dbhome_2/clone/bin/clone.pl /u01/app/oracle/product/12.2.0/dbhome_2/clone/bin/clone.pl.sbs /u01/app/oracle/product/12.2.0/dbhome_2/clone/bin/clone.pl.sbs.ouibak /u01/app/oracle/product/12.2.0/dbhome_2/clone/bin/prepare_clone.pl /u01/app/oracle/product/12.2.0/dbhome_2/inventory/Templates/clone/bin/clone.pl.sbs oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02 $
oracle@arrow:pooh:/u01/app/12.2.0.1/ggs02
$ cat /u01/app/oraInventory/ContentsXML/inventory.xml
<?xml version=”1.0″ standalone=”yes” ?>
<!– Copyright (c) 1999, 2011, Oracle. All rights reserved. –>
<!– Do not modify the contents of this file by hand. –>
<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>11.2.0.3.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME=”OraDb11g_home1″ LOC=”/u01/app/oracle/product/11.2.0/dbhome_1″ TYPE=”O” IDX=”1″/>
<HOME NAME=”OraDB12Home1″ LOC=”/u01/app/oracle/product/12.2.0/dbhome_1″ TYPE=”O” IDX=”2″/>
<HOME NAME=”OraHome1″ LOC=”/u01/app/oracle/product/12.2.0/dbhome_2″ TYPE=”O” IDX=”3″/>
<HOME NAME=”OraHome2″ LOC=”/u01/app/12.2.0.1/ggs01″ TYPE=”O” IDX=”4″/>
<HOME NAME=”OraHome3″ LOC=”/u01/app/12.2.0.1/ggs02″ TYPE=”O” IDX=”5″/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>
Silent Install GoldenGate 12.2
There are probably many blogs about installation; however, I tend to do things a little different.
oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1 $ cat /media/sf_working/oggcore_11gdb.rsp oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2 INSTALL_OPTION=ORA11g SOFTWARE_LOCATION=/u01/app/12.2.0.1/ggs01 START_MANAGER= MANAGER_PORT= DATABASE_LOCATION=/u01/app/oracle/product/11.2.0/dbhome_1 INVENTORY_LOCATION=/u01/app/oraInventory UNIX_GROUP_NAME=oinstall I like to see installation progress. oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1 $ ./runInstaller -silent -showProgress -waitforcompletion -responseFile /media/sf_working/oggcore_11gdb.rsp Starting Oracle Universal Installer... Checking Temp space: must be greater than 120 MB. Actual 28152 MB Passed Checking swap space: must be greater than 150 MB. Actual 4073 MB Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-04-04_01-04-39AM. Please wait ...You can find the log of this install session at: /u01/app/oraInventory/logs/installActions2016-04-04_01-04-39AM.log Prepare in progress. .................................................. 10% Done. Prepare successful. Copy files in progress. .................................................. 61% Done. .................................................. 69% Done. .................................................. 77% Done. .................................................. 82% Done. .................................................. 87% Done. .................................................. 94% Done. .................................................. 95% Done. Copy files successful. Link binaries in progress. .................................................. 95% Done. Link binaries successful. Setup files in progress. .................................................. 95% Done. .................................................. 96% Done. .................................................. 97% Done. .................................................. 98% Done. .................................................. 99% Done. .................................................. 100% Done. Setup files successful. The installation of Oracle GoldenGate Core was successful. Please check '/u01/app/oraInventory/logs/silentInstall2016-04-04_01-04-39AM.log' for more details. Successfully Setup Software. oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1 $ cat /u01/app/oraInventory/logs/silentInstall2016-04-04_01-04-39AM.log silentInstall2016-04-04_01-04-39AM.log The installation of Oracle GoldenGate Core was successful. I like to see distinct log type: INFO|WARNING|ERROR. oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1 $ grep -e '[[:upper:]]: ' /u01/app/oraInventory/logs/installActions2016-04-04_01-04-39AM.log|cut -d ":" -f1|sort -u INFO WARNING I like to see what WARNING are all about and to compare with subsequent install. oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1 $ grep -e '[[:upper:]]: ' /u01/app/oraInventory/logs/installActions2016-04-04_01-04-39AM.log|grep "^WARNING: " WARNING: CVU is not enabled. No CVU based operation will be performed. WARNING: Unable to find the namespace URI. Reason: Start of root element expected. WARNING: Unable to find the namespace URI. Reason: Start of root element expected. WARNING: Unable to get the value for INSTALL_OPTION. Error: null WARNING: Unable to get the value for INSTALL_OPTION. Error: null WARNING: Unable to get the value for INSTALL_OPTION. Error: null WARNING: Validation disabled for the state init WARNING: Validation disabled for the state summary WARNING: Validation disabled for the state finish Is WARNING bogus? oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1 $ grep INSTALL_OPTION /u01/app/oraInventory/logs/installActions2016-04-04_01-04-39AM.log WARNING: Unable to get the value for INSTALL_OPTION. Error: null WARNING: Unable to get the value for INSTALL_OPTION. Error: null WARNING: Unable to get the value for INSTALL_OPTION. Error: null INFO: Setting value for the property:INSTALL_OPTION in the bean:OGGInstallSettings INSTALL_OPTION ORA11g INSTALL_OPTION ORA11g INFO: adding the variable INSTALL_OPTION to command line args table oracle@arrow:pooh:/media/sf_working/fbo_ggs_Linux_x64_shiphome/Disk1 $
Deinstall GoldenGate 12.2
I always like to know how to remove software installation in the event I am not happy with how it was installed.
GoldenGate Home is at /u01/app/oracle/12.2/ggs01
Thinking of may be using the same directory structures as Grid Infrastructure, i.e. /u01/app/12.2.0.1/ggs01 with full release version.
oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01 $ ls -ld deinstall/ drwxr-xr-x. 2 oracle oinstall 4096 Apr 4 00:24 deinstall/ oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01 $ cd deinstall/ oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01/deinstall $ ls deinstall.sh oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01/deinstall $ ./deinstall.sh ALERT: Ensure all the processes running from the current Oracle Home are shutdown prior to running this software uninstallation script. Proceed with removing Oracle GoldenGate home: /u01/app/oracle/12.2/ggs01 (yes/no)? [no] yes Starting Oracle Universal Installer... Checking swap space: must be greater than 500 MB. Actual 4073 MB Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-04-04_12-48-07AM. Please wait ...Oracle Universal Installer, Version 11.2.0.3.0 Production Copyright (C) 1999, 2011, Oracle. All rights reserved. Starting deinstall Deinstall in progress (Monday, April 4, 2016 12:48:12 AM PDT) ............................................................... 100% Done. Deinstall successful End of install phases.(Monday, April 4, 2016 12:48:33 AM PDT) End of deinstallations Please check '/u01/app/oraInventory/logs/silentInstall2016-04-04_12-48-07AM.log' for more details. oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01/deinstall $ cat /u01/app/oraInventory/logs/silentInstall2016-04-04_12-48-07AM.log silentInstall2016-04-04_12-48-07AM.log Starting deinstall This deinstallation was successful End of deinstallations oracle@arrow:pooh:/u01/app/oracle/12.2/ggs01/deinstall $
Easy Way to Create pfile from spfile
I learned something new today; so excited as this will make automation so much easier.
create pfile=’/tmp/init@.ora’ from spfile;
The @ automatically substitute SID.
oracle@arrow:tiger:/tmp
$ sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 3 09:45:49 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ARROW:(SYS@tiger):PRIMARY> set time on
09:45:52 ARROW:(SYS@tiger):PRIMARY> !ls -l /tmp/init*
ls: cannot access /tmp/init*: No such file or directory
09:45:59 ARROW:(SYS@tiger):PRIMARY> create pfile='/tmp/init@.ora' from spfile;
File created.
09:46:04 ARROW:(SYS@tiger):PRIMARY> !ls -l /tmp/init*
-rw-r--r--. 1 oracle oinstall 1165 Apr 3 09:46 /tmp/inittiger.ora
09:46:10 ARROW:(SYS@tiger):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:tiger:/tmp
$
Tablespace Free w Fragmentation Info
I have been conversing with Liron Amitzi on twitter about his tablespace free SQL script.
He accommodate my request to add fragmentation details so I can verify against what I have.
Looks like I did it right.
You can find Liron script at this blog – Tablespaces Free Space and Stuff
oracle@arrow:tiger:/media/sf_working/sql
$ sysdba @ts_free_space.sql
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 24 12:35:23 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
TABLESPACE_NAME CURR_SIZE MAX_SIZE FREE_SIZE USED_SIZE PCT_FREE PCT_FREE_TOTAL FREE_CHUNKS LARGEST_CHUNK
------------------------------ ------------ ------------ ------------ ------------ ------------ -------------- ------------ -------------
USERS 1024 1024 1021.38 2.62 99.74 99.74 2 1021.13
UNDOTBS1 80 32767 64.75 15.25 80.94 99.95 12 37
SYSAUX 400 32767 218.94 181.06 54.73 99.45 1 218.94
SYSTEM 500 32767 231.19 268.81 46.24 99.18 3 231
ARROW:(SYS@tiger):PRIMARY> @free.sql
Enter value for 1: *
Database Freespace Summary
TABLESPACE_NAME BLKSZ DFCT CT_FRAG MB_FREE_FRAG MB_FREE MB_TOTAL PCT_USED MAX_MB_FREE MAX_PCT_USED
----------------------------------- ------ ------ ------------ ------------ ----------- ------------ -------- ----------- ------------
*m s SYSTEM 8192 1 3 231 231 499 53.71 32,767 .82
*m s UNDOTBS1 8192 1 12 37 65 79 53.16 32,767 .13
*a s SYSAUX 8192 1 1 219 219 399 45.13 32,767 .55
*m s USERS 8192 1 2 1,021 1,021 1023 .18 1,023 .18
------ ----------- -----------
sum 4 1,536 99,324
ARROW:(SYS@tiger):PRIMARY>
free.sql
set line 150 echo off verify off ttitle - center 'Database Freespace Summary' skip 2 break on report COMPUTE sum of mb_used on report COMPUTE sum of mb_free on report COMPUTE sum of max_mb_free on report COMPUTE sum of dfct on report COLUMN mb_used format 99,999,999 COLUMN mb_free format 99,999,999 COLUMN max_mb_free format 99,999,999 COLUMN mb_free_frag format 99,999,999 COLUMN dfct format 99999 COLUMN blksz format 99999 COLUMN pct_used format 999.99 COLUMN max_pct_used format 999.99 SELECT DECODE(extent_management,'LOCAL','*',' ') || DECODE(segment_space_management,'AUTO','a ','m ') || DECODE(allocation_type,'SYSTEM','s ','u ') || fs.tablespace_name tablespace_name, block_size blksz, dfct, fs.nfrag ct_frag, fs.mxfrag / 1048576 mb_free_frag, fs.free_bytes / 1048576 mb_free, df.avail / 1048576 mb_total, (df.avail-fs.mxfrag)/df.avail*100 pct_used, df.max_bytes / 1048576 max_mb_free, (df.avail-fs.mxfrag)/df.max_bytes*100 max_pct_used FROM dba_tablespaces ts, (SELECT tablespace_name, count(*) dfct, SUM(decode(maxbytes,0,user_bytes,greatest(maxbytes,user_bytes))) max_bytes, SUM(user_bytes) avail FROM dba_data_files GROUP BY tablespace_name ) df, (SELECT tablespace_name, nvl(sum(bytes),0) free_bytes, count(bytes) nfrag, nvl(max(bytes),0) mxfrag FROM dba_free_space GROUP BY tablespace_name ) fs WHERE fs.tablespace_name = ts.tablespace_name(+) AND fs.tablespace_name = df.tablespace_name AND regexp_like(fs.tablespace_name,'&1','i') ORDER BY pct_used desc
Don’t Know cron
Learn something new every day !!!
Did you know the Date and Day from cron is OR condition and not AND?
Wanted to schedule cron to run every 3rd Friday.
This ended up running on the 19th which is Saturday
$ crontab -l|head -1
### Schedule below will run Every Friday OR Date 15-21 41 18 15-21 * 5 /bin/date > /tmp/date.out $ date Sat Mar 19 18:40:05 PDT 2016 $ date Sat Mar 19 18:41:17 PDT 2016 $ ll /tmp/date.out -rw-r--r--. 1 oracle oinstall 29 Mar 19 18:41 /tmp/date.out $ cat /tmp/date.out Sat Mar 19 18:41:01 PDT 2016
OOPS!
++++++++++
Shell script will need to check for day and exit if not the correct day.
$ date Sat Mar 19 18:43:13 PDT 2016 $ crontab -l|head -1 44 18 15-21 * * /home/oracle/t.sh > /tmp/date.out ++++++++++ $ cat t.sh #!/bin/sh -ex # Exit if not Friday if [[ $(date +%u) -ne 5 ]] ; then exit fi date ++++++++++ $ date Sat Mar 19 18:44:26 PDT 2016 $ ll /tmp/date.out -rw-r--r--. 1 oracle oinstall 0 Mar 19 18:44 /tmp/date.out $ ./t.sh ++ date +%u + [[ 6 -ne 5 ]] + exit
Option 2: check day from cron.
$ date Sun Mar 20 04:44:39 PDT 2016 $ crontab -l|head -1 45 04 15-21 * * [ $(date +\%u) -eq 7 ] && /home/oracle/t2.sh > /tmp/date.out ++++++++++ $ cat t2.sh date ++++++++++ $ date Sun Mar 20 04:45:01 PDT 2016 $ ll /tmp/date.out -rw-r--r--. 1 oracle oinstall 29 Mar 20 04:45 /tmp/date.out $ cat /tmp/date.out Sun Mar 20 04:45:01 PDT 2016 Tested on: oracle@arrow:tiger:/home/oracle $ uname -an Linux arrow.localdomain 3.8.13-68.3.2.el6uek.x86_64 #2 SMP Tue Jun 9 17:07:32 PDT 2015 x86_64 x86_64 x86_64 GNU/Linux oracle@arrow:tiger:/home/oracle $ cat /etc/oracle-release Oracle Linux Server release 6.6 oracle@arrow:tiger:/home/oracle $
++++++++++
Updated: Mar 26, 2016
$ crontab -l|head -1
27 20 15-25 * * /usr/bin/test `date +\%a` = Fri && /home/oracle/t2.sh > /tmp/t2.sh.log 2>&1
Both && and || logic produce identical results for the correct day.
$ date
Fri Mar 25 21:21:26 PDT 2016
$ test `date +\%a` = Fri;echo $?
0
pwd if test = 0
$ test `date +\%a` = Fri && pwd; echo $?
/home/oracle
0
$ test `date +\%a` != Fri;echo $?
1
pwd if test != 0
$ test `date +\%a` != Fri || pwd; echo $?
/home/oracle
0
Noticed the difference in return code as mention in reference:
http://docstore.mik.ua/orelly/unix3/upt/ch25_02.htm
Using && returns 1 while using || returns 0 for incorrect day.
It’s not Monday; hence, pwd did not return values.
$ test `date +\%a` = Mon && pwd; echo $?
1
$ test `date +\%a` != Mon || pwd; echo $?
0
The right side of && (pwd) will only be evaluated if the left side exit status = 0.
$ test `date +\%a` = Mon;echo $?
1
pwd if test = 0
$ test `date +\%a` = Mon && pwd; echo $?
1
The right side of || (pwd) will only be evaluated if the left side exit status is != 0.
$ test `date +\%a` != Mon;echo $?
0
pwd if test != 0
$ test `date +\%a` != Mon || pwd; echo $?
0
Migrate to 12c using Export/Import
If you had success doing this, please share.
This took some troubleshooting and more than one pass.
Still not comfortable to guarantee target is identical to source.
Here are all the references, I have gathered if you want an adventure.
How to Perform a Full Database Export Import During Upgrade, Migrate, Copy or Move of a Database (Doc ID 286775.1)
Information On Installed Database Components and Schemas (Doc ID 472937.1)
Schemas CTXSYS MDSYS ORDSYS Are Not Exported (Doc ID 228482.1)
How to Install/Deinstall Oracle Workspace Manager (Doc ID 263428.1)
How to Determine if Workspace Manager is Being Used? (Doc ID 727765.1)
Data Pump: GRANTs On SYS Owned Objects Are Not Transferred With Data Pump And Are Missing In The Target Database (Doc ID 1911151.1)
After Full Import All Object Privileges Granted By SYS Are Lost (Doc ID 97902.1)
DataPump Export Fails With Error ORA-39095 (Doc ID 1071931.1)
++++++++++
How many components are installed?
*** dba_registry *** ACTUAL SOURCE: COMP_ID COMP_NAME VERSION STATUS NAMESPACE SCHEMA ---------- ----------------------------------- --------------- ------------ ------------ --------------- CATALOG Oracle Database Catalog Views 10.2.0.4.0 VALID SERVER SYS CATPROC Oracle Database Packages and Types 10.2.0.4.0 VALID SERVER SYS EM Oracle Enterprise Manager 10.2.0.4.0 VALID SERVER SYSMAN OWM Oracle Workspace Manager 10.2.0.4.3 VALID SERVER WMSYS ONE BAD SOURCE: COMP_ID COMP_NAME VERSION STATUS NAMESPACE SCHEMA ---------- ----------------------------------- --------------- ------------ ------------ --------------- APS OLAP Analytic Workspace 11.2.0.4.0 VALID SERVER SYS CATALOG Oracle Database Catalog Views 11.2.0.4.0 VALID SERVER SYS CATJAVA Oracle Database Java Packages 11.2.0.4.0 VALID SERVER SYS CATPROC Oracle Database Packages and Types 11.2.0.4.0 VALID SERVER SYS CONTEXT Oracle Text 11.2.0.4.0 VALID SERVER CTXSYS EXF Oracle Expression Filter 11.2.0.4.0 VALID SERVER EXFSYS JAVAVM JServer JAVA Virtual Machine 11.2.0.4.0 VALID SERVER SYS ORDIM Oracle Multimedia 11.2.0.4.0 VALID SERVER ORDSYS OWB OWB 11.2.0.4.0 VALID SERVER OWBSYS OWM Oracle Workspace Manager 11.2.0.4.0 VALID SERVER WMSYS RUL Oracle Rules Manager 11.2.0.4.0 VALID SERVER EXFSYS SDO Spatial 11.2.0.4.0 VALID SERVER MDSYS XDB Oracle XML Database 11.2.0.4.0 VALID SERVER XDB XML Oracle XDK 11.2.0.4.0 VALID SERVER SYS XOQ Oracle OLAP API 11.2.0.4.0 VALID SERVER SYS 15 rows selected.
++++++++++
Export error using FLASHBACK_TIME
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39150: bad flashback time
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Took a some time but finally found a work around other than using FLASHBACK_SCN which is hokie.
expdp_full.par
USERID="/ as sysdba"
directory=DATA_PUMP_DIR
METRICS=Y
PARALLEL=4
FULL=Y
DUMPFILE=full%U.dmp
FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(systimestamp,'DD-MM-YYYY HH24:MI:SS'), 'DD-MM-YYYY HH24:MI:SS')"
EXCLUDE=statistics
exclude=schema:"IN ('WMSYS','SYSMAN')"
LOGFILE=exp_full.log
++++++++++
Create 12c non-PDB database.
whenever sqlerror exit sql.sqlcode whenever oserror exit 1 set echo on shu abort; create spfile from pfile; startup force nomount; CREATE DATABASE USER SYS IDENTIFIED BY oracle USER SYSTEM IDENTIFIED BY oracle MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16 LOGFILE GROUP 1 size 100M,GROUP 2 size 100M,GROUP 3 size 100M EXTENT MANAGEMENT LOCAL DATAFILE SIZE 257M AUTOEXTEND ON NEXT 256M MAXSIZE 30721M SYSAUX DATAFILE SIZE 257M AUTOEXTEND ON NEXT 256M MAXSIZE 30721M DEFAULT TABLESPACE USERS DATAFILE SIZE 257M AUTOEXTEND ON NEXT 257M MAXSIZE 30721M DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE 30720M UNDO TABLESPACE UNDOTBS1 DATAFILE SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE 30720M ; set echo off @?/rdbms/admin/catalog.sql; @?/rdbms/admin/catproc.sql; @?/rdbms/admin/catblock.sql; @?/rdbms/admin/catoctk.sql; @?/rdbms/admin/catio.sql @?/rdbms/admin/dbmsiotc.sql @?/rdbms/admin/dbmspool.sql @?/rdbms/admin/userlock.sql @?/rdbms/admin/utlrp.sql shutdown immediate; startup mount; alter database archivelog; alter database open; connect system/oracle @?/sqlplus/admin/pupbld.sql exit
++++++++++
Check for errors.
grep '^ORA-' create_db_omf.tiger.out |cut -d ":" -f1|sort -u
ORA-00942 ORA-01432 ORA-01434 ORA-01921 ORA-02289 ORA-04043 ORA-29807
oerr ora 1432
01432, 00000, "public synonym to be dropped does not exist" // *Cause: // *Action:
grep '^ORA-' create_db_omf.tiger.out |cut -d ":" -f2|grep -v "not exist"|sort -u
role name 'EXP_FULL_DATABASE' conflicts with another user or role role name 'IMP_FULL_DATABASE' conflicts with another user or role
++++++++++
Don’t use FILESIZE – I know, there are presentation showing usage of FILESIZE.
Example: FILESIZE=8g ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
++++++++++
DATAFILE SIZE 2097152000 is larger than MAXSIZE 1048576000.
ORA-39083: Object type TABLESPACE:"USERS" failed to create with error: ORA-02494: invalid or missing maximum file size in MAXSIZE clause Failing sql is: CREATE TABLESPACE "USERS" DATAFILE SIZE 2097152000 AUTOEXTEND ON NEXT 104857600 MAXSIZE 1048576000
Related error:
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE ORA-39083: Object type TABLE: failed to create with error: ORA-01950: no privileges on tablespace 'USERS'
++++++++++
SYS triggers need to be extracted from Source and created at Target.
Remember this one – OJVM?
SQL> CREATE OR REPLACE TRIGGER "SYS"."DBMS_JAVA_DEV_TRG" before create 2 on database 3 begin 4 if (ora_dict_obj_type='JAVA') 5 then 6 raise_application_error(-20031,'Java Development Disabled'); 7 end if; 8 end; 9 / Trigger created. SQL> ALTER TRIGGER "SYS"."DBMS_JAVA_DEV_TRG" DISABLE; Trigger altered. SYS@SQL>
++++++++++
Tablespaces were pre-created since datafile locations changed. Alternatively, use REMAP_DATAFILE.
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=none
COL name NEW_VALUE name
select name from v$database;
define _filename='tbs_&name..sql'
spool &_filename
select dbms_metadata.get_ddl('TABLESPACE',TABLESPACE_NAME) from dba_tablespace
;
spool off
set term on
pro
pro *** Spool Output: &_filename
pro
pro
exit
++++++++++
Import done in 2 passes, impdp_full_user.par and impdp_full.par
impdp_full_user.par
directory=DATA_PUMP_DIR USERID="/ as sysdba" METRICS=Y PARALLEL=4 FULL=Y DUMPFILE=full%U.dmp LOGFILE=imp_user.log INCLUDE=user,role,default_role,tablespace_quota,role_grant,profile,password_verify_function,tablespace Want to detect as many issues as possible before actual data import. ORA-39083: Object type TABLESPACE:"USERS" failed to create with error ORA-39083: Object type TABLE: failed to create with error: ORA-01950: no privileges on tablespace 'USERS'
++++++++++
grep -v ORA-31684 imp_user.log | grep -A 1 Processing
Processing object type DATABASE_EXPORT/TABLESPACE
Completed 26 TABLESPACE objects in 1 seconds
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
Completed 1 PASSWORD_VERIFY_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/PROFILE
Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/SYS_USER/USER
Completed 1 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/USER
Completed 7796 USER objects in 20 seconds
Processing object type DATABASE_EXPORT/ROLE
Completed 321 ROLE objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Completed 21442 ROLE_GRANT objects in 129 seconds
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Completed 7796 DEFAULT_ROLE objects in 10 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Completed 7790 TABLESPACE_QUOTA objects in 24 seconds
++++++++++
impdp_full.par
directory=DATA_PUMP_DIR USERID="/ as sysdba" METRICS=Y PARALLEL=4 FULL=Y DUMPFILE=full%U.dmp LOGFILE=imp_full.log EXCLUDE=user,role,default_role,tablespace_quota,role_grant,profile,password_verify_function,tablespace STREAMS_CONFIGURATION=NO
++++++++++
grep -v ORA-31684 imp_full.log | grep -A 1 Processing
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Completed 6 PROC_SYSTEM_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Completed 145 SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/RESOURCE_COST
Completed 1 RESOURCE_COST objects in 0 seconds
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Completed 1 TRUSTED_DB_LINK objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Completed 197 SEQUENCE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Completed 205 OBJECT_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Completed 7 DIRECTORY objects in 0 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Completed 12 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/CONTEXT
Completed 7 CONTEXT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Completed 1139 SYNONYM objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Completed 1014 SYNONYM objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Completed 180 TYPE objects in 3 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Completed 2 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
--
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Completed 3 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Completed 1302 TABLE objects in 18 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Startup took 356 seconds
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Completed 68094 OBJECT_GRANT objects in 91 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Completed 4 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX: failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Completed 238 CONSTRAINT objects in 86 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Completed 696 COMMENT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/AUDIT_OBJ
Completed 1 AUDIT_OBJ objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/RLS_POLICY
Completed 68 RLS_POLICY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Completed 148 PACKAGE objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Completed 195 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Completed 42 FUNCTION objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Completed 52 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Completed 16 PROCEDURE objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Completed 2 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Completed 42 ALTER_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Completed 16 ALTER_PROCEDURE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Completed 72 VIEW objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Completed 155 OBJECT_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Completed 62 COMMENT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/RLS_POLICY
Completed 4 RLS_POLICY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT: failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Completed 148 PACKAGE_BODY objects in 4 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
Completed 16 TYPE_BODY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
ORA-39083: Object type INDEX: failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Completed 68 TRIGGER objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
Completed 3 TRIGGER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
Completed 1 MATERIALIZED_VIEW objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/JOB
Completed 5 JOB objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Completed 3 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
--
Processing object type DATABASE_EXPORT/AUDIT
Completed 5 AUDIT objects in 0 seconds
12c Clone DB_SWONLY Oracle Home
Just some notes on 12c Oracle Home Cloning.
I am too lazy to put step by step instructions in words, and if you have questions, please ask.
-defaultHomeName
<HOME NAME=”OraHome1” LOC=”/u01/app/oracle/product/12.2.0/dbhome_2″ TYPE=”O” IDX=”3″/>
oracle@arrow:hawlkas:/home/oracle $ . oraenv <<< db12c ORACLE_SID = [hawlkas] ? The Oracle base remains unchanged with value /u01/app/oracle oracle@arrow:db12c:/home/oracle $ echo $ORACLE_HOME /u01/app/oracle/product/12.2.0/dbhome_1 oracle@arrow:db12c:/home/oracle $ cd $ORACLE_HOME oracle@arrow:db12c:/u01/app/oracle/product/12.2.0/dbhome_1 $ pwd /u01/app/oracle/product/12.2.0/dbhome_1 oracle@arrow:db12c:/u01/app/oracle/product/12.2.0/dbhome_1 $ nohup tar -cvpf /tmp/db12cHome_${USER}_`hostname -s`_clone.tar . > /tmp/clone_${USER}_`hostname -s`_db12cHome.log 2>&1 & [1] 13092 ++++++++++ oracle@arrow:db12c:/u01/app/oracle/product/12.2.0/dbhome_1 $ [1]+ Done nohup tar -cvpf /tmp/db12cHome_${USER}_`hostname -s`_clone.tar . > /tmp/clone_${USER}_`hostname -s`_db12cHome.log 2>&1 oracle@arrow:db12c:/u01/app/oracle/product/12.2.0/dbhome_1 $ ll /tmp/*clone* -rw-r--r--. 1 oracle oinstall 1381582 Mar 6 16:24 /tmp/clone_oracle_arrow_db12cHome.log -rw-r--r--. 1 oracle oinstall 6581944320 Mar 6 16:24 /tmp/db12cHome_oracle_arrow_clone.tar oracle@arrow:db12c:/u01/app/oracle/product/12.2.0/dbhome_1 $ tail /tmp/clone_oracle_arrow_db12cHome.log ./jdk/bin/xjc ./jdk/bin/javadoc ./jdk/bin/jarsigner ./jdk/bin/pack200 ./jdk/bin/rmid ./jdk/bin/jrunscript ./jdk/bin/extcheck ./jdk/bin/keytool ./jdk/LICENSE ./jdk/.manifest oracle@arrow:db12c:/u01/app/oracle/product/12.2.0/dbhome_1 $ du -sh /tmp/db12cHome_oracle_arrow_clone.tar 6.2G /tmp/db12cHome_oracle_arrow_clone.tar oracle@arrow:db12c:/u01/app/oracle/product/12.2.0/dbhome_1 $ ++++++++++ oracle@arrow:test12c:/home/oracle $ . oraenv <<< test12c ORACLE_SID = [test12c] ? The Oracle base remains unchanged with value /u01/app/oracle oracle@arrow:test12c:/home/oracle $ cd $ORACLE_HOME oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2 $ pwd /u01/app/oracle/product/12.2.0/dbhome_2 oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2 $ ls oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2 $ nohup tar -xvf /tmp/db12cHome_oracle_arrow_clone.tar > /tmp/untar_${USER}_`hostname -s`_db12cHome.log 2>&1 & [1] 13182 oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2 $ [1]+ Done nohup tar -xvf /tmp/db12cHome_oracle_arrow_clone.tar > /tmp/untar_${USER}_`hostname -s`_db12cHome.log 2>&1 oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2 $ ll /tmp/untar_oracle_arrow_db12cHome.log -rw-r--r--. 1 oracle oinstall 1381582 Mar 6 16:44 /tmp/untar_oracle_arrow_db12cHome.log oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2 $ tail /tmp/untar_oracle_arrow_db12cHome.log ./jdk/bin/xjc ./jdk/bin/javadoc ./jdk/bin/jarsigner ./jdk/bin/pack200 ./jdk/bin/rmid ./jdk/bin/jrunscript ./jdk/bin/extcheck ./jdk/bin/keytool ./jdk/LICENSE ./jdk/.manifest oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2 $ ls addnode ccr crs dbs diagnostics hs javavm ldap mgw odbc oracore owm precomp rdbms scheduler sqlpatch sysman wwg apex cdata css dc_ocm dmu install jdbc lib network olap oraInst.loc perl QOpatch relnotes slax sqlplus ucp xdk assistants cfgtoollogs ctx deinstall dv instantclient jdk log nls OPatch ord plsql R rest sqldeveloper srvm usm bin clone cv demo has inventory jlib md ++++++++++ $ cd $ORACLE_HOME/clone/bin oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin $ perl clone.pl -silent -noconfig -defaultHomeName ORACLE_HOME="/u01/app/oracle/product/12.2.0/dbhome_2" ORACLE_BASE="/u01/app/oracle" OSDBA_GROUP=dba OSOPER_GROUP=dba ./runInstaller -clone -waitForCompletion -noconfig -defaultHomeName "ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_2" "ORACLE_BASE=/u01/app/oracle" "oracle_install_OSDBA=dba" "oracle_install_OSOPER=dba" -silent -paramFile /u01/app/oracle/product/12.2.0/dbhome_2/clone/clone_oraparam.ini Starting Oracle Universal Installer... Checking Temp space: must be greater than 500 MB. Actual 16049 MB Passed Checking swap space: must be greater than 500 MB. Actual 3994 MB Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-03-06_04-58-03PM. Please wait ...You can find the log of this install session at: /u01/app/oraInventory/logs/cloneActions2016-03-06_04-58-03PM.log .................................................. 5% Done. .................................................. 10% Done. .................................................. 15% Done. .................................................. 20% Done. .................................................. 25% Done. .................................................. 30% Done. .................................................. 35% Done. .................................................. 40% Done. .................................................. 45% Done. .................................................. 50% Done. .................................................. 55% Done. .................................................. 60% Done. .................................................. 65% Done. .................................................. 70% Done. .................................................. 75% Done. .................................................. 80% Done. .................................................. 85% Done. .......... Copy files in progress. Copy files successful. Link binaries in progress. Link binaries successful. Setup files in progress. Setup files successful. Setup Inventory in progress. Setup Inventory successful. Finish Setup successful. The cloning of OraHome1 was successful. Please check '/u01/app/oraInventory/logs/cloneActions2016-03-06_04-58-03PM.log' for more details. Setup Oracle Base in progress. Setup Oracle Base successful. .................................................. 95% Done. As a root user, execute the following script(s): 1. /u01/app/oracle/product/12.2.0/dbhome_2/root.sh .................................................. 100% Done. oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin $ su - Password: [root@arrow ~]# /u01/app/oracle/product/12.2.0/dbhome_2/root.sh Check /u01/app/oracle/product/12.2.0/dbhome_2/install/root_arrow.localdomain_2016-03-06_17-03-49.log for the output of root script [root@arrow ~]# exit logout oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin $ /u01/app/oracle/product/12.2.0/dbhome_2/OPatch/opatch lspatches 22139226;Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016) 21948354;Database Patch Set Update : 12.1.0.2.160119 (21948354) OPatch succeeded. oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin $ /u01/app/oracle/product/12.2.0/dbhome_2/OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.1.0.1.10 Copyright (c) 2016, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/12.2.0/dbhome_2 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/12.2.0/dbhome_2/oraInst.loc OPatch version : 12.1.0.1.10 OUI version : 12.1.0.2.0 Log file location : /u01/app/oracle/product/12.2.0/dbhome_2/cfgtoollogs/opatch/opatch2016-03-06_17-29-28PM_1.log Lsinventory Output file location : /u01/app/oracle/product/12.2.0/dbhome_2/cfgtoollogs/opatch/lsinv/lsinventory2016-03-06_17-29-28PM.txt -------------------------------------------------------------------------------- Local Machine Information:: Hostname: localhost ARU platform id: 226 ARU platform description:: Linux x86-64 Installed Top-level Products (1): Oracle Database 12c 12.1.0.2.0 There are 1 products installed in this Oracle Home. Interim patches (2) : Patch 22139226 : applied on Sun Mar 06 15:46:50 PST 2016 Unique Patch ID: 19729684 Patch description: "Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016)" Created on 4 Jan 2016, 01:41:46 hrs PST8PDT Bugs fixed: 19699946, 19176885, 19623450, 22139226, 19909862, 21811517, 19223010 21068507, 19895326, 19877336, 22118835, 22118851, 21566993, 19153980 20408829, 21047766, 19231857, 19895362, 19855285, 20415564, 21555660 19245191, 21047803, 20408866, 21566944 Patch 21948354 : applied on Sun Mar 06 15:35:02 PST 2016 Unique Patch ID: 19553095 Patch description: "Database Patch Set Update : 12.1.0.2.160119 (21948354)" Created on 20 Dec 2015, 23:39:33 hrs PST8PDT Sub-patch 21359755; "Database Patch Set Update : 12.1.0.2.5 (21359755)" Sub-patch 20831110; "Database Patch Set Update : 12.1.0.2.4 (20831110)" Sub-patch 20299023; "Database Patch Set Update : 12.1.0.2.3 (20299023)" Sub-patch 19769480; "Database Patch Set Update : 12.1.0.2.2 (19769480)" Bugs fixed: 19189525, 19075256, 19141838, 19865345, 19791273, 19280225, 18845653 20951038, 19243521, 19248799, 21756699, 18988834, 19238590, 21281532 20245930, 18921743, 18799063, 19134173, 19571367, 20476175, 20925795 19018206, 20509482, 20387265, 20588502, 19149990, 18849537, 18886413 17551063, 19183343, 19703301, 19001390, 18202441, 19189317, 19644859 19358317, 19390567, 19279273, 19706965, 19068970, 19619732, 20348653 18607546, 18940497, 19670108, 19649152, 18948177, 19315691, 19676905 18964978, 19035573, 20165574, 19176326, 20413820, 20558005, 19176223 19532017, 20134339, 19074147, 18411216, 20361671, 20425790, 18966843 20294666, 19307662, 19371175, 19195895, 19154375, 19468991, 19174521 19520602, 19382851, 21875360, 19326908, 19658708, 20093776, 20618595 21787056, 17835294, 19791377, 19068610, 20048359, 20746251, 19143550 19185876, 19627012, 20281121, 19577410, 22092979, 19001359, 19518079 18610915, 19490948, 18674024, 18306996, 19309466, 19081128, 19915271 20122715, 21188532, 20284155, 18791688, 20890311, 21442094, 18973548 19303936, 19597439, 20235511, 18964939, 19430401, 19044962, 19409212 19879746, 20657441, 19684504, 19024808, 18799993, 20877664, 19028800 19065556, 19723336, 19077215, 19604659, 21421886, 19524384, 19048007 18288842, 19689979, 20446883, 18952989, 16870214, 19928926, 21526048 19180770, 19197175, 19902195, 20318889, 19730508, 19012119, 19067244 20074391, 19512341, 19841800, 14643995, 20331945, 19587324, 19547370 19065677, 19637186, 21225209, 20397490, 18967382, 19174430, 18674047 19054077, 19536415, 19708632, 19289642, 20869721, 19335438, 17365043 18856999, 19869255, 20471920, 19468347, 21620471, 16359751, 18990693 17890099, 19439759, 19769480, 19272708, 19978542, 20101006, 21300341 20402832, 19329654, 19873610, 21668627, 21517440, 19304354, 19052488 20794034, 19291380, 18681056, 19896336, 19076343, 19561643, 18618122 20440930, 18456643, 19699191, 18909599, 19487147, 18250893, 19016730 18743542, 20347562, 16619249, 18354830, 19687159, 19174942, 20424899 19989009, 20688221, 20441797, 19157754, 19032777, 19058490, 19399918 18885870, 19434529, 19018447, 18417036, 20919320, 19022470, 19284031 20474192, 20173897, 22062026, 19385656, 19501299, 17274537, 20899461 19440586, 16887946, 19606174, 18436647, 17655240, 19023822, 19178851 19124589, 19805359, 19597583, 19155797, 19393542, 19050649 -------------------------------------------------------------------------------- OPatch succeeded. oracle@arrow:test12c:/u01/app/oracle/product/12.2.0/dbhome_2/clone/bin $ ++++++++++ oracle@arrow:tiger:/u01/app/oraInventory/logs $ ls -alrt|tail -rw-r-----. 1 oracle oinstall 239 Mar 6 16:54 installActions2016-03-06_04-54-57PM.log -rw-r-----. 1 oracle oinstall 0 Mar 6 16:56 oraInstall2016-03-06_04-55-18PM.err -rw-r-----. 1 oracle oinstall 200 Mar 6 16:56 oraInstall2016-03-06_04-55-18PM.out -rw-r-----. 1 oracle oinstall 11221 Mar 6 16:56 cloneActions2016-03-06_04-55-18PM.log -rw-r-----. 1 oracle oinstall 0 Mar 6 16:58 oraInstall2016-03-06_04-58-03PM.err -rw-r-----. 1 oracle oinstall 79 Mar 6 17:01 silentInstall2016-03-06_04-58-03PM.log -rw-r-----. 1 oracle oinstall 1868 Mar 6 17:01 oraInstall2016-03-06_04-58-03PM.out drwxrwx---. 6 oracle oinstall 4096 Mar 6 17:01 .. -rw-r-----. 1 oracle oinstall 7338332 Mar 6 17:01 cloneActions2016-03-06_04-58-03PM.log drwxrwx---. 3 oracle oinstall 4096 Mar 8 07:51 . oracle@arrow:tiger:/u01/app/oraInventory/logs $ grep -e '[[:upper:]]: ' cloneActions2016-03-06_04-58-03PM.log|cut -d ":" -f1|sort -u INFO WARNING oracle@arrow:tiger:/u01/app/oraInventory/logs $ grep -e '[[:upper:]]: ' cloneActions2016-03-06_04-58-03PM.log |grep "^WARNING: " WARNING: You have not provided an email address. If you do not want to receive security updates, set 'DECLINE_SECURITY_UPDATES' to 'true'. oracle@arrow:tiger:/u01/app/oraInventory/logs $ ++++++++++ oracle@arrow:test12c:/u01/app/oraInventory/ContentsXML $ grep -i "home name" inventory.xml <HOME NAME="OraDb11g_home1" LOC="/u01/app/oracle/product/11.2.0/dbhome_1" TYPE="O" IDX="1"/> <HOME NAME="OraDB12Home1" LOC="/u01/app/oracle/product/12.2.0/dbhome_1" TYPE="O" IDX="2"/> <HOME NAME="OraHome1" LOC="/u01/app/oracle/product/12.2.0/dbhome_2" TYPE="O" IDX="3"/>
RANT PSU Naming
Do you know what PSU there are?
oracle@arrow:db12c:/media/sf_working/12c/psu $ ll *.zip -rwxrwx---. 1 root vboxsf 147028181 Mar 4 20:00 p21948354_121020_Linux-x86-64.zip -rwxrwx---. 1 root vboxsf 101720296 Mar 4 19:58 p22139226_121020_Linux-x86-64.zip
Let me give you a hint – (12.1.0.1.160119)
Now do you know what they are?
Let me give you another hint.
21948354 (12.1.0.1.160119) – 12.1.0.1 JAN2016 PSU (Database assumed here)
22139226 (12.1.0.2.160119) – OJVM PSU Patches
160119 – what date format is this?
opatch lsinventory
Patch 22139226 : applied on Wed Feb 03 11:07:55 CST 2016
Unique Patch ID: 19729684
Patch description: "Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016)"
Created on 4 Jan 2016, 01:41:46 hrs PST8PDT
Bugs fixed:
19699946, 19176885, 19623450, 22139226, 19909862, 21811517, 19223010
21068507, 19895326, 19877336, 22118835, 22118851, 21566993, 19153980
20408829, 21047766, 19231857, 19895362, 19855285, 20415564, 21555660
19245191, 21047803, 20408866, 21566944
Patch 21948354 : applied on Wed Feb 03 11:03:24 CST 2016
Unique Patch ID: 19553095
Looks like the whole numbering system is screwed up now.
Patch description: "Database Patch Set Update : 12.1.0.2.160119 (21948354)"
Created on 20 Dec 2015, 23:39:33 hrs PST8PDT
Sub-patch 21359755; "Database Patch Set Update : 12.1.0.2.5 (21359755)"
Sub-patch 20831110; "Database Patch Set Update : 12.1.0.2.4 (20831110)"
Sub-patch 20299023; "Database Patch Set Update : 12.1.0.2.3 (20299023)"
Sub-patch 19769480; "Database Patch Set Update : 12.1.0.2.2 (19769480)"
12c Silent Install software-only BUG
12.1: Oracle Database Software Only Silent Installation Fails to Create configToolAllCommands (Doc ID 2037923.1)
The BUG is in RED BOLD below.
oracle@arrow:hawklas:/media/sf_working/12c/database/response $ cat /etc/oraInst.loc inventory_loc=/u01/app/oraInventory inst_group=oinstall ++++++++++ oracle@arrow:hawklas:/media/sf_working/12c/database/response $ cat db_swonly.rsp oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.1.0 oracle.install.option=INSTALL_DB_SWONLY # Specify the Unix group to be set for the inventory directory. UNIX_GROUP_NAME=oinstall INVENTORY_LOCATION=/etc/oraInst.loc SELECTED_LANGUAGES=en ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1 ORACLE_BASE=/u01/app/oracle oracle.install.db.InstallEdition=EE oracle.install.db.DBA_GROUP=dba oracle.install.db.OPER_GROUP=dba oracle.install.db.BACKUPDBA_GROUP=dba oracle.install.db.DGDBA_GROUP=dba oracle.install.db.KMDBA_GROUP=dba DECLINE_SECURITY_UPDATES=true oracle.installer.autoupdates.option=SKIP_UPDATES ++++++++++ oracle@arrow:hawklas:/media/sf_working/12c/database $ ./runInstaller -showProgress -waitforcompletion -silent -noconfig -force -responseFile /media/sf_working/12c/database/response/db_swonly.rsp Starting Oracle Universal Installer... Checking Temp space: must be greater than 500 MB. Actual 29558 MB Passed Checking swap space: must be greater than 150 MB. Actual 4080 MB Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-03-06_01-53-21PM. Please wait ...KiTTY X11 proxy: MIT-MAGIC-COOKIE-1 data did not matchYou can find the log of this install session at: /u01/app/oraInventory/logs/installActions2016-03-06_01-53-21PM.log Prepare in progress. .................................................. 8% Done. Prepare successful. Copy files in progress. .................................................. 13% Done. .................................................. 18% Done. .................................................. 23% Done. .................................................. 28% Done. .................................................. 33% Done. .................................................. 39% Done. .................................................. 45% Done. .................................................. 50% Done. .................................................. 55% Done. .................................................. 60% Done. .................................................. 65% Done. .................................................. 70% Done. .................................................. 75% Done. Copy files successful. Link binaries in progress. Link binaries successful. Setup files in progress. .............................. Setup files successful. .......... Setup Inventory in progress. Setup Inventory successful. .................................................. 80% Done. .......... Finish Setup successful. The installation of Oracle Database 12c was successful. Please check '/u01/app/oraInventory/logs/silentInstall2016-03-06_01-53-21PM.log' for more details. Setup Oracle Base in progress. Setup Oracle Base successful. .................................................. 95% Done. As a root user, execute the following script(s): 1. /u01/app/oracle/product/12.2.0/dbhome_1/root.sh .................................................. 100% Done. Successfully Setup Software. As install user, execute the following script to complete the configuration. 1. /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/configToolAllCommands 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). oracle@arrow:hawklas:/media/sf_working/12c/database $ su - Password: [root@arrow ~]# /u01/app/oracle/product/12.2.0/dbhome_1/root.sh Check /u01/app/oracle/product/12.2.0/dbhome_1/install/root_arrow.localdomain_2016-03-06_14-09-10.log for the output of root script [root@arrow ~]# ll /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/configToolAllCommands ls: cannot access /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/configToolAllCommands: No such file or directory [root@arrow ~]#
A Better Diff
I have been working on simplifying, perfecting, and comparing RMAN backup scripts.
The typical diff file1 file2 was not useful as I wanted a complete picture.
Look at what I found!
$ diff -iwyB --suppress-common-lines -W 150 rman_bkupinc.sh rman_bkuparc.sh;echo # RMAN database incremental backup | # RMAN archivelog backup # rman_bkupinc.sh | # rman_bkuparc.sh # Shell script calls bkupinc.rman at at SCRIPT_DIR location | # Shell script calls bkuparc.rman at at SCRIPT_DIR location SID=${1:?"---> USAGE: $DN/$BN -ORACLE_SID -LEVEL"} | SID=${1:?"---> USAGE: $DN/$BN -ORACLE_SID"} LVL=${2:?"---> USAGE: $DN/$BN -ORACLE_SID -LEVEL"} < DAY=`date '+%bW%U'` | DAY=`date '+%aH%H'` RMAN_LOG=$LOG_DIR/`echo $BN|cut -d'.' -f1`.$ORACLE_SID.L$2.$DAY.log | RMAN_LOG=$LOG_DIR/`echo $BN|cut -d'.' -f1`.$ORACLE_SID.$DAY.log rman @${RMAN_SCRIPT} ${LVL} msglog $RMAN_LOG | rman @${RMAN_SCRIPT} msglog $RMAN_LOG $ diff -iwy -W 150 rman_bkupinc.sh rman_bkuparc.sh;echo #!/bin/sh -ex #!/bin/sh -ex # Michael Dinh: Mar 03, 2016 # Michael Dinh: Mar 03, 2016 # RMAN database incremental backup | # RMAN archivelog backup # rman_bkupinc.sh | # rman_bkuparc.sh # Shell script calls bkupinc.rman at at SCRIPT_DIR location | # Shell script calls bkuparc.rman at at SCRIPT_DIR location DN=`dirname $0` DN=`dirname $0` BN=`basename $0` BN=`basename $0` SID=${1:?"---> USAGE: $DN/$BN -ORACLE_SID -LEVEL"} | SID=${1:?"---> USAGE: $DN/$BN -ORACLE_SID"} LVL=${2:?"---> USAGE: $DN/$BN -ORACLE_SID -LEVEL"} < set -a set -a ### Edit for proper location ### Edit for proper location SCRIPT_DIR=/media/sf_working/rman SCRIPT_DIR=/media/sf_working/rman LOG_DIR=/tmp LOG_DIR=/tmp PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin LD_LIBRARY_PATH=/lib:/usr/lib LD_LIBRARY_PATH=/lib:/usr/lib ### Edit resync_catalog.rman ### Edit resync_catalog.rman ### Uncomment if catalog is being used ### Uncomment if catalog is being used # RESYNC_CATALOG=$SCRIPT_DIR/resync_catalog.rman # RESYNC_CATALOG=$SCRIPT_DIR/resync_catalog.rman ORACLE_SID=$1 ORACLE_SID=$1 ORAENV_ASK=NO ORAENV_ASK=NO . oraenv . oraenv NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS" NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS" DAY=`date '+%bW%U'` | DAY=`date '+%aH%H'` RMAN_SCRIPT=$SCRIPT_DIR/`echo $BN|cut -d'.' -f1|cut -c6-`.rman RMAN_SCRIPT=$SCRIPT_DIR/`echo $BN|cut -d'.' -f1|cut -c6-`.rman RMAN_LOG=$LOG_DIR/`echo $BN|cut -d'.' -f1`.$ORACLE_SID.L$2.$DAY.log | RMAN_LOG=$LOG_DIR/`echo $BN|cut -d'.' -f1`.$ORACLE_SID.$DAY.log TMPLOG=/tmp/`echo $BN|cut -d'.' -f1`_$ORACLE_SID.log TMPLOG=/tmp/`echo $BN|cut -d'.' -f1`_$ORACLE_SID.log ERRLOG=/tmp/`echo $BN|cut -d'.' -f1`_$ORACLE_SID.err ERRLOG=/tmp/`echo $BN|cut -d'.' -f1`_$ORACLE_SID.err set +a set +a # Lock file # Lock file exec 200>/tmp/$BN.lck exec 200>/tmp/$BN.lck flock -n 200 || exit 1 flock -n 200 || exit 1 # RMAN archivelog backup # RMAN archivelog backup rman @${RMAN_SCRIPT} ${LVL} msglog $RMAN_LOG | rman @${RMAN_SCRIPT} msglog $RMAN_LOG ### Uncomment if catalog is being used ### Uncomment if catalog is being used # rman @${RESYNC_CATALOG} msglog $RMAN_LOG append # rman @${RESYNC_CATALOG} msglog $RMAN_LOG append cp -v $RMAN_LOG $TMPLOG cp -v $RMAN_LOG $TMPLOG egrep -i '^rman-|^ora-|error|fail' $RMAN_LOG > $ERRLOG egrep -i '^rman-|^ora-|error|fail' $RMAN_LOG > $ERRLOG exit exit $ ll /tmp/rman*.* -rw-r--r--. 1 oracle oinstall 0 Mar 3 17:19 /tmp/rman_bkuparc_hawklas.err -rw-r--r--. 1 oracle oinstall 7910 Mar 3 17:19 /tmp/rman_bkuparc_hawklas.log -rw-r--r--. 1 oracle oinstall 7910 Mar 3 17:19 /tmp/rman_bkuparc.hawklas.ThuH17.log -rw-r--r--. 1 oracle oinstall 0 Mar 3 17:19 /tmp/rman_bkuparc.sh.lck -rw-r--r--. 1 oracle oinstall 0 Mar 3 19:20 /tmp/rman_bkupinc_hawklas.err -rw-r--r--. 1 oracle oinstall 15315 Mar 3 19:20 /tmp/rman_bkupinc.hawklas.L1.MarW09.log -rw-r--r--. 1 oracle oinstall 15315 Mar 3 19:20 /tmp/rman_bkupinc_hawklas.log -rw-r--r--. 1 oracle oinstall 0 Mar 3 19:20 /tmp/rman_bkupinc.sh.lck
