Database Technologies...!

Below steps can be followed to import data from one schema to another, inside single db instance. impdp with network_link is used. Realted question on OTN --> https://community.oracle.com/message/13652057#13652057 SQL> create public database link impdpt connect to SYSTEM identified by abc123 using 'DG1'; Database link created. SQL> [oracle@prima admin]$ impdp system/abc123 directory=DATA_PUMP_DIR network_link=impdpt schemas=hr remap_schema=hr:hrtn logfile=test_same.log Import: Release 11.2.0.4.0 - Production on Tue Dec 29 02:53:24 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. 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 Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=DATA_PUMP_DIR network_link=impdpt schemas=hr remap_schema=hr:hrtn logfile=...

Came up with this very simple script to automate the restore of control file from given backup. This was to answer a question posted on OTN, https://community.oracle.com/message/13668456#13668456 [oracle@prima ~]$ cat cnt_rst.sh export ORACLE_HOME=/orahome/product/11.2.0/dbhome_1 export ORACLE_SID=DG1 bdate=20151214 rman target / cmdfile='to_os.scr' log='os_file.txt' #bpc=`cat os_file.txt | grep -i $bdate | grep -i ncnn | cut -d '/' -f 2-` - to restore from piece bpc=`cat os_file.txt | grep -i 20151214 | grep -i tag: | awk '{print $NF}'` echo "run {" >> rst_cnt.scr #echo "restore controlfile to '/home/oracle/cnt_$bdate.cnt' from '/$bpc';" >> rst_cnt.scr - to restore from piece echo "restore controlfile to '/home/oracle/cnt_$bdate.cnt' from tag $bpc;" >> rst_cnt.scr echo "}" >> rst_cnt.scr rman target / cmdfile='rst_cnt.scr...

Oracle version -- Oracle 9i to 11g While splitting partitions, SQL> alter TABLE ARBOR.CDR_BILLED split PARTITION CDR_BILLED_MAXVALUE at (TO_DATE('2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) into (partition CDR_BILLED_01SEP14,partition CDR_BILLED_MAXVALUE) TABLESPACE "C01_CDR_BILL" update global indexes; 2 into (partition CDR_BILLED_01SEP14,partition CDR_BILLED_MAXVALUE) TABLESPACE "C01_CDR_BILL" update global indexes * ERROR at line 2: ORA-14126: only a <parallel clause> may follow description(s) of resulting partitions 4:58:56 PM The reason for above error is while splitting max value partition, it is not allowed to explicitly provide a tablespace. The splitted partition will be reside on the same tablespace as the max value partitions. Below can be used without the tablespace clause to split the max value partition, alter TABLE ARBOR.CDR_BILLED split PARTITION CDR_BILLED_...

DB2 version - 10.1 OS version - AIX 6.1 After restoring db2 version 9.5 coldbackup on top of db2 v10.1 for upgrade, application users who logged in via 3rd party gui was getting the below error, DB2 SQL error: SQLCODE: -5193, SQLSTATE: 42524, SQLERRMC: null On the diag log bellow error was repeated when ever client was executing a query, FUNCTION: DB2 UDB, routine_infrastructure, sqlerCallDL, probe:5 RETCODE : ZRC=0x801A006D=-2145779603=SQLZ_CA_BUILT "SQLCA has already been built" when the user tries to query directly via the server it self, SQL5193N  The current session user does not have usage privilege on any enabled workloads.  SQLSTATE=42524 So although the first error was not very clear it was caused by not having workload privilege. By adding the user to a default workload as below, error can be resolved. bash-3.2$ db2 create role ROLE_REGULAR_USERS; DB20000I The SQL command completed successfully. bash-3.2$ db2 grant role ROLE_REGULAR_USERS t...

DB2 Version – 9.5 Fixpack 3 OS – AIX 5.3 SQL10004C An I/O error occurred while accessing the database directory. SQLSTATE=58031. This error was returned during the restoration of db backup on a new server. As per the docs and references this can be caused by several reasons including OS level permission, db and os level version etc.. But quick workaround that can be tested to resolve this error while performing a db2 restore is to try the restoration with different database directory without using the default one with the use "dbpath on" clause of db2 restore command.  By adding "dbpath on" to the restoration command as below, db2 "restore database CPSDB from '/data12/backup' dbpath on '/data12/cpsjun15' into cpsjun15 logtarget '/data12/cpsjun15/log' redirect"

Version -- Oracle 11.2.0.4 In most of the oracle production dbs public executes are revoked as per the security requirements. But doing so can cause some of the functionalities to fail. Here we will discuss the effect on ACLs by revoking public execute on UTL_* packages. Revokes --> 'revoke execute on UTL_TCP from public' 'revoke execute on UTL_HTTP from public' 'revoke execute on UTL_SMTP from public' now the ACL will not work since the users assigned to ACL do not have the required privileges on ULT packages. But this also triggers another event. select * from dba_network_acls will return a ora-00600 as below.   ERROR at line 1: ORA-00600: internal error code, arguments: [qmxqtmChkXQAtomMapSQL:2], [], [], [], [], [], [], [], [], [], [], [] 10046 trace for the session shows below,  XDB.DBMS_CSX_INT is invalid. Querying dba_objects show lots of invalid objects owned by XDB and SYS. To resolve, First UTL pacakage privileges were granted to XDB u...

OS - *ix Some useful sed, awk and bash commands for dbas. Some of them needs bit of improvements and might well have other better alternatives (e.g. one liners). 1. Provided with line by line list which needs to be put in to sql " IN " clause. [stefan@oc8233082860 ~]$ cat list.txt RMS_USER_ROLE_DEFINITION RMS_USER_ROLE RMS_USER_DETAILS [stefan@oc8233082860 ~]$ cat list.txt | awk -vORS=, '{ print $1 }' | sed 's/,$/\n/' > list2.txt [stefan@oc8233082860 ~]$ sed -r 's/,([^ ),]+)/,'\''\1'\''/g; s/,,/,'\'\'',/g' list2.txt RMS_USER_ROLE_DEFINITION,'RMS_USER_ROLE','RMS_USER_DETAILS' [stefan@oc8233082860 ~]$ 2. Getting datafile name removing the directory path. [stefan@pc_stefan ~]$ cat cmd_test.txt /dc2_db2/PACS/data/system.dbf /dc2_db2/PACS/data/undo.dbf /dc2_db2/PACS/data/sysaux.dbf /dc2_db2/PACS/data/pacs_cust_data01.dbf /dc2_db2/PACS/data/...