Linux, Oracle

Oracle TFA upgrade from 18.1 to 18.3 on RAC database

[root@prod ~]# /u01/app/12.2.0/grid/bin/tfactl status

.——————————————————————————————————-.
| Host | Status of TFA | PID | Port | Version | Build ID | Inventory Status |
+—————–+—————+——-+——+————+———————-+——————+
| prod | RUNNING | 32071 | 5000 | 18.1.1.0.0 | 18110020180328025002 | COMPLETE |
| prod2 | RUNNING | 6505 | 5000 | 18.1.1.0.0 | 18110020180328025002 | COMPLETE |
‘—————–+—————+——-+——+————+———————-+——————‘

[root@prod tmp]# ./installTFA-LINUX
TFA Installation Log will be written to File : /tmp/tfa_install_25637_2018_08_28-11_21_28.log

Starting TFA installation

TFA Version: 183000 Build Date: 201808081359

TFA HOME : /u01/app/12.2.0/grid/tfa/prod/tfa_home

Installed Build Version: 181100 Build Date: 201803280250

TFA is already installed. Patching /u01/app/12.2.0/grid/tfa/prod/tfa_home…
TFA patching typical install from zipfile is written to /u01/app/12.2.0/grid/tfa/prod/tfapatch.log

TFA will be Patched on:
prod
prod2

Do you want to continue with patching TFA? [Y|N] [Y]: y
Checking for ssh equivalency in prod2
prod2 is configured for ssh user equivalency for root user
Using SSH to patch TFA to remote nodes :

Applying Patch on prod2:

TFA_HOME: /u01/app/12.2.0/grid/tfa/prod2/tfa_home
Stopping TFA Support Tools…
Shutting down TFA
Removed symlink /etc/systemd/system/graphical.target.wants/oracle-tfa.service.
Removed symlink /etc/systemd/system/multi-user.target.wants/oracle-tfa.service.
. . . . .
. . .
Successfully shutdown TFA..
Copying files from prod to prod2…

Current version of Berkeley DB in prod2 is 5 or higher, so no DbPreUpgrade required
Running commands to fix init.tfa and tfactl in prod2…
Updating init.tfa in prod2…
Starting TFA in prod2…
Starting TFA..
Created symlink from /etc/systemd/system/multi-user.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.
Created symlink from /etc/systemd/system/graphical.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.
Waiting up to 100 seconds for TFA to be started..
. . . . .
Successfully started TFA Process..
. . . . .
TFA Started and listening for commands

Enabling Access for Non-root Users on prod2…
Applying Patch on prod:

Stopping TFA Support Tools…

Shutting down TFA for Patching…

Shutting down TFA
Removed symlink /etc/systemd/system/graphical.target.wants/oracle-tfa.service.
Removed symlink /etc/systemd/system/multi-user.target.wants/oracle-tfa.service.
. . . . .
. . .
Successfully shutdown TFA..

No Berkeley DB upgrade required

Copying TFA Certificates…

Running commands to fix init.tfa and tfactl in localhost
Starting TFA in prod…

Starting TFA..
Created symlink from /etc/systemd/system/multi-user.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.
Created symlink from /etc/systemd/system/graphical.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.
Waiting up to 100 seconds for TFA to be started..
. . . . .
Successfully started TFA Process..
. . . . .
TFA Started and listening for commands

Enabling Access for Non-root Users on prod…

.———————————————————————–.
| Host | TFA Version | TFA Build ID | Upgrade Status |
+—————–+————-+———————-+—————-+
| prod | 18.3.0.0.0 | 18300020180808135947 | UPGRADED |
| prod2 | 18.3.0.0.0 | 18300020180808135947 | UPGRADED |
‘—————–+————-+———————-+—————-‘

cleanup serializable files

[root@prod tmp]# /u01/app/oracle/product/12.2.0.1/db_1/bin/tfactl toolstatus
-bash: /u01/app/oracle/product/12.2.0.1/db_1/bin/tfactl: No such file or directory
[root@prod tmp]# /u01/app/12.2.0/grid/bin/tfactl toolstatus

.——————————————————————.
| TOOLS STATUS – HOST : prod |
+———————-+————–+————–+————-+
| Tool Type | Tool | Version | Status |
+———————-+————–+————–+————-+
| Development Tools | orachk | 12.2.0.1.3 | DEPLOYED |
| | oratop | 14.1.2 | DEPLOYED |
+———————-+————–+————–+————-+
| Support Tools Bundle | darda | 2.10.0.R6036 | DEPLOYED |
| | oswbb | 8.0.1 | RUNNING |
| | prw | 12.1.13.11.4 | NOT RUNNING |
+———————-+————–+————–+————-+
| TFA Utilities | alertsummary | 12.2.1.1.0 | DEPLOYED |
| | calog | 12.2.0.1.0 | DEPLOYED |
| | dbcheck | 18.3.0.0.0 | DEPLOYED |
| | dbglevel | 12.2.1.1.0 | DEPLOYED |
| | grep | 12.2.1.1.0 | DEPLOYED |
| | history | 12.2.1.1.0 | DEPLOYED |
| | ls | 12.2.1.1.0 | DEPLOYED |
| | managelogs | 12.2.1.1.0 | DEPLOYED |
| | menu | 12.2.1.1.0 | DEPLOYED |
| | param | 12.2.1.1.0 | DEPLOYED |
| | ps | 12.2.1.1.0 | DEPLOYED |
| | pstack | 12.2.1.1.0 | DEPLOYED |
| | summary | 12.2.1.1.0 | DEPLOYED |
| | tail | 12.2.1.1.0 | DEPLOYED |
| | triage | 12.2.1.1.0 | DEPLOYED |
| | vi | 12.2.1.1.0 | DEPLOYED |
‘———————-+————–+————–+————-‘

Note :-
DEPLOYED : Installed and Available – To be configured or run interactively.
NOT RUNNING : Configured and Available – Currently turned off interactively.
RUNNING : Configured and Available.

[root@prod tmp]# ps -ef |grep osw
grid 1881 1 0 11:24 ? 00:00:00 /bin/sh ./OSWatcher.sh 30 48 NONE /u01/app/grid/tfa/repository/suptools/prod/oswbb/grid/archive
grid 4687 1881 0 11:25 ? 00:00:00 /bin/sh ./OSWatcherFM.sh 48 /u01/app/grid/tfa/repository/suptools/prod/oswbb/grid/archive
root 18797 15763 0 11:27 pts/0 00:00:00 grep –color=auto osw
[root@prod tmp]# ls


[root@prod2 ~]# /u01/app/12.2.0/grid/bin/tfactl status

.——————————————————————————————————-.
| Host | Status of TFA | PID | Port | Version | Build ID | Inventory Status |
+—————–+—————+——-+——+————+———————-+——————+
| prod2 | RUNNING | 24858 | 5000 | 18.3.0.0.0 | 18300020180808135947 | COMPLETE |
| xqaytidmdbprod1 | RUNNING | 908 | 5000 | 18.3.0.0.0 | 18300020180808135947 | RUNNING |
‘—————–+—————+——-+——+————+———————-+——————‘
[root@prod2 ~]# /u01/app/12.2.0/grid/bin/tfactl toolstatus

.——————————————————————.
| TOOLS STATUS – HOST : prod2 |
+———————-+————–+————–+————-+
| Tool Type | Tool | Version | Status |
+———————-+————–+————–+————-+
| Development Tools | orachk | 12.2.0.1.3 | DEPLOYED |
| | oratop | 14.1.2 | DEPLOYED |
+———————-+————–+————–+————-+
| Support Tools Bundle | darda | 2.10.0.R6036 | DEPLOYED |
| | oswbb | 8.0.1 | RUNNING |
| | prw | 12.1.13.11.4 | NOT RUNNING |
+———————-+————–+————–+————-+
| TFA Utilities | alertsummary | 12.2.1.1.0 | DEPLOYED |
| | calog | 12.2.0.1.0 | DEPLOYED |
| | dbcheck | 18.3.0.0.0 | DEPLOYED |
| | dbglevel | 12.2.1.1.0 | DEPLOYED |
| | grep | 12.2.1.1.0 | DEPLOYED |
| | history | 12.2.1.1.0 | DEPLOYED |
| | ls | 12.2.1.1.0 | DEPLOYED |
| | managelogs | 12.2.1.1.0 | DEPLOYED |
| | menu | 12.2.1.1.0 | DEPLOYED |
| | param | 12.2.1.1.0 | DEPLOYED |
| | ps | 12.2.1.1.0 | DEPLOYED |
| | pstack | 12.2.1.1.0 | DEPLOYED |
| | summary | 12.2.1.1.0 | DEPLOYED |
| | tail | 12.2.1.1.0 | DEPLOYED |
| | triage | 12.2.1.1.0 | DEPLOYED |
| | vi | 12.2.1.1.0 | DEPLOYED |
‘———————-+————–+————–+————-‘

Note :-
DEPLOYED : Installed and Available – To be configured or run interactively.
NOT RUNNING : Configured and Available – Currently turned off interactively.
RUNNING : Configured and Available.

[root@prod2 ~]# ps -ef |grep osw
grid 25682 1 0 11:22 ? 00:00:00 /bin/sh ./OSWatcher.sh 30 48 NONE /u01/app/grid/tfa/repository/suptools/prod2/oswbb/grid/archive
root 25948 20854 0 11:29 pts/0 00:00:00 grep –color=auto osw
grid 27200 25682 0 11:23 ? 00:00:00 /bin/sh ./OSWatcherFM.sh 48 /u01/app/grid/tfa/repository/suptools/prod2/oswbb/grid/archive
[root@prod2 ~]#

 

Br. Erman.

Advertisements
Standard
Linux

Oracle Database TFA(Trace File Analyzer) upgrade from 12.2 to 18.3

Hello,Oracle releases a new version of Oracle Trace File Analyzer. Download the latest version of Oracle Trace File Analyzer with Oracle Database support tools bundle from My Oracle Support note 1513912.1.

Upgrading is similar to first-time install. As root, use the installTFAplatform script.
exaple for linux > ./installTFA-Linux.

step by steps upgrade TFA from 12.2 to 18.3.

check curently version of TFA.

[root@ermantest tmp]# /u01/app/oracle/product/12.1.0.2/db_1/bin/tfactl status

.————————————————————————————————-.
| Host | Status of TFA | PID | Port | Version | Build ID | Inventory Status |
+———–+—————+——+——-+————+———————-+——————+
| ermantest | RUNNING | 3610 | 62675 | 12.2.1.0.0 | 12210020161122170355 | COMPLETE |
‘———–+—————+——+——-+————+———————-+——————‘

  • Downloand TFA 18.3 under the tmp file, then run this file.

[root@ermantest tmp]# ./installTFA-LINUX
TFA Installation Log will be written to File : /tmp/tfa_install_4423_2018_08_27-17_40_27.log

Starting TFA installation

TFA Version: 183000 Build Date: 201808081359

TFA HOME : /u01/app/oracle/tfa/ermantest/tfa_home

Installed Build Version: 122100 Build Date: 201611221703

TFA is already installed. Patching /u01/app/oracle/tfa/ermantest/tfa_home…
TFA patching typical install from zipfile is written to /u01/app/oracle/tfa/ermantest/tfapatch.log

TFA will be Patched on Node ermantest:

Do you want to continue with patching TFA? [Y|N] [Y]: y

Applying Patch on ermantest:

Stopping TFA Support Tools…

Shutting down TFA for Patching…

Shutting down TFA
oracle-tfa stop/waiting
. . . . .
. . .
Successfully shutdown TFA..

No Berkeley DB upgrade required

Copying TFA Certificates…

Running commands to fix init.tfa and tfactl in localhost

Starting TFA in ermantest…

Starting TFA..
oracle-tfa start/running, process 5070
Waiting up to 100 seconds for TFA to be started..
. . . . .
. . . . .
Successfully started TFA Process..
. . . . .
TFA Started and listening for commands

Enabling Access for Non-root Users on ermantest…

WARNING – TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.
WARNING – TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.
.—————————————————————–.
| Host | TFA Version | TFA Build ID | Upgrade Status |
+———–+————-+———————-+—————-+
| ermantest | 18.3.0.0.0 | 18300020180808135947 | UPGRADED |
‘———–+————-+———————-+—————-‘

cleanup serializable files

You have new mail in /var/spool/mail/root

  • show new version of TFA.

[root@ermantest tmp]# /u01/app/oracle/product/12.1.0.2/db_1/bin/tfactl status

.————————————————————————————————-.
| Host | Status of TFA | PID | Port | Version | Build ID | Inventory Status |
+———–+—————+——+——-+————+———————-+——————+
| ermantest | RUNNING | 5153 | 15218 | 18.3.0.0.0 | 18300020180808135947 | UPGRADED |
‘———–+—————+——+——-+————+———————-+——————‘

[root@ermantest tmp]#

root@ermantest tmp]# /u01/app/oracle/product/12.1.0.2/db_1/bin/tfactl toolstatus

.——————————————————————.
| TOOLS STATUS – HOST : ermantest |
+———————-+————–+————–+————-+
| Tool Type | Tool | Version | Status |
+———————-+————–+————–+————-+
| Development Tools | orachk | 12.2.0.1.3 | DEPLOYED |
| | oratop | 14.1.2 | DEPLOYED |
+———————-+————–+————–+————-+
| Support Tools Bundle | darda | 2.10.0.R6036 | DEPLOYED |
| | oswbb | 8.0.1 | NOT RUNNING |
| | prw | 12.1.13.11.4 | NOT RUNNING |
+———————-+————–+————–+————-+
| TFA Utilities | alertsummary | 12.2.1.1.0 | DEPLOYED |
| | calog | 12.2.0.1.0 | DEPLOYED |
| | dbcheck | 18.3.0.0.0 | DEPLOYED |
| | dbglevel | 12.2.1.1.0 | DEPLOYED |
| | grep | 12.2.1.1.0 | DEPLOYED |
| | history | 12.2.1.1.0 | DEPLOYED |
| | ls | 12.2.1.1.0 | DEPLOYED |
| | managelogs | 12.2.1.1.0 | DEPLOYED |
| | menu | 12.2.1.1.0 | DEPLOYED |
| | param | 12.2.1.1.0 | DEPLOYED |
| | ps | 12.2.1.1.0 | DEPLOYED |
| | pstack | 12.2.1.1.0 | DEPLOYED |
| | summary | 12.2.1.1.0 | DEPLOYED |
| | tail | 12.2.1.1.0 | DEPLOYED |
| | triage | 12.2.1.1.0 | DEPLOYED |
| | vi | 12.2.1.1.0 | DEPLOYED |
‘———————-+————–+————–+————-‘

Note :-
DEPLOYED : Installed and Available – To be configured or run interactively.
NOT RUNNING : Configured and Available – Currently turned off interactively.
RUNNING : Configured and Available.

Nice, Br. Erman.

Standard
Linux

Oracle Database Trace File Analyzer Installation – Oracle database 12.2

[root@ermantest ~]# cd /u01/app/oracle/product/12.1.0.2/db_1/
[root@ermantest db_1]# ./root.sh
Performing root user operation.

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/12.1.0.2/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of “dbhome” have not changed. No need to overwrite.
The contents of “oraenv” have not changed. No need to overwrite.
The contents of “coraenv” have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Do you want to setup Oracle Trace File Analyzer (TFA) now ? yes|[no] :
yes
Installing Oracle Trace File Analyzer (TFA).
Log File: /u01/app/oracle/product/12.1.0.2/db_1/install/root_ermantest_2018-08-27_12-39-02-731205117.log
Finished installing Oracle Trace File Analyzer (TFA)
[root@ermantest db_1]# cd ü
-bash: cd: ü: No such file or directory
[root@ermantest db_1]# cd /u01/app/oracle/product/12.1.0.2/db_1/
addnode/ dbjava/ inventory/ odbc/ plsql/ sqlpatch/
apex/ dbs/ javavm/ olap/ precomp/ sqlplus/
assistants/ dc_ocm/ jdbc/ OPatch/ QOpatch/ srvm/
bin/ deinstall/ jdk/ opmn/ R/ suptools/
ccr/ demo/ jlib/ oracore/ racg/ sysman/
cdata/ diagnostics/ ldap/ oraInst.loc rdbms/ ucp/
cfgtoollogs/ dmu/ lib/ ord/ relnotes/ usm/
clone/ dv/ log/ ordim/ http://root.sh utl/
crs/ env.ora md/ ords/ schagent.conf wwg/
css/ has/ mgw/ oss/ scheduler/ xdk/
ctx/ hs/ network/ oui/ slax/
cv/ install/ nls/ owm/ sqldeveloper/
data/ instantclient/ oc4j/ perl/ sqlj/
[root@ermantest db_1]# cd /u01/app/oracle/product/12.1.0.2/db_1/bin/
[root@ermantest bin]# ./tfactl status

.————————————————————————————————–.
| Host | Status of TFA | PID | Port | Version | Build ID | Inventory Status |
+———–+—————+——-+——-+————+———————-+——————+
| ermantest | RUNNING | 10589 | 59891 | 12.2.1.0.0 | 12210020161122170355 | COMPLETE |
‘———–+—————+——-+——-+————+———————-+——————‘
br. erman

Standard
Linux

Trace File Analyzer Collector – TFA install and deinstall

[root@ermantest ~]# cd /tmp/
[root@ermantest tmp]# ./installTFA-LINUX
TFA Installation Log will be written to File : /tmp/tfa_install_1227_2018_08_27-11_45_20.log

Starting TFA installation

TFA Version: 183000 Build Date: 201808081359

Enter a location for installing TFA (/tfa will be appended if not supplied) [/tmp/tfa]:

Running Auto Setup for TFA as user root…

Would you like to do a [L]ocal only or [C]lusterwide installation ? [L|l|C|c] [C] : l
Installing TFA now…

Discovering Nodes and Oracle resources

Starting Discovery…

No Grid Infrastructure Discovered on this system . . . . .

ermantest
Searching for running databases…
1. TESTDB

Searching out ORACLE_HOME for selected databases…

Getting Oracle Inventory…

ORACLE INVENTORY: /u01/app/oraInventory

Discovery Complete…

TFA Will be Installed on ermantest…

TFA will scan the following Directories
++++++++++++++++++++++++++++++++++++++++++++

.——————————————————————.
| ermantest |
+——————————————————-+———-+
| Trace Directory | Resource |
+——————————————————-+———-+
| /u01/app/oraInventory/ContentsXML | INSTALL |
| /u01/app/oraInventory/logs | INSTALL |
| /u01/app/oracle/cfgtoollogs | CFGTOOLS |
| /u01/app/oracle/diag/rdbms/testdb/TESTDB/cdump | RDBMS |
| /u01/app/oracle/diag/rdbms/testdb/TESTDB/trace | RDBMS |
| /u01/app/oracle/diag/tnslsnr/ermantest/listener/cdump | TNS |
| /u01/app/oracle/diag/tnslsnr/ermantest/listener/trace | TNS |
| /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs | CFGTOOLS |
| /u01/app/oracle/product/12.1.0.2/db_1/install | INSTALL |
‘——————————————————-+———-‘

Installing TFA on ermantest:
HOST: ermantestTFA_HOME: /tmp/tfa/ermantest/tfa_home

.——————————————————————————.
| Host | Status of TFA | PID | Port | Version | Build ID |
+———–+—————+——+——-+————+———————-+
| ermantest | RUNNING | 1715 | 30560 | 18.3.0.0.0 | 18300020180808135947 |
‘———–+—————+——+——-+————+———————-‘

Running Inventory in All Nodes…

Enabling Access for Non-root Users on ermantest…

Please add world execute permissions for all directories above /tmp to enable TFA Non-Root Access

Adding default users to TFA Access list…

Summary of TFA Installation:
.—————————————————.
| ermantest |
+———————+—————————–+
| Parameter | Value |
+———————+—————————–+
| Install location | /tmp/tfa/ermantest/tfa_home |
| Repository location | /tmp/tfa/repository |
‘———————+—————————–‘

TFA is successfully installed…

Usage : /tmp/tfa/bin/tfactl <command> [options]
commands:diagcollect|collection|analyze|ips|run|start|stop|enable|disable|status|print|access|purge|directory|host|receiver|set|toolstatus|uninstall|diagnosetfa|syncnodes|setupmos|upload|availability|rest|events|search|changes|isa
For detailed help on each command use:
/tmp/tfa/bin/tfactl <command> -help

[root@ermantest tmp]# /u01/app/oracle/product/12.1.0.2/db_1/bin/tfactl status

.————————————————————————————————-.
| Host | Status of TFA | PID | Port | Version | Build ID | Inventory Status |
+———–+—————+——+——-+————+———————-+——————+
| ermantest | RUNNING | 1715 | 30560 | 18.3.0.0.0 | 18300020180808135947 | COMPLETE |
‘———–+—————+——+——-+————+———————-+——————‘

[root@ermantest tmp]# /u01/app/oracle/product/12.1.0.2/db_1/bin/tfactl toolstatus

.——————————————————————.
| TOOLS STATUS – HOST : ermantest |
+———————-+————–+————–+————-+
| Tool Type | Tool | Version | Status |
+———————-+————–+————–+————-+
| Development Tools | orachk | 12.2.0.1.3 | DEPLOYED |
| | oratop | 14.1.2 | DEPLOYED |
+———————-+————–+————–+————-+
| Support Tools Bundle | darda | 2.10.0.R6036 | DEPLOYED |
| | oswbb | 8.0.1 | NOT RUNNING |
| | prw | 12.1.13.11.4 | NOT RUNNING |
+———————-+————–+————–+————-+
| TFA Utilities | alertsummary | 12.2.1.1.0 | DEPLOYED |
| | calog | 12.2.0.1.0 | DEPLOYED |
| | dbcheck | 18.3.0.0.0 | DEPLOYED |
| | dbglevel | 12.2.1.1.0 | DEPLOYED |
| | grep | 12.2.1.1.0 | DEPLOYED |
| | history | 12.2.1.1.0 | DEPLOYED |
| | ls | 12.2.1.1.0 | DEPLOYED |
| | managelogs | 12.2.1.1.0 | DEPLOYED |
| | menu | 12.2.1.1.0 | DEPLOYED |
| | param | 12.2.1.1.0 | DEPLOYED |
| | ps | 12.2.1.1.0 | DEPLOYED |
| | pstack | 12.2.1.1.0 | DEPLOYED |
| | summary | 12.2.1.1.0 | DEPLOYED |
| | tail | 12.2.1.1.0 | DEPLOYED |
| | triage | 12.2.1.1.0 | DEPLOYED |
| | vi | 12.2.1.1.0 | DEPLOYED |
‘———————-+————–+————–+————-‘

Note :-
DEPLOYED : Installed and Available – To be configured or run interactively.
NOT RUNNING : Configured and Available – Currently turned off interactively.
RUNNING : Configured and Available.

[root@ermantest tmp]#

Erman KARA
[root@ermantest tmp]# /u01/app/oracle/product/12.1.0.2/db_1/bin/
Display all 195 possibilities? (y or n)
[root@ermantest tmp]# /u01/app/oracle/product/12.1.0.2/db_1/bin/tfactl uninstall
TFA will be uninstalled on ermantest :

Removing TFA from ermantest…

Stopping TFA Support Tools…

Stopping TFA in ermantest…

Shutting down TFA
oracle-tfa stop/waiting
. . . . .
Killing TFA running with pid 1715
. . .
Successfully shutdown TFA..

TFA-00002 Oracle Trace File Analyzer (TFA) is not running
Deleting TFA support files on ermantest:
Removing /etc/rc.d/rc0.d/K17init.tfa
Removing /etc/rc.d/rc1.d/K17init.tfa
Removing /etc/rc.d/rc2.d/K17init.tfa
Removing /etc/rc.d/rc4.d/K17init.tfa
Removing /etc/rc.d/rc6.d/K17init.tfa
Removing /etc/init.d/init.tfa…
Removing /tmp/tfa/bin…
Removing /tmp/tfa/ermantest…

[root@ermantest tmp]#
[root@ermantest tmp]#
[root@ermantest tmp]#
[root@ermantest tmp]#

Standard
Oracle

How can I Estimate Table Sizes within Schema – oracle

Look at the “dba_segments” view (or user_segments if you don’t have dba rights). The following query should give you what you’re looking for:

select
  owner as "Schema"
  , segment_name as "Object Name"
  , segment_type as "Object Type"
  , round(bytes/1024/1024,2) as "Object Size (Mb)"
  , tablespace_name as "Tablespace"
from dba_segments
order by owner;

demo :

Use LOOP to insert data to a table

create table ERMAN.Employee(
ID VARCHAR2(4 BYTE) NOT NULL primary key,
First_Name VARCHAR2(10 BYTE),
Last_Name VARCHAR2(10 BYTE),
Start_Date DATE,
End_Date DATE,
Salary Number(8,2),
City VARCHAR2(10 BYTE),
Description VARCHAR2(15 BYTE)
)
/


BEGIN
FOR v_LoopCounter IN 1..5000 LOOP
INSERT INTO ERMAN.employee (id)
VALUES (v_LoopCounter);
END LOOP;
END;
/

output:

demo

br. erman…

 

Standard
Oracle

Enabling ARCHIVELOG Mode

Most of the High Availability features of Oracle require you to enable ARCHIVELOG mode for your database. When you enable this mode redo logs will be archived instead of overwritten. The archivelogs are stored in a seperate place usually can backed up regularly by your standard filesystem backup system (NetBackup or whatever). Archive logs are utilized by RMAN, Data Guard, Flashback and many others.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1275068416 bytes
Fixed Size 8620272 bytes
Variable Size 872417040 bytes
Database Buffers 385875968 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL> shutdown immediate;
SP2-0734: unknown command beginning “sh▒utdown …” – rest of line ignored.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1275068416 bytes
Fixed Size 8620272 bytes
Variable Size 872417040 bytes
Database Buffers 385875968 bytes
Redo Buffers 8155136 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
————
ARCHIVELOG

SQL>

Enabling archive mode is simple, just connect to your database in mounted but closed mode (startup mount) and alter the database. But if you don’t tune alittle you’ll run into problems down the road, so lets specify some parameters too. Namely, consider LOG_ARCHIVE_DEST.

Standard
Oracle SQL Developer

Where to get this Java.exe file for a SQL Developer installation

I just installed Oracle 11g, and tried to start Oracle SQL developer so as to start writing queries.

It is asking me:

Enter the full pathname for the java.exe file .

Where do I find this?

answer :

cd $ORACLE_HOME\jdk

windows :

C:\app\product\12.2.0\client_1\jdk

Standard