Featured

How to Restrict Parallel Server Processes Running on Specific Oracle RAC Nodes

For this SQL, I want all the parallel processes running on this node or those nodes only.

All Oracle DBA knows, by default, the parallel server processes spawned to execute a SQL statement can operate on any or all Oracle RAC nodes in the cluster.

For big indexes rebuilding , a client wants all the parallel server processes running on specific node(s).

There are two easy ways to achieve this without bouncing database required.

Subscribe to get access

Read more of this content when you subscribe today.

Featured

Install Oracle RAC Software and Create Oracle RAC Database In Silent Mode

In this post, I am going to install Oracle 18c RAC software, and create a 18c RAC database in silent mode.

This post will also apply to how to install Oracle 19c RAC software, and create a 19c RAC database in silent mode.

Subscribe to get access

Read more of this content when you subscribe today.

Featured

Step by Step Setting Up SQL Server Always On Availability Group

This post demonstrates how to build SQL-Server High Availability feature Always On. All the step by step building procedures make use of Virtual machine, Windows OS, Networks, Active Directory Domain, DNS, Firewall, SQL Server, Clustering, etc.

Contents

Subscribe to get access

Read more of this content when you subscribe today.

Thanks for reading this post. If you have any questions about how to build SQL 2019 Always On High Availability Group, feel free to contact us.

Featured

Upgrade Oracle Database by Using AutoUpgrade Utility

Contents

Introduction

This post demonstrates how to upgrade 18c database to 19c by using Oracle upgrade utility tool – AutoUpgrade.

Environment :

Primary Database Host / Database : oemnode1 / OEMREP

Standby Database Host / Database:   stbnode1 / STBOEMREP

Subscribe to get access

Read more of this content when you subscribe today.

Final Checks

DGMGRL> show configuration;

Configuration - dg_oemrep

  Protection Mode: MaxPerformance
  Members:
  oemrep    - Primary database
    stboemrep - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 49 seconds ago)

DGMGRL> show database stboemrep

Database - stboemrep

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 30.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    STBOEMREP

Database Status:
SUCCESS
[oracle@oemnode1 dbs]$ $ORACLE_HOME/OPatch/opatch lspatches

30894985;OCW RELEASE UPDATE 19.7.0.0.0 (30894985)
30869156;Database Release Update : 19.7.0.0.200414 (30869156)
30805684;OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)

OPatch succeeded.
SQL> set pagesize 200
SQL> set linesize 300
SQL>  select CON_ID,
        TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,
        PATCH_ID,
        PATCH_TYPE,
        ACTION,
        DESCRIPTION,
        SOURCE_VERSION,
        TARGET_VERSION
   from CDB_REGISTRY_SQLPATCH
  order by CON_ID, action_time, patch_id;  

    CON_ID ACTION_TIM   PATCH_ID PATCH_TYPE ACTION   DESCRIPTION                                           SOURCE_VERSION  TARGET_VERSION
---------- ---------- ---------- ---------- -------- ----------------------------------------------------- --------------- ---------------
         1 2020-05-31   30805684 INTERIM    APPLY    OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)       19.1.0.0.0      19.1.0.0.0
         1 2020-05-31   30869156 RU         APPLY    Database Release Update : 19.7.0.0.200414 (30869156)  19.1.0.0.0      19.7.0.0.0
         3 2020-05-31   30805684 INTERIM    APPLY    OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)       19.1.0.0.0      19.1.0.0.0
         3 2020-05-31   30869156 RU         APPLY    Database Release Update : 19.7.0.0.200414 (30869156)  19.1.0.0.0      19.7.0.0.0
 SQL> select comp_name, status,version,VERSION_FULL 
        from dba_registry

COMP_NAME                         STATUS     VERSION   VERSION_FULL
--------------------------------- ---------  --------- ------------
Oracle Database Catalog Views     VALID      19.0.0.0.0 19.7.0.0.0
Oracle Database Packages and TypesVALID      19.0.0.0.0 19.7.0.0.0
Oracle Real Application Clusters  OPTION OFF 19.0.0.0.0 19.7.0.0.0
JServer JAVA Virtual Machine      VALID      19.0.0.0.0 19.7.0.0.0
Oracle XDK                        VALID      19.0.0.0.0 19.7.0.0.0
Oracle Database Java Packages     VALID      19.0.0.0.0 19.7.0.0.0
OLAP Analytic Workspace           VALID      19.0.0.0.0 19.7.0.0.0
Oracle XML Database               VALID      19.0.0.0.0 19.7.0.0.0
Oracle Workspace Manager          VALID      19.0.0.0.0 19.7.0.0.0
Oracle Text                       VALID      19.0.0.0.0 19.7.0.0.0
Oracle Multimedia                 VALID      19.0.0.0.0 19.7.0.0.0
Spatial                           VALID      19.0.0.0.0 19.7.0.0.0
Oracle OLAP API                   VALID      19.0.0.0.0 19.7.0.0.0
Oracle Label Security             VALID      19.0.0.0.0 19.7.0.0.0
Oracle Database Vault             VALID      19.0.0.0.0 19.7.0.0.0

15 rows selected.
SQL> show con_name;

CON_NAME
----------------
EMPDBREPOS

SQL> select count(*) from dba_objects where status!='VALID';

  COUNT(*)
----------
         0

SQL> connect / as sysdba
Connected.
SQL>  select count(*) from dba_objects where status!='VALID';

  COUNT(*)
----------
         0

Check TimeZone version has been upgraded to version 32 from version 31 in 18c.

SQL> SELECT * FROM v$timezone_file;

FILENAME             VERSION     CON_ID
-------------------- ------- ----------
timezlrg_32.dat           32          0


SQL> SELECT tz_version FROM registry$database;

TZ_VERSION
----------
        32

SQL> SELECT property_name, property_value
     FROM   database_properties
     WHERE  property_name LIKE 'DST_%'
  ORDER BY property_name

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         32
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL> SELECT DBMS_DST.get_latest_timezone_version  from dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
                         32

Drop GRP ( Guarantee Restore Point )

SQL> select name from v$restore_point;

NAME
--------------------------------------------
AUTOUPGRADE_221145114461854_OEMREP

SQL> drop restore point AUTOUPGRADE_221145114461854_OEMREP;

Restore point dropped.
Featured

Step by Step Installing Oracle Enterprise Manager 13cR3( 13.3.0.0 ) Cloud Control

CONTENTS

Introduction

This post demonstrates the installation of Oracle Enterprise Manager Cloud Control 13c Release 3 (13.3.0.0) on Oracle Linux  7 (x86_64).

Subscribe to get access

Read more of this content when you subscribe today.

Access Enterprise Manager Cloud Control 13c

URL :            https://oemnode1:7803/em
username:  sysman
Password:   you specified during your installation

Capture
Capture

Check OMS and Local Agent Status

Local Agent Status

[oracle@oemnode1 scripts]$ . oraenv
ORACLE_SID = [OEMREP] ? AGENT

[oracle@oemnode1 scripts]$ emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 13.3.0.0.0
OMS Version : 13.3.0.0.0
Protocol Version : 12.1.0.1.0
Agent Home : /u01/app/oracle/Product/Agent/agent_inst
Agent Log Directory : /u01/app/oracle/Product/Agent/agent_inst/sysman/log
Agent Binaries : /u01/app/oracle/Product/Agent/agent_13.3.0.0.0
Core JAR Location : /u01/app/oracle/Product/Agent/agent_13.3.0.0.0/jlib
Agent Process ID : 6463
Parent Process ID : 6403
Agent URL : https://oemnode1:3872/emd/main/
Local Agent URL in NAT : https://oemnode1:3872/emd/main/
Repository URL : https://oemnode1:4903/empbs/upload
Started at : 2020-03-27 23:43:12
Started by user : oracle
Operating System : Linux version 4.14.35-1902.10.7.el7uek.x86_64 (amd64)
Number of Targets : 33
Last Reload : (none)
Last successful upload : 2020-03-28 00:14:07
Last attempted upload : 2020-03-28 00:14:07
Total Megabytes of XML files uploaded so far : 1.72
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0
Available disk space on upload filesystem : 13.15%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2020-03-28 00:13:52
Last successful heartbeat to OMS : 2020-03-28 00:13:52
Next scheduled heartbeat to OMS : 2020-03-28 00:14:52

Agent is Running and Ready

OMS & BI Publisher status

[oracle@oemnode1 ~]$ . oraenv
ORACLE_SID = [OMS] ? OMS

[oracle@oemnode1 ~]$ emctl status oms -bip_only
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
BI Publisher Server is Up

[oracle@oemnode1 ~]$ emctl status oms -details
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host : oemnode1
HTTP Console Port : 7788
HTTPS Console Port : 7803
HTTP Upload Port : 4889
HTTPS Upload Port : 4903
EM Instance Home : /u01/app/oracle/Product/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /u01/app/oracle/Product/gc_inst/em/EMGC_OMS1/sysman/log
OMS is not configured with SLB or virtual hostname
Agent Upload is locked.
OMS Console is locked.
Active CA ID: 1
Console URL: https://oemnode1:7803/em
Upload URL: https://oemnode1:4903/empbs/upload

WLS Domain Information
Domain Name : GCDomain
Admin Server Host : oemnode1
Admin Server HTTPS Port: 7102
Admin Server is RUNNING

Oracle Management Server Information
Managed Server Instance Name: EMGC_OMS1
Oracle Management Server Instance Host: oemnode1
WebTier is Up
Oracle Management Server is Up
JVMD Engine is Up

BI Publisher Server Information
BI Publisher Managed Server Name: BIP
BI Publisher Server is Up

BI Publisher HTTP Managed Server Port : 9701
BI Publisher HTTPS Managed Server Port : 9803
BI Publisher HTTP OHS Port : 9788
BI Publisher HTTPS OHS Port : 9851
BI Publisher is locked.
BI Publisher Server named 'BIP' running at URL: https://oemnode1:9851/xmlpserver/servlet/home
BI Publisher Server Logs: /u01/app/oracle/Product/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/
BI Publisher Log : /u01/app/oracle/Product/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/bipublisher/bipublisher.log
[oracle@oemnode1 ~]$

Most Used Commands for OMS and Agent

Stop only OMS & JVMD Engine

[oracle@oemnode1 ~]$ emctl stop oms
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation.  All rights reserved.
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
JVMD Engine is Down

[oracle@oemnode1 ~]$ emctl status oms
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation.  All rights reserved.
WebTier is Up
Oracle Management Server is Down
JVMD Engine is Down
BI Publisher Server is Up
[oracle@oemnode1 ~]$

Stop OMS including Administration Server, BI Publisher,HTTP Server and Node Manager

[oracle@oemnode1 ~]$ emctl stop oms -all
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation.  All rights reserved.
Stopping Oracle Management Server...
WebTier Successfully Stopped
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
JVMD Engine is Down
Stopping BI Publisher Server...
BI Publisher Server Successfully Stopped
AdminServer Successfully Stopped
BI Publisher Server is Down
[oracle@oemnode1 ~]$

Check the status of OMS

[oracle@oemnode1 ~]$ emctl status oms
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
WebTier is Up
Oracle Management Server is Up
JVMD Engine is Up
BI Publisher Server is Up
[oracle@oemnode1 ~]$

Get detailed OMS status,  SYSMAN Password is required

[oracle@oemnode1 ~]$ emctl status oms -details
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host : oemnode1
HTTP Console Port : 7788
HTTPS Console Port : 7803
HTTP Upload Port : 4889
HTTPS Upload Port : 4903
EM Instance Home : /u01/app/oracle/Product/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /u01/app/oracle/Product/gc_inst/em/EMGC_OMS1/sysman/log
OMS is not configured with SLB or virtual hostname
Agent Upload is locked.
OMS Console is locked.
Active CA ID: 1
Console URL: https://oemnode1:7803/em
Upload URL: https://oemnode1:4903/empbs/upload

WLS Domain Information
Domain Name : GCDomain
Admin Server Host : oemnode1
Admin Server HTTPS Port: 7102
Admin Server is RUNNING

Oracle Management Server Information
Managed Server Instance Name: EMGC_OMS1
Oracle Management Server Instance Host: oemnode1
WebTier is Up
Oracle Management Server is Up
JVMD Engine is Up

BI Publisher Server Information
BI Publisher Managed Server Name: BIP
BI Publisher Server is Up

BI Publisher HTTP Managed Server Port : 9701
BI Publisher HTTPS Managed Server Port : 9803
BI Publisher HTTP OHS Port : 9788
BI Publisher HTTPS OHS Port : 9851
BI Publisher is locked.
BI Publisher Server named 'BIP' running at URL: https://oemnode1:9851/xmlpserver/servlet/home
BI Publisher Server Logs: /u01/app/oracle/Product/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/
BI Publisher Log : /u01/app/oracle/Product/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/bipublisher/bipublisher.log

Stop OEM agent

[oracle@oemnode1 ~]$ emctl stop agent

Start OEM agent

[oracle@oemnode1 ~]$ emctl start agent

Check status of OEM agent

[oracle@oemnode1 ~]$ emctl status agent
Featured

How to Upgrade Linux OS In RAC Environment

This post introduces how to upgrade Linux OS from RHEL 5 to RHEL 7 with zero downtime for two nodes RAC. 

Subscribe to get access

Read more of this content when you subscribe today.

Featured

Step by Step Installing Oracle 12c GI and RAC on Linux 7 Using VirtualBox

Great start to build your own RAC with detailed instructions and explanations. For building 18c/19c/20c GI/RAC, this post is still helpful. After completing this lab, you should be in better position as an Oracle RAC DBA.

Contents

Introduction

This article describes the detailed step by step installation of Oracle Database 12c  (12.1.0.2 64-bit) GI and RAC on Linux (Oracle Linux 7.1 64-bit) using VirtualBox (5.0.10). It applies to Oracle 18c and 19c GI and RAC as well.

Subscribe to get access

Read more of this content when you subscribe today.

Featured

Client Side Transparent Application Failover for Oracle RAC

TAF – Transparent Application Failover is a very useful feature in mission critical environment.

TAF – – Transparent Application Failover is a feature of the OCI driver, and TAF cannot be used with thin driver. The client side TAF can be configured through client connection definitions in TNS connect descriptors.

The tests are held under the following environment,  both 11gR2 database and client are used to test the behaviors of client side TAF by using SCAN.

                    GI: 12.1.0.2.0
4 nodes RAC: 11.2.0.4
Oracle Client: 11.2.0.4

The service name is RAC_TEST running on node1 and node4, while available nodes are node2 and node3.

$ srvctl status service -s RAC_TEST -d TESTDB
Service RAC_TEST is running on instance(s) TESTDB1,TESTDB4

There are two TYPES of TAF available, SESSION and SELECT.

SESSION: Session Failover re-creates the connections and sessions to the surviving instance.

SELECT: In addition to recreating the session, Select Failover also replays the queries that were in progress.

There are two METHODS in which TAF establishes the failover connection, BASIC and PRECONNECT.

BASIC: The second connection is re-established only after the first connection to the instance or service failed.

PRECONNECT: Two connections are established when the client logs in to the database. A login to database will create two connection at the same time. For this to work, clusterware actually starts two services. One main service and another shadow service.

Client Side TAF Configuration — BASIC

For non-TAF database connection, by query gv$session to get client side TAF connection details, which demonstrates the server side TAF is not configured. That is what we expected in this case.

SQL>select INST_ID,USERNAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER
from gv$session
where username='TESTUSER';

INST_ID USERNAME FAILOVER_TYPE FAILOVER_M FAILED_OVER ------- --------- -------------- ----------- -------------
4 TESTUSER NONE NONE NO

Use the following TNS entry to test client side TAF with BASIC method.

CLIENT_TAF =
( DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =CLU-SCAN)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = RAC_TEST)
(FAILOVER_MODE= (TYPE=select)(METHOD=basic)
(RETRIES=20)(DELAY=15))
)
)

1) Make a database connection by using TNS entry CLIENT_TAF, and run a long query :

SQL>select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,
           FAILOVER_METHOD,FAILED_OVER 
      from gv$session 
where username='TESTUSER';

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI -------- ----- -------- --------- ------------- ----------- --- 4 70 11171 TESTUSER SELECT BASIC NO

2) Manually kill the session ( only works when session is ACTIVE, otherwise it is not working for killing from testing ).

SQL> alter system kill session '70,11171,@4';

System altered.

 Query the session status, we can see a new failed over session with different ‘SID, serial#’ created, and the query is still going until completed.

 SQL> select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,
             FAILOVER_METHOD,FAILED_OVER 
from gv$session
where username='TESTUSER';

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAILED_OVER -------- ---- ------- -------- ------------- --------- ------------ 4 366 14271 TESTUSER SELECT BASIC YES

3) Instead of killing session in step 2, we manually stop the service on node 4 to simulate instance crash. (  it works for both ACTIVE and INACTIVE sessions ).

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE  FAILOVER_M FAI
------- --- ------- -------- -------------- ---------- ---
4 70 11185 TETSUSER SELECT BASIC NO

Now stop the service on node4, expect the session will failover onto survival node1 .

$ srvctl stop service -s RAC_TEST -i TESTDB4 -d TESTDB -f
SQL> select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,
FAILOVER_METHOD,FAILED_OVER
from gv$session
where username='TESTUSER';

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI
------- --- -------- --------- -------------- ---------- ---
1 367 8663 TETSUSER SELECT BASIC YES

restart the service on node4 for more tests.

$ srvctl start service -s RAC_TEST -i TESTDB4 -d TESTDB

Client Side TAF Configuration — PRECONNECT

Two tnsname entries are prepared with following contents:

PRIMARY=
( DESCRIPTION=(ADDRESS=
(PROTOCOL=tcp)(HOST=clu-scan)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=RAC_TEST)
(FAILOVER_MODE=(BACKUP=SECONDARY)(TYPE=select)
(METHOD=preconnect))
)
)

SECONDARY=(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=clu-scan)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=RAC_TEST)
(FAILOVER_MODE=
(BACKUP=PRIMARY)
(TYPE=select)
(METHOD=preconnect))
)
)

1) Make a database connection by using TNS entry above, and run a long query :

SQL> select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,
FAILOVER_METHOD,FAILED_OVER,status
from gv$session
where username='TESTUSER';

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI STATUS
------- ---- ------ ---------- ------------ ----------- --- -------- 1 42 8485 TESTUSER NONE NONE NO ACTIVE 4 366 14375 TESTUSER SELECT PRECONNECT NO INACTIVE

2) Manually stop service RAC_TEST on node 4.

$ srvctl stop service -s RAC_TEST -i TESTDB4 -d TESTDB -f

3) The preconnected session takes over the session and continue the query until completion.

SQL> select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,
FAILOVER_METHOD,FAILED_OVER,status
from gv$session
where username='TESTUSER';

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI STATUS ------- --- ------- ---------- ------------ ------------- --- -------- 1 42 8485 TESTUSER SELECT PRECONNECT YES ACTIVE

Restart service on node4 for next test.

$ srvctl start service -s RAC_TEST -i -i TESTDB4 -d TESTDB

4) if you kill this PRECONNECT session again , TAF still works again as preconnect.

SQL> alter system kill session '42,8485,@1';

System altered.
SQL> select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,
FAILOVER_METHOD,FAILED_OVER,status
from gv$session
where username='TESTUSER';

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI STATUS
------- --- ------- --------- ------------ ----------- --- ------
4 265 1563 TESTUSER SELECT PRECONNECT YES ACTIVE

TAF – Transparent Application Failover is a very useful feature in mission critical environment, and it should be used whenever it is available and possible.

How to Change the Owner of Database in SQL SERVER

Check database owner by the below sql query:

select name, SUSER_SNAME(owner_sid) OWNER from sys.databases;

To change the owner of a database, the following either way should do the job.

  1. On SQL Server Management Studio -> Right Click on Database -> Properties-> Files ->click Owner..

2. Change owner of a database by sql.

ALTER AUTHORIZATION ON DATABASE::TESTDB TO "VIRTUALLAB\Administrator";

If you meet the following error messages.

Msg 15110, Level 16, State 1, Line 48
The proposed new database owner is already a user or aliased in the database.

Then drop the user first, and re-run the above one.

USE "TESTDADB";
GO
drop user "VIRTUALLAB\Administrator";

ALTER AUTHORIZATION ON DATABASE::TESTDB TO "VIRTUALLAB\Administrator";

SQL Server Database in “Recovery Pending“ Status

Due to temporary disk availability issue, SQL Server database is in “Recovery Pending“ Status.

Recovery Pending

Let’s step by step repair the database by the following guidelines:

  • Make sure disks are available and with enough space for databases.
  • Make a backup of datafiles and logfiles. In case repairing failure, so the alternative repairing methods can be used later against database restoration from this backup.
  • Manually set database online.
  • Run CheckDB.
DBCC CHECKDB

You can run CHECKDB with option ‘NO_INFOMSGS’ to eliminate the informational messages.

DBCC CHECKDB ('TestDatabase') WITH NO_INFOMSGS;

In this case, CheckDB has completed without warning, so the database is healthy. Otherwise continue to repair the database.

• Set to single user mode for databases to be repaired.

ALTER DATABASE TestDatabase SET SINGLE_USER;

• Start to repair the database.

First try “REPAIR_REBUILD” option.

DBCC CHECKDB('TestDatabase',REPAIR_REBUILD)

The repaired database can be se to multiple user mode now, if it is successful.
Otherwise continue to repair with level ‘REPAIR_ALLOW_DATA_LOSS’.

DBCC CHECKDB(‘TestDatabase’,REPAIR_ALLOW_DATA_LOSS); 

Please note the option name explains the potential data loss could happen.

If the repairing is successful, the database can be set back to multiple user status to open to users.

If the repairing is still a failure, then continue to next step for repairing with ‘EMERGENCY’ mode.

ALTER DATABASE TestDatabase SET EMERGENCY;
ALTER DATABASE TestDatabase SET SINGLE_USER;
DBCC CHECKDB ('TestDatabase',REPAIR_ALLOW_DATA_LOSS) WITH  NO_INFOMSGS,ALL_ERRORMSGS;

Finally set database to ‘online’ and multiple user mode.

ALTER DATABASE TestDatabase SET ONLINE;
ALTER DATABASE TestDatabase SET MULTI_USER;

Let’s check database online and normal now.

DataPump: UDE-00008 ORA-31626 ORA-39086

The following errors occur when exporting data by using DataPump:

UDE-00008: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-39086: cannot retrieve job information
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2772
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3886
ORA-06512: at line 1

Check the log which shows exporting has been successful.

...
..
.
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
C:\TEMP\TEST.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:08:38

So it is a bug, and this error can be ignored.

How to Check and Fix Dead Tuples Not Removed By Vacuum From PostgreSQL Tables

Why Vacuum ?

In PostgreSQL, when rows are updated or deleted from a table, dead rows are created and left behind, vacuum daemon processes will clean those dead tuples ( rows) periodically based on parameter setting for vacuum process. Generally the following four reasons explain why vacuum is needed:

  1. To recover or reuse disk space occupied by updated or deleted rows.
  2. To update data statistics used by the PostgreSQL query planner.
  3. To update the visibility map, which speeds up index-only scans.
  4. To protect against loss of very old data due to transaction ID wraparound or multixact ID wraparound.

Tables’ Dead Tuples Not Removed

The following query result shows the dead tuples are not removed by auto vacuuming for some reasons. But they should have been removed from tables.

 schemaname  |          relname          | n_live_tup | n_dead_tup |    overthread    |              now              |        last_autovacuum        | autovacuum_count
------------+---------------------------+------------+------------+------------------+-------------------------------+-------------------------------+------------------
 pg_catalog | pg_statistic              |       3298 |      48500 |  127.69878883623 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:34:11.607386+00 |          1797116
 pg_catalog | pg_attribute              |       1116 |       6643 | 41.1076732673267 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:34:28.653988+00 |          1589637
 cdrdb      | users                     |      27316 |      87554 | 31.4761288467069 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:35:35.891844+00 |          1717941
 cdrdb      | products                  |        645 |       1689 | 14.7510917030568 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:35:32.192822+00 |          1596895
 cdrdb      | price                     |     154492 |     168499 |  10.871464333643 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:35:05.049206+00 |          1655072
 cdrdb      | contract_pdf              |     157701 |     165106 | 10.4364700602398 | 2020-10-10 03:35:43.573615+00 | 2020-10-10 03:35:27.945506+00 |          1656837
...
..
.
(27 rows)

Subscribe to get access

Read more of this content when you subscribe today.