ORA-00205: error in identifying control file, check alert log for more info / Removing a Control File

ORA-00205: error in identifying control file, check alert log for more info

If you recieve error then check controlfile physcial location  then remove bad controlfile location from database(control_files parameter ):



1. Identify which control file has experienced media failure by inspecting the alert.log for
information

ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/bsnl/control_files/control03.ctl'
ORA-27037: unable to obtain file status


2. Remove the unavailable control file name from the CONTROL_FILES parameter. If you’re
using an init.ora file, modify the file directly with an OS editor (such as vi). If you’re
using an spfile, modify the CONTROL_FILES parameter with the ALTER SYSTEM statement.
In this spfile example the control02.ctl control file is removed from the CONTROL_FILES
parameter:


alter system set control_files= '/u01/app/oracle/oradata/bsnl/control01.ctl','/u01/app/oracle/fast_recovery_area/bsnl/control02.ctl' scope=spfile;

3. Stop and start your database:
SQL> shutdown immediate;
SQL> startup;

How to Move/Add/Multiplex Control file

Adding controlfile in database means copy an existing controlfile and making database aware of copy by modifying control_files parameter .

Steps to Add controlfile :
==================================

Step 1:-

Alter the initialization CONTROL_FILES parameter to include new location and name of controlfile.

Step 2:-

Shutdown database

Step 3:-

Use OS command to copy  existing good control file  to new location and rename .

Step 4:-

start database



If database use pfile
============================
shutdown database,
copy one of controlfile to new locations,
manually edit pfile
startup database.




Example:
[oracle@mylab ~]$ sqlplus "/as sysdba"




**************************************************************
check current controlfiles  location information
***************************************************************

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/bsnl/control01.ctl, /u01/app/oracle/fast_recovery_area/bsnl/control02.ctl
SQL>




*****************************************************************************
 Make change CONTROL_FILES parameter  with new location using below command
*****************************************************************************

SQL > alter system set control_files= '/u01/app/oracle/oradata/bsnl/control01.ctl', '/u01/app/oracle/fast_recovery_area/bsnl/control02.ctl', '/u01/app/oracle/oradata/bsnl/control_files/control03.ctl' scope=spfile;




*******************************************
 Shut down database
*****************************************
SQL> shut immediate






**********************************************
Use OS command copy controlfile to new location
**********************************************

[oracle@mylab bsnl]$
[oracle@mylab bsnl]$ cp control01.ctl control_files/control03.ctl
[oracle@mylab bsnl]$




********************************************
Start database
********************************************
[oracle@mylab ~]$ sqlplus "/as sysdba"

SQL> startup

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/bsnl/control01.ctl, /u01/app/oracle/fast_recovery_area/bsnl/control02.ctl, /u01/app/oracle/oradata/bsnl/control_files/control03.ctl

How to Migrate SQL Profiles from One database to Another database

===============================How to Migrate SQL Profiles from One database to Another database=========================================


You can migrate SQL profile using export and import from one database to another database just like stored outline. Prior to oracle 10g you can migrate SQL profiles with the dbms_sqltune.import_sql_profile procedure where as in oracle 10g release 2 and beyond using dbms_sqltune package. In both case you have to create a staging table on the source database and populate that staging table with the relevant data. Below is the step to migrate SQL profile in 10g release 2.

Step1. Create the staging table to store SQL Profiles in source database
SQL> sys/oracle@orcl as sysdba
SQL> BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF
(table_name => ‘SQL_PROFILES’,schema_name=>TEST');
     END;
/
PL/SQL procedure successfully completed.


Step2. Now Copy SQL profiles from SYS to the Staging table
SQL> BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLPROF
(profile_name => 'PROF_4m0c9guyqybbd_2738228381',
staging_table_name => 'SQL_PROFILES',
staging_schema_owner=>'TEST');
END;
/
PL/SQL procedure successfully completed.
Note: As you need to copy all SQL profiles on my database ‘%’ value for profile_category was the best option.


Step3. Export the staging table at source
SQL> select count(*) from TEST.sql_profiles;
COUNT(*)
-------------
3
expdp system/***** dumpfile=expdp_sql_profiles.dmp TABLES=TEST.SQL_PROFILES DIRECTORY=DPUMP



Step4. Restore the database with the backup taken before all SQL profiles were generated and import the staging table at target database.
impdp system/***** dumpfile=expdp_sql_profiles.dmp TABLES=TEST.SQL_PROFILES DIRECTORY=DPUMP TABLE_EXISTS_ACTION=REPLACE
Note: Do not forget to create staging table on destination database. Use replace = TRUE if you need to have same SQL_Profiles on both the database.


Step5. Finally Unpack the SQL profiles from the staging table on destination database.
SQL> BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF
(staging_table_name => ‘SQL_PROFILES’,
staging_schema_owner=>’TEST2’, replace=>FALSE);
END;
/
PL/SQL procedure successfully completed.

Add partition in LVM linux

root@localhost dev]#
[root@localhost dev]# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x50f6f010.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): p

Disk /dev/sdc: 4294 MB, 4294967296 bytes
255 heads, 63 sectors/track, 522 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x50f6f010

   Device Boot      Start         End      Blocks   Id  System

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-522, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-522, default 522):
Using default value 522

Command (m for help): p

Disk /dev/sdc: 4294 MB, 4294967296 bytes
255 heads, 63 sectors/track, 522 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x50f6f010

   Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1         522     4192933+  83  Linux

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): p

Disk /dev/sdc: 4294 MB, 4294967296 bytes
255 heads, 63 sectors/track, 522 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x50f6f010

   Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1         522     4192933+  8e  Linux LVM

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@localhost dev]#
[root@localhost dev]#
[root@localhost dev]#
[root@localhost dev]# pvscan
  PV /dev/sdb1   VG oracle_lab   lvm2 [2.00 GiB / 1.61 GiB free]
  PV /dev/sda2   VG VolGroup     lvm2 [19.51 GiB / 0    free]
  Total: 2 [21.50 GiB] / in use: 2 [21.50 GiB] / in no VG: 0 [0   ]
[root@localhost dev]#
[root@localhost dev]#
[root@localhost dev]# pvcreate /dev/sdc1
  Physical volume "/dev/sdc1" successfully created
[root@localhost dev]#
[root@localhost dev]# pvscan
  PV /dev/sdb1   VG oracle_lab      lvm2 [2.00 GiB / 1.61 GiB free]
  PV /dev/sda2   VG VolGroup        lvm2 [19.51 GiB / 0    free]
  PV /dev/sdc1                      lvm2 [4.00 GiB]
  Total: 3 [25.50 GiB] / in use: 2 [21.50 GiB] / in no VG: 1 [4.00 GiB]
[root@localhost dev]#
[root@localhost dev]# vgextend oracle_lab /dev/sdc1
  Volume group "oracle_lab" successfully extended
[root@localhost dev]#
[root@localhost dev]# pvs
  PV         VG         Fmt  Attr PSize  PFree
  /dev/sda2  VolGroup   lvm2 a--  19.51g    0
  /dev/sdb1  oracle_lab lvm2 a--   2.00g 1.61g
  /dev/sdc1  oracle_lab lvm2 a--   4.00g 4.00g
[root@localhost dev]#

[root@localhost oracle_lab]#
[root@localhost oracle_lab]# lvextend /dev/oracle_lab/u01 /dev/sdc1
  Extending logical volume u01 to 4.39 GiB
  Logical volume u01 successfully resized
[root@localhost oracle_lab]#

[root@localhost oracle_lab]# resize2fs /dev/oracle_lab/u01
resize2fs 1.43-WIP (20-Jun-2013)
Filesystem at /dev/oracle_lab/u01 is mounted on /u01; on-line resizing required
old_desc_blocks = 2, new_desc_blocks = 18
The filesystem on /dev/oracle_lab/u01 is now 4599808 blocks long.

[root@localhost oracle_lab]#
[root@localhost oracle_lab]# df -h
Filesystem                    Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root   18G  3.9G   13G  24% /
tmpfs                         2.0G  112K  2.0G   1% /dev/shm
/dev/sda1                     477M   55M  397M  13% /boot
/dev/mapper/oracle_lab-u01    4.3G  3.1M  4.1G   1% /u01
[root@localhost oracle_lab]#