Thursday, March 13, 2014

Startup The Database In Archivelog Mode / NoArchivelog Mode

Startup The Database In Archivelog Mode


Steps Required To Take A Database Not In Archive Log Mode And Alter It To Archive Log Mode

SELECT log_mode
FROM v$database;

SHUTDOWN;

STARTUP MOUNT EXCLUSIVE;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

SELECT log_mode
FROM v$database;

 

Startup The Database In NoArchivelog Mode


Steps Required To Take A Database In Archive Log Mode And Alter It To No Archive Log Mode

SELECT log_mode
FROM v$database;

SHUTDOWN;

STARTUP MOUNT EXCLUSIVE;

ALTER DATABASE NOARCHIVELOG;

ALTER DATABASE OPEN;

SELECT log_mode
FROM v$database;

 

Source: http://psoug.org/reference/archivelog.html

Thursday, February 6, 2014

allowDefinition='MachineToApplication' beyond application level

When am trying to build a web application through visual studio 2010, I was getting the following error message.

·         "It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. 
This error can be caused by a virtual directory not being configured as an application in IIS. "

 

The solution is to check whether the is any other sub folder which is having web.config file. After removing it, The build was successful.

 

Best Regards

 

Sunday, November 17, 2013

Grants given to objects through Roles for a Schema & without Roles (Direct)

Hi Folks,

 

The following queries are very much useful to get the list of Grants given to Roles for a particular schema objects & to List the direct grants given to users.

 

Grants given to objects through Roles for a Schema

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

SELECT TP.OWNER,TP.GRANTEE,RP.GRANTEE, TP.TABLE_NAME, TP.PRIVILEGE FROM DBA_TAB_PRIVS TP, DBA_ROLE_PRIVS RP WHERE TP.GRANTEE = RP.GRANTED_ROLE AND TP.OWNER='Schema Name'

ORDER BY 1,2,3 

 

 

Individual Grants given to objects without using Roles:

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

SELECT TP.OWNER,TP.GRANTEE,TP.TABLE_NAME, TP.PRIVILEGE FROM dba_tab_privs TP

where TP.OWNER='<Schema Name>' AND GRANTEE NOT IN (Select ROLE from DBA_ROLES) ORDER BY 2,3,4

 

Thanks & Regards

Fauzi

 

Oracle: History of SQL Statements in Oracle 11g

Hello,

 

The following sql query is very much help full to get the history of sql queries run on the server.

 

SQL:

select a.USER_ID, a.SESSION_ID,b.sql_text from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b where a.sql_id=b.sql_id

 

Thanks & Regards

Fauzi

 

 

Monday, November 11, 2013

Oralce Query to Get Last Sunday or Frieday of the month

The following query is very much useful:

 

 

SELECT next_day(last_day(sysdate)-7,'Friday') from dual

 

SELECT next_day(last_day(sysdate)-7,'Sunday') from dual

 

Thanks & Regards

Fauzi

 

Thursday, May 2, 2013

Oracle: Steps to recover when Redo Files are Lost


RCAT>>select name from v$database;

NAME
---------
RCAT

RCAT>>alter system set db_recovery_file_dist='+DAT' scope=both;
alter system set db_recovery_file_dist='+DAT' scope=both
                 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


RCAT>>ed
Wrote file afiedt.buf

  1* alter system set db_recovery_file_dist='+DATA' scope=both
RCAT>>/
alter system set db_recovery_file_dist='+DATA' scope=both
                 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


RCAT>>ed
Wrote file afiedt.buf

  1* alter system set db_recovery_file_dest='+DATA' scope=both
RCAT>>/
alter system set db_recovery_file_dest='+DATA' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE


RCAT>>ed
Wrote file afiedt.buf

  1* alter system set db_recovery_file_dest_size=10M scope=both
RCAT>>/

System altered.

RCAT>>/ed
SP2-0042: unknown command "/ed" - rest of line ignored.
RCAT>>ed
Wrote file afiedt.buf

  1* alter system set db_recovery_file_dest='+DAT' scope=both
RCAT>>/

System altered.

RCAT>>archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           2
RCAT>>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
RCAT>>startup mount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
Database mounted.
RCAT>>shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
RCAT>>startuo
SP2-0042: unknown command "startuo" - rest of line ignored.
RCAT>>startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 1896
Session ID: 1 Serial number: 5


RCAT>>startup nomount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
RCAT>>shutdown
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
RCAT>>startup nomount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
RCAT>>shutdown immediate
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
RCAT>>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Administrator>
C:\Users\Administrator>
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 2 12:31:03 2013

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

Connected to an idle instance.

SQL> select * from v$instance
  2  ;
select * from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0


SQL> exit
Disconnected

C:\Users\Administrator>set oracle_sid=rcat

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 2 12:31:42 2013

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
ORA-03113: end-of-file on communication channel
Process ID: 3208
Session ID: 1 Serial number: 3


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Administrator>set oracle_sid=rcat

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 2 12:31:59 2013

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
SQL> alter database mount ;

Database altered.

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

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.

SQL>





--
Fauzi
+965-97324246

  


Wednesday, May 1, 2013

Oracle : Recovery Steps when control file is lost. If backup was done


SQL> startup;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> select status fron v$instance;
select status fron v$instance
                   *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> ed
Wrote file afiedt.buf

  1* select status from v$instance
SQL> /

STATUS
------------
STARTED

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      F:\RCAT\CONTROL01.CTL, F:\RCAT
                                                 \CONTROL02.CTL
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL>
SQL> shutdown
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2178336 bytes
Variable Size             390071008 bytes
Database Buffers          230686720 bytes
Redo Buffers                3391488 bytes
Database mounted.
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-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'F:\RCAT\SYSTEM01.DBF'


SQL> recover database using backup controlfile;
ORA-00279: change 967649 generated at 05/01/2013 10:32:22 needed for thread 1
ORA-00289: suggestion :
C:\ORACLEBASE\DATABASE\RDBMS\ARC0000000003_0814268715.0001
ORA-00280: change 967649 for thread 1 is in sequence #3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\rcat\redo01.log
ORA-00328: archived log ends at change 950490, need later change 967649
ORA-00334: archived log: 'F:\RCAT\REDO01.LOG'


SQL> select * from v$log ;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          1   52428800        512          1 NO
INACTIVE                947455 01-MAY-13       950491 01-MAY-13

         3          1          3   52428800        512          1 NO
CURRENT                 959675 01-MAY-13   2.8147E+14

         2          1          2   52428800        512          1 NO
INACTIVE                950491 01-MAY-13       959675 01-MAY-13


SQL> recover database using backup controlfile;
ORA-00279: change 967649 generated at 05/01/2013 10:32:22 needed for thread 1
ORA-00289: suggestion :
C:\ORACLEBASE\DATABASE\RDBMS\ARC0000000003_0814268715.0001
ORA-00280: change 967649 for thread 1 is in sequence #3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\rcat\redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1          1   52428800        512          1 NO
CURRENT                 972032 02-MAY-13   2.8147E+14

         2          1          0   52428800        512          1 YES
UNUSED                       0                      0

         3          1          0   52428800        512          1 YES
UNUSED                       0                      0


SQL> set sqlprompt RCAT>>
RCAT>>
--

Fauzi
+965-97324246