miércoles, 25 de noviembre de 2009
Oracle Sqlplus Unix Shell PLANOS
Cómo pasar valores desde sqlplus hacia Unix Shell
Ver Instancia Unicamente desde Unix
ps -fea | grep pmon |grep -v grep | awk {'print $9'} | cut -c 10-100
Hay ocasiones que dentro de un Shell script queremos consultar la base de datos y con dicho resultado continuar la lógica de nuestro Shell. Como logramos transferir ese resultado?
Tenemos dos enfoques para capturar valores luego de terminada la ejecución de Sql*plus: por archivos y por variables.
La primera de ellas es básicamente escribir la salida de la consulta en un archivo y luego levantarlo desde Shell. Este caso es ideal cuando nuestra consulta retorna varios registros, ya que el archivo servirá de entrada a algún comando Unix para procesarlo línea a línea. No veremos un ejemplo ya que es muy sencillo, basta usar el comando SPOOL de Sqlplus para obtener la salida en un archivo de texto. Si bien no es del todo prolijo, funciona.
Unix lo hace fácil: ejecutamos la consulta en una sesión sqlplus, y la salida es capturada en la variable 'resultado'. Para simplificar este ejemplo no he considerado si hubo algún error en la consulta, pero puede agregarse lógica que trate mensajes de error dentro de la variable asignada.
#!/bin/ksh
resultado=`sqlplus -s 'scott/tiger' << EOF
set serveroutput on
set feedback offset head off}
select * from dual;exit;
EOF`
echo "El resultado es: $resultado"
Varios resultados en una línea
Qué hacer si necesitamos retornar más de un valor? Continuamos retornando una única línea, pero separamos los valores dentro del sql con algún caracter que no ocurra dentro de cada resultado, por ejemplo punto y coma. En el ejemplo obtenemos el usuario conectado, la fecha actual y el valor de la columna de Dual, todo al mismo tiempo.
#!/bin/kshresultado=`sqlplus -s 'scott/tiger' << EOF
set serveroutput onset feedback off
set head offselect user||';'||sysdate||';'||dummy from dual;exit;EOF`
echo "Los valores son: $resultado"El valor retornado es: SCOTT;05-FEB-09;X
Con el comando cut separamos los valores fácilmente:
echo $resultado | cut -d';' -f1
SCOTT
echo $resultado | cut -d';' -f2
05-FEB-09
echo $resultado | cut -d';' -f3
X
Resultados multilínea
Si queremos obtener varias líneas en lugar de una sola, también podemos hacerlo de esta forma. Como en el caso anterior, si tenemos múltiples valores por línea es aconsejable usar separadores, ya que los espacios no son buenos a la hora de identificar strings que contengan espacios. Además, evitamos los incómodos espacios entre líneas al optimizar el tamaño de cada cadena y no llegar al fin de cada línea.
#!/bin/ksh
resultado=`sqlplus -s 'scott/tiger' << EOF
set serveroutput on
set feedback off
set head off
set linesize 131
set pagesize 9999
select empno||';'||ename||';'||job||';'||mgr||';'||deptno from emp;
exit;
EOF`
echo "El resultado es: $resultado"
La salida en este caso es:
7369;SMITH;CLERK;7902;20
7499;ALLEN;SALESMAN;7698;30
7521;WARD;SALESMAN;7698;30
7566;JONES;MANAGER;7839;20
7654;MARTIN;SALESMAN;7698;30
7698;BLAKE;MANAGER;7839;30
7782;CLARK;MANAGER;7839;10
7788;SCOTT;ANALYST;7566;20
7839;KING;PRESIDENT;;10
7844;TURNER;SALESMAN;7698;30
7876;ADAMS;CLERK;7788;20
7900;JAMES;CLERK;7698;30
7902;FORD;ANALYST;7566;20
Ahora, podemos usar cualquier comando para tratar las líneas. Uno de mis favoritos es awk, ya que nos provee de muchas funciones para tratamiento de cada tipo.
Para que awk consuma cada línea de la variable como si fuese un archivo, debemos incluir comillas dobles, de otro modo lo considerará como una única gran línea.
En el siguiente ejemplo, vemos como awk toma línea a línea e imprime un texto anexo.
echo "$resultado" | awk -F";" 'BEGIN {$cnt=1}
{print "Linea "$cnt, $0; $cnt=$cnt+1;}'
La salida generada por awk es:
Linea 1 7369;SMITH;CLERK;7902;20
Linea 2 7499;ALLEN;SALESMAN;7698;30
Linea 3 7521;WARD;SALESMAN;7698;30
Linea 4 7566;JONES;MANAGER;7839;20
Linea 5 7654;MARTIN;SALESMAN;7698;30
Linea 6 7698;BLAKE;MANAGER;7839;30
Linea 7 7782;CLARK;MANAGER;7839;10
Linea 8 7788;SCOTT;ANALYST;7566;20
Linea 9 7839;KING;PRESIDENT;;10
Linea 10 7844;TURNER;SALESMAN;7698;30
Linea 11 7876;ADAMS;CLERK;7788;20
Linea 12 7900;JAMES;CLERK;7698;30
Linea 13 7902;FORD;ANALYST;7566;20
Linea 14 7934;MILLER;CLERK;7782;10
lunes, 9 de noviembre de 2009
Oracle 11g Database Replay
Database Replay in Oracle Database 11g Release 1
The Database Replay functionality of Oracle 11g allows you to capture workloads on a production system and replay them exactly as they happened on a test system. This provides an accurate method to test the impact of a variety of system changes including:
Database upgrades.
Operating system upgrades or migrations.
Configuration changes, such as changes to initialization parameters or conversion from a single node to a RAC environment.
Hardware changes or migrations.
The capture and replay processes can be configured and initiated using PL/SQL APIs, or Enterprise Manager, both of which are demonstrated in this article. To keep things simple, the examples presented here are performed against two servers (prod-11g and test-11g), both of which run an identical database with a SID of DB11G.
Capture using the DBMS_WORKLOAD_CAPTURE Package
Replay using the DBMS_WORKLOAD_REPLAY Package
Capture using Enterprise Manager
Replay using Enterprise Manager
Capture using the DBMS_WORKLOAD_CAPTURE Package
The DBMS_WORKLOAD_CAPTURE package provides a set of procedures and functions to control the capture process. Before we can initiate the capture process we need an empty directory on the "prod-11g" database server to hold the capture logs.
mkdir /u01/app/oracle/db_replay_captureNext, we create a directory object pointing to the new directory.
CONN sys/password@prod AS SYSDBA
CREATE OR REPLACE DIRECTORY db_replay_capture_dir AS '/u01/app/oracle/db_replay_capture/';
-- Make sure existing processes are complete.
SHUTDOWN IMMEDIATE
STARTUPNotice the inclusion of a shutdown and startup of the database. This is not necessary, but Oracle suggest it as a good way to make sure any outstanding processes are complete before starting the capture process.
The combination of the ADD_FILTER procedure and the DEFAULT_ACTION parameter of the START_CAPTURE procedure allow the workload to be refined by including or excluding specific work based on the following attributes:
INSTANCE_NUMBER
USER
MODULE
ACTION
PROGRAM
SERVICE
For simplicity let's assume we want to capture everything, so we can ignore this and jump straight to the START_CAPTURE procedure. This procedure allows us to name a capture run, specify the directory the capture files should be placed in, and specify the length of time the capture process should run for. If the duration is set to NULL, the capture runs until it is manually turned off using the FINISH_CAPTURE procedure.
BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture (name => 'test_capture_1',
dir => 'DB_REPLAY_CAPTURE_DIR',
duration => NULL);
END;
/Now, we need to do some work to capture. First, we create a test user.
CREATE USER db_replay_test IDENTIFIED BY db_replay_test
QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE TO db_replay_test;Next, we create a table and populate it with some data.
CONN db_replay_test/db_replay_test@prod
CREATE TABLE db_replay_test_tab (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT db_replay_test_tab_pk PRIMARY KEY (id)
);
BEGIN
FOR i IN 1 .. 500000 LOOP
INSERT INTO db_replay_test_tab (id, description)
VALUES (i, 'Description for ' || i);
END LOOP;
COMMIT;
END;
/Once the work is complete we can stop the capture using the FINISH_CAPTURE procedure.
CONN sys/password@prod AS SYSDBA
BEGIN
DBMS_WORKLOAD_CAPTURE.finish_capture;
END;
/If we check out the capture directory, we can see that some files have been generated there.
$ cd /u01/app/oracle/db_replay_capture
$ ls
wcr_4f9rtgw00238y.rec wcr_cr.html wcr_scapture.wmd
wcr_4f9rtjw002397.rec wcr_cr.text
wcr_4f9rtyw00239h.rec wcr_fcapture.wmd
$We can retrieve the ID of the capture run by passing the directory object name to the GET_CAPTURE_INFO function, or by querying the DBA_WORKLOAD_CAPTURES view.
SELECT DBMS_WORKLOAD_CAPTURE.get_capture_info('DB_REPLAY_CAPTURE_DIR')
FROM dual;
DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO('DB_REPLAY_CAPTURE_DIR')
---------------------------------------------------------------
21
1 row selected.
SQL>
COLUMN name FORMAT A30
SELECT id, name FROM dba_workload_captures;
ID NAME
---------- ------------------------------
21 test_capture_1
1 row selected.
SQL>The DBA_WORKLOAD_CAPTURES view contains information about the capture process. This can be queried directly, or a report can be generated in text or HTML format using the REPORT function.
DECLARE
l_report CLOB;
BEGIN
l_report := DBMS_WORKLOAD_CAPTURE.report(capture_id => 21,
format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);
END;
/The capture ID can be used to export the AWR snapshots associated with the specific capture run.
BEGIN
DBMS_WORKLOAD_CAPTURE.export_awr (capture_id => 21);
END;
/A quick look at the capture directory shows a dump file and associated log file have been produced.
$ cd /u01/app/oracle/db_replay_capture
$ ls
wcr_4f9rtgw00238y.rec wcr_ca.dmp wcr_cr.text
wcr_4f9rtjw002397.rec wcr_ca.log wcr_fcapture.wmd
wcr_4f9rtyw00239h.rec wcr_cr.html wcr_scapture.wmd
$Replay using the DBMS_WORKLOAD_REPLAY Package
The DBMS_WORKLOAD_REPLAY package provides a set of procedures and functions to control the replay process. In order to replay the logs captured on the "prod-11g" system, we need to transfers the capture files to our test system. Before we can do this, we need to create a directory on the "test-11g" system to put them in. For simplicity we will keep the name the same.
mkdir /u01/app/oracle/db_replay_captureTransfer the files from the production server to the test server.
Next, we create a directory object pointing to the new directory.
CONN sys/password@test AS SYSDBA
CREATE OR REPLACE DIRECTORY db_replay_capture_dir AS '/u01/app/oracle/db_replay_capture/';It is a good idea to adjust the test system time to match the time when the capture process was started. This way any time-based processing will react in the same way. For this test I have ignored this step.
We can now prepare to replay the existing capture logs using the PROCESS_CAPTURE, INITIALIZE_REPLAY and PREPARE_REPLAY procedures. I've named the replay with the same name as the capture process (test_capture_1), but this is not necessary.
BEGIN
DBMS_WORKLOAD_REPLAY.process_capture('DB_REPLAY_CAPTURE_DIR');
DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => 'test_capture_1',
replay_dir => 'DB_REPLAY_CAPTURE_DIR');
DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => TRUE);
END;
/Before we can start the replay, we need to calibrate and start a replay client using the "wrc" utility. The calibration step tells us the number of replay clients and hosts necessary to faithfully replay the workload.
$ wrc mode=calibrate replaydir=/u01/app/oracle/db_replay_capture
Workload Replay Client: Release 11.1.0.6.0 - Production on Tue Oct 30 09:33:42 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Report for Workload in: /u01/app/oracle/db_replay_capture
-----------------------
Recommendation:
Consider using at least 1 clients divided among 1 CPU(s).
Workload Characteristics:
- max concurrency: 1 sessions
- total number of sessions: 3
Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE
$The calibration step suggest a single client on a single CPU is enough, so we only need to start a single replay client, which is shown below.
$ wrc system/password@test mode=replay replaydir=/u01/app/oracle/db_replay_capture
Workload Replay Client: Release 11.1.0.6.0 - Production on Tue Oct 30 09:34:14 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Wait for the replay to start (09:34:14)The replay client pauses waiting for replay to start. We initiate replay with the following command.
BEGIN
DBMS_WORKLOAD_REPLAY.start_replay;
END;
/If you need to stop the replay before it is complete, call the CANCEL_REPLAY procedure.
The output from the replay client includes the start and finish time of the replay operation.
$ wrc system/password@test mode=replay replaydir=/u01/app/oracle/db_replay_capture
Workload Replay Client: Release 11.1.0.6.0 - Production on Tue Oct 30 09:34:14 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Wait for the replay to start (09:34:14)
Replay started (09:34:44)
Replay finished (09:39:15)
$Once complete, we can see the DB_REPLAY_TEST_TAB table has been created and populated in the DB_REPLAY_TEST schema.
SQL> CONN sys/password@test AS SYSDBA
Connected.
SQL> SELECT table_name FROM dba_tables WHERE owner = 'DB_REPLAY_TEST';
TABLE_NAME
------------------------------
DB_REPLAY_TEST_TAB
SQL> SELECT COUNT(*) FROM db_replay_test.db_replay_test_tab;
COUNT(*)
----------
500000
SQL>Information about the replay processing is available from the DBA_WORKLOAD_REPLAYS view.
COLUMN name FORMAT A30
SELECT id, name FROM dba_workload_replays;
ID NAME
---------- ------------------------------
11 test_capture_1
1 row selected.
SQL>In addition, a report can be generated in text or HTML format using the REPORT function.
DECLARE
l_report CLOB;
BEGIN
l_report := DBMS_WORKLOAD_REPLAY.report(replay_id => 11,
format => DBMS_WORKLOAD_REPLAY.TYPE_HTML);
END;
/
The Database Replay functionality of Oracle 11g allows you to capture workloads on a production system and replay them exactly as they happened on a test system. This provides an accurate method to test the impact of a variety of system changes including:
Database upgrades.
Operating system upgrades or migrations.
Configuration changes, such as changes to initialization parameters or conversion from a single node to a RAC environment.
Hardware changes or migrations.
The capture and replay processes can be configured and initiated using PL/SQL APIs, or Enterprise Manager, both of which are demonstrated in this article. To keep things simple, the examples presented here are performed against two servers (prod-11g and test-11g), both of which run an identical database with a SID of DB11G.
Capture using the DBMS_WORKLOAD_CAPTURE Package
Replay using the DBMS_WORKLOAD_REPLAY Package
Capture using Enterprise Manager
Replay using Enterprise Manager
Capture using the DBMS_WORKLOAD_CAPTURE Package
The DBMS_WORKLOAD_CAPTURE package provides a set of procedures and functions to control the capture process. Before we can initiate the capture process we need an empty directory on the "prod-11g" database server to hold the capture logs.
mkdir /u01/app/oracle/db_replay_captureNext, we create a directory object pointing to the new directory.
CONN sys/password@prod AS SYSDBA
CREATE OR REPLACE DIRECTORY db_replay_capture_dir AS '/u01/app/oracle/db_replay_capture/';
-- Make sure existing processes are complete.
SHUTDOWN IMMEDIATE
STARTUPNotice the inclusion of a shutdown and startup of the database. This is not necessary, but Oracle suggest it as a good way to make sure any outstanding processes are complete before starting the capture process.
The combination of the ADD_FILTER procedure and the DEFAULT_ACTION parameter of the START_CAPTURE procedure allow the workload to be refined by including or excluding specific work based on the following attributes:
INSTANCE_NUMBER
USER
MODULE
ACTION
PROGRAM
SERVICE
For simplicity let's assume we want to capture everything, so we can ignore this and jump straight to the START_CAPTURE procedure. This procedure allows us to name a capture run, specify the directory the capture files should be placed in, and specify the length of time the capture process should run for. If the duration is set to NULL, the capture runs until it is manually turned off using the FINISH_CAPTURE procedure.
BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture (name => 'test_capture_1',
dir => 'DB_REPLAY_CAPTURE_DIR',
duration => NULL);
END;
/Now, we need to do some work to capture. First, we create a test user.
CREATE USER db_replay_test IDENTIFIED BY db_replay_test
QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE TO db_replay_test;Next, we create a table and populate it with some data.
CONN db_replay_test/db_replay_test@prod
CREATE TABLE db_replay_test_tab (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT db_replay_test_tab_pk PRIMARY KEY (id)
);
BEGIN
FOR i IN 1 .. 500000 LOOP
INSERT INTO db_replay_test_tab (id, description)
VALUES (i, 'Description for ' || i);
END LOOP;
COMMIT;
END;
/Once the work is complete we can stop the capture using the FINISH_CAPTURE procedure.
CONN sys/password@prod AS SYSDBA
BEGIN
DBMS_WORKLOAD_CAPTURE.finish_capture;
END;
/If we check out the capture directory, we can see that some files have been generated there.
$ cd /u01/app/oracle/db_replay_capture
$ ls
wcr_4f9rtgw00238y.rec wcr_cr.html wcr_scapture.wmd
wcr_4f9rtjw002397.rec wcr_cr.text
wcr_4f9rtyw00239h.rec wcr_fcapture.wmd
$We can retrieve the ID of the capture run by passing the directory object name to the GET_CAPTURE_INFO function, or by querying the DBA_WORKLOAD_CAPTURES view.
SELECT DBMS_WORKLOAD_CAPTURE.get_capture_info('DB_REPLAY_CAPTURE_DIR')
FROM dual;
DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO('DB_REPLAY_CAPTURE_DIR')
---------------------------------------------------------------
21
1 row selected.
SQL>
COLUMN name FORMAT A30
SELECT id, name FROM dba_workload_captures;
ID NAME
---------- ------------------------------
21 test_capture_1
1 row selected.
SQL>The DBA_WORKLOAD_CAPTURES view contains information about the capture process. This can be queried directly, or a report can be generated in text or HTML format using the REPORT function.
DECLARE
l_report CLOB;
BEGIN
l_report := DBMS_WORKLOAD_CAPTURE.report(capture_id => 21,
format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);
END;
/The capture ID can be used to export the AWR snapshots associated with the specific capture run.
BEGIN
DBMS_WORKLOAD_CAPTURE.export_awr (capture_id => 21);
END;
/A quick look at the capture directory shows a dump file and associated log file have been produced.
$ cd /u01/app/oracle/db_replay_capture
$ ls
wcr_4f9rtgw00238y.rec wcr_ca.dmp wcr_cr.text
wcr_4f9rtjw002397.rec wcr_ca.log wcr_fcapture.wmd
wcr_4f9rtyw00239h.rec wcr_cr.html wcr_scapture.wmd
$Replay using the DBMS_WORKLOAD_REPLAY Package
The DBMS_WORKLOAD_REPLAY package provides a set of procedures and functions to control the replay process. In order to replay the logs captured on the "prod-11g" system, we need to transfers the capture files to our test system. Before we can do this, we need to create a directory on the "test-11g" system to put them in. For simplicity we will keep the name the same.
mkdir /u01/app/oracle/db_replay_captureTransfer the files from the production server to the test server.
Next, we create a directory object pointing to the new directory.
CONN sys/password@test AS SYSDBA
CREATE OR REPLACE DIRECTORY db_replay_capture_dir AS '/u01/app/oracle/db_replay_capture/';It is a good idea to adjust the test system time to match the time when the capture process was started. This way any time-based processing will react in the same way. For this test I have ignored this step.
We can now prepare to replay the existing capture logs using the PROCESS_CAPTURE, INITIALIZE_REPLAY and PREPARE_REPLAY procedures. I've named the replay with the same name as the capture process (test_capture_1), but this is not necessary.
BEGIN
DBMS_WORKLOAD_REPLAY.process_capture('DB_REPLAY_CAPTURE_DIR');
DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => 'test_capture_1',
replay_dir => 'DB_REPLAY_CAPTURE_DIR');
DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => TRUE);
END;
/Before we can start the replay, we need to calibrate and start a replay client using the "wrc" utility. The calibration step tells us the number of replay clients and hosts necessary to faithfully replay the workload.
$ wrc mode=calibrate replaydir=/u01/app/oracle/db_replay_capture
Workload Replay Client: Release 11.1.0.6.0 - Production on Tue Oct 30 09:33:42 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Report for Workload in: /u01/app/oracle/db_replay_capture
-----------------------
Recommendation:
Consider using at least 1 clients divided among 1 CPU(s).
Workload Characteristics:
- max concurrency: 1 sessions
- total number of sessions: 3
Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE
$The calibration step suggest a single client on a single CPU is enough, so we only need to start a single replay client, which is shown below.
$ wrc system/password@test mode=replay replaydir=/u01/app/oracle/db_replay_capture
Workload Replay Client: Release 11.1.0.6.0 - Production on Tue Oct 30 09:34:14 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Wait for the replay to start (09:34:14)The replay client pauses waiting for replay to start. We initiate replay with the following command.
BEGIN
DBMS_WORKLOAD_REPLAY.start_replay;
END;
/If you need to stop the replay before it is complete, call the CANCEL_REPLAY procedure.
The output from the replay client includes the start and finish time of the replay operation.
$ wrc system/password@test mode=replay replaydir=/u01/app/oracle/db_replay_capture
Workload Replay Client: Release 11.1.0.6.0 - Production on Tue Oct 30 09:34:14 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Wait for the replay to start (09:34:14)
Replay started (09:34:44)
Replay finished (09:39:15)
$Once complete, we can see the DB_REPLAY_TEST_TAB table has been created and populated in the DB_REPLAY_TEST schema.
SQL> CONN sys/password@test AS SYSDBA
Connected.
SQL> SELECT table_name FROM dba_tables WHERE owner = 'DB_REPLAY_TEST';
TABLE_NAME
------------------------------
DB_REPLAY_TEST_TAB
SQL> SELECT COUNT(*) FROM db_replay_test.db_replay_test_tab;
COUNT(*)
----------
500000
SQL>Information about the replay processing is available from the DBA_WORKLOAD_REPLAYS view.
COLUMN name FORMAT A30
SELECT id, name FROM dba_workload_replays;
ID NAME
---------- ------------------------------
11 test_capture_1
1 row selected.
SQL>In addition, a report can be generated in text or HTML format using the REPORT function.
DECLARE
l_report CLOB;
BEGIN
l_report := DBMS_WORKLOAD_REPLAY.report(replay_id => 11,
format => DBMS_WORKLOAD_REPLAY.TYPE_HTML);
END;
/
Oracle 11g Query Result Cache
Query Result Cache in Oracle Database 11g Release 1
Oracle 11g allows the results of SQL queries to be cached in the SGA and reused to improve performance. Set up the following schema objects to see how the SQL query cache works.
CREATE TABLE qrc_tab (
id NUMBER
);
INSERT INTO qrc_tab VALUES (1);
INSERT INTO qrc_tab VALUES (2);
INSERT INTO qrc_tab VALUES (3);
INSERT INTO qrc_tab VALUES (4);
INSERT INTO qrc_tab VALUES (5);
CREATE OR REPLACE FUNCTION slow_function(p_id IN qrc_tab.id%TYPE)
RETURN qrc_tab.id%TYPE DETERMINISTIC AS
BEGIN
DBMS_LOCK.sleep(1);
RETURN p_id;
END;
/
SET TIMING ONThe function contains a one second sleep so we can easily detect if it has been executed by checking the elapsed time of the query.
Next, we query the test table using the slow function and check out the elapsed time. Each run takes approximately five seconds, one second sleep for each row queried.
SELECT slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
5 rows selected.
Elapsed: 00:00:05.15
SQL>Adding the RESULT_CACHE hint to the query tells the server to attempt to retrieve the information from the result cache. If the information is not present, it will cache the results of the query provided there is enough room in the result cache. Since we have no cached results, we would expect the first run to take approximately five seconds, but subsequent runs to be much quicker.
SELECT /*+ result_cache */ slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
5 rows selected.
Elapsed: 00:00:05.20
SELECT /*+ result_cache */ slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
5 rows selected.
Elapsed: 00:00:00.15
SQL>The default action of the result cache is controlled by the RESULT_CACHE_MODE parameter. When it is set to MANUAL, the RESULT_CACHE hint must be used for a query to access the result cache.
SHOW PARAMETER RESULT_CACHE_MODE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode string MANUAL
SQL>If we set the RESULT_CACHE_MODE parameter to FORCE, the result cache is used by default, but we can bypass it using the NO_RESULT_CACHE hint.
ALTER SESSION SET RESULT_CACHE_MODE=FORCE;
SELECT slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
5 rows selected.
Elapsed: 00:00:00.14
SELECT /*+ no_result_cache */ slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
5 rows selected.
Elapsed: 00:00:05.14
SQL>
Oracle 11g allows the results of SQL queries to be cached in the SGA and reused to improve performance. Set up the following schema objects to see how the SQL query cache works.
CREATE TABLE qrc_tab (
id NUMBER
);
INSERT INTO qrc_tab VALUES (1);
INSERT INTO qrc_tab VALUES (2);
INSERT INTO qrc_tab VALUES (3);
INSERT INTO qrc_tab VALUES (4);
INSERT INTO qrc_tab VALUES (5);
CREATE OR REPLACE FUNCTION slow_function(p_id IN qrc_tab.id%TYPE)
RETURN qrc_tab.id%TYPE DETERMINISTIC AS
BEGIN
DBMS_LOCK.sleep(1);
RETURN p_id;
END;
/
SET TIMING ONThe function contains a one second sleep so we can easily detect if it has been executed by checking the elapsed time of the query.
Next, we query the test table using the slow function and check out the elapsed time. Each run takes approximately five seconds, one second sleep for each row queried.
SELECT slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
5 rows selected.
Elapsed: 00:00:05.15
SQL>Adding the RESULT_CACHE hint to the query tells the server to attempt to retrieve the information from the result cache. If the information is not present, it will cache the results of the query provided there is enough room in the result cache. Since we have no cached results, we would expect the first run to take approximately five seconds, but subsequent runs to be much quicker.
SELECT /*+ result_cache */ slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
5 rows selected.
Elapsed: 00:00:05.20
SELECT /*+ result_cache */ slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
5 rows selected.
Elapsed: 00:00:00.15
SQL>The default action of the result cache is controlled by the RESULT_CACHE_MODE parameter. When it is set to MANUAL, the RESULT_CACHE hint must be used for a query to access the result cache.
SHOW PARAMETER RESULT_CACHE_MODE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode string MANUAL
SQL>If we set the RESULT_CACHE_MODE parameter to FORCE, the result cache is used by default, but we can bypass it using the NO_RESULT_CACHE hint.
ALTER SESSION SET RESULT_CACHE_MODE=FORCE;
SELECT slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
5 rows selected.
Elapsed: 00:00:00.14
SELECT /*+ no_result_cache */ slow_function(id) FROM qrc_tab;
SLOW_FUNCTION(ID)
-----------------
1
2
3
4
5
5 rows selected.
Elapsed: 00:00:05.14
SQL>
Oracle 11g Flashback Data Archive (Oracle Total Recall)
Flashback Data Archive (Oracle Total Recall)
Most flashback features work at the logical level, in that they don't directly allow you to recover an object. They simply give you a view of how the data was in the past, which in turn can be used to recover from logical corruption, such as accidental deletions. Typically, this view of the past is constructed using undo segments, which are retained for a period of time indicated by the UNDO_RETENTION parameter. Once the undo information is lost, the view of the past is lost also. At least that's how it was before Oracle 11g introduced the Flashback Data Archive functionality.
A flashback data archive is essentially an extended store of undo information, allowing some logical flashback operations to extend way back into the past with no difference in how they are coded. An individual flashback archives consists of one or more tablespaces, or parts of tablespace. Each flashback archive has a name, retention period and a quota on each associated tablespace. The database can have multiple flashback data archives, but only a single default archive. When a DML transaction commits an operation on a flashback archive enabled table, the Flashback Data Archiver (FBDA) process stores the pre-image of the rows into a flashback archive, along with metadata of the current rows. The FBDA process is also responsible for managing the data within the flashback archives, such as purging data beyond the retention period.
The following script creates a new tablespace, then creates two flashback data archives using the CREATE FLASHBACK ARCHIVE command. The first is limited in size to 10Gig with a retention period of 1 year, while the second has an unlimited quota and a retention period of 2 years.
CREATE TABLESPACE fda_ts
DATAFILE '/u01/app/oracle/oradata/DB11G/fda1_01.dbf'
SIZE 1M AUTOEXTEND ON NEXT 1M;
CREATE FLASHBACK ARCHIVE DEFAULT fda_1year TABLESPACE fda_ts
QUOTA 10G RETENTION 1 YEAR;
CREATE FLASHBACK ARCHIVE fda_2year TABLESPACE fda_ts
RETENTION 2 YEAR;Management of flashback archives falls into three distinct categories:
Tablespace management.
-- Set as default FBA
ALTER FLASHBACK ARCHIVE fba_name SET DEFAULT;
-- Add up to 10G of the specified tablespace to the specified flashback archive.
ALTER FLASHBACK ARCHIVE fba_name ADD TABLESPACE ts_name QUOTA 10G;
-- Add an unlimited quota of the specified tablespace to the specified flashback archive.
ALTER FLASHBACK ARCHIVE fba_name ADD TABLESPACE ts_name;
-- Change the tablespace quota to 20G.
ALTER FLASHBACK ARCHIVE fba_name MODIFY TABLESPACE ts_name QUOTA 20G;
-- Change the tablespace quota to unlimited.ALTER FLASHBACK ARCHIVE fba_name MODIFY TABLESPACE ts_name;
-- Remove the specified tablespace from the archive.
ALTER FLASHBACK ARCHIVE fba_name REMOVE TABLESPACE ts_name;Modifying the retention period.
ALTER FLASHBACK ARCHIVE fba_name MODIFY RETENTION 2 YEAR;
Purging data.
-- Remove all historical data.
ALTER FLASHBACK ARCHIVE fba_name PURGE ALL;
-- Remove all data before the specified time.
ALTER FLASHBACK ARCHIVE fba_name PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
-- Remove all data before the specified SCN.
ALTER FLASHBACK ARCHIVE fba_name PURGE BEFORE SCN 728969;Archives are removed using the DROP FLASHBACK ARCHIVE command, which drops the archive and all its historical data, but doesn't drop the associated tablespace.
DROP FLASHBACK ARCHIVE fba_name;
To enable flashback archiving on a specific table the user must have the FLASHBACK ARCHIVE object privilege on the specific flashback archive used. To try this out create a test user and grant the object privilege on the first flashback archive created earlier.
CONN sys/password AS SYSDBA
CREATE USER fda_test_user IDENTIFIED BY fda_test_user QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE TO fda_test_user;
GRANT FLASHBACK ARCHIVE ON fda_1year TO fda_test_user;
If we connect to the test user we are able to create a table and associate it with the default flashback archive as follows.
CONN fda_test_user/fda_test_user
CREATE TABLE test_tab_1 (
id NUMBER,
desription VARCHAR2(50),
CONSTRAINT test_tab_1_pk PRIMARY KEY (id)
)
FLASHBACK ARCHIVE;If we try to create a similar table, but point it at the second archive it fails, as we have no privileges on it.
CONN fda_test_user/fda_test_user
CREATE TABLE test_tab_2 (
id NUMBER,
desription VARCHAR2(50),
CONSTRAINT test_tab_2_pk PRIMARY KEY (id)
)
FLASHBACK ARCHIVE fda_2year;
CREATE TABLE test_tab_2 (
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
SQL>The ALTER TABLE command allows existing tables to have flashback archiving switched on or off.
-- Enable using the default FBDA.
ALTER TABLE table_name FLASHBACK ARCHIVE;
-- Enable using specific FBDA.
ALTER TABLE table_name FLASHBACK ARCHIVE fda_name;
-- Disable flashback archiving.
ALTER TABLE table_name NO FLASHBACK ARCHIVE;
As with the CREATE TABLE statement, the FLASHBACK ARCHIVE object privilege must be granted on the flashback archive being used.
Once flashback archiving is enabled the table is protected, so it cannot be switched off unless you have the FLASHBACK ARCHIVE ADMINISTER system privilege, or are logged on as SYSDBA.
SQL> ALTER TABLE test_tab_1 NO FLASHBACK ARCHIVE;
ALTER TABLE test_tab_1 NO FLASHBACK ARCHIVE
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
SQL>In addition, there are certain DDL restrictions associated with having flashback archiving enabled. The following operations result in a ORA-55610 error:
ALTER TABLE statements that drop, rename or modify columns.
ALTER TABLE statements that performs partition or subpartition operations.
ALTER TABLE statements that converts a LONG column to a LOB column.
ALTER TABLE statements that includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause.
DROP TABLE statements.
RENAME TABLE statements.
TRUNCATE TABLE statements.
Information about flashback data archives is displayed using the %_FLASHBACK_ARCHIVE view.
CONN sys/password AS SYSDBA
COLUMN flashback_archive_name FORMAT A20
SELECT flashback_archive_name, retention_in_days, status
FROM dba_flashback_archive;
FLASHBACK_ARCHIVE_NA RETENTION_IN_DAYS STATUS
-------------------- ----------------- -------
FDA_2YEAR 730
FDA_1YEAR 365 DEFAULT
2 rows selected.
SQL>The %_FLASHBACK_ARCHIVE_TS view displays the tablespaces and quotas associated with each flashback archive.
COLUMN flashback_archive_name FORMAT A20
COLUMN quota_in_mb FORMAT A10
SELECT flashback_archive_name, tablespace_name, quota_in_mb
FROM dba_flashback_archive_ts;
FLASHBACK_ARCHIVE_NA TABLESPACE_NAME QUOTA_IN_M
-------------------- ------------------------------ ----------
FDA_2YEAR FDA_TS
FDA_1YEAR FDA_TS 10240
2 rows selected.
SQL>
The %_FLASHBACK_ARCHIVE_TABLES view displays tables associated with each flashback archive, along with the name of the table holding the historical information.
COLUMN table_name FORMAT A15
COLUMN owner_name FORMAT A15
COLUMN flashback_archive_name FORMAT A20
COLUMN archive_table_name FORMAT A20
SELECT table_name, owner_name, flashback_archive_name, archive_table_name
FROM dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME
--------------- --------------- -------------------- --------------------
TEST_TAB_1 FDA_TEST_USER FDA_1YEAR SYS_FBA_HIST_72023
1 row selected.
SQL>
Most flashback features work at the logical level, in that they don't directly allow you to recover an object. They simply give you a view of how the data was in the past, which in turn can be used to recover from logical corruption, such as accidental deletions. Typically, this view of the past is constructed using undo segments, which are retained for a period of time indicated by the UNDO_RETENTION parameter. Once the undo information is lost, the view of the past is lost also. At least that's how it was before Oracle 11g introduced the Flashback Data Archive functionality.
A flashback data archive is essentially an extended store of undo information, allowing some logical flashback operations to extend way back into the past with no difference in how they are coded. An individual flashback archives consists of one or more tablespaces, or parts of tablespace. Each flashback archive has a name, retention period and a quota on each associated tablespace. The database can have multiple flashback data archives, but only a single default archive. When a DML transaction commits an operation on a flashback archive enabled table, the Flashback Data Archiver (FBDA) process stores the pre-image of the rows into a flashback archive, along with metadata of the current rows. The FBDA process is also responsible for managing the data within the flashback archives, such as purging data beyond the retention period.
The following script creates a new tablespace, then creates two flashback data archives using the CREATE FLASHBACK ARCHIVE command. The first is limited in size to 10Gig with a retention period of 1 year, while the second has an unlimited quota and a retention period of 2 years.
CREATE TABLESPACE fda_ts
DATAFILE '/u01/app/oracle/oradata/DB11G/fda1_01.dbf'
SIZE 1M AUTOEXTEND ON NEXT 1M;
CREATE FLASHBACK ARCHIVE DEFAULT fda_1year TABLESPACE fda_ts
QUOTA 10G RETENTION 1 YEAR;
CREATE FLASHBACK ARCHIVE fda_2year TABLESPACE fda_ts
RETENTION 2 YEAR;Management of flashback archives falls into three distinct categories:
Tablespace management.
-- Set as default FBA
ALTER FLASHBACK ARCHIVE fba_name SET DEFAULT;
-- Add up to 10G of the specified tablespace to the specified flashback archive.
ALTER FLASHBACK ARCHIVE fba_name ADD TABLESPACE ts_name QUOTA 10G;
-- Add an unlimited quota of the specified tablespace to the specified flashback archive.
ALTER FLASHBACK ARCHIVE fba_name ADD TABLESPACE ts_name;
-- Change the tablespace quota to 20G.
ALTER FLASHBACK ARCHIVE fba_name MODIFY TABLESPACE ts_name QUOTA 20G;
-- Change the tablespace quota to unlimited.ALTER FLASHBACK ARCHIVE fba_name MODIFY TABLESPACE ts_name;
-- Remove the specified tablespace from the archive.
ALTER FLASHBACK ARCHIVE fba_name REMOVE TABLESPACE ts_name;Modifying the retention period.
ALTER FLASHBACK ARCHIVE fba_name MODIFY RETENTION 2 YEAR;
Purging data.
-- Remove all historical data.
ALTER FLASHBACK ARCHIVE fba_name PURGE ALL;
-- Remove all data before the specified time.
ALTER FLASHBACK ARCHIVE fba_name PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
-- Remove all data before the specified SCN.
ALTER FLASHBACK ARCHIVE fba_name PURGE BEFORE SCN 728969;Archives are removed using the DROP FLASHBACK ARCHIVE command, which drops the archive and all its historical data, but doesn't drop the associated tablespace.
DROP FLASHBACK ARCHIVE fba_name;
To enable flashback archiving on a specific table the user must have the FLASHBACK ARCHIVE object privilege on the specific flashback archive used. To try this out create a test user and grant the object privilege on the first flashback archive created earlier.
CONN sys/password AS SYSDBA
CREATE USER fda_test_user IDENTIFIED BY fda_test_user QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE TO fda_test_user;
GRANT FLASHBACK ARCHIVE ON fda_1year TO fda_test_user;
If we connect to the test user we are able to create a table and associate it with the default flashback archive as follows.
CONN fda_test_user/fda_test_user
CREATE TABLE test_tab_1 (
id NUMBER,
desription VARCHAR2(50),
CONSTRAINT test_tab_1_pk PRIMARY KEY (id)
)
FLASHBACK ARCHIVE;If we try to create a similar table, but point it at the second archive it fails, as we have no privileges on it.
CONN fda_test_user/fda_test_user
CREATE TABLE test_tab_2 (
id NUMBER,
desription VARCHAR2(50),
CONSTRAINT test_tab_2_pk PRIMARY KEY (id)
)
FLASHBACK ARCHIVE fda_2year;
CREATE TABLE test_tab_2 (
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
SQL>The ALTER TABLE command allows existing tables to have flashback archiving switched on or off.
-- Enable using the default FBDA.
ALTER TABLE table_name FLASHBACK ARCHIVE;
-- Enable using specific FBDA.
ALTER TABLE table_name FLASHBACK ARCHIVE fda_name;
-- Disable flashback archiving.
ALTER TABLE table_name NO FLASHBACK ARCHIVE;
As with the CREATE TABLE statement, the FLASHBACK ARCHIVE object privilege must be granted on the flashback archive being used.
Once flashback archiving is enabled the table is protected, so it cannot be switched off unless you have the FLASHBACK ARCHIVE ADMINISTER system privilege, or are logged on as SYSDBA.
SQL> ALTER TABLE test_tab_1 NO FLASHBACK ARCHIVE;
ALTER TABLE test_tab_1 NO FLASHBACK ARCHIVE
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
SQL>In addition, there are certain DDL restrictions associated with having flashback archiving enabled. The following operations result in a ORA-55610 error:
ALTER TABLE statements that drop, rename or modify columns.
ALTER TABLE statements that performs partition or subpartition operations.
ALTER TABLE statements that converts a LONG column to a LOB column.
ALTER TABLE statements that includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause.
DROP TABLE statements.
RENAME TABLE statements.
TRUNCATE TABLE statements.
Information about flashback data archives is displayed using the %_FLASHBACK_ARCHIVE view.
CONN sys/password AS SYSDBA
COLUMN flashback_archive_name FORMAT A20
SELECT flashback_archive_name, retention_in_days, status
FROM dba_flashback_archive;
FLASHBACK_ARCHIVE_NA RETENTION_IN_DAYS STATUS
-------------------- ----------------- -------
FDA_2YEAR 730
FDA_1YEAR 365 DEFAULT
2 rows selected.
SQL>The %_FLASHBACK_ARCHIVE_TS view displays the tablespaces and quotas associated with each flashback archive.
COLUMN flashback_archive_name FORMAT A20
COLUMN quota_in_mb FORMAT A10
SELECT flashback_archive_name, tablespace_name, quota_in_mb
FROM dba_flashback_archive_ts;
FLASHBACK_ARCHIVE_NA TABLESPACE_NAME QUOTA_IN_M
-------------------- ------------------------------ ----------
FDA_2YEAR FDA_TS
FDA_1YEAR FDA_TS 10240
2 rows selected.
SQL>
The %_FLASHBACK_ARCHIVE_TABLES view displays tables associated with each flashback archive, along with the name of the table holding the historical information.
COLUMN table_name FORMAT A15
COLUMN owner_name FORMAT A15
COLUMN flashback_archive_name FORMAT A20
COLUMN archive_table_name FORMAT A20
SELECT table_name, owner_name, flashback_archive_name, archive_table_name
FROM dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME
--------------- --------------- -------------------- --------------------
TEST_TAB_1 FDA_TEST_USER FDA_1YEAR SYS_FBA_HIST_72023
1 row selected.
SQL>
miércoles, 4 de noviembre de 2009
Oracle 10g Copiar una base de datos Windows
Copiar una base de datos.
La petición textual era la creación de una base de datos igual que otra, pero con otro nombre. En algunos casos se trataba de replicar el entorno de producción para usarlo como test. En otros, simplemente, testear que el backup permitía recuperar la base de datos sobre otra máquina y dejar esa base de datos operativa como entorno auxiliar.
Cuando la base de datos puede conservar el mismo nombre y mismo SID, es tan fácil como copiar datafiles, redolog, controlfiles y archivos ORA sobre los mismos directorios originales en una máquina con un servidor Oracle instalado*.
* Por supuesto, misma plataforma O.S., versión y release de Oracle.
No obstante, en los casos en los que el nombre debe ser diferente, hay que ser muy preciso con los pasos a seguir. Incluyo dos pasos previos como opcionales por si son de utilidad.
En este ejemplo se asume que disponemos de la posibilidad de hacer un backup en frío de la base de datos.
Pasos preliminares (recomendado):
- Backup frío, backup caliente, export, Transport Tablespaces, backup del backup, backup de todo lo posible.
- Reducción de tamaño de los datafiles. (Tom Kyte tiene un fabuloso script para ello). Y otra vez backup.
Pasos a seguir:
1.- Backup en frío de la BBDD original
2.- Generación del pfile para la nueva BBDD
3.- Edición del pfile para sustituir: CONTROL_FILES, DB_NAME, y carpetas _DUMP_DEST,...
4.- Definir el nuevo ORACLE_SID
5.- Crear el nuevo servicio.
6.- Creación del fichero de passwords (sólo si el pfile tiene REMOTE_LOGIN_PASSWORDFILE=exclusive)
7.- Conexión a sqlplus como SYSDBA
8.- Arranque de la instancia y creación del fichero de parámetros SPFILE
9.- Backup del controlfile de la BBDD original a traza
10.- Recreación del controlfile con la cláusula SET NAME.
11.- Abrir la base de datos con modo OPEN RESETLOGS.
1.- Backup en frío de la BBDD original.
Restauración del backup sobre nueva ubicación.
-- Ejecutar el resultado de la siguiente sentencia como script.
-- NOTA: cuidado con los nombres duplicados de fichero.
-------------------------------------------------------------
select 'shutdown immediate;' from dual
union all
select 'host copy '||name||' &&directorio_destino' from v$controlfile
union all
select 'host copy '||member||' &directorio_destino' from v$logfile
union all
select 'host copy '||name||' &directorio_destino' from v$datafile
union all
select 'startup' from dual;
-- copia de los ficheros a los directorios destino
---------------------------------------------------
2.- Generación del pfile para la nueva base de datos
SQL> create pfile='?\admin\sid\pfile\inittest.ora' from spfile;
Archivo creado.
3.- Edición del pfile para sustituir: CONTROL_FILES, DB_NAME, y carpetas _DUMP_DEST,...
4.- Definir el nuevo ORACLE_SID
c:\>set ORACLE_SID=test
5.- Crear el nuevo servicio.
c:\>oradim -NEW -SRVC OracleServicetest -startmode auto
6.- Creación del fichero de passwords
c:\>orapwd file=C:\orant\ora92\database\PWDtest.ora password=xxxxxxxxx
7.- Conexión a sqlplus como SYSDBA
C:\>sqlplus
SQL*Plus: Release 10.2.0.4 - Production on Vie Jul 29 16:41:22 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Introduzca el nombre de usuario: sys/xxxxxxxxxx as sysdba
Conectado a una instancia inactiva.
8.- Arranque de la instancia y creación del fichero de parámetros SPFILE
SQL> STARTUP NOMOUNT PFILE='C:\DIRECTORIO_DESTINO\inittest.ora'
Instancia ORACLE iniciada.
Total System Global Area 1074866044 bytes
Fixed Size 456572 bytes
Variable Size 905969664 bytes
Database Buffers 167772160 bytes
Redo Buffers 667648 bytes
SQL> create spfile from pfile='C:\DIRECTORIO_DESTINO\inittest.ora';
Archivo creado.
9.- Backup del controlfile de PROD a trace.
SQL> alter database backup controlfile to trace;
Base de datos modificada.
10.- A partir de la traza del fichero de control.
Recreación del controlfile con el SET NAME al nuevo nombre.
CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG REUSE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 133
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 2 'C:\DATA\TEST\REDO\REDO02.LOG' SIZE 100M,
GROUP 3 'C:\DATA\TEST\REDO\REDO03.LOG' SIZE 100M,
GROUP 4 'C:\DATA\TEST\REDO\REDO04.LOG' SIZE 150M,
GROUP 5 'C:\DATA\TEST\REDO\REDO05.LOG' SIZE 150M,
GROUP 6 'C:\DATA\TEST\REDO\REDO06.LOG' SIZE 200M
-- STANDBY LOGFILE
DATAFILE
(... ficheros...)
'C:\DATA\TEST\SYSTEM\SYSTEM01.DBF',
'C:\DATA\TEST\DATA\DATA01.DBF'
CHARACTER SET WE8MSWIN1252
;
Archivo de control creado.
11.- Abrir la base de datos con modo OPEN RESETLOGS.
SQL> alter database open resetlogs;
Base de datos modificada.
12.- Verificación.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
test
SQL> select name from v$database;
NAME
---------
TEST
SQL> select status from v$thread;
STATUS
------
OPEN
12.- Shutdown y Open de la base de datos.
SQL> shutdown immediate;
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL> startup
Instancia ORACLE iniciada.
Total System Global Area 1074866044 bytes
Fixed Size 456572 bytes
Variable Size 905969664 bytes
Database Buffers 167772160 bytes
Redo Buffers 667648 bytes
Base de datos montada.
Base de datos abierta.
La petición textual era la creación de una base de datos igual que otra, pero con otro nombre. En algunos casos se trataba de replicar el entorno de producción para usarlo como test. En otros, simplemente, testear que el backup permitía recuperar la base de datos sobre otra máquina y dejar esa base de datos operativa como entorno auxiliar.
Cuando la base de datos puede conservar el mismo nombre y mismo SID, es tan fácil como copiar datafiles, redolog, controlfiles y archivos ORA sobre los mismos directorios originales en una máquina con un servidor Oracle instalado*.
* Por supuesto, misma plataforma O.S., versión y release de Oracle.
No obstante, en los casos en los que el nombre debe ser diferente, hay que ser muy preciso con los pasos a seguir. Incluyo dos pasos previos como opcionales por si son de utilidad.
En este ejemplo se asume que disponemos de la posibilidad de hacer un backup en frío de la base de datos.
Pasos preliminares (recomendado):
- Backup frío, backup caliente, export, Transport Tablespaces, backup del backup, backup de todo lo posible.
- Reducción de tamaño de los datafiles. (Tom Kyte tiene un fabuloso script para ello). Y otra vez backup.
Pasos a seguir:
1.- Backup en frío de la BBDD original
2.- Generación del pfile para la nueva BBDD
3.- Edición del pfile para sustituir: CONTROL_FILES, DB_NAME, y carpetas _DUMP_DEST,...
4.- Definir el nuevo ORACLE_SID
5.- Crear el nuevo servicio.
6.- Creación del fichero de passwords (sólo si el pfile tiene REMOTE_LOGIN_PASSWORDFILE=exclusive)
7.- Conexión a sqlplus como SYSDBA
8.- Arranque de la instancia y creación del fichero de parámetros SPFILE
9.- Backup del controlfile de la BBDD original a traza
10.- Recreación del controlfile con la cláusula SET NAME.
11.- Abrir la base de datos con modo OPEN RESETLOGS.
1.- Backup en frío de la BBDD original.
Restauración del backup sobre nueva ubicación.
-- Ejecutar el resultado de la siguiente sentencia como script.
-- NOTA: cuidado con los nombres duplicados de fichero.
-------------------------------------------------------------
select 'shutdown immediate;' from dual
union all
select 'host copy '||name||' &&directorio_destino' from v$controlfile
union all
select 'host copy '||member||' &directorio_destino' from v$logfile
union all
select 'host copy '||name||' &directorio_destino' from v$datafile
union all
select 'startup' from dual;
-- copia de los ficheros a los directorios destino
---------------------------------------------------
2.- Generación del pfile para la nueva base de datos
SQL> create pfile='?\admin\sid\pfile\inittest.ora' from spfile;
Archivo creado.
3.- Edición del pfile para sustituir: CONTROL_FILES, DB_NAME, y carpetas _DUMP_DEST,...
4.- Definir el nuevo ORACLE_SID
c:\>set ORACLE_SID=test
5.- Crear el nuevo servicio.
c:\>oradim -NEW -SRVC OracleServicetest -startmode auto
6.- Creación del fichero de passwords
c:\>orapwd file=C:\orant\ora92\database\PWDtest.ora password=xxxxxxxxx
7.- Conexión a sqlplus como SYSDBA
C:\>sqlplus
SQL*Plus: Release 10.2.0.4 - Production on Vie Jul 29 16:41:22 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Introduzca el nombre de usuario: sys/xxxxxxxxxx as sysdba
Conectado a una instancia inactiva.
8.- Arranque de la instancia y creación del fichero de parámetros SPFILE
SQL> STARTUP NOMOUNT PFILE='C:\DIRECTORIO_DESTINO\inittest.ora'
Instancia ORACLE iniciada.
Total System Global Area 1074866044 bytes
Fixed Size 456572 bytes
Variable Size 905969664 bytes
Database Buffers 167772160 bytes
Redo Buffers 667648 bytes
SQL> create spfile from pfile='C:\DIRECTORIO_DESTINO\inittest.ora';
Archivo creado.
9.- Backup del controlfile de PROD a trace.
SQL> alter database backup controlfile to trace;
Base de datos modificada.
10.- A partir de la traza del fichero de control.
Recreación del controlfile con el SET NAME al nuevo nombre.
CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG REUSE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 133
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 2 'C:\DATA\TEST\REDO\REDO02.LOG' SIZE 100M,
GROUP 3 'C:\DATA\TEST\REDO\REDO03.LOG' SIZE 100M,
GROUP 4 'C:\DATA\TEST\REDO\REDO04.LOG' SIZE 150M,
GROUP 5 'C:\DATA\TEST\REDO\REDO05.LOG' SIZE 150M,
GROUP 6 'C:\DATA\TEST\REDO\REDO06.LOG' SIZE 200M
-- STANDBY LOGFILE
DATAFILE
(... ficheros...)
'C:\DATA\TEST\SYSTEM\SYSTEM01.DBF',
'C:\DATA\TEST\DATA\DATA01.DBF'
CHARACTER SET WE8MSWIN1252
;
Archivo de control creado.
11.- Abrir la base de datos con modo OPEN RESETLOGS.
SQL> alter database open resetlogs;
Base de datos modificada.
12.- Verificación.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
test
SQL> select name from v$database;
NAME
---------
TEST
SQL> select status from v$thread;
STATUS
------
OPEN
12.- Shutdown y Open de la base de datos.
SQL> shutdown immediate;
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL> startup
Instancia ORACLE iniciada.
Total System Global Area 1074866044 bytes
Fixed Size 456572 bytes
Variable Size 905969664 bytes
Database Buffers 167772160 bytes
Redo Buffers 667648 bytes
Base de datos montada.
Base de datos abierta.
Oracle Uso de Logminer
EXAMPLE 1
alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss' ;
alter session set nls_language = american ;
set lines 4000
set trimspool on
set feed off
col session_info format a40 word_wrap
col sql_redo format a90 word_wrap
col sql_undo format a90 word_wrap
begin
sys.dbms_logmnr.add_logfile ('/SCEL/data14/archives/hagc/arch_1_189771.arc',sys.dbms_logmnr.new) ;
sys.dbms_logmnr.add_logfile ('/SCEL/data14/archives/hagc/arch_1_189772.arc',sys.dbms_logmnr.addfile) ;
sys.dbms_logmnr.add_logfile ('/SCEL/data14/archives/hagc/arch_1_189773.arc',sys.dbms_logmnr.addfile) ;
end ;
/
-- Para rastrear DMLs
--
exec sys.dbms_logmnr.start_logmnr (options => sys.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + sys.DBMS_LOGMNR.COMMITTED_DATA_ONLY -
+ SYS.DBMS_LOGMNR.PRINT_PRETTY_SQL );
--
-- Para rastrear DDLs
--
--exec sys.dbms_logmnr.start_logmnr (options => sys.DBMS_LOGMNR.DDL_DICT_TRACKING)
spool /tmp/invest.lst
set feed on
set pages 200
set lines 4000
set trimspool on
set term off
select
username,
timestamp,
session_info,
sql_redo ,
sql_undo ,
operation
from
v$logmnr_contents t
WHERE
upper (seg_owner) = 'SISCEL'
and upper (seg_name) = 'GA_SERVSUPLABO'
--or lower (seg_name) = 'bpd_planes'
-- upper (username) in ('DYRUIZGO')
-- UPPER (session_info) like '%ELIPROGS%'
--operation = 'DDL'
;
spool off
EXEC sys.DBMS_LOGMNR.END_LOGMNR
EXAMPLE 2
SQL> connect / as sysdba
Conectado.
SQL> show parameters utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string c:\oraclefiles
SQL> exec DBMS_LOGMNR_D.BUILD( DICTIONARY_FILENAME =>'dictionary.ora', DICTIONARY_LOCATION => 'c:\oraclefiles');
Procedimiento PL/SQL terminado correctamente.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\ORCL\REDO04A.LOG
C:\ORACLE\ORADATA\ORCL\REDO04B.LOG
C:\ORACLE\ORADATA\ORCL\REDO05A.LOG
C:\ORACLE\ORADATA\ORCL\REDO05B.LOG
C:\ORACLE\ORADATA\ORCL\REDO06A.LOG
C:\ORACLE\ORADATA\ORCL\REDO06B.LOG
6 filas seleccionadas.
SQL> exec DBMS_LOGMNR.add_logfile('C:\ORACLE\ORADATA\ORCL\REDO04A.LOG');
Procedimiento PL/SQL terminado correctamente.
SQL> exec DBMS_LOGMNR.add_logfile('C:\ORACLE\ORADATA\ORCL\REDO05A.LOG');
Procedimiento PL/SQL terminado correctamente.
SQL> exec DBMS_LOGMNR.add_logfile('C:\ORACLE\ORADATA\ORCL\REDO06A.LOG');
Procedimiento PL/SQL terminado correctamente.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>'c:\oraclefiles\dictionary.ora');
Procedimiento PL/SQL terminado correctamente.
SQL> set pages 100
SQL> set lines 120
SQL> column sql_redo format a50
SQL> column sql_undo format a50
SQL> select sql_redo, sql_undo from v$logmnr_contents where rownum<10;
SQL_REDO SQL_UNDO
-------------------------------------------------- --------------------------------------------------
alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss' ;
alter session set nls_language = american ;
set lines 4000
set trimspool on
set feed off
col session_info format a40 word_wrap
col sql_redo format a90 word_wrap
col sql_undo format a90 word_wrap
begin
sys.dbms_logmnr.add_logfile ('/SCEL/data14/archives/hagc/arch_1_189771.arc',sys.dbms_logmnr.new) ;
sys.dbms_logmnr.add_logfile ('/SCEL/data14/archives/hagc/arch_1_189772.arc',sys.dbms_logmnr.addfile) ;
sys.dbms_logmnr.add_logfile ('/SCEL/data14/archives/hagc/arch_1_189773.arc',sys.dbms_logmnr.addfile) ;
end ;
/
-- Para rastrear DMLs
--
exec sys.dbms_logmnr.start_logmnr (options => sys.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + sys.DBMS_LOGMNR.COMMITTED_DATA_ONLY -
+ SYS.DBMS_LOGMNR.PRINT_PRETTY_SQL );
--
-- Para rastrear DDLs
--
--exec sys.dbms_logmnr.start_logmnr (options => sys.DBMS_LOGMNR.DDL_DICT_TRACKING)
spool /tmp/invest.lst
set feed on
set pages 200
set lines 4000
set trimspool on
set term off
select
username,
timestamp,
session_info,
sql_redo ,
sql_undo ,
operation
from
v$logmnr_contents t
WHERE
upper (seg_owner) = 'SISCEL'
and upper (seg_name) = 'GA_SERVSUPLABO'
--or lower (seg_name) = 'bpd_planes'
-- upper (username) in ('DYRUIZGO')
-- UPPER (session_info) like '%ELIPROGS%'
--operation = 'DDL'
;
spool off
EXEC sys.DBMS_LOGMNR.END_LOGMNR
EXAMPLE 2
SQL> connect / as sysdba
Conectado.
SQL> show parameters utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string c:\oraclefiles
SQL> exec DBMS_LOGMNR_D.BUILD( DICTIONARY_FILENAME =>'dictionary.ora', DICTIONARY_LOCATION => 'c:\oraclefiles');
Procedimiento PL/SQL terminado correctamente.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\ORCL\REDO04A.LOG
C:\ORACLE\ORADATA\ORCL\REDO04B.LOG
C:\ORACLE\ORADATA\ORCL\REDO05A.LOG
C:\ORACLE\ORADATA\ORCL\REDO05B.LOG
C:\ORACLE\ORADATA\ORCL\REDO06A.LOG
C:\ORACLE\ORADATA\ORCL\REDO06B.LOG
6 filas seleccionadas.
SQL> exec DBMS_LOGMNR.add_logfile('C:\ORACLE\ORADATA\ORCL\REDO04A.LOG');
Procedimiento PL/SQL terminado correctamente.
SQL> exec DBMS_LOGMNR.add_logfile('C:\ORACLE\ORADATA\ORCL\REDO05A.LOG');
Procedimiento PL/SQL terminado correctamente.
SQL> exec DBMS_LOGMNR.add_logfile('C:\ORACLE\ORADATA\ORCL\REDO06A.LOG');
Procedimiento PL/SQL terminado correctamente.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>'c:\oraclefiles\dictionary.ora');
Procedimiento PL/SQL terminado correctamente.
SQL> set pages 100
SQL> set lines 120
SQL> column sql_redo format a50
SQL> column sql_undo format a50
SQL> select sql_redo, sql_undo from v$logmnr_contents where rownum<10;
SQL_REDO SQL_UNDO
-------------------------------------------------- --------------------------------------------------
ORACLE Usuario que Ejecuto Sentencia SQL a Partir del SQL_ID
En ocasiones queremos conocer, quién fue el que ejecutó una sentencia "x" en la base de datos y no sabemos como hacerlo.
Este es un ejemplo sencillo como averiguarlo.
Nota: Cada vez que se reinicia una instancia, se pierde la información almacenada en el shared pool, por tanto, si tienes un SQL_ID de días atrás y la base de datos se reinició, no te servirá de nada este procedimiento.
Tomemos por ejemplo un SQL_ID de una sentencia cualquiera, ejecutada en la base de datos. Esto se hace consultando la vista V_$SQLTEXT:
SQL> select sql_id, sql_text from sys.V_$SQLTEXT where rownum <>
SQL_ID SQL_TEXT
------------- -------------------------------------------------------
ay9t40xq6c00t ODEGA" = :2 AND "COD_UBICACION" = :3 AND "NO_CIA" = :1
Ahora consultando en la vista V_$SQLAREA, podemos obtener la fecha que se ejecutó por primera vez:
SQL> select USERS_OPENING, FIRST_LOAD_TIME, USERS_EXECUTING, SQL_ID, ELAPSED_TIME,
2 LAST_LOAD_TIME, PROGRAM_ID from sys.V_$SQLAREA
3 where sql_id='ay9t40xq6c00t';
USERS_OPENING FIRST_LOAD_TIME USERS_EXECUTING SQL_ID ELAPSED_TIME LAST_LOAD PROGRAM_ID
------------- ------------------- --------------- ------------- ------------ --------- ----------
0 2009-10-09/16:14:22 0 ay9t40xq6c00t 0 09-OCT-09 0
Modificando la consulta anterior, podemos obtener también de la misma vista, el id, del usuario que ejecutó la sentencia:
SQL> select FIRST_LOAD_TIME,SQL_ID,PARSING_USER_ID from sys.V_$SQLAREA
2 where sql_id='ay9t40xq6c00t';
FIRST_LOAD_TIME SQL_ID PARSING_USER_ID
------------------- ------------- ---------------
2009-10-09/16:14:22 ay9t40xq6c00t 175
Finalmente, consultamos en la tabla all_users, utilizando el ID del usuario y obtenemos el username del usuario que ejecutó la sentencia.
SQL> select username, user_id, created from dba_users where user_id='175';
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
LABORATORIO 175 06-APR-09
Este es un ejemplo sencillo como averiguarlo.
Nota: Cada vez que se reinicia una instancia, se pierde la información almacenada en el shared pool, por tanto, si tienes un SQL_ID de días atrás y la base de datos se reinició, no te servirá de nada este procedimiento.
Tomemos por ejemplo un SQL_ID de una sentencia cualquiera, ejecutada en la base de datos. Esto se hace consultando la vista V_$SQLTEXT:
SQL> select sql_id, sql_text from sys.V_$SQLTEXT where rownum <>
SQL_ID SQL_TEXT
------------- -------------------------------------------------------
ay9t40xq6c00t ODEGA" = :2 AND "COD_UBICACION" = :3 AND "NO_CIA" = :1
Ahora consultando en la vista V_$SQLAREA, podemos obtener la fecha que se ejecutó por primera vez:
SQL> select USERS_OPENING, FIRST_LOAD_TIME, USERS_EXECUTING, SQL_ID, ELAPSED_TIME,
2 LAST_LOAD_TIME, PROGRAM_ID from sys.V_$SQLAREA
3 where sql_id='ay9t40xq6c00t';
USERS_OPENING FIRST_LOAD_TIME USERS_EXECUTING SQL_ID ELAPSED_TIME LAST_LOAD PROGRAM_ID
------------- ------------------- --------------- ------------- ------------ --------- ----------
0 2009-10-09/16:14:22 0 ay9t40xq6c00t 0 09-OCT-09 0
Modificando la consulta anterior, podemos obtener también de la misma vista, el id, del usuario que ejecutó la sentencia:
SQL> select FIRST_LOAD_TIME,SQL_ID,PARSING_USER_ID from sys.V_$SQLAREA
2 where sql_id='ay9t40xq6c00t';
FIRST_LOAD_TIME SQL_ID PARSING_USER_ID
------------------- ------------- ---------------
2009-10-09/16:14:22 ay9t40xq6c00t 175
Finalmente, consultamos en la tabla all_users, utilizando el ID del usuario y obtenemos el username del usuario que ejecutó la sentencia.
SQL> select username, user_id, created from dba_users where user_id='175';
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
LABORATORIO 175 06-APR-09
ORACLE Recompiling Invalid Schema Objects
Recompiling Invalid Schema Objects
Operations such as upgrades, patches and DDL changes can invalidate schema objects. Provided these changes don't cause compilation failures the objects will be revalidated by on-demand automatic recompilation, but this can take an unacceptable time to complete, especially where complex dependencies are present. For this reason it makes sense to recompile invalid objects in advance of user calls. It also allows you to identify if any changes have broken your code base. This article presents several methods for recompiling invalid schema objects.
Identifying Invalid Objects
The Manual Approach
Custom Script
DBMS_UTILITY.compile_schema
UTL_RECOMP
utlrp.sql and utlprp.sql
Identifying Invalid Objects
The DBA_OBJECTS view can be used to identify invalid objects using the following query:
COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID';
ORDER BY owner, object_type, object_name;With this information you can decide which of the following recompilation methods is suitable for you.
The Manual ApproachFor small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types:
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;Notice that the package body is compiled in the same way as the package specification, with the addition of the word "BODY" at the end of the command.
An alternative approach is to use the DBMS_DDL package to perform the recompilations:
EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');This method is limited to PL/SQL objects, so it is not applicable for views.
Script Propio.set heading off
set feedback off
spool /export/home/oracle/kdbin/recompile.lst
SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE; ' FROM DBA_OBJECTS WHERE OBJECT_TYPE NOT IN ('PACKAGE BODY') AND OWNER = 'SPE' AND STATUS = 'IN
VALID';
spool off
@/export/home/oracle/kdbin/recompile.lst
spool /export/home/oracle/kdbin/recompile.lst
SELECT 'ALTER PACKAGE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY; ' FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('PACKAGE BODY') AND OWNER = 'SPE' AND STATUS = 'INVALID';
spool off
spool /export/home/oracle/kdbin/recompile.lst
SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE; ' FROM DBA_OBJECTS WHERE OBJECT_TYPE NOT IN ('PACKAGE BODY') AND STATUS = 'INVALID';
spool off
@/export/home/oracle/kdbin/recompile.lst
spool /export/home/oracle/kdbin/recompile.lst
SELECT 'ALTER PACKAGE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY; ' FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('PACKAGE BODY') AND STATUS = 'INVALID';
spool off
@/export/home/oracle/kdbin/recompile.lst
set heading on
set feedback on
exit
Custom Script
In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects. The following example identifies and recompiles invalid packages and package bodies.
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner,
object_name,
object_type,
DECODE(object_type, 'PACKAGE', 1,
'PACKAGE BODY', 2, 2) AS recompile_order
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
AND status != 'VALID'
ORDER BY 4)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
ElSE
EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
'"."' || cur_rec.object_name || '" COMPILE BODY';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
' : ' || cur_rec.object_name);
END;
END LOOP;
END;
/This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in. It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.
DBMS_UTILITY.compile_schema
The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus:
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');UTL_RECOMP
The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below:
PROCEDURE RECOMP_SERIAL(
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);
PROCEDURE RECOMP_PARALLEL(
threads IN PLS_INTEGER DEFAULT NULL,
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);The usage notes for the parameters are listed below:
schema - The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled.
threads - The number of threads used in a parallel operation. If NULL the value of the "job_queue_processes" parameter is used. Matching the number of available CPUs is generally a good starting point for this value.
flags - Used for internal diagnostics and testing only.
The following examples show how these procedures care used:
-- Schema level.
EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');
-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);
-- Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');There are a number of restrictions associated with the use of this package including:
Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.
The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.
Runnig DDL operations at the same time as this package may result in deadlocks.
utlrp.sql and utlprp.sql
The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:
0 - The level of parallelism is derived based on the CPU_COUNT parameter.
1 - The recompilation is run serially, one object at a time.
N - The recompilation is run in parallel with "N" number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.
For further information see:
DBMS_UTILITY.compile_schema
UTL_RECOMP
Operations such as upgrades, patches and DDL changes can invalidate schema objects. Provided these changes don't cause compilation failures the objects will be revalidated by on-demand automatic recompilation, but this can take an unacceptable time to complete, especially where complex dependencies are present. For this reason it makes sense to recompile invalid objects in advance of user calls. It also allows you to identify if any changes have broken your code base. This article presents several methods for recompiling invalid schema objects.
Identifying Invalid Objects
The Manual Approach
Custom Script
DBMS_UTILITY.compile_schema
UTL_RECOMP
utlrp.sql and utlprp.sql
Identifying Invalid Objects
The DBA_OBJECTS view can be used to identify invalid objects using the following query:
COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID';
ORDER BY owner, object_type, object_name;With this information you can decide which of the following recompilation methods is suitable for you.
The Manual ApproachFor small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types:
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;Notice that the package body is compiled in the same way as the package specification, with the addition of the word "BODY" at the end of the command.
An alternative approach is to use the DBMS_DDL package to perform the recompilations:
EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');This method is limited to PL/SQL objects, so it is not applicable for views.
Script Propio.set heading off
set feedback off
spool /export/home/oracle/kdbin/recompile.lst
SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE; ' FROM DBA_OBJECTS WHERE OBJECT_TYPE NOT IN ('PACKAGE BODY') AND OWNER = 'SPE' AND STATUS = 'IN
VALID';
spool off
@/export/home/oracle/kdbin/recompile.lst
spool /export/home/oracle/kdbin/recompile.lst
SELECT 'ALTER PACKAGE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY; ' FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('PACKAGE BODY') AND OWNER = 'SPE' AND STATUS = 'INVALID';
spool off
spool /export/home/oracle/kdbin/recompile.lst
SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE; ' FROM DBA_OBJECTS WHERE OBJECT_TYPE NOT IN ('PACKAGE BODY') AND STATUS = 'INVALID';
spool off
@/export/home/oracle/kdbin/recompile.lst
spool /export/home/oracle/kdbin/recompile.lst
SELECT 'ALTER PACKAGE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY; ' FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('PACKAGE BODY') AND STATUS = 'INVALID';
spool off
@/export/home/oracle/kdbin/recompile.lst
set heading on
set feedback on
exit
Custom Script
In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects. The following example identifies and recompiles invalid packages and package bodies.
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner,
object_name,
object_type,
DECODE(object_type, 'PACKAGE', 1,
'PACKAGE BODY', 2, 2) AS recompile_order
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
AND status != 'VALID'
ORDER BY 4)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
ElSE
EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
'"."' || cur_rec.object_name || '" COMPILE BODY';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
' : ' || cur_rec.object_name);
END;
END LOOP;
END;
/This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in. It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.
DBMS_UTILITY.compile_schema
The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus:
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');UTL_RECOMP
The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below:
PROCEDURE RECOMP_SERIAL(
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);
PROCEDURE RECOMP_PARALLEL(
threads IN PLS_INTEGER DEFAULT NULL,
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);The usage notes for the parameters are listed below:
schema - The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled.
threads - The number of threads used in a parallel operation. If NULL the value of the "job_queue_processes" parameter is used. Matching the number of available CPUs is generally a good starting point for this value.
flags - Used for internal diagnostics and testing only.
The following examples show how these procedures care used:
-- Schema level.
EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');
-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);
-- Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');There are a number of restrictions associated with the use of this package including:
Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.
The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.
Runnig DDL operations at the same time as this package may result in deadlocks.
utlrp.sql and utlprp.sql
The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:
0 - The level of parallelism is derived based on the CPU_COUNT parameter.
1 - The recompilation is run serially, one object at a time.
N - The recompilation is run in parallel with "N" number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.
For further information see:
DBMS_UTILITY.compile_schema
UTL_RECOMP
ORACLE Renaming or Moving Oracle Files
Renaming or Moving Oracle Files
Controlfiles
Logfiles
Datafiles
Recreating the Controlfile
Controlfiles
The current location of the controlfiles can be queried from the V$CONTROLFILE view, as shown below.
SQL> select name from v$controlfile;
NAME
-------------------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL01.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL02.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL03.CTL
3 rows selected.
SQL>In order to rename or move these files we must alter the value of the control_files instance parameter.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
control_files string C:\ORACLE\ORADATA\DB10G\CONTRO
L01.CTL, C:\ORACLE\ORADATA\DB1
0G\CONTROL02.CTL, C:\ORACLE\OR
ADATA\DB10G\CONTROL03.CTL
SQL>To move or rename a controlfile do the following:
Alter the control_files parameter using the ALTER SYSTEM comamnd.
Shutdown the database.
Rename the physical file on the OS.
Start the database.
The following SQL*Plus output shows how this is done for an instance using an spfile. For instances using a pfile replace the spfile manipulation steps with an amendment of the parameter in the init.ora file.
SQL> ALTER SYSTEM SET control_files='C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_CONTROL01.CTL', -
> 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL02.CTL', -
> 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL03.CTL' SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> HOST MOVE C:\ORACLE\ORADATA\DB10G\CONTROL01.CTL C:\ORACLE\ORADATA\DB10G\RENAME_CONTROL01.CTL
SQL> STARTUP
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 787968 bytes
Variable Size 61864448 bytes
Database Buffers 104857600 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL>Repeating the initial query shows that the the controlfile has been renamed in the data dictionary.
SQL> select name from v$controlfile;
NAME
-------------------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_CONTROL01.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL02.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL03.CTL
3 rows selected.
SQL>Logfiles
The current location of the logfiles can be queried from the V$LOGFILE view, as shown below.
SQL> SELECT member FROM v$logfile;
MEMBER
-------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO03.LOG
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO02.LOG
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO01.LOG
3 rows selected.
SQL>To move or rename a logfile do the following:
Shutdown the database.
Rename the physical file on the OS.
Start the database in mount mode.
Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
Open the database.
The following SQL*Plus output shows how this is done.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> HOST MOVE C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO01.LOG C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_REDO01.LOG
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 787968 bytes
Variable Size 61864448 bytes
Database Buffers 104857600 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO01.LOG' -
> TO 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_REDO01.LOG';
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL>Repeating the initial query shows that the the logfile has been renamed in the data dictionary.
SQL> SELECT member FROM v$logfile;
MEMBER
-------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO03.LOG
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO02.LOG
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_REDO01.LOG
3 rows selected.
SQL>Datafiles
The process for renaming a datafile is the same as renaming a logfile, but for the same of clarity it is repeated below. The current location of the datafiles can be queried from the V$DATAFILE view, as shown below.
SQL> SELECT name FROM v$datafile;
NAME
---------------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\USERS01.DBF
4 rows selected.
SQL>To move or rename a datafile do the following:
Shutdown the database.
Rename the physical file on the OS.
Start the database in mount mode.
Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
Open the database.
The following SQL*Plus output shows how this is done.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> HOST MOVE C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\USERS01.DBF C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_USERS01.DBF
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 787968 bytes
Variable Size 61864448 bytes
Database Buffers 104857600 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\USERS01.DBF' -
> TO 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_USERS01.DBF';
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL>Repeating the initial query shows that the the datafile has been renamed in the data dictionary.
SQL> SELECT name FROM v$datafile;
NAME
---------------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_USERS01.DBF
4 rows selected.
SQL>Recreating the Controlfile
For largescale rearrangements it may be easier to manipulate the controlfile contents manually by backing up the controlfile to trace.
SQL> CONN sys/password AS SYSDBA
Connected.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Database altered.
SQL>The resulting trace file in the user_dump_dest directory contains commands and instructions for recreating the controlfile. The paths in the CREATE CONTROLFILE command can be manipulated to rename all datafiles and logfiles on one step.
Controlfiles
Logfiles
Datafiles
Recreating the Controlfile
Controlfiles
The current location of the controlfiles can be queried from the V$CONTROLFILE view, as shown below.
SQL> select name from v$controlfile;
NAME
-------------------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL01.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL02.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL03.CTL
3 rows selected.
SQL>In order to rename or move these files we must alter the value of the control_files instance parameter.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
control_files string C:\ORACLE\ORADATA\DB10G\CONTRO
L01.CTL, C:\ORACLE\ORADATA\DB1
0G\CONTROL02.CTL, C:\ORACLE\OR
ADATA\DB10G\CONTROL03.CTL
SQL>To move or rename a controlfile do the following:
Alter the control_files parameter using the ALTER SYSTEM comamnd.
Shutdown the database.
Rename the physical file on the OS.
Start the database.
The following SQL*Plus output shows how this is done for an instance using an spfile. For instances using a pfile replace the spfile manipulation steps with an amendment of the parameter in the init.ora file.
SQL> ALTER SYSTEM SET control_files='C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_CONTROL01.CTL', -
> 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL02.CTL', -
> 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL03.CTL' SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> HOST MOVE C:\ORACLE\ORADATA\DB10G\CONTROL01.CTL C:\ORACLE\ORADATA\DB10G\RENAME_CONTROL01.CTL
SQL> STARTUP
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 787968 bytes
Variable Size 61864448 bytes
Database Buffers 104857600 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL>Repeating the initial query shows that the the controlfile has been renamed in the data dictionary.
SQL> select name from v$controlfile;
NAME
-------------------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_CONTROL01.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL02.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL03.CTL
3 rows selected.
SQL>Logfiles
The current location of the logfiles can be queried from the V$LOGFILE view, as shown below.
SQL> SELECT member FROM v$logfile;
MEMBER
-------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO03.LOG
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO02.LOG
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO01.LOG
3 rows selected.
SQL>To move or rename a logfile do the following:
Shutdown the database.
Rename the physical file on the OS.
Start the database in mount mode.
Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
Open the database.
The following SQL*Plus output shows how this is done.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> HOST MOVE C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO01.LOG C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_REDO01.LOG
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 787968 bytes
Variable Size 61864448 bytes
Database Buffers 104857600 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO01.LOG' -
> TO 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_REDO01.LOG';
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL>Repeating the initial query shows that the the logfile has been renamed in the data dictionary.
SQL> SELECT member FROM v$logfile;
MEMBER
-------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO03.LOG
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\REDO02.LOG
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_REDO01.LOG
3 rows selected.
SQL>Datafiles
The process for renaming a datafile is the same as renaming a logfile, but for the same of clarity it is repeated below. The current location of the datafiles can be queried from the V$DATAFILE view, as shown below.
SQL> SELECT name FROM v$datafile;
NAME
---------------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\USERS01.DBF
4 rows selected.
SQL>To move or rename a datafile do the following:
Shutdown the database.
Rename the physical file on the OS.
Start the database in mount mode.
Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
Open the database.
The following SQL*Plus output shows how this is done.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> HOST MOVE C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\USERS01.DBF C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_USERS01.DBF
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 787968 bytes
Variable Size 61864448 bytes
Database Buffers 104857600 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\USERS01.DBF' -
> TO 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_USERS01.DBF';
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL>Repeating the initial query shows that the the datafile has been renamed in the data dictionary.
SQL> SELECT name FROM v$datafile;
NAME
---------------------------------------------------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\RENAME_USERS01.DBF
4 rows selected.
SQL>Recreating the Controlfile
For largescale rearrangements it may be easier to manipulate the controlfile contents manually by backing up the controlfile to trace.
SQL> CONN sys/password AS SYSDBA
Connected.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Database altered.
SQL>The resulting trace file in the user_dump_dest directory contains commands and instructions for recreating the controlfile. The paths in the CREATE CONTROLFILE command can be manipulated to rename all datafiles and logfiles on one step.
jueves, 29 de octubre de 2009
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 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>
lunes, 19 de octubre de 2009
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';
■ 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/
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';
jueves, 15 de octubre de 2009
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;
}
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';
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;
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;
lunes, 5 de octubre de 2009
Unix Comandos Examples SED
Ejemplo SED cuando se tiene formato fecha
sed s/'24\/Jun'/'23\/Sep'/g hola.txt > prueba
sed s/'24\/Jun'/'23\/Sep'/g hola.txt > prueba
sed
s/’#PermitRoot’/’PermitRoot’/g sshd_config > sshd_test
viernes, 2 de octubre de 2009
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
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
Suscribirse a:
Entradas (Atom)
