1) Import 11g data dump into 10g
You can use Oracle data dump to export data dump from 11g server, and import into 10g using the data Pump parameter called Version. When you use Version, Datapump exports all objects that are compatible for your target version
So, If your database is 11g and you want to export 11g and import into 10g
from 11g db ,
$ expdp Test_schema_name/passs directory=datapump schemas=Test_schema_name Version=10.2.0.4.0.
Once the export is done, you do the regular import from 10g server.
2) Import multiple dump files
If the size of the dump file is large, usually they will be split into smaller chunks for easier ftp upload/download.
If you are trying to import a dump file that is split into many chunks, then you need to modify the DUMPFILE paratermeter to include %U
Ex: If the dump files are named EXP_PROD_1.dmp, EXP_PROD_2.dmp etc , then DUMPFILE=EXP_PROD_%U.DMP
3) How to kill data pump jobs
When you import or export using data pump impdp or expdp commands, the import/export is done by a job. You have an option to provide a job name using JOB_NAME parameter too
Following sql will give you the list of data pump jobs
select * from dba_datapump_jobs
If you want to kill your impdp or expdp
1) Make sure that your impdp/expdp command prompt window is active
2) Press Control-C , It will pause the job. Don't press another Control-C or close the command prompt. This will just close the window, but the job will still be running in the background
3) Type Kill_Job
ex: Import> kill_job
Are you sure you wish to stop this job (y/n): y
If by mistake, you closed the window and your import/export job is still running,
1) Get the name of the job using
select * from dba_datapump_jobs
2) Open a new command prompt window. If you want to kill your import job type
impdp username/password@database attach=name_of_the_job
3) Once you are attached to job, Type Kill_Job
ex: Import> kill_job
Are you sure you wish to stop this job (y/n): y
And your job is killed, it will no longer show in dba_datapump_jobs
4) REUSE_DUMPFILE :( Overwrite existing dumpfile)
This is the option with data pump expdp utility. Normally when you perform the export using expdp utility and if the dumpfile is present in the export directory it will throw an error “ORA-27038: created file already exists”. This situation happens when you wanted to perform the repetitive exports using the same dumpfile. Oracle provides an option reuse_dumpfile=[Y/N] to avoid this error. You should mention the parameter value as Y to overwrite the existing dump file. By default the option considered as N.
$ expdp scott/tiger directory=exp_dir dumpfile = x.dmp table s= example reuse_dumpfiles = y
Showing posts with label 12c. Show all posts
Showing posts with label 12c. Show all posts
DBA SCRIPTS
1) DROP USER OBJECTS:
conn username/password --->>> MANDATORY to connect as a user
declare
cursor fkc is select table_name,
constraint_name
from user_constraints
where constraint_type ='R';
tname user_constraints.table_name%TYPE;
cname user_constraints.constraint_name%TYPE;
begin
open fkc;
loop
fetch fkc into tname, cname;
exit when fkc%NOTFOUND;
dbms_output.put_line('alter table '||tname||' drop constraint '||cname);
execute immediate 'alter table '||tname||' drop constraint '||cname;
end loop;
close fkc;
end;
/
declare
cursor fkc is select object_name,
object_type
from user_objects
where object_name not in
('INDEX','PACKAGE BODY');
obj_name user_objects.object_name%TYPE;
obj_type user_objects.object_type%TYPE;
begin
open fkc;
loop
fetch fkc into obj_name, obj_type;
exit when fkc%NOTFOUND;
dbms_output.put_line('Drop '||obj_type||' '||obj_name);
begin
execute immediate 'Drop '||obj_type||' '||obj_name;
exception
when others then null;
end;
end loop;
close fkc;
end;
/
PURGE RECYCLEBIN;
DECLARE
the_job user_jobs.job%TYPE;
cursor c1 is select job from user_jobs;
BEGIN
open c1;
loop
fetch c1 into the_job;
exit when c1%NOTFOUND;
dbms_job.remove(the_job);
end loop;
close c1;
END;
/
VERIFY :
set heading off
SQL > select 'Objects left in schema : ' from dual;
SQL> select object_name,object_type from user_objects;
SQL> select 'Jobs left in schema: ' from dual;
SQL> select job,what from user_jobs;
2) User privileges & Grantee :
SELECT grantee, privilege, admin_option
FROM sys.dba_sys_privs
WHERE (privilege LIKE '% ANY %'
OR privilege IN ('BECOME USER', 'UNLIMITED TABLESPACE')
OR admin_option = 'YES')
AND grantee NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE',
'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS',
'TIMESERIES_DBA')
3) Free Space :
//** This script lists all Tablespaces and its datafiles with their free and used space **//
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
SET LINESIZE 500
6) Data Pump Monitoring Script :
8) To see what USERS are Running :
SELECT a.sid, a.serial#, a.username, b.sql_text FROM v$session a, v$sqlarea b
WHERE a.sql_address=b.address;
**** To see for a particular USER, what he is running *******
SELECT a.sid, a.serial#, a.username, b.sql_text FROM v$session a, v$sqlarea b
WHERE a.sql_address=b.address and a.username = '<username>';
9) Find FREE/USED/TOTAL size of oracle database :
(used space):
10) RMAN Cold Backup :
conn username/password --->>> MANDATORY to connect as a user
declare
cursor fkc is select table_name,
constraint_name
from user_constraints
where constraint_type ='R';
tname user_constraints.table_name%TYPE;
cname user_constraints.constraint_name%TYPE;
begin
open fkc;
loop
fetch fkc into tname, cname;
exit when fkc%NOTFOUND;
dbms_output.put_line('alter table '||tname||' drop constraint '||cname);
execute immediate 'alter table '||tname||' drop constraint '||cname;
end loop;
close fkc;
end;
/
declare
cursor fkc is select object_name,
object_type
from user_objects
where object_name not in
('INDEX','PACKAGE BODY');
obj_name user_objects.object_name%TYPE;
obj_type user_objects.object_type%TYPE;
begin
open fkc;
loop
fetch fkc into obj_name, obj_type;
exit when fkc%NOTFOUND;
dbms_output.put_line('Drop '||obj_type||' '||obj_name);
begin
execute immediate 'Drop '||obj_type||' '||obj_name;
exception
when others then null;
end;
end loop;
close fkc;
end;
/
PURGE RECYCLEBIN;
DECLARE
the_job user_jobs.job%TYPE;
cursor c1 is select job from user_jobs;
BEGIN
open c1;
loop
fetch c1 into the_job;
exit when c1%NOTFOUND;
dbms_job.remove(the_job);
end loop;
close c1;
END;
/
VERIFY :
set heading off
SQL > select 'Objects left in schema : ' from dual;
SQL> select object_name,object_type from user_objects;
SQL> select 'Jobs left in schema: ' from dual;
SQL> select job,what from user_jobs;
2) User privileges & Grantee :
SELECT grantee, privilege, admin_option
FROM sys.dba_sys_privs
WHERE (privilege LIKE '% ANY %'
OR privilege IN ('BECOME USER', 'UNLIMITED TABLESPACE')
OR admin_option = 'YES')
AND grantee NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE',
'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS',
'TIMESERIES_DBA')
3) Free Space :
//** This script lists all Tablespaces and its datafiles with their free and used space **//
SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET FEEDBACK OFF
SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,40) "File Name",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(e.used_bytes/1024/1024,2) "Used (M)",
Round(f.free_bytes/1024/1024,2) "Free (M)",
Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
FROM DBA_DATA_FILES df,
(SELECT file_id, Sum(Decode(bytes,NULL,0,bytes)) used_bytes
FROM dba_extents GROUP by file_id) e,
(SELECT Max(bytes) free_bytes, file_id
FROM dba_free_space GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name, df.file_name;
4) Tablespaces :
//** This script lists all Tablespaces with their Sizes **//
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
5) Locked_objects :
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
SET PAGESIZE 14
SET VERIFY ON
6) Data Pump Monitoring Script :
select sid, serial#, sofar, totalwork, dp.owner_name, dp.state, dp.job_mode
from gv$session_longops sl, gv$datapump_job dp
where sl.opname = dp.job_name and sofar != totalwork;
7) RMAN Job Monitoring Script :
SQL > SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0 AND SOFAR <> TOTALWORK ;
To see the Rman status and SID:
SQL > SELECT s.SID, p.SPID, s.CLIENT_INFO FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR AND CLIENT_INFO LIKE 'rman%';
8) To see what USERS are Running :
SELECT a.sid, a.serial#, a.username, b.sql_text FROM v$session a, v$sqlarea b
WHERE a.sql_address=b.address;
**** To see for a particular USER, what he is running *******
SELECT a.sid, a.serial#, a.username, b.sql_text FROM v$session a, v$sqlarea b
WHERE a.sql_address=b.address and a.username = '<username>';
9) Find FREE/USED/TOTAL size of oracle database :
(used space):
----------------
SQL> select sum(bytes)/1024/1024/1024 GB from dba_segments;
(free space):
---------------
SQL> select sum(bytes)/1024/1024/1024 GB from dba_free_space;
(Total database size):
---------------------------
SQL> select sum(bytes)/1024/1024/1024 GB from dba_data_files;
+
(Temp size):
---------------
SQL> select SUM(bytes_used)/1024/1024/1024 GBused, SUM(bytes_free)/1024/1024/1024 GBfree from v$temp_space_header;
(or)
SELECT SUM (a.log_space + b.data_space + c.tempspace) "Total_DB_Size (G)"
FROM (SELECT ROUND (SUM (BYTES/1024/1024/1024), 2) data_space FROM dba_data_files) b,(SELECT ROUND (SUM (BYTES*members/1024/1024/1024), 2) log_space FROM v$log) a, (SELECT NVL(ROUND(SUM(BYTES/1024/1024/1024),2), 0) tempspace
FROM dba_temp_files) c;
FROM (SELECT ROUND (SUM (BYTES/1024/1024/1024), 2) data_space FROM dba_data_files) b,(SELECT ROUND (SUM (BYTES*members/1024/1024/1024), 2) log_space FROM v$log) a, (SELECT NVL(ROUND(SUM(BYTES/1024/1024/1024),2), 0) tempspace
FROM dba_temp_files) c;
10) RMAN Cold Backup :
Rman target / nocatalog
Rman > configure controlfile autobackup on;
Rman > run {
2> allocate channel d1 type disk;
3> Backup full tag full_offline_bkup
4> Format ‘/var/backup/corppsdb/rman/db_t%t_s%s_p%p’
5> Database plus archivelog;
6> Release channel d1;
}
Find more scripts here : http://www.oracle-base.com/dba/Scripts.php
How to get port number list of EM and isqlplus
How to get port number list of EM and isqlplus
You can get your portlist information of enterprise manger or isqlplus or others web services in the location of $ORACLE_HOME/install/portlist.ini.
On my system the output is,
bash-3.00$ cat /oracle/app/oracle/product/10.2.0/db_1/install/portlist.ini
iSQL*Plus HTTP port number =5560
Enterprise Manager Console HTTP Port (DBASE) = 1158
Enterprise Manager Agent Port (DBASE) = 3938
Enterprise Manager Console HTTP Port (arjudba) = 5500
Enterprise Manager Agent Port (arjudba) = 1830
Enterprise Manager Console HTTP Port (arju) = 5501
Enterprise Manager Agent Port (arju) = 1831
But note that the ports that are listed in portlist.ini file were assigned during the installation. This file is not updated if port numbers are changed after the installation.
On my system the output is,
bash-3.00$ cat /oracle/app/oracle/product/10.2.0/db_1/install/portlist.ini
iSQL*Plus HTTP port number =5560
Enterprise Manager Console HTTP Port (DBASE) = 1158
Enterprise Manager Agent Port (DBASE) = 3938
Enterprise Manager Console HTTP Port (arjudba) = 5500
Enterprise Manager Agent Port (arjudba) = 1830
Enterprise Manager Console HTTP Port (arju) = 5501
Enterprise Manager Agent Port (arju) = 1831
But note that the ports that are listed in portlist.ini file were assigned during the installation. This file is not updated if port numbers are changed after the installation.
Oracle Database Link to MS Access
Oracle Database Link to MS Access
In this post, I describe how you can make an Oracle database link to an MS Access database.
When done, you can query from an oracle database onto an Access database.
But with some little changes, you can do the same trick to a SQL Server database or even a
database type you like (to which you can connect by ODBC).
Important: The init-parameter GLOBAL_NAMES should have value FALSE. Check this parameter first
before continuing the procedure.
In this example, I uses TESTDB as database name, replace this with your database/name/user id.
If I mention %oracle_home%, I mean the Oracle Home directory.
This example is worked out on an Oracle 8.1.7.4 installation,
but I suppose it will work on higher Oracle versions too.
1. Open the file inithsodbc.ora (%oracle_home%/hs/admin) and save this as initTESTDB.ora. Edit the content so it looks like:
2. Open the file listener.ora (%oracle_home%/network/admin) and add the following text, save and close the file:
3. Open the file tnsnames.ora (%oracle_home%/network/admin) and add the following text, save and close the file:
4. Check the file sqlnet.ora (%oracle_home%/network/admin) if the following setting is correct (correct if it differs):
5. Start the new TESTDB listener with the following command (on the command line tool):
Starting the new listener can produce an error message, because the services uses the default logon settings (system account).
6. Configure the ListenerTestDb:
If the test succeeded, the database link in Oracle can be made.
1. Create database link to TESTDB (note: credentials SYSTEM/MANGER can be changed to your configuration):
2. Test the link with a count(*) query to a table on the TESTDB database.
If you got a number of rows back, you're done!
When done, you can query from an oracle database onto an Access database.
But with some little changes, you can do the same trick to a SQL Server database or even a
database type you like (to which you can connect by ODBC).
Important: The init-parameter GLOBAL_NAMES should have value FALSE. Check this parameter first
before continuing the procedure.
In this example, I uses TESTDB as database name, replace this with your database/name/user id.
If I mention %oracle_home%, I mean the Oracle Home directory.
This example is worked out on an Oracle 8.1.7.4 installation,
but I suppose it will work on higher Oracle versions too.
1. Create a new ODBC link
- On the Oracle server start the ODBC Data Source Administrator. This can be done by using the run command: go to start/run and type odbcad32 and press enter.
- In the ODBC Data Source Administrator window go to the System DSN tab and click on Add...
- Choose in the Create New Data Source window the Microsoft Access Driver (*.mdb) and click on Finish.
- In the ODBC Microsoft Access Setup window enter the data source name (e.g. TESTDB), the description (e.g. Test database) and select the Access database
- If the Access database is on the same machine as Oracle is running, you can ignore step 1.5 and step 2!
- If the Access database is on another machine in the network, you should use UNC-path notation. In the Map Network Drive window, leave the drive-letter on (none) and select via the UNC-path the Access database. Finish the creation of the ODBC Link and close all the ODBC windows.
2. Create Network ID
In order to perform a good connection to the Access database on another machine, you need to follow the following steps:- Create on the network an user id (e.g. TestDbAdmin) and give this UID read rights on the location of the Access files. NOTE: use the appropriate rights, this depends on the actions you want to take on the Access database.
- Place this user, TestDbAdmin, in the ORA_DBA group on the Oracle server.
3. Create the Oracle TestDb listener
In these steps, some Oracle files will be created and/or modified. A new listener will be created, which will take care of the connection1. Open the file inithsodbc.ora (%oracle_home%/hs/admin) and save this as initTESTDB.ora. Edit the content so it looks like:
01.# This is a sample agent init file that contains the HS parameters that02.# are needed for an ODBC Agent.03.#04.# HS init parameters05.#06.HS_FDS_CONNECT_INFO = HORSODBC07.HS_FDS_TRACE_LEVEL = OFF08.#09.# Environment variables required for the non-Oracle system10.#11.#set <envvar>=<value>2. Open the file listener.ora (%oracle_home%/network/admin) and add the following text, save and close the file:
01.LISTENERTESTDB = 02.(ADDRESS_LIST= 03.(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)) 04.(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)) 05.) 06.SID_LIST_LISTENERTESTDB= 07.(SID_LIST= 08.(SID_DESC= 09.(SID_NAME=TESTDB) 10.(ORACLE_HOME = C:\oracle\ora81) 11.(PROGRAM=hsodbc) 12.) 13.)3. Open the file tnsnames.ora (%oracle_home%/network/admin) and add the following text, save and close the file:
1.TESTDB = 2.(DESCRIPTION = 3.(ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1522)) 4.(CONNECT_DATA = 5.(SID = TESTDB) 6.) 7.(HS = OK) 8.)4. Check the file sqlnet.ora (%oracle_home%/network/admin) if the following setting is correct (correct if it differs):
1.SQLNET.AUTHENTICATION_SERVICES= (NONE)5. Start the new TESTDB listener with the following command (on the command line tool):
Orahome\bin\lsnrctl start listenerTestDb
NOTE: DON'T USE A RDP (REMOTE DESKTOP) CONNECTION! Because of windows credential error. Apply this directly on the Oracle machine or via e.g. VNC Viewer connection.
Starting the new listener can produce an error message, because the services uses the default logon settings (system account).
6. Configure the ListenerTestDb:
- Open the properties panel of this service (can be found in the windows services list at the administrative tools)
- Stop the service (if it is running) and edit the log on credentials to the user account earlier made (e.g. TestDbAdmin)
- (Re)start the service.
1.%oracle_home%\bin\tnsping testdbIf the test succeeded, the database link in Oracle can be made.
4. Create Oracle Database Link to TestDb
1. Create database link to TESTDB (note: credentials SYSTEM/MANGER can be changed to your configuration):
1.CREATE PUBLIC DATABASE LINK “TESTDB” 2.CONNECT TO "SYSTEM" 3.IDENTIFIED BY "MANAGER"4.USING 'TESTDB'2. Test the link with a count(*) query to a table on the TESTDB database.
If you got a number of rows back, you're done!
Subscribe to:
Posts (Atom)