Why and How to export using a Data Pump utility?

  Why ?

In oracle database to move a data between systems or duplicate a schema in the same system there are several ways to achieve it. One of the common way to do this data migration and/or duplication you may use the Data Pump utility in the command line of the server and or the client with a full administration client installation. Below I will share a sample scripts that export and import using this utility, for advantage and features of this you may refer back to the oracle documentations, However on of the most important feature is that cross platform can be easily maintains by using this.

How ?

Below are sample script to do export and import using data pump ( dp), you have to create or use a user rather than sys, here I am using a system user and have created a Backup directory in OS/Oracle ,:

1- you may run export and import in one line with all necessary values as below, dmp will have the date and .log is the export/import logs, you may rename the import using REMAP as below example:

expdp system/<password>@<db_tns_name> directory=BACKUP schemas=<schema1>,<schema2> dumpfile=<name>.dmp logfile=<name>.log

impdp system/<password>@<db_tns_name> REMAP_SCHEMA=<old_name>:<new_name>,<old_name2>:<new_name2> directory=BACKUP dumpfile=<name>.dmp logfile=<name>.log

2- Export using parameter file with further export features as below example for certain tables of schame:

vi <parm_name>.par

 

DUMPFILE="<name>.dmp"

LOGFILE="<name>.log"

LOGTIME=NONE

DIRECTORY=BACKUP

FLASHBACK_TIME=SYSTIMESTAMP

COMPRESSION=METADATA_ONLY

CONTENT=ALLCOMPRESSION_ALGORITHM=BASIC

TABLES=('<schema."<table_name1>"', '<schema."<table_name2>"', '<schema>."<>table_name3"')

 

expdp system/<password>@<tns_name> PARFILE= <parm_name>.par

3- Import the export dump as needed.

Why and How to manage disk space quota in Solaris 11?

  Why ?

In Solaris you will have a disk and after that the sub disk will be assigned for a value of space to make sure that total space in disk divided on these sub directories and each take the assigned space from disk only , for example if you 100G and you need to make sure /u01 is only 20G and /u02 takes 80G , set a quota is the soft way , reservation and refquota will do this as well with different properties of each , here I will show only how to do quota.

How ?

Below are simple commands for getting and setting the quota

1- list the disks and it is detail:

zpool list

2- get a list of all list of create directory in the disk as mount point:

df –h

zfs list

3- To get/set specific path quota if any :

# zfs set quota=10G tank/home/jeff

# zfs get quota tank/home/jeff

 

My reference always is : https://docs.oracle.com/cd/E23824_01/html/821-1448/gazvb.html

Why and How to Monitor a Servers in WebLogic using a script?

  Why ?

In WebLogic admin console you can see the list of servers and it is health/state , if you don’t have a monitoring tools that is connected to WebLogic and check these servers ( like OEM with WebLogic pack) you have to write a script to get this status in the file for example. Most monitoring tool will be able to be configured with the file or shell script and report an alert in certain condition.

How ?

Below are steps and a sample code to monitor certain servers in WebLogic, WebLogic script use a jython which mostly like a python programing language, tab is considered in the jython code:

1- Create a shell script .sh file with executing permission to call the WebLogic file and store the result in a text file , this can be run in a crontab for example every 5 minutes to be like an archived logs or appended somewhere in a log file:

#!/bin/bash

now=$(date +%d-%m-%Y_%H-%M)

/oracle/Middleware/Oracle_Home/oracle_common/common/bin/wlst.sh /export/home/oracle/get_wls_serverstate_all.py &>/export/home/oracle/weblogic_status.txt

cp -p /export/home/oracle/weblogic_status.txt /export/home/oracle/archive_weblogic_status/weblogic_status_${now}.txt

2- Create a jython code file to monitor and exclude certain servers as below example:

redirect('/dev/null', 'false')

connect('weblogic','<password>','t3://<serverIP>:7001')

import datetime

servers = cmo.getServers()

domainRuntime()

 

for server in servers:

        if server.getName() not in ["",""]:

                try:

                        cd('/ServerRuntimes/' + server.getName());

                        currentState = get('OverallHealthState').getState()

                        now = datetime.datetime.now()

                        current_time = now.strftime("%d-%b-%y_%H:%M:%S")

                        dump_file = '/export/home/oracle/threadDump/'+current_time+server.getName()+'-ThreadDump.log'

                        if currentState == 0:

                                print server.getName() + ': ' + get('State') + ': HEALTH_OK'

                        elif currentState == 1:

                                try:

                                        threadDump('true',dump_file,server.getName())

                                        messg1 = 'ThreadDump has been generated on:'+dump_file

                                except WLSTException, e:

                                        messg1 = 'Exectpiont occur in threadDump'

                                print server.getName() + ': ' + get('State') + ': HEALTH_WARN :'+messg1

                        elif currentState == 2:

                                try:

                                        threadDump('true',dump_file,server.getName())

                                        messg1 = 'ThreadDump has been generated on:'+dump_file

                                except WLSTException, e:

                                        messg1 = 'Exectpiont occur in threadDump'

                                print server.getName() + ': ' + get('State') + ': HEALTH_CRITICAL :'+messg1

                        elif currentState == 3:

                                try:

                                        threadDump('true',dump_file,server.getName())

                                        messg1 = 'ThreadDump has been generated on:'+dump_file

                                except WLSTException, e:

                                        messg1 = 'Exectpiont occur in threadDump'

                                print server.getName() + ': ' + get('State') + ': HEALTH_FAILED :'+messg1

                        elif currentState == 4:

                                try:

                                        threadDump('true',dump_file,server.getName())

                                        messg1 = 'ThreadDump has been generated on:'+dump_file

                                except WLSTException, e:

                                        messg1 = 'Exectpiont occur in threadDump'

                                print server.getName() + ': ' + get('State') + ': HEALTH_OVERLOADED :'+messg1

                        else:

                                try:

                                        threadDump('true',dump_file,server.getName())

                                        messg1 = 'ThreadDump has been generated on:'+dump_file

                                except WLSTException, e:

                                        messg1 = 'Exectpiont occur in threadDump'

                                print server.getName() + ': ' + get('State') + ': UNKNOWN_HEALTH_STATE (' + str(currentState) + ') :'+messg1

                except WLSTException, e:

                        print server.getName()+': SHUTDOWN: Not_Running'

 

disconnect()

3- To use a crontab for example :

####

#Weblogic status

####

0,5,10,15,20,25,30,35,40,45,55 * * * * /export/home/oracle/check_weblogic_all.sh

 

Why and How to enable a DB system event traces?

  Why ?

In certain cases a further information will be needed in order to get know what it is the details of these issue occur, especially for those cases where Oracle consider it as an information ORA like 28 and 3136. Actually these steps can be used for any type of event traces where event is an ORA error expected to be. This is can be enabled for current connected session or sql it is self or for the whole system.

Note*: alter system will be reset and loosed after DB restart.

How ?

Below is steps to enable traces for ora-28 and ora-3436:

1- As sys user , login to sqlplus:

alter system set events '3136 trace name errorstack level 3';

ALTER SYSTEM SET EVENTS '28 trace name errorstack level 12';

2- To make sure trace is enabled you may use on of the below whether to send all details to trace file or get the details on screen as below:

oradebug setmypid

oradebug eventdump session

oradebug eventdump process

oradebug eventdump system

3- To send details to file :

oradebug setmypid

oradebug UNLIMIT

alter session set tracefile_identifier='myevents';

oradebug eventdump session

oradebug eventdump process

oradebug eventdump system

oradebug tracefile_name

Why and How I do upgrade from 12.2 to 19 ?

 Why ?

As a main stage in Oracle lifecycle management is performing the upgrade stage, this is will be due to new feature application may need or bug to be fixed, security enhancement, out of support version ..etc. eventually you will do an upgrade , below are steps and resources that I follow while performing the Oracle upgrade to 19c.

How

I used Sun Solaris 11 things here, DB and RAC option will be covered as well.

I used autoupgrade.jar option as a the only recommended way to upgrade to 19c as per oracle seminars.

1-   Install oracle 19c software only as below (use your OS oracle install document):

1.1 create new home for oracle 19 and I created folder/directory for upgrade files.

mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1

chown -R oracle:oinstall  /u01/app/oracle/product/19.0.0/dbhome_1

chmod 775 /u01/app/oracle/product/19.0.0/dbhome_1

mkdir /u01/upgrade

1.2 unzip the downloaded file of DB in the created path. Unzip your patch if any to be applied while installation of DB.

unzip -q db19.3.zip -d /u01/app/oracle/product/19.0.0/dbhome_1

unzip -q p31281355_190000_SOLARIS64.zip

1.3 Update the Opatch as needed by readme of that patch and replace it with the new opatch from oracle download.

rm -rf /u01/app/oracle/product/19.0.0/dbhome_1/OPatch/

unzip -q p6880880_190000_SOLARIS64.zip -d /u01/app/oracle/product/19.0.0/dbhome_1

 

1.4 Start the software only installation with applying a patch directly while install.

/u01/app/oracle/product/19.0.0/dbhome_1/runInstaller -applyRU /u01/upgrade/31281355

 

1.5 Copy the password, parameter, network and any dependencies file from old home to new home.

cp dr* /u01/app/oracle/product/19.0.0/dbhome_1/dbs/

cp orapwTWFADB /u01/app/oracle/product/19.0.0/dbhome_1/dbs/

cp spfileTWFADB.ora /u01/app/oracle/product/19.0.0/dbhome_1/dbs/

cp *.ora /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/

 

2-   Pre upgrade checkup and preparations.

2.1 Download, configure and run autoupgrade script using oracle doc : 2485457.1

java -jar  autoupgrade.jar -create_sample_file config

 

2.2 After modifying the file with needed details start the analyze stage .

java -jar autoupgrade.jar -config sample_config.cfg -mode ANALYZE

 

2.3 After applying the precheck needed things we will start the actual upgrade , below is needed action in case data-guard is used in placed and broker is configured as well . In Standby DB perform the below

DGMGRL> edit database <standby_db> set state = APPLY-OFF

DGMGRL> edit database <primary_db> set state = TRANSPORT-OFF

DGMGRL> DISABLE CONFIGURATION;

# In primary DB

SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;

# In standby DB

stop db and listener

 

2.4 to speed upgrade as per the pre-upgrade fix you will need to do the below to check the last update for optimizer gather objects date:

column OPERATION format a40

set linesize 200

select to_char(max(END_TIME),'DD-MON-YY hh24:mi') LATEST, OPERATION

from DBA_OPTSTAT_OPERATIONS

where OPERATION in ('gather_dictionary_stats', 'gather_fixed_objects_stats')

group by operation;

 

2.5 Start the upgrade stage.

java -jar autoupgrade.jar -config sample_config.cfg -mode deploy

 

2.6 To check the status life while it is run in html page.

cd ~/upg_logs/cfgtoollogs/upgrade/auto

python -m SimpleHTTPServer 8000

http://10.10.10.10:8000/state.html

 

3-   Pre upgrade checkup and preparations.

3.1       Convert TDE from sqlnet.ora to parameter based .

How To Convert From Using SQLNET.ENCRYPTION_WALLET_LOCATION To 19c Parameter (WALLET_ROOT and TDE_CONFIGURATION) (Doc ID 2642694.1)

 

3.2       Things to do right after upgrade

• Configure statistics history retention period

• Check space usage:

• Check retention

• Default: 31 days

• Adjust setting

• Example: 10 days.

SQL> select space_usage_kbytes/1024 mb

from v$sysaux_occupants where

occupant_name='SM/OPTSTAT';

SQL> select dbms_stats.get_stats_history_retention from dual;

SQL> exec dbms_stats.alter_stats_history_retention(10);

SQL> exec DBMS_STATS.PURGE_STATS (SYSDATE-10) ;

 

 

3.3       To check health in case of neede

hcheck.sql - Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c and Above (Doc ID 136697.1)

 

3.4       In case you need to check whether apex is need to be upgrade/used.

Check if any one use apex

 select workspace, view_date, seconds_ago from apex_workspace_activity_log

check work space

select workspace, workspace_id from apex_workspaces;