Ask Tom pipe
EXPORT AND IMPORT USING UNIX PIPES.
Sometimes, the space on disk may not be enough to hold a full export dump if uncompressed.
EXAMPLE – export schema MCULP from PROD database and import into DEV database.
To avoid space running out, unix pipes and compression can be used.
EXPORT IN PROD DATABASE
cd /u02/oradata/export
CREATE UNIX PIPE IN THIS AREA – WHERE THE EXPORT DUMP WILL BE WRITTEN TO.
mknod pipe p
CREATE PAR FILE –
parfile is mculp.par
vi mculp.par
buffer=2097152
recordlength=65535
consistent=y
owner=mculp
log=/u02/oradata/export/mculp.log
file=/u02/oradata/export/pipe
Now export schema MCULP.
1. nohup gzip -c </u02/oradata/export/pipe > /u02/oradata/export/mculp.dmp.gz &
Immediately enter next command –
2. nohup exp \’/ as sysdba\’ parfile=/u02/oradata/export/mculp.par &
Export of MCULP schema completes – compressed dump mculp.dmp.gz created.
ftp or copy the dump file mculp.dmp.gz to the DEV database box.
IMPORT IN DEV DATABASE – – Presume same directory structure exists on DEV box.
Create UNIX PIPE in area where dump is copied to in DEV box.
cd /u02/oradata/export
mknod import_pipe p
Create import parfile – called imp.par
vi imp.par
fromuser=mculp
touser=mculp
commit=y
buffer=2097152
ignore=y
file=/u02/oradata/export/import_pipe
log=/u02/oradata/export//imp_mculp.log
Enter commands –
1. nohup gzip -dc </u02/oradata/export/mculp.dmp.gz > import_pipe &
Immediately enter next command –
2. nohup imp \’/ as sysdba\’ parfile=/u02/oradata/export/imp.par
Check the logs for output of import.
cd $BACKUP_DIR
if [ ! -p $PIPE ]; then
mkfifo $PIPE
fi
echo "gzipping from $PIPE ..."
nohup gzip -c < $PIPE > ${DMPFILE}.gz &
echo "expdp started ..."
expdp $CREDENTIALS \
DIRECTORY=DP_EXP_DIR \
DUMPFILE=$PIPE \
FULL=y \
LOGFILE=$LOGFILE
create or replace trigger "T_HLO_ENT_TP_LOOKUP" before insert or update on "HLO_ENT_TP_LOOKUP" for each row begin if inserting and :new."HLO_ENT_TP_ID" is null then
for c1 in (select "HLO_ENT_TP_LOOKUP_SEQ".nextval nv from dual) loop
:new."HLO_ENT_TP_ID" := c1.nv; end loop; end if; end;
create or replace TRIGGER "HC_DATABASE_TRIG"
BEFORE INSERT ON MRCULP.HC_DATABASE FOR EACH ROW
BEGIN
SELECT hc_database_seq.NEXTVAL
INTO :new.db_id
FROM dual;
END;
CREATE SEQUENCE "HLO_ENT_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 5 NOCACHE NOORDER NOCYCLE NOPARTITION
/
Oracle 12c Tuning Guide
Oracle 12c Docs 12.1
Oracle12c High Availability Overview
Oracle12c HA Solutions for Planned Downtime
SQLNet.ora parameters
RAC One Node 2017 Presentation
Net Services pdf
Resource Manager
GoldenGate Whitepaper
SQL Automatic Tuning
Gateway Documentation 11.2
Trace File Collector
Oracle Clusterware Administration and Deployment Guide
Performance Tuning Guide 12.2
When using RAT in an 11g environment please note that the capture is to a SQL Tuning Set and can be done using OEM
The capture process is made by DB Control (EM Express in 12c), Grid Control (or Cloud Control) or using the DBMS_WORKLOAD_CAPTURE package.
The capture files are saved to a directory that we created and configured in the capture process.
How to judge how much impact on the prod system or whatever source system.
RAT can be used to:
- Evaluate new hardware, including migration from one platform to something completely different.
- Verify execution of SQL from today’s production on a newer version of the database
- Analyze the effect of changes in configuration
- Scaling up, see how current system works with higher load; this works best for queries / reporting and not so well for data manipulation (DML).
A project involving RAT can be divided into these activities:
- Capture workload from a production system, can be done via OEM
- Create a clone of production system with flashback database enabled and a restore point created.
- Prepare for replay on test database and with agents on application servers or other client machines.
- Execute replay
- Generate reports (RAT and AWR)
- Flashback database
- If you haven’t reached a conclusion yet, repeat from 4.
Real Application Testing Users Guide
Real Application Testing 12c Webpage
RAT .pdf
Oracle 12c Upgrade and Migrate
Upgrade and Migrate 12c pdf
CREATE [PUBLIC] DATABASE LINK
CONNECT TO
IDENTIFIED BY
USING '';
This link name is called "test" and uses an ID of jim (on the target database) identified by is the password (jim) using the test which is a connect string
CREATE DATABASE LINK test
CONNECT TO jim IDENTIFIED BY jim
USING 'test';
Service Naming
Naming of the service is a factor of the database name since we will have multiple databases running at the cluster level the service name should named as follows:
<DATABASENAME>_SVC_ZZ
<DATABASENAME> = identifies which database this service is for
_SVC_ = Standard abbreviation for “service”, this is different from previous identifier of SRV which is the abbreviation for “server”.
ZZ = Sequential identifier for multiple services per database optionally we could use a set of characters to identify an application or work stream. Further clarification for the optional used variant is currently being developed and further study is needed for this.
Ex: MRCXX50P01_SVC_01
MRCXXP01_SVC_ ARGRP