Fake Blogger

CREATE DATABASE LINK CONTROL.BMC.COM
CONNECT TO user
IDENTIFIED BY password
USING ‘connection’;
CREATE SYNONYM bla for bla@bla;
ALTER DATABASE RENAME GLOBAL_NAME TO sales.us.oracle.com;

1. set old Oracle SID:
set ORACLE_SID=(SID)
or
export ORACLE=(SID);
2. RMAN
rman
connect target /
connect catalog user/pw@catalogdb
set DBID;
startup force nomount
@re1.rcv
re1.rcv:
run{
allocate channel t1 type ‘SBT_TAPE’
PARMS ‘ENV=(NSR_SERVER=<backup-server>,NSR_DATA_VOLUME_POOL=<poolname>)’;
set command id to ‘bkrs’;
restore spfile;
release channel t1;
}
@re2.rcv
re2.rcv:
run{
allocate channel t1 type ‘SBT_TAPE’
PARMS ‘ENV=(NSR_SERVER=<backup-server>,NSR_DATA_VOLUME_POOL=<poolname>)’;
set command id to ‘bkrs’;restore controlfile;
release channel t1;
}
shutdown
sqlplus “/ as sysdba”
create pfile from spfile;
edit pfile and change path and name of controlfile:
source for controlfile […]

CREATE TEMPORARY TABLESPACE TEMP1
TEMPFILE ‘/u1/<Pfad>/<sid>/temp11.dbf’
SIZE 100M AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE ‘/u1/<Pfad>/<sid>/temp01.dbf’
SIZE 2048M AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
DROP TABLESPACE TEMP1 INCLUDING CONTENTS AND DATAFILES;

tnsnames.ora
ORCLTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac3vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)

Falls noch möglich, ist die letzte Online Redo Log Datei in der bisherigen Produktionsdatenbank zu archivieren.
Dies kann mit dem Befehl:
alter system archive log current;
erfolgen.
Falls die Standbydatenbank im “Manual Recovery Mode” aktiviert ist, muss versucht werden, die noch nicht angewandten oder transferierten Archive Log Dateien auf den Standby-Rechner zu kopieren und dort in der Standbydatenbank […]

Erstellen einer Kopie der Produktionsdatenbank PROD
Beispiel:
Bestimmen der Datenbankdateien:
select name from v$datafile;
NAME
————————————-
/u01/oradata/PROD/system01.dbf
/u01/oradata/PROD/temp01.dbf
/u01/oradata/PROD/rbs01.dbf
/u01/oradata/PROD/users01.dbf
/u01/oradata/PROD/index01.dbf
/u01/oradata/PROD/tools01.dbf
/u02/oradata/PROD/users02.dbf
Datenbank stoppen:
shutdown immediate
Datenbankdateien im Betriebssystem kopieren:
cp /u01/oradata/PROD/*dbf /backup
cp /u02/oradata/PROD/*dbf /backup
Datenbank starten:
startup
Erstellen einer Steuerdatei für die Standbydatenbank STBY
alter database create standby controlfile as ‘/backup/standby_control.ctl’;
Wichtig ist, dass diese Steuerdatei nach der Kopie der Dateien der Produktionsdatenbank erstellt wurde.
Kopieren der Datenbankdateien auf den Rechner mit der Standbydatenbank
Falls auf […]

Beispiele:
Wie lautet der aktuelle Benutzer:
SQL> SELECT user FROM dual;
USER
——————
c0ffee
Aktuelle Uhrzeit:
SQL> SELECT sysdate FROM dual;
SYSDATE
——————-
30.07.2003 15:02:54
Ist der aktuelle Benutzer als SYSDBA angemeldet ?
SQL> SELECT userenv(’ISDBA’) as SYSDBA from dual;
SYSDBA
——
FALSE
Aktuelles Terminal von dem man arbeitet?
SQL> SELECT userenv(’TERMINAL’) as “Term” from dual;
Term
———
GOOFY
Aktuelle Einstellung der NLS-Parameter
SQL> SELECT userenv(’LANGUAGE’) as “NLS” from dual;
NLS
——————————————
GERMAN_GERMANY.WE8ISO8859P1
CLIENT_INFO Gibt 64 Byte √ºber die Usersession zur√ºck, […]

Als erstes wird überprüft, wie viele Blöcke und Extents von der Tabelle allokiert sind.
Select BLOCKS, EXTENTS from USER_SEGMENTS where SEGMENT_NAME = ‘BIGEMP’;
BLOCKS EXTENTS
—— ———-
45696 116
Als weitere Ausgangswerte dienen die leeren Bl√∂cke und der durchschnittliche freie Platz innerhalb eines […]

DECLARE
JOB_ID NUMBER := 1;
BEGIN
EXECUTE IMMEDIATE(’ALTER USER TEST ACCOUNT LOCK’);
DBMS_JOB.SUBMIT( JOB_ID, ‘EXECUTE IMMEDIATE ”DROP USER TEST CASCADE”;’ );
END;
/

Oracle 10g Datapump for Export (expdp):
CREATE DIRECTORY export_dumps AS ‘c:\a’;
GRANT read, write ON DIRECTORY export_dumps TO gennick;

PAR-FILE:
DUMPFILE=gnis%U.dmp
DIRECTORY=export_dumps
LOGFILE=gnis_export.log
JOB_NAME=gnis_export
select * from all_directories;
DUMPFILE specifies the file to which I want to write exported data. The %U syntax gives me an incrementing counter, resulting in the filenames gnis01.dmp, gnis02.dmp, and so forth. DIRECTORY specifies my target directory.Note that if […]

select comp_id, comp_name, version,status from dba_registry;

Create the services “OracleService<Sid>” and the “OracleStart<Sid>” for “NEW_SID” with the following command:
oradim -new -sid <NEW_SID> -intpwd <password> -startmode auto -pfile <path_name>

select TABLESPACE_NAME,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
CONTENTS
from dba_tablespaces
order by TABLESPACE_NAME;

Oracle-Tablespace Coalesced Exts:
set linesize 2000
select TABLESPACE_NAME,
TOTAL_EXTENTS,
EXTENTS_COALESCED,
PERCENT_EXTENTS_COALESCED,
TOTAL_BYTES,
BYTES_COALESCED,
TOTAL_BLOCKS,
BLOCKS_COALESCED,
PERCENT_BLOCKS_COALESCED
from dba_free_space_coalesced
order by TABLESPACE_NAME;

column size_for_estimate format 999,999,999,999 heading ‘Cache Size (m)’
column buffers_for_estimate format 999,999,999 heading ‘Buffers’
column estd_physical_read_factor format 999.90 heading ‘Estd Phys|Read Factor’
column estd_physical_reads format 999,999,999 heading ‘Estd Phys| Reads’
SELECT size_for_estimate
, buffers_for_estimate
, estd_physical_read_factor
, estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = ‘DEFAULT’
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = ‘db_block_size’)
AND advice_status […]

select bytes “Bytes Allocated” from dba_segments where segment_name = ‘TABLE_NAME’;

SELECT vss.owner, vss.object_name, vss.statistic_name, vss.VALUE,
ds.BYTES segsize, ds.BUFFER_POOL
FROM v$segment_statistics vss, dba_segments ds
WHERE vss.statistic_name = ‘physical reads’
AND vss.VALUE > 5000000
AND ds.segment_type = vss.object_type
AND ds.segment_name = vss.object_name
AND ds.owner = vss.owner
AND ds.BUFFER_POOL = ‘DEFAULT’
ORDER BY VALUE
/
WITH segstat AS
(
SELECT object_type, object_name, owner, subobject_name,
MAX (CASE statistic_name
WHEN ‘physical reads’
THEN VALUE
END) pr,
MAX (CASE statistic_name
WHEN ‘physical reads direct’
THEN VALUE
END
) prd
FROM v$segment_statistics vss
WHERE statistic_name IN
(’physical […]

SELECT vss.owner, vss.object_name, vss.statistic_name, vss.VALUE,
ds.BYTES segsize, ds.BUFFER_POOL
FROM v$segment_statistics vss, dba_segments ds
WHERE vss.statistic_name = ‘physical reads’
AND vss.VALUE > 5000000
AND ds.segment_type = vss.object_type
AND ds.segment_name = vss.object_name
AND ds.owner = vss.owner
AND ds.BUFFER_POOL = ‘DEFAULT’
ORDER BY VALUE
/
WITH segstat AS
(
SELECT object_type, object_name, owner, subobject_name,
MAX (CASE statistic_name
WHEN ‘physical reads’
THEN VALUE
END) pr,
MAX (CASE statistic_name
WHEN ‘physical reads direct’
THEN VALUE
END
) prd
FROM v$segment_statistics vss
WHERE statistic_name IN
(’physical […]

Oracle drop all tables:
select ‘drop table ‘ ||object_name|| ‘ cascade constraints purge; ‘from user_objects where object_type=’TABLE’;
Find long running Statements:
select sid, target, sofar, totalwork, message from v$session_longops where totalwork > sofar;

SQL>
set long 900000
select DBMS_METADATA.GET_DDL(’TABLESPACE’,’tablespace_name’) from dual;
select DBMS_METADATA.GET_DDL(’TABLE’,’table_name’) from dual;