Disclaimer All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use.
This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove.
Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only.
This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only.
If you see any issues with Content and copy write issues, I am happy to remove if you notify me.
Contact Geek DBA Team, via geeksinsights@gmail.com
|
The ACTIVE DUPLICATE DATABASE option in 11g clone the source database by coping the full image of the data files to the auxiliary instance location through the network. This procedure doesn’t not required any pre backup existence to clone the database. However, in Oracle 12c, when ACTIVE DUPLICATE DATABASE is initiated, instead of sending the full image copy of the data files, RMAN first takes the backup of data files into backupsets, and these backups are transmit to the auxiliary location and will be restored/recovered subsequently. This functionality will surely reduce the overall impact on the source instance and also help fastening the cloning procedure. You have the flexibility to use backup compression, encryption option with this feature.
RMAN> DUPLICATE TARGET DATABASE to stby2
FROM ACTIVE DATABASE
SECTION SIZE 500M
USING COMPRESSED BACKUPSETS;
In the previous releases, the cloned database will be opened automatically upon the process completion. The additional NOOPEN clause in 12c, will prevent opening database immediately after the cloning process completion. Therefore, the database will remain in MOUNT state after the procedure and you will have to manually open the database. This will be a good option to use in the following scenario: When the cloned database required additional settings, like, flash back or incremental backup settings. To avoid any startup problems To adjust the initialization parameters If this database is used for upgrade purpose, you can just start the db in UPGRADE mode to proceed with the upgrade. Here is the example how to use NOOPEN clause:
RMAN> DUPLICATE TARGET DATABASE to stby3
FROM ACTIVE DATABASE
SECTION SIZE 500M
USING COMPRESSED BACKUPSETS
NOOPEN;
As discussed, earlier, Another important nice feature in RMAN from 12c is using network based recovery or incremental backup strategy from the servics. This eliminates taking incremental backups or copy those backup to standby and saves lot of time. Lets check, On Primary, check the latest log
Check on Primary
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 93
Next log sequence to archive 95
Current log sequence 95
On Primary Check the current_scn
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2205519
SQL>
On standby , cancel the media recovery just to get a gap for our test.
RMAN> alter database recover managed standby database cancel;
Statement processed
On Standby check the mrp process and the recover done until sequence 94
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 88
ARCH CLOSING 93
ARCH CONNECTED 0
ARCH CLOSING 94
ARCH CLOSING 91
RFS IDLE 0
RFS IDLE 95
RFS IDLE 0
8 rows selected.
SQL>
On standby database , connect to rman and give following command
RMAN> recover database from service db12c using compressed backupset;
Starting recover at 15-SEP-13
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
skipping datafile 5; already restored to SCN 1733202
skipping datafile 7; already restored to SCN 1733202
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service db12c
destination for restore of datafile 00001: /u01/app/oracle/oradata/STBY12C/system01.dbf
channel ORA_DISK_2: starting incremental datafile backup set restore
channel ORA_DISK_2: using compressed network backup set from service db12c
destination for restore of datafile 00003: /u01/app/oracle/oradata/STBY12C/sysaux01.dbf
channel ORA_DISK_3: starting incremental datafile backup set restore
channel ORA_DISK_3: using compressed network backup set from service db12c
destination for restore of datafile 00004: /u01/app/oracle/oradata/STBY12C/undotbs01.dbf
channel ORA_DISK_4: starting incremental datafile backup set restore
channel ORA_DISK_4: using compressed network backup set from service db12c
destination for restore of datafile 00006: /u01/app/oracle/oradata/STBY12C/new/users01.dbf
channel ORA_DISK_5: starting incremental datafile backup set restore
channel ORA_DISK_5: using compressed network backup set from service db12c
destination for restore of datafile 00008: /u01/app/oracle/oradata/STBY12C/pdb12c/system01.dbf
channel ORA_DISK_3: restore complete, elapsed time: 00:00:15
channel ORA_DISK_3: starting incremental datafile backup set restore
channel ORA_DISK_3: using compressed network backup set from service db12c
destination for restore of datafile 00009: /u01/app/oracle/oradata/STBY12C/pdb12c/sysaux01.dbf
channel ORA_DISK_5: restore complete, elapsed time: 00:00:30
channel ORA_DISK_5: starting incremental datafile backup set restore
channel ORA_DISK_5: using compressed network backup set from service db12c
destination for restore of datafile 00010: /u01/app/oracle/oradata/STBY12C/pdb12c/pdb12c_users01.dbf
channel ORA_DISK_5: restore complete, elapsed time: 00:00:03
channel ORA_DISK_5: starting incremental datafile backup set restore
channel ORA_DISK_5: using compressed network backup set from service db12c
destination for restore of datafile 00011: /u01/app/oracle/oradata/STBY12C/pdb12c/testtbs.dbf
channel ORA_DISK_5: restore complete, elapsed time: 00:00:03
channel ORA_DISK_2: restore complete, elapsed time: 00:01:08
channel ORA_DISK_3: restore complete, elapsed time: 00:00:53
channel ORA_DISK_1: restore complete, elapsed time: 00:01:38
channel ORA_DISK_4: restore complete, elapsed time: 00:03:28
starting media recovery
archived log for thread 1 with sequence 95 is already on disk as file /u01/app/oracle/fast_recovery_area/STBY12C/archivelog/2013_09_15/o1_mf_1_95_93cctlpl_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/STBY12C/archivelog/2013_09_15/o1_mf_1_95_93cctlpl_.arc thread=1 sequence=95
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-SEP-13
Note the lines using compressed network backup, yes the backup is from network not the local.
As you observed, without taking a incremental backup in production and copying that backup in standby and initiate recovery,
with single command the network recover and standby rollfoward has been performed. This nice feature definetely saves lot of time
in preparing the environments and reduce manual intervention.
Just check the Sync
On Primary
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2209958
SQL>
On Standby, Almost near
RMAN> select current_scn from v$database;
CURRENT_SCN
-----------
2209938
-Thanks
Geek DBA
From 12c Onwards, RMAN has been enhanced to restore/recover the datafiles/tablespaces etc from the network services rather copying them manually. This enhanancement facilitates easy recovery operations when you have standby database without backups or activation etc. YOu can even recover the database from standby using service in RMAN in restore command.
Lets see how it works Create a tablespace for our test.
SQL> create tablespace test datafile '/u01/app/oracle/oradata/DB12c/test.dbf' size 100m ;
Tablespace created.
Create a sample table
SQL> create table t tablespace test as select * from dba_objects;
Table created.
Check in standby and in primary the datafile exists
SQL> select file_id,file_name from dba_data_files;
FILE_ID FILE_NAME
---------- --------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/DB12c/system01.dbf
3 /u01/pp/oracle/oradata/DB12c/sysaux01.dbf
4 /u01/app/oracle/oradata/DB12c/undotbs01.dbf
6 /u01/app/oracle/oradata/DB12c/new/users01.dbf
13 /u01/app/oracle/oradata/DB12c/test.dbf
The datafile number is 13 that we are going to delete and restore
Removed the datafile intentionally
SQL> !rm -f /u01/app/oracle/oradata/DB12c/test.dbf
Try to access the file from database , it will throw error
SQL> Select count(*) from t;
ORA-00376: file 13 cannot be read at this time
ORA-01110: data file 13: '/u01/app/oracle/oradata/DB12c/test.dbf'
In Primary Database connect to rman
RMAN> connect target sys/admin123@db12c
connected to target database: DB12C (DBID=1279089071)
Make the datafile Offline
RMAN> alter database datafile 13 offline;
Statement processed
Check my service
[oracle@Geek DBA12c admin]$ tnsping stby12c
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 15-SEP-2013 18:07:20
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0.1/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Geek DBA12c.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STBY12C)))
OK (40 msec)
[oracle@Geek DBA12c admin]$
Restore the datafile 13 that we have deleted,
Please note, there is no backup and nothing, and use of new option " from service" this is the service where to connect to standby from primary.
RMAN> restore datafile 13 from service stby12c;
Starting restore at 15-SEP-13
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service stby12c
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/DB12c/test.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 15-SEP-13
Recover the datafile now
RMAN> recover datafile 13;
Starting recover at 15-SEP-13
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 15-SEP-13
Bring the datafile online
RMAN> alter database datafile 13 online;
Statement processed
Check your table and its table and its respective datafile that is deleted
RMAN> select count(*) from t;
COUNT(*)
----------
90777
RMAN> select file_id,file_name,tablespace_name from dba_data_files where file_id=13;
FILE_ID FILE_NAME TABLESPACE_NAME
--------------------------------------------------------------------------------
13 /u01/app/oracle/oradata/DB12c/test.dbf TEST
-Thanks
Geek DBA
Step by Step standby creation using RMAN duplicate database
This post is about to create a standby database in container enabled database (Just want to check any changes required from previous versions steps) All steps are similar like previous versions except we have to create directory structures for PDB databases otherwise RMAN script will fail Please go through the steps to create a standby database in same host. Environment is as follows
HostName: Geek DBA12c
Primary DB: DB12c
Standby DB: STBY12C
As all of you aware you donot required backups or filecopy to create standby from 11g onwards using Duplicate database for standby using active database, which connects to primary creates an onfly image copy backup and send to standby destination.
Further in 12c, this has been enhanced, the image copies can be send to destination host in small chunks (section copies) which I will post later.
For now, here are the steps to create standby database.
Keep the Primary Database in Force Logging mode
oracle@Geek DBA12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 15 13:26:48 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> alter database force logging;
Database altered.
SQL> exit
On Primary Database, Create Standby redologs
ALTER DATABASE ADD STANDBY LOGFILE
'/u01/app/oracle/oradata/DB12c/srl_redo04.log'
SIZE 52428800
/
ALTER DATABASE ADD STANDBY LOGFILE
'/u01/app/oracle/oradata/DB12c/srl_redo05.log'
SIZE 52428800
/
ALTER DATABASE ADD STANDBY LOGFILE
'/u01/app/oracle/oradata/DB12c/srl_redo06.log'
SIZE 52428800
Set Primary Database Parameters : DB12C
SQL> SHOW PARAMETER DB_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DB12c
SQL> SHOW PARAMETER DB_UNIQUE_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string DB12c
SQL> ALTER SYSTEM SET log_archive_config = 'dg_config=(DB12C,STBY12C)';
System altered.
SQL> ALTER SYSTEM SET log_archive_dest_2 = 'service=STBY12C async valid_for=(online_logfile,primary_role) db_unique_name=STBY12C';
System altered.
Update the TNSNAMES.ora with standby tns entry;
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DB12C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Geek DBA12c.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB12c)
)
)
PDB12C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Geek DBA12c.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb12c)
)
)
stby12c =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Geek DBA12c.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STBY12C)
)
)
Update the Listener.ora for Standby Service to be registered with listener.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = Geek DBA12c.localdomain)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = STBY12C)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = STBY12C)
)
)
Copy the primary password file to Standby site password file
[oracle@Geek DBA12c dbs]$ cp orapwDB12c orapwSTBY12C
Create a single entry init file for standby
[oracle@Geek DBA12c dbs]$ echo db_name=STBY12C >> initSTBY12C.ora
[oracle@Geek DBA12c dbs]$ cat initSTBY12C.ora
db_name=STBY12C
[oracle@Geek DBA12c dbs]$
Create a datafiles directory for standby
[oracle@Geek DBA12c dbs]$ cd $ORACLE_BASE/oradata
[oracle@Geek DBA12c oradata]$ ls -ltr
total 4
drwxr-x---. 5 oracle oinstall 4096 Sep 15 13:29 DB12c
[oracle@Geek DBA12c oradata]$ mkdir STBY12C
[oracle@Geek DBA12c oradata]$ ls -ltr
total 8
drwxr-x---. 5 oracle oinstall 4096 Sep 15 13:29 DB12c
drwxr-xr-x. 2 oracle oinstall 4096 Sep 15 13:42 STBY12C
[oracle@Geek DBA12c oradata]$
[oracle@Geek DBA12c admin]$ ls -ltr
total 4
drwxr-x---. 6 oracle oinstall 4096 Sep 2 08:28 DB12c
[oracle@Geek DBA12c admin]$ mkdir STBY12C
[oracle@Geek DBA12c admin]$ mkdir -p STBY12C/adump STBY12c/new STBY12C/pdbseed STBY12C/pdb12c
[oracle@Geek DBA12c admin]$
Startup the Standby by with parameter file we have created.
[oracle@Geek DBA12c admin]$ export ORACLE_SID=STBY12C
[oracle@Geek DBA12c admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 15 13:52:40 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initSTBY12C.ora
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2286656 bytes
Variable Size 159386560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5152768 bytes
SQL> SQL>
Connect to RMAN to TARGET (Primary) and Auxiliary Instance (Standby)
[oracle@Geek DBA12c admin]$ rman auxiliary /
Recovery Manager: Release 12.1.0.1.0 - Production on Sun Sep 15 14:07:27 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: STBY12C (not mounted)
RMAN> connect target sys@db12c
target database Password:
connected to target database: DB12C (DBID=1279089071)
RMAN>
# Run the RMAN Script below
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'DB12c','STBY12C'
set db_unique_name='STBY12C'
set db_file_name_convert='/DB12c/','/STBY12C/'
set log_file_name_convert='/DB12c/','/STBY12C/'
set control_files='/u01/app/oracle/oradata/STBY12C/STBY12C1.ctl'
set log_archive_max_processes='5'
set fal_client='STBY12C'
set fal_server='DB12C'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(DB12C,STBY12C)'
set log_archive_dest_2='service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=DB12C'
;
}
Standby database create completed,
To keep the length of this post short, I have copied the log to attachment, CLick here –>
Please see the RMAN memory script logfile Login To standby database and keep the standby in real time apply mode
[oracle@Geek DBA12c stage]$ . oraenv
ORACLE_SID = [STBY12C] ? STBY12C
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@Geek DBA12c stage]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 15 16:34:00 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> alter database recover managed standby database disconnect from session using current logfile;
Database altered.
SQL>
To check the standby working or not
On Primary, provide couple of manual switches
SQL> Alter system switch logfile;
SQL> Alter system switch logfile;
SQL> Verify the latest sequence in primary database
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 90
Next log sequence to archive 92
Current log sequence 92
On Standby check, the status of MRP process
SQL> select process,status,blocks,sequence# from v$managed_standby;
PROCESS STATUS BLOCKS SEQUENCE#
--------- ------------ ---------- ----------
ARCH CLOSING 2 88
ARCH CLOSING 6 90
ARCH CONNECTED 0 0
ARCH CLOSING 22 87
ARCH CLOSING 659 91
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 1 92
MRP0 APPLYING_LOG 102400 92
9 rows selected.
SQL>
The standby is ready and sync with production database.
Next posts:-
1) 12C Network restore using service
2) 12C, rollforward of standby using service
Prior to 12c, restoring a table if accidently dropped is a cumbersome process via RMAN backups.
The following are the steps are to be performed to do the same
1) Create a auxiliary instance
2) Duplicate the database
3) Manually export the dropped table
4) Import that into original database
5) clean up the auxiliary instance etc.
or using TSPITR way.
From 12c, all the above steps are automated, only you have to see the storage provision to hold your auxiliary database etc. So it means you must atleast need the storage space in your db host that original database has.
Connect to the PDB database and create a user and the table as requried
SQL> alter session set container=PDB12C;
Session altered.
SQL> create user TESTUSER identified by TESTUSER;
User created.
SQL>
SQL> grant dba to TESTUSER;
Grant succeeded.
SQL>
SQL> CREATE TABLESPACE TESTTBS DATAFILE AUTOEXTEND ON MAXSIZE 100M;
Tablespace created.
SQL>
SQL> create table TESTUSER.TEST_TABLE(ID NUMBER) TABLESPACE TESTTBS;
Table created.
SQL>
SQL> insert into TESTUSER.TEST_TABLE values (1);
1 row created.
SQL> commit;
Commit complete.
SQL>
Take a backup of PDB database
Geek DBA12c:/home/oracle>rman
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Sep 15 14:43:35 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
RMAN> CONNECT TARGET "sys AS SYSBACKUP";
target database Password:
connected to target database: PDB12C (DBID=3257067578)
RMAN> BACKUP PLUGGABLE DATABASE PDB12C;
Starting backup at 09/15/2013 14:44:36
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=270 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=+DATA/PDB12C/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/sysaux.275.819112037
input datafile file number=00008 name=+DATA/PDB12C/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/system.274.819112037
input datafile file number=00012 name=+DATA/PDB12C/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/tbs_tst.290.819120929
input datafile file number=00013 name=+DATA/PDB12C/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/TESTTBS.287.819121381
input datafile file number=00010 name=+DATA/PDB12C/E011004AA64F0CF9E0433514DA0A096B/DATAFILE/users.277.819112065
channel ORA_DISK_1: starting piece 1 at 09/15/2013 14:44:38
channel ORA_DISK_1: finished piece 1 at 09/15/2013 14:44:45
piece handle=+DATA/PDB12C/E011004AA64F0CF9E0433514DA0A096B/BACKUPSET/2013_06_26/nnndf0_tag20130626t144437_0.298.819125079 tag=TAG20130626T144437 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 09/15/2013 14:44:45
Starting Control File and SPFILE Autobackup at 09/15/2013 14:44:45
piece handle=+DATA/PDB12C/AUTOBACKUP/2013_06_26/s_819125085.301.819125085 comment=NONE
Finished Control File and SPFILE Autobackup at 09/15/2013 14:44:46
Drop the table
Geek DBA12c:/home/oracle>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Sep 15 14:45:21 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> alter session set container=PDB12C;
Session altered.
SQL> DROP TABLE TESTUSER.TEST_TABLE PURGE;
Table dropped.
SQL> select * from TESTUSER.TEST_TABLE;
select * from TESTUSER.TEST_TABLE
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Geek DBA12c:/home/oracle>
Now, Recover the table using new RMAN command recover table
Note: the option used here are
1) until time , you can use SCN or sequence also
2) Auxiliary destination, to mount your temporary database and restore the backups
3) DUMP destination, once the auxiliary database is opened the table can be exported to this dump destination
4) Alternatively you can also specify the notableimport not to import the table.
Geek DBA12c:/home/oracle>rman
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Sep 15 15:28:04 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
RMAN> CONNECT TARGET "sys AS SYSBACKUP";
target database Password:
connected to target database: PDB12C (DBID=3257067578)
RMAN> RECOVER TABLE TESTUSER.TEST_REC OF PLUGGABLE DATABASE PDB12C UNTIL TIME "to_date('2013-09-15:14:45:00','YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/tmp' DATAPUMP DESTINATION '/tmp' DUMP FILE 'tst_dump2.dmp';
Starting recover at 09/15/2013 15:28:37
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=272 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='AAxr'
initialization parameters used for automatic instance:
db_name=PDB12C
db_unique_name=AAxr_pitr_PDB12C_PDB12C
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/opt/app/oracle
db_create_file_dest=/tmp
log_archive_dest_1='location=/tmp'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance PDB12C
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 281019648 bytes
Database Buffers 780140544 bytes
Redo Buffers 5480448 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2013-09-15:14:45:00','YYYY-MM-DD:HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET until clause
Starting restore at 09/15/2013 15:28:50
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=82 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +DATA/PDB12C/AUTOBACKUP/2013_06_26/s_819125085.301.819125085
channel ORA_AUX_DISK_1: piece handle=+DATA/PDB12C/AUTOBACKUP/2013_06_26/s_819125085.301.819125085 tag=TAG20130626T144445
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
output file name=/tmp/PDB12C/controlfile/o1_mf_8wpj7slx_.ctl
Finished restore at 09/15/2013 15:28:58
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2013-09-15:14:45:00','YYYY-MM-DD:HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 8 to new;
set newname for clone datafile 9 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 3 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3, 8, 9;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /tmp/PDB12C/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /tmp/PDB12C/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 09/15/2013 15:29:04
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/PDB12C/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/PDB12C/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/PDB12C/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/PDB12C/BACKUPSET/2013_06_26/nnndf0_tag20130626t123856_0.282.819117537
channel ORA_AUX_DISK_1: piece handle=+DATA/PDB12C/BACKUPSET/2013_06_26/nnndf0_tag20130626t123856_0.282.819117537 tag=TAG20130626T123856
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /tmp/PDB12C/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to /tmp/PDB12C/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/PDB12C/E011004AA64F0CF9E0433514DA0A096B/BACKUPSET/2013_06_26/nnndf0_tag20130626t144437_0.298.819125079
channel ORA_AUX_DISK_1: piece handle=+DATA/PDB12C/E011004AA64F0CF9E0433514DA0A096B/BACKUPSET/2013_06_26/nnndf0_tag20130626t144437_0.298.819125079 tag=TAG20130626T144437
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 09/15/2013 15:29:27
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=819127767 file name=/tmp/PDB12C/datafile/o1_mf_system_8wpj8191_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=819127767 file name=/tmp/PDB12C/datafile/o1_mf_undotbs1_8wpj819j_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=819127767 file name=/tmp/PDB12C/datafile/o1_mf_sysaux_8wpj8199_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=11 STAMP=819127767 file name=/tmp/PDB12C/datafile/o1_mf_system_8wpj8jf1_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=12 STAMP=819127767 file name=/tmp/PDB12C/datafile/o1_mf_sysaux_8wpj8jdt_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2013-09-15:14:45:00','YYYY-MM-DD:HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 4 online";
sql clone "alter database datafile 3 online";
sql clone 'PDB12C' "alter database datafile
8 online";
sql clone 'PDB12C' "alter database datafile
9 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX", "PDB12C":"SYSTEM", "PDB12C":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 8 online
sql statement: alter database datafile 9 online
Starting recover at 09/15/2013 15:29:28
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 15 is already on disk as file +DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_15.279.819117567
archived log for thread 1 with sequence 16 is already on disk as file +DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_16.278.819123911
archived log for thread 1 with sequence 17 is already on disk as file +DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_17.294.819123955
archived log for thread 1 with sequence 18 is already on disk as file +DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_18.296.819123981
archived log for thread 1 with sequence 19 is already on disk as file +DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_19.295.819124251
archived log for thread 1 with sequence 20 is already on disk as file +DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_20.297.819124297
archived log for thread 1 with sequence 21 is already on disk as file +DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_21.299.819124453
archived log for thread 1 with sequence 22 is already on disk as file +DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_22.286.819125213
archived log file name=+DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_15.279.819117567 thread=1 sequence=15
archived log file name=+DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_16.278.819123911 thread=1 sequence=16
archived log file name=+DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_17.294.819123955 thread=1 sequence=17
archived log file name=+DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_18.296.819123981 thread=1 sequence=18
archived log file name=+DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_19.295.819124251 thread=1 sequence=19
archived log file name=+DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_20.297.819124297 thread=1 sequence=20
archived log file name=+DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_21.299.819124453 thread=1 sequence=21
archived log file name=+DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_22.286.819125213 thread=1 sequence=22
media recovery complete, elapsed time: 00:00:04
Finished recover at 09/15/2013 15:29:34
sql statement: alter database open read only
contents of Memory Script:
{
sql clone 'alter pluggable database PDB12C open read only';
}
executing Memory Script
sql statement: alter pluggable database PDB12C open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/tmp/PDB12C/controlfile/o1_mf_8wpj7slx_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 285213952 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes
sql statement: alter system set control_files = ''/tmp/PDB12C/controlfile/o1_mf_8wpj7slx_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 285213952 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2013-09-15:14:45:00','YYYY-MM-DD:HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 13 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 13;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 09/15/2013 15:30:08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=15 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00013 to /tmp/AAXR_PITR_PDB12C_PDB12C/datafile/o1_mf_TESTTBS_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +DATA/PDB12C/E011004AA64F0CF9E0433514DA0A096B/BACKUPSET/2013_06_26/nnndf0_tag20130626t144437_0.298.819125079
channel ORA_AUX_DISK_1: piece handle=+DATA/PDB12C/E011004AA64F0CF9E0433514DA0A096B/BACKUPSET/2013_06_26/nnndf0_tag20130626t144437_0.298.819125079 tag=TAG20130626T144437
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 09/15/2013 15:30:10
datafile 13 switched to datafile copy
input datafile copy RECID=14 STAMP=819127810 file name=/tmp/AAXR_PITR_PDB12C_PDB12C/datafile/o1_mf_TESTTBS_8wpjb1c3_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2013-09-15:14:45:00','YYYY-MM-DD:HH24:MI:SS')";
# online the datafiles restored or switched
sql clone 'PDB12C' "alter database datafile
13 online";
# recover and open resetlogs
recover clone database tablespace "PDB12C":"TESTTBS", "SYSTEM", "UNDOTBS1", "SYSAUX", "PDB12C":"SYSTEM", "PDB12C":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 13 online
Starting recover at 09/15/2013 15:30:10
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 22 is already on disk as file +DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_22.286.819125213
archived log file name=+DATA/PDB12C/ARCHIVELOG/2013_06_26/thread_1_seq_22.286.819125213 thread=1 sequence=22
media recovery complete, elapsed time: 00:00:00
Finished recover at 09/15/2013 15:30:11
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database PDB12C open';
}
executing Memory Script
sql statement: alter pluggable database PDB12C open
contents of Memory Script:
{
# create directory for datapump import
sql 'PDB12C' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/tmp''";
# create directory for datapump export
sql clone 'PDB12C' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/tmp''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_AAxr_wgmi":
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 64 KB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported "TESTUSER"."TEST_REC" 5.031 KB 1 rows
EXPDP> Master table "SYS"."TSPITR_EXP_AAxr_wgmi" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_AAxr_wgmi is:
EXPDP> /tmp/tst_dump2.dmp
EXPDP> Job "SYS"."TSPITR_EXP_AAxr_wgmi" successfully completed at Wed Sep 15 15:30:51 2013 elapsed 0 00:00:22
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYSBACKUP"."TSPITR_IMP_AAxr_yzka" successfully loaded/unloaded
IMPDP> Starting "SYSBACKUP"."TSPITR_IMP_AAxr_yzka":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "TESTUSER"."TEST_REC" 5.031 KB 1 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYSBACKUP"."TSPITR_IMP_AAxr_yzka" successfully completed at Wed Sep 15 15:31:23 2013 elapsed 0 00:00:08
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /tmp/PDB12C/datafile/o1_mf_temp_8wpj92wr_.tmp deleted
auxiliary instance file /tmp/PDB12C/datafile/o1_mf_temp_8wpj8zhc_.tmp deleted
auxiliary instance file /tmp/AAXR_PITR_PDB12C_PDB12C/onlinelog/o1_mf_3_8wpjb4l3_.log deleted
auxiliary instance file /tmp/AAXR_PITR_PDB12C_PDB12C/onlinelog/o1_mf_2_8wpjb49s_.log deleted
auxiliary instance file /tmp/AAXR_PITR_PDB12C_PDB12C/onlinelog/o1_mf_1_8wpjb434_.log deleted
auxiliary instance file /tmp/AAXR_PITR_PDB12C_PDB12C/datafile/o1_mf_TESTTBS_8wpjb1c3_.dbf deleted
auxiliary instance file /tmp/PDB12C/datafile/o1_mf_sysaux_8wpj8jdt_.dbf deleted
auxiliary instance file /tmp/PDB12C/datafile/o1_mf_system_8wpj8jf1_.dbf deleted
auxiliary instance file /tmp/PDB12C/datafile/o1_mf_sysaux_8wpj8199_.dbf deleted
auxiliary instance file /tmp/PDB12C/datafile/o1_mf_undotbs1_8wpj819j_.dbf deleted
auxiliary instance file /tmp/PDB12C/datafile/o1_mf_system_8wpj8191_.dbf deleted
auxiliary instance file /tmp/PDB12C/controlfile/o1_mf_8wpj7slx_.ctl deleted
auxiliary instance file tst_dump2.dmp deleted
Finished recover at 09/15/2013 15:31:28
RMAN> exit
Recovery Manager complete.
Geek DBA12c:/home/oracle>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Sep 15 15:31:36 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> alter session set container=PDB12C;
Session altered.
SQL> select * from TESTUSER.TEST_TABLE;
ID
----------
1
SQL>
Starting 12c, from RMAN prompt the SELECT statement can be fired, Lets check some Select on V$ views
RMAN> select file_name from dba_data_files;
using target database control file instead of recovery catalog
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB12c/system01.dbf
/u01/app/oracle/oradata/DB12c/sysaux01.dbf
/u01/app/oracle/oradata/DB12c/undotbs01.dbf
/u01/app/oracle/oradata/DB12c/new/users01.dbf
RMAN> select username,sid,status from v$session;
USERNAME SID STATUS
------------------------------ ---------- --------
2 ACTIVE
3 ACTIVE
4 ACTIVE
5 ACTIVE
6 ACTIVE
7 ACTIVE
8 ACTIVE
9 ACTIVE
10 ACTIVE
11 ACTIVE
12 ACTIVE
13 ACTIVE
14 ACTIVE
15 ACTIVE
16 ACTIVE
17 ACTIVE
18 ACTIVE
21 ACTIVE
22 ACTIVE
23 ACTIVE
25 ACTIVE
27 ACTIVE
28 ACTIVE
30 ACTIVE
32 ACTIVE
SYS 34 ACTIVE
38 ACTIVE
SYS 39 INACTIVE
40 ACTIVE
42 ACTIVE
44 ACTIVE
31 rows selected
Select on any table which does not exist
RMAN> select * from test;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 09/13/2013 21:28:11
ORA-00942: table or view does not exist
RMAN>
Connected to the PDB database which was down, and started from RMAN prompt.
[oracle@Geek DBA12c ~]$ rman target test/test@pdb12c
Recovery Manager: Release 12.1.0.1.0 - Production on Fri Sep 13 21:28:49 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB12C (DBID=1279089071, not open)
RMAN> select * from t;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 09/13/2013 21:29:03
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only
RMAN> alter database open;
Statement processed
RMAN>
Check the DBA views with select
RMAN> select segment_name,owner from dba_segments where owner='TEST';
no rows selected
RMAN>
Create/DROP table and select on custom tables also works
RMAN> create table t1 as select * from dba_objects;
Statement processed
RMAN> select count(*) from t1;
COUNT(*)
----------
90776
RMAN>
RMAN> drop table t1;
Statement processed
RMAN>
however, Connect command does not work
RMAN> conn test/test@pdb12c
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "advise, allocate, alter, analyze, associate statistics, audit, backup, begin, @, call, catalog, change, comment, commit, configure, connect, convert, copy, create, create catalog, create global, create script, create virtual, crosscheck, declare, delete, delete from, describe, describe catalog, disassociate statistics, drop, drop catalog, drop database, duplicate, exit, explain plan, flashback, flashback table, grant, grant catalog, grant register, host, import, insert, list, lock, merge, mount, noaudit, open, print, purge, quit, recover, register, release, rename, repair, replace, report, "
RMAN-01008: the bad identifier was: conn
RMAN-01007: at line 1 column 1 file: standard input
[oracle@Geek DBA12c admin]$ rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Fri Sep 13 21:15:43 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB12C (DBID=1279089071)
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 5;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 5 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
RMAN> backup as copy section size 100M datafile 3 format '/home/oracle/backup_copy_sysaux_%U';
Starting backup at 13-SEP-13
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/DB12c/sysaux01.dbf
backing up blocks 1 through 12800
channel ORA_DISK_2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/DB12c/sysaux01.dbf
backing up blocks 12801 through 25600
channel ORA_DISK_3: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/DB12c/sysaux01.dbf
backing up blocks 25601 through 38400
channel ORA_DISK_4: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/DB12c/sysaux01.dbf
backing up blocks 38401 through 51200
channel ORA_DISK_5: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/DB12c/sysaux01.dbf
backing up blocks 51201 through 64000
output file name=/home/oracle/backup_copy_sysaux_data_D-DB12C_I-1279089071_TS-SYSAUX_FNO-3_0iojpc1d tag=TAG20130913T212045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/DB12c/sysaux01.dbf
backing up blocks 64001 through 76800
output file name=/home/oracle/backup_copy_sysaux_data_D-DB12C_I-1279089071_TS-SYSAUX_FNO-3_0iojpc1d tag=TAG20130913T212045
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/DB12c/sysaux01.dbf
backing up blocks 76801 through 89600
output file name=/home/oracle/backup_copy_sysaux_data_D-DB12C_I-1279089071_TS-SYSAUX_FNO-3_0iojpc1d tag=TAG20130913T212045
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_3: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/DB12c/sysaux01.dbf
backing up blocks 89601 through 90880
output file name=/home/oracle/backup_copy_sysaux_data_D-DB12C_I-1279089071_TS-SYSAUX_FNO-3_0iojpc1d tag=TAG20130913T212045
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:26
output file name=/home/oracle/backup_copy_sysaux_data_D-DB12C_I-1279089071_TS-SYSAUX_FNO-3_0iojpc1d tag=TAG20130913T212045
channel ORA_DISK_5: datafile copy complete, elapsed time: 00:00:26
output file name=/home/oracle/backup_copy_sysaux_data_D-DB12C_I-1279089071_TS-SYSAUX_FNO-3_0iojpc1d tag=TAG20130913T212045
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:03
output file name=/home/oracle/backup_copy_sysaux_data_D-DB12C_I-1279089071_TS-SYSAUX_FNO-3_0iojpc1d tag=TAG20130913T212045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
output file name=/home/oracle/backup_copy_sysaux_data_D-DB12C_I-1279089071_TS-SYSAUX_FNO-3_0iojpc1d tag=TAG20130913T212045
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:16
Finished backup at 13-SEP-13
Starting Control File and SPFILE Autobackup at 13-SEP-13
piece handle=/u01/app/oracle/fast_recovery_area/DB12C/autobackup/2013_09_13/o1_mf_s_826060887_936f3zh5_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-13
At OS or in DISK the file is only one,
[oracle@Geek DBA12c ~]$ ls -ltr
-rw-r-----. 1 oracle oinstall 744497152 Sep 13 21:21 backup_copy_sysaux_data_D-DB12C_I-1279089071_TS-SYSAUX_FNO-3_0iojpc1d
[oracle@Geek DBA12c ~]$
-Thanks
Geek DBA
With multisection copies the backup will be faster than earlier
Prior to 12c, If you are taking a backup of database or datafile for image copy, the file will be copied as is, and backup cannot split into multiple pieces.
According to documentation, you can now create the image copy backup of database or datafile into multiple sections backups. This will provide us the benefit to scatter the backup across and also leverage the parallelism option to backup the database/datafile faster.
Let’s see how this works,
From 12c Onwards, DBA_users has two new columns, Last_login and Oracle_maintained
SQL> desc dba_users
Name Null? Type
------------------------------- -------- ----------------------
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(4000)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(128)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(12)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
PROXY_ONLY_CONNECT VARCHAR2(1)
COMMON VARCHAR2(3)
LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE
ORACLE_MAINTAINED VARCHAR2(1)
About LAST_LOGIN
This is very userful when an request from user has been raised to know when was the user last logged.
IMP Note: This works only for the sessions that logged via SQLPLUS. Connect to your database as normal user and check the last_login.
BTW auditing is enabled by default.
[oracle@Geek DBA12c admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 13 19:57:16 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> conn test/test@pdb12c
Connected.
SQL> col username for a15
SQL> select username,last_login from dba_users where username='TEST';
USERNAME LAST_LOGIN
--------------- ---------------------------------------------------------------------------
TEST 13-SEP-13 07.58.25.000000000 PM +05:30
Well, you can see the last_login details for user test. And now SQLPLUS display the last login of the user
[oracle@Geek DBA12c admin]$ sqlplus test/test@pdb12c
SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 13 19:57:16 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Last Successful login time: Tue Sep 12 2013 07:58:25 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
If you do not want this should display, you can use
[oracle@Geek DBA12c admin]$ sqlplus test/test@pdb12c -nologintime
BUT , the super privilege user last login details will not be shown,
Continue reading 12c Database : New columns in dba_users, no more hassels in auditing
Most of the times when the index key length is bigger you cannot do the rebuild the index online , this is due to the fact that IOT table will be created by Oracle while rebuilding online and the key length is exceeded.
ORA-01450: maximum key length (3215) exceeded
More read on this, http://jonathanlewis.wordpress.com/2009/06/05/online-rebuild/
But this is no more the case with Oracle 12c, Let's check Create a table with maximum varchar sizes so that the index keys are bigger
11G
[oracle@Geek DBA11g tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 9 16:20:01 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> Conn test/test123;
Connected.
SQL> create table t1(
v1 varchar2(4000),
v2 varchar2(2387),
v3 varchar2(100)
) tablespace users;
Table created.
SQL> create index t1_i1 on t1(v1, v2) tablespace users;
Index created.
SQL> SQL> alter index t1_i1 rebuild;
Index altered.
SQL> alter index t1_i1 rebuild online;
alter index t1_i1 rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded
As you see the rebuild (offline) operation suceeded not the online one, lets check in the 12c Database
12c
[oracle@Geek DBA12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 13 19:41:02 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=pdb12c;
Session altered.
SQL> conn test/test@//Geek DBA12c:1521/pdb12c
Connected.
SQL> create table t1(
v1 varchar2(4000),
v2 varchar2(2387),
v3 varchar2(100)
) tablespace users;
Table created.
SQL> create index t1_i1 on t1(v1, v2) tablespace users;
Index created.
SQL> alter index t1_i1 rebuild;
Index altered.
SQL> alter index t1_i1 rebuild online;
Index altered.
Now, you see no more ora-1450 error Note: The issue can be reproduced in 12c also if you connect with SYS user, Thanks to Jonathan for pointing this out to me.
–Thanks
Geek DBA
From 12c onwards, we can create a mixed indexes (bitmap and btree both) on same columns which is very useful in DW environments. Before to 12c you will receive the such column is already indexed error or index already exists if you want to create the same.
I have a copy of dba_objects as below
SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(13)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
Create an btree index
SQL> create index t1 on sys.t(object_name);
Index created.
Lets try to create another index t2,
SQL> create index t2 on sys.t(object_name);
create index t2 on sys.t(object_name)
*
ERROR at line 1:
ORA-01408: such column list already indexed
Hu Hu, no where possible, Lets mark this index invisible;
SQL> alter index t1 invisible;
Index altered.
SQL> create index t2 on sys.t(object_name);
create index t2 on sys.t(object_name)
*
ERROR at line 1:
ORA-01408: such column list already indexed
Still the same when I am trying to create the btree index, Let's try to create the bitmap index
SQL> create bitmap index t2 on sys.t(object_name);
Index created.
Whoa, bitmap index created on same column which already contains btree index. So it means you can have combination of bitmap or btree indexes on same columns whilst one should be invisble. Ofcourse if you want to use the invisible index just set the parameter optimizer_use_invisible_indexes to true will use both indexes by optimizer.
|
Oracle 23c New Features for DBA's
Follow Me!!!