oracle blogs – Technology Geek

Queryable Patch Inventory New Features 12c

Posted on June 27, 2013December 23, 2018 by Osama Mustafa in Database

Again !!! Oracle 12c Under test , every time i tested it i discover new features, while i am testing today i found new folder under $ORACLE_HOME called QOPatch , for the first time when i opened this folder i only found two files,

Check the https://support.oracle.com note :

Oracle Database 12.1 : FAQ on Queryable Patch Inventory [ID 1530108.1]

from the name you can see it’s retrieve information using query, and to use query you have to get access to SQL Plus, this features allow you access to the OPatch information from within the database. This Package called DBMS_QOPATCH

Some of Attribute to use with this package :

GET_OPATCH_INSTALL_INFO
SET_CURRENT_OPINST
GET_OPATCH_LIST
IS_PATCH_INSTALLED
GET_OPATCH_DATA
GET_OPATCH_BUGS
GET_OPATCH_FILES
GET_OPATCH_COUNT
GET_OPATCH_PREQS
GET_OPATCH_OLAYS
PATCH_CONFLICT_DETECTION
GET_PENDING_ACTIVITY
GET_OPATCH_XSLT
GET_OPATCH_LSINVENTORY
GET_SQLPATCH_STATUS

Since this function used to get Opatch information within database then you have to access to SQL Plus

Check the below examples :

SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;

DBMS_QOPATCH.GET_OPATCH_LSINVENTORY()
——————————————————————————–

Another Examples :

SQL> select dbms_qopatch.GET_PENDING_ACTIVITY() from dual;
DBMS_QOPATCH.GET_PENDING_ACTIVITY()
——————————————————————————–

My Database still fresh and not patch yet So you will not find that much of information

Thank you

Osama mustafa

Data Pump impdp expdp NETWORK_LINK option

Posted on August 2, 2012December 23, 2018 by Osama Mustafa in Database

First what is the network_Link Option :

you can import the schema from source database to target database. One advantage of this option you don’t need export and import as it does the export and import in single shot from the source to destination. Also, the file system space is not needed to accommodate the huge dump files as we can directly import to target using network_link. It is very amazing option with data pump. You can take the backup of source database schema from another database and you can store in dump files in target location as well.

Examples One :

SQL> select name from v$database;

NAME

———

Production

SQL> show user

USER is “OSAMA”

SQL> select * from tab;

no rows selected

SQL> create table test1 as select * from all_objects;

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

—————————— ——- ———-

test1 TABLE

added a TNS entry (File location: $ORACLE_HOME/network/admin/tnsnames.ora) for production database in my source database box. Entry as below:

production =

(description =

(address =

(protocol = tcp)

(host = xxx.xxxx.xxx.xxx)

(port = 1521)

)

(connect_data =

(server = dedicated)

(sid = production)

)

Make Sure you test the connection using tnsping .

Connect to source database using sqlplus and create a database link to production database with osama user

SQL> create database link production connect to osama identified by osama using ‘production’;

Database link created.

SQL> select * from tab@production

2 ;

TNAME TABTYPE CLUSTERID

—————————— ——- ———-

test1 TABLE

import the osama schema of production database to source database without dumpfile(Run it From Source)

$ impdp osama/osama directory=network logfile=osama.log network_link=production

Import: Release 11.2.0.3.0 – 64bit Production on Tuesday, 24 July, 2012 01:30:35

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting “OSAMA”.”SYS_IMPORT_SCHEMA_01″: OSAMA/******** directory=exp_dir logfile=impnetworkscott.log network_link=prod8

Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 12 MB

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:”OSAMA” already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . imported “OSAMA”.”TEST1″ 11222 rows

Example Two :

we will export schema from source database from target machine. You can store the dump in files.

From Source Run (Copy to dump to folder network)

$ expdp osama/osama directory=network dumpfile=osama.dmp logfile=osama.log network_link=production

Export: Release 11.2.0.3.0 – 64bit Production on Tuesday, 24 July, 2012 02:05:09

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting “OSAMA”.”SYS_EXPORT_SCHEMA_01″: osama/******** directory=test dumpfile=osama.dmp logfile=osama.log network_link=production

Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 12 MB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . exported “OSAMA”.”TEST1″ 9.496 MB 11222rows

Master table “osama”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

******************************************************************************

Also Check
1-Ronny Egners Blog

Enjoy

Osama Mustafa

Table Locks

Posted on July 24, 2012December 23, 2018 by Osama Mustafa in Scripts

query to get the locked tables in oracle :

SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||’.’||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
DECODE(L.LOCKED_MODE, 0,’NONE’,
1,’NULL’,
2,’ROW SHARE’,
3,’ROW EXCLUSIVE’,
4,’SHARE’,
5,’SHARE ROW EXCLUSIVE’,
6,’EXCLUSIVE’,
NULL) LOCK_MODE
FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P
WHERE L.OBJECT_ID = O.OBJECT_ID
and l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
and s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by l.inst_id ;

Or you can do the below :

select * From v$locked_object;
select * From v$session where SID = ”;
select * from dba_objects where object_id = ”;

Locked objects :

Select object_name, owner, object_type from dba_objects
Where object_id in (select object_id from v$locked_object);

KILL SESSION COMMAND

ALTER SYSTEM KILL SESSION ‘SID,Serial#’ IMMEDIATE;

Osama Mustafa

Use TABLE_EXISTS_ACTION in Impdp

Posted on July 23, 2012December 23, 2018 by Osama Mustafa in Database

Those who are familiar with oracle data pump may know very well about TABLE_EXISTS_ACTION import (impdp) parameter.

TABLE_EXISTS_ACTION = {SKIP | APPEND | TRUNCATE | REPLACE}

This parameter is used when you import a table which is already exists in import schema. The default value is ‘SKIP‘, so if you not use this parameter and impdp found that the table which to be imported is already exist then impdp skip this table from import list.

Now you may interested about rest of the three values-

APPEND – The import will be done if the table does not have any Primary key or Unique key constraints. If such constraint exists then you need to ensure that append operation does not violate Primary key or Unique key constraints (that means it does not occur data duplication).

TRUNCATE – If the table is not a parent table ( i.e, No other table references it by creating foreign key constraint) then it truncate the existing data and load data from dump file. Otherwise data will not be loaded.

REPLACE – This is the most tricky value of TABLE_EXISTS_ACTION parameter. If the importing table is a parent table ( i.e, other table references it by creating foreign key constraint ) then the foreign key will be deleted from child table. All existing data will be replaced with imported data.

Shell Script For Cold Backup

Posted on July 22, 2012December 23, 2018 by Osama Mustafa in Scripts

This shell script selects the datafiles, logfiles and control files,

tars and gzips them and then sends them to a remote host via rsh.

Download Scripts : Cold_backup.sh

I have to upload the script since its contain codes can’t be appeared on Blog .

<eofsql 0="" 120="" <eofsql eofsql

Enjoy
osama mustafa

Manage Oracle Files / Rename Or Move Logfiles Part-2

Posted on July 22, 2012December 23, 2018 by Osama Mustafa in Database

In First Topic Part 1 we talked about how to move/Rename Control File Today the same topic but for different file LogFile Let start

SQL> SELECT member FROM v$logfile;

MEMBER
-------------------------------------------------
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\REDO03.LOG
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\REDO02.LOG
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\REDO01.LOG

3 rows selected.

SQL>

To move or rename a logfile do the following.

Shutdown the database.
Rename the physical file on the OS.
Start the database in mount mode.
Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
Open the database.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

Rename/Move logfile to what you want/where you want

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.

 ALTER DATABASE RENAME FILE 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\REDO01.LOG' -
>  TO 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\RENAME_REDO01.LOG';

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>

The Last topic will be About DataFiles .

Enjoy

Osama mustafa

ORA-1122 :database file %s failed verification check

Posted on July 19, 2012December 23, 2018 by Osama Mustafa in Database

After trying to make Partation on tables i received the following Error :

ORA-1122 :database file %s failed verification check
ORA-1110 : datafile :
ORA-1207: ORA 1207 file is more recent than control file

Solution

1.Mount the database
SQL> Startup mount

2. Save the information from the control file:
SQL> Alter database backup controlfile to trace;

3. Create a control file creation script from the tracefile generated in user_dump_dest.
Use the Noresetlogs option

4. Shutdown the database and start it in NOMOUNT mode

SQL> shutdown abort
SQL> startup nomount

5. Create the control file
6.Recover the database
SQL> recover database;

7. Open the database
SQL> Alter database open;

Thank you
Osama mustafa

Rename/Move Oracle Files / Control FIle Part 1

Posted on July 18, 2012December 23, 2018 by Osama Mustafa in Database

In this Topics i will post how to move control file to another detestation , i will post other ORACLE FILES :

SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL01.CTL
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL02.CTL
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL03.CTL

3 rows selected.

SQL>

OR

SQL> show parameter control_files

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
control_files                        string                           C:\ORACLE\ORADATA\DB10G\CONTRO
                                                                      L01.CTL, C:\ORACLE\ORADATA\ORCL
                                                                      \CONTROL02.CTL, C:\ORACLE\OR
                                                                      ADATA\ORCL\CONTROL03.CTL
SQL>

To move or rename a controlfile do the following.

Alter the control_files parameter using the ALTER SYSTEM comamnd.
Shutdown the database.
Rename the physical file on the OS.
Start the database.

SQL> ALTER SYSTEM SET control_files='C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\RENAME_CONTROL01.CTL', -
> 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL02.CTL', -
> 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL03.CTL' SCOPE=SPFILE;

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

RE-NAME CONTROL FILE TO THE SAME WE DID ON ALTER STATMENT .

SQL> STARTUP
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL>

SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\RENAME_CONTROL01.CTL
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL02.CTL
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL03.CTL

3 rows selected.

SQL>

the same steps for move control file .

We will continue

Enjoy 

osama mustafa

Script to Generate Grant in Database

Posted on July 15, 2012December 23, 2018 by Osama Mustafa in Scripts

Sometime when you want to create test environment you need to sure it will Identical and make sure grant are the same .

the below scripts include all grants made by user.
P.S :

For non-Windows platforms, change the second-last line, “host notepad”, to call your favorite text editor instead.
You will need to input a list of users.
If you don’t know the passwords of some of the users, wait until they’re not logged on, copy their encrypted passwords from dba_users, reset their passwords to a temporary value, run the generated script, and then reset their passwords to their original values using alter user … identified by values ‘…’
Before you run the generated script, you need to know the passwords of all the users who will be making grants. Search the generated script for “connect” to figure out who this is.

I upload the scripts as grant.sql

Enjoy

Osama Mustafa

ora-29540 class oracle/jpub/runtime/dbws/dbwsproxy does not exist

Posted on July 14, 2012December 23, 2018 by Osama Mustafa in Database

ERROR at line 1:
ORA-29540: class oracle/jpub/runtime/dbws/DbwsProxy does not exist
ORA-06512: at "SYS.UTL_DBWS", line 195
ORA-06512: at "INTERFACE.GET_JOKE", line 13

Logout of sqlplus and run:
loadjava -u / -r -v -f -s -grant public -genmissing  dbwsclientws.jar dbwsclientdb102.jar

Enjoy

osama mustafa