Is there a way I can do a backup of *just* a database and its tablespace, instead of doing the whole instance? We're migrating from Informix to Oracle, and I'm playing with a test environment and trying to get this worked out before we go live.
View 1 RepliesI am doing some test, seeking your expert opinion.I have a database and have the following backup strategy.
1) Database running in archive log mode.
2) First I backup all the table space excluding one tablespace 'DP_TS_LOB'.
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE 'DP_TS_LOBS';
RMAN> BACKUP DATABASE;
3) I take a separate Backup of DP_TS_LOB tablespace.
RMAN> BACKUP TABLESPACE DP_TS_LOB;
4) I backup all the archived redo logs.
RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
RMAN> backup archivelog all delete input;
Now I wanted to restore the database from the backup excluding the DP_TS_LOB and bring it up and running. DP_TS_LOB is huge and used only on certain work flows. I don't want the database to be down until we restore DP_TS_LOB. I wanted to restore all the other tablespace and bring the database up operational and restore and recover the DP_TS_LOB tablespace datafile's in background taking the datafiles of DP_TS_LOB tablespace offline.I tried the following but unsuccessful.
RMAN> restore controlfile to 'g:ctl_bckctl_01' from autobackup;
RMAN> restore spfile to 'g:ctl_bckspfile' from autobackup;
RMAN> sql "create PFILE = ''G:ctl_bckPFILE'' from SPFILE = ''G:ctl_bckSPFILE''";
Update the new parameter with new control file name. Copied the control file and parameter file to corresponding location.
RMAN> startup nomount pfile=D:appdivaproduct11.1.0db_1databasepfile;
RMAN> restore database skip tablespace 'dp_ts_lob';
RMAN> alter database mount;
RMAN> sql 'alter database datafile 8 offline'; ==> datafile of DP_TS_LOB tablespace.
First I assumed "Recover Database" will only recover online database files. But got the following error.
RMAN> recover database;
Starting recover at 27-JUL-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/27/2011 18:23:03
RMAN-06094: datafile 8 must be restored
Then I tried recovering data file separately, but same error.
RMAN> recover datafile 1;
Starting recover at 27-JUL-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/27/2011 18:24:30
RMAN-06067: RECOVER DATABASE required with a backup or created control file
i want to clone the test db to dev db but i dont want default users and tablespaces(SYS,SYSAUX,...) in the test db datapump and exp are not working, then contacted ORACLE support and they concluded that test db sysaux was corrupted.i took RMAN all tablespace bkup except default tablespaces. then created new database using DBCA and now i want to restore and recover all those test db tablesapces but while i'm doing restore its saying
RMAN> restore tablespace MAIN_DATA;
Starting restore at 07-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
creating datafile file number=20 name=/opt/oracle/oradata/DEV71/main_data01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 07-SEP-12
i created all tablespaces in dev same as test db,
we are using dataware house of huge amount of data I want to take backup using rman so that I restore oracle instance and specific tablespace only not full database
View 4 Replies View RelatedI recently started reading about oracle backup and recovery concepts, and I needed to understand what is happening where we put a tablespace to a backup mode (alter tablespace tablespace_name begin backup) ?
View 10 Replies View Relatedi fired query to find the tablespace_name for the table FYI below
SQL> select table_name , tablespace_name from dba_tables where
2 table_name like 'CLAIM_HEADER';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
CLAIM_HEADER
it showing missing ....find out tablespace_name.
I have a "prj_tbl" named tablespace in a user's schema in my database. i have given 100mb size to the "prj_tbl" at the time of its creation and auto extend is open for 10mb,and now this tablespace is using nearly 3.6mb space for its data and remaining space is free,now i want to reduce the size of "prj_tbl" tablespace to 20mb and when i tried to resize it then ,
It throws an error- "'ora-03297 file contains used data beyond requested resize value oracle"
I think this message showing that my new size is small than the size of data on my tablespace but it shows free space nearly 96mb then it means my new size is larger than the size of data on tablespace. How should I reduce the size..
We are managing a huge amount of data warehouse on oracle 11.2.0.3.0. We need to take backup using rman of some tablespace not the whole database backup.
we need a backup strategy in which we can restore the backup of some tablespace plus oracle instance if any disaster occur.
I have taken backup using rman on test db of system , sysaux , user tablespace then i restore these tablespace successfully and make the remaining tablespace offline , but while open the database it show me below error.
My question is how i backup using rman so that in case of disaster i restore oracle instance plus specific tablespace not full database.
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'C:APPADMINISTRATORORADATASYSTEMSYSTEM01.DBF'
I have oracle 10g installed on my system and name of the database is "ORCL" for which I have schedule the incremental backup everyday. Mentioned below are the steps followed
*************PARAMETERS TO BE CHANGED******************
configure channel 1 device type disk
format '\192.16.17.140dbbackups192.16.17.152oracle_rman_backup_incrementalstd_%U';
configure channel 2 device type disk
format '\192.16.17.140dbbackups192.16.17.152oracle_rman_backup_incrementalstd_%U';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK
TO '\192.16.17.140dbbackups192.16.17.152oracle_rman_backup_incrementalcntrl_%U';
CONFIGURE RETENTION POLICY TO REDUNDANCY 7;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
*******************************************************
*******COMMAND FOR THE CONNECTING TO RMAN**************
rman LOG = \192.16.17.140dbbackups192.16.17.152oracle_rman_backup_incremental
rmanlog_%date:~4,2%-%date:~7,2%-%date:~10%.txt APPEND
CONNECT TARGET SYS/ORACLE@ORCL
*******************************************************
********INCREMENTAL BACKUP COMMAND*********************
RUN
{
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
BACKUP ARCHIVELOG ALL DELETE INPUT;
}
********************************************************************
Now I want to restore this backup to some other system with new database. How to do this recovery to some other database on new system.
I have taken the backup of a tablespace using the below mentioned command.
run
{
allocate channel c1 device type disk;
backup tablespace tcs_tbs;
}
Now I after even after one month or one year I want to restore the backup on my same database to bring the tablespace to the previous position.
Is it possible to restore even after one year, Is retention period gonna play its role in this?
I have taken the tablespace export it came 2.1gb and for the same user i have taken the schema export it came 5.1gb
why their is a lot of difference in size?
Operating System - WindowXP
oracle version 10.2.0.1
I was learning some recovery part in my home laptop. Database is in Archivelog, flashback mode. All of sudden, i deleted it from OS level with out taking backup of it.
When i tried to open database, it failed to start. Database is in mount mode.while trying to open, it gives message -
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'F:ORACLEPRODUCT10.2.0ORADATADBSYSTEM01.DBF'
I tried to create on file named "UNDOTBS01.DBF" but oracle is not recognizing it.
How many times i can do recover (TSPITR) a tablespace ? While trying to recover tspitr tablespace on 2nd time i got error message ORA-01178 file 13 created before last create controlfile,but first time it was success.
View 1 Replies View Related Imagine this situation
Current time :05 Apr 2011
I have to perform the TSPITR of a tablespace to recover it to a time 03-apr-2011 5 pm.Now I have done that.
Now I find that I need to actually recover it ti 04-apr-2011.Can I again recover the tablespace to 04-Apr after recovering it to 03-Apr by mistake I am not usng recovery catalog;
1) I have created One tablespace "ABCD" and assigned one user on it "ABCD_AR". I made table through the user and made some transaction.
2) Then I have taken RMAN backup, which include backup of tablespace (ABCD) also.
List of Datafiles in backup set 10
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTSYSTEM01.DBF
2 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTSYSAUX01.DBF
3 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTUNDOTBS01.DBF
4 Full 997754 17-JUN-13 C:APPADMINISTRATORORADATATESTUSERS01.DBF
5 Full 997754 17-JUN-13 C:DATAFABCD01.DBF
3) After taking Rman backup, I issued command - "Drop tablespace ABCD including contents and datafiles;"
4) When i issue "LIst backup" through RMAN, its shows blank for the particular tablespace datafile.
SQL> DROP TABLESPACE ABCD INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
[code]....
how to recover those tablespace's datafile?What happened to earlier tablesapce backup?
I have facing problem while taking backup on Windows 7 client of Oracle 11g R2 database. I have installed oracle 11gR2 for windows on windows 7 machine. I have created a directory like below in database.
On Database Server
SQL> create directory win_expdp_dir as 'd:expimp';
Directory created.
SQL> grant read, write on directory win_expdp_dir to lab;
Grant succeeded.
On Windows 7 machine (client machine)
D:appproduct11.2.0client_1BIN>expdp lab/lab@wbdata.wbh-db11g DIRECTORY=win_expdp_dir DUMPFILE=lab.dmp LOGFILE=lab.log
Export: Release 11.2.0.1.0 - Production on Mon May 2 12:51:44 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
I have give all sharing rights on d:expimp directory. My main question is why i'm getting this error. Is there any thing missing in setup. how to take export on windows 7 client.
I am trying to clone a database on another server with different direcory structure. So that path on the source db server are /u04 whereas on on target db server it would be /u03.
Since this I am testing it on small database initially I have kept all datafiles, backup and archivelogs at /u04 and /u03 on the source and target db servers respectively Now I have copied the backups from source db server to target and since path is changed, I cataloged it
However during restore I am getting
Quote:ORA-19870: error reading backup
Here are the session details
RMAN> catalog start with '/u03/oradata/db7fra';
searching for all files that match the pattern /u03/oradata/db7fra
List of Files Unknown to the Database
=====================================
File Name: /u03/oradata/db7fra/DB7/archivelog/2011_03_15/o1_mf_1_16_6qyvpb3w_.arc
File Name: /u03/oradata/db7fra/DB7/backupset/2011_03_15/o1_mf_annnn_TAG20110315T123018_6qypyv5v_.bkp
[code].....
I have altered permissions on the backup files as well but of no use
oracle@dev-biz:/u03/oradata/db7fra/DB7/backupset/2011_03_15 $ls -ltr
total 545260
-rwxrwxrwx 1 oracle dba 12419072 Mar 15 13:52 o1_mf_ncsnf_TAG20110315T123008_6qypyrz2_.bkp
-rwxrwxrwx 1 oracle dba 3072 Mar 15 13:52 o1_mf_annnn_TAG20110315T125043_6qyr54to_.bkp
-rwxrwxrwx 1 oracle dba 426496 Mar 15 13:52 o1_mf_annnn_TAG20110315T125006_6qyr3zk4_.bkp
-rwxrwxrwx 1 oracle dba 14336 Mar 15 13:52 o1_mf_annnn_TAG20110315T123018_6qypyv5v_.bkp
[code].....
I've read a lot about the different types of backup available with Oracle (hot and cold backup). However, I was thinking of a different way of performing this task. I'm currently using Windows Server 2008 R2 and Oracle 11g Standard Edition. I'd like to schedule an entire backup of my server via the utility "Windows Server Backup" (available for free).That way, I could recover my entire server with all the programs and files in case the latter crashes.I'm wondering if this solution could be used as a way of backing up (and recovering) the Oracle database. Should I still set up a regular hot backup with the Archivelog mode enabled in case some operations/transactions were being done at the time of the crash (for the data integrity)?
View 2 Replies View Relateddatabase size is 30GB is it recommended for RMAN backup
View 2 Replies View Relatedcan we restore database from obsolete backup using rman
View 2 Replies View RelatedSQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
after i backup my database,i check the alert log ,i found the following errror:
Mon May 14 09:19:42 2012
Errors in file /u01/app/oracle/admin/szcargo/udump/szcargo_ora_26967.trc:
Mon May 14 09:19:42 2012
Errors in file /u01/app/oracle/admin/szcargo/udump/szcargo_ora_26967.trc:
Mon May 14 09:19:42 2012
Errors in file /u01/app/oracle/admin/szcargo/udump/szcargo_ora_26967.trc:
[code]....
the trace number 26967 :
[oracle@shenzhengair archivelog]$ cat /u01/app/oracle/admin/szcargo/udump/szcargo_ora_26967.trc
/u01/app/oracle/admin/szcargo/udump/szcargo_ora_26967.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
[code]....
what does the error mean?
online backup done thru RMAN.Suppose i am taking online backup of full database. During the backup, user's are inserting/deleting/modifying data. This data is getting stored as online archives. Once the database backup is finished, how these archives are applied to the database to make the database up to date.
View 1 Replies View RelatedHow to setup the database for automated RMAN backup. like if we want to have a backup exactly at 8:00 PM IST. So how to configure it.
View 6 Replies View RelatedBackup entire database, without archived logs, while the database is open for user activity and also This backup should be the base for an incremental backup strategy.
View 10 Replies View RelatedI am admin of my actual server and I want to backup all databases for later use. For that this is my approach.
1 - List all databases on the system
2 - Apply the backup command to each database
We are running a 5Tb database backup which takes really a long time to backup and impacts the performance in general during the backup. We got a DataGaurd database in place which we want to utilize to run the RMAN backup on to avoid performance impacts and just use DG database for backup and DR purposes. My Question is if we run the backups on DataGaurd Database, How would a RMAN DG database backup would clear the archive logs from Primary production and from DataGaurd Database during the backup process?
View 7 Replies View RelatedI need to restore a backup of database A from into Database B. Both have the same names. Database B is already up and running.
I have a full rman backup of Database A ( it was taken with a recovery catalog which I don't have access to now as it was deleted ). I just have the full backup pieces including the control files. Is it possible to recover this database into Database B from this stand point.
I was thinking
2. Shutdown database B
3. Mount database B. It has same name as Database A.
4. With RMAN restore controlfile. Will a new controlfile be created from the backup directory I have the rman pieces for the full backup in ?
crosscheck backup of database vs crosscheck backup command
RMAN> crosscheck backup;
it shows all backup pieces.
RMAN> crosscheck backup of database;
it shows 2 backup piece of each backupset.
when should be use: crosscheck backup of database;
and when should be use: crosscheck backup;
My require is to get all tablespace used,free space report for all the databases. So I have scheduled Job in OEM to get it with below script. But always got error and my job is getting failed again and again. How to get the all tablespace (used,free space) report for all the databases.
sql' select distinct
a.tablespace_name,
SUM(a.bytes)/1024/1024 "Used Size MB",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "Max Size Mb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "Total Used MB",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "Total
[code]......
I want to create a database named Influx as well as tablespace influx.for this i login in sys account.now i am trying to run the following command:
CREATE DATABASE INFLUX LOGFILE GROUP 1 ('G:appdhirenderoradataINFLUX
edo01.log') SIZE 100M, GROUP 2 ('G:appdhirenderoradataINFLUX
edo02.log') SIZE 100M, GROUP 3 ('G:appdhirenderoradataINFLUX
edo03.log') SIZE 100M MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 DEFAULT TABLESPACE users DATAFILE 'G:appdhirenderoradataINFLUXINFLUX01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED FOLLWOING ERROR IS COMMING:ora-01501 datbase fialed.ora-01100
database already mount .