Tuesday, September 6, 2022

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>

Thursday, August 25, 2022

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

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

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}

Monday, August 22, 2022

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 ;

Friday, March 11, 2022

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.

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';

Wednesday, February 9, 2022

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'

Thursday, January 27, 2022

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..

Friday, January 7, 2022

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

DATAGUARD

select name,db_unique_name,open_mode,log_mode,database_role from v$database;

alter database recover managed standby database disconnect;

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.THREAD#;

select PROCESS, PID, STATUS, THREAD#, SEQUENCE# from gv$managed_standby;

select PROCESS, PID, STATUS, THREAD#, SEQUENCE# from gv$managed_standby where STATUS='IDLE';

select PROCESS, PID, STATUS, THREAD#, SEQUENCE# from gv$managed_standby where PROCESS='MRP0';

set lines 555

col DEST_NAME for a30

col DESTINATION for a30

col ERROR for a30

select INST_ID,DEST_ID,DEST_NAME,DESTINATION,ERROR,STATUS from gv$archive_dest where DESTINATION IS NOT NULL;

RMAN

RMAN:

=========

RMAN Retention Policy

The RMAN retention policy determines how long backups are kept for and how many copies are retained.

The retention policy can be defined in terms of:

REDUNDANCY - number of copies to retain
RECOVERY WINDOW - number of days to retain backups

The retention policy is configured by the RETENTION POLICY parameter. The default value is

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

To specify a recovery window of 1 day use:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;

Backup files can be marked as expired or obsolete.

Expired - RMAN has performed a crosscheck and the file cannot be found
Obsolete - based on the retention policy the file is not needed for recovery

The backup retention policy only applies to full or level 0 datafile and control file backups.

The retention policy does not directly affect archived redo logs and incremental level 1 backups. These files become obsolete when no full backups exist that need them.

Datafile backup sets cannot be deleted until all datafile backups within the backup set are obsolete.

Obsolete backups can be identified using the REPORT OBSOLETE command and deleted using the DELETE OBSOLETE command.

The retention policy can also be set to NONE

RMAN> CONFIGURE RETENTION POLICY TO NONE;

To revert to the default value use:

RMAN> CONFIGURE RETENTION POLICY CLEAR;

RMAN> crosscheck backup;

using channel ORA_DISK_1