RMAN Drop Database

SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2139256 bytes
Variable Size 396188552 bytes
Database Buffers 1207959552 bytes
Redo Buffers 4325376 bytes
Database mounted.

SQL> alter system enable restricted session;

System altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[afrodita_POWER ]# rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Oct 29 11:21:59 2009

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

connected to target database: COPIA (DBID=1588920959, not open)

RMAN> drop database;

database name is "COPIA" and DBID is 1588920959

Do you really want to drop the database (enter YES or NO)? YES
database dropped

RMAN>

Oracle 11g New Features

if your database doesn’t satisfy the requirements for upgrade to Oracle Database 11g

■ catupgrd.sql This is the script that performs the actual upgrading of the
database to the Oracle Database 11g release and it now supports parallel
upgrades of the database.
■ utlu111s.sql This is the Upgrade Status Utility script which lets you
check the status of the upgrade—that is, whether the upgraded database’s
components have a valid status.
■ catuppst.sql This is the script you run to perform post-upgrade actions. This
is new in Oracle Database 11g Release 1.
■ utlrp.sql This script recompiles and revalidates any remaining application
objects.

file from the $ORACLE_HOME/rdbms/admin
directory to a staging directory such as /u01/app/oracle/upgrade. Log in as the owner
of the Oracle home directory of the older release and run the utlu111.i sql script
(from the /u01/app/oracle/upgrade directory). Spool the results so you can review the
output. Here’s an example showing the output of an execution of the utlu111i.sql
script on my system:

SQL> spool upgrade.log
SQL> @utlu111i.sql
Oracle Database 11.1 Pre-Upgrade Information Tool
01-30-2008 05:33:22
***********************************************************

How Is Oracle Managing My Memory?

You can monitor how Oracle is managing your memory by reviewing the
V$MEMORY_RESIZE_OPS view. This view contains a list of the last 800 SGA resize
requests handled by Oracle. Here is an example:


select parameter, initial_size, target_size, start_time
from v$memory_resize_ops
where initial_size > 0 and final_size > 0
order by parameter, start_time;

DDL WAIT Option Now DefaultIn Oracle Database 10g, by default DDL

commands would not wait if the object
was locked. Instead an error would be generated and the attempted change would
fail with an ORA-00054, indicating the resource was locked. Now, in Oracle
Database 11g, Oracle in many cases will not only not return an error, but will
execute the change without a wait being required. For example, you can now do
the following in Oracle Database 11g:

Session 1:
SQL>insert into test values (1);
1 row created.
Session 2:
SQL>alter table test add (id2 number);
Table altered.

approach but the only feasible one. In Oracle 11g, you do not need to do
much. In the session you want to issue a DDL statement, issue this SQL first:

alter session set ddl_lock_timeout = 10;

Duplicate
RMAN> duplicate database to dupdb
2> from active database
3> db_file_name_convert '/u01/app/oracle','/u05/app/oracle'
4> spfile
5> parameter_value_convert '/u01/app/oracle','/u05/app/oracle'
6> set log_file_name_convert '/u01/app/oracle',
'/u05/app/oracle'
7> set sga_max_size '3000m'
8> set sga_target '2000m';

Restoring an Archival Backup

You can issue the duplicate database command to restore an archival backup.
Here are the steps to restore and recover the database using an archival backup:

1. Create an auxiliary instance after creating the usual password file and the
parameter files. Connect to the auxiliary instance and start it.

2. Connect to the recovery catalog, the target, and the auxiliary instances, as
shown here:

RMAN> connect target sys/@prod1
RMAN> connect catalog rman/rman@catdb
RMAN> connect auxiliary /

3. Issue the list restore point all command to find out the name of
the restore points that are available.

RMAN> list restore point all;
SCN RSP Time Type Time Name
------- ------------- ---------- ------------
3074299 30-DEC-07 FIRSTQUART07

RMAN>
4. Issue the duplicate database command, making sure you specify the
correct restore point name to restore the database to the point in time the
restore point stands for.

RMAN> duplicate database
2> to newdb
3> until restore point firstquart07
4> db_file_name_convert='/u01/prod1/dbfiles/',
5>'/u01/newdb/dbfiles'
6> pfile = '/u01/newdb/admin/init.ora';

Using the Recover…Block Command

RMAN> recover datafile 2 block 24
datafile 4 block 10;

You can specify the exact backup from which you want RMAN to recover the
corrupt data blocks by specifying the backup tag with the recover . . . block
command, as shown here:

RMAN> recover datafile 2 block 24
datafile 4 block 10

The following example demonstrates how to create a flashback data archive in
the FLASH_TBS1 tablespace:

SQL> create flashback data archive flash1
tablespace flash_tbs1
retention 4 year;
Flashback Data Archive created.

SQL>
The clause retention 4 year specifies that the database must retain the
data in the new flashback data archive flash1 for four years before purging it. The
absence of the quota clause means the flash1 archive can occupy the entire
tablespace FLASH_TBS1. If you want to limit the archive to only a part of the
tablespace, specify the quota clause, as shown here:

SQL> create flashback data archive flash2
tablespace flash_tbs1
quota 2000m
retention 4 year;
Flashback Data Archive created.

SQL> drop flashback archive flash1;

SQL> alter flashback archive flash1
set default # makes flash1 the default archive

SQL> alter flashback archive flash1
add tablespace
flash_tbs1 # adds space to the flashback archive

SQL> alter flashback archive flash1
modify tablespace
flash_tbs1 quota 10G; # changes the quota for the archive

SQL> alter flashback archive flash1
modify retention
2 year; # changes the archive retention time

SQL> alter flashback tablespace flash1
add tablespace flash_tbs2; #adds a tablespace to an archive

SQL> alter flashback tablespace flash1
remove tablespace
flash_tbs2; #removes a tablespace from an archive

SQL> alter flashback archive flash1
purge all; # purges all archived data

SQL> alter flashback archive flash1
purge before


SQL> alter table employees
flashback archive;

SQL> alter table employees
flashback archive flash2;

SQL> alter table employees
no flashback archive;

Monitoring Flashback Data Archives

SQL> select flashback_archive_name,retention_in_days
from dba_flashback_archive;
FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS
---------------------- -----------------
FLASH1 365

Accessing Older Data

SQL> select transaction_number, doctor_name, count
from patient_info as of
timestamp to_timestamp ('2007-01-01 00:00:00',
'YYYY-MM-DD HH23:MI:SS');

Case Sensitive
DBA, if you want to avoid one of those upgrade-broke-my-system moments?

alter system set sec_case_sensitive_logon = false;

Default Password Use

DBA_USERS_WITH_DEFPWD, that you can query to
determine whether a given user account is using one of these default passwords

SQL> SELECT * FROM DBA_USERS_WITH_DEFPWD;
USERNAME
------------------------------
DIP
MDSYS
WK_TEST
CTXSYS
OLAPSYS
OUTLN
EXFSYS
SCOTT

Preparing the Database for Tablespace Encryption

Configuring the Compatible Parameter Correctly

Here is an example of changing the compatible parameter (as you might do after
a database upgrade from Oracle Database 10g to Oracle Database 11g):

Alter system set compatible='11.1.0.0.0' scope=spfile;

Configure the Sqlnet.ora File (Optional) Oracle will, by default, create the wallet
in the directory $ORACLE_BASE/admin/$ORACLE_SID/wallet (you will need to
create this directory if it does not already exist). If you wish to use a location other than the default location, then configure the encryption_wallet_location parameter in the sqlnet.ora file of our database. Here is an example of such an

entry:

Encryption_wallet_location=
(source=(method=file)
(method_data=(directory=/mywallet)))

Open the Wallet and Create the Master Encryption Key

Alter system set encryption key authenticated by "robert";
The wallet is closed each time the database is shut down. You will need to
reopen the wallet each time you cycle the database, and the database will not open
until you open the wallet (which implies you have to nomount or mount the
database, open the wallet, and then open the database). The command is slightly
different to open an existing wallet:

Alter system set encryption wallet open authenticated by "robert";
While it might be an unusual operation, you can also close the wallet with the
alter system command as seen here:

Alter system set encryption wallet close;

Creating Encrypted Tablespaces

Create tablespace my_secure_tbs
datafile '/oracle01/oradata/orcl/my_secure_tbs_01.dbf' size 100m
encryption using '3DES168' default storage (encrypt);

You can also encrypt a tablespace using the default encryption algorithm by just
using the encryption keyword as seen here:

Create tablespace my_second_secure_tbs
datafile '/oracle01/oradata/orcl/my_second_secure_tbs_01.dbf' size 100m
encryption default storage (encrypt);

SECUREFILE LOBS must be created in tablespaces using Automatic Segment Space
Management (ASSM), and you must have your wallet open in order to use SECUREFILE.
Here is an example of the creation of a table with a SECUREFILE LOB:

Example:

create table notes (note_doc clob encrypt using 'AES128')
lob(note_doc) store as securefile
(cache nologging);

example of using the alter table command to compress the contents of a table. Also all future contents will be compressed:

Alter table emp move compress;


select table_name, partition_name, compression
from dba_tab_partitions
where table_name='COMPRESS_DEMO'
order by 1,2;

TABLE_NAME PARTITION_NAME COMPRESS
------------------------------ ------------------------------ --------
COMPRESS_DEMO CLOSE_BUT_NOT_YET DISABLED
COMPRESS_DEMO LONG_AGO ENABLED
COMPRESS_DEMO NOT_SO_LONG_AGO DISABLED
COMPRESS_DEMO NOW_OR_FUTURE DISABLED

New lock table Parameter

Lock table my_tab in exclusive mode wait 60;
Lock table my_tab in share mode nowait;

You can make an index invisible when you create it with the create index
command by using the invisible keyword as seen in this example:

Create index ix_test on test(id) invisible;
Alter index ix_test visible;
alter index ix_test invisible;


Read-Only Tables
The alter table command can now be used to make a table read-only. This allows
the DBA to make a table read-only across the database, including the owner of the
table. The following examples demonstrate the use of the alter table command along
with the read only keywords to make a table read-only, and then the use of the read
write keywords to make the table read-write:

Alter table my_table read only;

SQL> delete from my_table;
delete from my_table
*
ERROR at line 1:

ORA-12081: update operation not allowed on table "SCOTT"."MY_TABLE"
alter table my_table read write;

Using the Result Cache
You can tell if you are using the result cache by looking at the execution plan of
your SQL statement. Here is an example of a SQL statement that uses the result_
cache hint, along with its associated execution plan (the output is cleaned up for the
benefit of the size of this page):

select /*+ result_cache */ sum(sal) sum from emp;

You can generate a report on the result cache using the Oracle PL/SQL
procedure dbms_result_cache.memory_report as seen in this example:

SQL> set serveroutput on
SQL> dbms_result_cache.memory_report
SQL> exec dbms_result_cache.memory_report
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 864K bytes (864 blocks)
Maximum Result Size = 43K bytes (43 blocks)
[Memory]
Total Memory = 103528 bytes [0.077% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.004% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.073% of the Shared Pool]
....... Overhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)
PL/SQL procedure successfully completed.

Create Triggers as Enabled or DisabledOracle Database 11g allows you to create a trigger either as enabled (the default) or
disabled using the new enabled or disable clause. If you want to create a trigger as
disabled, you would use the disable clause as seen in this example:
Create or replace trigger trigger_two
before insert on test
disable
begin
null;
end;
/

Performance Tuning

For example, I could run the following query to find all cumulative waits of more than one second that occurred on foreground/background processes:


select event, total_waits_fg twg, total_timeouts_fg ttf,
time_waited_fg twf, average_wait_fg awf,
time_waited_micro_fg twmf
from v$system_event
where time_waited_micro_fg > 1000000
and wait_class !='Idle';

RMAN Recovery ORACLE_SID Diferente Unix

cp initCOPIA.ora initCOPIA.ora

Modificar directorios

Instancia 1
RMAN> backup database plus archivelog;

Instancia 1
SQL> alter database backup controlfile to trace;

REVISAR EL ARCHIVO GENERADO EN EL DIRECTORIO udump
Y MODIFICARLO

Instancia 2 OJO CAMBIAR REUSE POR SET

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

Database altered.

[afrodita_COPIA ]# rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Oct 15 14:30:54 2009

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1610612736 bytes

Fixed Size 2139256 bytes
Variable Size 396188552 bytes
Database Buffers 1207959552 bytes
Redo Buffers 4325376 bytes

RMAN> restore controlfile from '/u01/app/oracle/COPIA/flash_recovery_area/COPIA/autobackup/2009_10_15/o1_mf_s_700324017_5fgxw1yz_.bkp';

Starting restore at 15-OCT-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=107 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oracle/oradata/COPIA/control01.ctl
output filename=/u01/app/oracle/oradata/COPIA/control02.ctl
output filename=/u01/app/oracle/oradata/COPIA/control03.ctl
Finished restore at 15-OCT-09

RMAN>


RMAN> catalog backuppiece '/u01/app/oracle/POWER/flash_recovery_area/POWER/backupset/2009_10_15/o1_mf_nnndf_TAG20091015T142630_5fgxv6ld_.bkp';

cataloged backuppiece
backup piece handle=/u01/app/oracle/POWER/flash_recovery_area/POWER/backupset/2009_10_15/o1_mf_nnndf_TAG20091015T142630_5fgxv6ld_.bkp recid=1 stamp=700327997

RMAN> list backup summary;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B F A DISK 15-OCT-09 1 1 NO TAG20091015T142630

RMAN> restore database;

Starting restore at 15-OCT-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=106 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/COPIA/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/COPIA/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/COPIA/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/COPIA/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/COPIA/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/POWER/flash_recovery_area/POWER/backupset/2009_10_15/o1_mf_nnndf_TAG20091015T142630_5fgxv6ld_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/POWER/flash_recovery_area/POWER/backupset/2009_10_15/o1_mf_nnndf_TAG20091015T142630_5fgxv6ld_.bkp tag=TAG20091015T142630
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 15-OCT-09

RMAN> recover database;

Starting recover at 15-OCT-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=105 devtype=DISK

starting media recovery

RMAN> alter database open resetlogs;

database opened

Si se requiere entonces

RESTORE DATABASE UNTIL SEQUENCE 13243;


OTRO METODO


STARTUP NOMOUNT
RUN
{
RESTORE CONTROLFILE FROM 'save_controlfile_location';
ALTER DATABASE MOUNT;
RESTORE DATABASE FROM TAG before_upgrade
RECOVER DATABASE NOREDO;
ALTER DATABASE OPEN RESETLOGS;
}

RMAN Duplicate Windows

sc delete OracleServiceAUXPOWER

SQL> STARTUP mount PFILE=D:\oracle\product\10.2.0\admin\POWER\pfile\INITaux.ora;

SQL> create pfile = 'D:\oracle\product\10.2.0\admin\POWER\pfile\INITaux.ora' fro
m spfile;

Crear password

D:\oracle\product\10.2.0\admin\POWER\pfile>orapwd file=d:\oracle\product\10.2.0\
admin\POWER\pfile\pwdauxpower.ora password=oracle entries=5

Crear servicio

D:\oracle\product\10.2.0\admin\POWER\pfile>oradim -new -sid auxpower -startmode m -pfile D:\oracle\product\10.2.0\admin\POWER\pfile\INITaux.ora
Instance created.


SET ORACLE_SID=AUXPOWER

sqlplus "/as sysdba"

SQL>startup mount;

SQL>startup mount force;

SQL>select * from v$instance;

RMAN> rman target /@power auxiliary /

RMAN> duplicate target database to auxpower;


SQL> alter database open resetlogs;


File created.

RMAN
CONNECT AUXILIARY SYS/ORACLE@AUX.WORLD
CONNECT TARGET SYS/ORACLE@ORCL.WORLD
STARTUP CLONE NOMOUNT FORCE;
DUPLICATE TARGET DATABASE TO AUX;

*.control_files='D:\oracle\product\10.2.0\oradata\AUXPOWER\control01.ctl','D:\oracle\product\10.2.0\oradata\AUXPOWER\control02.ctl','D:\oracle\product\10.2.0\oradata\AUXPOWER\control03.ctl'


DUPLICATE TARGET DATABASE to auxpower UNTIL TIME “TO_DATE(’09/18/2008',’MM/DD/YYYY’)”;

DUPLICATE TARGET DATABASE TO auxpower NOFILENAMECHECK UNTIL TIME 'SYSDATE-7';

DUPLICATE

Crear la nueva instanacia donde se va a levantar la base de datos:
para eso vamos donde estan los archivos de inicializacion de las bases de datos de oracle.

cd $HOME/dbs
cp initorac.ora initoracrest.init

y modificamos el archivo “initoracrest.init”.

Cambiamos los paramentros donde diga “orac” por “oracrest” y cambiamos los path de:
1 archive
2 control
3 dump

Todos a la nueva ruta como se muestra:

log_archive_dest=/storage/restore/oracle/archive/
control_files=/storage/restore/oracle/control/cntrlorac.dbf,/storage/restore/oracle/control/contr2orac.dbf
rollback_segments = ROLL1,ROLL2,ROLL3,ROLL4,ROLL5,ROLL6,ROLL7,ROLL8,ROLL9,ROLL10,ROLL11,ROLL12,ROLL13,ROLL14,ROLL15,ROLL16,ROLL17,ROLL18,ROLL19,ROLL20,ROLL21
,ROLL22,ROLL23,ROLL24,ROLL25,ROLL26,ROLL27

core_dump_dest = /storage/restore/oracle/cdump

crear los anteriores directorios

ahora fijamos el ORACLE_SID=oracrest
export ORACLE_SID=oracrest

ahora nos conectamos como sysdba.

sqlplus '/as sysdba'

SQL>startup nomount;

y verificamos que tengamos la nueva instancia:

#ps -fea | grep pmon

Debe estar la “oracrest”

Ahora vamos a construir el script de recuperacion del control file:

/opt/oracle/rman/oracrest/
mdkir oracrest
cd oracrest
mkdir -p restore/control
cd restore/control
vi control.sh
y adicionamos

RECUPERAR ARCHIVO DE CONTROL

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

run {
ALLOCATE CHANNEL ch00
TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=heo_01_v4904,NB_ORA_POLICY=BD_Online_orac_heo_01_v4904,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Application-Backup)';
restore controlfile;

RELEASE CHANNEL ch00;
}


Verificar que el restore fue exitoso.

oracle@heo_01_v4904-V4im:/storage/restore/oracle/control#ls
cntrlorac.dbf contr2orac.dbf

Ahora hacer el scrips de recuperar los datos:

oracle@heo_01_v4904-V4im:/rman/oracrest/restore/database#cat database.sh
connect catalog rman/rman@cat9i;
set dbid=887613172;
connect target;

run {
ALLOCATE CHANNEL ch00
TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=heo_01_v4904,NB_ORA_POLICY=BD_Online_orac_heo_01_v4904,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Application-Backup)';
ALLOCATE CHANNEL ch01
TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=heo_01_v4904,NB_ORA_POLICY=BD_Online_orac_heo_01_v4904,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Application-Backup)';
ALLOCATE CHANNEL ch02
TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=heo_01_v4904,NB_ORA_POLICY=BD_Online_orac_heo_01_v4904,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Application-Backup)';
ALLOCATE CHANNEL ch03
TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=heo_01_v4904,NB_ORA_POLICY=BD_Online_orac_heo_01_v4904,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Application-Backup)';

set newname for datafile 1 to '/storage/restore/oracle/datos/2systemorac1.dbf';
set newname for datafile 2 to '/storage/restore/oracle/datos/2tsr1.dat';
set newname for datafile 3 to '/storage/restore/oracle/datos/2tsr2.dat';
set newname for datafile 4 to '/storage/restore/oracle/datos/2tempgeneral.dbf';
set newname for datafile 5 to '/storage/restore/oracle/datos/2uhi_real_tmp.dbf';
set newname for datafile 6 to '/storage/restore/oracle/datos/2uhi_tab_tmp.dbf';
set newname for datafile 7 to '/storage/restore/oracle/datos/2HIS_tb_pr.dbf';
set newname for datafile 8 to '/storage/restore/oracle/datos/2HIS_i_pr.dbf';
set newname for datafile 9 to '/storage/restore/oracle/datos/2HIS_tb_bkp.dbf';
set newname for datafile 10 to '/storage/restore/oracle/datos/2HIS_i_bkp.dbf';
set newname for datafile 11 to '/storage/restore/oracle/datos/2conmut_real_tmp.dbf';
set newname for datafile 12 to '/storage/restore/oracle/datos/2conmut_tmp.dbf';
set newname for datafile 13 to '/storage/restore/oracle/datos/2_NEAX61E_tb_pr.dbf';
set newname for datafile 14 to '/storage/restore/oracle/datos/2_NEAX61E_i_pr.dbf';
set newname for datafile 15 to '/storage/restore/oracle/datos/2_NEAX61E_tb_bkp.dbf';
set newname for datafile 16 to '/storage/restore/oracle/datos/2_NEAX61E_i_bkp.dbf';
set newname for datafile 17 to '/storage/restore/oracle/datos/2_AXE_tb_pr.dbf';
set newname for datafile 18 to '/storage/restore/oracle/datos/2_AXE_i_pr.dbf';
set newname for datafile 19 to '/storage/restore/oracle/datos/2_AXE_tb_bkp.dbf';
set newname for datafile 20 to '/storage/restore/oracle/datos/2_AXE_i_bkp.dbf';
set newname for datafile 21 to '/storage/restore/oracle/datos/2BDCentral_idx.dbf';
set newname for datafile 22 to '/storage/restore/oracle/datos/2BDCentral_pr.dbf';
set newname for datafile 23 to '/storage/restore/oracle/datos/2BDCentral_tmp.dbf';
set newname for datafile 24 to '/storage/restore/oracle/datos/2dbgenap001.dbf';
set newname for datafile 25 to '/storage/restore/oracle/datos/2dbgenap002.dbf';
set newname for datafile 26 to '/storage/restore/oracle/datos/2dbgenap003.dbf';
set newname for datafile 27 to '/storage/restore/oracle/datos/2_MGPP_tb_pr.dbf';
set newname for datafile 28 to '/storage/restore/oracle/datos/2_MGPP_i_pr.dbf';
set newname for datafile 29 to '/storage/restore/oracle/datos/2_MGPP_tb_bkp.dbf';
set newname for datafile 30 to '/storage/restore/oracle/datos/2_MGPP_i_bkp.dbf';
set newname for datafile 31 to '/storage/restore/oracle/datos/2_MGPP_tb_pr1.dbf';
set newname for datafile 32 to '/storage/restore/oracle/datos/2_NEAX61E_tb_pr1.dbf';
set newname for datafile 33 to '/storage/restore/oracle/datos/2_AXE_tb_pr1.dbf';
restore database;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
}

rman cmdfile=database.sh

verificar que el restore fue exitoso.

Nos conectamos a la base de datos para iniciar el proceso de subirla.

Como se esta restaurando sobre la misma maquina se requiere recrear el control file:

Recrear Archivo de Control.

SQL>alter database mount;
SQL>alter database backup controlfile to trace;
SQL>show parameters dump;
SQL>exit

Vamos a la ruta mostrada para los udump.

cd /opt/oracle/product/9.2.0.8/rdbms/log

ls -ltr

se edita y se cambian las rutas, tambien se cambian

cp orac_ora_7152.trc control

oracle@heo_01_v4904-V4im:/product/9.2.0.8/rdbms/log#cat orac_ora_7152.trc
Dump file /opt/oracle/product/9.2.0.8/rdbms/log/orac_ora_7152.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /opt/oracle/product/9.2.0.8
System name: SunOS
Node name: heo_01_v4904
Release: 5.10
Version: Generic_125100-07
Machine: sun4u
Instance name: orac
Redo thread mounted by this instance: 1
Oracle process number: 11
Unix process pid: 7152, image: oracle@heo_01_v4904 (TNS V1-V3)

Archivo Control File Original

*** SESSION ID:(10.3) 2007-11-10 12:13:35.698
*** 2007-11-10 12:13:35.698
# The following are current System-scope REDO Log Archival related
# parameters and can be included in the database initialization file.
#
# LOG_ARCHIVE_DEST=''
# LOG_ARCHIVE_DUPLEX_DEST=''
#
# LOG_ARCHIVE_FORMAT=orac_%s_%t.ARC
# REMOTE_ARCHIVE_ENABLE=TRUE
# LOG_ARCHIVE_START=TRUE
# LOG_ARCHIVE_MAX_PROCESSES=2
# STANDBY_FILE_MANAGEMENT=MANUAL
# STANDBY_ARCHIVE_DEST=?/dbs/arch
# FAL_CLIENT=''
# FAL_SERVER=''
#
# LOG_ARCHIVE_DEST_1='LOCATION=/users/oracle/archive/'
# LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
# LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM SYNC'
# LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
# LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
# LOG_ARCHIVE_DEST_STATE_1=ENABLE
#
# Below are two sets of SQL statements, each of which creates a new
# control file and uses it to open the database. The first set opens
# the database with the NORESETLOGS option and should be used only if
# the current versions of all online logs are available. The second
# set opens the database with the RESETLOGS option and should be used
# if online logs are unavailable.
# The appropriate set of statements can be copied from the trace into
# a script file, edited as necessary, and executed when there is a
# need to re-create the control file.
#
# Set #1. NORESETLOGS case
#
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORAC" NORESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 120
MAXINSTANCES 1
MAXLOGHISTORY 680
LOGFILE
GROUP 1 (
'/OraDatos/REDO/redoorac1.log',
'/OraIndices/REDO/redoorac3.log'
) SIZE 10M,
GROUP 2 (
'/OraDatos/REDO/redoorac2.log',
'/OraIndices/REDO/redoorac4.log'
) SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'/OraDatos/SYSTEM/systemorac1.dbf',
'/OraIndices/ROLLBACK/tsr1.dat',
'/OraDatos/ROLLBACK/tsr2.dat',
'/OraDatos/TABLESPACES/tempgeneral.dbf',
'/OraDatos/TABLESPACES/uhi_real_tmp.dbf',
'/OraDatos/TABLESPACES/uhi_tab_tmp.dbf',
'/OraDatos/TABLESPACES/HIS_tb_pr.dbf',
'/OraIndices/TABLESPACES/HIS_i_pr.dbf',
'/OraDatos/TABLESPACES/HIS_tb_bkp.dbf',
'/OraIndices/TABLESPACES/HIS_i_bkp.dbf',
'/OraDatos/TABLESPACES/conmut_real_tmp.dbf',
'/OraDatos/TABLESPACES/conmut_tmp.dbf',
'/OraDatos/TABLESPACES/_NEAX61E_tb_pr.dbf',
'/OraIndices/TABLESPACES/_NEAX61E_i_pr.dbf',
'/OraDatos/TABLESPACES/_NEAX61E_tb_bkp.dbf',
'/OraIndices/TABLESPACES/_NEAX61E_i_bkp.dbf',
'/OraDatos/TABLESPACES/_AXE_tb_pr.dbf',
'/OraIndices/TABLESPACES/_AXE_i_pr.dbf',
'/OraDatos/TABLESPACES/_AXE_tb_bkp.dbf',
'/OraIndices/TABLESPACES/_AXE_i_bkp.dbf',
'/opt/oracle/product/9.2.0.8/dbs/BDCentral_idx.dbf',
'/opt/oracle/product/9.2.0.8/dbs/BDCentral_pr.dbf',
'/opt/oracle/product/9.2.0.8/dbs/BDCentral_tmp.dbf',
'/opt/oracle/product/9.2.0.8/dbs/dbgenap001.dbf',
'/opt/oracle/product/9.2.0.8/dbs/dbgenap002.dbf',
'/opt/oracle/product/9.2.0.8/dbs/dbgenap003.dbf',
'/OraDatos/TABLESPACES/_MGPP_tb_pr.dbf',
'/OraIndices/TABLESPACES/_MGPP_i_pr.dbf',
'/OraDatos/TABLESPACES/_MGPP_tb_bkp.dbf',
'/OraIndices/TABLESPACES/_MGPP_i_bkp.dbf',
'/OraDatos/TABLESPACES/_MGPP_tb_pr1.dbf',
'/OraDatos/TABLESPACES/_NEAX61E_tb_pr1.dbf',
'/OraDatos/TABLESPACES/_AXE_tb_pr1.dbf'
CHARACTER SET US7ASCII
;


# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;
# No tempfile entries found to add.
#
# Set #2. RESETLOGS case
#
# The following commands will create a new control file and use it
# to open the database.
# The contents of online logs will be lost and all backups will
# be invalidated. Use this only if online logs are damaged.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORAC" RESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 120
MAXINSTANCES 1
MAXLOGHISTORY 680
LOGFILE
GROUP 1 (
'/OraDatos/REDO/redoorac1.log',
'/OraIndices/REDO/redoorac3.log'
) SIZE 10M,
GROUP 2 (
'/OraDatos/REDO/redoorac2.log',
'/OraIndices/REDO/redoorac4.log'
) SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'/OraDatos/SYSTEM/systemorac1.dbf',
'/OraIndices/ROLLBACK/tsr1.dat',
'/OraDatos/ROLLBACK/tsr2.dat',
'/OraDatos/TABLESPACES/tempgeneral.dbf',
'/OraDatos/TABLESPACES/uhi_real_tmp.dbf',
'/OraDatos/TABLESPACES/uhi_tab_tmp.dbf',
'/OraDatos/TABLESPACES/HIS_tb_pr.dbf',
'/OraIndices/TABLESPACES/HIS_i_pr.dbf',
'/OraDatos/TABLESPACES/HIS_tb_bkp.dbf',
'/OraIndices/TABLESPACES/HIS_i_bkp.dbf',
'/OraDatos/TABLESPACES/conmut_real_tmp.dbf',
'/OraDatos/TABLESPACES/conmut_tmp.dbf',
'/OraDatos/TABLESPACES/_NEAX61E_tb_pr.dbf',
'/OraIndices/TABLESPACES/_NEAX61E_i_pr.dbf',
'/OraDatos/TABLESPACES/_NEAX61E_tb_bkp.dbf',
'/OraIndices/TABLESPACES/_NEAX61E_i_bkp.dbf',
'/OraDatos/TABLESPACES/_AXE_tb_pr.dbf',
'/OraIndices/TABLESPACES/_AXE_i_pr.dbf',
'/OraDatos/TABLESPACES/_AXE_tb_bkp.dbf',
'/OraIndices/TABLESPACES/_AXE_i_bkp.dbf',
'/opt/oracle/product/9.2.0.8/dbs/BDCentral_idx.dbf',
'/opt/oracle/product/9.2.0.8/dbs/BDCentral_pr.dbf',
'/opt/oracle/product/9.2.0.8/dbs/BDCentral_tmp.dbf',
'/opt/oracle/product/9.2.0.8/dbs/dbgenap001.dbf',
'/opt/oracle/product/9.2.0.8/dbs/dbgenap002.dbf',
'/opt/oracle/product/9.2.0.8/dbs/dbgenap003.dbf',
'/OraDatos/TABLESPACES/_MGPP_tb_pr.dbf',
'/OraIndices/TABLESPACES/_MGPP_i_pr.dbf',
'/OraDatos/TABLESPACES/_MGPP_tb_bkp.dbf',
'/OraIndices/TABLESPACES/_MGPP_i_bkp.dbf',
'/OraDatos/TABLESPACES/_MGPP_tb_pr1.dbf',
'/OraDatos/TABLESPACES/_NEAX61E_tb_pr1.dbf',
'/OraDatos/TABLESPACES/_AXE_tb_pr1.dbf'
CHARACTER SET US7ASCII
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
# No tempfile entries found to add.
#

Se edita y se peluquea el archivo y se deja como en los Nuevos Path de Almacenamiento

bash-3.00# more control.sh


STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "oracrest" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 120
MAXINSTANCES 1
MAXLOGHISTORY 680
LOGFILE
GROUP 1 (
'/storage/restore/oracle/redoorac1.log',
'/storage/restore/oracle/redoorac3.log'
) SIZE 10M,
GROUP 2 (
'/storage/restore/oracle/redoorac2.log',
'/storage/restore/oracle/redoorac4.log'
) SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'/storage/restore/oracle/datos/2systemorac1.dbf',
'/storage/restore/oracle/datos/2tsr1.dat',
'/storage/restore/oracle/datos/2tsr2.dat',
'/storage/restore/oracle/datos/2tempgeneral.dbf',
'/storage/restore/oracle/datos/2uhi_real_tmp.dbf',
'/storage/restore/oracle/datos/2uhi_tab_tmp.dbf',
'/storage/restore/oracle/datos/2HIS_tb_pr.dbf',
'/storage/restore/oracle/datos/2HIS_i_pr.dbf',
'/storage/restore/oracle/datos/2HIS_tb_bkp.dbf',
'/storage/restore/oracle/datos/2HIS_i_bkp.dbf',
'/storage/restore/oracle/datos/2conmut_real_tmp.dbf',
'/storage/restore/oracle/datos/2conmut_tmp.dbf',
'/storage/restore/oracle/datos/2_NEAX61E_tb_pr.dbf',
'/storage/restore/oracle/datos/2_NEAX61E_i_pr.dbf',
'/storage/restore/oracle/datos/2_NEAX61E_tb_bkp.dbf',
'/storage/restore/oracle/datos/2_NEAX61E_i_bkp.dbf',
'/storage/restore/oracle/datos/2_AXE_tb_pr.dbf',
'/storage/restore/oracle/datos/2_AXE_i_pr.dbf',
'/storage/restore/oracle/datos/2_AXE_tb_bkp.dbf',
'/storage/restore/oracle/datos/2_AXE_i_bkp.dbf',
'/storage/restore/oracle/datos/2BDCentral_idx.dbf',
'/storage/restore/oracle/datos/2BDCentral_pr.dbf',
'/storage/restore/oracle/datos/2BDCentral_tmp.dbf',
'/storage/restore/oracle/datos/2dbgenap001.dbf',
'/storage/restore/oracle/datos/2dbgenap002.dbf',
'/storage/restore/oracle/datos/2dbgenap003.dbf',
'/storage/restore/oracle/datos/2_MGPP_tb_pr.dbf',
'/storage/restore/oracle/datos/2_MGPP_i_pr.dbf',
'/storage/restore/oracle/datos/2_MGPP_tb_bkp.dbf',
'/storage/restore/oracle/datos/2_MGPP_i_bkp.dbf',
'/storage/restore/oracle/datos/2_MGPP_tb_pr1.dbf',
'/storage/restore/oracle/datos/2_NEAX61E_tb_pr1.dbf',
'/storage/restore/oracle/datos/2_AXE_tb_pr1.dbf'
CHARACTER SET US7ASCII ;

SQL>@control.sh

SQL>select open_mode from v$database;
SQL>alter database open resetlogs;
# Recuperar la base de datos, mediante los archives.
SQL>recover database using backup control file until cancel;

En este paso hay que recuperar con otro shell los archives que esta pidiendo en este punto.

oracle@heo_01_v4904-V4im:/rman/oracrest/restore/archives#pwd
/opt/oracle/rman/oracrest/restore/archives
oracle@heo_01_v4904-V4im:/rman/oracrest/restore/archives#ls
archives.sh
oracle@heo_01_v4904-V4im:/rman/oracrest/restore/archives#

oracle@heo_01_v4904-V4im:/rman/oracrest/restore/archives#more archives.sh
connect catalog rman/rman@cat9i;
set dbid=887613172;
connect target;

run {
ALLOCATE CHANNEL ch00
TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=heo_01_v4904,NB_ORA_POLICY=BD_Online_orac_heo_01_v4904,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Application-Bac
kup)';

restore archivelog from logseq=2917 until logseq=2925;

RELEASE CHANNEL ch00;
}

Esperar hasta que aplique todos los archives.

Se da un enter en la pantalla anterior(primera pantalla), se le da enter hasta que llegue al ultimo, y en ese punto se le da cancel.

SQL>alter database open resetlogs;
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;

Unix Comandos Examples SED

Ejemplo SED cuando se tiene formato fecha

sed s/'24\/Jun'/'23\/Sep'/g hola.txt > prueba

sed s/’#PermitRoot’/’PermitRoot’/g sshd_config > sshd_test

Oracle DataGuard

INIT POWER

POWER.__db_cache_size=1207959552
POWER.__java_pool_size=16777216
POWER.__large_pool_size=16777216
POWER.__shared_pool_size=352321536
POWER.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/POWER/adump'
*.background_dump_dest='/u01/app/oracle/admin/POWER/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/POWER/control01.ctl','/u01/app/oracle/oradata/POWER/control02.ctl','/u01/app/oracle/oradata/POWER/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/POWER/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='POWER'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='/u01/app/oracle/POWER/flash_recovery_area'
*.DB_UNIQUE_NAME='POWER'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=POWERXDB)'
*.FAL_CLIENT='POWER'
*.FAL_SERVER='AUXPOWER'
*.job_queue_processes=10
*.log_archive_config='dg_config=(POWER,AUXPOWER)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/POWER/flash_recovery_area'
*.log_archive_dest_2='service=AUXPOWER async valid_for=(online_logfile,primary_role) db_unique_name=AUXPOWER'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.nls_language='LATIN AMERICAN SPANISH'
*.nls_territory='COLOMBIA'
*.open_cursors=300
*.pga_aggregate_target=848297984
*.processes=200
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=225
*.sga_target=1610612736
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/POWER/udump'


INIT AUXPOWER


*.audit_file_dest='/u01/app/oracle/admin/AUXPOWER/adump'
*.background_dump_dest='/u01/app/oracle/admin/AUXPOWER/bdump'
*.compatible='10.2.0.3.0'
*.core_dump_dest='/u01/app/oracle/admin/AUXPOWER/cdump'
*.control_files='/u01/app/oracle/oradata/AUXPOWER/control01.ctl','/u01/app/oracle/oradata/AUXPOWER/control02.ctl','/u01/app/oracle/oradata/AUXPOWER/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='POWER'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='/u01/app/oracle/AUXPOWER/flash_recovery_area'
*.DB_UNIQUE_NAME='AUXPOWER'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=AUXPOWERXDB)'
*.job_queue_processes=10
*.log_archive_config='dg_config=(POWER,AUXPOWER)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/AUXPOWER/flash_recovery_area'
*.log_archive_dest_2='service=POWER async valid_for=(online_logfile,primary_role) db_unique_name=POWER'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.nls_language='LATIN AMERICAN SPANISH'
*.nls_territory='COLOMBIA'
*.open_cursors=300
*.pga_aggregate_target=848297984
*.processes=200
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=225
*.sga_target=700612736
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/AUXPOWER/udump'
*.FAL_SERVER=POWER
*.FAL_CLIENT=AUXPOWER
*.db_file_name_convert=('/u01/app/oracle/oradata/POWER','/u01/app/oracle/oradata/AUXPOWER')
*.log_file_name_convert=('/u01/app/oracle/oradata/POWER','/u01/app/oracle/oradata/AUXPOWER')




SQL> alter system set log_archive_config='dg_config=(POWER,AUXPOWER)';

SQL> alter system set log_archive_dest_2='service=AUXPOWER async valid_for=(online_logfile,primary_role) db_unique_name=AUXPOWER';


-bash-3.00$ rman target sys/sys@power auxiliary /

INSTANCIA PRIMARIA


startup mount pfile=initPOWER.ora
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2139256 bytes
Variable Size 396188552 bytes
Database Buffers 1207959552 bytes
Redo Buffers 4325376 bytes
Database mounted.


SQL> alter database add standby logfile
('/u01/app/oracle/oradata/POWER/srl01.log',
'/u01/app/oracle/oradata/POWER/srl02.log',
'/u01/app/oracle/oradata/POWER/srl03.log')
size 52428800
/


SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/oradata/AUXPOWER/auxpowerstby.ctl';


SELECT * FROM V$STANDBY_LOG;


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2139256 bytes
Variable Size 396188552 bytes
Database Buffers 1207959552 bytes
Redo Buffers 4325376 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.


NOMOUNT


$ export ORACLE_SID=AUXPOWER

SQL> startup nomount pfile=initAUXPOWER.ora
ORACLE instance started.

Total System Global Area 700448768 bytes
Fixed Size 2141504 bytes
Variable Size 186471104 bytes
Database Buffers 507510784 bytes
Redo Buffers 4325376 bytes
SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-bash-3.00$ rman target sys/sys@power auxiliary /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Sep 30 18:02:57 2008

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

connected to target database: POWER (DBID=1586828541)
connected to auxiliary database: POWER (not mounted)

RMAN> duplicate target database for standby nofilenamecheck dorecover;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

SQL> alter database recover managed standby database cancel;

SQL> alter database open;

SQL> create spfile from pfile;


EJEMPLO DE SALIDA DE STANDBY DATABASE

-bash-3.00$ rman target sys/sys@power auxiliary /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Oct 1 15:57:13 2008

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

connected to target database: POWER (DBID=1586828541)
connected to auxiliary database: POWER (not mounted)

RMAN> duplicate target database for standby nofilenamecheck dorecover;

Starting Duplicate Db at 01-OCT-08
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=104 devtype=DISK

contents of Memory Script:
{
set until scn 3950745;
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script

executing command: SET until clause

Starting restore at 01-OCT-08
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: copied control file copy
input filename=/u01/app/oracle/oradata/POWER/auxpower.ctl
output filename=/u01/app/oracle/oradata/AUXPOWER/control01.ctl
output filename=/u01/app/oracle/oradata/AUXPOWER/control02.ctl
output filename=/u01/app/oracle/oradata/AUXPOWER/control03.ctl
Finished restore at 01-OCT-08

sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1

contents of Memory Script:
{
set until scn 3950745;
set newname for tempfile 1 to
"/u01/app/oracle/oradata/AUXPOWER/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/AUXPOWER/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/AUXPOWER/undotbs01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/AUXPOWER/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/AUXPOWER/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/AUXPOWER/example01.dbf";
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/AUXPOWER/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 01-OCT-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=104 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/AUXPOWER/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/AUXPOWER/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/AUXPOWER/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/AUXPOWER/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/AUXPOWER/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/POWER/flash_recovery_area/POWER/backupset/2008_10_01/o1_mf_nnndf_TAG20081001T144816_4g7o00mh_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/POWER/flash_recovery_area/POWER/backupset/2008_10_01/o1_mf_nnndf_TAG20081001T144816_4g7o00mh_.bkp tag=TAG20081001T144816
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 01-OCT-08

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy recid=8 stamp=666979072 filename=/u01/app/oracle/oradata/AUXPOWER/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=9 stamp=666979072 filename=/u01/app/oracle/oradata/AUXPOWER/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=10 stamp=666979072 filename=/u01/app/oracle/oradata/AUXPOWER/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=11 stamp=666979072 filename=/u01/app/oracle/oradata/AUXPOWER/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=12 stamp=666979072 filename=/u01/app/oracle/oradata/AUXPOWER/example01.dbf

contents of Memory Script:
{
set until scn 3950745;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 01-OCT-08
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 24 is already on disk as file /u01/app/oracle/POWER/flash_recovery_area/1_24_666695680.arc
archive log thread 1 sequence 25 is already on disk as file /u01/app/oracle/POWER/flash_recovery_area/1_25_666695680.arc
archive log thread 1 sequence 26 is already on disk as file /u01/app/oracle/POWER/flash_recovery_area/1_26_666695680.arc
archive log thread 1 sequence 27 is already on disk as file /u01/app/oracle/POWER/flash_recovery_area/1_27_666695680.arc
archive log thread 1 sequence 28 is already on disk as file /u01/app/oracle/POWER/flash_recovery_area/1_28_666695680.arc
archive log thread 1 sequence 29 is already on disk as file /u01/app/oracle/POWER/flash_recovery_area/1_29_666695680.arc
archive log thread 1 sequence 30 is already on disk as file /u01/app/oracle/POWER/flash_recovery_area/1_30_666695680.arc
archive log filename=/u01/app/oracle/POWER/flash_recovery_area/1_24_666695680.arc thread=1 sequence=24
archive log filename=/u01/app/oracle/POWER/flash_recovery_area/1_25_666695680.arc thread=1 sequence=25
archive log filename=/u01/app/oracle/POWER/flash_recovery_area/1_26_666695680.arc thread=1 sequence=26
archive log filename=/u01/app/oracle/POWER/flash_recovery_area/1_27_666695680.arc thread=1 sequence=27
archive log filename=/u01/app/oracle/POWER/flash_recovery_area/1_28_666695680.arc thread=1 sequence=28
archive log filename=/u01/app/oracle/POWER/flash_recovery_area/1_29_666695680.arc thread=1 sequence=29
archive log filename=/u01/app/oracle/POWER/flash_recovery_area/1_30_666695680.arc thread=1 sequence=30
media recovery complete, elapsed time: 00:00:02
Finished recover at 01-OCT-08
Finished Duplicate Db at 01-OCT-08

RMAN> exit


Recovery Manager complete.
-bash-3.00$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Oct 1 15:59:23 2008

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 AUXPOWER
afrodita
10.2.0.4.0 01/10/08 MOUNTED NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/AUXPOWER/flash_recovery_area
Oldest online log sequence 23
Next log sequence to archive 0
Current log sequence 31
SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 AUXPOWER
afrodita
10.2.0.4.0 01/10/08 MOUNTED NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> set line 300
SQL> /



SQL> SELECT * FROM V$DATAGUARD_STATS;


INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
--------------- ---------------- ---------------------------------------------------------------- ----------------- -------- ------------ --- ---------- ------- --------------- ---------- --- ----------------- ------------------ --------- ---
1 AUXPOWER afrodita 10.2.0.4.0 01/10/08 MOUNTED NO 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/AUXPOWER/flash_recovery_area
Oldest online log sequence 31
Next log sequence to archive 0
Current log sequence 31
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

SQL> SELECT * FROM V$DATAGUARD_STATS;


SQL> ALTER DATABASE OPEN READ ONLY;


Verify the new redo data was archived on the standby database.

SQL > SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


SQL> SELECT NAME,OPEN_MODE,SWITCHOVER_STATUS,GUARD_STATUS,CURRENT_SCN,FLASHBACK_ON, DB_UNIQUE_NAME, STANDBY_BECAME_PRIMARY_SCN FROM V$DATABASE;

SQL > SELECT PROTECTION_MODE FROM V$DATABASE;


Verify that received redo has been applied.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#


SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;





SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;


SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;


SQL> SELECT NAME,GUARD_STATUS,CURRENT_SCN,LAST_OPEN_INCARNATION#,SWITCHOVER_STATUS FROM V$DATABASE


SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE

SQL> SELECT * FROM V$MANAGED_STANDBY

RMAN on Windows

Another way to check the service name command is to use the SC command. Here is how
you can check the database service:

C:\>sc query OracleServiceMOBDB11

If the service is set to Manual, then you must start it. To start it, you can right-click the name of the service and click Start. Alternatively, you can execute the following command from the command prompt:

C:> net start OracleServiceMOBDB11

creating an Oracle service to starting/stopping and even removing the services.
Here is how you can start the Oracle service using this tool:

C:\> oradim -startup -sid MOBDB11 -starttype srvc,inst


You can also use the following command to see the users in the ORA_DBA group:

c:\work\orascripts>net localgroup ora_dba

Alias name ora_dba
Comment Oracle DBA Group
Members
-------------------------------------------------------------------------
arup
CORP\arupnan
NT AUTHORITY\SYSTEM
The command completed successfully.

RMAN Using Recovery Advisor

Listing Failures

Problem
You suspect you have a media failure in your database. You want to use the Data Recovery Advisor to list media failures.

Solution
Use the list failure command as shown here:

RMAN> list failure;


Can determine the location of the base directory for the ADR by viewing the diagnostic_dest initialization parameter. You can view the names and values of directories in the ADR with this query:

SQL> select name, value from v$diag_info;

If you suspect there is a problem with your database and the Data Recovery Advisor is not reporting a failure, you can proactively initiate a database health check by running the following

RMAN> validate database;

RMAN> list failure 15 detail;

Table 20-1. Data Recovery Advisor List Failure Options Failure Type Description
ALL Lists all failures

CRITICAL Lists only critical failures with an OPEN status
HIGH Lists only high-priority failures with an OPEN status
LOW Lists only low-priority failures with an OPEN status CLOSED Lists failures with a CLOSED status
EXCLUDE FAILURE Excludes failures by a specified failure number
DETAIL Provides verbose description of the failure

If you want to see all failures that have been resolved and closed, then use the closed clause:

RMAN> list failure closed;


Getting Advice

Problem

You’ve experienced a media failure. You want to get advice from the Data Recovery Advisor about how to restore and recover your database.

Solution

First use the list failure command to display all failures (see recipe 20-1 for details). If you have only one failure associated with your database, then you can run the advise failure command without any parameters:

RMAN> advise failure;

RMAN> advise failure 15;

specifying one of the following keywords: ALL, CRITICAL, HIGH, or LOW. This example
displays advice about just the critical errors:

RMAN> advise failure critical;


Repairing Failures

Problem

You’ve experienced a media failure. You want to use the Data Recovery Advisor to perform a restore and recovery.

Solution

We recommend that you always run the following RMAN commands in this order:

1. list failure
2. advise failure
3. repair failure

By running list failure and advise failure, you will gain an understanding as to what
the problem is and how to fix it. You can use the repair failure command to run the repair script generated by the Data Recovery Advisor:


RMAN> repair failure;

You will be prompted to specify whether you want to run the repair script:
Do you really want to execute the above repair (enter YES or NO)?

RMAN> repair failure preview;

The preview clause instructs RMAN to display only the commands that it recommends be
run to resolve the failure. If you want to spool the output of a preview command to a file, then use the spool command:

RMAN> spool log to rmanout.txt;
RMAN> repair failure preview;

If you don’t want to be prompted during a repair session, then use the noprompt clause:

RMAN> repair failure noprompt;