Configuracion Archivos Oracle

Archivo Init.ora

cd $ORACLE_HOME/dbs

letodb.__db_cache_size=7633633280
letodb.__java_pool_size=134217728
letodb.__large_pool_size=2147483648
letodb.__shared_pool_size=2248146944
letodb.__streams_pool_size=134217728
*._awr_flush_threshold_metrics=TRUE
*._bloom_filter_enabled=FALSE
*.audit_file_dest='/u01/app/oracle/admin/letodb/adump'
*.audit_sys_operations=TRUE
*.audit_trail='OS'
*.background_dump_dest='/u01/app/oracle/admin/letodb/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/letodb/control01.ctl','/u02/letodb/control02.ctl','/u01/letodb/control03.ctl'#Restore Controlfile
*.core_dump_dest='/u01/app/oracle/admin/letodb/cdump'
*.cursor_sharing='SIMILAR'
*.db_block_size=8192
*.db_cache_size=1024,M
*.db_create_file_dest='/u01/letodb'
*.db_domain=''
*.db_file_multiblock_read_count=64
*.db_keep_cache_size=536870912
*.db_name='letodb'
*.db_recovery_file_dest='/u02/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=429496729600
*.dispatchers='(PROTOCOL=TCP)(dispatchers=50)'
*.fast_start_mttr_target=300
*.java_pool_size=134217728
*.job_queue_processes=20
*.large_pool_size=2147483648
*.log_archive_dest_1=''
*.log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.log_archive_dest_2=''
*.log_archive_format='archive_%T_%S_%R'
*.log_checkpoints_to_alert=TRUE
*.max_dispatchers=2000
*.max_dump_file_size='5242880'
*.max_shared_servers=500
*.nls_date_format='dd-mm-yyyy'
*.nls_language='LATIN AMERICAN SPANISH'
*.nls_territory='COLOMBIA'
*.nls_timestamp_format='dd-mm-yyyy hh24:mi:ss.ff'
*.nls_timestamp_tz_format='dd-mm-yyyy hh24:mi:ss.ff TZH:TZM'
*.open_cursors=3000
*.optimizer_mode='FIRST_ROWS_100'
*.parallel_max_servers=64
*.parallel_min_servers=64
*.pga_aggregate_target=1073741824
*.processes=4500
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
letodb.resource_manager_plan='SPE_PLAN'
*.resource_manager_plan='SYSTEM_PLAN'
*.session_cached_cursors=1000
*.sessions=3000
*.sga_max_size=16106127360
*.sga_target=12884901888
*.shared_pool_reserved_size=213909504
*.shared_pool_size=2147483648
*.shared_servers=25
*.sort_area_retained_size=0
*.sql_trace=FALSE
*.statistics_level='TYPICAL'
*.streams_pool_size=128M
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=7200
*.undo_tablespace='UNDO'
*.user_dump_dest='/u01/app/oracle/admin/letodb/udump'
*.utl_file_dir='/tmp'

Archivo listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = letodb)
(SID_NAME = letodb)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = CT1BOSUNBD-SPE1)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF


Archivo tnsnames.ora


# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LETODB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.101.13)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LETODB)
)
)

CAT10G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dboraclenew)(PORT = 1528))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cat10g)
)
)

Archivo Oratab

-bash-3.00$ pwd
/var/opt/oracle
-bash-3.00$ cat oratab
#



# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
letodb:/u01/app/oracle/product/10.2.0:N
-bash-3.00$

Activar Mode Archivelog Oracle

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

ORACLE 10G

SQL> shutdown immediate
SQL> startup mount
SQL> alter system set log_archive_dest_n=' ' scope = spfile;
SQL> alter database archivelog;
SQL> alter database flashback on;
SQL> alter database open;
SQL> alter system checkpoint;
SQL> show parameter log_archive_dest;

ORACLE 9I

ACTIVAR ARCHIVELOG 9I

SQL> select * from v$option
SQL> alter database archivelog;
SQL> alter system set log_archive_start = true scope=spfile;
SQL> alter system set log_archive_dest_1 = 'LOCATION=/app/oracle/archives';

*. log_archive_start = true en pfile

TABLAS PARTICIONADAS

CREATE TABLE "DBO"."TEM_SEG_OFERTA" ("HGO_ID" NUMBER NOT NULL,
"OFT_ID" NUMBER(9) NOT NULL, "FSO_ID" NUMBER(2) NOT NULL,
"HGO_FCH_REGISTRO" DATE NOT NULL, "FUN_ID" NUMBER(4),
"HGO_DESCRIPCION" VARCHAR2(300 byte)
)
PARTITION BY RANGE (HGO_FCH_REGISTRO)
(PARTITION HIS_SEG_OFERTA_2003 VALUES LESS THAN (TO_DATE('01/01/2004', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2004 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2005 VALUES LESS THAN (TO_DATE('01/01/2006', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2006 VALUES LESS THAN (TO_DATE('01/01/2007', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2007 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2008 VALUES LESS THAN (TO_DATE('01/01/2009', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2009 VALUES LESS THAN (TO_DATE('01/01/2010', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2010 VALUES LESS THAN (TO_DATE('01/01/2011', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2011 VALUES LESS THAN (TO_DATE('01/01/2012', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2012 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2013 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2019 VALUES LESS THAN (TO_DATE('01/01/2020', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2020 VALUES LESS THAN (TO_DATE('01/01/2021', 'DD/MM/YYYY')),
PARTITION MAX_VALUE VALUES LESS THAN (MAXVALUE)
TABLESPACE "USERS")
/

ALTER TABLE big_table
SPLIT PARTITION MAX_VALUE AT (TO_DATE('01-01-2021','DD-MM-YYYY'))
INTO (PARTITION big_table_2021, PARTITION MAX_VALUE )


ALTER TABLE BIG_TABLE
ADD PARTITION big_table_2010 VALUES LESS THAN (TO_DATE('01/01/2011', 'DD/MM/YYYY'));

ALTER TABLE BIG_TABLE DROP PARTITION BIG_TABLE_2010;

ALTER TABLE BIG_TABLE ADD PARTITION big_table_2009 VALUES LESS THAN (MAXVALUE);

Funcion Complejidad Clave Oracle 10g

cd $ORACLE_HOME/rdbms/admin

/u01/app/oracle/product/10.2.0/rdbms/admin
-bash-3.00$ ls -l utlpwdmg.sql
-rw-r--r-- 1 oracle oinstall 5737 Sep 11 2000 utlpwdmg.sql
-bash-3.00$

Rem
Rem $Header: utlpwdmg.sql 31-aug-2000.11:00:47 nireland Exp $
Rem
Rem utlpwdmg.sql
Rem
Rem Copyright (c) Oracle Corporation 1996, 2000. All Rights Reserved.
Rem
Rem NAME
Rem utlpwdmg.sql - script for Default Password Resource Limits
Rem
Rem DESCRIPTION
Rem This is a script for enabling the password management features
Rem by setting the default password resource limits.
Rem
Rem NOTES
Rem This file contains a function for minimum checking of password
Rem complexity. This is more of a sample function that the customer
Rem can use to develop the function for actual complexity checks that the
Rem customer wants to make on the new password.
Rem
Rem MODIFIED (MM/DD/YY)
Rem nireland 08/31/00 - Improve check for username=password. #1390553
Rem nireland 06/28/00 - Fix null old password test. #1341892
Rem asurpur 04/17/97 - Fix for bug479763
Rem asurpur 12/12/96 - Changing the name of password_verify_function
Rem asurpur 05/30/96 - New script for default password management
Rem asurpur 05/30/96 - Created
Rem

-- This script sets the default password resource parameters
-- This script needs to be run to enable the password features.
-- However the default resource parameters can be changed based
-- on the need.
-- A default password complexity function is also provided.
-- This function makes the minimum complexity checks like
-- the minimum length of the password, password not same as the
-- username, etc. The user may enhance this function according to
-- the need.
-- This function must be created in SYS schema.
-- connect sys/ as sysdba before running the script

CREATE OR REPLACE FUNCTION verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);

BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;<=>?_';

-- Check if the password is same as the username
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;

-- Check for the minimum length of the password
IF length(password) < 4 THEN
raise_application_error(-20002, 'Password length less than 4');
END IF;

-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
raise_application_error(-20002, 'Password too simple');
END IF;

-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. Check for the digit
isdigit:=FALSE;
m := length(password);
FOR i IN 1..10 LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(digitarray,i,1) THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
END IF;
-- 2. Check for the character
<>
ischar:=FALSE;
FOR i IN 1..length(chararray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(chararray,i,1) THEN
ischar:=TRUE;
GOTO findpunct;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;
-- 3. Check for the punctuation
<>
ispunct:=FALSE;
FOR i IN 1..length(punctarray) LOOP
FOR j IN 1..m LOOP
IF substr(password,j,1) = substr(punctarray,i,1) THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ispunct = FALSE THEN
raise_application_error(-20003, 'Password should contain at least one \
digit, one character and one punctuation');
END IF;

<>
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL THEN
differ := length(old_password) - length(password);

IF abs(differ) < 3 THEN
IF length(password) < length(old_password) THEN
m := length(password);
ELSE
m := length(old_password);
END IF;

differ := abs(differ);
FOR i IN 1..m LOOP
IF substr(password,i,1) != substr(old_password,i,1) THEN
differ := differ + 1;
END IF;
END LOOP;

IF differ < 3 THEN
raise_application_error(-20004, 'Password should differ by at \
least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/

-- This script alters the default parameters for Password Management
-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION verify_function;

Proyecto Seguridad Bases de Datos Oracle

1. VALIDACION DEFAULT PASSWORD

EJECUTAR CON EL USUARIO SYS

SQL> set serveroutput on

SQL> set serveroutput on;
SQL> execute dba_valida_usuario_password;
UHI
UHI2
GENAP
RMAN

PL/SQL procedure successfully completed.

create or replace procedure DBA_VALIDA_USUARIO_PASSWORD as
hexpw varchar2(30);
modpw varchar2(30);
un varchar2(30);
cursor c1 is select username,password from dba_users
where length(trim(password)) = 16;
begin
--execute immediate 'TRUNCATE TABLE DBO.AUD_VAL_USUARIO_PASSWORD';
for i in c1 loop
hexpw := i.password;
un := i.username;
execute immediate 'alter user '||un||' identified by '||un;
select password into modpw from dba_users where username = un;
if modpw = hexpw then
dbms_output.put_line(un);
-- INSET INTO DBO.AUD_VAL_USUARIO_PASSWORD VALUES(un);
else
EXECUTE IMMEDIATE 'ALTER USER '||UN||' IDENTIFIED BY VALUES '''||HEXPW||'''';
end if;
commit;
end loop;
end;
/

osp_install.sql

PROMPT To install Oracle Security Probe, you need log in
PROMPT as a user with DBA or CREATE USER privileges.
PROMPT

CONNECT dbo/clave@letodb

@@osp_install_user.sql
@@osp_install_tab.sql
@@osp_install_data.sql
@@osp_install_pack.sql
@@osp_exec.sql

osp_install_user.sql

GRANT create session TO dbo;
GRANT create procedure TO dbo;
GRANT create table TO dbo;
GRANT select ON sys.dba_users TO dbo;
GRANT select_catalog_role TO dbo;

osp_install_tab.sql

DROP TABLE ORA_ACCOUNTS
/

CREATE TABLE ORA_ACCOUNTS
( product VARCHAR2(30)
, security_level NUMBER(1)
, username VARCHAR2(30)
, password VARCHAR2(30)
, hash_value VARCHAR2(30)
, commentary VARCHAR2(200))
TABLESPACE USERS
/

osp_install_data.sql

insert into ORA_ACCOUNTS
(product
, security_level
, username
, password
, hash_value
, commentary
) values (
'Oracle'
,3
,'BRIO_ADMIN'
,'BRIO_ADMIN'
,'EB50644BE27DF70B'
,'BRIO_ADMIN is an account of a 3rd party product.'
)
/

osp_install_pack.sql

CREATE OR REPLACE PACKAGE osp_pack AS
PROCEDURE default_pass_check;
END osp_pack;
/

show errors


CREATE OR REPLACE PACKAGE BODY osp_pack
AS
PROCEDURE default_pass_check
IS
CURSOR c_dba_users IS
SELECT username, password, account_status
FROM dba_users;

v_userpass_exists NUMBER;
v_default_password VARCHAR2(30);
v_security_level NUMBER;
v_tel_defaults NUMBER := 0;
v_commentary VARCHAR2(200);

BEGIN

dbms_output.put_line('Oracle accounts with default passwords');
dbms_output.put_line('======================================'||CHR(10));

FOR r_dba_users IN c_dba_users
LOOP
<>

SELECT count(*)
INTO v_userpass_exists
FROM ORA_ACCOUNTS
WHERE username=r_dba_users.username
AND hash_value=r_dba_users.password;

IF v_userpass_exists = 1 THEN

v_tel_defaults := v_tel_defaults + 1;

SELECT password, security_level, commentary
INTO v_default_password, v_security_level, v_commentary
FROM ORA_ACCOUNTS
WHERE username=r_dba_users.username
AND hash_value=r_dba_users.password;

dbms_output.put_line('Username: '||r_dba_users.username);
dbms_output.put_line('Password: '||v_default_password);
IF r_dba_users.account_status LIKE '%LOCKED%' THEN
dbms_output.put_line('Status: '||r_dba_users.account_status);
END IF;
dbms_output.put_line('-----------------------------------------------');

dbms_output.put_line('WARNING! The password of '||r_dba_users.username||' is a default '|| 'password. It is well known to hackers'||CHR(10));
dbms_output.put_line('Additional information:');
dbms_output.put_line(v_commentary||CHR(10)||CHR(10));
END IF;

END LOOP userpass_loop;

IF v_tel_defaults = 0 THEN
dbms_output.put_line('No default passwords have been detected.');
END IF;

END default_pass_check;

END osp_pack;
/

show errors

osp_exec.sql

SET PAGESIZE 1000
SET HEADING off
SET VERIFY off
SET FEEDBACK off
SET ARRAYSIZE 1
SET LINESIZE 80
TTITLE off

connect dbo/clave@conexion

SET SERVEROUTPUT on SIZE 100000

SPOOL /export/home/oracle/rman/sql/spools/default_password1.log


-- PROMPT
-- PROMPT **********************************************************************
-- PROMPT * *
-- PROMPT * D e f a u l t p a s s w o r d s *
-- PROMPT * *
-- PROMPT **********************************************************************



exec osp_pack.default_pass_check;

SPOOL off

SET LINESIZE 80
SET TIMING off
SET VERIFY off
SET NUMWIDTH 10
SET HEADING off

RMAN Recovery

RECOVERY DATAFILE TAPE VERITAS

connect catalog rman/rman@cat9i;
set dbid=888297452;
connect target;

run {
ALLOCATE CHANNEL ch00
TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=heo_01_v4902,NB_ORA_POLICY=BD_Online_orac_heo_01_V4902,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Application-Backup)';
restore datafile 11;
recover database;
mount database;
RELEASE CHANNEL ch00;
}

VISTAS CATALOGO RMAN

How does one create a RMAN recovery catalog?
Submitted by admin on Sun, 2005-10-16 03:36.
Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example:
sqlplus sys

SQL> create user rman identified by rman;

CREATE SMALLFILE
TABLESPACE "TOOLS"
LOGGING
DATAFILE '/u03/app/oracle/oradata/cat11g/tools01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

SQL> alter user rman default tablespace tools temporary tablespace temp;

SQL> select username,default_tablespace from dba_users;

SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;

rman catalog rman/rmanRMAN>
create catalog tablespace tools;
RMAN> exit;

/etc/hosts Agregar Servidor de Backups

172.28.250.207 dboraclenew



REGISTRAR UNA INSTANCIA EN EL CATALOGO DE RMAN

telbesg1() -TCPPGA- admin > rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Mar Ago 18 14:13:04 2009

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

connected to target database: TCPPGA (DBID=3926676825)

RMAN> connect catalog rman/rman@cat10g;

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

VER INSTANCIAS REGISTRADAS EN EL CATALOGO DE RMAN

SQL> connect rman/password
Conectado.
SQL> select * from rc_database;

DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOG
---------- ---------- ---------- -------- ----------------- --------
1 2 3173394589 BDDWSGRD 526290 20/12/07
2969 2970 3814852239 LETODB 186029 03/03/07
45025 45026 217801452 IVDB 1 04/06/07
50801 50802 1638475926 VPDB 543066 20/10/08
52385 52386 262046602 IVDB 1 20/10/08
68203 68204 1927027167 CAREDM 1 14/11/08
73337 73338 3851637185 CAREOLTP 1 14/11/08
78542 78543 3392160091 SIRS 1 28/03/08

8 filas seleccionadas.

SQL> /

UNREGISTER DATABASE ORACLE 9I

SQL> select * from rc_database;

DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
1 2 2220393743 SERA 1 07-MAR-07
2276 2277 2222621424 SERA 1 02-APR-07
8209 8210 887613172 ORAC 1 17-APR-07
8438 8439 887777038 ORAC 1 19-APR-07
8616 8617 888297452 ORAC 1 25-APR-07
8845 8846 882104236 ORAC 1 15-FEB-07
9301 9302 2128016642 CAT9I 174968 05-JUN-07
44081 125951 4007731659 NETCOOL 9510756 07-SEP-07
135468 135469 895732053 ORAC 1 18-JUL-07

9 rows selected.

SQL> select db_key from db where db_id=4007731659;

DB_KEY
----------
44081

SQL> exec dbms_rcvcat.unregisterdatabase(44081,4007731659);


TABLAS DE CATALOGOS

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RC_BACKUP_CONTROLFILE VIEW
RC_BACKUP_CORRUPTION VIEW
RC_BACKUP_DATAFILE VIEW
RC_BACKUP_PIECE VIEW
RC_BACKUP_REDOLOG VIEW
RC_BACKUP_SET VIEW
RC_BACKUP_SPFILE VIEW
RC_CHECKPOINT VIEW
RC_CONTROLFILE_COPY VIEW
RC_COPY_CORRUPTION VIEW
RC_DATABASE VIEW

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RC_DATABASE_BLOCK_CORRUPTION VIEW
RC_DATABASE_INCARNATION VIEW
RC_DATAFILE VIEW
RC_DATAFILE_COPY VIEW
RC_LOG_HISTORY VIEW
RC_OFFLINE_RANGE VIEW
RC_PROXY_CONTROLFILE VIEW
RC_PROXY_DATAFILE VIEW
RC_REDO_LOG VIEW
RC_REDO_THREAD VIEW
RC_RESYNC VIEW

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RC_RMAN_CONFIGURATION VIEW
RC_STORED_SCRIPT VIEW
RC_STORED_SCRIPT_LINE VIEW
RC_TABLESPACE VIEW
RLH TABLE
RR TABLE
RT TABLE
SCR TABLE
SCRL TABLE
TS TABLE
TSATT TABLE

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
XCF TABLE
XDF TABLE


UNREGISTER DATABASE

RMAN> CONNECT CATALOG rman@catdb

recovery catalog database Password: password
connected to recovery catalog database

RMAN> SET DBID 28014364;

executing command: SET DBID
database name is "PROD" and DBID is 28014364

RMAN> UNREGISTER DATABASE;

Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog


Unregister a Database From RMAN
First we start up RMAN with a connection to the catalog and the target, making a note of the DBID in the banner:

C:\>rman catalog=rman/rman@dba1 target=sys/password@w2k1

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: W2K1 (DBID=1487421514)
connected to recovery catalog database

RMAN>Next we list and delete any backupsets recorded in the repository:

RMAN> LIST BACKUP SUMMARY;
RMAN> DELETE BACKUP DEVICE TYPE SBT;
RMAN> DELETE BACKUP DEVICE TYPE DISK;Next we connect to the RMAN catalog owner using SQL*Plus and issue the following statement:


SQL> CONNECT rman/rman@dba1
Connected.
SQL> SELECT db_key, db_id
2 FROM db
3 WHERE db_id = 1487421514;

DB_KEY DB_ID
---------- ----------
1 1487421514

1 row selected.

SQL>The resulting key and id can then be used to unregister the database:

SQL> EXECUTE dbms_rcvcat.unregisterdatabase(1, 1487421514);

PL/SQL procedure successfully completed.

SQL>

RMAN Script Backup Disk - Tape

backup_database_disk_TCOPGE.sh

BACKUP DISK

#/usr/bin/ksh
export ORACLE_HOME=/softw/app/oracle/product/10.2.0/db
export PATH=/softw/app/oracle/product/10.2.0/db/bin:/home/oracle/rman/scripts:/usr/local/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:.
export RMAN_SCRIPTS=/home/oracle/rman/scripts

export ORACLE_SID=TCOPGE

export LOGS_RMAN=/altamira_bd/TCOPGE/arch/backup/TCOPGE/logs
cd $ORACLE_HOME/bin
rman target / LOG=$LOGS_RMAN/backup_database_disk_TCOPGE.log APPEND CMDFILE=$RMAN_SCRIPTS/backup_database_disk_TCOPGE.rcv


backup_database_disk_TCOPGE.rcv

run {
allocate channel ch01 device type disk format '/altamira_bd/TCOPGE/arch/backup/TCOPGE/database/%U' ;
backup as compressed backupset database ;
backup current controlfile;
crosscheck backup;
crosscheck backup archivelog all;
delete noprompt obsolete device type disk;
delete noprompt expired backup;
release channel ch01;
}

backup_archivelog_disk_TCOPGE.sh

#/usr/bin/ksh
export ORACLE_HOME=/softw/app/oracle/product/10.2.0/db
export PATH=/softw/app/oracle/product/10.2.0/db/bin:/home/oracle/rman/scripts:/usr/local/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:.
export RMAN_SCRIPTS=/home/oracle/rman/scripts

export ORACLE_SID=TCOPGE

export LOGS_RMAN=/altamira_bd/TCOPGE/arch/backup/TCOPGE/logs

rman target / LOG=$LOGS_RMAN/backup_archivelog_disk_TCOPGE.log APPEND CMDFILE=$RMAN_SCRIPTS/backup_archivelog_disk_TCOPGE.rcv

backup_archivelog_disk_TCOPGE.rcv

run {
allocate channel ch01 device type disk format '/altamira_bd/TCOPGE/arch/backup/TCOPGE/archives/%U' ;
backup as COMPRESSED BACKUPSET archivelog all delete input ;
crosscheck archivelog all;
release channel ch01;
}

00 01 * * * /home/oracle/rman/scripts/backup_database_disk_TCOPGE.sh
00 14,20 * * * /home/oracle/rman/scripts/backup_archivelog_disk_TCOPGE.sh


BACKUP TAPE CLUSTER

connect rcvcat rman/rman@cat9i
connect target /

RUN {
ALLOCATE CHANNEL ch00
TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64";
SEND 'NB_ORA_CLIENT=telbebdsdp1,NB_ORA_POLICY=BD_Online_ALTAMIRA_SDPTE01';
BACKUP
INCREMENTAL LEVEL=0
FORMAT 'db_%s_%p_%t'
TAG 'BD_SDPTE01_ALTAMIRA'
DATABASE;

RELEASE CHANNEL ch00;

# Backup Archived Logs
sql 'alter system archive log current';

ALLOCATE CHANNEL ch00
TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64" connect rman/rman@RAC0;

ALLOCATE CHANNEL ch01
TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64" connect rman/oracle@RAC1;

ALLOCATE CHANNEL ch03
TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64" connect rman/oracle@RAC2;

ALLOCATE CHANNEL ch04
TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64" connect rman/oracle@RAC3;

BACKUP
FORMAT 'arch_%s_%p_%t'
ARCHIVELOG
ALL
DELETE INPUT;

RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;

# Control file backup

ALLOCATE CHANNEL ch00
TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64";
SEND 'NB_ORA_CLIENT=telbebdsdp1,NB_ORA_POLICY=BD_Online_ALTAMIRA_SDPTE01';
BACKUP
FORMAT 'ctrl_%s_%p_%t'
CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
}

Script Export

ORACLE_HOME=/u01/app/oracle/product/10.2.0;
export ORACLE_HOME
PATH=.:/bin:$ORACLE_HOME/bin:/usr/bin:/usr/local/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/dt/bin;
export PATH

ORACLE_SID=sid
export ORACLE_SID
sid=$ORACLE_SID

# Variables de Configuracion
fecha=`date +%d-%m-%y`

# Stamp del nombre del script ejecutado
echo " fullexport.sh "

# Time stamp de ejecucion del script
date '+DATE: %m/%d/%y%nTIME: %H:%M:%S'

directorio=/u02/exports/$sid
archivo=$directorio/$sid-$fecha.dmp
archivolog=$directorio/log/$sid-$fecha.log

# Borrado de export de mas de 7 dias
/bin/find $directorio \( -name '*.gz' \) -mtime +7 -exec rm {} \;
/bin/find $directorio/log \( -name '*.log' \) -mtime +7 -exec rm {} \;

$ORACLE_HOME/bin/exp dbo/power_ext@$sid file=$archivo full=y log=$archivolog buffer=50000 grants=y consistent=y compress=y

/usr/bin/gzip $archivo


0 23 * * * /u02/exports/full_export.sh > /dev/null

STARTING RMAN

Enabling Archivelog Mode

SQL> connect sys/chaya as sysdba
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

SQL> select log_mode from v$database;
LOG_MODE
--------------------
ARCHIVELOG

Starting and Exiting RMAN

Start the RMAN executable at the operating system command line without specifying any connection options, as in this example:

% rman

Start the RMAN executable at the operating system command line while connecting to a target database and, possibly, a recovery catalog, as in these examples:

% rman TARGET /
% rman TARGET SYS/oracle@trgt NOCATALOG
% rman TARGET / CATALOG rman/cat@catdb

Setting Globalization Support Environment Variables for RMAN

The following example shows typical language and date format settings:

NLS_LANG=american
NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'

Recovery Window-Based Backup Retention Policy
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;

Redundancy-Based Backup Retention Policy
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

3.1.3 Entering RMAN Commands at the Command Prompt
When the RMAN client is ready for your commands, it displays the command prompt, as in this example:

RMAN>

Enter commands for RMAN to execute. For example:

RMAN> CONNECT TARGET /
RMAN> CONNECT CATALOG rman/rman@inst2

RMAN> BACKUP DATABASE ;

Most RMAN commands take a number of parameters and must end with a semicolon. (The few exceptions, such as STARTUP, SHUTDOWN, and CONNECT, can be used with or without a semicolon.)

To display information about your backup, use the list backup command as follows:

RMAN> list backup;

Determine the location of a target database datafile so that you can rename it to simulate
media failure:

RMAN> report schema;

When you enter a line of text that is not a complete command, RMAN prompts for continuation input with a line number. For example:

RMAN> BACKUP DATABASE
2> INCLUDE CURRENT
3> CONTROLFILE
4> ;

Using Command Files with RMAN
For repetitive tasks, you can create a text file containing RMAN commands, and start the RMAN client with the @ argument, followed by a filename. For example, create a text file cmdfile1 in the current directory contained one line of text as shown here:

BACKUP DATABASE INCLUDE CURRENT CONTROLFILE;

You can run this command file from the command line as shown in this example, and the command contained in it is executed:

% rman TARGET / @cmdfile1

RMAN> @cmdfile1