Author Archives: SandeepSingh DBA

About SandeepSingh DBA

Hi, I am working in IT industry with having more than 9 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Managing RMAN Backup Window Load and Duration

Managing RMAN Backup Window Load and Duration

Duration in RMAN

DURATION Clause used to complete the RMAN backup in that particular duration. If it’s not complete in that specific time then old backup is retained and new backup is discarded


PARTIAL is used with Duration to overcome the interrupted caused due to incomplete in time duration during RMAN backup.
In RUN{…} windows then after 4 hours if rman backup is not completed then it’s interrupted and stop all other commands.
To overcome this PARTIAL is used which make possible to run all other commands present in RUN{..} statements.


You can run the RUN backup as fast or low dependent upon the duration you specify in DURATION CLAUSE.

To maximize performance, use the MINIMIZE TIME option with DURATION:


To utilize the full time present in duration clause,


Note: RMAN estimate that the backup will finish before the end of the backup window then it slows down the rate of backup so it will utilize the complete window of duration.
Benefit: This reduces the overhead on the database.


ORA-20001: Statistics Advisor: Invalid task name

ORA-20001: Statistics Advisor: Invalid task name

On checking the alert log file of database i am getting the following error:


Errors in file E:\ORACLE\diag\rdbms\ic\ic\trace\ic_j000_532.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_5501"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197

Issue occurred while creating the database DBCA, the advisory package is not created properly.


1. Connect with the SYSDBA privilege user:

2. Run the following query for checking advisory package:

select name, ctime, how_created
from sys.wri$_adv_tasks
where owner_name = 'SYS'

no rows selected

3. Initilze the package with following commands.

EXEC dbms_stats.init_package();

4. Check again with step 2 you will find the result. It will fixed the issue.


ORA-03113: end-of-file on communication

ORA-03113: end-of-file on communication channel

Oerr Utility output:
Cause: The connection between Client and Server process was broken.
Action: There was a communication error that requires further investigation.

When we are going to start the Oracle database, i am getting the ORA-03113 error during the startup command. My first step is looking into the Alert log what is the cause of error and find the solution according to cause.


SQL> startup
ORACLE instance started.
Total System Global Area 23584982528 bytes
Fixed Size 2452778 bytes
Variable Size 4531678966 bytes
Database Buffers 2342356778 bytes
Redo Buffers 25876431 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 2588
Session ID: 1705 Serial number: 5

Case 1: Db_recovery_file_Dest is full.

1. Checked the alert log find, we find the db_recovery_file_dest is full and alert log is giving following warning:

ORA-19815: WARNING: db_recovery_file_dest_size of 2456687415514 bytes is 100.00% used, and has 0 remaining bytes available.

2. Open the Database in mount state

startup mount

3. Check and increase the parameter current value:

Show parameter db_recovery_file_dest_size
-- add 10GB size more to this parameter
alter system set db_recovery_file_dest_size = 75G scope=both

4. Open the Database.

alter database open;

5. Fixed the issue with RMAN.

RMAN> backup archivelog all delete input;

Case 2: Redo log file seems inactive or corrupted

1. Startup the instance in nomount:

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1224738936 bytes
Database Buffers 905969664 bytes
Redo Buffers 13848576 bytes

2. Open database into mount state:
alter database mount;
Database altered.

3. Clear the redo log files having issue due to power failure or unclean shutdown of database.

SQL> alter database clear unarchived logfile group 1;
Database altered.

SQL> alter database clear unarchived logfile group 2;
Database altered.

SQL> alter database clear unarchived logfile group 3;
Database altered.

4. Shutdown the database and open it.

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1224738936 bytes
Database Buffers 905969664 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.


Delimited Text Format Backups with mysqldump utility

Delimited-Text Format Backups with mysqldump utility

TAB Option
Without –tab option
MYSQLDUMP writes SQL statements to the standard output as default. Output consists of DDL statements to create dumped objects (databases, tables, stored routines, and so forth), and DML statements to load data into tables like insert. It will generate output as one file which will used to reproduced the current state of database.

With –tab option
MYSQLDUMP produces two output files for each dumped table.
One file is used for tab-delimited text, one line per table row. This file is named tbl_name.txt in the output directory.
Second file has CREATE TABLE statement for the table to mysqldump, which writes it as a file named tbl_name.sql in the output directory.
Example of tab option
For a table named t1, the files are named t1.sql and t1.txt.
The .sql file contains a CREATE TABLE statement for the table.
The .txt file contains the table data, one line per table row.

Backup with delimited format

-- Backup the database in /tmp directory
mysqldump --tab=/tmp db1

-- Backup the database with specific option as used needed for other ETL process:
mysqldump --tab=/tmp --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1

Restore or Reload Delimited file
Restore and reload the delimited file into the database.

-- First Way:
shell> mysql db1 mysqlimport db1 t1.txt

-- Second way run from mysql commands for import data:
mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;

-- First way with options:
mysqlimport --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt


MYSQLDUMP for Backup and Restore the MySQL Database

MYSQLDUMP for Backup and Restore the MySQL Database

MYSQLDUMP is a used to performs the logical backups, generating a set of SQL statements like DDL , DML for reproduced backup Schema.
It dumps one or more MySQL database for backup or transfer to another SQL server.
You can also generate output in CSV, other delimited text, or XML format.

Drawback of MySQLdump
The restoration procedure can take a long time to execute if you have a very large database.

–To make incremental backups, we need to save the incremental changes.
–In MySQL, these changes are represented in the binary log, so the MySQL server should always be started with the –log-bin option to enable that log.
–Always run the MySQL Server with the –log-bin option, or even –log-bin=log_name, where the log file name is located on some safe media different from the drive on which the data directory is located.

Backup with MYSQLDUMP
mysqldump is used to take logical backup of the database or multiple database.

Following is the syntax of MYSQLDUMP:

mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

[uname] Your database username
[pass] The password for your database (note there is no space between -p and the password)
[dbname] The name of your database
[backupfile.sql] The filename for your database backup
[--opt] The mysqldump option

--Take Backup of SalesDB database:
mysqldump -u root -p Salesdb > Salesdb_backup.sql

--Take backup of tables present in salesdb database such as TRAN, TRANX :
mysqldump -u root -p Salesdb TRAN TRANX > TRAN_TABLE_backup.sql

-- More databasez backup taken in one commands:
mysqldump --databases db1 db2 db3 > all_database_dump.sql

-- command to use mysqldump to simply backup all of your databases
mysqldump -u root -p --all-databases > alldb_backup.sql
mysqldump --all-databases > dump.sql

--single-transaction uses a consistent read and guarantees that data seen by mysqldump does not change.
mysqldump --all-databases --single-transaction --user=root --password > all_databases.sql

Options in mysqldump
–all-databases – this dumps all of the tables in all of the databases
–user – The MySQL user name you want to use for the backup
–password – The password for this user. You can leave this blank or include the password value (which is less secure)
–single-transaction – for InnoDB tables is used for taking consistent backup with mysqldump after changes present in log bin.

Restore commands in mysqldump

1. Connect with the mysql Server:

mysql -u username -p

2. Create an appropriately named database on the target machine;

CREATE DATABASE database_name;

3. Load the file using the mysql command:

mysql < dump.sql
mysql db1 < dump.sql

mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]
mysql -u root -p Tutorials < tut_backup.sql
mysql -u username -p database_name < backup_name.sql


ORA-01722: invalid number

ORA-01722: invalid number

On Executing the function to_number, convert character, date value with to_number function to number caused the ORA error because it not matched as numeric value. If table contains any special character or alphabet then Oracle throw the ORA error when it tried to convert to number.

My id column is of varchar type and i have numeric data in it. It working fine for me up-to last day but on morning my package will through the error ORA-01722. On checking the data inserted in last night having problem may some special character or alphabet is inserted with ETL Process.

SQL> select to_number(id) from test;
ORA-01722: invalid number

On checking on net from Oracle side:

ORA-01722 invalid number

The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character “E” or “e” and retry the operation.

If my table having inserted last night some character or special character instead of numeric value or decimal numeric value. We need to check the table data and find the row to fixed the issue which having special character.

For find we use regular expression query to find non numeric data in table

SQL> select column_name from table_name where REGEXP_LIKE (column name, '[^0123456789.]');

SQL> select id from test where REGEXP_LIKE (id, '[^0123456789.]');


Note: correct this lines will fixed the above error as data.


ORA-12154 / TNS-12154 TNS:could not resolve service name

ORA-12154 / TNS-12154 TNS:could not resolve service name

1. Check the tnsnames.ora and sqlnet.ora file is present on the location.

On windows: ORACLE_HOME\network\admin\

ON LINUX: ORACLE_HOME/network/admin.

2. Check the entries in tnsnames.ora is correct.

(Port = 1521) ) )

Check Tnsping command with ORCL alias, if it working or giving error

3. Check the listener on the server with listener command and check service name is present as you mentioned in above service name.

lsnrctl service

Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
"DEDICATED" established:0 refused:0
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
"DEDICATED" established:0 refused:0
The command completed successfully

Note: in this case ORCL service is not present.
1. Check database is started if not start the database first may service is register dynamically with listener it will fixed issue when database started.
2. Tried to register the service name manually by editing listener.ora file/ management console and reload the listener after changes.

4. If tnsnames having domain name as alias then check sqlnet.ora file also


Check SQLNET file support domain as:
Parameter NAMES.DEFAULT_DOMAIN=oracle need to look into the SQLNET.ORA file.
Case 1: IF parameter NAMES.DEFAULT_DOMAIN and alias.domain in tnsnames.ora present then its OK
Case 2: IF parameter NAMES.DEFAULT_DOMAIN not present and alias.domain present in tnsnames.ora then need to add NAMES.DEFAULT_DOMAIN=oracle in SQLNET.ora file.
Case 3: IF parameter NAMES.DEFAULT_DOMAIN is present and alias only used for connection as TNS entries then remove the parameter NAMES.DEFAULT_DOMAIN from SQLNET.ora
Case 4: IF parameter NAMES.DEFAULT_DOMAIN is not present and alias only used as tns entries then its OK.

5. Check after setting the Environment Variable

TNS_ADMIN environment variable.
Third: ORACLE_HOME\network\admin