Here is a quick way to find the current timestamp from the current SCN.
SQL> select to_char(CURRENT_SCN) from v$database;
TO_CHAR(CURRENT_SCN)
—————————————-
48133107062
SQL> select scn_to_timestamp(48133107062) from dual;
SCN_TO_TIMESTAMP(48133107062)
—————————————————————————
17-MAY-10 08.41.47.000000000 AM
SQL>
RECYLE BIN
USER_RECYCLEBIN – This view has dropped objects in current user’s schema
Clean current user’s recycle bin
SQL> purge recyclebin;
Recyclebin purged.
DBA_RECYCLEBIN – Has dropped objects in all users
# purge all tables in the recycle bin
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
How to create cold backup using RMAN?
Using the steps below one take cold backup using RMAN. As it’s a cold backup the database as the database is in mount stage the database doesn’t have to be archivelog so it can taken for a database which is in noarchivelog mode too.Step 1) Shutdown database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Step 2) Start database in mount stage
SQL> startup mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019320 bytes
Variable Size 75497480 bytes
Database Buffers 88080384 bytes
Redo Buffers 2174976 bytes
Step 3) Run rman and connect to target database and run rman to backup database and connection to catalog if you are using one
$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.1.0 – Production on Fri Apr 23 02:33:38 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST (DBID=1992878807, not open)
RMAN> backup database;
Starting backup at 23-APR-10
..
using target database control file instead of recovery catalog
…
How to find RMAN catalog version?
SQL> select * from rcver;
VERSION
————
10.02.00.00
SQL> select object_type from user_objects where object_name = ‘RCVER’;
OBJECT_TYPE
——————-
TABLE
SQL> desc rcver;
Name Null? Type
—————————————– ——– —————————-
VERSION NOT NULL VARCHAR2(12)
ShareThis
Steps to create rman catalog and register DB
April 22nd, 2010 1 comment
1) Create tablespace for example name catalog_tbs2) Create user for example user rman_catalog and assign catalog_tbs as default tablespace
3) Grant user rman_catalog the following roles: connect, resource, recovery_catalog_owner.
4) Connect to catalog through RMAN
5) Create recovery catalog
$ rman catalog=rman_catalog/password
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> create catalog tablespace catalog_tbs;
recovery catalog created
6) Next register database with the catalog
$ rman target=sys/syspassword catalog=rman_catlog/password
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Note: One can register multiple databases with different versions in the same catalog but the catalog version is the highest version of the databases registered so if you upgrade a database the catalog will need to be upgraded.
To upgrade the catalog, it is included in the steps of upgrade how to upgrade the catalog, below shows how to upgrade the catalog.
RMAN> upgrade catalog;
recovery catalog owner is RMAN_CAT
enter UPGRADE CATALOG command again to confirm catalog upgrade
RMAN> upgrade catalog;
recovery catalog upgraded to version 10.02.00.00
DBMS_RCVMAN package upgraded to version 10.02.00.00
DBMS_RCVCAT package upgraded to version 10.02.00.00
How to change/clear retention policy for RMAN?
The example below shows the difference between the two.
——— Display retention policy and shows using the option CLEAR ———-
RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
RMAN> configure retention policy to redundancy 3;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
new RMAN configuration parameters are successfully stored
RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
RMAN> configure retention policy clear;
old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
RMAN configuration parameters are successfully reset to default value
RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
——— Display retention policy and shows using the option NONE ———-
RMAN> show retention policy;
RMAN configuration parameters are:
RMAN> configure retention policy to none;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO NONE;
new RMAN configuration parameters are successfully stored
RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO NONE;
RMAN> configure retention policy clear;
old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO NONE;
RMAN configuration parameters are successfully reset to default value
RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
ShareThis
How to create backup of control file?
SQL> alter database backup controlfile to ‘/tmp/x.ctl’;
Database altered.
– backup control file in readable format in /tmp/x.trc, the script created doesn’t contain information about backups so if you backup in the binary format it will keep the backup information
SQL> alter database backup controlfile to trace as ‘/tmp/x.trc’;
Database altered.
control_file_record_keep_time
snapshot controlfile and RMAN-08512
$ rman target /
– display parameter value
RMAN> show all;
..
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/10.2.0.4/TESTDB/snapcf_TESTDB.f'; # default
– set to new path for controlfile snapshot
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/TESTDB/snapcf_TESTDB.f';
When multiple RMAN are running one could run into an issue RMAN-08512 where RMAN is waiting for getting a lock on snapshot controlfile header. To find the session run the following SQL.
SELECT vs.sid, vs.username, vs.program, vs.module, TO_CHAR(vs.logon_time, 'DD-MON-YYYY HH24:MI:SS')
FROM v$session vs, v$enqueue_lock vel
WHERE vs.sid = vel.sid AND vel.type = 'CF' AND vel.id1 = 0 AND vel.id2 = 2
ShareThis
How to use DBMS_FLASHBACK?
In 10g using dbms_flashback one can flashback using timestamp or SCN. If a user accidentally deletes rows or updates rows one can use this to review the data without need to have flashback turned on in the database. Note: SYS can’t use dbms_flashback to flashback, Oracle will return “ORA-08185: Flashback not supported for user SYS” when SYS will try to enable flashback.Example:
– shows flashback is off
SQL> select flashback_on from V$database;
FLASHBACK_ON
——————
NO
– declare a variable to store current SCN
SQL> variable current_scn number
– Get the current SCN and initialize current_scn
SQL> execute :current_scn := sys.dbms_flashback.get_system_change_number();
PL/SQL procedure successfully completed.
– print the current scn
SQL> print current_scn;
CURRENT_SCN
———–
389907
– query the table, shows no rows
SQL> select * FROM test;
no rows selected
– insert the row and commit
SQL> insert into test values ( 1 );
1 row created.
SQL> commit;
Commit complete.
– flashback to before insert was executed, so select should return no rows
SQL> execute dbms_flashback.enable_at_system_change_number(:current_scn);
PL/SQL procedure successfully completed.
– as expected select returned no rows
SQL> select * FROM test;
no rows selected
– disable flashback
SQL> execute dbms_flashback.disable;
PL/SQL procedure successfully completed.
excellent..............
ReplyDelete