Move a datafile with RMAN

Using RMAN is the best way before 12c to relocate a datafile with a minimum downtime. The only downtime is for switching from the old datafile to the new one and recover it. That means that the datafile size as no impact on the downtime.

Check schmea report before moving datafile.

Here is the situation before moving the datafile

RMAN> report schema
2> ;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name XE

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 360 SYSTEM *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
2 650 SYSAUX *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
3 25 UNDOTBS1 *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
4 100 USERS *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF


List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP.DBF


1.Create a copy of datafile to new destination.

The first step is to create a copy of your datafile in the new destination. Here, we will copy the USERS datafile to
C:\ORACLEXE\APP\ORACLE\ORADATA2\XE:


RMAN> copy datafile 4 to 'C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\USERS.DBF';

Starting backup at 14-01-27
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF
output file name=C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\USERS.DBF tag=TAG20140127T132133 RECID=1 STAMP=837955296
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-01-27

2.Take tablespace offline.

Now I hae a copy of my datafile in the new destination, I have to switch from the old datafile to the new one. Of course this operation cannot be done online, I’ll have to put my tablespace offline, this is the begining of the downtime.

RMAN> SQL 'ALTER TABLESPACE USERS OFFLINE';

sql statement: ALTER TABLESPACE USERS OFFLINE

3.Switch old datafile to new datafile.

Now I can switch from the old datafile to the new one:

RMAN> SWITCH DATAFILE 4 TO COPY;

datafile 4 switched to datafile copy "C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\USERS.DBF"

4.Recover the datafile.

Now we have to recover the datafile:

RMAN> RECOVER DATAFILE 4;

Starting recover at 14-01-27
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 14-01-27

5.Put tablespace back to online.

And we put back the tablespace USERS online:

RMAN> SQL 'ALTER TABLESPACE USERS ONLINE';

sql statement: ALTER TABLESPACE USERS ONLINE

6. Check status now.

Here is the new situation:

Report of database schema for database with db_unique_name XE

RMAN> report schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 360 SYSTEM *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
2 650 SYSAUX *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
3 25 UNDOTBS1 *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
4 100 USERS *** C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\USERS.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP.DBF

The old datafile is kept as copy:

7.Check old datafile copy.

RMAN> list copy of datafile 4;

List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
2 4 A 14-01-27 379024 14-01-27
Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF

Check 8.Remove old copy if do not need

We can remove it as we no longer need it:

RMAN> DELETE DATAFILECOPY 2;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
2 4 A 14-01-27 379024 14-01-27
Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF

Do you really want to delete the above objects (enter YES or NO)? yes
deleted datafile copy
datafile copy file name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF RECID=2 STAMP=837955601

Deleted 1 objects


And its done :)




I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

ora-01244: unnamed datafile(s) added to control file by media recovery

Cause: Media recovery with a backup control file or a control file that was rebuilt, encountered the creation of a datafile that was not in the control file. An entry has been added to the control file for the new datafiles, but with the file name UNNAMEDnnnn, where nnnn is the file number. Attached errors describe the file names that were originally used to create the files.

Action: Rename the files to valid file names and resume recovery. If necessary the command ALTER DATABASE CREATE DATAFILE may be used to create a file suitable for recovery and do the rename. If the file is not going to be recovered then take it offline with the FOR DROP option.

You'll encouter below error in alert log:

Errors in file /u01/app/11.2.0.3/diag/rdbms/odsprod/odsprod/trace/odsprod_pr00_16095.trc:

ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 684: '/OSP/Data45/osp/data/tblspc_osp_686.dbf'
Slave exiting with ORA-283 exception

And your database recovery will abort

Now there can be a couple of scenarios, where in you may encounter this error, and their respective solutions .

1.You restored a full database and are recovering it through archives from source

Here, you are recovering after a full restore and hence the error says that the required data file doesn't exist.

To over come this issue, you simply have to execute below SQL:

alter database create datafile file#_from_error as '/new/path/to/data/file.dbf';

So, in our case that would be:

SQL> alter database create datafile 684 as '/bkp_loc/OSP/data/tblspc_osp_686.dbf';

The path is where you want the data file to be created. This can be similar to the one reported in error (if you have same path as source) or could be different, as in this case.

Also, if you are using ASM or to create Oracle managed datafile name

SQL> alter database create datafile 684 as new;

Now, you can continue with your recovery.

2.You lost a control file and restored it from a backup after which you had added data files to the database

Here, the data files already exist, (but they didn't at the time you backed up control file) but just that the restored control file is from time before the data files were added and hence not aware of them.

So, in this case, we can just rename the file to its existing file:

SQL> alter database rename file '/u01/app/product/11.2.0/dbhome_1/dbs/UNNAMED00684' to '/OSP/Data45/osp/data/tblspc_osp_686.dbf';

While renaming or recreating, just ensure that the target file being created doesn't already exist or if renaming, you aren't renaming it to other existing data files.

In any case if you know that you dont need that file, you can mark the same offline

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

SQL Tuning Advisor for SQL_ID

Here we are going to discuss how to do SQL Tuning Advisor for SQL_ID.

1.Create SQL Tuning Advisor task

DECLARE

  my_task_name VARCHAR2(30);

begin

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '7a6b4442j5pcz',scope => 'COMPREHENSIVE',time_limit => 60,task_name => 'STA:7a6b4442j5pcz',description => '7a6b4442j5pcz');

end;

/

2.Check if the task has been created

SELECT task_name FROM DBA_ADVISOR_LOG WHERE owner = ‘TEST’;

3. Run Task 

EXEC DBMS_SQLTUNE.execute_tuning_task( task_name => 'STA:7a6b4442j5pcz');

3.Checking the Status

SELECT status FROM DBA_ADVISOR_TASKS WHERE task_name = ‘STA:7a6b4442j5pcz’;
SELECT sofar, totalwork, username FROM V$ADVISOR_PROGRESS ;

3. View results 

SET LONG 10000

SET PAGESIZE 1000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('STA:7a6b4442j5pcz') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('STA:7A6B4442J5PCZ')

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

GENERAL INFORMATION SECTION

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

---

Tuning Task Name                  : STA:7a6b4442j5pcz

Tuning Task Owner                 : SYS

Scope                             : COMPREHENSIVE

Time Limit(seconds)               : 60

Completion Status                 : COMPLETED

Started at                        : 10/16/2012 19:47:

27

Completed at                      : 10/16/2012 19:47:54

Number of SQL Profile Findings    : 1

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

FINDINGS SECTION (1 finding)

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

-----------

1- SQL Profile Finding (see explain plans section below)

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

  A potentially better execution plan was found for thi

s statement.

  Recommendation (estimated benefit: 99.94%)

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

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_nam

e => 'STA:7a6b4442j5pcz',

            replace => TRUE);

4.Delete task.

BEGIN

DBMS_SQLTUNE.drop_tuning_task (task_name => 'STA:7A6B4442J5PCZ');

END;

/

hope this article helped you. Your suggestions/feedbacks are most welcome.

Keep learning... Have a great day!!!

To Find out If MySQL Is Running On Linux Or Not

Recently got a chance to work on Mysql database.So would like to share few besic article reagrding mysql Database.Below article will help you to check whether your mysql server is runnign or not.

You can use mysql startup script or mysqladmin command to find out if it is running on Linux. Then you can use ps command and telnet command too (it is not reliable but it works.). mysqladmin is a utility for performing administrative operations. You can also use shell script to monitor MySQL server. You can use mysqladmin as follows:

# mysqladmin -u root -p status

Output:

Enter password:
Uptime: 4  Threads: 1  Questions: 62  Slow queries: 0  Opens: 51  Flush tables: 1  Open tables: 45  Queries per second avg: 15.500

If MySQL serer is running it will display output as above. It displays uptime and number of queries etc. If server is not running then it will dump error as follows
# mysqladmin -u root -p status
Output:

mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

Under Debian Linux you can type following command to find out if MySQL server is running or not

# /etc/init.d/mysql status

Output:

/usr/bin/mysqladmin  Ver 8.41 Distrib 4.1.15, for pc-linux-gnu on i486
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version          4.1.15-Debian_1-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 4 min 16 sec
Threads: 1  Questions: 66  Slow queries: 0  Opens: 51  Flush tables: 1  Open tables: 45  Queries per second avg: 0.258

If you are using RedHat of Fedora then you can use following script"
# service mysqld status

OR

# /etc/init.d/mysqld status

 hope this article helped you. Your suggestions/feedbacks are most welcome.

Keep learning... Have a great day!!!

EXP-00002: error in writing to export file

While exporting table or schema using exp/imp utility you may come across below error.

Most of the time this error occurs due to insufficient space available on disk.so confirm space is available where you are taking taking export dump and re-run export.

[oracle@DEV admin]$ exp test/test@DEV tables=t1,t2,t3,t4 file=exp_tables.dmp log=exp_tables.log

Export: Release 9.2.0.8.0 - Production on Thu Sep 10 12:25:52 2015

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table t1 1270880 rows exported

. . exporting table t2 2248883 rows exported

. . exporting table t3 2864492 rows exported

. . exporting table t4

EXP-00002: error in writing to export file

EXP-00002: error in writing to export file

EXP-00000: Export terminated unsuccessfully

I hope this article helped you. Your suggestions/feedbacks are most welcome.
Keep learning... Have a great day!!!

How to catalog backups / archivelogs / datafile copies / control file copies

RMAN Catalog:It is mainly used for following:

Can store database backup scripts and backup information to safe place(RMAN Repository)apart from target database control file.
Can add backup pieces and image copies of backup on disk to the RMAN repository.
Can record a data file copy as a level 0 incremental backup in the RMAN repository and that can be used foran incremental backup.
Can record latest user-managed datafile copies to RMAN repository made after the clean shutdown of database.

Limitations and Advantages of Catalog:

To connect to target database using RMAN target database must be mounted or open.

1. Catalog database must be OPEN if RMAN is connected to catalog database.
2. RMAN can only catalog a datafile copy, control file copy, archived log or backup piece if these areuser-managed copy (OS level copy).
3. Catalogis accessible on disk.
4. File belongs to another database can not be cataloged using CATALOG.
5. All user managed (File system) backups considered as an IMAGE COPIES by RMAN while cataloged.
6. RMAN does not check whether the file was correctly copied by the operating system utility; it just reads and checks the header during cataloging but file should be clean(consistent) while copying through OS utilities so .

Cataloging Backups Examples:

1. Cataloging Archived Logs:Here we assumed that archive logs have been moved to other location using operating system utilities (CP/MV/COPY) and we cataloging those moved Archiveloged to RMAN REPOSITORY.

RMAN > CATALOG ARCHIVELOG ‘?/oradata/backup/arch_1.dbf’, ‘?/oradata/backup/arch_9.dbf’;

If you need to catalog multiple archive files then use below simple script which will generate .rcv file and run newly created .rcv file.

[oracle@test ~] echo > catalog.rcv for archivelog in /u01/test/arch/* ; do echo "catalog archivelog '$archivelog';" >> catalog.rcv done

Now connect to rman promt and run .rcv using below command.

[oracle@test ~] Rman target / cmdfile=catalog.rcv

2.Cataloging consistent copy of datafile as an incremental backup:Here we are cataloging datafile copy data01.dbfas an incremental level 0 backup to RMAN repository,

This datafile copy must be consistent and can be either taken by OS copy commands or by RAMN backup as copy command:

CATALOG DATAFILECOPY ‘?/oracle/oradata/data01.dbf’ LEVEL 0;

3.Cataloging controlfile copy in the RMAN Repository tken by user:

a. Take backup of controle file by using below query:

SQL> alter database backup controlfile to ‘/u01/bkp/controlfile01.dbf’;

b. Catalog above controlfile copy to RMAN REPOSITORY using blow query:

RMAN> catalog controlfilecopy ‘oracle/oradata/control01.ctl’;

4.Cataloging all the database file copies present in one Directory:

Below example will catalog all the archivelog copied using OS command to directory /u01/bkp/archlog:

RMAN> CATALOG START WITH ‘/u01/bkp/archlog’;

5.Cataloging Files in the Flash Recovery Area:Below will catalog Flash Recovery Area without prompting to user:

CATALOG RECOVERY AREA NOPROMPT;

6. Cataloging RMAN Backup Pieces:BELOW WILL CATALOG a RMAN backup piece to RMAN Repository which is manually copied using OS commands to default backup location:

CATALOG BACKUPPIECE ‘/u01/bkp/rman/KOKTSV_ORCL_RMAN_ONL_ST’;

7.Below command will catalog the Backup Pieces present in the Location set for DB_RECOVERY_FILE_DEST Parameter:

RMAN> catalog db_recovery_file_dest;

Note:catalog backuppiece, catalog recovery area, catalog start with, catalog db_recovery_file_dest commands are not available in 9i.

I hope this article helped you. Your suggestions/feedbacks are most welcome.

Keep learning... Have a great day!!!

Oracle Baseline/ SQL plan migration to its DEV/QA/Clone

In this article I have explained step by step how to copy/migrate SQL baseline from source database to target database.

On Source database

============ 

Check the list of SQL plan baselines on Source

SQL> select SQL_HANDLE,plan_name, enabled, accepted FROM dba_sql_plan_baselines ;


SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyv0239c560 YES NO
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyv3fc6434b YES NO
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyvccdd3f88 YES YES

Create the staging table to hold the SQL baseline

SQL> BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name > 'spm_staging_tab',
table_owner > 'BIASDBA',
tablespace_name > 'TOOLS');
END;


PL/SQL procedure successfully completed.

Check the baseline staging table

SQL> desc biasdba.spm_staging_tab
Name Null? Type
----------------------------------------- -------- ----------------------------
VERSION NUMBER
SIGNATURE NUMBER
SQL_HANDLE VARCHAR2(30)
OBJ_NAME VARCHAR2(30)
OBJ_TYPE VARCHAR2(30)
PLAN_ID NUMBER
SQL_TEXT CLOB
CREATOR VARCHAR2(30)
ORIGIN VARCHAR2(30)
DESCRIPTION VARCHAR2(500)
DB_VERSION VARCHAR2(64)
CREATED TIMESTAMP(6)
LAST_MODIFIED TIMESTAMP(6)
LAST_EXECUTED TIMESTAMP(6)
LAST_VERIFIED TIMESTAMP(6)
STATUS NUMBER
OPTIMIZER_COST NUMBER
MODULE VARCHAR2(64)
ACTION VARCHAR2(64)
EXECUTIONS NUMBER
ELAPSED_TIME NUMBER
CPU_TIME NUMBER
BUFFER_GETS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
ROWS_PROCESSED NUMBER
FETCHES NUMBER
END_OF_FETCH_COUNT NUMBER
CATEGORY VARCHAR2(30)
SQLFLAGS NUMBER
TASK_ID NUMBER
TASK_EXEC_NAME VARCHAR2(30)
TASK_OBJ_ID NUMBER
TASK_FND_ID NUMBER
TASK_REC_ID NUMBER
INUSE_FEATURES NUMBER
PARSE_CPU_TIME NUMBER
PRIORITY NUMBER
OPTIMIZER_ENV RAW(2000)
BIND_DATA RAW(2000)
PARSING_SCHEMA_NAME VARCHAR2(30)
COMP_DATA CLOB

Now pack the baselines / load the baselines to the staging table created above

SQL> SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
SQL> 2 3 BEGIN
4 l_plans_packed : DBMS_SPM.pack_stgtab_baseline(
table_name > 'spm_staging_tab',
5 6 table_owner > 'BIASDBA');

7 8 DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
9 10 /

Plans Packed: 3
PL/SQL procedure successfully completed.

Check the baseline table after loading

SQL> select sql_handle from BIASDBA.spm_staging_tab;
SQL_HANDLE
------------------------------
SQL_172a4815a43b43db
SQL_172a4815a43b43db
SQL_172a4815a43b43db


On target database
============

Copy required table to target database.

Create the DB link to the source database ( If the DBlink not an option then use export / import to copy the baseline staging table data to target).

SQL> create public database link efipfqa1 connect to biasdba identified by biasdba using 'efipfqa1';

Check the DB link is working or not

SQL> select SQL_HANDLE from biasdba.spm_staging_tab@efipfqa1;
SQL_HANDLE
------------------------------
SQL_172a4815a43b43db
SQL_172a4815a43b43db
SQL_172a4815a43b43db

Create the baseline staging table using the source database baseline staging table data

SQL> create table biasdba.spm_staging_tab as select * from biasdba.spm_staging_tab@efipfqa1;Table created.

Now Unpack / unload the baseline to the database dictionary
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_plans_unpacked PLS_INTEGER;
3 BEGIN
l_plans_unpacked : DBMS_SPM.unpack_stgtab_baseline(
4 5 table_name > 'spm_staging_tab',
6 table_owner > 'BIASDBA');

7 8 DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
9 END;
10 /

Plans Unpacked: 3
PL/SQL procedure successfully completed.

Check the baselines created from the unload operation above

SQL> select SQL_HANDLE,plan_name, enabled, accepted FROM dba_sql_plan_baselines ;

SQL_HANDLE PLAN_NAME ENA ACC
------------------------------ ------------------------------ --- ---
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyv0239c560 YES NO
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyv3fc6434b YES NO
SQL_172a4815a43b43db SQL_PLAN_1fak82qk3qhyvccdd3f88 YES YES

I hope this article helped you. Your suggestions/feedbacks are most welcome.
Keep learning... Have a great day!!!