ANTYNET.PL – Administracja Sieci

OPMNCTL (Oracle Process Management and Notification Server)
$ORACLE_AS_HOME/opmn/bin/opmnctl help
$ORACLE_AS_HOME/opmn/bin/opmnctl status help
$ORACLE_AS_HOME/opmn/bin/opmnctl status -l
$ORACLE_AS_HOME/opmn/bin/opmnctl stopall
$ORACLE_AS_HOME/opmn/bin/opmnctl startall

check AS version:
cat $ORACLE_AS_HOME/config/ias.properties | grep Version
$ORACLE_AS_HOME/OPatch/opatch lsinventory -detail

SELECT * FROM dba_db_links;
SELECT * FROM user_db_links;
DROP DATABASE LINK [link_name];
CREATE DATABASE LINK [link_name] CONNECT TO [user] IDENTIFIED BY [password] USING '[tns_alias]’;
SELECT count(*) FROM [email protected][link_name];

shrink undo tablespace:
* CREATE UNDO TABLESPACE [name] DATAFILE '[path]’ size [nr]G ;
* ALTER SYSTEM SET UNDO_TABLESPACE = [name];
* DROP TABLESPACE [old_name] including contents and datafiles;

check rollback status:
SELECT used_urec FROM v$transaction; –records
SELECT used_ublk FROM v$transaction; –blocks
SELECT time_remaning FROM v$session_longops WHERE sid = [sid];

create database manualy:


* create PFILE
main changes:
– db_name
– control_files
– audit_file_dest
– background_dump_dest
– user_dump_dest
– core_dump_dest

example init[dbname].ora

* create password file for new database:
orapwd file=orapw[dbname] password=[pass]

* add new database to listener.ora and tnsnames.ora,

* create dirs for new database file,

* create dirs for trace logs as admin with subdirectories: adump, udump, cdump, bdump,

* run script:

* run script:

find locks:

select 
  oracle_username
  os_user_name,
  locked_mode,
  object_name,
  object_type
from 
  v$locked_object a,dba_objects b
where 
  a.object_id = b.object_id 

select l1.sid, ' BLOCK ', l2.sid from v$lock l1, v$lock l2
  where l1.block =1 and l2.request > 0
  and l1.id1=l2.id1
  and l1.id2=l2.id2;

How to copy binaries to other server:

1. turn off database and all services

2. make backup ORACLE_HOME

in new server:
3. make dir ORACLE_HOME

4. set other system variables

5. put ORACLE_HOME from previous backup

6. set user and group ORACLE_HOME dir

7. remove .ora files from ORACLE_HOME/network/admin

8. run command:

ORACLE_HOME/oui/bin/runInstaller -clone -silent ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=new_Oracle_home_name

9. run script root.sh

10. try run dbca

podane za Oracle® Database Reference 10g Release 2 (10.2) Part Number B14237-04

Table B-1 Creating the Data Dictionary Scripts

Table B-2 Creating Additional Data Dictionary Structures

Table B-4 Upgrade and Downgrade Scripts

Table B-5 Java Scripts

how to clone database by RMAN:
it depends on recovering files from previous backup

1. create PFILE for new database based on PFILE from source database
   init[db_new].ora
   main changes:
   • db_name
   • control_files
   • audit_file_dest
   • background_dump_dest
   • user_dump_dest
   • core_dump_dest
2. create password file for new database:
   orapwd file=orapw[db_new] password=[pass]
3. add new database to listener.ora and tnsnames.ora,
4. create dirs for new database file,
5. create directories for trace logs as admin with subdirectories: adump, udump, cdump, bdump,
6. startup new auxiliary database in nomout mode(! then exit from your session because it will be hang on)
7. start rman with ORACLE_SID = auxiliary database:
8. at the auxiliary server run command:
   rman auxiliary / target sys/[sys_pass]@[targetdb_tnsalias] catalog rman/[rman_pass]@[catalogdb_tnsalias]
   you should get an output:
   connected to target database: [db_name] (DBID=[db_id])
   connected to recovery catalog database
   connected to auxiliary database: [db_name] (not mounted)
9. run script:
   run {
   set newname for datafile 4 to '/oracle/oradata/[db_new]/users01.dbf’;
   set newname for datafile 3 to '/oracle/oradata/[db_new]/sysaux01.dbf’;
   set newname for datafile 2 to '/oracle/oradata/[db_new]/undotbs01.dbf’;
   set newname for datafile 1 to '/oracle/oradata/[db_new]/system01.dbf’;
   set newname for datafile 5 to '/oracle/oradata/[db_new]/example01.dbf’;
   set newname for tempfile 1 to '/oracle/oradata/[db_new]/temp.dbf’;
   duplicate target database to [db_new]
   pfile=/oracle/product/10.2.0/db_1/dbs/init[db_new]aux.ora
   logfile
   ’/oracle/oradata/[db_new]/redo01.log’ size 50m,
   ’/oracle/oradata/[db_new]/redo02.log’ size 50m,
   ’/oracle/oradata/[db_new]/redo03.log’ size 50m;}

   to generate script above use sql at source db:
   spool clonescript.rcv
   select 'set newname for datafile '||file_id||’ to ”’||file_name||”’;’ from dba_data_files order by file_id;
   select 'set newname for tempfile '||file_id||’ to ”’||file_name||”’;’ from dba_temp_files order by file_id;
   spool off;
   and next modify output in vim:
   :% s/ora6\/oradata\/DBSRC/u01\/oradata\/DBAUX\/datafile
   or another script:

   run {
   set until time „to_date(’2010-07-02:01:00′,’yyyy-mm-dd:hh24:mi’)”;
   configure default device type to sbt;
   configure device type sbt parallelism 2;
   configure auxiliary channel 1 device type sbt parms 'ENV=(NB_ORA_SERV=server_name,NB_ORA_CLIENT=server_target_name)’;
   configure auxiliary channel 2 device type sbt parms 'ENV=(NB_ORA_SERV=server_name,NB_ORA_CLIENT=server_target_name)’;
   duplicate target database to [db_new]
   DB_FILE_NAME_CONVERT=('[target_path1]/’,'[aux_path]’,
   '[target_path2]/’,'[aux_path]’)
   pfile=[pfile_aux_path]/[db_new].ora
   logfile
   '[logfile_aux_path]/redo01.log’ size 100m,
   '[logfile_aux_path]/redo02.log’ size 100m,
   '[logfile_aux_path]/redo03.log’ size 100m;
   }

for more read documentation

how to add database to DBCA:
if you want to add database, which was made by hand „create database etc.” not automatically by dbca, you should update /etc/oratab