Creating Physical Standby Database

• OS and platform must be the same as primary db's
•    Install soft and database with identical home directories
• Enable forced logging on your primary database:

SQL> ALTER DATABASE FORCE LOGGING;

select force_logging from v$database;

• Create password file on primary database if there is no already one. And copy it to stb server.

 Select * from v$pwfile_users;

$cd $ORACLE_HOME/dbs

$orapwd file=pwdRKRUS.ora password=xxxxxxxx force=y

• Enable Archiving on Primary. 

If your primary database is not already in Archive Log mode, enable the archive log mode: SQL>shutdown immediate; SQL>startup mount; SQL>alter database archivelog; SQL>alter database open; SQL>archive log list;

• Set Primary Database Initialization Parameters

db_name="RKRUS" instance_name="RKRUS" log_archive_dest_1='LOCATION=/u05/oradata/RKRUS/arch' log_archive_dest_2='SERVICE=rkrusstb LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RKRUSstb'   log_archive_format='log%t_%s_%r.arc'

•  Prepare Stanby Initialization Parameters (copy from primary DB and edit the following parameters)

db_name="RKRUS" db_unique_name="RKRUSstb" instance_name="RKRUS" log_archive_dest_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,STANDBY_ROLE)' standby_file_management='AUTO' standby_archive_dest='/u01/arch' fal_server='rkrus' fal_client='rkrusstb' local_listener='NURSTB_LISTENERS'

•  Create standby controlfile on primary db

sqlplus / as sysdba alter database create standby controlfile as ‘/tmp/ctl.stb’;

•  Ensure that the standby database is down
• Copy and rename the control file to RKRUSstb to the directories defined in the init file on the standby database

/u01/oradata/rkrus/control01.ct, /u02/oradata/rkrus/control02.ctl"

• (RKRUSstb) Mount the database

sqlplus / as sysdba

 startup mount;

• (RKRUSstb)  Restore from backup

rman target / nocatalog;

run{

ALLOCATE CHANNEL ch1 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=nurbcvdb1,NB_ORA_POLICY=sol_oracle_stb,NB_ORA_SERV=netbackup)';

ALLOCATE CHANNEL ch2 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=nurbcvdb1,NB_ORA_POLICY=sol_oracle_stb,NB_ORA_SERV=netbackup)';

restore database;

}

•  Start the Media Recovery Process(MRP)

sqlplus / as sysdba

alter database recover managed standby database disconnect;

Trigger to log DROP operations on schema

create TABLE drop_log (
user_name     VARCHAR2(30),
machine varchar(30),
ddl_date      DATE,
ddl_type      VARCHAR2(30),
object_type   VARCHAR2(18),
owner         VARCHAR2(30),
object_name   VARCHAR2(128))
;


CREATE OR REPLACE TRIGGER drop_trig
before drop
on kpi.schema
declare
machine varchar(30);

BEGIN
 select machine into machine from v$session where sid = ( select sid from v$mystat where rownum=1 );

  INSERT INTO drop_log
  (user_name, machine, ddl_date, ddl_type,
   object_type, owner,
   object_name)
  VALUES
  (ora_login_user, machine, SYSDATE, ora_sysevent,
   ora_dict_obj_type, ora_dict_obj_owner,
   ora_dict_obj_name);
END drop_trig;
/

select * from drop_log;

Oracle ASM

Set Environment variables
export ORACLE_HOME=opt/grid --ensure it is correct ASM home directory
export ORACLE_SID=+ASM1 --select * from V$ASM_CLIENT;

Note: Ensure that you set the correct ASM home directory.

You can look for ASM instance name at direcorty of logs like in this example /opt/oracle/diag/asm/+asm/+ASM1

Connect to the ASM instance

sqlplus / as sysasm

-----------------------------------------------------------------------

create pfile from spfile;

/opt/grid/dbs/init+ASM1.ora

+ASM1.asm_diskgroups='DISK01'#Manual Dismount

*.asm_diskstring='/dev/sd*'

*.asm_power_limit=1

*.diagnostic_dest='/opt/oracle'

*.instance_type='asm'

*.large_pool_size=12M

*.remote_login_passwordfile='EXCLUSIVE'

-------------------------------------------------------------------------

ASM diskgroups

SELECT name, type, total_mb, free_mb, required_mirror_free_mb,  usable_file_mb FROM V$ASM_DISKGROUP;

-------------------------------------------------------------------------

ASM disks

SELECT name, header_status, path FROM V$ASM_DISK;

DISK01_0000 |  MEMBER  | /dev/sda DATA00_0000 | MEMBER  | /dev/sdd DATA00_0001 | MEMBER  | /dev/sde DATA00_0002 | MEMBER  | /dev/sdf   | CANDIDATE | /dev/sdc   | CANDIDATE | /dev/sdb 

------------------------------------------------------------------------

SQL> show parameter asm  NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ asm_diskgroups                       string      DISK01 asm_diskstring                       string      /dev/sd* asm_power_limit                      integer     1 asm_preferred_read_failure_groups    string 

------------------------------------------------------------------------

Mount / Dismount the ASM diskgroup

ALTER DISKGROUP DISK02 MOUNT

ALTER DISKGROUP DISK01 DISMOUNT 

ALTER DISKGROUP ALL MOUNT

ALTER DISKGROUP ALL ENABLE VOLUME ALL

--------------------------------------------------------------------------

ALTER DISKGROUP DISK02 ADD DISK      '/dev/sdc' NAME DISK_0001,      '/dev/sdb' NAME DISK_0002;

----------------------------------------------------------------------------

http://docs.oracle.com/cd/B28359_01/server.111/b31107/asmdiskgrps.htm

-------------------------------------------------------------------------

ORA-01555: snapshot too old: rollback segment number with name "" too small

В процессе выполнения экспорта схемы со следующей командой выдало ошибку EXP-00056: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
-----------------------------------------------------------------------------------------
mknod $PIPE p
(gzip < $PIPE > $BACKUP_FILE.dmp.gz) &

exp \
userid=system/fuckoracle \
owner=NAT_ADMIN \
consistent=Y \
direct=Y \
file=$PIPE log=$BACKUP_FILE.log
------------------------------------------------------------------------------------------
В данном случае, увеличение значения UNDO_RETENTION и размера UNDOTBS не исправило дело.
По этому поводу в металинке нашла ноту Doc ID: 833635.1

All the above errors are produced when exporting, because the LOBs stored in the table to be exported might be corrupted.
To have this checked a PLSQL block should be run against the table.

Вот решение которое предлагается:

1. Create a new temporary table for storing all rowids of the corrupted LOBs. Let's call it "corrupt_lobs"

SQL> create table corrupt_lobs (corrupt_rowid rowid);

2. Make a desc on the table containing the LOB column:

DESC LOBDATA

Name Null? Type
----------------------------------------- --------
ID NOT NULL NUMBER
DOCUMENT BLOB

Вставить название таблицы и название колонки содержащее LOB объект в процедуру вместо LOBDATA и document соответственно

Run the following PLSQL block
declare
error_1578 exception;
error_1555 exception;
error_22922 exception;
pragma exception_init(error_1578,-1578);
pragma exception_init(error_1555,-1555);
pragma exception_init(error_22922,-22922);
n number;
begin
for cursor_lob in (select rowid r, document from LOBDATA ) loop
begin
n:=dbms_lob.instr(cursor_lob.document,hextoraw('889911'));
exception
when error_1578 then
insert into corrupt_lobs values (cursor_lob.r);
commit;
when error_1555 then
insert into corrupt_lobs values (cursor_lob.r);
commit;
when error_22922 then
insert into corrupt_lobs values (cursor_lob.r);
commit;
end;
end loop;
end;
/

In the end all rowids of the corrupted LOBs will be inserted into the corrupt_lobs newly created table.

A possible solution would then be to empty the affected LOBs using:
Eg:
SQL> update LOBDATA set document = empty_blob()
where rowid in (select corrupt_lobs from corrupt_lobs);

Для дальнейшего подтверждения повреждения, попробуйте запустить экспорт на эти сегменты

% exp scott/tiger file=test2.dmp log=test2.log tables=EMP query=\"where rowid=\' AAEWBsAAGAAACewAAG \' \"

В случае если данный процесс выдаст ошибку,можно считать повреждение подтвержденным.
Металинк предлает одно из следующих решений для данной проблемы:

-- Restore and Recover the LOB segment using physical backup.

Or
-- Empty the affected LOBs using UPDATE statement as mentioned in the Note 787004.1

SQL> update EMP set EMP_XML = empty_blob()
where rowid in (select Corrupted_rowid from corrupted_lobs);

Or
-- Perform the export excluding the corrupted rowids.

Using datapump export:

% expdp scott/tiger directory=data_pump_dir dumpfile=test.dmp logfile=test.log tables=EMP query=\"where rowid not in \(\'AAEWBsAAGAAACewAAC\',\'AAEWBsAAGAAACewAAF\' ,\'AAEWBsAAGAAACewAAG\'\)\"

Using normal export:

% exp scott/tiger file=test.dmp log=test.log tables=EMP query=\"where rowid not in \(\'AAEWBsAAGAAACewAAC\',\'AAEWBsAAGAAACewAAF\' ,\'AAEWBsAAGAAACewAAG\'\)\"

Используются ли курсоры совместно

Для определения хэш планов запросов с неразделяемыми курсорами, можно запустить следующий запрос. Необходимо заметить, что все курсоры с одним и тем же планом могут быть использованы совместно.

SELECT plan_hash_value, count(*)
FROM v$sql group by plan_hash_value order by 2 desc;

Следующий запрос покажет SQL запросы которые могут быть использованы совместно

select b.sql_fulltext, b.sql_id, a.executions, a.disk_reads
from v$sqlarea a, v$sql b
where b.sql_id=a.sql_id and
a.plan_hash_value=NNNNNNN
order by 3 desc;

где NNNNNNN является plan_hash_value с наивысшим значением count

Для определения причины, по которой дочерние курсоры не могут быть использованы совместно, воспользуйтесь представлением V$SQL_SHARED_CURSOR, где указаны 50 возможных причин, по которым они стали неразделяемыми.