Thursday 6 July 2017

Setting server system variable 'old_alter_table' back to '0'.

Error :-- MySQL Enterprise Backup version 4.1.0 Linux-2.6.39-400.215.10.el5uek-x86_64 [2017/03/01] Copyright (c) 2003, 2017, Oracle and/or its affiliates. All Rights Reserved.

170705 03:00:01 MAIN    INFO: A thread created with Id '140533386585920'
170705 03:00:01 MAIN    INFO: Starting with following command line ...
/arch03/MySQL_Backup/meb-4.1.0-linux-glibc2.5-x86-64bit/bin/mysqlbackup
        -u root -pxxxxxxx --no-locking
        --backup-dir=/backup/backup_full_3302/03hr_05_July_2017
        backup-and-apply-log

170705 03:00:01 MAIN    INFO:
170705 03:00:01 MAIN    INFO: MySQL server version is '5.7.16-enterprise-commercial-advanced-log'
170705 03:00:01 MAIN    INFO: MySQL server compile os version is 'linux-glibc2.5'
170705 03:00:01 MAIN    INFO: Got some server configuration information from running server.
170705 03:06:19 RDR1    INFO: Copying the database directory 'mysql'
170705 03:06:19 RDR1    INFO: Copying the database directory 'performance_schema'
170705 03:06:19 RDR1    INFO: Copying the database directory 'routeFinderBuildCircuitTable'
170705 03:06:19 RDR1    INFO: Copying the database directory 'routeFinderRouteValidation'
170705 03:06:19 RDR1    INFO: Copying the database directory 'routeFinderStatusReports'
170705 03:06:20 RDR1 Progress in MB: 46400 46600 46800 47000
170705 03:06:21 RDR1    INFO: Copying the database directory 'routeFinderTables'
170705 03:06:22 RDR1 Progress in MB: 47200 47400 47600 47800 48000 48200 48400 48600 48800 49000 49200 49400 49600 49800 50000 50200 50400 50600 50800 51000 51200 51400 51600 51800 52000 52200 52400 52600 52800
170705 03:06:38 WTR1 ERROR: Disk full while writing '/backup/backup_full_3302/03hr_05_July_2017/datadir/routeFinderTables/DvGtestT_C.MYI'
170705 03:06:38 WTR1    INFO: Requesting completion of redo log copy. Rapid: 1
170705 03:06:38 RLR1    INFO: Signaled '1' to complete log copy.
170705 03:06:38 RLR1    INFO: Signaled '1' to complete log copy.
170705 03:06:38 RLR1    INFO: Redo log reader waited = 395270.00 ms for logs to generate.

mysqlbackup failed with errors!
170705 03:06:38 MAIN    INFO: Setting server system variable 'old_alter_table' back to '0'. 



Issue :-- 170705 03:06:38 WTR1 ERROR: Disk full while writing '/backup/backup_full_3302/03hr_05_July_2017/datadir/routeFinderTables/DvGrptestT_C.MYI'

Setting server system variable 'old_alter_table' back to '0'.

Solution :--  Check server disk space where backup files generated & remove old files & start backup again  

Wednesday 1 March 2017

ALTERNATE archival destination in Oracle

Understanding Archive Destination Status

Each archive destination has the following variable characteristics that determine its status:
·         Valid/Invalid: indicates whether the disk location or service name information is specified and valid
·         Enabled/Disabled: indicates the availability state of the location and whether the database can use the destination
·         Active/Inactive: indicates whether there was a problem accessing the destination
Several combinations of these characteristics are possible. To obtain the current status and other information about each destination for an instance, query the V$ARCHIVE_DEST view.
The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 31) initialization parameter lets you control the availability state of the specified destination (n).
·         ENABLE indicates that the database can use the destination.
·         DEFER indicates that the location is temporarily disabled.
·         ALTERNATE indicates that the destination is an alternate. The availability state of an alternate destination is DEFER. If its parent destination fails, the availability state of the alternate becomes ENABLE. ALTERNATE cannot be specified for destinations LOG_ARCHIVE_DEST_11 to LOG_ARCHIVE_DEST_31.

Specifying Alternate Destinations
If you want to specify that a location be an archive destination only in the event of a failure of another destination, you can make it an alternate destination. Both local and remote destinations can be alternates. The following example makes LOG_ARCHIVE_DEST_4 an alternate for LOG_ARCHIVE_DEST_3:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_4 = 'LOCATION=/disk4/arch';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 = 'LOCATION=/disk3/arch
   ALTERNATE=LOG_ARCHIVE_DEST_4';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4=ALTERNATE;


SQL> SELECT dest_name, status, destination FROM v$archive_dest;

DEST_NAME               STATUS    DESTINATION
----------------------- --------- ----------------------------------------------
LOG_ARCHIVE_DEST_1      VALID     /disk1/arch
LOG_ARCHIVE_DEST_2      VALID     +RECOVERY
LOG_ARCHIVE_DEST_3      VALID     /disk3/arch

LOG_ARCHIVE_DEST_4      ALTERNATE /disk4/arch

doubt transactions in oracle

Query to check in doubt transactions...
=====================================

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'), STATE, MIXED FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID          GLOBAL_TRAN_ID                           TO_CHAR(FAIL_TIME,'DD-MON-YYY STATE            MIX
---------------------- ---------------------------------------- ----------------------------- ---------------- ---
88.33.88299            abc.a930e3e9.36.36.71299             24-jan-2017 07:56:31          collecting       no
44.77.88441            abc.a930e3e9.33.40.68441             24-jan-2017 09:27:49          collecting       no
99.99.99919            abc.a930e3e9.27.44.59919             24-jan-2017 15:39:21          collecting       no

SQL> SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;

LOCAL_TRAN_ID          IN_ I DATABASE
---------------------- --- - ------------------------------
01.01.11299            in  N
33.44.55441            in  N
22.44.55919            in  N
88.33.88299            out N REM2CRM_HZ_PARTIES_DBLINK
44.77.88441            out N REM2CRM_HZ_PARTIES_DBLINK
99.99.99919            out N REM2CRM_HZ_PARTIES_DBLINK

6 rows selected.

SQL>

To clear the in doubt transactions...
=====================================
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('88.33.88299');
COMMIT;

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('44.77.88441');
COMMIT;

Tuesday 28 February 2017

Oracle External Table Error ORA-12801 ORA-29913

Error:

SQL> select * from abc.EXT_TAB_BILLRUN;
select * from abc.EXT_TAB_BILLRUN
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P000
ORA-29913: error in executing ODCIEXTTABLEFETCH callout

Cause:

External Table loading rejecting the Bad records & were unable to append errors in bad log file due to permission issue

select dbms_metadata.get_ddl('TABLE','EXT_TAB_BILLRUN','abc') from dual;

.
.
   DEFAULT DIRECTORY "EXT_TABLES_DIR"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
   BADFILE 'bad_ext_tab_billrun'
   LOGFILE 'log_ext_tab_billrun'
   FIELDS TERMINATED BY '^'

[oracle9i@:/home/oracle9i>]tail -f log_ext_tab_billrun.log
      Trim whitespace same as SQL Loader
    BILLING_METHOD                  CHAR (255)
      Terminated by "^"
      Enclosed by """ and """
      Trim whitespace same as SQL Loader
KUP-04021: field formatting error for field SERVICE_ADDRESS
KUP-04036: second enclosing delimiter not found
KUP-04101: record 3 rejected in file /index01/collab/EXT_TAB_BILLRUN_DIR/RevenueRegister.csv
KUP-04001: error opening file /index01/collab/EXT_TAB_BILLRUN_DIR/bad_ext_tab_billrun.bad
KUP-04017: OS message: Permission denied

-rwxr-xr-x    1 collab   oinstall        627 Sep 20 00:33 bad_ext_tab_billrun.bad
[oracle9i@:/home/oracle9i>]pwd
/index01/collab/EXT_TAB_BILLRUN_DIR


Solution:
Collab: oinstall changed to oracle9i:oinstall

oracle9i@#sudo chown -R oracle9i:oinstall bad_ext_tab_billrun.bad
oracle9i@#ls -lrt bad*
-rwxr-xr-x    1 collab   oinstall        120 Aug 30 11:12 bad_tariff_input.bad
-rwxr-xr-x    1 oracle9i oinstall        627 Sep 20 00:33 bad_ext_tab_billrun.bad

  1*  select * from abc.EXT_TAB_BILLRUN
SQL> /

SERVICE
--------------------------------------------------------------------------------
CUSTOMER_REF_NUM
-------------------------------