Create Restore point On Oracle Standard Edition (no Flashback technology) and recovery .. ..
Take Oracle backup via rman ..
RMAN> RUN
{
configure controlfile autobackup on;
set command id to 'ORCLEEBackupFull';
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET full database tag ORCLEE_FULL format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_FULL' ;
sql 'alter system archive log current';
backup tag ORCLEE_ARCHIVE format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;
backup tag ORCLEE_CONTROL current controlfile format '/u01/app/oracle/RmanBackup/%d_%T_%s_%p_CONTROL';
release channel c1;
}
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
executing command: SET COMMAND ID
allocated channel: c1
channel c1: SID=34 device type=DISK
Starting backup at 06-JAN-17
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/oraee/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/oraee/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/oraee/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/oraee/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/oraee/users01.dbf
channel c1: starting piece 1 at 06-JAN-17
channel c1: finished piece 1 at 06-JAN-17
piece handle=/u01/app/oracle/RmanBackup/ORAEE_20170106_8_1_FULL tag=ORCLEE_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:01:45
Finished backup at 06-JAN-17
Starting Control File and SPFILE Autobackup at 06-JAN-17
piece handle=/u01/app/oracle/flash_recovery_area/ORAEE/autobackup/2017_01_06/o1_mf_s_932562080_d6z59152_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-JAN-17
sql statement: alter system archive log current
Starting backup at 06-JAN-17
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=4 STAMP=932562082
channel c1: starting piece 1 at 06-JAN-17
channel c1: finished piece 1 at 06-JAN-17
piece handle=/u01/app/oracle/RmanBackup/ORAEE_20170106_10_1_ARCHIVE tag=ORCLEE_ARCHIVE comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: deleting archived log(s)
archived log file name=/u01/app/oracle/ArchiveEE/1_1_932558267.dbf RECID=4 STAMP=932562082
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=3 STAMP=932558270
channel c1: starting piece 1 at 06-JAN-17
channel c1: finished piece 1 at 06-JAN-17
piece handle=/u01/app/oracle/RmanBackup/ORAEE_20170106_11_1_ARCHIVE tag=ORCLEE_ARCHIVE comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: deleting archived log(s)
archived log file name=/u01/app/oracle/ArchiveEE/1_5_932550469.dbf RECID=3 STAMP=932558270
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=5 STAMP=932562082
channel c1: starting piece 1 at 06-JAN-17
channel c1: finished piece 1 at 06-JAN-17
piece handle=/u01/app/oracle/RmanBackup/ORAEE_20170106_12_1_ARCHIVE tag=ORCLEE_ARCHIVE comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: deleting archived log(s)
archived log file name=/u01/app/oracle/ArchiveEE/1_2_932558267.dbf RECID=5 STAMP=932562082
Finished backup at 06-JAN-17
Starting backup at 06-JAN-17
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 06-JAN-17
channel c1: finished piece 1 at 06-JAN-17
piece handle=/u01/app/oracle/RmanBackup/ORAEE_20170106_13_1_CONTROL tag=ORCLEE_CONTROL comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-JAN-17
Starting Control File and SPFILE Autobackup at 06-JAN-17
piece handle=/u01/app/oracle/flash_recovery_area/ORAEE/autobackup/2017_01_06/o1_mf_s_932562088_d6z598x6_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-JAN-17
released channel: c1
RMAN>
Recovery Manager complete.
RMAN> exit
SQL> def
DEFINE _DATE = "06-01-2017 13:24:14" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "oraee" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Release 11.2.0.1.0 - 64bit Production" (CHAR) <<<<< Oracle version
DEFINE _O_RELEASE = "1102000100" (CHAR)
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
[oracle@ora oracle]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 6 13:09:50 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
SQL>
create table restore_test (data_date date, Col1 varchar2(50));
Table created.
SQL> insert into restore_test values (sysdate, 'Before Restore Point');
1 row created.
SQL> commit ;
Commit complete.
SQL> create restore point Before_upgrade ;
Restore point created.
SQL>
set linesize 200
col name for a30
select scn, to_char(time,'dd.mm.yyyy hh24:mi:ss') time, name from v$restore_point;
SCN TIME NAME
---------- ------------------- ------------------------------
987489 06.01.2017 13:13:48 BEFORE_UPGRADE
SQL> insert into restore_test values (sysdate, 'AFTER RESTORE POINT');
1 row created.
SQL> insert into restore_test values (sysdate, 'UPGRADE ACTIONS PERFORMED');
1 row created.
SQL> commit ;
Commit complete.
SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss' ;
Session altered.
SQL> select * from restore_test ;
DATA_DATE COL1
------------------- --------------------------------------------------
06-01-2017 13:13:33 Before Restore Point
06-01-2017 13:14:10 AFTER RESTORE POINT
06-01-2017 13:14:22 UPGRADE ACTIONS PERFORMED
SQL> commit ;
Commit complete.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Recovery Manager complete.
[oracle@ora oracle]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 6 13:17:08 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount ;
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2215064 bytes
Variable Size 167773032 bytes
Database Buffers 360710144 bytes
Redo Buffers 3764224 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
[oracle@ora oracle]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 6 13:17:30 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORAEE (DBID=2923458434, not open)
RMAN> LIST RESTORE POINT ALL;
using target database control file instead of recovery catalog
SCN RSP Time Type Time Name
---------------- --------- ---------- --------- ----
987489 06-JAN-17 BEFORE_UPGRADE
RMAN> restore database until restore point Before_upgrade ;
Starting restore at 06-JAN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/oraee/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/oraee/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/oraee/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/oraee/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/oraee/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORAEE_20170106_8_1_FULL
channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORAEE_20170106_8_1_FULL tag=ORCLEE_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 06-JAN-17
RMAN> recover database until restore point Before_upgrade ;
Starting recover at 06-JAN-17
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 06-JAN-17
RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs
RMAN> exit
Recovery Manager complete.
[oracle@ora oracle]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 6 13:21:07 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss' ;
Session altered.
SQL> select * from restore_test ;
DATA_DATE COL1
------------------- --------------------------------------------------
06-01-2017 13:13:33 Before Restore Point <<<<< Only one row :)
