How to Copy Only Contacts, Notes and Messages from iTunes after iPhone Factory Restore?

Recently I had a requirement to restore the phone to the factory default and copy only Contacts, Notes and Messages because of the LowMemory issues with my phone. I have faced lot of issues after the iOs 7 upgrade and LowMemory issue was one the one of the major one. It used to crash at least one or twice because of the low memory issue and it used to log in diagnostic report. (Settings->General->About->Diagnostics&Usage->Diagnostics & Usage Data). To fix that issue I restored the phone to the factory default and restored only contact, message and notes.

Fix: iPhone Old Messages are not Indexing in the Spotlight search after the iOS 7 Upgrade

I have done the iOS 7.1.2 upgrade in my iPhone 4 recently. My iPhone was pretty stable with the iOS 6.0.1. Due to some mandatory requirement for the OS upgrade I have performed the iOS 7 upgrade. After upgrading my iPhone 4 to iOS 7, I feel there are lot of positives and negatives. The iOS 7 gives new look and feel to the phone but I lost my stability with the phone. Lot of performance issues started with the phone like it won’t respond to our touches as looks like the phone is quite busy. Sometimes the apps are getting crashes.

Database in READ WRITE Mode; desc dba_ gives error ;ORA-04043

Error Description:

The database was working perfectly and there was no major changes done in the database. Database is in Open mode (read write); But still the describe the data dictionary views like DBA_tables gives following error.

 

SQL> desc dba_tables

ERROR:

ORA-04043: object dba_tables does not exist

 

Solution Description

 

Solve ORA-39170: Schema expression 'OPS' does not correspond to any schemas

Error Description:
Data pump expdp failed with following error message.
$ expdp directory=exp_dir schemas=ops$jobs dumpfile=exp.dmp logfile=exp.log
Export: Release 11.2.0.3.0 - Production on Thu Sep 4 07:30:24 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
ORA-39001: invalid argument value
ORA-39170: Schema expression 'OPS' does not correspond to any schemas.
 

How to Find out / Check SGA and PGA size of Oracle Database?

SGA Size 

Generally SGA_TARGET shows the value of SGA. It can be increased up to sga_max_size. But if you have enabled automatic memory management the (non-zero values for Memory_ parameters) the value for the memory_target will be consisderred for the SGA.

Find out Database Growth Month by Month Using V$datafile - Creation_time

In v$datafile there is a field called creation_time and this field is having the datafile creation date. Suppose if you are database's space addition generally happening by adding the data file then you can easily find out the database growth using this column.

This method will not be effective in the following cases.

a. If you are resizing the existing file this method will not be effective as it records the datafile creation date.

How to Check the Required Data is Present in Flashback Database or not for Recovery?

Here is the query to find out whether you can recover the lost data using the flashback database option.

SQL>select to_char(OLDEST_FLASHBACK_TIME,'dd-mm-yyyy hh:mi'), to_char(sysdate,'dd-mm-yyyy hh:mi') FROM V$FLASHBACK_DATABASE_LOG;

TO_CHAR(OLDEST_F TO_CHAR(SYSDATE,

---------------- ----------------
28-06-2014 01:34 28-08-2014 02:19

Here in above result we have the data from 28th of June as the transaction is very less with the database. Almost 2 months data can be flashback.

How to Enable/Configure and Disable Flashback (On/Off) Feature in Oracle

Pre-requesites:
a. Database must in archivelog mode.
b. Automatic undo tablespace management must be on
c. There should be enough space (depends on your requirement) in undo tablespace.
Steps
1. Verify the flashback feature is on or not using the flashback_on column in the v$database

Listener Not Starting: IBM/AIX RISC System/6000 Error: 1: Not owner TNS-12555, TNS-12560, TNS-00525

Problem Description: lsnrctl start command failing with following error message

> lsnrctl start

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 12-AUG-2014 17:15:10

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

Starting /oracle/DWR/112_64/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production

Fix ORA-30052: invalid lower limit snapshot expression

Error Description:
Oracle flashback transaction version query failing with following error message.
SQL> SELECT *
FROM dept
VERSIONS BETWEEN TIMESTAMP
      TO_TIMESTAMP('2014-07-14 14:12:00')
  AND TO_TIMESTAMP('2014-07-14 14:26:01')   ;
FROM dept
     *
ERROR at line 2:
ORA-30052: invalid lower limit snapshot expression
 

Oracle Script to Create or Drop Series of Users or Schemas

Sometimes DBAs are asked to create series of users of same pattern like xyz01 to xyNN. In such situations it is very difficult in case we are approaching with manual user creation. You can use below enclosed script for this purpose.

Script to create series of users

Find out New or Updated Rows from an Oracle Table without having Date Column

It is a common question to the DBA from the developer that is there any way to find out the rows inserted or modified recently without having the date column in the table. If you have date column inside the table it is easy job to find out the data. If you have flashback feature enabled in your database here is the way to find out. This will work out up to certain extend.

See an example below

Method to Create New Column in Oracle Table with Combination Other Columns

It is not a regular requirement. But sometimes we need it such cases. This can be achieved by the virtual columns in the Oracle.

See some examples below.

SQL>alter table emp add name_job as (ename||'-'||job);

Table altered.

Impdp Expdp Failed with ORA-31626, ORA-31633, ORA-06512, ORA-01031

Error description

Oracle data pump expdp / impdp failed with following errors.

Import: Release 11.2.0.3.0 - Production on Fri Jun 20 07:42:33 2014

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

Username: scott

Password:

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

ORA-31626: job does not exist

Use of Expdp Impdp nologfile=y

Oracle data pump has one option called nologfile and by default its value is N. The purpose of this option is that if you specify the value for this parameter as Y then it will not create the log file for the export or import activity. This is very useful option when you do an import where the oracle user doesn’t have write privilege on the directory where the dump file exists. In such cases the impdp will throw the following error.

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

Method to Track Oracle Schema User Password and Modification History; Find out Current, New Password

This is the solution to find out the Oracle user current password by recording the password history. This would be helpful to find out who changed the password for a user, when he changed, for which user with new password. From the recorded history you can find out the previous/old as well as current password for the Oracle user. This is achieved by oracle autonomous_transaction and profile PASSWORD_VERIFY_FUNCTION.

Here are the steps:

Sql Query to Find and Reclaim the Fragmented Space in Oracle Table

How the table hits by space fragmentation?

If a table has large number of records and the particular table is getting updated or the rows getting deleted periodically then there will be unused blank spaces (holes) in the table segments. These blank spaces will get created by the row deletions which will not be used without reorg or reclaiming. Over a period of time the volume of the unused space will get accumulated to a huge size. These unused spaces are called fragmentations. The oracle will not automatically release this space into usable free space whereas we have to perform the reorg activity to claim the fragmented space.

Example for Table Reorg and Reclaim the Fragmented Space Using Shrink Space Command

Advantages of this method:

        - Syntax: alter table

shrink space

        - Most recommended method

        - Applicable for the databases 10g and above

        - No downtime required

        - None of the depended objects will get invalidated.

        - Least time consuming method

Example
//Create a test table and insert testing data

Example for Table Reorg and Reclaim the Fragmented Space Using Table Movement

        - It is time a consuming method

        - Application down time required as the table will not be available during the time of activity

        - There are chances for the depended objects invalidation. It has to be properly verified after reorg

        - Indexes also needs to be rebuild which requires an extra time apart from the table movement

        - This activity requires extra space on other tablespace to move the table.

Difference Between Oracle Table Shrink Space, Space Compact, Space Cascade - with Example

Table shrink is an interesting feature provided by Oracle and this is very useful for the database reorganizations. There are 3 different options available with table shrink command. The table shrinking is achieved by moving the rows between the Oracle blocks. To perform the table shrinking we need to enable the row movement for the table.

Option 1. Alter table table_name shrink space

How to Shrink a Table in Oracle Database - With Example

Database reorg is the major activity for the Oracle DBAs. Oracle provides shrink table feature to reclaim the space within table after doing the deletes. Shrink is accomplished by rearranging or moving the rows between the oracle blocks. To achieve this table shrinking we need to enable the row movement feature which is associated with the table.

Example
//Creating an example table
SQL> create table objlist as select * from  all_objects;

Recursive Function, Procedure, PLSQL in Oracle

In oracle we can write PLSQL blocks with recursive calling.

1.   See an example of recursive function for printing multiple table.

SQL> create or replace function mul (n number) return number as

begin
      IF n <= 1 THEN
            dbms_output.put_line(to_char (n) ||' x 3 = '|| to_CHAR (3*n));
            RETURN 1;   
      ELSE
            dbms_output.put_line(to_char (n) ||' x 3 = '|| to_CHAR (3*n));   
            return mul(n-1);

DGMGRL ORA-16600: not connected to target standby database for failover

Error Description:

DGMGRL data guard failover filed with following error.
DGMGRL> failover to 'MQPRD';

Performing failover NOW, please wait...

Error: ORA-16600: not connected to target standby database for failover

Failed.

Unable to failover

DGMGRL Error: ORA-16647: could not start more than one observer

Problem Description:

DGMGRL start observer failed with Error: ORA-16647: could not start more than one observer
Failed.
DGMGRL> start observer;
Error: ORA-16647: could not start more than one observer
Failed.
DGMGRL> exit
Solution Description

The problem is the observer is already started.

DGMGRL Error: ORA-16820: fast-start failover observer is no longer observing this database

Error Description

Data guard DGMGRL show configuration command gives Error: ORA-16820: fast-start failover observer is no longer observing this database

DGMGRL> show configuration verbose

Configuration - TRPROCONFIG
  Protection Mode: MaxAvailability
  Databases:
    TRPRO         - Primary database
      Error: ORA-16820: fast-start failover observer is no longer observing this database
    TRPRO_STANDBY - (*) Physical standby database

ORA-06512 ORA-06503: PL/SQL: Function returned without value

Error Description:
Select from the oracle function return the following error
SQL> select fib(3) from dual;
       *
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "STHOMAS.FIB", line 4
Solution Description:

A function must return a value when you select from the same.

Oracle Session Disable Commit in Procedure

This is an interesting feature. This is a very useful feature to test a Oracle procedure without committing the actual changes made by the procedure. If you disable the commit in procedure you will get an error while executing the procedure.

See an example here.

SQL> create procedure xyz is

begin

Oracle Query to find out the SQL Text Using the Unix Process ID SPID, SID and for Long Running Sessions

If you have process id (SPID) with use following query

set pages 1000

set lines 120
col sid for 99999
col username for a20
col sql_text for a80

SELECT  T.SQL_TEXT FROM v$SQLTEXT T, v$SESSION S

WHERE   S.SQL_ADDRESS=T.ADDRESS and s.status ='ACTIVE'

Sequence Default Cache value 20; ORA-04013: number to CACHE must be less than one cycle

Error Description

SQL>   create sequence seq_norm start with 1 maxvalue 100 increment by 25 cycle;

  create sequence seq_norm start with 1 maxvalue 100 increment by 25 cycle

*

ERROR at line 1:

ORA-04013: number to CACHE must be less than one cycle

Difference Between Oracle Join and Union

Join:

·        Joins are used to join two or more tables to extract the data

·        Joins are used to retrieve the relational based data from the tables

·        Joins mainly used to filter the records from the multiple tables

·        Joins can be used to retrieve the matching and non-matching data

Change / Modify / update AdSense Publisher Bank Account for EFT Wire Transfer

Google AdSense introduced EFT / wire transfer for the Indian publishers. You can upgrade your payment mode to the new system with the following steps. Suppose if you have already updated the bank account once and if you wanted to change it to some other account also you can refer this following steps.

Follow these steps to change your publisher bank account details to new one.

IMPDP Import Only the Rows Does not exist in the Target Table

This is a question or requirement which I got from one of my blog reader. “How to import only the rows does not present in the targeted table?”. The rows present in the table should not be deleted and import only rows which does not exist in the database target table. This can be achieved by using the combination of table_exists_action and query options.

Here I have given an example with normal import and with data pump API import.

a.   Normal import method.

EXPDP Filesize : Split or Slice the Dump file into Multiple / Different Directories and File Systems

Suppose if your database size is big or the size targeted data for the export is too high, you cannot accommodate the dump file in single file. Sometimes the OS will not allow you to create such big files.

Oracle Sequence Cycle vs Nocycle Difference and Comparison

The cycle option repeats the same set of sequence values once it reaches the max value. The next value will be reset to minimum value once it reaches the max value. The cycle option will end up with error if you are using this sequence for the primary key values as it won’t allow the duplicates. 

Show Error Returns with No errors: How to find out PLSQL Compilation Errors?

Error Description

The PLSQL object created with compilation error. But SQLPLUS show error returns ‘No errors.’

SQL> create force view VW_Force_Invalid

    as select * from bonus;

Warning: View created with compilation errors.

SQL> show error

No errors.

How to find out the Size of the Oracle Database (Any Versions 10g, 11g)

Col DBFSizeGB for 99999.99

Col TMPFSizeGB for 99999.99

Col LOGFSizeMB for 99999.99

Col CTRLSizeGB for 99999.99

Col TotalSIzeGB for 99999.99

 

select DBFSizeGB, TMPFSizeGB, LOGFSizeMb, CTRLSizeMB, DBFSizeGB+TMPFSizeGB+LOGFSizeMb/1024+CTRLSizeMB/1024 TotalSIzeGB

Oracle pfile vs spfile: Difference and Comparison

Pfile/Init file

1.   It is used for database startup

2.   It is also called INIT file

3.   It is a text file which contains all the main parameters for the database startup

4.   This can be edited using notepad or Vi editor

Oracle SQL, Function for finding out Age from DOB

PLSQL Script for Function

create or replace function dob (birth char) return char as

x date;

age varchar2(30);

begin

  select to_date(birth,'ddmmyyyy') into x from dual;

  select trunc(months_between(sysdate, x)/12) ||' Years '||

trunc(mod(months_between(sysdate, x),12)) ||' Months '||

trunc(sysdate-add_months(x, trunc(months_between(sysdate, x)/12)*12+trunc(mod(months_between(sysdate,x),12))))|| ' Days ' into age

from dual;

return age;

exception

Create or Alter User failed: ORA-00600: internal error code, arguments: [kzsviver:4]

Error Description

Create user command failed with following error.

SQL>  CREATE USER "ST" IDENTIFIED BY VALUES 'S:E285C80B22886275424125E6A479E3B9CA84

F5FE51932DE4EF028DEA2CBF;D766CF03D5031EDC'    ;

 CREATE USER "ST" IDENTIFIED BY VALUES 'S:E285C80B22886275424125E6A479E3B9CA84

*

ERROR at line 1:

TRACEFILE_IDENTIFIER Parameter Oracle 11g

This parameter is used to identify the tracefile easily from the directory. When you specify or set this parameter with a value that value becomes the part of the trace file name. So that easily can be identified from the directory. This is a session modifiable parameter and each time when you modify the parameter value the name of the very immediate trace file will get effect of the same.  

Putty Configuration Error: There is no disk in the drive. Please insert a disk into drive

Error Description:

After putting/accessing a DVD in the drive, while opening the putty.exe it is giving the following error message.

PuTTY Configuration: There is no disk in the drive. Please insert a disk into drive D:.

Data Pump Expdp ESTIMATE_ONLY Parameter

This parameter is useful when you wanted get the approximate size of the dump file. Suppose you have only limited space available in the disk and if you wanted to check whether your dump will be fit into the available space then you can use this parameter to check or estimate the size of the dump with doing the actual export. Some facts are here about estimate_only parameter.

Oracle SQL to Check the Database / Instance Name to Which the Current Session Is Connected using a Non DBA user

There are couple of options to find out the database name for the current session. If you are a DBA a privileged user/session you can query any data dictionary views. But it is not the same case for non-dba user session.

Here are the options.

Data Pump Expdp Impdp Metrics=Y Undocumented Parameter

This is an undocumented parameter for the data pump. The Matrics=y option will log the list of objects exported or imported in each category and the time taken for each category objects. All the details will be logged in the logfile. See an example below.

$ expdp dumpfile=tab_exp.dmp logfile=tab_exp.log schemas=sthomas metrics=Y directory=exp_dir

Invoke Data Pump EXPDP IMPDP export / import Using SQL Developer

Using SQL Developer 4, you can initiate the data pump export or import job. It has export wizard under the menu view -> DBA. Using this you don’t need to aware about the data pump options to invoke a data pump job. All those data pump options can be invoked through the wizard. Here I will show you an example for one export job.

Data Pump Impdp show=y Replaced with SQLFILE Option

Most of the legacy mode options will work in the data pump with its corresponding options. In legacy mode (imp utility) we have show=y option to spool the content of the dump file into sql scripts without doing the actual import. The same command can be used in data pump impdp also but it will get replaced with sqlfile option.

See one example below.

Create File or Modify the Existing UNIX/LINUX File With Old / New Timestamp

Use touch command to change the timestamp of a file to newer or older one. Also, this command can be used to copy the timestamp between files.

touch –t  [[CC]YY]MMDDhhmm [.SS] filename

where          MM - The month of the year [01-12].

           DD - The day of the month [01-31].

Unix Shell Script to Find out Files Created within x Hours Without Using mmin / cmin for Solaris

HW=3 # Modify this variable value. In this example it will find out the files created within last 3 hours.

a=$( date +%H )

if [ $a -gt $HW ]; then

    a=$(( $a - $HW ))

else if [ $a -lt $HW ]; then

    a=$(( $a + 24 - $HW ))

else

    a=00

Replace SQL Null Values with SQLPLUS Set Null Command

You can use the SQL NVL function to handle the null values in the query. Second option is SQLPLUS set null feature to replace the null values with user defined values.

See an example here.

We have a table DEPT with some of the HOD column values are null. 

Create a Case Sensitive Table in Oracle / Table Name in Lower Case

In the same oracle schema you can create 2 tables with same name one is in small case and second one with upper case. By default all the DML, DDL command applicable to the table name with upper case. If you wanted to access the table with lower case you have to specify the table name with double quote (“)

See some examples here.

How to Check the Number of Concurrent Users in the Oracle Database Dynamically?

There are two ways or two dynamic views to check this -v$license and v$session

The sessions_current column from the v$license gives the current count of the users in the database. This count will not have the oracle by default connection whereas it has only the application user connections are on live as of now.

Fix ORA-01927: cannot REVOKE privileges you did not grant

Error Description: 

Revoke privilege operation/SQL command failed with following error message ORA-01927: cannot REVOKE privileges you did not grant 

Compress/zip Sqlplus Spool Output Automatically with Sql Execution Using UNIX Pipe

We generally use the oracle sqlplus spooling feature to log the output of the sql commands. If the output of the SQL command is too big in size, in such cases better to zip/compress the spool file. Again the available free space in the file system is too low to make the zip after creating the spool file is bit risky. Sometimes there will not be room for hold the actual and zip file during the compress process.

SQLPLUS: Create Spool File with DB Schema Name and Current Time and Date/Sysdate

Sometimes it is better to give database/schema and timestamp for the spool files which get created from the sqlplus prompt. If you are keeping all the spool files in single directory this method will be useful to pick up the right spool file out from all files.

Here is the method to spool a file with database name, schema name and current timestamp.

Rename Oracle 9i, 10g, 11g Datafile with Blank Space/White Space/Special/New line Characters

There are chances of mistake in life and the same kind of mistakes can happen in the DBA life also. The mistake on the production database is not advisable. J But anyway, there are chances of adding special characters by mistake in the data file name while adding the datafile to the tablespace. You can rename the datafile using following steps.

Here in this example I am adding one datafile with special character and new line character.

How to Append Command Outputs To Oracle SQLPLUS Spool File

Generally we use sqlplus spool command to record the sql command outputs to a file. It will record the command outputs between spool on and spool off. Generally once you done with the spool off command the output file will be closed from the further logging.

Oracle 10g onwards one more option added to the spool command to append the sql command to an existing file.

Fix ORA-14400: inserted partition key does not map to any partition

Problem description:

Insert to a partitioned table from the application is getting failed with following error message.

java.sql.SQLException: ORA-14400: inserted partition key does not map to any partition

Solution Description

First of you have to check whether in your partitioned table has a default partition.

How to Check Oracle 9i, 10g, 11g Scheduler Jobs Status

Oracle 9i and above you can use following query from the DBA_JOBS view to get the details like description, next execution, last successful execution etc.

SQL>SELECT JOB, SUBSTR(WHAT,1,45), LAST_DATE, NEXT_DATE, NEXT_SEC, BROKEN FROM DBA_JOBS;

       JOB SUBSTR(WHAT,1,45)                             LAST_DATE           NEXT_DATE           NEXT_SEC B

---------- --------------------------------------------- ------------------- ------------------- -------- -

        41 pk_cost.p_start_cost_batch;                   2014-01-30 11:35:30 2014-01-30 11:55:30 11:55:30 N

        48 DBMS_JOB.NEXT_DATE ( 41,to_date(sysdate + 5/4 2014-01-29 23:44:17 2014-01-30 23:44:17 23:44:17 N

How to Run Expdp Impdp Jobs in Background

Step 1: Create export or import parameter file

$ cat exp.par

userid=sthomas/tiger

job_name=tab_export

directory=EXP_DIR

dumpfile=TST_table_exp.dmp

logfile=TST_tab_exp.log

REUSE_DUMPFILES=y

tables=sox.PROFILE,sox.PROFIL_ACC_TYPE,sox.PURCHASE_STEP,sox.RULE,sox.SUB,sox.SUB_TABLE,sox.TIME_PERIOD

Without WWW Blogger Blogspot Domain is not Working- “Not Found 404”

Problem Description:

Blogger custom domain address is not working without www. It is giving “Not found 404” error code.

Solution Description:

Problem is quite critical with respect to a web site as the site will lose good amount of traffic because of this error. But the solution is simple just to make some settings change in the blogger which will fix this errorJ. Here is the solution.

How to Fix ORA-20005: object statistics are locked (stattype = ALL)

Problem Description:

Dbms_stats.gather_table_stats, analyze table command failed with following error.

 

BEGIN dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => ‘TRANSACTION_ID’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE,method_opt => ' FOR ALL COLUMNS SIZE AUTO', degree => 8); END;

 

*

Pay BESCOM Electricity Bill Online for Sarjapur Location

BESCOM has recently enabled many of the rural places into online payment. You can use www.bescom.org website for the online payment which is very convenient. It is happening through Billdesk which is having a nominal service charge as mentioned below.  

Processing Fee for each transaction

How Can I Get vi Editor Commands History

Option 1:

        Open vim editor and press then (colon Key) :

        Use up and down arrows to see the previous command history.

Option 2:

        Open vi editor and press then :history

        This will list around 20+ commands in the screen.

Option 3

        Open the .viminfo file in the home directory which will give you the list of commands which got executed preiously.