I am new to DBA activities.I am working on 11.2.0.1.0 version in Oracle.
My database keeps on growing so as a security measure, I am asked to take backup of the database periodically without shutting down this database. Since this task has to be done periodically, I have to schedule the job for this task.? I know how to take export of the database.. but not sure about the periodical backups... Now sure how to proceed on the activity,
I am working on 11.2.0.1.0 version in Oracle.
My database keeps on growing so as a security measure, I am asked to take backup of the databse periodically without shuttingdown this databse. Since this task has to be done periodically, I have to schedule the job for this task.? I know how to take export of the database.. but not sure about the periodical backups..
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 Relatedwhat RMAN will do at the background while taking hot backup.I am not getting any suitable docs to clear my concepts.
View 10 Replies View RelatedUser wants to take a backup of the table .
There are 2 methods that i know to take a table backup. From those 2 methods
1.Using Export ?
exp user/pass file=table.dmp tables=owner.table compress=n consistent=y grants=y
feedback=1000 log=table.log
2.Copy method ?
create table table_backup as select * from <table_name>.
What is the best possible method to take backup of 50 tables with data ? My DB version is 11gR2
View 14 Replies View RelatedI am getting below error while taking backup through RMAN.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_SBT_TAPE_1 channel at 01/09/2011 20:27:10
ORA-27191: sbtinfo2 returned error
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 RelatedIs this possible to take the dump of a huge table , say 500gb, into multiple files and then import it in other database? if yes how can we do it?
Note that it is a single table with 500GB of size.
I took backup of Datafiles , archivelog files and controlfile through RMAN. Now, i want 3 backup file will created as a output of RMAN operation. It would be :
1 backupfile for all the datafiles
1 backupfile for all the archivelog files
1 backupfile for controlfile.
It have to be regardlessly whatevr my DB size is. It it possible to do so. How i can can write rman script to accomplish the task.
I have a requirement like to retrieve past 6 months data and i have used below query.
select count(1) from event where trunc(start_datae) between trunc(sydate)-180 and trunc(sysdate);it is giving the results but am not sure whether it is giving correct data or not.If ran the above query it is taking more time to execute.
Is the above approach is correct? Is there any difference between trunc(sysdate)-180 and trunc(sysdate-180) ?
I am getting ORA-39127 while taking full database export using EXPDP on oracle 10g enterprise edition 10.1.0.2 . This is production database.
Details of the errors are:
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OBJECT_GRANT
ORA-39127: unexpected error from call to export_string :=SYS.LT_EXPORT_PKG.syste
m_info_exp(0,dynconnect,10.01.00.02.00',newblock)
ORA-06537: OUT bind variable bound to an IN position
ORA-06512: at "SYS.DBMS_METADATA", line 5107
Processing object type DATABASE_EXPORT/DE_SYSTEM_PROCOBJACT/DE_PRE_SYSTEM_ACTION
[code]....
Datapump Export completes with these 2 errors and dumpfile is generated. Are these errors Problematic. Will these errors cause problems while taking import from the dumpfile .
We have a database server running oracle 12G on linux which is the production then we have a DR using the same version of DB and linux, I want to restore from tape into the DR from the production backup. the UNIX admin has restored already from tape the rman backup, i would like to know what are the steps to restore the backup from tape, also the DR database server was created identical like production using the same spfile etc.
were do i copy restored file into on the DR server? also then in RMAN do i just start the DR database and run the command restore database? what else do i need?
I have taken cold back-up of a complete database using below rcv from a server.
RMAN>
connect target /
run{
set command id to 'bkp_EMCPROD_db';
allocate channel c1 type 'sbt_tape' parms="BLKSIZE=1048576, ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.opt)";
backup current controlfile format 'ct_t%t_T%T_d%d_s%s.ctl';
backup database format 'db_t%t_T%T_d%d_s%s_p%p.db';
backup current controlfile format 'ct_t%t_T%T_d%d_s%s.ctl';
release channel c1;
}
I need to restore this back-up on another server of the same Database Name. Below is the rcv which my sr. dba asked me to look into for the restore.
/* From the new server to restore the back-up */
RMAN>
connect target "rman/xxxxx@emcprod"
connect auxiliary /
run {
set until time "to_date('Nov 03 2011 19:00:00','Mon DD YYYY HH24:MI:SS')";
allocate auxiliary channel c1 type 'sbt_tape' parms="ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.sacsun8.opt)";
restore database;
}
I am restoring Oracle rman backup from nodeA to nodeB through RMAN and I was in the middle of the process of rename the redo log files to new location where I suddenly got the below mentioned error:
RMAN> SQL "alter database rename file 'E:oracleproduct10.2.0oradatadbadeepakREDO01.LOG' to 'E: est_rman estrmanREDO01.LOG'";
sql statement: alter database rename file 'E:oracleproduct10.2.0oradatadbadeepakREDO01.LOG' to 'E: est_rman estrmanREDO01.LOG'
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 01/31/2011 12:53:04
[code]....
In an attempt to take older data off line and allow database refreshes to be faster, tablespaces associated with partitioned table data for a given time period was taken off line, leaving only tablespaces that relate to the current time period online. In effect, tablespaces related to 2010 and earlier were taken offline from a table.
1. Without giving a filter on the partition key (the business date) to scan for data greater than the dates in the off lined tablespace partition, we get a ORA-376/ORA-1110 error (data file cannot be read at this time).
2. Materialized views using fast refresh or refresh on commit, will also not work because of the partitions being off line.
Queries directly querying the tables are manageable from an application point of view.But the materialized views failing to aggregate is a bigger problem.
how we can manage this situation? I know that I can move the partitions to a different table in a tablespace to be taken off line. But if possible, we wanted to solve this without doing a move partition.
We have production database and standby database in 11g.
Production db server is taking care by different team even I don't have any access of the production server and I am responsible only for standby server. As of now everything is working fine except database backup on standby server because standby server is not register in catalog.
How to register standby server in catalog for RMAN backup?
I have near 114 export.dmp.z* export backup. I am trying to import it on newly created database using imp.
But i am not getting how can i select all export.dmp.z* files using imp. Its easy in impdp, but i have exported backup.
I am trying to take backup of the user tstcvs of cvmdb database but it fails due to some reason [oracle@cvs ~]$ exp
Export: Release 10.2.0.1.0 - Production on Tue Jan 3 12:37:06 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: sys/sysdba@cvsdbm as sysdba
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Enter array fetch buffer size: 4096 >
Export file: expdat.dmp > 3-1-12-tstcvs
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 2
Export grants (yes/no): yes >
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users ...
User to be exported: (RETURN to quit) > tstcvs
User to be exported: (RETURN to quit) >
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TSTCVS
. exporting PUBLIC type synonyms
. exporting private type synonyms
[Code]....
I have created an user named "Raja" with a default tablespace as "Raja_TBS" along with a datafile "rajadata.dbf". I have taken the tablespace offline
SQL> alter tablespace raja_tbs offline;
Tablespace Altered. when I take a tablespace offline, which means I cannot read or write and the tablespace is currently unavailable for users. I am still able to create a table on the "Raja_TBS" while it is offline.
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.
Expdp directory=xxx.dmp dumpfile=aaa.dmp logfile=xxx.log FULL=Y
: :: : : :: : : : ;
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 24.87 MB
Processing object type SCHEMA_EXPORT/USER
[code]...
then my export hangs..... checked in alert log nothing found.and then killed the job and reran again but same....checked the status and it's saying EXECUTING.
I am try to import 4G dump in Oracle 11R2 version, in that we have around 9000+ Package Body which is taking huge time than other objects (about 8 to 12 hrs) and also it is expecting lots of system space (roughly about 10GB).
I have tried both parallel and non-parallel.how to improve speed of the package body import.
Details about the Schema & Import No. of objects in Schema
SQL> select object_type,count(1) from user_objects GROUP BY ROLLUP( object_type);
OBJECT_TYPE COUNT(1)
------------------- ----------
FUNCTION 248
INDEX 5161
JAVA CLASS 471
JAVA RESOURCE 1
JAVA SOURCE 16
LIBRARY 1
ORA-00933: SQL command not properly ended
I'm taking export dump using expdp of some schema's of total size is 300GB. This is the par file:
DIRECTORY=expdp
FILESIZE=32212254720
DUMPFILE=expdp_schema01.dmp,expdp_schema02.dmp,expdp_schema03.dmp,expdp_schema04.dmp,expdp_schema05.dmp,expdp_schema06.dmp,expdp_sche ma07.dmp,expdp_schema08.dmp,expdp_schema09.dmp,expdp_schema10.dmp,expdp_schema11.dmp,expdp_schema12.dmp,expdp_schema13.d
[code]....
here one biggest schema size is 250GB and the total size of all the schema's is 300GB. The file where am taking the dump has 350GB space but even then the expdp failed saying
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
why it failed and how to restart it and make sure it runs successfully without error.
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].....
database 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?
How 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 Related