Tales From A Lazy Fat DBA

Loves all databases! – Its all about performance, troubleshooting & much more …. ¯\_(ツ)_/¯

  • Prashant Dixit is the 'FatDBA' ...
  • Follow me on Twitter

Posts Tagged ‘Database’

Are you suffering from excessive ‘cursor: mutex X’ & ‘cursor: mutex S’ waits after the upgrade ?

Posted by FatDBA on September 15, 2021

Hi Everyone,

Recently, I was contacted by one of my friend who was battling with some performance issues, since they moved from 12c to 19c. He was mostly strained about a particular problem with the new 19c database where he was getting excessive concurrency classed waits of “cursor: mutex X” (> 92% of the DB Time) and some “cursor: mutex S” events in the database. This was leading to frequent database hang instances.

As per the above snippet from AWR report for the period, ‘cursor: mutex X’ was consuming more than 170 ms per wait or an average wait time and was responsible for more than 91% of the total DB Time consumption.

Initially I though it was a case of classic hard parsing issue, as Cursor: mutex S wait usually occurs when Oracle is serializing parsing of multiple SQL statements. I mean there must be SQLs which are going through excessive hard parsing and has too many child cursors in the library cache. So, I immediately checked section ‘SQL Ordered by Version Count’ and saw one individual statement was there with 7,201 versions or Childs within a period of 2 hours.

Same was confirmed through ASH report too (see below pasted snippet). This particular SELECT statement was waiting on both on these two concurrency classed events specific to library cache.

I further drilled down on this issue to identify the cause behind this problem by querying V$SQL_SHARED_CURSOR (for reasons) to know why a particular child cursor was not shared with existing child cursors, and I was getting BIND_EQUIV_FAILURE as a reason. The database has the ACS(Adaptive Cursor Sharing) and CFB(Cardinality Feedback) enabled and seemed a ‘cursor leak’ issue.

I also noted huge sleeps for CP type mutexes on functions kkscsAddChildNode & kkscsPruneChild, below is the snippet from AWR, take a look at the first two in red.

And when I was about to prepare the strategy (i.e. specific plan purges etc.) to handle the situation, I thought to generate the hang analyze to identify if there are any known/familiar hang chains within stack traces. And I saw most of the chains running the same cursor from different processes waiting on ‘cursor: mutex X’ with below error stack … I mean there were multiple unique sessions waiting for a parent cursor mutex in exclusive mode on the same cursor under the following stack.

<-kgxExclusive<-kkscsAddChildNode<-kxscod<-kkscsCompareBinds<-kkscscid_bnd_eval<-kkscsCheckCriteria<-kkscsCheckCursor<-kkscsSearchChildList<-kksfbc<-

So, we had an error stack showing wait chains running the same cursor from different processes waiting on ‘cursor: mutex X’ and with BIND_EQUIV_FAILURE=Y in V$SQL_SHARED_CURSOR and CFB & ACS enabled, it was appearing that this was happening due to some bug.

Oracle support confirmed my doubt. They affirmed that this was happening all due to two unpublished bugs 28889389 and 28794230. For first one we need to apply patch 28889389, which has the optimized code for cursor mutex while searching the parent cursor for the match, for second one 28794230, they recommended few alternatives .given below …

_optimizer_use_feedback=false
_optimizer_adaptive_cursor_sharing=false
_optimizer_extended_cursor_sharing_rel=none

But even after setting above three undocumented parameters, which is to disable cardinality feedback and adaptive & extended cursor sharing, we only saw ~30% reduction in total waits. Later on they recommended us to apply the optimizer related bug fix control

_fix_control='23596611:OFF

and that completely resolved the issue.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , | Leave a Comment »

Installing and configuring Oracle 21c using RPM method.

Posted by FatDBA on September 6, 2021

Hi Folks,

I know there are already few posts there explaining how to install Oracle 21c database using RPMs, but this one is to explicate both installing the software and creating a test PDB database after RPM installation using ‘configure’ command.

Alright, so let me first install the oracle-database-preinstall-21c package which will do all pre-work for you.

[root@localhost ~]#
[root@localhost ~]# yum install oracle-database-preinstall-21c.x86_64
BDB2053 Freeing read locks for locker 0x829: 3296/140273180206912
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-preinstall-21c.x86_64 0:1.0-1.el7 will be installed
--> Processing Dependency: ksh for package: oracle-database-preinstall-21c-1.0-1.el7.x86_64
--> Running transaction check
---> Package ksh.x86_64 0:20120801-142.0.1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================
 Package                                            Arch                       Version                                  Repository                      Size
=============================================================================================================================================================
Installing:
 oracle-database-preinstall-21c                     x86_64                     1.0-1.el7                                ol7_latest                      26 k
Installing for dependencies:
 ksh                                                x86_64                     20120801-142.0.1.el7                     ol7_latest                     882 k

Transaction Summary
=============================================================================================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 908 k
Installed size: 3.2 M
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/7Server/ol7_latest/packages/oracle-database-preinstall-21c-1.0-1.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Public key for oracle-database-preinstall-21c-1.0-1.el7.x86_64.rpm is not installed
(1/2): oracle-database-preinstall-21c-1.0-1.el7.x86_64.rpm                                                                            |  26 kB  00:00:01
(2/2): ksh-20120801-142.0.1.el7.x86_64.rpm                                                                                            | 882 kB  00:00:02
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                        350 kB/s | 908 kB  00:00:02
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
Importing GPG key 0xEC551F03:
 Userid     : "Oracle OSS group (Open Source Software group) <build@oss.oracle.com>"
 Fingerprint: 4214 4123 fecf c55b 9086 313d 72f9 7b74 ec55 1f03
 Package    : 7:oraclelinux-release-7.7-1.0.5.el7.x86_64 (@anaconda/7.7)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : ksh-20120801-142.0.1.el7.x86_64                                                                                                           1/2
  Installing : oracle-database-preinstall-21c-1.0-1.el7.x86_64                                                                                           2/2
  Verifying  : oracle-database-preinstall-21c-1.0-1.el7.x86_64                                                                                           1/2
  Verifying  : ksh-20120801-142.0.1.el7.x86_64                                                                                                           2/2

Installed:
  oracle-database-preinstall-21c.x86_64 0:1.0-1.el7

Dependency Installed:
  ksh.x86_64 0:20120801-142.0.1.el7

Complete!
[root@localhost ~]#

Now when all pre-work is done, time to install the software using RPM package which I’ve downloaded from Oracle’s website.

[root@localhost ~]#
[root@localhost ~]#
[root@localhost ~]# yum -y localinstall  oracle-database-ee-21c-1.0-1.ol7.x86_64.rpm
Loaded plugins: langpacks, ulninfo
Examining oracle-database-ee-21c-1.0-1.ol7.x86_64.rpm: oracle-database-ee-21c-1.0-1.x86_64
Marking oracle-database-ee-21c-1.0-1.ol7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-ee-21c.x86_64 0:1.0-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================
 Package                                  Arch                     Version                  Repository                                                  Size
=============================================================================================================================================================
Installing:
 oracle-database-ee-21c                   x86_64                   1.0-1                    /oracle-database-ee-21c-1.0-1.ol7.x86_64                   7.1 G

Transaction Summary
=============================================================================================================================================================
Install  1 Package

Total size: 7.1 G
Installed size: 7.1 G
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : oracle-database-ee-21c-1.0-1.x86_64                                                                                                       1/1
[INFO] Executing post installation scripts...
[INFO] Oracle home installed successfully and ready to be configured.
To configure a sample Oracle Database you can execute the following service configuration script as root: /etc/init.d/oracledb_ORCLCDB-21c configure
  Verifying  : oracle-database-ee-21c-1.0-1.x86_64                                                                                                       1/1

Installed:
  oracle-database-ee-21c.x86_64 0:1.0-1

Complete!
[root@localhost ~]#

Installation of software is finished! Next we will create a test database with name ORCLCDB and a pluggable database with name ORCLPDB1.

[root@localhost ~]# /etc/init.d/oracledb_ORCLCDB-21c configure
Configuring Oracle Database ORCLCDB.
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
43% complete
46% complete
Completing Database Creation
51% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/ORCLCDB.
Database Information:
Global Database Name:ORCLCDB
System Identifier(SID):ORCLCDB
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log" for further details.

Database configuration completed successfully. The passwords were auto generated, you must change them by connecting to the database using 'sqlplus / as sysdba' as the oracle user.
[root@localhost ~]#

Okay, its all set. Lets connect with the CDB and the pluggable database that we’ve created above.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Thu Sep 2 12:03:22 2021
Version 21.3.0.0.0

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


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>
SQL> alter session set container = ORCLPDB1;

Session altered.


SQL> show con_name

CON_NAME
------------------------------
ORCLPDB1
SQL>

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , | Leave a Comment »

My top 5 Oracle 21c features …

Posted by FatDBA on September 3, 2021

Hi Guys,

Recently I started the ‘Top 5’ series where I share my top 5 features in any particular tool or product. Last time I did for SQL Developer command line (SQLcl) & TOP utility, this time it will be about top 5 features in Oracle 21c database.

So, without any particular order, below are my top 5 Oracle 21c features …

1. Immutable Tables :

Native Blockchain Tables provide in-database immutable, insert-only tables. This type of tamper resistance helps protect against hacks and illegal changes and is a great feature added into Oracle 21c database. Even an account with DBA role cannot modify there tables. Immutable tables intended for use in an environment where it is required that an audit trail could potentially be tampered with my insertion but that once a record was inserted it would not be possible to alter or delete it except within the date constraints imposed as part of the NO DROP and NO DELETE clauses.

Let me do a demo to explain!

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Thu Sep 2 12:03:22 2021
Version 21.3.0.0.0

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


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>
SQL>
--- You will get an error if you try to create it in the roo container.
SQL> create immutable table testimmu (
  id            number,
  testname         varchar2(20),
  class           number,
  created_date  date,
  constraint testimmu_pk primary key (id)
)
no drop until 1 days idle
no delete until 20 days after insert; 
create immutable table testimmu (
*
ERROR at line 1:
ORA-05729: blockchain or immutable table cannot be created in root container


SQL> alter session set container = ORCLPDB1;

Session altered.


SQL> show con_name

CON_NAME
------------------------------
ORCLPDB1
SQL>


SQL> show user
USER is "SYS"
SQL>
SQL> create immutable table testimmu (
  id            number,
  testname         varchar2(20),
  class           number,
  created_date  date,
  constraint testimmu_pk primary key (id))
no drop until 1 days idle
no delete until 20 days after insert;  

Table created.

SQL>
SQL>
SQL>
SQL> SELECT row_retention "Row Retention Period", row_retention_locked "Row Retention Lock", table_inactivity_retention "Table Retention Period" FROM dba_immutable_tables WHERE table_name = 'TESTIMMU';

Row Retention Period Row Table Retention Period
-------------------- --- ----------------------
                  20 NO                       1

SQL>
-- lets try to alter the NO DELETE clause.
SQL> alter table testimmu no delete until 60 days after insert;

Table altered.

SQL>
SQL> SELECT row_retention "Row Retention Period", row_retention_locked "Row Retention Lock", table_inactivity_retention "Table Retention Period" FROM dba_immutable_tables WHERE table_name = 'TESTIMMU';

Row Retention Period Row Table Retention Period
-------------------- --- ----------------------
                  60 NO                       1

-- What happens when anyone tries to lower down that ?
SQL>  alter table testimmu no delete until 59 days after insert;
 alter table testimmu no delete until 59 days after insert
*
ERROR at line 1:
ORA-05732: retention value cannot be lowered


-- Lets insert some data.
SQL> insert into testimmu (id, testname, class, created_date) values (10,'Elisa',50,sysdate-1);

1 row created.

SQL>
SQL> select * from testimmu;

        ID TESTNAME                  CLASS CREATED_D
---------- -------------------- ---------- ---------
        10 Elisa                        50 01-SEP-21

-- Now try to UPDATE the table record.
SQL> update testimmu set CLASS=40 where TESTNAME='Elisa';
update testimmu set CLASS=40 where TESTNAME='Elisa'
       *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table



SQL> alter table testimmu no drop;

Table altered.

SQL>
SQL> SELECT row_retention "Row Retention Period", row_retention_locked "Row Retention Lock", table_inactivity_retention "Table Retention Period" FROM dba_immutable_tables WHERE table_name = 'TESTIMMU';

Row Retention Period Row Table Retention Period
-------------------- --- ----------------------
                  60 NO                  365000

SQL>

-- Now will try to drop it and will see what will happen.
SQL> drop table testimmu;
drop table testimmu
           *
ERROR at line 1:
ORA-05723: drop blockchain or immutable table TESTIMMU not allowed


SQL> alter table testimmu no drop until 10 days idle;
alter table testimmu no drop until 10 days idle
*
ERROR at line 1:
ORA-05732: retention value cannot be lowered

2. Compare EXECUTION PLANS:

Starting from 21c, now you can compare your execution plans. This is a great in-build feature which helps you to identify the differences between any two plans. Maybe a demo can help explaining how …

SQL> explain plan
  2  set statement_id = 'd1'
  3  for select /*+ full(bigtab) */ * from bigtab where id=840;

Explained.

SQL>
SQL> explain plan
  2  set statement_id = 'd2'
  3  for select /*+ index(bigtab) */ * from bigtab where id=840;

Explained.

SQL>


SQL> VARIABLE d varchar2(5000)
SQL> exec :d := dbms_xplan.compare_explain('d1','d2')

PL/SQL procedure successfully completed.

SQL>


SQL> print d

D
----------------------------------------------------------------------------------------------------------------

COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
  Current user           : SYS
  Total number of plans  : 2
  Number of findings     : 1
---------------------------------------------------------------------------------------------

COMPARISON DETAILS
---------------------------------------------------------------------------------------------
 Plan Number            : 1 (Reference Plan)
 Plan Found             : Yes
 Plan Source            : Plan Table
 Plan Table Owner       : SYS
 Plan Table Name        : PLAN_TABLE
 Statement ID           : d1
 Plan ID                : 1
 Plan Database Version  : 21.0.0.0
 Parsing Schema         : "SYS"
 SQL Text               : No SQL Text

Plan
-----------------------------

 Plan Hash Value  : 441133017

-----------------------------------------------------------------------
| Id  | Operation           | Name   | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |   74 |  2590 |   71 | 00:00:01 |
| * 1 |   TABLE ACCESS FULL | BIGTAB |   74 |  2590 |   71 | 00:00:01 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("ID"=840)


Notes
-----
- Dynamic sampling used for this statement ( level = 2 )


---------------------------------------------------------------------------------------------
 Plan Number            : 2
 Plan Found             : Yes
 Plan Source            : Plan Table
 Plan Table Owner       : SYS
 Plan Table Name        : PLAN_TABLE
 Statement ID           : d2
 Plan ID                : 2
 Plan Database Version  : 21.0.0.0
 Parsing Schema         : "SYS"
 SQL Text               : No SQL Text

Plan
-----------------------------

 Plan Hash Value  : 3941851520

--------------------------------------------------------------------------------------------
| Id  | Operation                             | Name      | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |           |   74 |  2590 |   87 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | BIGTAB    |   74 |  2590 |   87 | 00:00:01 |
| * 2 |    INDEX RANGE SCAN                   | IDX_TESTA |   74 |       |    1 | 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=840)


Notes
-----
- Dynamic sampling used for this statement ( level = 2 )


Comparison Results (1):
-----------------------------
 1. Query block SEL$1, Alias "BIGTAB"@"SEL$1": Access path is different -
    reference plan: FULL (line: 1), current plan: INDEX_RS_ASC (lines: 1, 2).


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

3. CHECKSUM with export dumps

This is again a great feature and this is to avoid any data tampering or modifications in export dumps. So, you generate the checksum at the time of export backup and if someone modifies any data into the dump (via any editing tool), it will be highlighted during import time.

-- To create a checksum at the time of exporting the data in to dump.
[oracle@localhost admin]$ expdp system/*****@*** DIRECTORY=testdirectory1 DUMPFILE=bigtabtestbkp.dmp tables=bigtab CHECKSUM=YES

-- To verify checksum during import
[oracle@localhost admin]$ impdp system/*****@*** DUMPFILE=bigtabtestbkp.dmp verify_checksum=yes

4. Zero Down Time Timezone Upgrade :

One problem with this is that DST patch required “startup upgrade”, I mean it is not a RAC-rolling patch and standby databases have to be in MOUNT mode. With this feature now these patches are RAC-rolling patches and standby databases can be OPEN-ed. This is really cool! Who knows they will even be part of RU & RUR’s 🙂

5. MULTIVALUE INDEX on JSON data.

I’ve recently starting using JSON data for one of our data and I know how difficult it was when you are doing searches using JSON_EXISTS or JSON_QUERY operators in your query.
Now in 21c, a new create index syntax CREATE MULTIVALUE INDEX allows you to create a functional index on arrays of strings or numbers within a JSON type column. Each unique value within the array will become a searchable index entry. This avoids the need for full JSON scans to find values within arrays in JSON columns, when searched using the JSON_EXISTS or JSON_QUERY operators.

CREATE MULTIVALUE INDEX idx_jsndatest ON mytable tempjsdata (temp.jcol.item_grade.numberOnly());

There are few other good features like automatic materialized views, SQL Macros, Expressions in Initialization Parameters (i.e. alter system set pga_aggregate_target=’sga_target/2‘ now possible) etc. available with 21c.

Hope It Helped
Prashant Dixit

Posted in Uncategorized | Tagged: , | Leave a Comment »

With Oracle 21c, now you have Clusterware REST API …

Posted by FatDBA on September 1, 2021

Hi Guys,

While reading official database 21c guides, I came across something really cool – Starting from 21c you have Clusterware REST API 🙂 … REST API is available in previous versions too, but not for Clusterware command line utilities, but with release of 21c this is finally here ..

The REST application programming interfaces (APIs) for Oracle Clusterware makes you capable to remotely execute commands on your database cluster, whether in the Oracle Cloud, at remote physical locations, or locally purveyed. With the remote running of REST interface commands, you are able to get back information about that execution, including output, error codes, and execution lengths. The REST interface allows secure support for Oracle Clusterware command line utilities like CRSCTL, CLUVFY and SRVCTL.

REST APIs for Oracle Clusterware expect that the CDP Cross Cluster Domain Protocol (CDP) daemon is running on all of the SCAN VIPs of the cluster. To support the ability to make requests from outside the cluster, you can run the srvctl modify cdp command to provide a list of IPs or networks in CIDR format.

Below are few of the cluster commands you can run using REST APIs …

To enable connections from outside the cluster, run the following commands.

$ srvctl start cdp
$ srvctl modify cdp -allow "ip/networkid1,ip/networkid2,.."

You can view the configuration information with the following command

$ srvctl config cdp

Get the list of all homes

curl -k -X GET https://scan-name:port/grid/cmd/v1/cmd/ --user admin:DixitTestPassword 

Create a job (crsctl) and monitor the status

curl -k -X POST \
    https://scan-name:port/grid/cmd/v1/cmd/exec \
    '-H "accept: text/plain,text/javascript,application/json"' \
    '-H "content-type: application/vnd.oracle.resource+json;type=singular"' \
     --user admin:DixitTestPassword \
    '-d  {"command" : ["crsctl", "stat", "res", "-t"], "runAsUser":"osUser", "userPassword":"osPasswd"}'

curl -k -X GET https://scan-name:port/grid/cmd/v1/cmd/jobs/myJobId --user admin:DixitTestPassword 

Monitor the status of all jobs

curl -k -X GET \
    https://scan-name:port/grid/cmd/v1/cmd/jobs/ --user admin:DixitTestPassword 

Delete a job

curl -k -X DELETE \ 
         https://scan-name:port/grid/cmd/v1/cmd/jobs/myJobId --user admin:DixitTestPassword 

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , | Leave a Comment »

Oracle zero downtime migration 21.2

Posted by FatDBA on August 31, 2021

I am happy, excited and I guess this time no much writing and explanation is needed, as this sole image is enough 🙂
Pic courtesy: Oracle Corp

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

What are Oracle Managed File (OMF) ?

Posted by FatDBA on September 11, 2012

What are Oracle-Managed Files?
Using Oracle-managed files simplifies the administration of an Oracle database. Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle database. You specify operations in terms of database objects rather than filenames.

– Provides default location, name and size
– OMF is still optional. Normal file creation techniques still available

Oracle internally uses standard file system interfaces to create and delete files as needed for the following database structures:

Tablespaces
Online redo log files
Control files

First you have to enable OMF by altering parameter db_create_file_dest.
Example:
SQL> show parameter db_create

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_create_file_dest                  string

Here i’m trying to create a tablespace with name ‘tb1’ but you’ll recieve error message asking you to provide DATAFILE/TEMPFILE clause.

SQL> create tablespace tbi;
create tablespace tbi
                    *
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause
Alter parameter db_create_file_dest.
SQL> alter system set db_create_file_dest=’/u01/app/oracle/oradata/’ scope=both;

System altered.
SQL> show parameter db_create_file_dest;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_create_file_dest                  string      /u01/app/oracle/oradata/

Let’s try to create Tablespace in same fashion we tried earlier.
SQL> create tablespace tb1;

Tablespace created.
Done.

Posted in Advanced | Tagged: , , | 2 Comments »

ORA-00265: Cannot set archivelog (FIX)

Posted by FatDBA on August 28, 2012

Today while turning on Archivelog facility on one of my Oracle Database i recieved an error while reads “instance recovery required, cannot set ARCHIVELOG mode” and i find a very unique way to get rid of this problem.

SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

 

Resolution Steps:
SHUTDOWN -> STARTUP -> SHUTDOWN IMMEDIATE -> STARTUP MOUNT -> alter database archivelog

 

SQL> shutdown
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  306184192 bytes
Fixed Size                  1219112 bytes
Variable Size             104859096 bytes
Database Buffers          197132288 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

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

SQL> startup mount
ORACLE instance started.
Total System Global Area  306184192 bytes
Fixed Size                  1219112 bytes
Variable Size             109053400 bytes
Database Buffers          192937984 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4

Posted in Basics | Tagged: , | Leave a Comment »

 
%d bloggers like this: