martes, 2 de febrero de 2010

Laboratorio Oracle Redologs

LABORATORIO UNIX

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.group#
/

1 52428800 INACTIVE /u01/letodb/redo01.log
2 52428800 CURRENT /u01/letodb/redo02.log
3 52428800 ACTIVE /u01/letodb/redo03.log

SQL> ALTER DATABASE ADD LOGFILE (' /u01/letodb/redo01a.log ') SIZE 300M;

SQL> ALTER DATABASE ADD LOGFILE (' /u01/letodb/redo02a.log ') SIZE 300M;

SQL> ALTER DATABASE ADD LOGFILE (' /u01/letodb/redo03a.log ') SIZE 300M;


SQL> ALTER DATABASE DROP LOGFILE GROUP 1;



LABORATORIO WINDOWS

SQL> /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG

SQL> ed
Escrito file afiedt.buf

1* select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.group#
SQL> /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG

SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG

SQL> ALTER DATABASE ADD LOGFILE ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG') SIZE 100M;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG

SQL> ALTER DATABASE ADD LOGFILE ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG') SIZE 100M;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG

SQL> ALTER DATABASE ADD LOGFILE ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG') SIZE 100M;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
2 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02.LOG
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

6 filas seleccionadas.

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
3 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03.LOG
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 UNUSED C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 1
*
ERROR en línea 1:
ORA-01624: log 1 necesario para recuperación de fallo de la instancia power (thread 1)
ORA-00312: log online 1 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG'


SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> alter system switch logfile;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 ACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

Sistema modificado.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
1 52428800 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01.LOG
4 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Base de datos modificada.

SQL> select a.group#,a.bytes,a.status,b.member,a.members from v$log a, v$logfile b where a.group#=b.
group#
2 /

GROUP# BYTES STATUS MEMBER
---------- ---------- ---------------- -------------------------------------------------------------
4 104857600 CURRENT C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO01A.LOG
5 104857600 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO02A.LOG
6 104857600 INACTIVE C:\ORACLE\PRODUCT\10.2.0\ORADATA\POWER\REDO03A.LOG