Practice SQL with Sample Tables
CREATE
TABLE "AGENTS"
(
"AGENT_CODE" CHAR(6) NOT NULL PRIMARY KEY,
"AGENT_NAME" CHAR(40),
"WORKING_AREA" CHAR(35),
"COMMISSION" NUMBER(10,2),
"PHONE_NO" CHAR(15),
"COUNTRY" VARCHAR2(25)
);
INSERT INTO
AGENTS VALUES ('A007', 'Ramasundar', 'Bangalore', '0.15', '077-25814763', '');
INSERT INTO
AGENTS VALUES ('A003', 'Alex ', 'London', '0.13', '075-12458969', '');
INSERT INTO
AGENTS VALUES ('A008', 'Alford', 'New York', '0.12', '044-25874365', '');
INSERT INTO
AGENTS VALUES ('A011', 'Ravi Kumar', 'Bangalore', '0.15', '077-45625874', '');
INSERT INTO
AGENTS VALUES ('A010', 'Santakumar', 'Chennai', '0.14', '007-22388644', '');
INSERT INTO
AGENTS VALUES ('A012', 'Lucida', 'San Jose', '0.12', '044-52981425', '');
INSERT INTO
AGENTS VALUES ('A005', 'Anderson', 'Brisban', '0.13', '045-21447739', '');
INSERT INTO
AGENTS VALUES ('A001', 'Subbarao', 'Bangalore', '0.14', '077-12346674', '');
INSERT INTO
AGENTS VALUES ('A002', 'Mukesh', 'Mumbai', '0.11', '029-12358964', '');
INSERT INTO
AGENTS VALUES ('A006', 'McDen', 'London', '0.15', '078-22255588', '');
INSERT INTO
AGENTS VALUES ('A004', 'Ivan', 'Torento', '0.15', '008-22544166', '');
INSERT INTO
AGENTS VALUES ('A009', 'Benjamin', 'Hampshair', '0.11', '008-22536178', '');
CREATE TABLE "CUSTOMER"
(
"CUST_CODE" VARCHAR2(6) NOT NULL PRIMARY KEY,
"CUST_NAME" VARCHAR2(40) NOT
NULL,
"CUST_CITY" CHAR(35),
"WORKING_AREA" VARCHAR2(35)
NOT NULL,
"CUST_COUNTRY" VARCHAR2(20)
NOT NULL,
"GRADE" NUMBER,
"OPENING_AMT" NUMBER(12,2)
NOT NULL,
"RECEIVE_AMT" NUMBER(12,2)
NOT NULL,
"PAYMENT_AMT" NUMBER(12,2)
NOT NULL,
"OUTSTANDING_AMT"
NUMBER(12,2) NOT NULL,
"PHONE_NO" VARCHAR2(17) NOT
NULL,
"AGENT_CODE" CHAR(6) NOT NULL
REFERENCES AGENTS
);
INSERT INTO
CUSTOMER VALUES ('C00013', 'Holmes', 'London', 'London', 'UK', '2', '6000.00',
'5000.00', '7000.00', '4000.00', 'BBBBBBB', 'A003');
INSERT INTO
CUSTOMER VALUES ('C00001', 'Micheal', 'New York', 'New York', 'USA', '2',
'3000.00', '5000.00', '2000.00', '6000.00', 'CCCCCCC', 'A008');
INSERT INTO
CUSTOMER VALUES ('C00020', 'Albert', 'New York', 'New York', 'USA', '3',
'5000.00', '7000.00', '6000.00', '6000.00', 'BBBBSBB', 'A008');
INSERT INTO
CUSTOMER VALUES ('C00025', 'Ravindran', 'Bangalore', 'Bangalore', 'India', '2',
'5000.00', '7000.00', '4000.00', '8000.00', 'AVAVAVA', 'A011');
INSERT INTO
CUSTOMER VALUES ('C00024', 'Cook', 'London', 'London', 'UK', '2', '4000.00',
'9000.00', '7000.00', '6000.00', 'FSDDSDF', 'A006');
INSERT INTO
CUSTOMER VALUES ('C00015', 'Stuart', 'London', 'London', 'UK', '1', '6000.00',
'8000.00', '3000.00', '11000.00', 'GFSGERS', 'A003');
INSERT INTO
CUSTOMER VALUES ('C00002', 'Bolt', 'New York', 'New York', 'USA', '3',
'5000.00', '7000.00', '9000.00', '3000.00', 'DDNRDRH', 'A008');
INSERT INTO
CUSTOMER VALUES ('C00018', 'Fleming', 'Brisban', 'Brisban', 'Australia', '2',
'7000.00', '7000.00', '9000.00', '5000.00', 'NHBGVFC', 'A005');
INSERT INTO
CUSTOMER VALUES ('C00021', 'Jacks', 'Brisban', 'Brisban', 'Australia', '1',
'7000.00', '7000.00', '7000.00', '7000.00', 'WERTGDF', 'A005');
INSERT INTO
CUSTOMER VALUES ('C00019', 'Yearannaidu', 'Chennai', 'Chennai', 'India', '1',
'8000.00', '7000.00', '7000.00', '8000.00', 'ZZZZBFV', 'A010');
INSERT INTO
CUSTOMER VALUES ('C00005', 'Sasikant', 'Mumbai', 'Mumbai', 'India', '1',
'7000.00', '11000.00', '7000.00', '11000.00', '147-25896312', 'A002');
INSERT INTO
CUSTOMER VALUES ('C00007', 'Ramanathan', 'Chennai', 'Chennai', 'India', '1',
'7000.00', '11000.00', '9000.00', '9000.00', 'GHRDWSD', 'A010');
INSERT INTO
CUSTOMER VALUES ('C00022', 'Avinash', 'Mumbai', 'Mumbai', 'India', '2',
'7000.00', '11000.00', '9000.00', '9000.00', '113-12345678','A002');
INSERT INTO
CUSTOMER VALUES ('C00004', 'Winston', 'Brisban', 'Brisban', 'Australia', '1',
'5000.00', '8000.00', '7000.00', '6000.00', 'AAAAAAA', 'A005');
INSERT INTO
CUSTOMER VALUES ('C00023', 'Karl', 'London', 'London', 'UK', '0', '4000.00',
'6000.00', '7000.00', '3000.00', 'AAAABAA', 'A006');
INSERT INTO
CUSTOMER VALUES ('C00006', 'Shilton', 'Torento', 'Torento', 'Canada', '1',
'10000.00', '7000.00', '6000.00', '11000.00', 'DDDDDDD', 'A004');
INSERT INTO
CUSTOMER VALUES ('C00010', 'Charles', 'Hampshair', 'Hampshair', 'UK', '3',
'6000.00', '4000.00', '5000.00', '5000.00', 'MMMMMMM', 'A009');
INSERT INTO
CUSTOMER VALUES ('C00017', 'Srinivas', 'Bangalore', 'Bangalore', 'India', '2',
'8000.00', '4000.00', '3000.00', '9000.00', 'AAAAAAB', 'A007');
INSERT INTO
CUSTOMER VALUES ('C00012', 'Steven', 'San Jose', 'San Jose', 'USA', '1',
'5000.00', '7000.00', '9000.00', '3000.00', 'KRFYGJK', 'A012');
INSERT INTO
CUSTOMER VALUES ('C00008', 'Karolina', 'Torento', 'Torento', 'Canada', '1',
'7000.00', '7000.00', '9000.00', '5000.00', 'HJKORED', 'A004');
INSERT INTO
CUSTOMER VALUES ('C00003', 'Martin', 'Torento', 'Torento', 'Canada', '2',
'8000.00', '7000.00', '7000.00', '8000.00', 'MJYURFD', 'A004');
INSERT INTO
CUSTOMER VALUES ('C00009', 'Ramesh', 'Mumbai', 'Mumbai', 'India', '3',
'8000.00', '7000.00', '3000.00', '12000.00', 'Phone No', 'A002');
INSERT INTO CUSTOMER
VALUES ('C00014', 'Rangarappa', 'Bangalore', 'Bangalore', 'India', '2',
'8000.00', '11000.00', '7000.00', '12000.00', 'AAAATGF', 'A001');
INSERT INTO
CUSTOMER VALUES ('C00016', 'Venkatpati', 'Bangalore', 'Bangalore', 'India',
'2', '8000.00', '11000.00', '7000.00', '12000.00', 'JRTVFDD', 'A007');
INSERT INTO
CUSTOMER VALUES ('C00011', 'Sundariya', 'Chennai', 'Chennai', 'India', '3',
'7000.00', '11000.00', '7000.00', '11000.00', 'PPHGRTS', 'A010');
CREATE TABLE "ORDERS"
(
"ORD_NUM" NUMBER(6,0) NOT
NULL PRIMARY KEY,
"ORD_AMOUNT" NUMBER(12,2) NOT
NULL,
"ADVANCE_AMOUNT" NUMBER(12,2)
NOT NULL,
"ORD_DATE" DATE NOT NULL,
"CUST_CODE" VARCHAR2(6) NOT
NULL REFERENCES CUSTOMER,
"AGENT_CODE" CHAR(6) NOT NULL
REFERENCES AGENTS,
"ORD_DESCRIPTION"
VARCHAR2(60) NOT NULL
);
INSERT INTO
ORDERS VALUES('200100', '1000.00', '600.00', '08-JAN-08', 'C00013', 'A003',
'SOD');
INSERT INTO
ORDERS VALUES('200110', '3000.00', '500.00', '04-FEB-08', 'C00019', 'A010',
'SOD');
INSERT INTO
ORDERS VALUES('200107', '4500.00', '900.00', '08-MAR-08', 'C00007', 'A010',
'SOD');
INSERT INTO
ORDERS VALUES('200112', '2000.00', '400.00', '05-APR-08', 'C00016', 'A007',
'SOD');
INSERT INTO ORDERS
VALUES('200113', '4000.00', '600.00', '06-MAY-08', 'C00022', 'A002', 'SOD');
INSERT INTO
ORDERS VALUES('200102', '2000.00', '300.00', '05-JUN-08', 'C00012', 'A012',
'SOD');
INSERT INTO
ORDERS VALUES('200114', '3500.00', '2000.00', '08-JUL-08', 'C00002', 'A008',
'SOD');
INSERT INTO
ORDERS VALUES('200122', '2500.00', '400.00', '09-AUG-08', 'C00003', 'A004',
'SOD');
INSERT INTO
ORDERS VALUES('200118', '500.00', '100.00', '07-SEP-08', 'C00023', 'A006',
'SOD');
INSERT INTO
ORDERS VALUES('200119', '4000.00', '700.00', '09-OCT-08', 'C00007', 'A010',
'SOD');
INSERT INTO
ORDERS VALUES('200121', '1500.00', '600.00', '09-NOV-08', 'C00008', 'A004',
'SOD');
INSERT INTO
ORDERS VALUES('200130', '2500.00', '400.00', '07-DEC-08', 'C00025', 'A011',
'SOD');
INSERT INTO ORDERS
VALUES('200134', '4200.00', '1800.00', '09-JAN-08', 'C00004', 'A005', 'SOD');
INSERT INTO
ORDERS VALUES('200108', '4000.00', '600.00', '02-FEB-08', 'C00008', 'A004',
'SOD');
INSERT INTO
ORDERS VALUES('200103', '1500.00', '700.00', '05-MAR-08', 'C00021', 'A005',
'SOD');
INSERT INTO
ORDERS VALUES('200105', '2500.00', '500.00', '07-APR-08', 'C00025', 'A011',
'SOD');
INSERT INTO
ORDERS VALUES('200109', '3500.00', '800.00', '07-MAY-08', 'C00011', 'A010',
'SOD');
INSERT INTO
ORDERS VALUES('200101', '3000.00', '1000.00', '07-JUN-08', 'C00001', 'A008',
'SOD');
INSERT INTO
ORDERS VALUES('200111', '1000.00', '300.00', '07-JUL-08', 'C00020', 'A008',
'SOD');
INSERT INTO
ORDERS VALUES('200104', '1500.00', '500.00', '03-AUG-08', 'C00006', 'A004',
'SOD');
INSERT INTO ORDERS
VALUES('200106', '2500.00', '700.00', '04-SEP-08', 'C00005', 'A002', 'SOD');
INSERT INTO
ORDERS VALUES('200125', '2000.00', '600.00', '10-OCT-08', 'C00018', 'A005',
'SOD');
INSERT INTO
ORDERS VALUES('200117', '800.00', '200.00', '10-NOV-08', 'C00014', 'A001',
'SOD');
INSERT INTO
ORDERS VALUES('200123', '500.00', '100.00', '09-DEC-08', 'C00022', 'A002',
'SOD');
INSERT INTO
ORDERS VALUES('200120', '500.00', '100.00', '07-JAN-08', 'C00009', 'A002',
'SOD');
INSERT INTO
ORDERS VALUES('200116', '500.00', '100.00', '07-FEB-08', 'C00010', 'A009',
'SOD');
INSERT INTO
ORDERS VALUES('200124', '500.00', '100.00', '06-MAR-08', 'C00017', 'A007',
'SOD');
INSERT INTO
ORDERS VALUES('200126', '500.00', '100.00', '06-APR-08', 'C00022', 'A002',
'SOD');
INSERT INTO
ORDERS VALUES('200129', '2500.00', '500.00', '07-MAY-08', 'C00024', 'A006',
'SOD');
INSERT INTO
ORDERS VALUES('200127', '2500.00', '400.00', '07-JUN-08', 'C00015', 'A003',
'SOD');
INSERT INTO
ORDERS VALUES('200128', '3500.00', '1500.00', '07-JUL-08', 'C00009', 'A002',
'SOD');
INSERT INTO
ORDERS VALUES('200135', '2000.00', '800.00', '09-AUG-08', 'C00007', 'A010',
'SOD');
INSERT INTO
ORDERS VALUES('200131', '900.00', '150.00', '08-SEP-08', 'C00012', 'A012',
'SOD');
INSERT INTO
ORDERS VALUES('200133', '1200.00', '400.00', '06-OCT-08', 'C00009', 'A002',
'SOD');
Sample Table File_2
Set FRA in Oracle RAC Database
Setting Fast Recovery Area (FRA) and Enabling archive log in Oracle RAC Database
aSS
1- Check the database status: srvctl status database -d ORCL
Instance ORCL1 is running on node rac-node1
Instance ORCL2 is running on node rac-node2
2- Create arch directory in ASM disk location (+DATA)
$ asmcmd
asmcmd> lsdg
asmcmd> cd +DATA
asmcmd> mkdir arch
3- If database is up then connect to any instance of database and run the following query to set the parameter:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH SID='*';
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+Data/arch' SCOPE=BOTH SID='*';
4- Stop the database : srvctl stop database -d ORCL
5- Start database in mount state : srvctl start database -d ORCL -o mount
6- Connect to datbase and enable archive log:
sqlplus / as sysdba
alter database archivelog;
7- Stop the database :srvctl stop database -d ORCL
8- Start database and check archive log is enabled or not
$ srvctl start database -d ORCL
$ sqlplus / as sysdba
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 24
Next log sequence to archive 25
Current log sequence 25
9- Check archive is generating or not
SQL> alter system switch logfile;
Go to the location '+Data/arch' in asmcmd and type lsdg
aSS
1- Check the database status: srvctl status database -d ORCL
Instance ORCL1 is running on node rac-node1
Instance ORCL2 is running on node rac-node2
2- Create arch directory in ASM disk location (+DATA)
$ asmcmd
asmcmd> lsdg
asmcmd> cd +DATA
asmcmd> mkdir arch
3- If database is up then connect to any instance of database and run the following query to set the parameter:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH SID='*';
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+Data/arch' SCOPE=BOTH SID='*';
4- Stop the database : srvctl stop database -d ORCL
5- Start database in mount state : srvctl start database -d ORCL -o mount
6- Connect to datbase and enable archive log:
sqlplus / as sysdba
alter database archivelog;
7- Stop the database :srvctl stop database -d ORCL
8- Start database and check archive log is enabled or not
$ srvctl start database -d ORCL
$ sqlplus / as sysdba
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 24
Next log sequence to archive 25
Current log sequence 25
9- Check archive is generating or not
SQL> alter system switch logfile;
Go to the location '+Data/arch' in asmcmd and type lsdg
Setting Up FRA in Oracle Database
To enable the flash recovery area(FRA), you must set the two initialization parameters DB_RECOVERY_FILE_DEST_SIZE (which specifies the disk quota, or maximum space to use for flash recovery area files for this database) and
DB_RECOVERY_FILE_DEST (which specifies the location of the flash recovery area).
Note:
DB_RECOVERY_FILE_DEST (which specifies the location of the flash recovery area).
Note:
- DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST in parameter file.
- In a RAC database, all instances must have the same values for these parameters
SELECT dest_name, status, destination FROM v$archive_dest;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u02/oracle/fra';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH SID='*';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/disk1/flash_recovery_area' SCOPE=BOTH SID='*';
If FRA is ASM Disk Group
--------------------------
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+disk1' SCOPE=BOTH SID='*';
Startup mount
alter database close;
Alter database archivelog;
alter database open;
archive log list;
10.10.10.73
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH ;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u06/FRA/DCLUATarch' SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH ;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u06/FRA/dg731122arch' SCOPE=BOTH ;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH ;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u06/FRA/dg731211arch' SCOPE=BOTH;
dg731211
10.10.10.89
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/install/EBSUAT/EBSarch' SCOPE=BOTH;
41-RAC
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH SID='*';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+OCRVFDG' SCOPE=BOTH SID='*';
srvctl stop database -d ORCL
srvctl start database -d ORCL -o mount
alter database archivelog;
srvctl stop database -d ORCL
srvctl start database -d ORCL
10.10.10.74
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u04/ARCH/DG731124' SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u04/ARCH/DG731212' SCOPE=BOTH;
----- 10.10.10.73
Instance FRA_LOCATION SIZE ArchiveLog
--------------------------------------------------------------
DCLUAT /u06/FRA/DCLUAT 3G ENABLED
dg731122 /u06/FRA/dg731122arch 3G ENABLED
dg731211 /u06/FRA/dg731211arch 3G ENABLED
----- 10.10.10.41
Instance FRA_LOCATION SIZE ArchiveLog
-----------------------------------------------------------------
ORCL +OCRVFDG 3G ENABLED
----- 10.10.10.74
Instance FRA_LOCATION SIZE ArchiveLog
--------------------------------------------------------------
DG731124 /u04/ARCH/DG731124 3G ENABLED
DG731212 /u04/ARCH/DG731212 3G ENABLED
Dataguard -1
10.10.10.73 primary db:
10.10.10.74 standby
parallel from 41 have to take backup of EBSUAT in /mnt
shift db in archive log and take rman backup in mount state in location of /mnt
dataguard -2
primary 10.10.10.41
standby 10.10.10.89 db has been dropped.
EBSUAT database env file location : . /u01/app/oracle/product/11.2.0/EBSUAT_rac-node1.env
FILE_NAME
--------------------------------------------------------------------------------
+OCRVFDG/ebsuat/data/system17.dbf.
Logfile Maintenance in PostgreSQL
Logfile Maintenance in PostgreSQL
- Save the database server's log output somewhere,
- Need to "rotate" the log files so that new log files are started and old ones removed after a reasonable period of time.
- Only way to truncate the log file is to stop and restart the server.
- Better approach is to send the server's stderr output to some type of log rotation program. There is a built-in log rotation program, which you can use by setting the configuration parameter logging_collector to true in postgresql.conf
- To managing log output is to send it all to syslog and let syslog deal with file rotation. To do this, set the configuration parameter log_destination to syslog (to log to syslog only) in postgresql.conf
Patch in Oracle Database
Patch is a piece of software or code designed to fix the problems of the existing software. These fixes can be security vulnerability fixes or bug fixes. In certain cases the patches increases or enhances the functionality of the software as well
Interim patch
Interim patches are also known as “one of patches”. Interim patches are released in between the release of CPU or PSU patch to fix a specific issue. These patches generally address specific bug’s fixes for a specific customer and should not be applied unless specified by Oracle support services.
Diagnostic patch
It’s a type of interim patch used for the diagnosis of a specific issue. As the name indicates this patches doesn’t fix an issue but helps us to understand an issue it is mostly used by Oracle support services.
Bundle patch
It’s a cumulative patch issued between patch set bundles. patches normally include only fixes but sometimes they include minor enhancements as well. Example Windows bundle patch
Critical patch update (CPU)
These are the cumulative patches consisting of security fixes. The new name for the critical patch updates is security patch update. CPU or SPU provide the security fixes for a single product eg: Oracle Database 11.2.0.1. These patches are cumulative for the same product or a component For example Oracle database 11.2.0.1
Critical patch updates or CPUs are released on quarterly basis.
Mostly they are released on the third week of January, April, July and October.
Patching in Oracle Databases using Opatch Utility
- OPatch is an Oracle-supplied utility that assists you with the process of applying interim patches to Oracle's software and rolling back interim patches from Oracle's software.
- Patches are a small collection of files copied over to an existing installation and associated with particular versions of Oracle products.
- Interim patches are bug fixes available to customers in response to specific bugs and They require a particular base release or patchset to be installed before you can apply them, generally address specific bugs for a particular customer.
- OPatch also supports silent operation. This mode allows you to run the software without any user interaction.
- OPatch has various levels of logging and tracing mechanisms. It also has a debug option that helps to easily diagnose software problems.
OPatch supports the following tasks
1- Applying an interim patch.
2- Rolling back the application of an interim patch.
3- Detecting a conflict when applying an interim patch after previous interim patches have been applied. It also suggests the best options to resolve a conflict.
4- Reporting on installed products and interim patches.
OPatch uses the following environment variables
ORACLE_HOME — Oracle home location.
export ORACLE_HOME=/u04/oracle/product/database/12102
PATH — Path information.
export PATH=$ORACLE_HOME/OPatch:$PATH
Required:
Oracle Home must to set to valid location
Java commands for Windows, and ar, cp, fuser, and make commands for UNIX must be made available
Prerequisite Checks for OPatch
1- Check ORACLE_HOME and Environment Variable
2- Check for JRE
3- Check for Oracle Universal Installer and OPatch Version Compatibility
4- Check for OPatch Lsinventory : OPatch lsinventory -detail
Note: It is highly recommended before applying the patch , database must be down and back up the ORACLE_HOME before any patch operation
[oracle@dgprim2 ~]$ opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.13
Copyright (c) 2019, Oracle Corporation. All rights reserved.
Oracle Home : /u04/oracle/product/database/12102
Central Inventory : /u03/app/oracle/oraInventory
from : /u04/oracle/product/database/12102/oraInst.loc
OPatch version : 12.2.0.1.13
OUI version : 12.1.0.2.0
Log file location : /u04/oracle/product/database/12102/cfgtoollogs/opatch/opatch2019-04-02_11-47-29AM_1.log
Lsinventory Output file location : /u04/oracle/product/database/12102/cfgtoollogs/opatch/lsinv/lsinventory2019-04-02_11-47-29AM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: dgprim2.transappdata.com
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 12c 12.1.0.2.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
Location of Opatch Utility: $ORACLE_HOME/OPatch
syntax: opatch [-help] [command] [-option]
help — Displays the help message for the command.
$ opatch -help
Apply Command for OUI-based Oracle Homes
opatch napply Command for OUI-based Oracle Homes
opatch napply <patch_location>
opatch napply <patch_location> -id 1,2,3
opatch napply <patch_location> -skip_subset -skip_duplicate
opatch napply <patch_location> -id 1,2,3 -skip_subset -skip_duplicate
u
Clone an Oracle Database using controlfile
Clone of Oracle Database with new database name using control file
Source Database Name: DG731124
Source Database Version: 11.2.0.4 Standard Edition
Source Datafile location: /u03/app/oracle/oradata/DG731124/
Source Logfile location: /u03/app/oracle/oradata/DG731124/
Target location of datafile and logfile: /u04/testdir/
Target Database name: TCLONE
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u03/app/oracle/oradata/DG731124/system01.dbf
/u03/app/oracle/oradata/DG731124/sysaux01.dbf
/u03/app/oracle/oradata/DG731124/undotbs01.dbf
/u03/app/oracle/oradata/DG731124/users01.dbf
/u03/app/oracle/oradata/DG731124/test011.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u03/app/oracle/oradata/DG731124/redo03.log
/u03/app/oracle/oradata/DG731124/redo02.log
/u03/app/oracle/oradata/DG731124/redo01.log
SQL> create pfile='/tmp/initTCLONE.ora' from spfile;
Add tablespace, user and table to test the cloning of the database.
Connect to source database
Create tablespace test01
SQL> create tablespace test01 datafile '/u03/app/oracle/oradata/DG731124/test011.dbf' size 15M;
Add user 'u11' to the database
SQL> create user u11 identified by ******;
SQL> grant connect ,resource , create session to u11;
SQL> alter user u11 quota unlimited on test01;
SQL> conn u11;
Now user can create database object.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
SQL> alter system switch logfile;
Create control file
SQL> alter database backup controlfile to trace;
SQL> shut immediate;
Go to the trace file location and rename recently create trace file as control.sql
cd /u03/app/oracle/diag/rdbms/dg731124/DG731124/trace
ls -lrt
cp DG731124_ora_12252.trc control.sql
Remove unwanted lines from the control file keep information like this:
--------------------------------------------------------------------------------------------------------
CREATE CONTROLFILE SET DATABASE "TCLONE" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u04/testdir/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u04/testdir/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u04/testdir/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u04/testdir/system01.dbf',
'/u04/testdir/sysaux01.dbf',
'/u04/testdir/undotbs01.dbf',
'/u04/testdir/users01.dbf',
'/u04/testdir/test011.dbf'
CHARACTER SET AL32UTF8
;
RECOVER DATABASE;
-- Database can now be opened normally.
--ALTER DATABASE OPEN;
--ALTER TABLESPACE TEMP ADD TEMPFILE '/u04/testdir/temp01.dbf'
-- SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
----------------------------------------------------------------------------------------------------------
1- Use set option in create control file to set new name of the database.
2- Replace the old path of datafile and logfile with new location.
3- Edit the initTCLONE.ora replace the path and create following directory as described in pfile.
4- Shut down the source database.
5- copy the datafile, logfile, control file and pfile from source to target location '/u04/testdir/
-------------------------------------------------------------------------------------------------------------
export the oracle_sid for new database instance
# export ORACLE_SID=TCLONE
Start database in nomount state using pfile:
SQL> startup nomount pfile='/u04/testdir/initTCLONE.ora';
Execute the control file
SQL> @/u04/testdir/control.sql
Control file created.
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1258136 generated at 03/29/2019 10:47:01 needed for thread 1
ORA-00289: suggestion :
/u04/testdir/fast_recovery_area/TCLONE/archivelog/2019_03_29/o1_mf_1_9_%u_.arc
ORA-00280: change 1258136 for thread 1 is in sequence #9
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u04/testdir/redo03.log
Log applied.
Media recovery complete.
Note: Apply the logfile until the Media recovery get completed.
SQL> alter database open resetlogs;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u04/testdir/temp01.dbf' SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Clone database TCLONE get created in different location.
SQL> select name from v$database;
NAME
---------
TCLONE
SQL> SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u04/testdir/system01.dbf
/u04/testdir/sysaux01.dbf
/u04/testdir/undotbs01.dbf
/u04/testdir/users01.dbf
/u04/testdir/test011.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u04/testdir/redo03.log
/u04/testdir/redo02.log
/u04/testdir/redo01.log
SQL> conn u11;
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
Subscribe to:
Posts
(
Atom
)
