Recover Database when system.dbf is corrupted

SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 30 17:31:08 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1388352 bytes
Variable Size             620757184 bytes
Database Buffers          444596224 bytes
Redo Buffers                4591616 bytes
Database mounted.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:\ORACLE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF'

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 4987181 generated at 03/19/2017 12:47:52 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2017_03_30\O1_MF_1_173_%U_.ARC
ORA-00280: change 4987181 for thread 1 is in sequence #173


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:\ORACLE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF'



SQL> create pfile='D:\oracle\app\oracle\product\11.2.0\server\database\initXE.ora' from spfile;

File created.


Open pfile created at last setp at the follwoing path D:\oracle\app\oracle\product\11.2.0\server\database\initXE.ora

and at end of file add follwoing parameter.

_allow_resetlogs_corruption=true

and save the file.

Now shutdown database and start with this file as below.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='D:\oracle\app\oracle\product\11.2.0\server\database\initXE.ora';
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1388352 bytes
Variable Size             620757184 bytes
Database Buffers          444596224 bytes
Redo Buffers                4591616 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1388352 bytes
Variable Size             620757184 bytes
Database Buffers          444596224 bytes
Redo Buffers                4591616 bytes
Database mounted.
Database opened.
SQL>

Use Sql Developer to load formatted excel report data into Oracle

I this post we will learn to load formatted report data from excel or csv files into ORACLE database.

sample data in excel format.

Jan 2002- Dec 2002
St.Name	N	E	Jan_2002	Feb_2002	Mar_2002	Apr_2002	May_2002	June_2002	July_2002	Aug_2002	Sep_2002	Oct_2002	Nov_2002	Dec_2002
Bhakkar	31.61667	71.06667	0	0	0	0	0	0	0	0	0	0	0	0
Bahawal-Nagar	29.88099	73.35250	48	44	30	19	16	28	34	40	40	29	40	48
Bahawal-Pur	30.53333	71.78333	45	41	33	18	20	29	36	40	48	41	44	46
CHAKWAL	32.91667	72.85000	0	0	0	0	0	0	0	0	0	0	0	0
Jan 2003- Dec 2003
St.Name	N	E	Jan_2003	Feb_2003	Mar_2003	Apr_2003	May_2003	June_2003	July_2003	Aug_2003	Sep_2003	Oct_2003	Nov_2003	Dec_2003
Bhakkar	31.61667	71.06667	0	0	0	0	0	0	0	0	0	0	0	0
Bahawal-Nagar	29.88099	73.35250	58	51	37	20	14	31	55	53	49	28	29	46
Bahawal-Pur	30.53333	71.78333	53	52	35	19	17	31	53	55	51	35	44	45
CHAKWAL	32.91667	72.85000	0	0	0	0	0	0	0	0	0	0	0	0
D-G-KHAN	30.05000	70.63333	55	44	38	22	15	25	53	54	53	33	30	40
Jan 2004- Dec 2004
St.Name	N	E	Jan_2004	Feb_2004	Mar_2004	Apr_2004	May_2004	June_2004	July_2004	Aug_2004	Sep_2004	Oct_2004	Nov_2004	Dec_2004
Bhakkar	31.61667	71.06667	0	0	0	0	0	0	0	0	0	0	0	0
Bahawal-Nagar	29.88099	73.35250	63	40	26	19	19	32	36	49	39	37	35	45
Bahawal-Pur	30.53333	71.78333	59	46	32	21	22	30	38	56	48	45	43	48
CHAKWAL	32.91667	72.85000	0	0	0	0	0	0	0	0	0	0	0	0
D-G-KHAN	30.05000	70.63333	55	44	35	21	21	34	46	54	47	40	41	42

To load above data execute following scripts on your database with appropriate user and follow the procedure.

I have used scott user for this demo.


Following scrip will create table in which data would be loaded.


create table climate_data
(
city_Name varchar2(100) ,
North varchar2(100) ,
East varchar2(100) ,
Jan varchar2(100) ,
Feb varchar2(100) ,
Mar varchar2(100) ,
Apr varchar2(100) ,
May varchar2(100) ,
Jun varchar2(100) ,
Jul varchar2(100) ,
Aug varchar2(100) ,
Sep varchar2(100) ,
Oct varchar2(100) ,
Nov varchar2(100) ,
Dec varchar2(100),
year_name varchar2(100) )
/


This package will help to hold data temporarily.


CREATE OR REPLACE PACKAGE uldemo7_climate_data AS
    year_name  varchar2(100);
END uldemo7_climate_data;
/


This trigger will convert the formatted data in to our required column in my case I want to populate year_name column.


CREATE OR REPLACE TRIGGER uldemo7_climate_data_insert
  BEFORE INSERT ON climate_data
  FOR EACH ROW

  BEGIN
  IF substr(:new.Dec,1,3) ='Dec' THEN
     uldemo7_climate_data.year_name := substr(:new.Dec,5,4);   -- save value for later use
  ELSE
     :new.year_name := uldemo7_climate_data.year_name;   -- use last valid value
  END IF;

  END;
/


Following triggers is used to  delete extra/garbage  data form the table.


create or replace TRIGGER uldemo7_climate_data_aft_ins
  after INSERT ON climate_data

  BEGIN
 delete from climate_data where year_name is null or city_name like 'Jan%' or north is null;

  END;
  /


After executing above scripts we are ready to used sql developer or sqlloader to load data in our required format in the climate_data table.


To load data using sql developer start the sql developer with the your user in my case it is scott.

open the table tree in the object explorer and right click the climate_data table and click import.

at first screen click browse and provide the excel or csv file.

header checkbox must be unchecked click next

select import method to insert and click next
again click next on the choose column page
in the column definition page select match by position option and click next.
on finish page click finish.

A dialog box will appear to show the status if import .

click ok


now you can query climate_data table to view the loaded data .

Load formatted report in to Oracle using sqlloader

I this post we will learn to load formatted report data from excel or csv files into ORACLE database.

sample data in excel format.

Jan 2002- Dec 2002
St.Name	N	E	Jan_2002	Feb_2002	Mar_2002	Apr_2002	May_2002	June_2002	July_2002	Aug_2002	Sep_2002	Oct_2002	Nov_2002	Dec_2002
Bhakkar	31.61667	71.06667	0	0	0	0	0	0	0	0	0	0	0	0
Bahawal-Nagar	29.88099	73.35250	48	44	30	19	16	28	34	40	40	29	40	48
Bahawal-Pur	30.53333	71.78333	45	41	33	18	20	29	36	40	48	41	44	46
CHAKWAL	32.91667	72.85000	0	0	0	0	0	0	0	0	0	0	0	0
Jan 2003- Dec 2003
St.Name	N	E	Jan_2003	Feb_2003	Mar_2003	Apr_2003	May_2003	June_2003	July_2003	Aug_2003	Sep_2003	Oct_2003	Nov_2003	Dec_2003
Bhakkar	31.61667	71.06667	0	0	0	0	0	0	0	0	0	0	0	0
Bahawal-Nagar	29.88099	73.35250	58	51	37	20	14	31	55	53	49	28	29	46
Bahawal-Pur	30.53333	71.78333	53	52	35	19	17	31	53	55	51	35	44	45
CHAKWAL	32.91667	72.85000	0	0	0	0	0	0	0	0	0	0	0	0
D-G-KHAN	30.05000	70.63333	55	44	38	22	15	25	53	54	53	33	30	40
Jan 2004- Dec 2004
St.Name	N	E	Jan_2004	Feb_2004	Mar_2004	Apr_2004	May_2004	June_2004	July_2004	Aug_2004	Sep_2004	Oct_2004	Nov_2004	Dec_2004
Bhakkar	31.61667	71.06667	0	0	0	0	0	0	0	0	0	0	0	0
Bahawal-Nagar	29.88099	73.35250	63	40	26	19	19	32	36	49	39	37	35	45
Bahawal-Pur	30.53333	71.78333	59	46	32	21	22	30	38	56	48	45	43	48
CHAKWAL	32.91667	72.85000	0	0	0	0	0	0	0	0	0	0	0	0
D-G-KHAN	30.05000	70.63333	55	44	35	21	21	34	46	54	47	40	41	42

To load above data execute following scripts on your database with appropriate user.

I have used scott user for this demo.


Following scrip will create table in which data would be loaded.


create table climate_data
(
city_Name varchar2(100) ,
North varchar2(100) ,
East varchar2(100) ,
Jan varchar2(100) ,
Feb varchar2(100) ,
Mar varchar2(100) ,
Apr varchar2(100) ,
May varchar2(100) ,
Jun varchar2(100) ,
Jul varchar2(100) ,
Aug varchar2(100) ,
Sep varchar2(100) ,
Oct varchar2(100) ,
Nov varchar2(100) ,
Dec varchar2(100),
year_name varchar2(100) )
/


This package will help to hold data temporarily.


CREATE OR REPLACE PACKAGE uldemo7_climate_data AS
    year_name  varchar2(100);
END uldemo7_climate_data;
/


This trigger will convert the formatted data in to our required column in my case I want to populate year_name column.


CREATE OR REPLACE TRIGGER uldemo7_climate_data_insert
  BEFORE INSERT ON climate_data
  FOR EACH ROW

  BEGIN
  IF substr(:new.Dec,1,3) ='Dec' THEN
     uldemo7_climate_data.year_name := substr(:new.Dec,5,4);   -- save value for later use
  ELSE
     :new.year_name := uldemo7_climate_data.year_name;   -- use last valid value
  END IF;

  END;
/


Following triggers is used to  delete extra/garbage  data form the table.


create or replace TRIGGER uldemo7_climate_data_aft_ins
  after INSERT ON climate_data

  BEGIN
 delete from climate_data where year_name is null or city_name like 'Jan%' or north is null;

  END;
  /
 

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

To load data using sqlloader


create load_data.ctl  with following contents in any folder  in my case I am using d:\dataloading.



------------------------------------------------------------
-- SQL-Loader Basic Control File
------------------------------------------------------------
options  ( skip=1 )
load data
  INFILE                'mydata.csv'          
truncate into table   scott.climate_data
fields terminated by ','  
trailing nullcols
  (
    city_Name ,
North ,
East ,
Jan ,
Feb ,
Mar ,
Apr ,
May ,
Jun ,
Jul ,
Aug ,
Sep ,
Oct ,
Nov ,
Dec ,
year_name
  )

 
  --------control file end ----------------------
 
in control file mydata.csv is c.v file that we want to load .
climate_data is table in which data would be loaded.
every time we will load data existing data in the table will be truncated.

Now place your mydata.csv in the same folder where control file is placed.i.e d:\dataloading

go to d:\dataloading using command prompt and execute following command.

  sqlldr scott/oracle@gisdb  control='load_data.ctl' log='result.log'

above command will upload data in results will be displayed.

now we can query climate_data to view loaded data.