Removes archive logs from archive log destination.
rman
connect target /
delete noprompt archivelog until time 'sysdate -1' ;
crosscheck archivelog all ;
-- Will clean up logs deleted from command line
delete noprompt expired archivelog all ;
Showing posts with label Rman. Show all posts
Showing posts with label Rman. Show all posts
Migrate to new rman catalog
[rmancat@milrmnvip01] /oracle>: sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 4 11:17:37 2009
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> drop user rman cascade;
User dropped.
SQL> drop user rmancat cascade;
User dropped.
SQL> drop user rcvcat cascade;
User dropped.
SQL> create user RMANCAT IDENTIFIED BY rmancatpassword DEFAULT TABLESPACE rman QUOTA UNLIMITED ON rman;
User created.
SQL> grant connect,resource to rmancat;
Grant succeeded.
SQL> grant recovery_catalog_owner to rmancat;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64 bit Production
[rmancat@milrmnvip01] /oracle>: impdp system directory=DATAPUMPDIR DUMPFILE=rmanschema.exp LOGFILE=rmanschema.imp.log TABLE_EXISTS_ACTION=replace SCHEMAS=rmanca t
Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 04 June, 2009 11:26:47
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Password:
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
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=DATAPUMPDIR DUMPFILE=rmanschema.exp LOGFILE=rmanschema.imp.log TABLE_EXISTS_ACTION=replace SCHEMAS=rmancat
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"RMANCAT" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "RMANCAT"."ROUT" 14.24 MB 150132 rows
. . imported "RMANCAT"."BRL" 5.440 MB 66707 rows
. . imported "RMANCAT"."RLH" 4.393 MB 78403 rows
. . imported "RMANCAT"."BDF" 3.815 MB 36827 rows
. . imported "RMANCAT"."BP" 2.359 MB 11405 rows
. . imported "RMANCAT"."BS" 857.8 KB 11407 rows
. . imported "RMANCAT"."RSR" 380.5 KB 2631 rows
. . imported "RMANCAT"."AL" 80.5 KB 349 rows
. . imported "RMANCAT"."CCF" 48.06 KB 267 rows
. . imported "RMANCAT"."DFATT" 53.77 KB 571 rows
. . imported "RMANCAT"."BCF" 27.37 KB 263 rows
. . imported "RMANCAT"."CKP" 18.28 KB 160 rows
. . imported "RMANCAT"."CONF" 6.593 KB 3 rows
. . imported "RMANCAT"."CONFIG" 5.234 KB 1 rows
. . imported "RMANCAT"."DB" 6.476 KB 1 rows
. . imported "RMANCAT"."DBINC" 14.62 KB 3 rows
. . imported "RMANCAT"."DF" 22.06 KB 265 rows
. . imported "RMANCAT"."NODE" 6.828 KB 1 rows
. . imported "RMANCAT"."OFFR" 15.34 KB 120 rows
. . imported "RMANCAT"."ORL" 6.835 KB 16 rows
. . imported "RMANCAT"."RCVER" 4.937 KB 2 rows
. . imported "RMANCAT"."RT" 7.187 KB 4 rows
. . imported "RMANCAT"."TF" 9.429 KB 39 rows
. . imported "RMANCAT"."TFATT" 10.77 KB 39 rows
. . imported "RMANCAT"."TS" 10.67 KB 58 rows
. . imported "RMANCAT"."TSATT" 8.664 KB 87 rows
. . imported "RMANCAT"."BCB" 0 KB 0 rows
. . imported "RMANCAT"."BSF" 0 KB 0 rows
. . imported "RMANCAT"."CCB" 0 KB 0 rows
. . imported "RMANCAT"."CDF" 0 KB 0 rows
. . imported "RMANCAT"."FB" 0 KB 0 rows
. . imported "RMANCAT"."GRSP" 0 KB 0 rows
. . imported "RMANCAT"."RR" 0 KB 0 rows
. . imported "RMANCAT"."SCR" 0 KB 0 rows
. . imported "RMANCAT"."SCRL" 0 KB 0 rows
. . imported "RMANCAT"."XAL" 0 KB 0 rows
. . imported "RMANCAT"."XCF" 0 KB 0 rows
. . imported "RMANCAT"."XDF" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 11:31:17
[rmancat@milrmnvip01] /oracle>: sqlplus rmancat/rmancatpassword
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 4 11:32:31 2009
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 rc_database;
DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE#
---------- ---------- ---------- -------- -----------------
RESETLOGS_TIME
--------------------------
72579 72580 1722235645 BIPRDDB 500966
27-JUN-2006 20:28:50
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
[rmancat@milrmnvip01] /oracle>: rman catalog rmancat/rmancatpassword
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Jun 4 11:33:22 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> upgrade catalog;
recovery catalog owner is RMANCAT
enter UPGRADE CATALOG command again to confirm catalog upgrade
RMAN> upgrade catalog;
recovery catalog upgraded to version 10.02.00.04
DBMS_RCVMAN package upgraded to version 10.02.00.04
DBMS_RCVCAT package upgraded to version 10.02.00.04
RMAN>
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 4 11:17:37 2009
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> drop user rman cascade;
User dropped.
SQL> drop user rmancat cascade;
User dropped.
SQL> drop user rcvcat cascade;
User dropped.
SQL> create user RMANCAT IDENTIFIED BY rmancatpassword DEFAULT TABLESPACE rman QUOTA UNLIMITED ON rman;
User created.
SQL> grant connect,resource to rmancat;
Grant succeeded.
SQL> grant recovery_catalog_owner to rmancat;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64 bit Production
[rmancat@milrmnvip01] /oracle>: impdp system directory=DATAPUMPDIR DUMPFILE=rmanschema.exp LOGFILE=rmanschema.imp.log TABLE_EXISTS_ACTION=replace SCHEMAS=rmanca t
Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 04 June, 2009 11:26:47
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Password:
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
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=DATAPUMPDIR DUMPFILE=rmanschema.exp LOGFILE=rmanschema.imp.log TABLE_EXISTS_ACTION=replace SCHEMAS=rmancat
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"RMANCAT" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "RMANCAT"."ROUT" 14.24 MB 150132 rows
. . imported "RMANCAT"."BRL" 5.440 MB 66707 rows
. . imported "RMANCAT"."RLH" 4.393 MB 78403 rows
. . imported "RMANCAT"."BDF" 3.815 MB 36827 rows
. . imported "RMANCAT"."BP" 2.359 MB 11405 rows
. . imported "RMANCAT"."BS" 857.8 KB 11407 rows
. . imported "RMANCAT"."RSR" 380.5 KB 2631 rows
. . imported "RMANCAT"."AL" 80.5 KB 349 rows
. . imported "RMANCAT"."CCF" 48.06 KB 267 rows
. . imported "RMANCAT"."DFATT" 53.77 KB 571 rows
. . imported "RMANCAT"."BCF" 27.37 KB 263 rows
. . imported "RMANCAT"."CKP" 18.28 KB 160 rows
. . imported "RMANCAT"."CONF" 6.593 KB 3 rows
. . imported "RMANCAT"."CONFIG" 5.234 KB 1 rows
. . imported "RMANCAT"."DB" 6.476 KB 1 rows
. . imported "RMANCAT"."DBINC" 14.62 KB 3 rows
. . imported "RMANCAT"."DF" 22.06 KB 265 rows
. . imported "RMANCAT"."NODE" 6.828 KB 1 rows
. . imported "RMANCAT"."OFFR" 15.34 KB 120 rows
. . imported "RMANCAT"."ORL" 6.835 KB 16 rows
. . imported "RMANCAT"."RCVER" 4.937 KB 2 rows
. . imported "RMANCAT"."RT" 7.187 KB 4 rows
. . imported "RMANCAT"."TF" 9.429 KB 39 rows
. . imported "RMANCAT"."TFATT" 10.77 KB 39 rows
. . imported "RMANCAT"."TS" 10.67 KB 58 rows
. . imported "RMANCAT"."TSATT" 8.664 KB 87 rows
. . imported "RMANCAT"."BCB" 0 KB 0 rows
. . imported "RMANCAT"."BSF" 0 KB 0 rows
. . imported "RMANCAT"."CCB" 0 KB 0 rows
. . imported "RMANCAT"."CDF" 0 KB 0 rows
. . imported "RMANCAT"."FB" 0 KB 0 rows
. . imported "RMANCAT"."GRSP" 0 KB 0 rows
. . imported "RMANCAT"."RR" 0 KB 0 rows
. . imported "RMANCAT"."SCR" 0 KB 0 rows
. . imported "RMANCAT"."SCRL" 0 KB 0 rows
. . imported "RMANCAT"."XAL" 0 KB 0 rows
. . imported "RMANCAT"."XCF" 0 KB 0 rows
. . imported "RMANCAT"."XDF" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 11:31:17
[rmancat@milrmnvip01] /oracle>: sqlplus rmancat/rmancatpassword
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 4 11:32:31 2009
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 rc_database;
DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE#
---------- ---------- ---------- -------- -----------------
RESETLOGS_TIME
--------------------------
72579 72580 1722235645 BIPRDDB 500966
27-JUN-2006 20:28:50
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
[rmancat@milrmnvip01] /oracle>: rman catalog rmancat/rmancatpassword
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Jun 4 11:33:22 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> upgrade catalog;
recovery catalog owner is RMANCAT
enter UPGRADE CATALOG command again to confirm catalog upgrade
RMAN> upgrade catalog;
recovery catalog upgraded to version 10.02.00.04
DBMS_RCVMAN package upgraded to version 10.02.00.04
DBMS_RCVCAT package upgraded to version 10.02.00.04
RMAN>
Set DBID so views work in sqlplus
CALL DBMS_RCVMAN.SETDATABASE(null,
null,
null,
2283997583,
null);
The fourth parameter must be the DBID of a database registered in the recovery catalog. The other parameters must all be NULL.
Calculate the progress of an RMAN job
To calculate the progress of an RMAN job, run the following query in SQL*Plus while the RMAN job is executing:
SELECT SID,
SERIAL#,
CONTEXT,
SOFAR,
TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;
Terminate an RMAN Session
Terminating an RMAN Command
There are several ways to terminate an RMAN command in the middle of execution:
*
The preferred method is to press CTRL+C (or the equivalent "attention" key combination for your system) in the RMAN interface. This will also terminates allocated channels, unless they are hung in the media management code, as happens when, for example, when they are waiting for a tape to be mounted.
*
You can kill the server session corresponding to the RMAN channel by running the SQL ALTER SYSTEM KILL SESSION statement.
*
You can terminate the server session corresponding to the RMAN channel on the operating system.
Terminating the Session with ALTER SYSTEM KILL SESSION
You can identify the Oracle session ID for an RMAN channel by looking in the RMAN log for messages with the format shown in the following example:
channel ch1: sid=15 devtype=SBT_TAPE
The sid and devtype are displayed for each allocated channel. Note that the Oracle sid is different from the operating system process ID. You can kill the session using a SQL ALTER SYSTEM KILL SESSION statement.
ALTER SYSTEM KILL SESSION takes two arguments, the sid printed in the RMAN message and a serial number, both of which can be obtained by querying V$SESSION. For example, run the following statement, where sid_in_rman_output is the number from the RMAN message:
SELECT SERIAL# FROM V$SESSION WHERE SID=sid_in_rman_output;
Then, run the following statement, substituting the sid_in_rman_output and serial number obtained from the query:
ALTER SYSTEM KILL SESSION 'sid_in_rman_output,serial#';
Note that this will not unhang the session if the session is hung in media manager code..
Terminating the Session at the Operating System Level
Finding and killing the processes that are associated with the server sessions is operating system specific. On some platforms the server sessions are not associated with any processes at all. Refer to your operating system specific documentation for more information.
Terminating an RMAN Session That Is Hung in the Media Manager
You may sometimes need to kill an RMAN job that is hung in the media manager. The best way to terminate RMAN when the channel connections are hung in the media manager is to kill the session in the media manager. If this action does not solve the problem, then on some platforms, such as Unix, you may be able to kill the Oracle processes of the connections. (Note that killing the Oracle processes may cause problems from the media manager. See your media manager documentation for details.)
There are several ways to terminate an RMAN command in the middle of execution:
*
The preferred method is to press CTRL+C (or the equivalent "attention" key combination for your system) in the RMAN interface. This will also terminates allocated channels, unless they are hung in the media management code, as happens when, for example, when they are waiting for a tape to be mounted.
*
You can kill the server session corresponding to the RMAN channel by running the SQL ALTER SYSTEM KILL SESSION statement.
*
You can terminate the server session corresponding to the RMAN channel on the operating system.
Terminating the Session with ALTER SYSTEM KILL SESSION
You can identify the Oracle session ID for an RMAN channel by looking in the RMAN log for messages with the format shown in the following example:
channel ch1: sid=15 devtype=SBT_TAPE
The sid and devtype are displayed for each allocated channel. Note that the Oracle sid is different from the operating system process ID. You can kill the session using a SQL ALTER SYSTEM KILL SESSION statement.
ALTER SYSTEM KILL SESSION takes two arguments, the sid printed in the RMAN message and a serial number, both of which can be obtained by querying V$SESSION. For example, run the following statement, where sid_in_rman_output is the number from the RMAN message:
SELECT SERIAL# FROM V$SESSION WHERE SID=sid_in_rman_output;
Then, run the following statement, substituting the sid_in_rman_output and serial number obtained from the query:
ALTER SYSTEM KILL SESSION 'sid_in_rman_output,serial#';
Note that this will not unhang the session if the session is hung in media manager code..
Terminating the Session at the Operating System Level
Finding and killing the processes that are associated with the server sessions is operating system specific. On some platforms the server sessions are not associated with any processes at all. Refer to your operating system specific documentation for more information.
Terminating an RMAN Session That Is Hung in the Media Manager
You may sometimes need to kill an RMAN job that is hung in the media manager. The best way to terminate RMAN when the channel connections are hung in the media manager is to kill the session in the media manager. If this action does not solve the problem, then on some platforms, such as Unix, you may be able to kill the Oracle processes of the connections. (Note that killing the Oracle processes may cause problems from the media manager. See your media manager documentation for details.)
Subscribe to:
Posts (Atom)