ORACLE FGA

DESHABILITAR QUE EL USUARIO NO LE APLIQUEN LAS POLITICAS DE FGA

SQL> REVOKE EXEMPT ACCESS POLICY FROM HR;

Revoke succeeded.

SQL> GRANT EXEMPT ACCESS POLICY TO HR;

Grant succeeded.

SQL> REVOKE EXEMPT ACCESS POLICY FROM HR;

Revoke succeeded.

SQL> SELECT * FROM DBA_SYS_PRIVS WHERE PRIVILEGE LIKE '%EXE%';

Opatch Invoking OPatch

bash-3.00$ cd OPatch/

bash-3.00$ pwd
/u01/app/oracle/product/10.2.0/OPatch
bash-3.00$ ls
docs emdpatch.pl jlib opatch opatch.ini opatch.pl opatchprereqs
bash-3.00

$ opatch lsinventory
Invoking OPatch 10.2.0.4.3

Oracle Interim Patch Installer version 10.2.0.4.3
Copyright (c) 2007, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/product/10.2.0
Central Inventory : /u01/app/oracle/oraInventory
from : /var/opt/oracle/oraInst.loc
OPatch version : 10.2.0.4.3
OUI version : 10.2.0.4.0
OUI location : /u01/app/oracle/product/10.2.0/oui
Log file location : /u01/app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch2010-10-01_17-32-41PM.log

Lsinventory Output file location : /u01/app/oracle/product/10.2.0/cfgtoollogs/opatch/lsinv/lsinventory2010-10-01_17-32-41PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0
There are 2 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.
bash-3.00$

Transparent Data Encryption

Transparent Data Encryption

TDE was first introduced in Oracle 10g Release 2. It is an encryption at column level. Oracle 11g introduced the encryption at tablespace level.

Enterprise Edition.

[edit] Test case
Create a "wallet" directory in $ORACLE_BASE/admin/$ORACLE_SID where Oracle can store its encryption key. If not, you will get error: ORA-28368: cannot auto-create wallet.

$ mkdir /app/oracle/admin/orcl/wallet
Create the wallet to hold the encryption key:

SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "secretpassword";
The above created wallet must be reopened after an instance restart:

SQL> ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "secretpassword";
Create a table with encrypted columns:

CREATE TABLE tde_test (
id NUMBER,
data VARCHAR2(30) ENCRYPT
);
INSERT INTO tde_test (id, data) VALUES (1, 'This data in encrypted!');
Select from the table to see the data (wallet is still open):

SQL> SELECT data FROM tde_test;
DATA
------------------------------
This data in encrypted!
Closing the wallet to prevent access to encrypted columns:

SQL> ALTER SYSTEM SET WALLET CLOSE;
Select from the table to see the data (wallet is closed):

SQL> SELECT data FROM tde_test;
SELECT data FROM tde_test
*
ERROR at line 1:
ORA-28365: wallet is not open

EXAMPLE POWER


1. Crear carpeta wallet en

C:\oracle\product\10.2.0\admin\POWER\wallet
Crear wallet

ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "0r1cl310g";

Crear columna encriptada

alter table "DBO"."A" modify ( "A" ENCRYPT USING 'AES192' );

ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "0r1cl310g";

ALTER SYSTEM SET WALLET CLOSE;

alter table "DBO"."A" modify ( "A" DECRYPT );

Oracle Database 10g Release 2 (10.2.0.2) Installation On Solaris 10 (x86)

Oracle Database 10g Release 2 (10.2.0.2) Installation On Solaris 10 (x86)
In this article I'll describe the installation of Oracle Database 10g Release 2 (10.2.0.2) on Solaris 10 (x86). The article is based on a default server installation as shown here. Alternative installations may require a different setup procedure.

•Download Software
•Unpack Files
•Hosts File
•Set Kernel Parameters
•Setup
•Installation
•Post Installation
Download Software
Download the following software:

•Oracle Database 10g Downloads
Unpack Files
Unzip the files:

unzip 10202_database_solx86.zipYou should now have a single directory called "database" containing installation files.
Hosts File
The /etc/hosts file must contain a fully qualified name for the server:

Set Kernel Parameters
In previous versions of Solaris, kernel parameters were amended by adding entries to the "/etc/system" file, followed by a system reboot.

set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=256
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmni=100The Oracle installer recognizes kernel parameters set using this method, but it is now deprecated in favour of resource control projects, explained below.

As the root user, issue the following command.

projadd oracleAppend the following line to the "/etc/user_attr" file.

oracle::::project=oracleIf you've performed a default installation, it is likely that the only kernel parameter you need to alter is "max-shm-memory". To check the current value issue the following command.

# prctl -n project.max-shm-memory -i project oracle
project: 100: oracle
NAME PRIVILEGE VALUE FLAG ACTION RECIPIENT
project.max-shm-memory
privileged 254MB - deny -
system 16.0EB max deny -
#To reset this value, make sure at least one session is logged in as the oracle user, then from the root user issue the following commands.

# prctl -n project.max-shm-memory -v 4gb -r -i project oracle
# projmod -s -K "project.max-shm-memory=(priv,4gb,deny)" oracleThe first dynamically resets the value, while the second makes changes to the "/etc/project" file so the value is persistent between reboots.

# cat /etc/project
system:0::::
user.root:1::::
noproject:2::::
default:3::::
group.staff:10::::
oracle:100::::project.max-shm-memory=(priv,4294967296,deny)
#The Oracle installer seems incapable of recognising kernel parameter set using resource control projects, but if you ignore the warnings the installation completes successfully.
Setup
Add the "SUNWi1cs" and "SUNWi15cs" packages using the "pkgadd" command.

# pkgadd -d /cdrom/sol_10_106_x86/Solaris_10/Product SUNWi1cs SUNWi15cs

Processing package instance from

X11 ISO8859-1 Codeset Support(i386) 2.0,REV=2004.10.17.15.04
Copyright 2004 Sun Microsystems, Inc. All rights reserved.
Use is subject to license terms.

This appears to be an attempt to install the same architecture and
version of a package which is already installed. This installation
will attempt to overwrite this package.

Using as the package base directory.
## Processing package information.
## Processing system information.
16 package pathnames are already properly installed.
## Verifying package dependencies.
## Verifying disk space requirements.
## Checking for conflicts with packages already installed.
## Checking for setuid/setgid programs.

This package contains scripts which will be executed with super-user
permission during the process of installing this package.

Do you want to continue with the installation of [y,n,?] y

Installing X11 ISO8859-1 Codeset Support as

## Installing part 1 of 1.

Installation of was successful.

Processing package instance from

X11 ISO8859-15 Codeset Support(i386) 2.0,REV=2004.10.17.15.04
Copyright 2004 Sun Microsystems, Inc. All rights reserved.
Use is subject to license terms.

This appears to be an attempt to install the same architecture and
version of a package which is already installed. This installation
will attempt to overwrite this package.

Using as the package base directory.
## Processing package information.
## Processing system information.
21 package pathnames are already properly installed.
## Verifying package dependencies.
## Verifying disk space requirements.
## Checking for conflicts with packages already installed.
## Checking for setuid/setgid programs.

This package contains scripts which will be executed with super-user
permission during the process of installing this package.

Do you want to continue with the installation of [y,n,?] y

Installing X11 ISO8859-15 Codeset Support as

## Installing part 1 of 1.

Installation of was successful.
#Create the new groups and users:

groupadd oinstall
groupadd dba
groupadd oper

useradd -g oinstall -G dba -d /export/home/oracle oracle
mkdir /export/home/oracle
chown oracle:oinstall /export/home/oracle
passwd -r files oracleCreate the directories in which the Oracle software will be installed:

mkdir -p /u01/app/oracle/product/10.2.0/db_1
chown -R oracle:oinstall /u01If you have not partitioned your disks to allow a "/u01" mount point, you may want to install the software in the "/export/home/oracle" directory as follows:

mkdir -p /export/home/oracle/product/10.2.0/db_1
chown -R oracle:oinstall /export/home/oracleLogin as the oracle user and add the following lines at the end of the .profile file, making sure you have set the correct ORACLE_BASE value:

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

# Select the appropriate ORACLE_BASE
#ORACLE_BASE=/export/home/oracle; export ORACLE_BASE
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=TSH1; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATHInstallation
Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable:

DISPLAY=:0.0; export DISPLAYStart the Oracle Universal Installer (OUI) by issuing the following command in the database directory:

./runInstaller

PL/SQL wrapper

http://hz.codecheck.ch/UnwrapIt/

Ejemplos Manejo de Tablespaces

CREATE SMALLFILE
TABLESPACE "DATOS"
LOGGING
DATAFILE '/u01/app/oracle/oradata/acme/datos01.dbf' SIZE 5M,
'/u01/app/oracle/oradata/acme/datos02.dbf' SIZE 5M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;


DROP TABLESPACE DATOS;


SQL> drop tablespace datos including contents and datafiles;

Tablespace borrado.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE

6 filas seleccionadas.

SQL>

SQL> ALTER TABLESPACE "DATOS" OFFLINE NORMAL;

Tablespace modificado.


SQL> create table PRUEBA ( a varchar2(20)) tablespace DATOS;
create table PRUEBA ( a varchar2(20)) tablespace DATOS
*
ERROR en linea 1:
ORA-01542: tablespace 'DATOS' offline, no se puede asignar espacio en el


SQL> ALTER TABLESPACE "DATOS" ONLINE;

Tablespace modificado.

SQL> create table PRUEBA ( a varchar2(20)) tablespace DATOS;

Tabla creada.

SQL>

ALTER TABLESPACE "DATOS" OFFLINE IMMEDIATE;
ALTER TABLESPACE "DATOS" OFFLINE FOR RECOVER;

ALTER TABLESPACE "DATOS" READ ONLY;


SQL> create table PRUEBA ( a varchar2(20)) tablespace DATOS;

Tabla creada.

SQL> insert into PRUEBA values('HOLA');

1 fila creada.

SQL> insert into PRUEBA values('PRUEBA');

1 fila creada.

SQL> commit;

Confirmacion terminada.

SQL> ALTER TABLESPACE "DATOS" READ ONLY;

Tablespace modificado.

SQL> insert into PRUEBA values('LOLO');
insert into PRUEBA values('LOLO')
*
ERROR en linea 1:
ORA-00372: el archivo 7 no se puede modificar en este momento
ORA-01110: archivo de datos 7: '/u01/app/oracle/oradata/acme/datos02.dbf'


SQL> select * from PRUEBA;

A
--------------------
HOLA
PRUEBA

SQL> ALTER TABLESPACE "DATOS" READ WRITE;

Tablespace modificado.

SQL>

SQL> alter database datafile '/u01/app/oracle/oradata/acme/datos02.dbf' resize 10M

SQL> alter database datafile '/u01/app/oracle/oradata/acme/datos02.dbf' resize 2M;

GENERAR ARCHIVOS CSV

CREATE OR REPLACE PACKAGE csv AS
-- --------------------------------------------------------------------------
-- Author : DR Timothy S Hall
-- Description : Basic CSV API. For usage notes see:
--
-- CREATE OR REPLACE DIRECTORY dba_dir AS '/u01/app/oracle/dba/';
-- ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
--
-- EXEC csv.generate('DBA_DIR', 'generate.csv', p_query => 'SELECT * FROM emp');
--
-- Requirements : UTL_FILE, DBMS_SQL
-- Ammedments :
-- When Who What
-- =========== ======== =================================================
-- 14-MAY-2005 Tim Hall Initial Creation
-- --------------------------------------------------------------------------

PROCEDURE generate (p_dir IN VARCHAR2,
p_file IN VARCHAR2,
p_query IN VARCHAR2);
END csv;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY csv AS
-- --------------------------------------------------------------------------
-- Author : DR Timothy S Hall
-- Description : Basic CSV API. For usage notes see:
--
-- CREATE OR REPLACE DIRECTORY dba_dir AS '/u01/app/oracle/dba/';
-- ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
--
-- EXEC csv.generate('DBA_DIR', 'generate.csv', p_query => 'SELECT * FROM emp');
--
-- Requirements : UTL_FILE, DBMS_SQL
-- Ammedments :
-- When Who What
-- =========== ======== =================================================
-- 14-MAY-2005 Tim Hall Initial Creation
-- --------------------------------------------------------------------------

g_sep VARCHAR2(5) := ',';

PROCEDURE generate (p_dir IN VARCHAR2,
p_file IN VARCHAR2,
p_query IN VARCHAR2) AS
l_cursor PLS_INTEGER;
l_rows PLS_INTEGER;
l_col_cnt PLS_INTEGER;
l_desc_tab DBMS_SQL.desc_tab;
l_buffer VARCHAR2(32767);

l_file UTL_FILE.file_type;
BEGIN
l_cursor := DBMS_SQL.open_cursor;
DBMS_SQL.parse(l_cursor, p_query, DBMS_SQL.native);

DBMS_SQL.describe_columns (l_cursor, l_col_cnt, l_desc_tab);

FOR i IN 1 .. l_col_cnt LOOP
DBMS_SQL.define_column(l_cursor, i, l_buffer, 32767 );
END LOOP;

l_rows := DBMS_SQL.execute(l_cursor);

l_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767);

-- Output the column names.
FOR i IN 1 .. l_col_cnt LOOP
IF i > 1 THEN
UTL_FILE.put(l_file, g_sep);
END IF;
UTL_FILE.put(l_file, l_desc_tab(i).col_name);
END LOOP;
UTL_FILE.new_line(l_file);

-- Output the data.
LOOP
EXIT WHEN DBMS_SQL.fetch_rows(l_cursor) = 0;

FOR i IN 1 .. l_col_cnt LOOP
IF i > 1 THEN
UTL_FILE.put(l_file, g_sep);
END IF;

DBMS_SQL.COLUMN_VALUE(l_cursor, i, l_buffer);
UTL_FILE.put(l_file, l_buffer);
END LOOP;
UTL_FILE.new_line(l_file);
END LOOP;

UTL_FILE.fclose(l_file);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
IF DBMS_SQL.is_open(l_cursor) THEN
DBMS_SQL.close_cursor(l_cursor);
END IF;
RAISE;
END generate;

END csv;
/
SHOW ERRORS

VirtualBox

COMPARTIR ARCHIVOS

mount.vboxsf Oracle_Install /compartida

CLONAR MAQUINA VIRTUAL

VboxManage clonehd

Laboratorios Flashback database

SQL> alter system set undo_retention=1800 scope=both;

System altered.

SQL> show parameter undo;

NAME      TYPE VALUE

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

temp_undo_enabled      boolean FALSE

undo_management      string AUTO

undo_retention      integer 1800

undo_tablespace      string UNDOTBS1

SQL> select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME        RETENTION

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

SYSTEM        NOT APPLY

SYSAUX        NOT APPLY

UNDOTBS1        NOGUARANTEE

TEMP        NOT APPLY

USERS        NOT APPLY

EXAMPLE        NOT APPLY

TBS_1        NOT APPLY

7 rows selected.

SQL> 

SQL> alter tablespace undotbs1 retention guarantee;

SQL> shutdown immediate;
SQL> startup mount;
ORACLE instance started.


SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=4320;
System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=10G;
System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST='/u02/fra';
System altered.

SQL> alter database flashback on;
Database altered.

SQL> alter database open;
Database altered.

Alter system set recyclebin=on scope=both;







1 LABORATORIO


SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 8 10:54:13 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> connect hr/hr;
Conectado.
SQL>
SQL>
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW

8 filas seleccionadas.

SQL> create table COPIA_REGIONS as (select * from REGIONS);

Tabla creada.

SQL> select count(*) from COPIA_REGIONS;

COUNT(*)
----------
4

SQL> drop table COPIA_REGIONS;

Tabla borrada.

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
COPIA_REGIONS BIN$iuNRnAWxvlDgQAB/AQAL8Q==$0 TABLE 2010-07-08:11:00:42
SQL>

SQL> SQL> flashback table COPIA_REGIONS to before drop;

Flashback terminado.

VALIDAMOS LOS DATOS DE LA TABLA

SQL> select * from COPIA_REGIONS;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

VER EL TAMAÑO DE FLASHBACK

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG ,06 ,06 5
BACKUPPIECE 11,66 0 4
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0


OTRO METODO.

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
COPIA_REGIONS BIN$iuNRnAWxvlDgQAB/AQAL8Q==$0 TABLE 2010-07-08:11:00:42
SQL>
SQL>
SQL> flashback table "BIN$iuNRnAWxvlDgQAB/AQAL8Q==$0" to before drop;

Flashback terminado.


2 LABORATORIO PUNTOS DE RESTAURACION

SQL> CREATE RESTORE POINT good_data;

Punto de restauracion creado.

SQL> select * from hr.regions where region_id=1;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe

SQL>

SQL> update hr.regions set region_name='Colombia' where region_id=1;

1 fila actualizada.

SQL> commit;

Confirmacion terminada.

SQL> select * from hr.regions where region_id=1;

REGION_ID REGION_NAME
---------- -------------------------
1 Colombia


SQL> flashback table hr.regions to restore point good_data;

ERROR en linea 1:
ORA-08189: no se puede realizar flashback en la tabla porque el movimiento de
filas no esta activado

SQL> alter table hr.regions enable row movement;

Tabla modificada.


SQL> flashback table hr.regions to restore point good_data;

Flashback terminado.

SQL> select * from hr.regions where region_id=1;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe

SQL>


PARA VER PUNTOS DE RESTAURACION CREADOS EN LA INSTANCIA

SQL> select name, storage_size from v$restore_point;

BORRAR PUNTO DE RESTAURACION

drop restore point good_data;


3. LABORATORIO FLASHBACK DATABASE

NOTA: SI NO ESTA ACTIVADA LA OPCION DE LA BD NO FUNCIONA

EJEMPLO CUANDO NO ESTA ACTIVADA

VER CURRENT_SCN

[oracle@oracle11g ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 12:04:28 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc v$database;
Nombre ?Nulo? Tipo
----------------------------------------- -------- ----------------------------
DBID NUMBER
NAME VARCHAR2(9)
CREATED DATE
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
PRIOR_RESETLOGS_CHANGE# NUMBER
PRIOR_RESETLOGS_TIME DATE
LOG_MODE VARCHAR2(12)
CHECKPOINT_CHANGE# NUMBER
ARCHIVE_CHANGE# NUMBER
CONTROLFILE_TYPE VARCHAR2(7)
CONTROLFILE_CREATED DATE
CONTROLFILE_SEQUENCE# NUMBER
CONTROLFILE_CHANGE# NUMBER
CONTROLFILE_TIME DATE
OPEN_RESETLOGS VARCHAR2(11)
VERSION_TIME DATE
OPEN_MODE VARCHAR2(10)
PROTECTION_MODE VARCHAR2(20)
PROTECTION_LEVEL VARCHAR2(20)
REMOTE_ARCHIVE VARCHAR2(8)
ACTIVATION# NUMBER
SWITCHOVER# NUMBER
DATABASE_ROLE VARCHAR2(16)
ARCHIVELOG_CHANGE# NUMBER
ARCHIVELOG_COMPRESSION VARCHAR2(8)
SWITCHOVER_STATUS VARCHAR2(20)
DATAGUARD_BROKER VARCHAR2(8)
GUARD_STATUS VARCHAR2(7)
SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3)
FORCE_LOGGING VARCHAR2(3)
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
RECOVERY_TARGET_INCARNATION# NUMBER
LAST_OPEN_INCARNATION# NUMBER
CURRENT_SCN NUMBER
FLASHBACK_ON VARCHAR2(18)
SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3)
DB_UNIQUE_NAME VARCHAR2(30)
STANDBY_BECAME_PRIMARY_SCN NUMBER
FS_FAILOVER_STATUS VARCHAR2(21)
FS_FAILOVER_CURRENT_TARGET VARCHAR2(30)
FS_FAILOVER_THRESHOLD NUMBER
FS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7)
FS_FAILOVER_OBSERVER_HOST VARCHAR2(512)

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
708133

SQL> flashback database to scn 708132;
flashback database to scn 708132
*
ERROR en linea 1:
ORA-38757: La base de datos debe estar montada y no estar abierta para
FLASHBACK.


SQL> shutdown immediate;
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL>

SQL> start mount;
SP2-0310: no se ha podido abrir el archivo "mount.sql"
SQL> startup mount;
Instancia ORACLE iniciada.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
SQL>

SQL> flashback database to scn 70812;
flashback database to scn 70812
*
ERROR en linea 1:
ORA-38726: El registro de flashback de la base de datos no esta activado.

SQL>

4 LABORATORIO FLASHBACK DATABASE

ACTIVACION FLASHBACK

[oracle@oracle11g ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 14:39:51 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
SQL>
SQL> archive log list;
Modo log de la base de datos Modo de Archivado
Archivado automatico Activado
Destino del archivo USE_DB_RECOVERY_FILE_DEST
Secuencia de log en linea mas antigua 2
Siguiente secuencia de log para archivar 4
Secuencia de log actual 4
SQL>
SQL> alter database flashback on;

Base de datos modificada.

SQL> show parameter db_flashback;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL> select current_scn,flashback_on from v$database;

CURRENT_SCN FLASHBACK_ON
----------- ------------------
0 YES

SQL> alter database flashback off;

Base de datos modificada.

SQL> select current_scn,flashback_on from v$database;

CURRENT_SCN FLASHBACK_ON
----------- ------------------
0 NO

SQL> alter database flashback on;

Base de datos modificada.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 09/07/10 MOUNTED NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> alter database open;

Base de datos modificada.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 09/07/10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INS


EMPEZAMOS EL LABORATORIO FLASHBACK DATABASE

SQL> connect hr/hr;
Conectado.
SQL>
SQL>
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
COPIA_REGIONS TABLE


SQL> drop table COPIA_REGIONS;

Tabla borrada.

SQL>

SQL> create table COPIA_EMPLOYEES as (select * from employees);

Tabla creada.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
BIN$ivm+7nDFpazgQAB/AQALUg==$0 TABLE
COPIA_EMPLOYEES TABLE

SQL> delete from COPIA_EMPLOYEES where 1=1;

107 filas suprimidas.

SQL> commit;

Confirmacion terminada.

SQL>

SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 14:55:59 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

CURRENT ACTUAL

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
708510


SQL> desc hr.regions;
Nombre ?Nulo? Tipo
----------------------------------------- -------- ----------------------------
REGION_ID NOT NULL NUMBER
REGION_NAME VARCHAR2(25)

SQL> insert into hr.regions values(6,'COLOMBIA');

1 fila creada.

SQL> insert into hr.regions values(7,'PERU');

1 fila creada.

SQL> COMMIT;

Confirmacion terminada.

SQL> select * from hr.regions;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
6 COLOMBIA
7 PERU

6 filas seleccionadas.

COMO HAGO PARA DEVOLVERME A UN ESTADO ANTERIOR DE LA BASE DE DATOS ANTES DEL DELETE

SQL> shutdown immediate;
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL>
SQL>
SQL> startup mount;
Instancia ORACLE iniciada.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 09/07/10 MOUNTED NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> select current_scn from v$database;

CURRENT_SCN
-----------
0

VALIDAR EL RESULTADO DE ESTE QUERY

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;


SQL> flashback database to scn 708358;

Flashback terminado.

SQL> alter database open;
alter database open
*
ERROR en linea 1:
ORA-01589: debe utilizar la opcion RESETLOGS o NORESETLOGS para abrir la base
de datos


SQL> alter database open resetlogs;

Base de datos modificada.

SQL>

VER NUEVA INCARNATION GENERADA EN LA INSTANCIA

[oracle@oracle11g ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Jul 9 15:02:27 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ACME (DBID=1908099333, not open)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ACME 1908099333 PARENT 584972 06-JUL-10
2 2 ACME 1908099333 CURRENT 654164 07-JUL-10

RMAN> list incarnation;


List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ACME 1908099333 PARENT 584972 06-JUL-10
2 2 ACME 1908099333 PARENT 654164 07-JUL-10
3 3 ACME 1908099333 CURRENT 708361 09-JUL-10

RMAN>

VALIDAR LA DATA A VER SI QUEDO BIEN


SQL> connect hr/hr;
Conectado.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
COPIA_REGIONS TABLE

9 filas seleccionadas.

SQL> select * from regions;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

SQL>

NOTA: REALIZAR BACKUP VIA RMAN FULL DATABASE ARCHIVELOG


5 LABORATORIO FLASHBACK QUERY

[oracle@oracle11g ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 15:23:03 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> connect hr/hr;
Conectado.
SQL> CREATE TABLE emp
(empno NUMBER PRIMARY KEY,
empname VARCHAR2(16),
salary NUMBER);
2 3 4
Tabla creada.

SQL> INSERT INTO emp VALUES (111, 'Mike', 555);

1 fila creada.

SQL> commit;

Confirmacion terminada.

SQL> CREATE TABLE dept
(deptno NUMBER,
deptname VARCHAR2(32));
2 3
Tabla creada.

SQL> INSERT INTO dept VALUES (10, 'Accounting');

1 fila creada.

SQL> commit;

Confirmacion terminada.

SQL> UPDATE emp SET salary = salary + 100 WHERE empno = 111;

1 fila actualizada.

SQL> COMMIT;

Confirmacion terminada.

SQL> select * from emp;

EMPNO EMPNAME SALARY
---------- ---------------- ----------
111 Mike 655

SQL>
SQL> INSERT INTO dept VALUES (20, 'Finance');

1 fila creada.

SQL> DELETE FROM emp WHERE empno = 111;

1 fila suprimida.

SQL> commit;

Confirmacion terminada.

SQL>


REALIZAN UNA NUEVA TRANSACCION PARA EL CODIGO 111

a new transaction reinserts employee id 111 with a new employee name into the emp table.

[oracle@oracle11g ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 15:30:54 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> SELECT versions_xid XID, versions_startscn START_SCN,
versions_endscn END_SCN, versions_operation OPERATION,
empname, salary FROM hr.emp
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
where empno = 111;
2 3 4 5
XID START_SCN END_SCN O EMPNAME SALARY
---------------- ---------- ---------- - ---------------- ----------
0800230006010000 710161 I Tom 927
0600220049010000 710077 D Mike 655
0500290027010000 710057 710077 U Mike 655
04000800DB000000 709971 710057 I Mike 555

SQL>

VALIDAMOS EL undo_sql

SELECT xid, start_scn, commit_scn,
operation, logon_user,
undo_sql FROM flashback_transaction_query
WHERE xid = HEXTORAW('0600220049010000')
/

XID START_SCN COMMIT_SCN OPERATION LOGON_USER
---------------- ---------- ---------- -------------------------------- ------------------------------
UNDO_SQL
0600220049010000 710065 710077 DELETE HR
insert into "HR"."EMP"("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');

0600220049010000 710065 710077 INSERT HR
delete from "HR"."DEPT" where ROWID = 'AAAM7pAAEAAAAGcAAB';

0600220049010000 710065 710077 BEGIN HR

•To obtain an SCN to use later with a flashback feature, use

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER.


6 MANEJO PAPELERA RECICLAJE
SELECT * FROM RECYCLEBIN;

ALTER SESSION SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF;

VISTAS

USER_RECYCLEBIN
This view can be used by users to see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use.

DBA_RECYCLEBIN
This view gives administrators visibility to all dropped objects in the recycle bin

PURGE TABLE nombre_tabla;
Elimina la tabla indicada de la papelera. El nombre de la tabla puede ser el nombre original o el renombrado.

PURGE INDEX nombre_índice;
Elimina el índice indicado de la papelera. El nombre del índice es el nombre original y no el renombrado.

PURGE RECYCLEBIN;
Elimina todos los objetos (del usuario que lanza la orden) de la papelera.

PURGE DBA_RECYCLEBIN;
Elimina todos los objetos (de todos los usuarios) de la papelera. Solo un SYSDBA puede lanzar este comando.

PURGE TABLESPACE nombre_tablespace;
Elimina todos los objetos (del usuario) de la papelera que residan en el tablespace indicado.

PURGE TABLESPACE nombre_tablespace USERS nombre_usuario;
Elimina todos los objetos de la papelera que residan en el tablespace indicado y pertenezcan el usuario indicado.

SQL> show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME---------------- ------------------------------ ------------ -------------------PENDIENTES BIN$WJ4MAU6RU7XgRAAUT2gJUg==$0 TABLE 2008-10-06:17:17:21PRUEBA BIN$WMDKHy9/HiHgRAAUT2gJUg==$0 TABLE 2008-10-08:10:44:19PRUEBA BIN$WL+UR7qDG8/gRAAUT2gJUg==$0 TABLE 2008-10-08:09:17:41PRUEBA BIN$WL83vpCuGmTgRAAUT2gJUg==$0 TABLE 2008-10-08:08:51:49

SQL> flashback table pendientes to before drop;
Flashback complete.
SQL>


SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMPLOYEES BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE 2003-10-27:14:00:19

Tmabien se puede realizar un query con el nombre de la tabla de esta forma.

example: SELECT * FROM "BIN$yrMKlZaVMhfgNAgAIMenRA==$0";

PURGE TABLE BIN$jsleilx392mk2=293$0;

PURGE TABLESPACE example;

PURGE TABLESPACE example USER oe;

Limpia toda la papelera reciclaje

PURGE RECYCLEBIN;

Laboratorios Recovery RMAN

CONDICIONES INICIALES

RMAN> backup database;

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/system01.dbf
/u01/app/oracle/oradata/acme/undotbs01.dbf
/u01/app/oracle/oradata/acme/sysaux01.dbf
/u01/app/oracle/oradata/acme/users01.dbf
/u01/app/oracle/oradata/acme/example01.dbf


SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/control01.ctl
/u01/app/oracle/oradata/acme/control02.ctl
/u01/app/oracle/oradata/acme/control03.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/redo03.log
/u01/app/oracle/oradata/acme/redo02.log
/u01/app/oracle/oradata/acme/redo01.log


COLOCAR INSTANCIA EN MODE ARCHIVELOG

SQL> shutdown immediate;
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL>

SQL> startup mount;
Instancia ORACLE iniciada.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
SQL>
SQL> alter database archivelog;

Base de datos modificada.

SQL> alter database open;

Base de datos modificada.

SQL> archive log list;
Modo log de la base de datos Modo de Archivado
Archivado automatico Activado
Destino del archivo USE_DB_RECOVERY_FILE_DEST
Secuencia de log en linea mas antigua 1
Siguiente secuencia de log para archivar 2
Secuencia de log actual 2

SQL> show parameter db_recovery;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 5G
SQL>


SQL> alter system switch logfile;

Sistema modificado.

SQL> archive log list;
Modo log de la base de datos Modo de Archivado
Archivado automatico Activado
Destino del archivo USE_DB_RECOVERY_FILE_DEST
Secuencia de log en linea mas antigua 1
Siguiente secuencia de log para archivar 3
Secuencia de log actual 3
SQL>

CREAR ARCHIVO DE PARAMETROS

SQL> create pfile from spfile;

Archivo creado.

SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g ~]$ cd $ORACLE_HOME/dbs
[oracle@oracle11g dbs]$ pwd
/u01/app/oracle/product/10.2.0/db/dbs
[oracle@oracle11g dbs]$ ls
hc_acme.dat initdw.ora lkACME spfileacme.ora
initacme.ora init.ora orapwacme
[oracle@oracle11g dbs]$ ls -ltr
total 76
-rw-r----- 1 oracle oinstall 8385 sep 11 1998 init.ora
-rw-r--r-- 1 oracle oinstall 12920 may 3 2001 initdw.ora
-rw-rw---- 1 oracle oinstall 1544 jul 6 09:48 hc_acme.dat
-rw-rw---- 1 oracle oinstall 24 jul 6 09:49 lkACME
-rw-r----- 1 oracle oinstall 1536 jul 6 09:53 orapwacme
-rw-r----- 1 oracle oinstall 3584 jul 6 10:04 spfileacme.ora
-rw-r--r-- 1 oracle oinstall 1058 jul 6 10:15 initacme.ora
[oracle@oracle11g dbs]$

SCRIPT PARA REALIZAR COPIA DE SEGURIDAD FISICA DATAFILES

set head off
set feed off echo off head off trimspool on line 500
spool copia.sh
select 'cp ' || name || ' /u01/copia' from v$datafile
union all
select 'cp ' || name || ' /u01/copia' from v$controlfile
union all
select 'cp ' || member || ' /u01/copia' from v$logfile
/
spool off
set feed on


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> start copia_todo.sql

cp /u01/app/oracle/oradata/acme/system01.dbf /u01/copia
cp /u01/app/oracle/oradata/acme/undotbs01.dbf /u01/copia
cp /u01/app/oracle/oradata/acme/sysaux01.dbf /u01/copia
cp /u01/app/oracle/oradata/acme/users01.dbf /u01/copia
cp /u01/app/oracle/oradata/acme/example01.dbf /u01/copia
cp /u01/app/oracle/oradata/acme/control01.ctl /u01/copia
cp /u01/app/oracle/oradata/acme/control02.ctl /u01/copia
cp /u01/app/oracle/oradata/acme/control03.ctl /u01/copia
cp /u01/app/oracle/oradata/acme/redo03.log /u01/copia
cp /u01/app/oracle/oradata/acme/redo02.log /u01/copia
cp /u01/app/oracle/oradata/acme/redo01.log /u01/copia

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 10:30:44 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;

[oracle@oracle11g copia]$ ls -ltr
total 1045864
-rw-r----- 1 oracle oinstall 503324672 jul 6 10:32 system01.dbf
-rw-r----- 1 oracle oinstall 26222592 jul 6 10:33 undotbs01.dbf
-rw-r----- 1 oracle oinstall 251666432 jul 6 10:33 sysaux01.dbf
-rw-r----- 1 oracle oinstall 5251072 jul 6 10:33 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 jul 6 10:33 example01.dbf
-rw-r----- 1 oracle oinstall 7061504 jul 6 10:33 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 10:33 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 10:33 control03.ctl
-rw-r----- 1 oracle oinstall 52429312 jul 6 10:34 redo03.log
-rw-r----- 1 oracle oinstall 52429312 jul 6 10:34 redo02.log
-rw-r----- 1 oracle oinstall 52429312 jul 6 10:34 redo01.log
[oracle@oracle11g copia]$
[oracle@oracle11g copia]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 11:14:01 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
Base de datos abierta.
SQL>
SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g copia]$
[oracle@oracle11g copia]$
[oracle@oracle11g copia]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 6 11:14:39 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ACME (DBID=1908099333)

RMAN> list backup summary;

using target database control file instead of recovery catalog


[oracle@oracle11g copia]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 11:18:20 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user hr account unlock;

Usuario modificado.

SQL> connect hr/hr;
Conectado.
SQL>
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW

8 filas seleccionadas.

SQL> desc regions;
Nombre ?Nulo? Tipo
----------------------------------------- -------- ----------------------------
REGION_ID NOT NULL NUMBER
REGION_NAME VARCHAR2(25)

SQL> select count(*) from regions;

COUNT(*)
----------
4

SQL> insert into regions values(5,'CUNDINAMARCA');

1 fila creada.

SQL> commit;

Confirmacion terminada.

SQL> select * from regions;

REGION_ID REGION_NAME
---------- -------------------------
5 CUNDINAMARCA
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system switch logfile;

Sistema modificado.

SQL>

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 6 11:21:24 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ACME (DBID=1908099333)

RMAN> backup archivelog all;

Starting backup at 06-JUL-10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=205 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=723636781
input archive log thread=1 sequence=3 recid=2 stamp=723640844
input archive log thread=1 sequence=4 recid=3 stamp=723640892
channel ORA_DISK_1: starting piece 1 at 06-JUL-10
channel ORA_DISK_1: finished piece 1 at 06-JUL-10
piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_annnn_TAG20100706T112133_636p0h6k_.bkp tag=TAG20100706T112133 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 06-JUL-10

RMAN> list backup summary;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B F A DISK 06-JUL-10 1 1 NO TAG20100706T111529
2 B F A DISK 06-JUL-10 1 1 NO TAG20100706T111529
3 B A A DISK 06-JUL-10 1 1 NO TAG20100706T112133

RMAN> list backupset 1;


List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 581.77M DISK 00:01:25 06-JUL-10
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20100706T111529
Piece Name: /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 499458 06-JUL-10 /u01/app/oracle/oradata/acme/system01.dbf
2 Full 499458 06-JUL-10 /u01/app/oracle/oradata/acme/undotbs01.dbf
3 Full 499458 06-JUL-10 /u01/app/oracle/oradata/acme/sysaux01.dbf
4 Full 499458 06-JUL-10 /u01/app/oracle/oradata/acme/users01.dbf
5 Full 499458 06-JUL-10 /u01/app/oracle/oradata/acme/example01.dbf

RMAN> list backupset 2;


List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 6.80M DISK 00:00:02 06-JUL-10
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20100706T111529
Piece Name: /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_ncsnf_TAG20100706T111529_636or3gl_.bkp
Control File Included: Ckp SCN: 499497 Ckp time: 06-JUL-10
SPFILE Included: Modification time: 06-JUL-10

RMAN>

RMAN> list backupset 3;


List of Backup Sets
===================

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3 7.45M DISK 00:00:02 06-JUL-10
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20100706T112133
Piece Name: /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_annnn_TAG20100706T112133_636p0h6k_.bkp

List of Archived Logs in backup set 3
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 494642 06-JUL-10 498441 06-JUL-10
1 3 498441 06-JUL-10 499609 06-JUL-10
1 4 499609 06-JUL-10 499632 06-JUL-10

RMAN>

NOTA: CON LO ANTERIOR YA NOS PODEMOS TIRAR LA BD

1. LABORATORIO BORRAR datafile temp01.dbf

NOTA: NO PASA NADA

[oracle@oracle11g acme]$ ls -ltr
total 1063364
-rw-r----- 1 oracle oinstall 20979712 jul 6 09:51 temp01.dbf
-rw-r----- 1 oracle oinstall 5251072 jul 6 11:15 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 jul 6 11:15 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 11:20 redo03.log
-rw-r----- 1 oracle oinstall 251666432 jul 6 11:21 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 11:21 redo01.log
-rw-r----- 1 oracle oinstall 26222592 jul 6 11:23 undotbs01.dbf
-rw-r----- 1 oracle oinstall 503324672 jul 6 11:23 system01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 11:23 redo02.log
-rw-r----- 1 oracle oinstall 7061504 jul 6 11:23 control03.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 11:23 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 11:23 control01.ctl
[oracle@oracle11g acme]$ rm -r temp01.dbf
[oracle@oracle11g acme]$

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/temp01.dbf

SQL>

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/temp01.dbf

SQL> shutdown immediate;
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL>

SQL> ! ls /u01/app/oracle/oradata/acme/
control01.ctl control02.ctl control03.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf

SQL>

SQL> ! ls /u01/app/oracle/oradata/acme/
control01.ctl control02.ctl control03.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf

SQL> startup
Instancia ORACLE iniciada.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
Base de datos abierta.
SQL> ! ls /u01/app/oracle/oradata/acme/
control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf

SQL>

2. LABORATORIO BORRAR datafile /u01/app/oracle/oradata/acme/users01.dbf

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/system01.dbf
/u01/app/oracle/oradata/acme/undotbs01.dbf
/u01/app/oracle/oradata/acme/sysaux01.dbf
/u01/app/oracle/oradata/acme/users01.dbf
/u01/app/oracle/oradata/acme/example01.dbf

SQL> !
[oracle@oracle11g copia]$ cd /u01/app/oracle/oradata/acme/
[oracle@oracle11g acme]$ rm users01.dbf
[oracle@oracle11g acme]$ exit
exit

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/system01.dbf
/u01/app/oracle/oradata/acme/undotbs01.dbf
/u01/app/oracle/oradata/acme/sysaux01.dbf
/u01/app/oracle/oradata/acme/users01.dbf
/u01/app/oracle/oradata/acme/example01.dbf

SQL>

SQL> desc dba_users;
Nombre ?Nulo? Tipo
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)

SQL>
SQL> select username,default_tablespace from dba_users where username='HR';

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
HR USERS

SQL>

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW

8 filas seleccionadas.

SQL> create table SUPPLIERS ( id number(10), description varchar2(40));
create table SUPPLIERS ( id number(10), description varchar2(40))
*
ERROR en linea 1:
ORA-01116: error al abrir el archivo de base de datos 4
ORA-01110: archivo de datos 4: '/u01/app/oracle/oradata/acme/users01.dbf'
ORA-27041: no se ha podido abrir el archivo
Linux Error: 2: No such file or directory
Additional information: 3

PROBAR LO SIGUIENTE CON LA INSTANCIA ARRIBA (ESTO ES UN ERROR A PROPOSITO)

NOTA; NO VA DEJAR RESTAURAR EL DATAFILE

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 6 11:41:07 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ACME (DBID=1908099333)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** /u01/app/oracle/oradata/acme/system01.dbf
2 25 UNDOTBS1 *** /u01/app/oracle/oradata/acme/undotbs01.dbf
3 240 SYSAUX *** /u01/app/oracle/oradata/acme/sysaux01.dbf
4 0 USERS *** /u01/app/oracle/oradata/acme/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/oradata/acme/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/acme/temp01.dbf

RMAN> restore datafile users;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "double-quoted-string, integer, single-quoted-string"
RMAN-01008: the bad identifier was: users
RMAN-01007: at line 1 column 18 file: standard input

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "allocate, alter, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, host, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, transport, upgrade, unregister, validate"
RMAN-01007: at line 1 column 23 file: standard input

RMAN> restore datafile 4;

Starting restore at 06-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=199 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/app/oracle/oradata/acme/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/06/2010 11:42:33
ORA-19870: error al leer parte de la copia de seguridad /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
ORA-19573: no se puede obtener la cola exclusive para el archivo de datos 4

RMAN>

AHORA SI PROBAMOS LA RESTAURACION BAJANDO LA INSTANCIA


SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> shutdown immediate;
ORA-01116: error al abrir el archivo de base de datos 4
ORA-01110: archivo de datos 4: '/u01/app/oracle/oradata/acme/users01.dbf'
ORA-27041: no se ha podido abrir el archivo
Linux Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> shutdown abort;
Instancia ORACLE cerrada.
SQL>
SQL> startup mount;
Instancia ORACLE iniciada.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
SQL>

[oracle@oracle11g copia]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 6 11:45:26 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ACME (DBID=1908099333, not open)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** /u01/app/oracle/oradata/acme/system01.dbf
2 25 UNDOTBS1 *** /u01/app/oracle/oradata/acme/undotbs01.dbf
3 240 SYSAUX *** /u01/app/oracle/oradata/acme/sysaux01.dbf
4 0 USERS *** /u01/app/oracle/oradata/acme/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/oradata/acme/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/acme/temp01.dbf

RMAN> restore datafile 4;

Starting restore at 06-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=211 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/app/oracle/oradata/acme/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp tag=TAG20100706T111529
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 06-JUL-10

RMAN>

[oracle@oracle11g acme]$ ls -ltr
total 1045944
-rw-r----- 1 oracle oinstall 52429312 jul 6 11:29 redo03.log
-rw-r----- 1 oracle oinstall 52429312 jul 6 11:29 redo01.log
-rw-r----- 1 oracle oinstall 104865792 jul 6 11:29 example01.dbf
-rw-r----- 1 oracle oinstall 20979712 jul 6 11:29 temp01.dbf
-rw-r----- 1 oracle oinstall 26222592 jul 6 11:44 undotbs01.dbf
-rw-r----- 1 oracle oinstall 503324672 jul 6 11:44 system01.dbf
-rw-r----- 1 oracle oinstall 251666432 jul 6 11:44 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 11:44 redo02.log
-rw-r----- 1 oracle oinstall 5251072 jul 6 11:45 users01.dbf
-rw-r----- 1 oracle oinstall 7061504 jul 6 11:46 control03.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 11:46 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 11:46 control01.ctl
[oracle@oracle11g acme]$

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** /u01/app/oracle/oradata/acme/system01.dbf
2 25 UNDOTBS1 *** /u01/app/oracle/oradata/acme/undotbs01.dbf
3 240 SYSAUX *** /u01/app/oracle/oradata/acme/sysaux01.dbf
4 0 USERS *** /u01/app/oracle/oradata/acme/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/oradata/acme/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/acme/temp01.dbf

RMAN> restore datafile 4;

Starting restore at 06-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=211 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/app/oracle/oradata/acme/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp tag=TAG20100706T111529
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 06-JUL-10

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/06/2010 11:47:04
ORA-01113: el archivo 4 necesita recuperacion del medio fisico
ORA-01110: archivo de datos 4: '/u01/app/oracle/oradata/acme/users01.dbf'

RMAN> recover database;

Starting recover at 06-JUL-10
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 06-JUL-10

RMAN> alter database open;

database opened

RMAN>

Recovery Manager complete.
[oracle@oracle11g copia]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 11:50:32 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/system01.dbf
/u01/app/oracle/oradata/acme/undotbs01.dbf
/u01/app/oracle/oradata/acme/sysaux01.dbf
/u01/app/oracle/oradata/acme/users01.dbf
/u01/app/oracle/oradata/acme/example01.dbf

SQL> connect hr/hr;
Conectado.
SQL> create table SUPPLIERS ( id number(10), description varchar2(40));

Tabla creada.

SQL>

3. LABORATORIO TABLESPACES


SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE

6 filas seleccionadas.

SQL>

[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 15:13:16 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select username,default_tablespace from dba_users where username='HR';

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
HR USERS

SQL> connect hr/hr;
Conectado.
SQL> create table SUPPLIERS ( id number(10), description varchar2(40));
create table SUPPLIERS ( id number(10), description varchar2(40))
*
ERROR en linea 1:
ORA-01116: error al abrir el archivo de base de datos 4
ORA-01110: archivo de datos 4: '/u01/app/oracle/oradata/acme/users01.dbf'
ORA-27041: no se ha podido abrir el archivo
Linux Error: 2: No such file or directory
Additional information: 3


[oracle@oracle11g acme]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 6 15:15:28 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ACME (DBID=1908099333)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** /u01/app/oracle/oradata/acme/system01.dbf
2 25 UNDOTBS1 *** /u01/app/oracle/oradata/acme/undotbs01.dbf
3 240 SYSAUX *** /u01/app/oracle/oradata/acme/sysaux01.dbf
4 0 USERS *** /u01/app/oracle/oradata/acme/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/oradata/acme/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/acme/temp01.dbf


VER LOS ERRORES POR NO ESTAR EN MODE MOUNT

RMAN> restore tablespace users;

Starting restore at 06-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=201 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/app/oracle/oradata/acme/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/06/2010 15:16:11
ORA-19870: error al leer parte de la copia de seguridad /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
ORA-19573: no se puede obtener la cola exclusive para el archivo de datos 4

RMAN>


SQL> shutdown immediate;
ORA-01116: error al abrir el archivo de base de datos 4
ORA-01110: archivo de datos 4: '/u01/app/oracle/oradata/acme/users01.dbf'
ORA-27041: no se ha podido abrir el archivo
Linux Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> shutdown abort;
Instancia ORACLE cerrada.
SQL>
SQL>
SQL> startup mount;
Instancia ORACLE iniciada.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g acme]$
[oracle@oracle11g acme]$ rman target /

[oracle@oracle11g acme]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 6 15:18:44 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ACME (DBID=1908099333, not open)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** /u01/app/oracle/oradata/acme/system01.dbf
2 25 UNDOTBS1 *** /u01/app/oracle/oradata/acme/undotbs01.dbf
3 240 SYSAUX *** /u01/app/oracle/oradata/acme/sysaux01.dbf
4 0 USERS *** /u01/app/oracle/oradata/acme/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/oradata/acme/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/acme/temp01.dbf

RMAN> restore tablespace users;

Starting restore at 06-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=211 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/app/oracle/oradata/acme/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp tag=TAG20100706T111529
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 06-JUL-10

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/06/2010 15:19:38
ORA-01113: el archivo 4 necesita recuperacion del medio fisico
ORA-01110: archivo de datos 4: '/u01/app/oracle/oradata/acme/users01.dbf'

RMAN> recover database;

Starting recover at 06-JUL-10
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_3_636oyw60_.arc
archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_4_636p0d5o_.arc
archive log thread 1 sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_5_636qno97_.arc
archive log thread 1 sequence 6 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_6_6372gws3_.arc
archive log filename=/u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_3_636oyw60_.arc thread=1 sequence=3
archive log filename=/u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_4_636p0d5o_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:04
Finished recover at 06-JUL-10

RMAN> alter database open;

database opened

RMAN>

4 LABORATORIO BORRAR TABLESPACE SYSTEM


[oracle@oracle11g acme]$ rm system01.dbf
[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 15:27:32 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/system01.dbf
/u01/app/oracle/oradata/acme/undotbs01.dbf
/u01/app/oracle/oradata/acme/sysaux01.dbf
/u01/app/oracle/oradata/acme/users01.dbf
/u01/app/oracle/oradata/acme/example01.dbf

SQL> archive log list;
Modo log de la base de datos Modo de Archivado
Archivado automatico Activado
Destino del archivo USE_DB_RECOVERY_FILE_DEST
Secuencia de log en linea mas antigua 6
Siguiente secuencia de log para archivar 8
Secuencia de log actual 8
SQL>
SQL> alter system switch logfile;

Sistema modificado.

SQL> archive log list;
Modo log de la base de datos Modo de Archivado
Archivado automatico Activado
Destino del archivo USE_DB_RECOVERY_FILE_DEST
Secuencia de log en linea mas antigua 7
Siguiente secuencia de log para archivar 9
Secuencia de log actual 9
SQL>
SQL>
SQL> select * from dba_tables;
select * from dba_tables
*
ERROR en linea 1:
ORA-00604: se ha producido un error a nivel 1 de SQL recursivo
ORA-01116: error al abrir el archivo de base de datos 1
ORA-01110: archivo de datos 1: '/u01/app/oracle/oradata/acme/system01.dbf'
ORA-27041: no se ha podido abrir el archivo
Linux Error: 2: No such file or directory
Additional information: 3


SQL> shutdown abort;
Instancia ORACLE cerrada.
SQL>
SQL> startup mount;
Instancia ORACLE iniciada.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
SQL>
SQL> exit

[oracle@oracle11g acme]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 6 15:30:36 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ACME (DBID=1908099333, not open)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** /u01/app/oracle/oradata/acme/system01.dbf
2 25 UNDOTBS1 *** /u01/app/oracle/oradata/acme/undotbs01.dbf
3 240 SYSAUX *** /u01/app/oracle/oradata/acme/sysaux01.dbf
4 5 USERS *** /u01/app/oracle/oradata/acme/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/oradata/acme/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/acme/temp01.dbf

RMAN> restore datafile 1;

Starting restore at 06-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=211 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/acme/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp tag=TAG20100706T111529
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 06-JUL-10

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/06/2010 15:32:30
ORA-01113: el archivo 1 necesita recuperacion del medio fisico
ORA-01110: archivo de datos 1: '/u01/app/oracle/oradata/acme/system01.dbf'

RMAN> recover database;

Starting recover at 06-JUL-10
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_3_636oyw60_.arc
archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_4_636p0d5o_.arc
archive log thread 1 sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_5_636qno97_.arc
archive log thread 1 sequence 6 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_6_6372gws3_.arc
archive log thread 1 sequence 7 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_7_63748vtz_.arc
archive log thread 1 sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_8_6374gv6n_.arc
archive log filename=/u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_3_636oyw60_.arc thread=1 sequence=3
archive log filename=/u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_4_636p0d5o_.arc thread=1 sequence=4
archive log filename=/u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_5_636qno97_.arc thread=1 sequence=5
archive log filename=/u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_6_6372gws3_.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:02
Finished recover at 06-JUL-10

RMAN> alter database open;

database opened

RMAN> exit


Recovery Manager complete.
[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 15:33:08 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL>

5 LABORATORIO ELIMINAR CONTROLFILE

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 STARTED NO 0 STOPPED
ALLOWED NO ACTIVE UNKNOWN NORMAL NO


SQL>

SQL> shutdown abort;
Instancia ORACLE cerrada.
SQL>
SQL>
SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 15:40:15 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
ORA-00205: error al identificar el archivo de control, compruebe el log de
alertas para obtener mas informacion


SQL> select name from v$controlfile;

ninguna fila seleccionada

SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g acme]$
[oracle@oracle11g acme]$ cd $ORACLE_HOME/dbs
[oracle@oracle11g dbs]$ cat initacme.ora
acme.__db_cache_size=188743680
acme.__java_pool_size=4194304
acme.__large_pool_size=4194304
acme.__shared_pool_size=83886080
acme.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/acme/adump'
*.background_dump_dest='/u01/app/oracle/admin/acme/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/acme/control01.ctl','/u01/app/oracle/oradata/acme/control02.ctl','/u01/app/oracle/oradata/acme/control03.ctl'*.core_dump_dest='/u01/app/oracle/admin/acme/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=8
*.db_name='acme'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=5368709120
*.dispatchers='(PROTOCOL=TCP) (SERVICE=acmeXDB)'
*.job_queue_processes=10
*.nls_language='SPANISH'
*.nls_territory='COLOMBIA'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=200
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=225
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/acme/udump'
[oracle@oracle11g dbs]$


[oracle@oracle11g acme]$ ls
control02.ctl control03.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@oracle11g acme]$ ls -ltr
total 1039032
-rw-r----- 1 oracle oinstall 20979712 jul 6 11:29 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 15:35 redo03.log
-rw-r----- 1 oracle oinstall 52429312 jul 6 15:35 redo02.log
-rw-r----- 1 oracle oinstall 5251072 jul 6 15:35 users01.dbf
-rw-r----- 1 oracle oinstall 26222592 jul 6 15:35 undotbs01.dbf
-rw-r----- 1 oracle oinstall 503324672 jul 6 15:35 system01.dbf
-rw-r----- 1 oracle oinstall 251666432 jul 6 15:35 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 15:35 redo01.log
-rw-r----- 1 oracle oinstall 104865792 jul 6 15:35 example01.dbf
-rw-r----- 1 oracle oinstall 7061504 jul 6 15:36 control03.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 15:36 control02.ctl

SI PODEMOS COPIAR EL ARCHIVO DE CONTROLFILE DE UNO EXISTEN LO HACEMOS

[oracle@oracle11g acme]$ cp control02.ctl control01.ctl
[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 15:42:15 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 15:42:21 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
ORA-01507: base de datos sin montar


Instancia ORACLE cerrada.
SQL>
SQL> startup
Instancia ORACLE iniciada.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
Base de datos abierta.
SQL>

6 LABORATORIO ELIMINAR TODOS LOS CONTROLFILES

[oracle@oracle11g acme]$ rm *.ctl
[oracle@oracle11g acme]$ ls -ltr
total 1045944
-rw-r----- 1 oracle oinstall 20979712 jul 6 11:29 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 15:42 redo03.log
-rw-r----- 1 oracle oinstall 52429312 jul 6 15:42 redo02.log
-rw-r----- 1 oracle oinstall 5251072 jul 6 15:44 users01.dbf
-rw-r----- 1 oracle oinstall 26222592 jul 6 15:44 undotbs01.dbf
-rw-r----- 1 oracle oinstall 503324672 jul 6 15:44 system01.dbf
-rw-r----- 1 oracle oinstall 251666432 jul 6 15:44 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 15:44 redo01.log

[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 15:55:20 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
ORA-00205: error al identificar el archivo de control, compruebe el log de
alertas para obtener mas informacion


SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g acme]$

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 MOUNTED NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> alter database open;
alter database open
*
ERROR en linea 1:
ORA-01589: debe utilizar la opcion RESETLOGS o NORESETLOGS para abrir la base
de datos

ABRIMOS LA BASE DE DATOS CON MODE OPEN RESETLOOGS LO QUE QUIERE DECIR ES QUE GENERA UNA INCARNATION NUEVA

SQL> alter database open resetlogs;

Base de datos modificada.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ACME 1908099333 PARENT 1 17-FEB-08
2 2 ACME 1908099333 CURRENT 464631 06-JUL-10

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ACME 1908099333 PARENT 1 17-FEB-08
2 2 ACME 1908099333 PARENT 464631 06-JUL-10
3 3 ACME 1908099333 CURRENT 584972 06-JUL-10

RMAN>

NOTA: COMO SE GENERO UNA NUEVA INCARNATION ES OBLIGAORIO O SE SUGIERE REALIZAR UN BACKUP FULL DE LA BD Y DE LOS ARCHIVES.

SQL> select dbid from v$database;

DBID
----------
1908099333

SQL> desc v$database;
Nombre ?Nulo? Tipo
----------------------------------------- -------- ----------------------------
DBID NUMBER
NAME VARCHAR2(9)
CREATED DATE
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
PRIOR_RESETLOGS_CHANGE# NUMBER
PRIOR_RESETLOGS_TIME DATE
LOG_MODE VARCHAR2(12)
CHECKPOINT_CHANGE# NUMBER
ARCHIVE_CHANGE# NUMBER
CONTROLFILE_TYPE VARCHAR2(7)
CONTROLFILE_CREATED DATE
CONTROLFILE_SEQUENCE# NUMBER
CONTROLFILE_CHANGE# NUMBER
CONTROLFILE_TIME DATE
OPEN_RESETLOGS VARCHAR2(11)
VERSION_TIME DATE
OPEN_MODE VARCHAR2(10)
PROTECTION_MODE VARCHAR2(20)
PROTECTION_LEVEL VARCHAR2(20)
REMOTE_ARCHIVE VARCHAR2(8)
ACTIVATION# NUMBER
SWITCHOVER# NUMBER
DATABASE_ROLE VARCHAR2(16)
ARCHIVELOG_CHANGE# NUMBER
ARCHIVELOG_COMPRESSION VARCHAR2(8)
SWITCHOVER_STATUS VARCHAR2(20)
DATAGUARD_BROKER VARCHAR2(8)
GUARD_STATUS VARCHAR2(7)
SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3)
FORCE_LOGGING VARCHAR2(3)
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
RECOVERY_TARGET_INCARNATION# NUMBER
LAST_OPEN_INCARNATION# NUMBER
CURRENT_SCN NUMBER
FLASHBACK_ON VARCHAR2(18)
SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3)
DB_UNIQUE_NAME VARCHAR2(30)
STANDBY_BECAME_PRIMARY_SCN NUMBER
FS_FAILOVER_STATUS VARCHAR2(21)
FS_FAILOVER_CURRENT_TARGET VARCHAR2(30)
FS_FAILOVER_THRESHOLD NUMBER
FS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7)
FS_FAILOVER_OBSERVER_HOST VARCHAR2(512)

SQL> select CURRENT_SCN from v$database;

CURRENT_SCN
-----------
586120

ELIMINAR BACKUPS DE LA INCARNATION ANTERIOR

RMAN> list backup summary;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B F X DISK 06-JUL-10 1 1 NO TAG20100706T111529
2 B F X DISK 06-JUL-10 1 1 NO TAG20100706T111529
3 B A X DISK 06-JUL-10 1 1 NO TAG20100706T112133
4 B F X DISK 06-JUL-10 1 1 NO TAG20100706T154914
5 B F A DISK 06-JUL-10 1 1 NO TAG20100706T155041
6 B F A DISK 06-JUL-10 1 1 NO TAG20100706T160049

RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp recid=1 stamp=723640530
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_ncsnf_TAG20100706T111529_636or3gl_.bkp recid=2 stamp=723640627
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_annnn_TAG20100706T112133_636p0h6k_.bkp recid=3 stamp=723640895
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T154914_6375pcdd_.bkp recid=4 stamp=723656955
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/autobackup/2010_07_06/o1_mf_s_723656691_6375s233_.bkp recid=5 stamp=723657446
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/autobackup/2010_07_06/o1_mf_s_723657649_6376d28p_.bkp recid=6 stamp=723657650
Crosschecked 6 objects


RMAN> delete backup expired;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "expired": expecting one of: "backed, completed, controlfile, device, like, of, ;, tag"
RMAN-01007: at line 1 column 15 file: standard input

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "allocate, alter, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, host, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, transport, upgrade, unregister, validate"
RMAN-01007: at line 1 column 22 file: standard input

RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
2 2 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_ncsnf_TAG20100706T111529_636or3gl_.bkp
3 3 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_annnn_TAG20100706T112133_636p0h6k_.bkp
4 4 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T154914_6375pcdd_.bkp

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp recid=1 stamp=723640530
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_ncsnf_TAG20100706T111529_636or3gl_.bkp recid=2 stamp=723640627
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_annnn_TAG20100706T112133_636p0h6k_.bkp recid=3 stamp=723640895
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T154914_6375pcdd_.bkp recid=4 stamp=723656955
Deleted 4 EXPIRED objects


RMAN> list backup summary;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
5 B F A DISK 06-JUL-10 1 1 NO TAG20100706T155041
6 B F A DISK 06-JUL-10 1 1 NO TAG20100706T160049

RMAN> delete backupset 5;

using channel ORA_DISK_1

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
5 5 1 1 AVAILABLE DISK /u01/app/oracle/flash_recovery_area/ACME/autobackup/2010_07_06/o1_mf_s_723656691_6375s233_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/autobackup/2010_07_06/o1_mf_s_723656691_6375s233_.bkp recid=5 stamp=723657446
Deleted 1 objects


RMAN> delete backupset 6;

using channel ORA_DISK_1

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
6 6 1 1 AVAILABLE DISK /u01/app/oracle/flash_recovery_area/ACME/autobackup/2010_07_06/o1_mf_s_723657649_6376d28p_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/autobackup/2010_07_06/o1_mf_s_723657649_6376d28p_.bkp recid=6 stamp=723657650
Deleted 1 objects


RMAN>

REALIZAR BACKUP CON LA NUEVA INCARNATION

7 LABORATORIO BORRAR REDOLOGS

[oracle@oracle11g acme]$ ls -ltr
total 1045944
-rw-r----- 1 oracle oinstall 52429312 jul 6 16:18 redo03.log
-rw-r----- 1 oracle oinstall 52429312 jul 6 16:18 redo02.log
-rw-r----- 1 oracle oinstall 5251072 jul 6 16:32 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 jul 6 16:32 example01.dbf
-rw-r----- 1 oracle oinstall 20979712 jul 6 17:17 temp01.dbf
-rw-r----- 1 oracle oinstall 503324672 jul 6 17:38 system01.dbf
-rw-r----- 1 oracle oinstall 251666432 jul 6 17:38 sysaux01.dbf
-rw-r----- 1 oracle oinstall 26222592 jul 6 17:39 undotbs01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 17:40 redo01.log
-rw-r----- 1 oracle oinstall 7061504 jul 6 17:40 control03.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 17:40 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 17:40 control01.ctl
[oracle@oracle11g acme]$
[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 17:41:11 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/redo03.log
/u01/app/oracle/oradata/acme/redo02.log
/u01/app/oracle/oradata/acme/redo01.log

[oracle@oracle11g acme]$ pwd
/u01/app/oracle/oradata/acme
[oracle@oracle11g acme]$ ls
control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@oracle11g acme]$
[oracle@oracle11g acme]$
[oracle@oracle11g acme]$ ls -ltr
total 1045944
-rw-r----- 1 oracle oinstall 20979712 jul 6 17:17 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 17:47 redo03.log
-rw-r----- 1 oracle oinstall 52429312 jul 6 17:47 redo01.log
-rw-r----- 1 oracle oinstall 5251072 jul 6 17:47 users01.dbf
-rw-r----- 1 oracle oinstall 26222592 jul 6 17:47 undotbs01.dbf
-rw-r----- 1 oracle oinstall 503324672 jul 6 17:47 system01.dbf
-rw-r----- 1 oracle oinstall 251666432 jul 6 17:47 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 17:47 redo02.log
-rw-r----- 1 oracle oinstall 104865792 jul 6 17:47 example01.dbf
-rw-r----- 1 oracle oinstall 7061504 jul 6 17:47 control03.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 17:47 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 17:47 control01.ctl
[oracle@oracle11g acme]$
[oracle@oracle11g acme]$
[oracle@oracle11g acme]$ cp redo01.log redo01.cop
[oracle@oracle11g acme]$ rm redo01.log
[oracle@oracle11g acme]$


[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 17:59:48 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database clear unarchived logfile group 1;

Base de datos modificada.

SQL> alter database open;

Base de datos modificada.

1* select group#,members,status from v$logfile
SQL> ed
Escrito file afiedt.buf

1* select group#,members,status from v$log
SQL> /

GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 1 UNUSED

SQL> alter system switch logfile;

Sistema modificado.

SQL> select group#,members,status from v$log;

GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 INACTIVE
3 1 CURRENT

SQL> alter system switch logfile;

Sistema modificado.

SQL> select group#,members,status from v$log;

GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 CURRENT
3 1 ACTIVE

SQL>


8 LABORATORIO BORRAR CONTROLFILES Y RECUPERARLOS SIN BACKUP
BORRAMOS ARCHIVOS CTL CONTROLFILES

[oracle@oracle11g app]$ cd oracle/oradata/acme/
[oracle@oracle11g acme]$ pwd
/u01/app/oracle/oradata/acme
[oracle@oracle11g acme]$ ls
afiedt.buf control02.ctl example01.dbf redo01.log redo03.log system01.dbf undotbs01.dbf
control01.ctl control03.ctl redo01.cop redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@oracle11g acme]$ rm *.ctl

AL ABRIR LA INSTANCIA SE PRODUCE EL ERROR

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
ORA-00205: error al identificar el archivo de control, compruebe el log de
alertas para obtener mas informacion


SE VALIDA Y PARA EL EJEMPLO NO SE CONSIDERA QUE NO SE TIENE BACKUP

NOS TOCA CREAR EL ARCHIVO DE CONTROLFILE MANUALMENTE

creamos un archivo control.sql con lo siguiente

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "acme" RESETLOGS ARCHIVELOG REUSE
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/acme/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/acme/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/acme/redo03.log' SIZE 50M
DATAFILE
'/u01/app/oracle/oradata/acme/system01.dbf',
'/u01/app/oracle/oradata/acme/undotbs01.dbf',
'/u01/app/oracle/oradata/acme/sysaux01.dbf',
'/u01/app/oracle/oradata/acme/users01.dbf',
'/u01/app/oracle/oradata/acme/example01.dbf'
CHARACTER SET WE8ISO8859P1;


[oracle@oracle11g scripts]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jul 7 10:46:18 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> start control.sql
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes

Archivo de control creado.

SQL> alter database open;
alter database open
*
ERROR en linea 1:
ORA-01589: debe utilizar la opcion RESETLOGS o NORESETLOGS para abrir la base
de datos


SQL> alter database open resetlogs;

Base de datos modificada.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 07/07/10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO