Delete Archivelog until time

Oracle database is archiving redo log group files if database is in archivelog mode.

 

When redo log files are archived, then redo log files are overwritten with new redo data. But archivelogs are not deleted or automatically if you don’t schedule any deletion job.

You need to delete archivelog periodically, otherwise you will get archiver errors.

Delete Archivelog via RMAN

You can delete all archivelogs with following query.

RMAN>  delete archivelog all;

Delete Archivelog Until Time Oracle
You can delete archivelogs until spesific time ( until time 1 day ago ) like following query.
RMAN>  delete archivelog until time 'sysdate -1';
You can delete archivelog which is backed up 1 times to Disk.
RMAN>   delete archivelog all backed up 1 times to DISK;

OEL 8 VNC server issue:

Error:

The XKEYBOARD keymap compiler (xkbcomp) reports:

> Warning:          Could not resolve keysym XF86MonBrightnessCycle

> Warning:          Could not resolve keysym XF86RotationLockToggle

Errors from xkbcomp are not fatal to the X server

Fix:

 # dnf install tigervnc-server tigervnc-server-module -y

# cp /usr/lib/systemd/system/vncserver@.service /etc/systemd/system/vncserver@:16.service
# firewall-cmd --add-port=5916/tcp --permanent
# firewall-cmd --add-service=vnc-server --permanent
# firewall-cmd --reload
# vi /etc/tigervnc/vncserver.users
:16=user

# vi /etc/tigervnc/vncserver-config-defaults
securitytypes=vncauth,tlsvnc
desktop=sandbox
geometry=2000x1200
localhost
alwaysshared

# \su - user
user$ vncpasswd
user$ exit
# systemctl daemon-reload
# systemctl start vncserver@:16.service
# systemctl enable vncserver@:16.service
# systemctl status vncserver@:16.service

● vncserver@:16.service - Remote desktop service (VNC)
   Loaded: loaded (/etc/systemd/system/vncserver@:16.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Tue 2020-12-15 12:49:05 PST; 18min ago
 Main PID: 50980 (code=exited, status=0/SUCCESS)

Dec 15 12:49:04 xxxx.xxxx.xxx systemd[1]: Starting Remote desktop service (VNC)...
Dec 15 12:49:04 xxxx.xxxx.xxx systemd[1]: Started Remote desktop service (VNC).
Dec 15 12:49:05 xxxx.xxxx.xxx systemd[1]: vncserver@:16.service: Succeeded.

 How to move data files to other mounts ??

There are multiple ways.. here are the ones which I followed for PROD :)

1. Shut down your DB

2. At OS level move your datafiles/Tempfiles/Undo or any dbf files to your destination location i.e /u02

    mv /u01/system10.dbf  /u02/system10.dbf

3. Now start the db in mount so that it will update your control files

   startup mount

4. Now use RENAME command as below:

alter database rename file '/u01/system10.dbf' to '/u02/system10.dbf'

..--> Do the same for all required files -->

5. Alter database open

Now for validation , you can check 

select name from v$datafile where name like '%u02%'; - should point out new locations for datafiles

2nd Method:

From 12C onwards, there is an option to move datafile online 

1. Simply use below command to move online datafile from /u01 to /u02

set echo on

set timing on

ALTER DATABASE MOVE DATAFILE '/U05/applfarm/farmdata/a_txn_ind12.dbf' to '/u01/a_txn_ind12.dbf';

ALTER DATABASE MOVE DATAFILE '/U05/applfarm/farmdata/a_txn_ind14.dbf' to '/u02/a_txn_ind14.dbf';

NOTE:  Problem with online datafile move is .. It will not RELEASE space at OS level immediately

as some of your BG process are still accessing the space. After restart space gets released

SQL to keep Concurrent requests on Hold

 

1)Create table apps.conc_req_on_hold_DDMMYY as select fcr.*, fcrv.user_concurrent_program_name from fnd_Concurrent_requests fcr, fnd_concurrent_programs_VL fcrv where fcr.PHASE_CODE='P' and fcr.hold_flag='N' and fcr.requested_start_date > sysdate and fcr.CONCURRENT_PROGRAM_ID=fcrv.CONCURRENT_PROGRAM_ID;

 

2) select count(*) from apps.conc_req_on_hold_DDMMYY

 

3) update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and requested_start_date > sysdate and request_id in (select request_id from  apps.conc_req_on_hold_DDMMYY);

 

NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same

 

4) Commit;

 

To Release hold on Concurrent Requests after the maintenance window, run the below sql :

 

5) update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.conc_req_on_hold_DDMMYY);

 

6)Commit the changes

 

commit;

DR Maintenance Steps sequence

Pre-steps in all db and apps servers

=======================

Take Backup of below things

1. df -hP o/p of all app and db servers

2. cp -rp /etc/resolv.conf      /etc/resolv.conf_bkp

3.

service iptables status

service ip6tables status

4.    comment cron if needed

5. date

DR STOP pre-steps:

=================

1. select max( sequence#)  from v$archived_log where applied='YES'; -- From primary

2. select max( sequence#)  from v$archived_log where applied='YES'; -- From DR

Make sure both match same number 

3. Take o/of below from DR

ps -ef| grep tns

sho parameter db_name, db_unique, local, archive log list

4. Take backup of parameter file

show parameter pfile, spfile

create pfile='?dbs/init_pfile.ora_bkp' from spfile;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER scope=both; -   from PRIMARY

On Standby:

No RFS Process showing when query below:

SELECT PROCESS,  STATUS, SEQUENCE#  FROM V$MANAGED_STANDBY;

5. Stop listener On standby server:

ps -ef| grep tns

lsnrctl stop listername

6. Cancel Managed Recovery from standbyb

ps -ef| grep mrp

SQL> alter database recover managed standby database cancel;

verify its not running -  ps -ef| grep mrp

7. Comment cron in DB -- Take backup before - Standby only

8. - Shutdown the Standby Database

connect to Primary - check status

STANDBY -   SQL> shutdown immediate

ps -ef| grep mrp

ps -ef| gep tns

DR start:

======

In  all app server:

Validate the mount points as below.

Restore the /etc/resolv.conf file from resolv.conf_25aug20 backup.

          cp -p /etc/resolv.conf_25aug20 /etc/resolv.conf

Disable firewall running on all 3 servers as per below command:

         service iptables save

         service iptables stop

         service ip6tables stop

         chkconfig iptables off

 IN DR:

1. Uncomment Cron

2. Start db 

SQL> startup mount;

SQL> alter database recover managed standby database disconnect from session;

3. Start Database Listener

    lsnrct start listemer - STANDBY

4. Set log transport state to ENABLE status:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

5. select open_mode, database_role, name from v$database;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

6. SELECT PROCESS,  STATUS, SEQUENCE#  FROM V$MANAGED_STANDBY;

7. @gap

8. select max( sequence#)  from v$archived_log where applied='YES'; -- From primary

 select max( sequence#)  from v$archived_log where applied='YES'; -- From DR

9. archive log list, check db_name. unique, spfile, alert logs

10. dgmgrl / , show configuration, -- primary side

show database verbose 'db_name_primary', 

show database verbose 'db_name_DR'

Deleting an Application Tier Nodes in Multi Tier environment EBS 12.2

 

Deleting an Application Tier Nodes in Multi Tier environment EBS 12.2

There are some cases where we do wrong configuration of system then we need to cleanup that node and then add it back / if nodes have been abandoned then also we need to follow delete and add and there are many cases where we will be using simple script for deletion of the node.

If Host is accessible :

$ perl $AD_TOP/patch/115/bin/adProvisionEBS.pl ebs-delete-node -contextfile=$CONTEXT_FILE

If host is not accessible then run below from other node.

$ perl $AD_TOP/patch/115/bin/adProvisionEBS.pl ebs-delete-node -contextfile=$CONTEXT_FILE -hostname=<Host2bdeleted>

Things it will take care :

1. It checks the admin server in both RUN & PATCH file system

2. Cleaning up database topology information for node Host mentioned in both RUN & Patch File system.

From AD_NODES_CONFIG_STATUS 1 row deleted successfully.

From fnd_oam_context_files 7 row deleted successfully.

From FND_NODES 1 row deleted successfully.

From ADOP_VALID_NODES 1 row deleted successfully.

Deleting RUN file system configuration information.

Server oacore_server4 deleted successfully.

Server forms_server4 deleted successfully.

Server oafm_server4 deleted successfully.

Server forms-c4ws_server4 deleted successfully.

3. Once all above done it will cleanup OHS

OHS instance EBS_web_<Instance>_Instance_Num unregistered successfully.

 

 

How to get explain plan of sql_id and ROW chaining from cursor

First of all, we need to get   child number of the sql_id.

There can be many child number but only have one plan_hash_value.

SELECT sql_id, child_number, plan_hash_value

FROM gv$sql

WHERE sql_id = '98txwedq81qy3';

 

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE

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

98txwedq81qy3 1 233137988

 

We can get explain plan using by below

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('&sqlid', &child_number));

Once you get based on that we can see which table and Index  is using more time based on elapsed time and verify row chaining and leaf index block count

* 10 |      TABLE ACCESS BY INDEX ROWID| GL_CODE_COMBINATIONS       |     1 |    78 |     3   (0)| 00:00:01 |

|* 11 |       INDEX UNIQUE SCAN         | GL_CODE_COMBINATIONS_U1    |     1 |       |     2   (0)| 00:00:01 |

|  12 |      TABLE ACCESS BY INDEX ROWID| GL_CONS_FLEXFIELD_MAP      |   461 | 51171 |   143K  (1)| 00:28:44 |

|* 13 |       INDEX RANGE SCAN          | GL_CONS_FLEXFIELD_MAP_CST1 |   461 |       |   143K  (1)| 00:28:39 |

SQL> SELECT TABLE_NAME, CHAIN_CNT FROM DBA_TABLES WHERE TABLE_NAME='GL_CONS_FLEXFIELD_MAP';

TABLE_NAME                      CHAIN_CNT

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

GL_CONS_FLEXFIELD_MAP                   0

SQL>  SELECT TABLE_NAME, INDEX_NAME, BLEVEL, LEAF_BLOCKS FROM DBA_INDEXES WHERE INDEX_NAME ='GL_CONS_FLEXFIELD_MAP_CST1';

TABLE_NAME                     INDEX_NAME                         BLEVEL LEAF_BLOCKS

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

GL_CONS_FLEXFIELD_MAP          GL_CONS_FLEXFIELD_MAP_CST1              3      285837