How To Check if Oracle Gateway for MSSQL is Installed

How To Check if Oracle Gateway for MSSQL is Installed:

If the following command returns a list of files, then Oracle Gateway for MSSQL is installed:

# ls -l $ORACLE_HOME/dg4msql

If Oracle Gateway for MSSQL is installed, the following command will show you the Oracle Gateway version details:

# $ORACLE_HOME/bin/dg4msql 

To check if Oracle Gateway is configured on the system, search for "dg4msql" in the listener status output: [Provide the listener name if you are using a non-default listener name]

# lsnrctl status

 

Reference:

https://docs.oracle.com/database/121/OTGIS/configsql.htm#OTGIS900

DBA Diary

 During your DBA career lifetime, how many times you faced a problem on a database and you tried to find out the recent implemented changes on this DB?

 It is crucial for DBAs --whether they work separately or in a team to track their own technical activities in one journal; this not only sounds professional, but also critical to keep a log of all DB changes and maintenance activities.

This template can help you track your technical activities in a simple and quick way:

https://www.dropbox.com/scl/fi/gerhknfx7zlmwswmf1bt8/Databases-Journal.xlsx?dl=0&rlkey=vz0j1uvq87y3l01qesy3x55az

Example of how I use it:

All you want to do is to customize this template to be compatible with your database environments; you need to fill these sections at the bottom of the sheet with databases and DBA names:

Under "Change Type list" I've listed the most common DBA activities, feel free to amend or add more activities.

Cleanup Logs on Oracle RAC 12c+

 The following technical steps illustrate the cleanup of Oracle logs on Oracle Cluster system (Grid Infrastructure 12c and above).

Before actioning these steps, make sure that logs are no more required for troubleshooting errors neither required for auditing purposes.

Cleanup Database related logs:

The following commands should be executed against each database installed on the host replacing "orcl" with the actual database/instance name, and "racnode1" with the actual hostname.

Cleaning up database related logs can be done by executing the following command from ADRCI:

# adrci

adrci> show home
ADR Homes:
diag/rdbms/orcl/orcl1
diag/asm/+asm/+ASM1
diag/crs/racnode1/crs
diag/tnslsnr/racnode1/asmnet1lsnr_asm
diag/tnslsnr/racnode1/listener_scan1
diag/tnslsnr/racnode1/listener
diag/tnslsnr/racnode1/listener_scan3
diag/tnslsnr/racnode1/listener_scan2

adrci> set home diag/rdbms/orcl/orcl1
adrci> purge -age 0 -type ALERT
adrci> purge -age 0 -type INCIDENT
adrci> purge -age 0 -type TRACE
adrci> purge -age 0 -type LOG
adrci> purge -age 0 -type CDUMP
adrci> purge -age 0 -type UTSCDMP
adrci> purge -age 0 -type HM

Note: -age 0 means; remove all logs except the ones created in the recent hour, you can replace 0 with the number of hours back you want to keep logs.

This will cleanup the logs under the following directories:

SELECT name, value FROM v$diag_info where VALUE like '%rdbms%';

NAME                  VALUE
--------------------- -------------------------------------------
ADR Home              /u01/oracle/diag/rdbms/orcl/orcl1
Diag Trace              /u01/oracle/diag/rdbms/orcl/orcl1/trace
Diag Alert               /u01/oracle/diag/rdbms/orcl/orcl1/alert
Diag Incident         /u01/oracle/diag/rdbms/orcl/orcl1/incident
Diag Cdump           /u01/oracle/diag/rdbms/orcl/orcl1/cdump
Health Monitor      /u01/oracle/diag/rdbms/orcl/orcl1/hm
Default Trace File  /u01/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_31270.trc

Or you can manually remove the logs as follows:

# cd /u01/oracle/diag/rdbms/orcl/orcl1/alert; du -sh .
# rm -f log_*.xml; ls -ltrh

# cd /u01/oracle/diag/rdbms/orcl/orcl1/trace; du -sh .
# rm -f *.trm *.trc; ls -ltrh

# cd /u01/oracle/diag/rdbms/orcl/orcl1/cdump; du -sh .
# rm -rf *; ls -ltrh

# cd /u01/oracle/diag/rdbms/orcl/orcl1/incident; du -sh .
# rm -rf cdmp_*; rm -rf incdir_*; du -sh .

For audit logs, you have to delete them manually, but you have to perform the following workaround to avoid slowing down the DB during this activity:

# cd /u01/oracle/admin/orcl;ls -rtlh; du -sh *
# mv adump adump_old; mkdir adump; chmod o-rwx adump; ls -rtlh
# rm -rf adump_old

 

Cleanup ASM related logs:

Using ADRCI: 

adrci> set home diag/asm/+asm/+ASM1
adrci> purge -age 0 -type ALERT
adrci> purge -age 0 -type INCIDENT
adrci> purge -age 0 -type TRACE

For ASM audit logs, you have to delete them manually, using the same technique we used for cleaning up database audit logs to avoid performance degradation:

# cd /u01/grid/12.2.0.3/rdbms/audit; ls -rtlh; du -sh *
# mv adump adump_old; mkdir adump; chmod o-rwx adump; ls -rtlh
# rm -rf adump_old

 

Cleanup Grid Infrastructure related logs:

First, start with cleaning up listeners' logs

Using ADRCI:

adrci> show home
ADR Homes:
diag/rdbms/orcl/orcl1
diag/asm/+asm/+ASM1
diag/crs/racnode1/crs
diag/tnslsnr/racnode1/asmnet1lsnr_asm
diag/tnslsnr/racnode1/listener_scan1
diag/tnslsnr/racnode1/listener
diag/tnslsnr/racnode1/listener_scan3
diag/tnslsnr/racnode1/listener_scan2

adrci> set home diag/tnslsnr/racnode1/asmnet1lsnr_asm
adrci> purge -age 0 -type ALERT
adrci> purge -age 0 -type INCIDENT
adrci> purge -age 0 -type TRACE

adrci> set home diag/tnslsnr/racnode1/listener
adrci> purge -age 0 -type ALERT
adrci> purge -age 0 -type INCIDENT
adrci> purge -age 0 -type TRACE

adrci> set home diag/tnslsnr/racnode1/listener_scan1
adrci> purge -age 0 -type ALERT
adrci> purge -age 0 -type INCIDENT
adrci> purge -age 0 -type TRACE

adrci> set home diag/tnslsnr/racnode1/listener_scan2
adrci> purge -age 0 -type ALERT
adrci> purge -age 0 -type INCIDENT
adrci> purge -age 0 -type TRACE

adrci> set home diag/tnslsnr/racnode1/listener_scan3
adrci> purge -age 0 -type ALERT
adrci> purge -age 0 -type INCIDENT
adrci> purge -age 0 -type TRACE

Or instead of using ADRCI, you can cleanup the logs manually as follows:

# cd /u01/oracle/diag/tnslsnr/racnode1/listener/alert; ls -rtlh; du -sh .
# rm -f log_*.xml; ls -ltrh

# cd /u01/oracle/diag/tnslsnr/racnode1/listener/trace; ls -rtlh; du -sh .
# rm -f listener_*; ls -ltrh

# cd /u01/oracle/diag/tnslsnr/racnode1/listener_scan1/alert; ls -rtlh; du -sh .
# rm -f log_*.xml; ls -ltrh

# cd /u01/oracle/diag/tnslsnr/racnode1/listener_scan1/trace; ls -rtlh; du -sh .
# rm -f  listener_scan1_*.log; ls -ltrh

# cd /u01/oracle/diag/tnslsnr/racnode1/listener_scan2/alert; ls -rtlh; du -sh .
# rm -f log_*.xml; ls -ltrh

# cd /u01/oracle/diag/tnslsnr/racnode1/listener_scan2/trace; ls -rtlh; du -sh .
# rm -f  listener_scan2_*.log; ls -ltrh

# cd /u01/oracle/diag/tnslsnr/racnode1/listener_scan3/alert; ls -rtlh; du -sh .
# rm -f log_*.xml; ls -ltrh

# cd /u01/oracle/diag/tnslsnr/racnode1/listener_scan3/trace; ls -rtlh; du -sh .
# rm -f  listener_scan2_*.log; ls -ltrh
 

Second, cleaning up clusterware logs:

Using ADRCI:

adrci> set home diag/crs/racnode1/crs

adrci> purge -age 0 -type ALERT
adrci> purge -age 0 -type INCIDENT
adrci> purge -age 0 -type TRACE
adrci> purge -age 0 -type LOG
adrci> purge -age 0 -type CDUMP

Or manually cleanup the clusterware logs:

# cd /u01/oracle/diag/crs/racnode1/crs/alert; du -sh .
# rm -f log_*.xml; ls -ltrh

# cd /u01/oracle/diag/crs/racnode1/crs/trace; du -sh .
# rm -f *.trm *.trc; ls -ltrh

# cd /u01/oracle/diag/crs/racnode1/crs/cdump; du -sh .
# rm -rf *; ls -ltrh

# cd /u01/oracle/crsdata/racnode1/trace/chad; du -sh .
# rm -f ochad.trc.{1..9}; ls -ltrh

Follow the same steps on the other RAC nodes.

Tools to help you out:

Cleanup Oracle logs script

Maximum Availability Architecture For New Databases

Just thought to share the major points of the Maximum Availability Architecture I'm following. It will be difficult to provide references as I've gathered/developed these points throughout a decade!

I'm sharing it with the hope it will be helpful for you without any warranty.

DBA Bundle 5.4 Is Ready

DBA Bundle V5.4 is now available in this link:
https://www.dropbox.com/s/k96rl0f4g39ukih/DBA_BUNDLE5.tar?dl=0

The following are the new features:
- Excluding +APX DB (>=12.2) from the DB list when bundle scripts get invoked.
- Defaulting RMAN_full.sh & schedule_rman_full_bkp.sh scripts to create Full backup instead of Incremental 0 to avoid interfering with any existing backup policies.
- Added Hard parsing command post to the completion of tuning task in sql_id_details.sh.
- All DB users DDL generated script out of configuration_baseline.sh can be executed directly from SQLPLUS when needed.

- Bug fixes applied to the following scripts:
Table rebuild online script: rebuild_table.sh
Aliases setup script: aliases_DBA_BUNDLE.sh
Database/Server Monitoring script: dbalarm.sh
Health Check Report script: dbdailychk.sh
Checking Active Sessions script: active_sessions.sh

If you are new to the DBA BUNDLE please read the full post:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html

Datafile Rename with minimal Downtime using RMAN

Case:
Let's suppose you created a new datafile and missed the .dbf extension or had a typo in the name but you want to fix it.

Let's suppose the bad datafile name is /data01/STFBKUAT01/datafile/users012 and it should be renamed to /data01/STFBKUAT01/datafile/users02.dbf

Prerequisite: - The database should be in ARCHIVELOG mode.
                     - It's always recommended to take a full backup before and after changing the
                       database physical structure.

Solution:
If you are on 12c, lucky you are, you can do the job with a single command with NO DOWNTIME but with more overhead:

SQL> alter database move datafile '/data01/STFBKUAT01/datafile/users012' to '/data01/STFBKUAT01/datafile/users02.dbf';

Database altered.

This command will copy the datafile using the new name, then will switch to the new datafile, then will delete the old datafile with the old name. as it's engaging a physical copy of the datafile this will generate an overhead based on the datafile size and system speed.

If you are on 11g backward you can rename the datafile with minimal downtime, but with less overhead compared to 12c method:

-- Bring the datafile back offline: [Downtime start]
SQL> alter database datafile '/data01/STFBKUAT01/datafile/users012' offline;

Database altered.

-- Rename the datafile on the OS side:
SQL> host mv /data01/STFBKUAT01/datafile/users012  /data01/STFBKUAT01/datafile/users02.dbf

-- Rename the datafile on the DB side:
SQL> alter database rename file '/data01/STFBKUAT01/datafile/users012' to '/data01/STFBKUAT01/datafile/users02.dbf';

Database altered.

-- Recover the datafile:
SQL> recover datafile '/data01/STFBKUAT01/datafile/users02.dbf';
Media recovery complete.

-- Bring the datafile back online: [Downtime end]
SQL> alter database datafile '/data01/STFBKUAT01/datafile/users02.dbf' online;

Database altered.

Drop Oracle Database

There are two ways to drop an Oracle database:

Before starting:
1) It's always wise to take a Full backup of the database before dropping it.
2) If the database is already registered with Grid/Cloud Control, un-register it:
Connect to Grid Control with an admin user (e.g. SYSMAN)
Targets -> Databases -> Select DB_NAME "ORCL" -> Remove

First and best option: Using DBCA:

Why DBCA is the best? Because it will clean up all the database traces properly from the system (datafiles, controlfiles, redologs, archivelogs, trace files and log files) using one command. Whether the database is a Standalone, Oracle Restart or RAC setup:

How:
# dbca -silent -deleteDatabase -sourceDB orcl -forceArchiveLogDeletion -sysDBAPassword abc123 -sysDBAUserName sys

-silent                                        Run dbca in command line mode
-sourceDB                                 Provide the DB_NAME you want to drop (i.e. orcl)
-forceArchiveLogDeletion        Delete archivelogs 
-sysDBAPassword                    Provide SYS user password (i.e. abc123)
-sysDBAUserName                  Provide a user with SYSDBA privilege (i.e. sys)


Second Option: Using RMAN:

Case1: Dropping a STANDALONE DB:

Restart the DB in "RESTRICT EXCLUSIVE" mode:

[By oracle]
# export ORACLE_SID=orcl
# sqlplus "/ as sysdba"
SQL> shu immediate;
SQL> startup mount exclusive restrict;
SQL> select INSTANCE_NAME,HOST_NAME from v$instance;

Drop the database from RMAN:
# export ORACLE_SID=orcl
# rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 10 10:19:48 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1220202490, not open)

RMAN> DROP DATABASE INCLUDING BACKUPS;
...
database name is "ORCL" and DBID is 1220202490
database dropped
RMAN> exit

Note: INCLUDING BACKUPS option will delete RMAN Backups/Datafiles & Controlfiles copies along with the Database files.

Remove the DB from ORACLE RESTART repository:
[By oracle]
# srvctl remove database -d orcl 

Delete the database entry from /etc/oratab: [both Nodes]

# vi /etc/oratab
[Remove the similar line]
orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N

[Optional] Delete the instance files from $ORACLE_HOME/dbs:

# cd $ORACLE_HOME/dbs
# ls -l *orcl*

[Optional] Remove database diag directory from $ORACLE_BASE/diag/rdbms:

# adrci
ADR base = "/u01/app/oracle"

adrci> show homes rdbms
ADR Homes:
diag/rdbms/orcl/orcl

adrci> exit

# cd /u01/app/oracle/diag/rdbms
# rm -rf orcl

Case2: Dropping a RAC DB:

Set the cluster_database parameter to false:
SQL> alter system set cluster_database=false scope=spfile;

Shutdown the database on ALL RAC nodes:
# srvctl stop database -d orcl

Startup the DB in "RESTRICT" "EXCLUSIVE" mode: from only one node:

[By oracle]
# export ORACLE_SID=orcl1
# sqlplus "/ as sysdba"
SQL> startup mount exclusive restrict;
SQL> select INSTANCE_NAME,HOST_NAME from v$instance;

Drop the database:
-----------------
# export ORACLE_SID=orcl1
# rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 10 10:17:42 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1675848900, not open)

RMAN> DROP DATABASE INCLUDING BACKUPS;
...
database name is "ORCL" and DBID is 1675848900
database dropped
RMAN> exit

Remove the database from the Clusterware configurations:
[By oracle]
# srvctl remove database -d orcl

Delete the database & instance entry from /etc/oratab: [Both RAC nodes]
# vi /etc/oratab
orcl1:/u01/app/oracle/product/11.2.0/db_1:N # line added by Agent
orcl:/u01/app/oracle/product/11.2.0/db_1:N # line added by Agent

[Optional] Delete instance files from $ORACLE_HOME/dbs: [Both RAC nodes]
# cd $ORACLE_HOME/dbs
# ls -l *orcl*

[Optional] Remove the database diag directory from $ORACLE_BASE/diag/rdbms:  [Both RAC nodes]

# adrci
ADR base = "/u01/app/oracle"

adrci> show homes rdbms
ADR Homes:
diag/rdbms/orcl/orcl1
diag/rdbms/orcl/orcl
diag/rdbms/test/test
diag/rdbms/test/test1
diag/rdbms/_mgmtdb/-MGMTDB

adrci> exit

# cd /u01/app/oracle/diag/rdbms

# rm -rf orcl1

# rm -rf orcl

DBA Bundle 5.3 Is Ready

DBA Bundle V5.3 is now available for download:
https://www.dropbox.com/s/k96rl0f4g39ukih/DBA_BUNDLE5.tar?dl=0

It comes with the following features:

- active_sessions.sh script that shows current active sessions on the DB will display the OS commands to PAUSE/RESUME/KILL a current running RMAN backup.

- Bug fixes applied to the following scripts:
RMAN_full.sh
dbdailychk.sh
dbalarm.sh
active_sessions.sh
aliases_DBA_BUNDLE.sh

If you are new to the DBA BUNDLE follow this link to get the basic idea of it:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html

RMAN-05502: the target database must be mounted when issuing a DUPLICATE command

Problem:

While restoring a Non-Production DB from RMAN backup using Duplicate method , I received this error:
RMAN-05502: the target database must be mounted when issuing a DUPLICATE command

Here is the RMAN script I was using:

# export ORACLE_SID=pssp

# rman AUXILIARY /

RMAN> 

run {

ALLOCATE AUXILIARY CHANNEL ch1  DEVICE TYPE DISK;

ALLOCATE AUXILIARY CHANNEL ch2  DEVICE TYPE DISK;

duplicate database to "pssp"  backup location '/db02/bkp/2019-06-02' nofilenamecheck

UNTIL TIME "TO_DATE('01/06/2019 13:05:17', 'DD/MM/YYYY HH24:MI:SS')";

}

This duplicate method is supposed to work while the instance "to be restored" is in NOMOUNT mode, so why the error message is asking to mount it?!

I used to execute the duplicate command one time with a random "UNTIL TIME" date ahead of the backup date, then the RMAN will give me an error message referring to the actual farthest recovery date the backup can restore to, then I feed that date into the "UNTIL TIME" clause when I run the same script second time.

Solution:

If you are performing the Duplicate from a backup:

First: 90% of the cases, your rman command is being aliased in the shell environment to connect to "target /", so it will not connect as "auxiliary /" as you want:

# which rman

 

 

 

 

 If this is the case, then remove that alias and run the rman script again and most probably it will succeed this time:

# unalias rman 

 

Second: You are trying to connect target and auxiliary, while it is not necessary for cloning from backup to connect as target:
i.e.
Command returning RMAN-05502:
# rman target / auxiliary /@TARGETDB

Try using:
# export ORACLE_SID=TARGETDB
# rman auxiliary /

Third: You are trying unnecessarily to provide username and password to the auxiliary clause, while you can simply "connect auxiliary /"
i.e.
Command returning RMAN-05502:
# rman auxiliary sys/passw@TARGETDB

Try using:
# export ORACLE_SID=TARGETDB
# rman auxiliary /

Forth: Simply try to remove the UNTIL TIME clause  from the script if you want to recover the database to the latest archivelog available in the backup.

 

Or: If you insist on restoring the DB to a specific point in time inside that backup, then you have to know that the date specified in the UNTIL TIME clause is trying to restore the backup to a date older than the backup creation date, this is why I received that vague error message. Once I used a date ahead of the backup time (randomly) I got the right farthest recovery date the backup can go to:

run {

ALLOCATE AUXILIARY CHANNEL ch1  DEVICE TYPE DISK;

ALLOCATE AUXILIARY CHANNEL ch2  DEVICE TYPE DISK;

duplicate database to "pssp"  backup location '/db02/bkp/2019-06-02' nofilenamecheck

UNTIL TIME "TO_DATE('20/06/2019 13:05:17', 'DD/MM/YYYY HH24:MI:SS')";

}

RMAN-06617: UNTIL TIME (20-Jun-2019 13:05:17) is ahead of last NEXT TIME in archived logs (02-Jun-2019 17:30:19)

Then I restarted the instance in NOMOUNT mode one more time and used the exact "last NEXT TIME in archived logs" received from the above error message and the duplicate succeeded.

 run {

ALLOCATE AUXILIARY CHANNEL ch1  DEVICE TYPE DISK;

ALLOCATE AUXILIARY CHANNEL ch2  DEVICE TYPE DISK;

duplicate database to "pssp"  backup location '/db02/bkp/2019-06-02' nofilenamecheck

UNTIL TIME "TO_DATE('02/06/2019 17:30:19', 'DD/MM/YYYY HH24:MI:SS')";

}

If you are performing the Duplicate from a source LIVE database:

Most probably you are swapping between source and target DBs in the Connect part in your script, when duplicating from an ACTIVE DB, the TARGET should be connecting to the source LIVE DB which you will restore from e.g. Production DB, and the AUXILIARY should be the TARGET DB which you will restore to e.g. Test DB which should be in NOMOUNT mode, like this:

connect target sys/xxx@SOURCEDB 

connect auxiliary sys/xxx@TARGETDB

...

DUPLICATE TARGET DATABASE TO ... FROM ACTIVE DATABASE

... 

 

Tip: When duplicating from an active database, It's NOT recommended to use "NOFILENAMECHECK" with the duplicate command to avoid overwriting the source DB files.

Ref: https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/rman-duplicating-databases.html#GUID-815B6F9A-7B64-47B3-A68F-B62CECAE141D

 

Conclusion:

- When doing a duplicate from an RMAN backup, RMAN-05502 error can happen because rman command is being aliased to connect to target as a default, remove this alias using "unalias rman" command and run the duplicate command again.

- If your rman command is not aliased, you may be using a wrong date in the UNTIL TIME clause, once you use the right date and time of the backupset, the Duplicate command will go through.

- If you are duplicating from an active database, RMAN-05502 error can indicate that you are wrongly swapping between source and target DBs in the RMAN connect command.

Bundle 5.2 Is Now Available

DBA Bundle V5.2 is now available for download:
https://www.dropbox.com/s/k96rl0f4g39ukih/DBA_BUNDLE5.tar?dl=0

It comes with the following features:

- rebuild_table.sh has been totally re-developed to utilize ONLINE table rebuild features such as DBMS_REDEFINITION and ALTER TABLE MOVE ONLINE. Check this link for more details [https://dba-tips.blogspot.com/2019/05/rebuild-table-script-and-reclaim-wasted.html]

- Added the reporting of the "Top Fragmented Tables" in the daily health check report script dbdailychk.sh.

- Fixing bugs and enhancing the execution time of the following scripts:
dbdailychk.sh
dbalarm.sh
backup_ctrl_spf_AWR.sh
gather_stats.sh
db_locks.sh
active_sessions.sh

If you are new to the DBA BUNDLE the following link will give you a detailed idea:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html