Tuesday, July 5, 2022

 

ORA-01012: not logged on error

HOME » ORA-01012: NOT LOGGED ON ERROR

ORA-01012: not logged on error while trying to start the oracle database.







Solution:

To resolve this error  remove the orphaned shared memory segment using sysresv utility. sysresv command will list the currently allocated IPC resources for shared memory and remove the shared memory segment using ipcrm -m command.





Now start the database



 

Wednesday, April 20, 2022

 Determine an index needs to be rebuilt


First, the relevant index should be analyzed. You can do this with the following command.

SQL> analyze index <username>.<index_name> validate structure;

Index analyzed.


The analysis process fills the table “sys.index_stats”. This table contains only one row and therefore only one index can be analyzed at a time. Information about the relevant index can be obtained from the sys.index_stats table in the analyzed session.


SQL> select del_lf_rows,lf_rows,height,lf_rows,lf_blks from sys.index_stats;


DEL_LF_ROWS    LF_ROWS    HEIGHT    LF_ROWS LF_BLKS

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

  842     41356545           3       41356545 109441



After the analysis, according to the data in the “sys.index_stats” table, if any of the following conditions occur you can decide whether rebuild the index or not.


If the percentage of deleted rows exceeds 30% of the total. So if del_lf_rows / lf_rows> 0.3 in the sys.index_stats table.

If ‘HEIGHT’ is greater than 4.

If the number of rows in the index(LF_ROWS) is much less than (LF_BLKS). This indicates that too many records have been deleted from the index.

When one of these conditions occurs you can rebuild the index as follows.


SQL> alter index <username>.<index_name> rebuild online;


Index altered.

Wednesday, November 10, 2021

 

How to use the purge command in adrci


Active Diagnostic Repository (ADR), which serves as a common repository for all log files, tracefiles, incidents etc, that the database produces.

ADR has a "short" and a "long" purging policy. The default values are:

• 720 hours (30 days) for SHORTP_POLICY
• 8760 hours (356 days) for LONGP_POLICY

Each policy controls different types of content:

These files are controlled by the value of LONGP_POLICY:

ALERT
INCIDENT
SWEEP
STAGE
HM

These files are controlled by the value of SHORTP_POLICY:

TRACE
CDUMP
UTSCDMP
IPS

Sometimes it may be necessary to manually purge the ADR. The following example will delete all trace files older than 48 hours:
adrci> purge -age 48 -type trace
Other examples (2160 hrs = 90 days):
purge -age 2160 -type alert
purge -age 2160 -type incident
purge -age 2160 -type cdump
purge -age 2160 -type stage
purge -age 2160 -type sweep
purge -age 2160 -type hm

The policies can be adjusted according to need by using the following commands :
adrci> show control

ADR Home = /u01/oracle/diag/rdbms/proddb01/proddb01:

*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY
-------------------- -------------------- --------------------
597879840            720                  8760

Set a new policy (2 and 3 days, respectively):
set control (SHORTP_POLICY = 48)
set control (LONGP_POLICY = 72)

An example from one of my databaes:
adrci> show incident -mode basic

ADR Home = /u01/oracle/diag/rdbms/proddb01/proddb01:
*************************************************************************

INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
30858                ORA 603                                                     2014-03-19 11:26:41.287000 +01:00
30857                ORA 600 [kqlidchg0]                                         2014-03-19 11:26:38.226000 +01:00
27284                ORA 603                                                     2014-03-18 14:42:28.228000 +01:00
27283                ORA 600 [kqlidchg0]                                         2014-03-18 14:42:23.706000 +01:00
.
.
.
17044                ORA 603                                                     2013-09-26 15:37:30.870000 +02:00
17043                ORA 600 [kqlidchg0]                                         2013-09-26 15:37:27.255000 +02:00
17042                ORA 603                                                     2013-09-26 15:35:45.918000 +02:00
17041                ORA 600 [kqlidchg0]                                         2013-09-26 15:35:42.239000 +02:00

45 rows fetched
Remove incidents that are older than 90 days:
adrci> purge -age 2160 -type incident
Another check of the incident list shows that the number has been reduced:
adrci> show incident -mode basic

ADR Home = /u01/oracle/diag/rdbms/sergat/sergat:
*************************************************************************

INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
30858                ORA 603                                                     2014-03-19 11:26:41.287000 +01:00
30857                ORA 600 [kqlidchg0]                                         2014-03-19 11:26:38.226000 +01:00
27284                ORA 603                                                     2014-03-18 14:42:28.228000 +01:00
27283                ORA 600 [kqlidchg0]                                         2014-03-18 14:42:23.706000 +01:00
.
.
.
24 rows fetched

Wednesday, October 13, 2021

 

Enable and disable data guard broker


https://logic.edchen.org/how-to-stop-data-guard-services/


1. Login to primary db server


$ dgmgrl sys/Manager_1@sslcdb

DGMGRL> show configuration

2. Check the apply and transport status:


Disable Data Guard with Broker


DGMGRL> show database sslcdb

Database - SSLCDBDR

Role:               PRIMARY

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

@stanby

$ dgmgrl sys/Manager_1@sslcdbdr

DGMGRL> show database sslcdbdr

Database - SSLCDBDR

  Role:               PHYSICAL STANDBY


3. DGMGRL Stop Transport

disable transport service of the primary database.

dgmgrl sys/Manager_1@sslcdb


DGMGRL> EDIT DATABASE sslcdbdr SET STATE='TRANSPORT-OFF';

Succeeded.


4. DGMGRL Stop Apply

dgmgrl sys/Manager_1@sslcdbdr


DGMGRL> edit database SSLCDBDR set state='APPLY-OFF';

Succeeded.


5. Disable DG Configuration


DGMGRL> DISABLE CONFIGURATION;

Disabled.


Enable Data Guard with Broker


6. Disable DG Configuration


DGMGRL> ENABLE CONFIGURATION;

Enableabled.


7. DGMGRL Start Transport


Enableable transport service of the primary database.

dgmgrl sys/Manager_1@sslcdb


DGMGRL> EDIT DATABASE sslcdbdr SET STATE='TRANSPORT-ON';

Succeeded.


8. DGMGRL Start Apply


dgmgrl sys/Manager_1@sslcdbdr


DGMGRL> edit database SSLCDBDR set state='APPLY-ON';

Succeeded.


SQL> show parameter dg_broker_start

NAME                                 TYPE        VALUE

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

dg_broker_start                      boolean     TRUE


SQL> select name, value from v$parameter where name = 'dg_broker_start';


NAME                       VALUE

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

dg_broker_start            TRUE


Tuesday, October 12, 2021

 SQL query to get DB info

spool dbinfo.html
set markup html on
select * from v$version;
select * from v$instance;
select * from v$parameter;
select * from v$resource_limit;
select * from dba_users;
select * from dba_registry;
SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90

SELECT
a.ksppinm "Parameter",
decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
c.ksppstvl "Instance",
decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
decode(p.isdefault,'FALSE','F','TRUE','T') "D",
a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
ORDER BY a.ksppinm;

set markup html off
spool off

Monday, October 11, 2021

 

ORA-10873: file 1 needs to be either taken out of backup mode or media recovered


Scenario :

Database went down due to power fluctuation and during that time hot backup was running.So once we started the database we face the following issue.

Error :

ORA-10873: file 1 needs to be either taken out of backup mode or media recovered

ORA-01110: data file 1: ‘D:\ORADB\RECTCDB\system01.dbf’

Cause :

Database is in backup mode while it went down.

Solution :

Step 1 : Sqlplus  ‘/as sysdba’

Step 2 : startup mount

Step 3 : Check backup file using below command.

select * from V$BACKUP

Step 4 : The following command can be used to take all of the data files out of hot backup mode:


SQL> ALTER DATABASE END BACKUP;

Step 5 :  SQL> Alter database open;






























Friday, October 8, 2021

 

Enable and Disable the dataguard steps

Disable Data Guard

On Standby Database:
1. Cancel the Recovery job with following command

alter database recover managed standby database cancel;

2. Stop the destination

alter system set log_archive_dest_state_2=DEFER;

On Primary Database:
1. Stop the standby destination for transport archives.

ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;

Enable the dataguard:
On Primary DB:
1. Enable the archive log transport:

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

On Standby DB:
1. Start the recovery process with following command:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

2. Enable the log archive for switchover condition needed.

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

 
Shutdown and Start the Primary / Standby database dataguard.

https://dbaclass.com/article/remove-dataguard-configuration-from-primary-database/

 RMAN Recovery Catalog About Recovery Catalog RMAN recovery catalog Is another database which is out of your normal databases or which is o...