Purging statistics from the SYSAUX tablespace

set linesize 120
set pagesize 100

SELECT * FROM V$SYSAUX_OCCUPANTS;

COLUMN "Item" FORMAT A25
COLUMN "Space Used (GB)" FORMAT 999.99
COLUMN "Schema" FORMAT A25
COLUMN "Move Procedure" FORMAT A40

SELECT occupant_name "Item",
space_usage_kbytes/1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 1
/

select snap_interval, retention
from dba_hist_wr_control
/

select OCCUPANT_NAME,SCHEMA_NAME,SPACE_USAGE_KBYTES from V$sysaux_occupants;
/
SELECT
snap_id, begin_interval_time, end_interval_time
FROM
SYS.WRM$_SNAPSHOT
WHERE
snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)
UNION
SELECT
snap_id, begin_interval_time, end_interval_time
FROM
SYS.WRM$_SNAPSHOT
WHERE
snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)
/

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
15720 11-08-2008 19:00:25.442 11-08-2008 20:00:40.792
24219 21-09-2009 10:00:19.262 21-09-2009 11:00:41.262

BEGIN
dbms_workload_repository.drop_snapshot_range(low_snap_id => 7556, high_snap_id=>15000);
END;
/


select dbms_stats.get_stats_history_retention from dual;

Set retention of old stats to 10 days

exec dbms_stats.alter_stats_history_retention(10);

Purge stats older than 10 days (best to do this in stages if there is a lot of data (sysdate-30,sydate-25 etc)

exec DBMS_STATS.PURGE_STATS(SYSDATE-10);

Show available stats that have not been purged

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
26-08-2008 19:38:08.580380000 -05:00

Show how big the tables are and rebuild after stats have been purged

col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6

set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type='TABLE'
group by segment_name,segment_type order by 1 asc

MB SEGMENT_NAME SEGMEN
---------- ---------------------------------------- ------
0 WRI$_OPTSTAT_OPR TABLE
0 WRI$_OPTSTAT_AUX_HISTORY TABLE
88 WRI$_OPTSTAT_TAB_HISTORY TABLE
126 WRI$_OPTSTAT_IND_HISTORY TABLE
158 WRI$_OPTSTAT_HISTGRM_HISTORY TABLE
4,482 WRI$_OPTSTAT_HISTHEAD_HISTORY TABLEShow how big the indexes are ready for a rebuild after stats have been purged


col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%'
and segment_type='INDEX'
group by segment_name,segment_type order by 1 asc
/

MB SEGMENT_NAME SEGMEN
---------- ---------------------------------------- ------
0 WRH$_OPTIMIZER_ENV_PK INDEX
0 I_WRI$_OPTSTAT_OPR_STIME INDEX
0 I_WRI$_OPTSTAT_AUX_ST INDEX
88 I_WRI$_OPTSTAT_TAB_ST INDEX
105 I_WRI$_OPTSTAT_IND_ST INDEX
105 I_WRI$_OPTSTAT_H_ST INDEX
195 I_WRI$_OPTSTAT_TAB_OBJ#_ST INDEX
213 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
214 I_WRI$_OPTSTAT_IND_OBJ#_ST INDEX
2,055 I_WRI$_OPTSTAT_HH_ST INDEX
3,883 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX


Note that you cannot enable row movement and shrink the tables as the indexes are function based

alter table WRI$_OPTSTAT_IND_HISTORY enable row movement;
alter table WRI$_OPTSTAT_IND_HISTORY shrink space;


select 'alter table '||segment_name||' move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE'

Run the rebuild table commands – note that this does cause any gather_stats jobs to fail

alter table WRI$_OPTSTAT_TAB_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_IND_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_AUX_HISTORY move tablespace sysaux;
alter table WRI$_OPTSTAT_OPR move tablespace sysaux;
alter table WRH$_OPTIMIZER_ENV move tablespace sysaux;Script to generate rebuild statements

select 'alter index '||segment_name||' rebuild online parallel (degree 14);' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'

select di.index_name,di.index_type,di.status from dba_indexes di
where di.tablespace_name = 'SYSAUX'
and di.index_name like '%OPT%'
order by 1 asc


SQL>
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
I_WRI$_OPTSTAT_AUX_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_HH_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_H_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_IND_OBJ#_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_IND_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_OPR_STIME FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_TAB_OBJ#_ST FUNCTION-BASED NORMAL VALID
I_WRI$_OPTSTAT_TAB_ST FUNCTION-BASED NORMAL VALID
WRH$_OPTIMIZER_ENV_PK NORMAL VALID

Finally lets see what space has been saved with a retention date of 1 day and a gather schema stats for the SYSASDM schema

exec dbms_stats.alter_stats_history_retention(1);

select dbms_stats.get_stats_history_retention from dual;

MB SEGMENT_NAME SEGMEN
---------- ---------------------------------------- ------
0 WRI$_OPTSTAT_OPR TABLE
0 WRI$_OPTSTAT_AUX_HISTORY TABLE
3 WRI$_OPTSTAT_TAB_HISTORY TABLE
4 WRI$_OPTSTAT_IND_HISTORY TABLE
8 WRI$_OPTSTAT_HISTGRM_HISTORY TABLE
104 WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE

MB SEGMENT_NAME SEGMEN
---------- ---------------------------------------- ------
0 WRH$_OPTIMIZER_ENV_PK INDEX
0 I_WRI$_OPTSTAT_OPR_STIME INDEX
0 I_WRI$_OPTSTAT_AUX_ST INDEX
2 I_WRI$_OPTSTAT_IND_ST INDEX
2 I_WRI$_OPTSTAT_TAB_ST INDEX
3 I_WRI$_OPTSTAT_TAB_OBJ#_ST INDEX
4 I_WRI$_OPTSTAT_IND_OBJ#_ST INDEX
5 I_WRI$_OPTSTAT_H_ST INDEX
9 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
41 I_WRI$_OPTSTAT_HH_ST INDEX
96 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX

Snapshots. Config en DBA_HIST_WR_CONTROL. Ej de cambio a intervalo de 30días cada
30min (expresado en minutos y si intervalo=0 no se calculan más snapshots):


SELECT snap_id, startup_time, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
ORDER BY 1,2

– DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(43200,30);
– DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (22, 32); Borra
rango

• Baselines. Pareja de snapshots (q ya no se borrarán).

Speed up ‘removal’ of old AWR reports

removing the entries takes ages and fails on undo errors … Metalink note Doc ID: 852028.1 states that I can safely remove the AWR metadata tables and recreate them.

SQL> connect / as sysdba
SQL> @?/rdbms/admin/catnoawr.sql
SQL> @?/rdbms/admin/catawrtb.sql


– DBMS_WORKLOAD_REPOSITORY.create_baseline (210, 220, 'batch baseline'); Snapshots
inicial y final, y nombre para el baseline

– DBMS_WORKLOAD_REPOSITORY.drop_baseline ( 'batch baseline', 'FALSE); Si
TRUE, borra los snaphosts asociados

• Informes AWR en $ORACLE_HOME/rdbms/admin (con salida en HTML o TEXTO):
– awrrpt.sql, pedirá formato salida (text o html), los snapshots inicial y final, y el nombre
del fichero del informe. Existe awrrpti.sql q permite seleccionar una instancia.
– awrsqrpt.sql, muestra estadísticas de una sentencia SQL para un rango de snapshots.
– awrddrpt.sql, compara atributos detallados de rendimiento y valores de configuración
entre dos períodos de tiempo.

Oracle Multi-master Replication

Oracle Multi-master Replication

I’ll start with a high-level view of multi-master replication and introduce some basic concepts. Multi-master replication is such a complex topic that I can’t fully address every issue about it in this space. However, I hope you’ll be happy with a conceptual explanation of the mechanisms.

In a nutshell, multi-master replication is nothing more than a coordinated set of updateable snapshots. By “updateable,” I mean that the snapshot allows the FOR UPDATE clause in the snapshot definition. To illustrate this concept, refer to the example below, where you’ll see that the snapshot is allowed to propagate updates back to the master table.

create snapshot
customer_updatable_snap
refresh fast start with sysdate
next sysdate + 1/24
for update
query rewrite
as
select * from customer@master_site;

Multi-master Conflicts and Resolutions

At first blush, multi-master replication may appear straightforward. However, there is a dark side to the process. Whenever a snapshot has the ability to send updates to other “master” tables, you always run the risk of update conflicts. So what’s the best way to avoid and/or resolve those conflicts? Let’s start the lesson by reviewing multi-master conflict avoidance. Then we’ll dive head-first into the details of procedural replication, so we can see how it all fits together.

An update conflict occurs when one remote user overlays the updates made by a user on another database. Your multi-master replication model should detect and resolve conflicts. Unfortunately, detecting and resolving those conflicts can get extremely complex. Let’s start by looking at what conflicts can occur, and then we’ll look at mechanisms for resolving them.

Conflict Types
Here are the most common types of conflicts you’ll encounter with multi-master replication:

*
Uniqueness conflict — This conflict results from an attempt from two different sites to insert records with the same primary key. To avoid uniqueness conflicts, you can choose from three available options. Those three pre-built methods are called Append Site Name To Duplicate Value, Append Sequence To Duplicate Value, and Discard Duplicate Value.
*
Update conflict — This conflict is caused by simultaneous update operations on the same record.
*
Delete conflict — This type of conflict occurs when one transaction deletes a row that another transaction updates (before the delete is propagated).

Oracle provides several pre-written scripts to help in resolving conflicts. In the case of update conflicts, your only option is to write conflict-resolution routines, and deal with each conflict on a case-by-case basis. Fortunately, Oracle provides several pre-built methods for creating the routines. Click here for the details about Oracle conflict-resolution techniques.

Conflict Resolution Mechanisms
Here are the most common mechanisms at your disposal for resolving conflicts:

*
Latest Timestamp Value. With this simple technique, you apply updates as they are received. Based on timestamp value, the most recent updates overlays prior updates. This approach can result in situations where one user’s update gets overlaid by a more recent update.

*
Earliest Timestamp Value. This mechanism is the opposite of the latest timestamp value, in that the first update overlays subsequent updates. As you’d expect, not many shops use this method, but it is an option.

*
Minimum and Maximum Value. This mechanism may be used when the advanced replication facility detects a conflict with a column group. The advanced replication facility calls the minimum value conflict resolution method and then compares the new value from the originating site with the current value from the destination site for a designated column in the column group. You must designate that column when you select the minimum value conflict resolution method.

*
Additive and Average Value. When you’re dealing with replicated numeric values, this additive method adds a new value to the existing value using the following formula: (current value = current value + (new value - old value)). The average method averages the conflicting values into the existing value using the formula (current value = (current value + new value)/2).

*
Groups priority Value. Using this method, some groups have priority (a higher rank) over other groups. Therefore, the update associated with the highest-ranked group gets the update.

*
Site Priority Value. In this method, all master sites are NOT created equal. Some remote sites will have priority over other sites.

To illustrate how conflict resolution is defined, consider the example below. In this code, we execute dbms_repcat.add_update_resolution to direct Oracle to use the “latest timestamp” method for conflict resolution for updates to the EMP table.

execute dbms_repcat.add_update_resolution( -
sname => 'SCOTT', -
oname => 'EMP', -
column_group => 'EMP_COLGRP', -
sequence_no => 1, -
method => 'LATEST TIMESTAMP', -
parameter_column_name => 'EMPNO');

At this point, you should be starting to appreciate the complexity of conflict resolution in multi-master replication. Now let’s take a quick look at the techniques you can use to define procedural multi-master replication.


Implementing Procedural Multi-master Replication

Although Procedural multi-master replication is an extremely complex process, you can break down the basic steps for defining procedural replication into four phases:

*
Phase I: Pre-configuration. (Set-up Oracle parameters and catalog scripts.)
*
Phase II: Define the repadmin user and database links.
*
Phase III: Create master database and refresh groups.
*
Phase IV: Monitor the replication environment.

Let’s take a close look at each phase in turn.

Phase I: Pre-configuration Steps for Multi-master Replication

Before you’re ready to define a multi-master replication environment, there’s a short checklist you need to deal with up front. For every site that will be participating in the replication, you must check the values of these parameters:

1. Oracle parameters minimum settings

*
shared_pool_size=10m
*
global_names=true
*
job_queue_processes=4

To check those values, run this script on your database:

select
name,
value
from
v_$parameter
where
name in (
'job_queue_processes',
'global_names',
‘shared_pool_size’);

2. You also must be sure that the following dictionary scripts have been run from ORACLE_HOME/rdbms/admin. The catalog.sql was run when you created your instance, and the catproc.sql script is for the procedural option in Oracle.

*
catalog.sql
*
catproc.sql

Phase II: Set-up REPADMIN User and Database Links

The following illustrates some of the main steps you’ll follow in pre-creating the REPADMIN users and the required database links for multi-master replication. You should review these steps with great care.

REM Assign global name to the current DB
alter database rename global_name to PUBS.world;

REM Create public db link to the other master databases
create public database link NEWPUBS using 'newpubs';

REM Create replication administrator / propagator / receiver
create user
repadmin
identified by
repadmin
default tablespace
USER_DATA
temporary tablespace
TEMP
quota unlimited on
USER_DATA;

REM Grant privileges to the propagator, to propagate changes to remote
sites
execute dbms_defer_sys.register_propagator(username=>'REPADMIN');

REM Grant privileges to the receiver to apply deferred transactions
grant execute any procedure to repadmin;

REM Authorize the administrator to administer replication groups
execute dbms_repcat_admin.grant_admin_any_repgroup('REPADMIN');

REM Authorize the administrator to lock and comment tables
grant lock any table to repadmin;
grant comment any table to repadmin;

connect repadmin/repadmin

REM Create private db links for repadmin
create database link newpubs
connect to repadmin identified by repadmin;

REM Schedule job to push transactions to master sites
REM This will replicate every minute
execute dbms_defer_sys.schedule_push( -
destination => 'newpubs', -
interval => 'sysdate+1/24/60', -
next_date => sysdate+1/24/60, -
stop_on_error => FALSE, -
delay_seconds => 0, -
parallelism => 1);

REM Schedule job to delete successfully replicated transactions
execute dbms_defer_sys.schedule_purge( -
next_date => sysdate+1/24, -
interval => 'sysdate+1/24');

REM Test the database link
select global_name from global_name@newpubs;

Phase III: Create the Master Database and Refresh Groups

Once the repadmin user and the links are in place, you’re ready to define the replication. Again, this is an extremely complex process. However, the following script will provide you with the general steps to get the work done.

connect repadmin/repadmin



REM Create replication group for MASTERDEF site
execute dbms_repcat.create_master_repgroup('MYREPGRP');

REM Register objects within the group
execute dbms_repcat.create_master_repobject('SCOTT', -
'EMP', 'TABLE', gname=>'MYREPGRP');

execute dbms_repcat.make_column_group( -
sname => 'SCOTT', -
oname => 'EMP', -
column_group => 'EMP_COLGRP', -
list_of_column_names => 'EMPNO');

execute dbms_repcat.add_update_resolution( -
sname => 'SCOTT', -
oname => 'EMP', -
column_group => 'EMP_COLGRP', -
sequence_no => 1, -
method => 'LATEST TIMESTAMP', -
parameter_column_name => 'EMPNO');

REM Add master destination sites
execute
dbms_repcat.add_master_database( -
'MYREPGRP', -
'TD2.world');

REM Generate replication support for objects within the group
execute
dbms_repcat.generate_replication_support( -
'SCOTT', -
'EMP', -
'table');

Dropping Multi-master Replication

As you’d expect, there will be instances when you may need to turn-off multi-master replication. Some of the obvious cases include database maintenance activities such as upgrades and reorganizations. You can use this sample script to disable multi-master replication.

connect repadmin/repadmin


REM Stop replication
execute dbms_repcat.suspend_master_activity(gname=>'MYREPGRP');

REM Delete replication groups
-- execute dbms_repcat.drop_master_repobject('SCOTT', 'EMP',
'TABLE');
execute dbms_repcat.drop_master_repgroup('MYREPGRP');
execute dbms_repcat.remove_master_databases('MYREPGRP',
'newpubs.world');

REM Remove private database links to other master databases
drop database link newpubs.world;


connect sys

REM Remove the REPADMIN user
execute
dbms_defer_sys.unregister_propagator(username=>'REPADMIN');

execute

dbms_repcat_admin.revoke_admin_any_schema(username=>'REPADMIN')
;

drop user repadmin cascade;

REM Drop public database links to other master databases
drop public database link newpubs.world;

Phase IV: Monitoring Multi-master Replication

The final phase of implementing multi-master replication involves monitoring. A variety of dictionary views provide the key to monitoring complex multi-replication processes. I cannot stress enough the importance of checking these views on every database in the multi-master network.

*
DBA_REPSCHEMA. This view contains details for the replication schema
*
DBA_REPCATLOG. This view provides a log of all replication activities.
*
DBA_JOBS. Use this view to monitor all scheduled job in the database.
*
DBA_REPCAT. This view shows the replication catalog.
*
ALL_REPCONFLICT. This view provides a list of all replication conflicts.
*
ALL_REPRESOLUTION. For systems defined with pre-defined conflict resolution, this view lists the resolution of every conflict.
*
DBA_REPOBJECT. This view gives you a list of al replicated objects.
*
DBA_REPSITES. This view provides is a list of replicated sites.

At this point, you’ll want to closely review the following script, which is the one most commonly used to monitor procedural replication. Of course, you must run this script on each remote database.

connect repadmin/repadmin

set pages 50000

col sname format a20 head "SchemaName"
col masterdef format a10 head "MasterDef?"
col oname format a20 head "ObjectName"
col gname format a20 head "GroupName"
col object format a35 trunc
col dblink format a35 head "DBLink"
col message format a25
col broken format a6 head "Broken?"

prompt Replication schemas/ sites
select
sname,
masterdef,
dblink
from
sys.dba_repschema;

prompt RepCat Log (after a while you should see no entries):
select
request,
status,
message,
errnum
from
sys.dba_repcatlog;

prompt Entries in the job queue
select
job,
last_date,
last_sec,
next_date,
next_sec,
broken,
failures,
what
from
sys.dba_jobs
where
schema_user = 'REPADMIN';

prompt Replication Status:
select
sname,
master,
status
from
sys.dba_repcat;

prompt Returns all conflict resolution methods
select * from all_repconflict;

prompt Returns all resolution methods in use
select * from all_represolution;

prompt Objects registered for replication
select
gname,
type||' '||sname||'.'||oname object,
status
from
sys.dba_repobject;

select * from dba_repsites;

Resources for Defining Multi-master Replication

When it comes to defining multi-master replication for your shop, you don’t have to start from scratch. Oracle offers the following pre-defined PL/SQL packages that can assist you:

*
dbms_repcat package — This complex package provides over 50 stored procedures. Follow this link for a listing of the procedures in dbms_repcat.
*
dbms_reputil package — This package contains several stored procedures. Here is a list of the procedures in dbms_reputil.
*
dbms_defer_sys package — This collection contains 19 replication procedures. Here is a list of the procedures in dbms_defer_sys.

Oracle Application Server 10g

Ver Plugins Instalados
about:plugins

/u01/app/oracle/j2ee2/opmn/conf
Archivo de Configuracion opmn.xml

bash-3.00$ vi opmn.xml

Luego de Modificar el Archivo se debe ejecutar

bash-3.00$ dcmctl updateconfig


SERVICIOS

opmnctl startall

DISCOVERER

http://fenix123.oracle.com:7778/discoverer/portletprovider

OIDDAS

http://fenix122.oracle.com:7777/oiddas/


INFRAESTRUCTURA

Oracle Identity Management

cd $ORACLE_HOME/bin

./oidadmin

usuario: orcladmin


DISCOVER CREATE END USER LAYER

eulapi -connect biuser/biuser@immr -create_eul -default_tablespace USERS -temporary_tablespace TEMP -private

-create_eul
-connect <**********>
-private
-default_tablespace USERS
-temporary_tablespace TEMP
Command completed.
-bash-3.00$ eulapi -connect biuser/biuser@immr -load "Sales History" -user sh_comp -object channels -object countries -object customers -object products -object times -object costs -object sales -log load.log
-load Sales History
-connect <**********>
-log load.log
-object channels
-user sh_comp
-object countries
-object customers
-object products
-object times
-object costs
-object sales
Command completed.

DISCOVERE PLUS

http://fenix123.oracle.com:7778/discoverer/plus


Editar mod_oc4j.conf en /u01/app/oracle/j2ee1/Apache/Apache/conf/

En este Archivo se encuentra la configuracion de todos los servicios de OAS Capa Media

CONFIGURACION DE FORMS

forms.conf

/u01/app/oracle/j2ee1/forms/server

Configuracion Funcione Java

/u01/app/oracle/j2ee1/forms/server

formsweb.cfg

jpi_mimetype=application/x-java-applet;jpi-version=1.4.2_06
jpi_mimetype=application/x-java-applet;jpi-version=1.6.0_01

Actualiza un archivo po linea de comandos se debe ejecutar

dcmctl updateconfig -ct ohs
dcmctl restard -ct ohs
dcmctl start -ct ohs

Kill -1 reiniciar un proceso

cd /u01/app/oracle/j2ee1/opmn/logs ver los Archivos segun el error

ODL (Oracle Diagnostic Loader) Estandar

EL log loader es un log para guardar en la base de datos esta en la siguiente ruta

/u01/app/oracle/j2ee1/dcm/logs/dcmctl_logs

Modificar el Archivo para crear repositorio en base de datos

/u01/app/oracle/immr/diagnostic/config/ archivo logloader.xml

Para borrar los logs luego de crear el repositorio en bd

SQL> @ORACLE_HOME/diagnostics/admin/dmrep_cleanup 7 DAY

bash-3.00$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
bash-3.00$ ./logloader -storePassword -user dmrepuser -pwd dmreppwd

EL archivo logloader.xml debe quedar asi.









Configuracion OC4J

Farm > Application Server: j2ee1.fenix123.oracle.com > OC4J: home >

Java Options

-Xmx8m -Xms8m -Xss64k

AggreSpy Performance

http://127.0.0.1:7201/dmsoc4j/AggreSpy

CAMBIAR PASSWORD OID - ODS

dpasswd to change the ODS password.

Probar Servicios Ldap

ldapbind -h fenix120.oracle.com -p 2103

oidctl

Roles y Privilegios

Ver Privilegios de sysdba

SELECT grantee,granted_role from DBA_ROLE_PRIVS where granted_role='DBA';

select * from v$pwfile_users;

SQL> select * from v$pwfile_users;

USERNAME        SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS        TRUE  TRUE  FALSE FALSE FALSE FALSE     0
SYSDG        FALSE FALSE FALSE FALSE TRUE  FALSE     0
SYSBACKUP        FALSE FALSE FALSE TRUE  FALSE FALSE     0
SYSKM        FALSE FALSE FALSE FALSE FALSE TRUE     0

Solaris 10 Manejo de Zonas

How to Reboot a Zone
You must be the global administrator in the global zone to perform this procedure.

Become superuser, or assume the Primary Administrator role.

To create the role and assign the role to a user, see Using the Solaris Management Tools With RBAC (Task Map) in System Administration Guide: Basic Administration.

List the zones running on the system.

global# zoneadm list -v

You will see a display that is similar to the following:


ID NAME STATUS PATH BRAND IP
0 global running / native shared
1 my-zone running /export/home/my-zone native shared

Use the zoneadm command with the -z reboot option to reboot the zone my-zone.

global# zoneadm -z my-zone reboot

List the zones on the system again to verify that my-zone has been rebooted.

global# zoneadm list -v

You will see a display that is similar to the following:

ID NAME STATUS PATH BRAND IP
0 global running / native shared
2 my-zone running /export/home/my-zone native shared

Date Functions

Date Calculations

Returns A Day A Specified Number Of Days In The Future Skipping Weekends

CREATE OR REPLACE FUNCTION business_date (start_date DATE,
Days2Add NUMBER) RETURN DATE IS
Counter NATURAL := 0;
CurDate DATE := start_date;
DayNum POSITIVE;
SkipCntr NATURAL := 0;
BEGIN
WHILE Counter < Days2Add LOOP
CurDate := CurDate+1;
DayNum := TO_CHAR(CurDate, 'D');

IF DayNum BETWEEN 2 AND 6 THEN
Counter := Counter + 1;
ELSE
SkipCntr := SkipCntr + 1;
END IF;
END LOOP;
RETURN start_date + Counter + SkipCntr;
END business_date;
/


Business Date function, above, enhanced by Larry Benton to handle negative values for the days2add parameter.

CREATE OR REPLACE FUNCTION business_date (start_date DATE,
days2add NUMBER) RETURN DATE IS
Counter NATURAL := 0;
CurDate DATE := start_date;
DayNum POSITIVE;
SkipCntr NATURAL := 0;
Direction INTEGER := 1; -- days after start_date
BusinessDays NUMBER := Days2Add;
BEGIN
IF Days2Add < 0 THEN
Direction := - 1; -- days before start_date
BusinessDays := (-1) * BusinessDays;
END IF;

WHILE Counter < BusinessDays LOOP
CurDate := CurDate + Direction;
DayNum := TO_CHAR( CurDate, 'D');

IF DayNum BETWEEN 2 AND 6 THEN
Counter := Counter + 1;
ELSE
SkipCntr := SkipCntr + 1;
END IF;
END LOOP;

RETURN start_date + (Direction * (Counter + SkipCntr));
END business_date;
/

Returns The First Day Of A Month


CREATE OR REPLACE FUNCTION fday_ofmonth(value_in DATE)
RETURN DATE IS
vMo VARCHAR2(2);
vYr VARCHAR2(4);
BEGIN
vMo := TO_CHAR(value_in, 'MM');
vYr := TO_CHAR(value_in, 'YYYY');
RETURN TO_DATE(vMo || '-01-' || vYr, 'MM-DD-YYYY');
EXCEPTION
WHEN OTHERS THEN
RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY');
END fday_ofmonth;
/

Time Calculations

CREATE OR REPLACE FUNCTION time_diff (
DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS

NDATE_1 NUMBER;
NDATE_2 NUMBER;
NSECOND_1 NUMBER(5,0);
NSECOND_2 NUMBER(5,0);

BEGIN
-- Get Julian date number from first date (DATE_1)
NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));

-- Get Julian date number from second date (DATE_2)
NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));

-- Get seconds since midnight from first date (DATE_1)
NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));

-- Get seconds since midnight from second date (DATE_2)
NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));

RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));
END time_diff;
/

Calculating time from seconds

SELECT DECODE(FLOOR(999999/86400), 0, '',
FLOOR(999999/86400) || ' day(s), ') ||
TO_CHAR(TO_DATE(MOD(999999, 86400),'SSSSS'), 'HH24:MI:SS') AS elapsed
FROM dual;

FUNCTION SQL Examples

FUNCION TO_CHAR

SQL> SELECT DISTINCT(TO_CHAR(HGO_FCH_REGISTRO,'YYYY')) FROM HIS_SEG_OFERTA;

(TO_CHAR(HGO_FCH_REGISTRO,'YYY
---------------------------------------------------------------------------
2009
2019
2006
2008
2007
2005
2022

SELECT * FROM HIS_SEG_OFERTA WHERE TO_CHAR(HGO_FCH_REGISTRO,'YYYY') > 2009
/

Oracle RAC Comands

VER ESTADO SERVICIOS DEL CLUSTER

$ crs_stat -t

Name Type Target State Host
------------------------------------------------------------
ora....01.lsnr application ONLINE ONLINE ct1b...ip01
ora....p01.gsd application ONLINE OFFLINE
ora....p01.ons application ONLINE OFFLINE
ora....p01.vip application ONLINE ONLINE ct1b...ip01
ora....02.lsnr application ONLINE ONLINE ct1b...ip02
ora....p02.gsd application ONLINE ONLINE ct1b...ip02
ora....p02.ons application ONLINE OFFLINE
ora....p02.vip application ONLINE ONLINE ct1b...ip02
ora.sirs.db application ONLINE ONLINE ct1b...ip02
ora....s1.inst application ONLINE ONLINE ct1b...ip01
ora....s2.inst application ONLINE ONLINE ct1b...ip02
ora...._srv.cs application OFFLINE OFFLINE
ora....rs1.srv application OFFLINE OFFLINE
ora....rs2.srv application OFFLINE OFFLINE

SUBIR SERVICIOS DEL NODEAPPS

$ srvctl start nodeapps -n ct1bosunsipip02
$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....01.lsnr application ONLINE ONLINE ct1b...ip01
ora....p01.gsd application ONLINE ONLINE ct1b...ip01
ora....p01.ons application ONLINE ONLINE ct1b...ip01
ora....p01.vip application ONLINE ONLINE ct1b...ip01
ora....02.lsnr application ONLINE ONLINE ct1b...ip02
ora....p02.gsd application ONLINE ONLINE ct1b...ip02
ora....p02.ons application ONLINE ONLINE ct1b...ip02
ora....p02.vip application ONLINE ONLINE ct1b...ip02
ora.sirs.db application ONLINE ONLINE ct1b...ip02
ora....s1.inst application ONLINE ONLINE ct1b...ip01
ora....s2.inst application ONLINE ONLINE ct1b...ip02
ora...._srv.cs application OFFLINE OFFLINE
ora....rs1.srv application OFFLINE OFFLINE
ora....rs2.srv application OFFLINE OFFLINE

SUBIR LISTENER CLUSTER

srvctl start listener -n ct1bosunsipep01

SUBIR TODOS LOS SERVICIOS

$ crs_start -all

bash-3.00$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....01.lsnr application ONLINE ONLINE ct1b...ip01
ora....p01.gsd application ONLINE ONLINE ct1b...ip01
ora....p01.ons application ONLINE ONLINE ct1b...ip01
ora....p01.vip application ONLINE ONLINE ct1b...ip01
ora....02.lsnr application ONLINE ONLINE ct1b...ip02
ora....p02.gsd application ONLINE ONLINE ct1b...ip02
ora....p02.ons application ONLINE ONLINE ct1b...ip02
ora....p02.vip application ONLINE ONLINE ct1b...ip02
ora.sirs.db application ONLINE ONLINE ct1b...ip02
ora....s1.inst application ONLINE ONLINE ct1b...ip01
ora....s2.inst application ONLINE ONLINE ct1b...ip02
ora...._srv.cs application ONLINE ONLINE ct1b...ip02
ora....rs1.srv application ONLINE ONLINE ct1b...ip01
ora....rs2.srv application ONLINE ONLINE ct1b...ip02


# Start and stop cluster
srvctl start database -d db10g
srvctl stop database -d db10g

# Start and stop individual instance
srvctl start instance -d db10g -i db10g1
srvctl stop instance -d db10g -i db10g1

# Get status of whole database or specific instance
srvctl status database -d db10g
srvctl status instance -d db10g -i db10g1

# Get current database config
srvctl config database -d db10g

start_crs.sh
/app/oracle/crs/10.2.0.3/bin/crsctl start crs

stop_crs.sh
/app/oracle/crs/10.2.0.3/bin/crsctl stop crs

status_crs.sh
/app/oracle/crs/10.2.0.3/bin/crs_stat -t


- Dirección pública e interconnect.
$ORA_CRS_HOME/bin/oifcfg getif

- Para las direcciones virtuales
$ srvctl config nodeapps -n nombre_nodo -a

rac1-> srvctl status nodeapps -n rac1
VIP is running on node: rac1
GSD is running on node: rac1
Listener is running on node: rac1
ONS daemon is running on node: rac1

rac1-> srvctl status nodeapps -n rac2
VIP is running on node: rac2
GSD is running on node: rac2
Listener is running on node: rac2
ONS daemon is running on node: rac2


rac1-> srvctl status asm -n rac1
ASM instance +ASM1 is running on node rac1.

rac1-> srvctl status asm -n rac2
ASM instance +ASM2 is running on node rac2.

rac1-> srvctl status database -d devdb
Instance devdb1 is running on node rac1
Instance devdb2 is running on node rac2

rac1-> srvctl status service -d devdb

Check ONS Daemon si esta corriendo

onsctl ping


ORACLE_HOME/bin/onsctl help

usage: ORACLE_HOME/bin/onsctl

start|stop|ping|reconfig|debug
start - Start opmn only.
stop - Stop ons daemon
ping - Test to see if ons daemon is running
debug - Display debug information for the ons
daemon
reconfig - Reload the ons configuration
help - Print a short syntax description
(this).

$ onsctl start
onsctl: ons started


lLSNRCTL> set help
The following operations are available after set
An asterisk (*) denotes a modifier or extended command:

password rawmode
displaymode trc_file
trc_directory trc_level
log_file log_directory
log_status current_listener
inbound_connect_timeout startup_waittime
save_config_on_stop dynamic_registration

LSNRCTL> show inbound_connect_timeout
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 60
The command completed successfully


srvctl status service -d sirspe Ver servicios

Verificar instalacion CRS

/app/oracle/crs/10.2.0.3/bin/ olsnodes -n

$ olsnodes -n
ct1bosunsipep01 0
ct1bosunsipep02 1


CHEQUEAR EL TIPO DE FAILOVER

select instance_name, host_name,
NULL AS failover_type,
NULL AS failover_method,
NULL AS failed_over
FROM v$instance
UNION
SELECT NULL, NULL, failover_type, failover_method, failed_over
FROM v$session
WHERE username = 'SYSTEM';

VER SERVICIO DEL CLUSTER EN CADA UNO DE LOS NODOS

srvctl status database -d sirspe

VER SERVICIOS DEL CLUSTER

crs_stat -t

SUBIR TODOS LOS SERVICIOS DEL CLUSTER

crs_start -all

BAJAR TODOS LOS SERVICIOS DEL CLUSTER

crs_stop -all


srvctl start nodeapps -n
srvctl start nodeapps -n
srvctl start asm -n
srvctl start asm -n
srvctl start database -d
srvctl start service -d -s

crs_stat -t

srvctl stop service -d -s
srvctl stop database -d
srvctl stop asm -n
srvctl stop asm -n
srvctl stop nodeapps -n
srvctl stop nodeapps -n


Shut down the Oracle Cluster Synchronization Services (CSS) daemon as the root user:

# /etc/init.d/init.cssd stop


Shut down all ASM instances on all nodes. To shut down an ASM instance, enter the following command where node is the name of the node where the ASM instance is running:

$ oracle_home/bin/srvctl stop asm -n node

Stop all node applications on all nodes. To stop node applications running on a node, enter the following command where node is the name of the node where the applications are running:

$ oracle_home/bin/srvctl stop nodeapps -n node

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

set define [SQL*Plus]

set define [SQL*Plus]
set define on
set define off
set define x
set define x
set define x specifies the prefix-character for substitution variables. The default is the ampersand (&).
set define +

select * from dba_objects
where object_name like '%+object_name%';

set define on

Turns on substitution variables.
set define on

select '&hello' from dual;
If define is set to on and SQL*Plus finds the current substituion prefix, it asks for a string to be entered. In the following example, I entered: this string was entered
Enter value for hello: this string was entered
old 1: select '&hello' from dual
new 1: select 'this string was entered' from dual

'THISSTRINGWASENTERED'
-----------------------
this string was entered
It might be annoying to see the following lines printed by SQL*Plus:
old 1: select '&hello' from dual
new 1: select 'this string was entered' from dual
This behavior can be turned off by setting verify off.
set define off
Turns off substitution variables.
set define off

select '&hello' from dual;
'&HELL
------
&hello

optimal

The following is an example of how to resize the online log groups:

NOTE: Examples are given for 9i and higher. In prior releases, you needed
to use Server Manager and connect as the internal user.

1. First see the size of the current logs:

> sqlplus /nolog
SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 1048576 INACTIVE
2 1048576 CURRENT
3 1048576 INACTIVE

Logs are 1MB from above, let's size them to 10MB.


2. Retrieve all the log member names for the groups:

SQL> select group#, member from v$logfile;

GROUP# MEMBER
--------------- ----------------------------------------
1 /usr/oracle/dbs/log1PROD.dbf
2 /usr/oracle/dbs/log2PROD.dbf
3 /usr/oracle/dbs/log3PROD.dbf

3. In older versions of the database you needed to shutdown and issue the following
commands in restricted mode. You can still do this, but the database can be online
to perform these changes.

Let's create 3 new log groups and name them groups 4, 5, and 6, each 10MB in
size:

SQL> alter database add logfile group 4
'/usr/oracle/dbs/log4PROD.dbf' size 10M;

SQL> alter database add logfile group 5
'/usr/oracle/dbs/log5PROD.dbf' size 10M;

SQL> alter database add logfile group 6
'/usr/oracle/dbs/log6PROD.dbf' size 10M;


4. Now run a query to view the v$log status:

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

GROUP# STATUS
--------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
4 UNUSED
5 UNUSED
6 UNUSED

From the above we can see log group 2 is current, and this is one of the
smaller groups we must drop. Therefore let's switch out of this group into
one of the newly created log groups.


5. Switch until we are into log group 4, so we can drop log groups 1, 2, and 3:

SQL> alter system switch logfile;
** repeat as necessary until group 4 is CURRENT **


6. Run the query again to verify the current log group is group 4:

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

GROUP# STATUS
--------- ----------------
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 CURRENT
5 UNUSED
6 UNUSED


7. Now drop redo log groups 1, 2, and 3:

SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;

Verify the groups were dropped, and the new groups' sizes are correct.

SVRMGR> select group#, bytes, status from v$log;

GROUP# BYTES STATUS
--------- --------- ----------------
4 10485760 CURRENT
5 10485760 UNUSED
6 10485760 UNUSED

8. At this point, you consider taking a backup of the database.

9. You can now go out to the operating system and delete the files associated
with redo log groups 1, 2, and 3 in step 2 above as they are no longer
needed:

% rm /usr/oracle/dbs/log1PROD.dbf
% rm /usr/oracle/dbs/log2PROD.dbf
% rm /usr/oracle/dbs/log3PROD.dbf

Monitor the alert.log for the times of redo log switches. Due to increased
redo log size, the groups should not switch as frequently under the same
load conditions.