Posts Tagged ‘database’

January 10th, 2018, posted in Oracle Queries

ORA-01194: file 1 needs more recovery to be consistent

Method 1:

1) Start the database in mount state

SQL> startup mount;

2) Recover the database.

SQL> recover database;

If you come across below error

ORA-00283: recovery session canceled due to errors
 ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
 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\PRODUCT\10.2.0\ORADATA\DBTEST\SYSTEM01.DBF’

then, do the following

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 766152 generated at 03/16/2013 12:12:04 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_14_%u_.arc

ORA-00280: change 766152 for thread 1 is in sequence #14

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_10_8n43no4v_.arc

ORA-00310: archived log contains sequence 10; sequence 14 required

ORA-00334: archived log:

'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_10_8n43no4v_.arc'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01195: online backup of file 1 needs more recovery to be consistent

ORA-01110: data file 1:

'/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_system_7qm3ck4o_.dbf'

2)

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 766152 generated at 03/16/2013 12:12:04 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_14_%u_.arc

ORA-00280: change 766152 for thread 1 is in sequence #14

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_11_8n43qq5j_.arc

ORA-00310: archived log contains sequence 11; sequence 14 required

ORA-00334: archived log:

'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_11_8n43qq5j_.arc'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01195: online backup of file 1 needs more recovery to be consistent

ORA-01110: data file 1:

'/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_system_7qm3ck4o_.dbf'

3)

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 766152 generated at 03/16/2013 12:12:04 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_14_%u_.arc

ORA-00280: change 766152 for thread 1 is in sequence #14

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_16/o1_mf_1_14_8n875owh_.arc

ORA-00279: change 769526 generated at 03/16/2013 12:48:13 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_15_%u_.arc

ORA-00280: change 769526 for thread 1 is in sequence #15

ORA-00278: log file

'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_16/o1_mf_1_14_8n875

owh_.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

3) Open the database in resetlog mode

SQL> alter database open resetlogs;

4) Check the status

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS

———————— ————-

DUPDB OPEN

SQL> select name, open_mode from v$database;

NAME OPEN_MODE

———- ——————

DUPDB READ WRITE

Method 2:

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 530288640 bytes

Fixed Size 2131120 bytes

Variable Size 310381392 bytes

Database Buffers 209715200 bytes

Redo Buffers 8060928 bytes

Database mounted.

SQL> ALTER SYSTEM SET “_allow_resetlogs_corruption”= TRUE SCOPE = SPFILE;

SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE = SPFILE;

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 530288640 bytes

Fixed Size 2131120 bytes

Variable Size 310381392 bytes

Database Buffers 209715200 bytes

Redo Buffers 8060928 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> CREATE UNDO TABLESPACE undo1 datafile ‘<ora_data_path>undo1_1.dbf’ size 200m autoextend on maxsize unlimited;

Tablespace created.

SQL> ALTER SYSTEM SET undo_tablespace = undo1 SCOPE=spfile;

System altered.

SQL> alter system set undo_management=auto scope=spfile;

System altered.

SQL> shutdown immediate

SQL> startup

read more no comments

RC-00118 Error Occurred During Creation Of Database

December 10th, 2017, posted in Oracle

I was cloning PROD to DEV instance … when I am configuring DEV instance , got following errors.

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

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2235208 bytes
Variable Size             427820216 bytes
Database Buffers          624951296 bytes
Redo Buffers               13930496 bytes

Control file created.

alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u02/oracle/DEV/oradata/DEV/system01.dbf'


alter tablespace TEMP1 add tempfile '/u02/oracle/DEV/oradata/DEV/temp01.dbf' REUSE
*
ERROR at line 1:
ORA-01109: database not open


alter tablespace TEMP2 add tempfile '/u02/oracle/DEV/oradata/DEV/temp02.dbf' REUSE
*
ERROR at line 1:
ORA-01109: database not open


ALTER DATABASE RENAME GLOBAL_NAME TO "DEV.localhost"
                                     *
ERROR at line 1:
ORA-01109: database not open

ORA-01109: database not open


Error Running create/replace library with adupdlib.sql
ORA-01109: database not open

PL/SQL procedure successfully completed.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
exit_code=0

Checking for errors ...

The database has not been successfully created. Shutting down the instance ...

SQL*Plus: Release 11.2.0.3.0 Production on Sat Nov 1 01:26:22 2014

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

Connected.
ORACLE instance shut down.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

.end std out.

.end err out.

---------------------------------------------------------------
                   ADX Database Utility
---------------------------------------------------------------

getConnectionUsingAppsJDBCConnector() -->
    APPS_JDBC_URL='null'
    Trying to get connection using SID based connect descriptor
getConnection() -->
    sDbHost    : localhost
    sDbDomain  : localhost.domain.com
    sDbPort    : 1530
    sDbSid     : DEV
    sDbUser    : apps
    Trying to connect using SID...
getConnectionUsingSID() -->
    JDBC URL: jdbc:oracle:thin:@localhost:1530:DEV
    Exception occurred: java.sql.SQLException: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

    Trying to connect using SID as ServiceName
getConnectionUsingServiceName() -->
    JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=DEV)))
    Exception occurred: java.sql.SQLException: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

    Trying to connect using SID as ServiceName.DomainName
getConnectionUsingServiceName() -->
    JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=DEV.localhost)))
    Exception occurred: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

    Connection could not be obtained; returning null

-------------------ADX Database Utility Finished---------------

RC-00118: Error occurred during creation of database
Raised by oracle.apps.ad.clone.ApplyDatabase


StackTrace:
java.lang.Exception: Control file creation failed
        at oracle.apps.ad.clone.ApplyDatabase.doConf(ApplyDatabase.java:635)
        at oracle.apps.ad.clone.ApplyDatabase.doApply(ApplyDatabase.java:473)
        at oracle.apps.ad.clone.ApplyDatabase.<init>(ApplyDatabase.java:366)
        at oracle.apps.ad.clone.ApplyDBTier.<init>(ApplyDBTier.java:110)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:501)
        at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:67)
        at java.lang.Thread.run(Thread.java:637)

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

In my case, control file were created successfully, but above error indicates that database was not created successfully.

I found root of this issue that when i had copied PROD binaries and data files at same time some services were running using ORACLE user.

Note: You would get this error if oracle database was not properly shutdown when you copied the files.

All my stuff were wasted.
So, I have copied only datafiles again from PROD to DEV.
And ran adcfgclone.pl dbTier again and cloning has been completed successfully without any error !!

read more no comments

Oracle : Size Of Database

March 31st, 2015, posted in Oracle Queries

An Oracle Database consists of data files, redo log files, control files, temporary files.
Whenever you say the size of the database this actually means the summation of these files.

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from 
( select sum(bytes)/1024/1024 data_size from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) d;

-------------------------------------------------------------
For Round Figure For MB :

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from 
( select round( sum(bytes)/1024/1024 ) data_size from dba_data_files ) a,
( select round( nvl(sum(bytes),0)/1024/1024 ) temp_size from dba_temp_files ) b,
( select round( sum(bytes)/1024/1024 ) redo_size from sys.v_$log ) c,
( select round( sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 ) controlfile_size from v$controlfile) d;

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

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB"
from 
( select sum(bytes/1024/1024/1024) data_size from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) b,
( select sum(bytes/1024/1024/1024) redo_size from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) d;

-------------------------------------------------------------
For Round Figure GB :

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB"
from 
( select round( sum(bytes/1024/1024/1024) ) data_size from dba_data_files ) a,
( select round( nvl(sum(bytes),0)/1024/1024/1024 ) temp_size from dba_temp_files ) b,
( select round( sum(bytes/1024/1024/1024) ) redo_size from sys.v_$log ) c,
( select round( sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 ) controlfile_size from v$controlfile) d;



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

Get database size from v$datafile:

select round((sum(bytes)/1048576/1024),2) from v$datafile;

------------------------------------------------------------
Get Oracle Database size from dba_data_files:

select "Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)"
from(
select 
(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)",
(select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)"
from dual
);

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

here
a is megabytes allocated to ALL datafiles
b is megabytes allocated to ALL TEMP files
c is megabytes allocated to ALL redo-logs
d is megabytes allocated to ALL control files

read more no comments

ORACLE :How to Configure Net8 in Non-RAC Database

February 17th, 2013, posted in Oracle

To access the database from other than the system in which database installed you need to configure oracle supplies Net8 for this purpose. It is a common interface to client application that needs to connect to the oracle database. It consists of the three files: tnsname.ora, listener.ora, sqlnet.ora.

Steps or Process:

1. Â Â Â Edit the tnsname.ora file and add the new service name for use by the client software.

2. Â Â Â Edit the listener.ora file add an entry for another database instance.

3. Â Â Â Use the ping command to verify connectivity to the Host.

4. Â Â Â Use the tnsping command to verify the connectivity to the Net8 listener.

5. Â Â Â Start/Stop the Net8 listener to reload newly added DB instance.

6. Â Â Â Connect to a remote database over the network.

tnsname.ora:

The normal location for this file is D:oracleora92networkadmin. This file is located on both client and server. If you make configuration changes on the server ensure you can connect to the database through the listener if you are logged on to the server. If you make configuration change on the client ensure you can connect from your client workstation to the database through the listener running on the server.

Add the following entry in your tnsname.ora file and change the value in the bracket according to your environment.

RMAN.AL-SADHAN.COM =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rmanbackup)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = RMAN)

)

Note: You can put either system name or directly static IP (192.168. 0.1) as HOST.

C:tnsping rman

Use the above command to verify the connectivity on windows environment.

listener.ora:

The normal location for this file is D:oracleora92networkadmin. This file is client side file (typically on remote PC). The client uses thistnsname.ora file to obtain connection details from the desired database. Add the following entry in your listener.ora file and changed the value in bracket as per your environment.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = D:oracleora92)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = RMAN)

(ORACLE_HOME = D:oracleora92)

(SID_NAME = RMAN)

)

Note: Provide the unique global name as you specify at the time of database creation. You can change it later with your own domain name.

SQL>ALTER DATABASE rename global_name to rman.al-sadhan.com;

C:lsnrctl

Lsnrctl>stop listener

Lsnrctl>start listener

Lsnrctl>reload listener

You can use above command to bounce the listener.

sqlnet.ora:

The normal location for this file is D:oracleora92networkadmin. The sqlnet.ora file is the profile configuration file, and it resides on the client machines and the database server. The sqnet.ora is text file that contain basic configuration details used by the SQL*Net.

NAMES.DEFAULT_DOMAIN = al-sadhan.com

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

trace_level_client = OFF

sqlnet.expire_time = 30

names.default_domain: Â If the net service name does not have a domain specified, this parameter value is appended to the service name.

names_directory_path: This parameter specifies the order of naming methods used when a client attempts a connection to a database. Possible values include: LDAP; TNSNAMES; HOSTNAME; ONAMES; and EZCONNECT.

sqlnet.expire_time: Â This parameter, set on the server, enables dead connection detection. Â After the specified time interval, expressed in minutes, the server checks to see if the client is still connected. If the client is not still connected, the server process exits.

sqlnet.authentication_services: This parameter is used to enable one or more authentication services. Â There is no default setting, so if authentication has been installed, it this parameter should be set to either NONE for no authentication methods or ALL for all authentication methods.

trace_level_client: This parameter enables tracing unless it is set to OFF or 0, which is the default. Â Use the following values to set tracing levels: USER (4); ADMIN (10); and SUPPORT (16).

*********************************************************************************************************************

Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as
 well.
*********************************************************************************************************************

read more no comments

« Older Entries

Newer Entries »

POiSON WORLD

Posts Tagged ‘database’