
Oracledbwr
644 FOLLOWERS
Articles on Database Architecture, Real Application Clusters, Performance Tuning, Backup and Recovery, Disaster Recovery, Dataguard, GlodenGate, Cloud DBA and much more.
Oracledbwr
1y ago
Description:-
In this article we are going to see about Oracle standby database – Archivelog gap
Archivelog gap
set pagesize 320
set linesize 200
set wrap off
select thread#, sequence#, applied,to_char(first_time,’mm/dd/yy hh24:mi:ss’) first,to_char(next_time, ‘mm/dd/yy hh24:mi:ss’) next,to_char(completion_time, ‘mm/dd/yy hh24:mi:ss’) completion
from v$archived_log where thread# = 1 order by first_time;
Log Gap Check
SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM (SELECT T ..read more
Oracledbwr
1y ago
Description:-
In this article we are going to see about Invalid Component of Oracle XML Database
Demo:-
Step:-1 Check invalid Invalid Component
SQL> SELECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS=’INVALID’;
COMP_NAME
——————————————————————————–
Oracle XML Database
Step:-2 execute the below packages and triggers
ALTER PACKAGE XDB.DBMS_CLOBUTIL COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_CSX_ADMIN COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_CSX_INT2 COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_JSON COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_JSON_INT COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_SODA_ADMIN CO ..read more
Oracledbwr
1y ago
Invalid Component of Spatial SDO
Description:-
In this article we are going to see about Invalid Component of Spatial SDO
Demo:-
Step:-1 Check invalid Invalid Component
SQL> alter session set container=PDB$SEED;
Session altered.
SQL> SELECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS=’INVALID’;
COMP_NAME
——————–
Spatial
Step:-2 execute the below script
SQL> @$ORACLE_HOME/md/admin/sdopatch.sql
Session altered.
Session altered.
Procedure created.
No errors.
Session altered.
Session altered.
Upgrading mdprivs.sql
Session altered.
Grant succeeded.
User altered.
User altered.
Gr ..read more
Oracledbwr
2y ago
Description:-
In this article we are going to see about Invalid Component of Oracle Database Catalog Views- catalog
Problem:-
Invalid Component of Oracle Database Catalog Views-catalog after RU pathing on database.
catalog.sql script needs to execute on startup upgrade mode and rebuild the component
[oracle@oracledbwr ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 – Production on Mon Apr 3 17:15:07 2023
Version 21.7.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1241512 ..read more
Oracledbwr
2y ago
Description:-
In this article we are going to see about Invalid Component of Oracle Database Packages and Types-catproc
Demo:-
Problem:-
Invalid component of Oracle Database Packages and Types (catproc) after RU pathing on database.
catproc.sql script needs to execute on startup upgrade mode and rebuild the component
Step:-1 Check the component state and startup upgrade mode
[oracle@oracledbwr ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 – Production on Mon Apr 3 18:49:07 2023
Version 21.7.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to an idle instance.
SQ ..read more
Oracledbwr
2y ago
Description:-
In this article we are going to see about Temporary Tablespace Groups.
Large transactions can sometimes run out of temporary space. Large sort jobs, especially those involving tables with many partitions, lead to heavy use of the temporary tablespaces, thus potentially leading to a performance issue.
Benefits of Temporary Tablespace Groups
A single user can simultaneously use multiple temporary tablespaces in different sessions.
You can specify multiple default temporary tablespaces at the database level.
Parallel execution servers in a parallel operation will efficientl ..read more
Oracledbwr
2y ago
Description:-
In this article we are going to see how to rezize Temp Tablespace on PDB database level
Demo:-
Login in to PDB Database
SQL> alter session set container=ORCLPDB;
Session altered.
First Check Temporary Tablespace Usage
From PDB Level:-
SELECT A.tablespace_name tablespace, D.GB_total,SUM (A.used_blocks * D.block_size) / 1024 / 1024 / 1024 GB_used,D.GB_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 / 1024 GB_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 / 1024 GB_total
FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP B ..read more
Oracledbwr
2y ago
Description:-
How to Startup And Shutdown The Oracle Pluggable Database
Startup Oracle PDB Database:-
Method:-1 From PDB database level
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
3 ORCLPDB MOUNTED
SQL> select con_id, name, open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
———- ——————– ———-
3 ORCLPDB MOUNTED
SQL> startup
Pluggable Database opened.
SQL> select con_id, name, open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
———- ——————– ———-
3 ORCLPDB READ WRITE
OR
SQL> select con_id, name, open ..read more
Oracledbwr
2y ago
Description:-
In this article, I will elaborate on how we can cloning a PDB database in Same CDB using DBCA method
Environment:-
COLUMN DB_NAME FORMAT A10
COLUMN CON_ID FORMAT 999
COLUMN PDB_NAME FORMAT A15
COLUMN OPERATION FORMAT A16
COLUMN OP_TIMESTAMP FORMAT A10
COLUMN CLONED_FROM_PDB_NAME FORMAT A15
SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY
WHERE CON_ID > 2
ORDER BY CON_ID;
Demo:-
Step:-1 Prepare the oradbwrpdb1 Before Cloning
[oracle@oracle21c ~]$ sqlplus scott/tiger@oradbwrpdb1
SQL*Plus: Release 21.0.0.0.0 – Producti ..read more
Oracledbwr
2y ago
Description:-
Starting with Oracle 21c new features, one of them called PREPARE DATABASE FOR DATA GUARD , which is used to perform basic configurations on the primary server when creating a database with Dataguard like Creates standby redo log files, Enables force logging, Enables Flashback & enables Archivelog, Set required parameters
Note:- Remember that version 21c is Innovation Release ,It is not Long Term support Release
Let’s start Demo:-
Prepare a primary database for a Data Guard environment in Oracle 21c
Environment:-
Before Activity: –
SQL> show parameter db_name
NAME TYPE ..read more