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:
A project involving RAT can be divided into these activities:
  1. Capture workload from a production system, can be done via OEM
  2. Create a clone of production system with flashback database enabled and a restore point created.
  3. Prepare for replay on test database and with agents on application servers or other client machines.
  4. Execute replay
  5. Generate reports (RAT and AWR)
  6. Flashback database
  7. 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

 

Ready for Action?

LET'S GO!
Copyright 2024 IT Remote dot com
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram