Marzec « 2013 « Różności …

Różności …

18 marca 2013

archive log – cannot allocate new log

Zaszufladkowany do: Oracle — Tagi: — Jacek @ 22:29

Droping online redo logs, if the redo log is Status is CURRENT or ACTIVE, the on-line redo log cannot be dropped. When trying to drop CURRENT online redo log, Oracle will return “ORA-01623: log 1 is current log for instance testdb (thread 1) – cannot drop” error message. When trying to drop online redo log in ACTIVE status, Oracle will return “ORA-01624: log 1 needed for crash recovery of instance testdb (thread 1)”

SQL> ALTER DATABASE
ADD LOGFILE GROUP 4 (‘E:\ORACLE\DB\TESTDB\ORADATA\REDO04A.LOG’,
‘E:\ORACLE\DB\TESTDB\ORADATA\REDO04B.LOG’)
SIZE 200M;

SQL>alter database add logfile member
‘/u01/oracle/ica/log11.ora’ to group 1;
Wszystkie redologi w grupie mają taką samą zawartość. Oracle zaleca aby w grupie były co najmniej dwa redologi.
W przypadku “cannot allocate new log” – należy dodać nowe grupy logów.

sql> alter database drop logfile group 1;
gdy grupa ma status active należy wykonać:
sql> alter system checkpoint;
sql> alter system switch logfile;

REDO LOG STATUS:

  • USED. Indicates either that a log has just been added (never used), or that a RESETLOGS command has been issued.
  • CURRENT. Indicates a valid log that is in use.
  • ACTIVE. Indicates a valid log file that is not currently in use.
  • CLEARING. Indicates a log is being re-created as an empty log due to DBA action.
  • CLEARING CURRENT. Means that a current log is being cleared of a closed thread. If a log stays in this status, it could indicate there is some failure in the log switch.
  • INACTIVE. Means that the log is no longer needed for instance recovery but may be needed for media recovery.

The v$logfile table has a status indicator that gives these additional codes:

  • INVALID. File is inaccessible.
  • STALE. File contents are incomplete (such as when an instance is shut down with SHUTDOWN ABORT or due to a system crash).
  • DELETED. File is no longer used.

17 marca 2013

ORA-19089 limit exceeded for recovery files

Zaszufladkowany do: Oracle — Tagi: — Jacek @ 22:30

parameter: db_recovery_file_dest
/u01/app/oracle/flash_recovery_area
c:\oracle\app\oracle\flash_recovery_area

parameter: db_recovery_file_dest_size

SQL> alter database open;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=2048576000 SCOPE=BOTH;
SQL> shutdown abort
SQL> startup

– fast recovery area usage
SELECT
NAME,
TO_CHAR(SPACE_LIMIT/1024/1024,’9999999.99′) || ‘ MB’ AS SPACE_LIMIT,
TO_CHAR((SPACE_LIMIT – SPACE_USED + SPACE_RECLAIMABLE)/1024/1024,’9999999.99′) || ‘ MB’ AS SPACE_AVAILABLE,
ROUND((SPACE_USED – SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;
fast recovery area – informacje
http://www.orafaq.com/wiki/FRA
SELECT flashback_on, log_mode FROM v$database;
enabling archive log
http://www.oracledistilled.com/oracle-database/backup-and-recovery/enabledisable-archive-log-mode-10g11g/ (uwaga – TUTAJ NIE KORZYSTAJĄ Z FRA!!!!!!!!)

Przełączenie do bazy Oracle bez tnsnames.

Zaszufladkowany do: Oracle — Tagi: — Jacek @ 21:03

informacja o oracle service name:
lsnrctl> services

sqlplus sys@”(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=IP)(PORT=NUMER_PORTU))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SID)))” as sysdba

SQLPLUS – komendy: http://docs.oracle.com/cd/B10501_01/server.920/a90842/ch13.htm

define _editor=vim
save nazwa append;

SQL> column colum_name alias alias_name
SQL> column colum_name clear

SQL> column colum_name entmap on
SQL> column colum_name entmap off

SQL> column colum_name fold_after
SQL> column colum_name fold_before
SQL> column colum_name format a25
SQL> column colum_name heading header_text

SQL> column colum_name justify left
SQL> column colum_name justify right
SQL> column colum_name justify center

SQL> column colum_name like expr|alias
SQL> column colum_name newline
SQL> column colum_name new_value variable
SQL> column colum_name print
SQL> column colum_name noprint
SQL> column colum_name old_value 

SQL> column colum_name on
SQL> column colum_name off 

SQL> column colum_name wrapped
SQL> column colum_name word_wrapped
SQL> column colum_name truncated
olumn column_name format a20
column column_name format a50 word_wrapped
column column_name format 999.999  -- Decimal sign
column column_name format 999,999  -- Seperate thousands
column column_name format $999     -- Include leading $ sign
http://www.adp-gmbh.ch/ora/sqlplus/

5 marca 2013

Oracle archive log enable/disable.

Zaszufladkowany do: Oracle — Tagi: — Jacek @ 12:03

ORACLE 11g/10g

http://www.oracledistilled.com/oracle-database/backup-and-recovery/enabledisable-archive-log-mode-10g11g/

RMAN

rman> show controlfile autobackup;

rman> configure controlfile autobackup on;

rman> report schema;

rman> list backup;

rman> backup database;

Strona startowa: www.jaceksen.pl