ASM Queries

 SET LINESIZE  550

SET PAGESIZE  9999

SET VERIFY    off  

 

COLUMN full_alias_path        FORMAT a63                  HEAD 'File Name'

COLUMN system_created         FORMAT a8                   HEAD 'System|Created?'

COLUMN type                   FORMAT a18                  HEAD 'File Type'

COLUMN redundancy             FORMAT a12                  HEAD 'Redundancy'

COLUMN striped                FORMAT a8                   HEAD 'Striped'

COLUMN creation_date          FORMAT a20                  HEAD 'Creation Date'

COLUMN disk_group_name        noprint

SELECT

    CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path

  ,  space/(1024*1024*1024*2) "InGB"

  , NVL(LPAD(type, 18), 'DATAC1')  type

  , creation_date

  , disk_group_name

  , LPAD(system_created, 4) system_created

FROM

    ( SELECT

          g.name               disk_group_name

        , a.parent_index       pindex

        , a.name               alias_name

        , a.reference_index    rindex

        , a.system_created     system_created

        , f.bytes              bytes

        , f.space              space

        , f.type               type

        , TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS')  creation_date

      FROM

          v$asm_file f RIGHT OUTER JOIN v$asm_alias     a USING (group_number, file_number)

                                   JOIN v$asm_diskgroup g USING (group_number)

    )

WHERE type IS NOT NULL

and  space/(1024*1024*1024*2) >500

START WITH (MOD(pindex, POWER(2, 24))) = 0

    CONNECT BY PRIOR rindex = pindex

order by space/(1024*1024*1024*2) desc 

/

Migration

 Migration

select  count(1),segment_type from dba_segments where tablespace_name='LOB_NEW' group by segment_type;

SELECT TABLE_OWNER,TABLE_NAME,COLUMN_NAME,LOB_NAME,PARTITION_NAME,LOB_PARTITION_NAME,SECUREFILE,TABLESPACE_NAME

FROM DBA_LOB_PARTITIONS 

WHERE TABLE_OWNER = 'CCUSER' 

AND TABLESPACE_NAME = 'LOB'

AND TABLE_NAME = 'CC_MESSAGE';

select 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' MOVE PARTITION '||partition_name||' LOB ('||column_name||') STORE AS BASICFILE (TABLESPACE LOB_NEW) parallel 32;' from 

DBA_LOB_PARTITIONS 

WHERE TABLE_OWNER = 'CCUSER' 

AND TABLESPACE_NAME = 'LOB'

AND TABLE_NAME = 'CC_MESSAGE';

select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE PROD_DATA;' from DBA_TABLES WHERE OWNER ='PROD';

select 'ALTER INDEX ' || OWNER || '.'|| index_name ||' rebuild tablespace INDX_TB online;' from dba_indexes where owner='PROD';

can you try this :

create tablespace lepdata 
datafile '+p1rac1d4_oradata' size 20g
logging
online
permanent
blocksize 8192
extent management local autoallocate
default nocompress
segment space management auto;

Course URL: - https://learn.microsoft.com/en-us/training/courses/AZ-104T00?WT.mc_id=ilt_partner_webpage_wwl&ocid=4428290&DCS=DEPR2340032#study-guide 

Achievement Code URL: - https://learn.microsoft.com/en-us/users/me/achievements?redeem=RDG8DZ&WT.mc_id=ilt_partner_webpage_wwl&ocid=4428290&DCS=DEPR2340032

https://ind01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fforms.office.com%2Fr%2FNx5dy3JiLW&data=05%7C01%7CRM00759529%40TechMahindra.com%7C465303814e924bfb158708db1b1939f9%7Cedf442f5b9944c86a131b42b03a16c95%7C0%7C0%7C638133567383492233%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=C%2FAc9WeKaVKB0QRGjFuk0x%2B6WZQRRgrrrjtKsJJtwkY%3D&reserved=0

important points

1.  Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle 8i introduced the Statspack functionality which Oracle 9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).
2. Oracle 10g took code instrumentation in the kernel to a whole new level with the introduction of the Automatic Workload Repository (AWR) and Active Session History (ASH) features. Oracle 11g takes that further with options such as the Automatic Diagnostic Repository (ADR) and the SQL Performance Analyzer (SPA).
3. In Oracle database 11g, a new facility, the ADR, was added. As part of this new facility, there’s a new V$ view—V$DIAG_INFO.
4. select * from v$diag_info;
5. Starting in Oracle database 11g, The ADRCI tool allows you to review “problems” (critical errors in the database) and incidents (occurrences of those critical errors) and to package them up for transmission to support
   adrci> show tracefile -I 6177
   This shows me the location of the trace file for incident number 6177. Further, I can see a lot of detail about the incident if I so choose:
   adrci> show incident -mode detail -p "incident_id=6177"
6. Automatic Storage Management (ASM): This is a new feature of Oracle 10g Release1 (for both Standard and Enterprise editions).
7. Oracle 11g Release 2, ASM provides not only this database file system but optionally a clustered file system as well, which is described next.
8. This feature of multiple block sizes was introduced for the purpose of making transportable tablespaces usable in more cases.
9. Tablespaces with multiple block sizes should be used to facilitate transporting tablespaces; they are not generally used for anything else.
10. The change-tracking file is a new, optional file for use with Oracle 10g Enterprise Edition and above
11. Flashback logs were introduced in Oracle 10g in support of the FLASHBACK DATABASE command
12. The Data Pump format is exclusive to Oracle 10g Release 1 and above—it did not exist in any Oracle9i release, nor can it be used with that release.
13. PGA_AGGREGATE_TARGET: This parameter controls how much memory the instance should allocate, in total, for all work areas used to sort or hash data.

14. select sum(bytes) from v$sgastat where pool = 'shared pool';

    The sharedpool holds many other structures that are outside the scope of the corresponding parameter. The SHARED_POOL_SIZE is typically the largest contributor to the shared pool as reported by the SUM(BYTES), but it is not the only contributor.

    In Oracle 10g, the SHARED_POOL_SIZE parameter controls the size of the shared pool, whereas in Oracle9i and before, it was just the largest contributor to the shared pool. You should review your 9i and before actual shared pool size (based on V$SGASTAT) and use that figure to set your SHARED_POOL_SIZE parameter in Oracle 10g and above.

15. When someone complains of deadlocks in the database, I have them run a script that finds unindexed foreign keys; 99 percent of the time we locate an offending table. By simply indexing that foreign key, the deadlocks—and lots of other contention issues—go away.

16. The number one cause of deadlocks in the Oracle database, in my experience, is unindexed foreign keys. (Thenumber two cause is bitmap indexes on tables subject to concurrent updates, which we’ll cover in Chapter 11).

17. The higher the transaction isolation level, the locking overhead can increase while user concurrency can decrease.

18. GRANT execute ON dbms_monitor TO scott;

19. What happens when you run an UPDATE
    statement, as follows, and while that statement is running, someone updates a row it has yet to read from Y=5 to Y=6
    and commits?
    Update t Set x = 2 Where y = 5;

20. So, why are constraints validated after the SQL statement executes? Why not during? This is because it is very natural for a single statement to make individual rows in a table momentarily inconsistent.
21. we also have the ability to defer constraint checking, which can be quite advantageous for

    various operations. The one that immediately jumps to mind is the requirement to cascade an UPDATE of a primary key to the child keys.
22. Page:290 : the bottom line is, only use deferrable constraints where you have an identified need to use them. They introduce subtle side effects that could cause differences in your physical implementation (non-unique vs. unique indexes) or in your query plans 
23. With the single UPDATE statement, we just reissue the

    UPDATE. We know that it will entirely succeed or entirely fail; there will not be partial work to worry about.
24. My final words on bad transaction habits concern the one that arises from using the popular programming APIs ODBC and JDBC. These APIs “autocommit” by default.
25. Every Oracle database has at least two online redo log groups with at least a single member (redo log file) in each group.
26. Those two things together—that the segment was actually created by the INSERT but not “uncreated” by the ROLLBACK, and that the new formatted blocks created by the INSERT were scanned the second time around—show that a rollback is a logical “put the database back the way it was” operation. The database will not be exactly the way it was, just logically the same.

RMAN Backup status

 

This script will report on all currently running RMAN backups like full, incremental & archivelog backups:

col STATUS format a9

col hrs format 999.99

select SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,

elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

Below script will give you SID, Total Work, Sofar & % of completion.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, 

ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

Oracle ASM

Column HOT_USED_MB & COLD_USED_MB in V$ASM_DISKGROUP

 

What's the below two columns use for?

HOT_USED_MB 
Number of used megabytes in the hot region  

COLD_USED_MB 
Number of used megabytes in the cold region 

you can do it all using a new attribute of diskgroup which allows two values: “hot” and “cold”. The former puts the data near the edges while the latter places it near the center. This is done via templates. Let’s define a template – hot_files – for those most accessed files.

alter diskgroup dg1 add template hot_files attributes (hot) /                        

Diskgroup altered.  

Once the template is created, you can use it to create the datafiles using that template:

create tablespace hot_ts datafile '+DATA(hot_files)/hot_ts_01.dbf' size 1M / 

in a disk, the outer part have greater performance than the inner part.  ---imagine a disk as your DVD or CD.

outer parts are called hot region and inner parts are called cold region.

oracle give you the flexibility to be able to place files in either hot or cold region. IF the geometry  is not mask from ASM instance.

++++++++++++++The below is the db alert log file during startup, ASMB is the process from database to ASM instance communication for data write and read+++++++++++++

This instance was first to open

Starting background process ASMB

Tue Sep 06 23:54:44 2022

ASMB started with pid=35, OS id=5410

Tue Sep 06 23:54:44 2022

NOTE: ASMB registering with ASM instance as Standard client 0xffffffffffffffff (reg:1656733188) (new connection)

Starting background process RBAL

Tue Sep 06 23:54:44 2022

RBAL started with pid=36, OS id=5414

Tue Sep 06 23:54:44 2022

NOTE: Loaded library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so

Tue Sep 06 23:54:44 2022

NOTE: ASMB connected to ASM instance +ASM1 osid: 5412 (Flex mode; client id 0xffffffffffffffff)

NOTE: initiating MARK startup

Starting background process MARK

++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SQL> alter database datafile '+TESTDG/NMS/DATAFILE/testtbs.256.1114369479' offline;

Database altered.

SQL>

Tablespace monitoring

 set linesize 180

select b.db_unique_name, a.tablespace_name

               ,sum(a.tots)/1024/1024/1024 "Total Size(GB)"

               ,sum(a.sumb)/1024/1024/1024 "Total Free(GB)"

           ,sum(a.sumb)*100/sum(a.tots) "% Free"

                    ,sum(a.tots-a.sumb)*100/sum(a.tots) "% Used"

          ,sum(a.largest)/1024/1024/1024 "Max Free"

           from

          (

            select tablespace_name

               ,0 tots

               ,sum(bytes) sumb

              ,max(bytes) largest

        from dba_free_space a

      group by tablespace_name

      union

      select tablespace_name

            ,sum(bytes) tots

            ,0

            ,0

       from dba_data_files

      group by tablespace_name) a , v$database b , v$instance c

 where a.tablespace_name NOT like 'UNDO%' and c.INSTANCE_NUMBER=1

        group by a.tablespace_name,b.db_unique_name,c.INSTANCE_NUMBER

having sum(a.sumb)*100/sum(a.tots) > 50

and sum(a.tots)/1024/1024/1024 > 100

order by sum(a.tots)/1024/1024/1024 desc

     ;

Note: use gv$instance if it is a rac

2. tablespace_monitoring.sh

HOST=`hostname | cut -b1-14`

dt=`date +%d%m%Y`

BASE_DIR=/u01/app/oracle/scripts/tablespace_monitoring

LOG_DIR=/u01/app/oracle/scripts/tablespace_monitoring/LOG

SPOOL_FILE=${LOG_DIR}/Tablespace_status_${dt}.html

ps -ef|grep smon|grep -v grep |grep -v ASM |awk '{print $8}'|cut -b10-20 > ${BASE_DIR}/NONPROD_DATABASE_LIST.txt

while read line

do

export ORACLE_SID=$line

export ORACLE_HOME=`grep -w "$line" /etc/oratab | awk -F":" '{print $2}'`

echo $line

#$ORACLE_HOME/bin/sqlplus -s / as sysdba  << EOF > ${TEMP_SPOOL}

$ORACLE_HOME/bin/sqlplus -s -M "HTML ON" "/ as sysdba"<<EOF >> ${SPOOL_FILE}

set space 0

set feedback off

set echo off

set serveroutput off

@'$BASE_DIR/datafile_monitoring.sql';

exit

EOF

done <${BASE_DIR}/NONPROD_DATABASE_LIST.txt

fline=`wc -l ${SPOOL_FILE}|awk '{print $1}'`

if [ $fline -gt 0 ]

then

${BASE_DIR}/mail.sh ${SPOOL_FILE} ${HOST}

fi

Testing data

1.CREATE TABLE HR.RTABLE ( RID NUMBER PRIMARY KEY, NOTES VARCHAR2(20));

2.create a procedure that will populate the table.

CREATE PROCEDURE HR.POPULATE_RTABLE ( N NUMBER ) 
IS 
/* this procedure inserts N records into RTABLE */ 
V INTEGER; 
BEGIN 
-- get the maximum ID from the table 
SELECT MAX(RID) INTO V FROM RTABLE; 
FOR I IN 1..N LOOP 
INSERT INTO RTABLE (RID, NOTES) VALUES(I, 'record ' || TO_CHAR(I)); 
-- end the transactions every 5 inserts 
IF MOD(I,5)=0 THEN 
COMMIT; 
END IF; 
END LOOP; 
COMMIT; 
END POPULATE_RTABLE; 
/  

13. Populate the testing table with a few thousands rows. 

SQL> exec HR.POPULATE_RTABLE ( 10000 ) 

SQL> SELECT COUNT(*) FROM HR.RTABLE ;

Oracle Golden Gate

 Golden gate daily hits

[oracle@ggsrv1 ogg]$ echo $GG_HOME

/u01/app/oracle/product/ogg

[oracle@ggsrv1 ogg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48

Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.

GGSCI (ggsrv1.localdomain) 1>

GGSCI (ggsrv1.localdomain) 1> h

GGSCI Command History

    1: h

GGSCI (ggsrv1.localdomain) 2> history

GGSCI Command History

    1: h

    2: history

GGSCI (ggsrv1.localdomain) 3>

GGSCI (ggsrv1.localdomain) 3> !2   --> this command will execute the history 

1. In source database (db1), verify that all the tables included in your replication have primary key constraints

SQL> select t.OWNER, t.TABLE_NAME from DBA_TABLES t where not exists

(select 1 from DBA_CONSTRAINTS c where c.OWNER = t.OWNER and c.TABLE_NAME = t.TABLE_NAME and c.CONSTRAINT_TYPE = 'P') and t.OWNER='HR';  2

no rows selected

B. Verify the archiving mode is enabled

Make sure the archiving mode is enabled in the databases involved in your GoldenGate configuration. If not, enable it.

2. In both databases, login as sys user and enable the archiving mode:

conn / as sysdba 

SELECT LOG_MODE FROM V$DATABASE ; 

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=BOTH; 

SHUTDOWN IMMEDIATE 

STARTUP MOUNT 

ALTER DATABASE ARCHIVELOG; 

ALTER DATABASE OPEN; 

ALTER SYSTEM SWITCH LOGFILE; 

SELECT NAME FROM V$ARCHIVED_LOG;

C.Set up a GoldenGate database user

In both the source and target databases, you need to create a database user that will be used by GoldenGate. In source database, the GoldenGate user reads the data and transactions made on the source tables. In the target database, GoldenGate user will apply the transactions data. Grant the required privileges to the GoldenGate user.

In each database, create a database user that will be used by Oracle GoldenGate

-- in DB1 -- create tablespace dedicated to OGG 

CREATE TABLESPACE OGG_TBS DATAFILE '/u01/app/oracle/oradata/DB1/oggtbs01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 

--create OGG user 

CREATE USER OGG IDENTIFIED BY oracle DEFAULT TABLESPACE OGG_TBS; 

-- in DB2 

-- create tablespace dedicated to OGG 

CREATE TABLESPACE OGG_TBS DATAFILE '/u01/app/oracle/oradata/DB2/oggtbs01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 

-- create OGG user 

CREATE USER OGG IDENTIFIED BY oracle DEFAULT TABLESPACE OGG_TBS;

4. Grant the required privileges to OGG user

-- (on both databases) grant privileges to OGG user 

EXEC dbms_goldengate_auth.grant_admin_privilege('OGG') ;

-- check out the privileges granted to OGG: 

SELECT PRIVILEGE FROM dba_sys_privs WHERE grantee='OGG' 

union all 

SELECT GRANTED_ROLE FROM dba_role_privs WHERE grantee='OGG' 

union all 

SELECT PRIVILEGE||' on '|| table_name FROM dba_tab_privs WHERE grantee='OGG';

256 rows selected.

-- (on both databases) grant further privileges to OGG:  (on both source and target databases)

GRANT CREATE SESSION TO OGG; 

GRANT ALTER SYSTEM TO OGG; 

GRANT RESOURCE TO OGG; 

GRANT CONNECT TO OGG; 

GRANT DBA to OGG; 

GRANT SELECT ANY DICTIONARY TO OGG; 

GRANT EXECUTE on DBMS_FLASHBACK TO OGG; 

GRANT LOCK ANY TABLE TO OGG;

# (in db1): 

GRANT SELECT ANY TRANSACTION TO OGG; 

# (in db2) only required by Replicat: 

GRANT INSERT ANY TABLE TO OGG; 

GRANT UPDATE ANY TABLE TO OGG; 

GRANT DELETE ANY TABLE TO OGG; 

GRANT CREATE TABLE TO OGG;

D. Enable supplemental logging

5. You must enable the minimal supplemental logging in the database. You also must enable the table-level or schema-level supplemental logging for the data to replicate.

# in both databases 

SELECT SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING FROM V$DATABASE; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

 ALTER DATABASE FORCE LOGGING; 

ALTER SYSTEM SWITCH LOGFILE;

6. (Optional) Open a monitoring session to monitor the ggserr.log file. Use this monitoring session to view how to GoldenGate responds to your commands in the background and for troubleshooting. 

tail -f $GG_HOME/ggserr.log

7. In both databases, enable GoldenGate replication 

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE scope=both;

8. In the source database (db1), 

enable schema-level supplemental logging for HR schema 

cd $GG_HOME 

ggsci 

ggsci> DBLogin UserID ogg, Password oracle 

ggsci> ADD SCHEMATRANDATA hr

E. Configure and start the Manager process

We have not created the Manager process during in the installer wizard and it is time to create it now. As with all GoldenGate processes, you first configure the parameter file then you start the process.

9. At the GGSCI prompt, enter the command as shown below.

This command creates a new mgr.prm file and starts the vi editor to allow you to edit the parameter file. If the file already exists, it will start the editor and opens the file for you. 

ggsci> edit Param mgr

10. Add the following runtime parameter to the file: 

Port 7809 

DynamicPortList 9500-9520 

PurgeOldExtracts ./dirdat/*, UseCheckpoints, MinKeepDays 5

11. Save and close the file.

12. Start the Manager process. The following command is equivalent to the command “start manager” ggsci> start mgr

13. View the status of the Manager process. 

ggsci> Info mgr

14. Repeat the same steps to create and start the Manager process in the target database system (ggsrv2). Put the following parameters in its parameter file:  In the target side, the port number will be different that is 7810

Port 7810 

PurgeOldExtracts ./dirdat/*, UseCheckpoints, MinKeepDays 5

F. Configure the GLOBALS parameter file

The parameters that you add to the GLOBALS file affect all the processes in an Oracle GoldenGate instance. In the following steps, you will create the file and add a parameter to it.

15. In the source system at the ggsci prompt, issue the following command to create the GLOBALS file (uppercase, without a file extension). 

ggsci> EDIT PARAMS ./GLOBALS

16. Add the parameter GGSCHEMA to it.

This parameter tells the GoldenGate which database schema it should use to store its objects. GGSCHEMA OGG

Repeat the same steps in the target system (ggsrv2).

======******=========*****========*****=========*******=====*****=====

GGSCI (ggsrv1.localdomain) 20> send extract ESRV1,status

ERROR: EXTRACT ESRV1 not currently running.

GGSCI (ggsrv1.localdomain) 21> send extract PSRV1, status

Sending STATUS request to EXTRACT PSRV1 ...

EXTRACT PSRV1 (PID 3112)

  Current status: Recovery complete: At EOF

  Current read position:

  Sequence #: 4

  RBA: 404850

  Timestamp: 2022-05-27 22:21:59.218609

  Extract Trail: ./dirdat/es

  Current write position:

  Sequence #: 7

  RBA: 882

  Timestamp: 2022-06-24 11:47:04.239881

  Extract Trail: ./dirdat/rt                                 ----> rt=remote trail

**Please check the environment set for the concerned database for which you want to enter into the GGSCI command prompt mode**

****executing command from history****

GGSCI (ggsrv2.localdomain) 21> h

GGSCI Command History

   12: edit params rsrv1

   13: edit params rsrv2

   14: start r*

   15: info all

   16: stop r*

   17: edit param rsrv2

   18: start r*

   19: info all

   20: stop r*

   21: h

GGSCI (ggsrv2.localdomain) 22> !17   --> here use !(cmd number from above list)

*******get param info *******

-- in ggsrv1:

send extract esrv1 getparaminfo

-- in ggsrv2:

send replicat rsrv2 getparaminfo

 

GGSCI (ggsrv1.localdomain as ogg@db1) 15> lag extract esrv1

 Sending GETLAG request to EXTRACT ESRV1 ...

Last record lag 2 seconds.

At EOF, no more records to process.

To verify that all the tables included in your replication have primary key constraints.

select t.OWNER, t.TABLE_NAME
from DBA_TABLES t
where not exists (select 1
from DBA_CONSTRAINTS c
where c.OWNER = t.OWNER and c.TABLE_NAME = t.TABLE_NAME
and c.CONSTRAINT_TYPE = 'P')
and t.OWNER='HR'; 

db_validation

 db_validation

set lines 500

col BYTES/1024/1024/1024 for 9999999999999999;

col AUTOEXTENSIBLE for a10;

col FILE_NAME for a60;

col MAXBYTES for 99999999999999999999;

select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 from dba_temp_files;

select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 from dba_data_files;

show parameter memory_max_target;

show parameter memory_target;

show parameter sga_max_size;

show parameter sga_target;

show parameter pga_aggregate_target;

show parameter optimizer_adaptive_features;

show parameter nls_length_semantics;

SELECT  1 FROM NLS_DATABASE_PARAMETERS DBPARMS, NLS_SESSION_PARAMETERS CURRENTPARMS WHERE

DBPARMS.PARAMETER = 'NLS_CHARACTERSET' AND CURRENTPARMS.PARAMETER = 'NLS_LENGTH_SEMANTICS' AND

((DBPARMS.VALUE IN ('AL32UTF8','UTF8') AND (CURRENTPARMS.VALUE = 'CHAR')) OR (DBPARMS.VALUE NOT

IN ('AL32UTF8','UTF8') AND (CURRENTPARMS.VALUE = 'BYTE')));

show parameter max_string_size;

show parameter db_recovery_file_dest_size;

show parameter Use_large_pages;

COL CLIENT_NAME FORMAT a31;

SELECT CLIENT_NAME, STATUS FROM   DBA_AUTOTASK_CLIENT WHERE  CLIENT_NAME = 'auto optimizer stats collection';

show parameter processes

show parameter open_cur

col PROPERTY_NAME for a30;

col PROPERTY_VALUE for a30;

col DESCRIPTION for a20;

show parameter db_block_size

select * from database_properties

where PROPERTY_NAME in ('NLS_CHARACTERSET',

'NLS_NCHAR_CHARACTERSET');

show parameter cpu;

show parameter resource_Manager_plan;

select flashback_on from v$database;

select force_logging from v$database;

select dbtimezone from dual;

col COMP_NAME for a40;

col status for a20;

select COMP_NAME,STATUS from dba_registry;

select * from all_db_links;

select * from dba_db_links;

TABLESPACE CREATION:

CREATE BIGFILE TABLESPACE TESTTBS DATAFILE '+DATAC1' SIZE 10M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;

CREATE BIGFILE UNDO TABLESPACE UNDOTBS3  DATAFILE '+DATAC1' SIZE 200M AUTOEXTEND ON NEXT 500M MAXSIZE 200G;

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS4 SCOPE=BOTH SID='dbname';

 

DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

srvctl setenv database -d CNPMWAFT -t 'TZ=America/Los_Angeles'

Changing dataguard mode

 Changing Dataguard mode from Maximum performance mode to maximum availability

alter system set log_archive_dest_2='SERVICE=oradb_s2 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) NET_TIMEOUT=30 REOPEN=50 DB_UNIQUE_NAME=oradb_s2';

SERVICE=oradb_s2 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb_s2

NET_TIMEOUT – Specifies the time in seconds that the primary database log writer will wait for a response from the Log Network Service (LNS) before terminating the connection and marking the standby (destination) as failed. The default value is 30 seconds.

REOPEN – Specifies the time in seconds that the log writer should wait before attempting to access a previously failed standby (destination). The default is 50 seconds.

Note : Shut down the primary database and restart it in mounted mode if the protection mode is being set to Maximum Protection or being changed from Maximum Performance to Maximum Availability. If the primary database is an Oracle Real Applications Cluster, shut down all of the instances and then start and mount a single instance.

Now, shutdown database :

STARTUP MOUNT

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

alter database open;

SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

alter system dump logfile '/u01/app/oracle/fra/ORADB_S2/onlinelog/o1_mf_5_ho2pm6m3_.log' validate;

PR00 (PID:16647): Media Recovery Log /u01/app/oracle/fra/ORADB_S2/archivelog/2022_01_27/o1_mf_1_124_jz4w8rdo_.arc

Errors with log /u01/app/oracle/fra/ORADB_S2/archivelog/2022_01_27/o1_mf_1_124_jz4w8rdo_.arc

PR00 (PID:16647): MRP0: Background Media Recovery terminated with error 328

2022-01-27T16:58:26.816034+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_16647.trc:

ORA-00328: archived log ends at change 3830620, need later change 4142985

ORA-00334: archived log: '/u01/app/oracle/fra/ORADB_S2/archivelog/2022_01_27/o1_mf_1_124_jz4w8rdo_.arc'

PR00 (PID:16647): Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

col name for a80

col thread# for 99

col sequence# for 9999

col archived for a5

col status for a10

select name, thread#, sequence#, archived, applied, status from v$archived_log where 4142985 between FIRST_CHANGE# and NEXT_CHANGE#;

select name, thread#, sequence#, archived, applied, status from v$archived_log where 3830620 between FIRST_CHANGE# and NEXT_CHANGE#;

The standby database is not-in-sync after converting maximum performance mode to maximum availability with the above error

Lets do roll forward for this , as we have no solution and will see the result..

what happens at DR side if space is exhausted during tablespace creation at primary side

oradb--> primary db

oradb_s2 -->Standby db

SQL> CREATE BIGFILE TABLESPACE TESTTBS2 DATAFILE '/backup/testtbs02.dbf' SIZE 200M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;

Tablespace created.

SQL> show parameter service

NAME                                 TYPE        VALUE

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

service_names                        string      oradb.localdomain     

The below is the alert log from DR side, when space is exhausted at DR side while tablespace creation at primary side. 

WARNING: File being created with same name as in Primary

Existing file may be overwritten

2022-01-07T22:09:20.122725+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_4229.trc:

ORA-27072: File I/O error

Additional information: 4

Additional information: 12288

Additional information: 237568

2022-01-07T22:09:20.179041+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_4229.trc:

ORA-19502: write error on file "/backup/testtbs02.dbf", block number 12288 (block size=8192)

ORA-27072: File I/O error

Additional information: 4

Additional information: 12288

Additional information: 237568

File #14 added to control file as 'UNNAMED00014'.

Originally created as:

'/backup/testtbs02.dbf'

Recovery was unable to create the file as:

'/backup/testtbs02.dbf'

PR00 (PID:4229): MRP0: Background Media Recovery terminated with error 1274

2022-01-07T22:09:20.343651+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_4229.trc:

ORA-01274: cannot add data file that was originally created as '/backup/testtbs02.dbf'

PR00 (PID:4229): Managed Standby Recovery not using Real Time Apply

2022-01-07T22:09:22.638703+04:00

Recovery interrupted!

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal

Recovered data files to a consistent state at change 3262529

stopping change tracking

2022-01-07T22:09:23.132280+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_4229.trc:

ORA-01274: cannot add data file that was originally created as '/backup/testtbs02.dbf'

2022-01-07T22:09:23.184841+04:00

Background Media Recovery process shutdown (oradb_s2)

Again if we forcefully starting the MRP, it is terminating , below is the log from alert log.

alter database recover managed standby database disconnect

2022-01-07T22:17:03.094994+04:00

Attempt to start background Managed Standby Recovery process (oradb_s2)

Starting background process MRP0

2022-01-07T22:17:03.116141+04:00

MRP0 started with pid=60, OS id=5031

2022-01-07T22:17:03.118382+04:00

Background Managed Standby Recovery process started (oradb_s2)

2022-01-07T22:17:08.146408+04:00

 Started logmerger process

2022-01-07T22:17:08.164904+04:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal

PR00 (PID:5037): Managed Standby Recovery starting Real Time Apply

2022-01-07T22:17:08.216985+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_dbw0_1947.trc:

ORA-01186: file 14 failed verification tests

ORA-01157: cannot identify/lock data file 14 - see DBWR trace file

ORA-01111: name for data file 14 is unknown - rename to correct file

ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'

2022-01-07T22:17:08.217231+04:00

File 14 not verified due to error ORA-01157

2022-01-07T22:17:08.218311+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_dbw0_1947.trc:

ORA-01157: cannot identify/lock data file 203 - see DBWR trace file

ORA-01110: data file 203: '/u01/app/oracle/oradata/ORADB/pdb1/temp01.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

2022-01-07T22:17:08.431502+04:00

max_pdb is 3

PR00 (PID:5037): MRP0: Background Media Recovery terminated with error 1111

2022-01-07T22:17:08.462884+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_5037.trc:

ORA-01111: name for data file 14 is unknown - rename to correct file

ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'

ORA-01157: cannot identify/lock data file 14 - see DBWR trace file

ORA-01111: name for data file 14 is unknown - rename to correct file

ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'

PR00 (PID:5037): Managed Standby Recovery not using Real Time Apply

stopping change tracking

2022-01-07T22:17:08.626152+04:00

Recovery Slave PR00 previously exited with exception 1111

2022-01-07T22:17:08.674098+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_mrp0_5031.trc:

ORA-01111: name for data file 14 is unknown - rename to correct file

ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'

ORA-01157: cannot identify/lock data file 14 - see DBWR trace file

ORA-01111: name for data file 14 is unknown - rename to correct file

ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'

2022-01-07T22:17:08.674347+04:00

Background Media Recovery process shutdown (oradb_s2)

2022-01-07T22:17:09.182195+04:00

Completed: alter database recover managed standby database disconnect