I am the junior DBA first time i need to do Database Refreshment on UNIX platform.
1. Database Names are different and mount points are different.
2. Synonyms and dblinks are should be retained.
How to do this and what are the steps i need to follow.
i have Oracle 10g data guard set up on windows environment.....i need to know the what are the right steps to perform failover in case the primary database gets fail.
View 4 Replies View RelatedI need to refresh a PROD database into TEST database. The PROD and TEST runs on 10g. I need a full refresh. Is there any pre req's which i should keep in mind ?.
View 1 Replies View RelatedHow can we refresh a database ?
View 3 Replies View RelatedOracle 11.2.0.1 and 11.2.0.3.
Can rman refresh database from 11.2.0.1 to 11.2.0.3?
In another words, my source database is 11.2.0.1, my target database is 11.2.0.3
I want to refresh target database from source database. Is it ok since version is different?
I am getting ORA -32320 when I tried to fast fresh MVs that were truncated.I am able to complete refresh all MVs. However, some MVs are still giving me ORA-32320 when I try to do fast refresh even after complete refresh.
DBMS_MVIEW.EXPLAIN_PLAN says the MVIEW can be fast refreshed.
ERROR at line 1:
ORA-32320: REFRESH FAST of "dev"."Claim_line" unsupported after cointainer table
PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1
Quote:ORA-32320:
REFRESH FAST of "string"."string" unsupported after container table PMOPs Cause: A Partition Maintenance Operation (PMOP) has been performed on the materialized view, and no materialized view supports fast refersh after container table PMOPs. Action: Use REFRESH COMPLETE. Note: you can determine why your materialized view does not support fast refresh after PMOPs using the DBMS_MVIEW.EXPLAIN_MVIEW() API.
how to again make the MVs fast refreshable?
DB_VERSION=10.2.0.4
OS_VERSION=Windows 2008 R2
RAM_SIZE=32 GB
We have 2 production (OLTP) database( DB1 (18GB) and DB2 (200GB) ) on a server out of which DB2 database has almost 20 Materalized Views all (REFRESH FORCE ..ON DEMAND).
I have already informed the client that this is not the OLAP database where you are going to make use of the M Views for reporting purpose. However currently the client does not have any alternative.
Breif details :-
Out of 20 M Views 3 refresh once a week and where as the rest refresh every night daily.
We are currently testing a new website for which we had to re-schedule all M Views from their Old timings to New Timings i.e. Daily Basis.
But post re-scheduling the database performance was badly degarded as the database was too slow also the number of archives increasted to 300GB which was earlier 100 GB.To resolve the issue , we had to go back to the old timings and the database is currently working fine.
Requirement :-Client is looking out for an alternative solution as he needs to once again change the M View refresh time i.e. Daily Refresh.We already have 2 Oracle Instance on the server
Questions :-
1. Can i create another database ( sayTESTDB2 ) in no archivelog mode and create a db link to reduce the load on the server ?
2. If Yes .. Then is there any relavent link /example or Metalink Note ID for the same??
3. What all parameters do i need to consider while creating a DB Link ?
3. Out of 32 GB RAM , I have given 4GB and 8GB sga_max to DB1 and DB2 Respectively . Also the cpu utilization is normally 50-%60%.
Since currently we do not have any Licence for other server .. Can i make use of the same server ( i.e. creating 3 rd Oracle Instance )?
Since the main intention of this activity is reduce the load from the DB2 onto TESTDB when the daily refresh has set once again
I am trying to refresh the validation database with the old production backup i.e. our requirement. I have given the rman script i have executed and the output error message amd the rman configuration setting. Plus in the next post ill post the current sucessfully running RMAN script for your reference.
rman TARGET sys/passwd@Production CATALOG rman_pmxp/rman@catlog AUXILIARY sys/passwd @validation
RMAN> show all
2> ;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 90 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'H:\backups\DB_Sunday_Backup\Prod_%d_%F.rman.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F';
[code]...
How to perform db refresh.
View 1 Replies View Relatedversion 10203 on windows (old db on solaris 8170)
i Have to refresh database data, No of users/schemas are 400+. fastest way to do that would be to do full exp/imp.but 1st drop current users cascade. (any command to drop all users in with one command?)then validate all all tables/schemas are same & up-to-date. i am thinking to check full exp logs on old & new db.(what that can take forever manually going through thousands of tables etc)
I was using physical standby but my primary database had had some HW issue.So I had activated standby database to handle production.During 3 months, standby database was our live database and our old primary was unavailable.Now HW issue on primary has been fixed.And I want to set again my old configuration : means set primary and standby db.
I'm trying this :
1) check the SCN on old primary
2) Rman incremantal backup from scn
3) on primary db : catalog
4) on primary db : recover database noredo
The steps for the subject line mentioned. and pre-reqs or post-checks if any.
View 3 Replies View Relatedi upgraded oracle db from 11.2.0.2.0 11.2.0.3.0 on linux using dbua. after completion of the up gradation what are the post upgradation steps. does i need to run any scripts.
View 4 Replies View Relatedreview/correct my steps for adding an entry to the orapwd file.Is there an environment variable I need to set to tell it the name of the password file. In addition, how do I get a "user" in the password file when orapwd accepts file, password and entries as paramaters. Does this mean I need to give user "sys" sysdba privs?
USERNAME SYSDBA SYSOPER
------------------------------ ------ -------
SYS TRUE TRUE
/oracle/10.2.0.1.0/dbs> orapwd file=orapwd_pri password=sys entries=50;
/oracle/10.2.0.1.0/dbs> ls -tl orapwd_pri
-rwxrwxr-x 1 oracle dba 7680 Jul 06 12:09 orapwd_pri
/oracle/10.2.0.1.0/dbs> sqlplus "/ as sysdba"
SQL> grant sysdba to xxx;
grant sysdba to xxx
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled
SQL> show parameter password;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
Is there a special way I have to name the file or setup a env to find it?
I have installed grid and oracle 11g on linux environment. I want to installation January patch related to grid and oracle 11g. This is the first time i m going to install the oracle 11g.
View 4 Replies View RelatedA link which describe step by step with screen shots how can i install oracle 10 r2 on oracle linux server release 6.1 using vmware workshop 8.my actual os is windows server 64 bit in this in install vmware 8 and i install oracle linux server release 6.1 32 bit now i want to install oracle 10g r2 or oracle 11g r2 on this .
View 2 Replies View RelatedI am working on the quality center oracle database to write a query that fetches all steps of a test case including the ones having calls to tests. Structure of table is explained below. I've made up an example and attached it as an image to this post. This image also has the expected result of the query I want to write.
Table Name: STEPS (This table contains steps belonging to tests. Some steps are simply calls/links to other tests)
Columns:
STEP_ID (primary key - integer)
STEP_NAME (char)
STEP_DESC (char)
ORDER (integer)
TEST_ID (reference to table TEST.test_id)
[code].......
Referring to the example (see attached image), I'm looking to write a query that gives me all steps (including steps from called tests) of the test - "Empty trash from mailbox" in the correct order.
To start with I can write the following query to get steps of the test - "Empty trash from mailbox".
SELECT * FROM steps WHERE test_id = (SELECT test_id FROM test WHERE test_name = 'Empty trash from mailbox')
the steps for moving the files between ASM diskgroups in 10g or 11g?
View 4 Replies View RelatedI have a single node Oracle E-Biz 12.1.3 Installation.We plan to convert this to a dual node RAC install and I wanted to run by the steps to perform this AND clone over the current production system to the new RAC system (note this will not be using ASM - but will be based on NetApp NFS)
1. Install GRID 11.2.0.3
- installed acorss two nodes, Binaries can be shared
- voting disks / cluster rigistry MUST be shared
2. Install Database Software for RAC
- Binaries cannot be shared and must be installed on each node independently
3. Configure the GRID for database (database / listeners)
4. Clone over the stand alone system (clone database as RAC)
I will be using SCAN - and therefore would expect all the web services / concurrent manager nodes to point to the SCAN hostname as opposed to individual host names.
I will have to proceed with Oracle 9 database refresh from production server to integration server. 5 biggest schemas must be exported and imported. They constitute 97% space used in a database. This is very big database so I would like to be sure that everything will go smoothly. That is why i want to ask you some questions.
Have you got any clues for me before I start with exp/imp? From my side i will tell you that I will have to exp/imp schema by schema because there is small space both on production and integration disk for a dump. First thing I thought are dependencies between schemas that are exported and that which are not, and also between schemas that are exported/imported one by one.
This is procedure that I plan:
For every schema that is to be refreshed
{
1. Export schema with ROWS=N CONSTRAINTS=Y
2. EXPORT schema with ROWS=y CONSTRAINTS=N
3. Import schema from step one
4. Disable all the foreign key constraints using ALTER TABLE DISABLE CONSTRAINT.
5. Import schema with rows
}
ALTER TABLE ENABLE CONSTRAINT
With above procedure i think that I will avoid problems with dependencies between schemas exported/imported one by one. But my concern is if there are any dependencies between those schemas and schemas that are not exported. Is there an way to check it before refresh ?
Which step in the following plan is the first step of execution
I reckon it is "TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE"
Is that correct?
In the "Predicate Information (identified by operation id):"
section the predicates - access and filter for the step "TABLE ACCESS FULL | PYMNT_DUES" are displayed first
Isn't there any relation between the order of execution steps and the order in which predicates are displayed?
Execution Plan
----------------------------------------------------------
Plan hash value: 538700484
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2364 | 15 (14)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 2364 | 15 (14)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 2364 | 14 (8)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 2313 | 13 (8)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 2281 | 12 (9)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 2255 | 11 (10)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 175 | 6 (17)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | INDX_2 | 12 | 612 | 2 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | PYMNT_DUES | 43 | 5332 | 3 (0)| 00:00:01 |
| 10 | VIEW PUSHED PREDICATE | | 1 | 2080 | 5 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 154 | 5 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 103 | 4 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE | 1 | 32 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | INDX_BBS_1 | 3 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| DAILY_CHECK | 1 | 71 | 2 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | INDX_SEARCH | 1 | | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | INDX_IAM_SR_NO | 1 | 26 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | INDX_2 | 1 | 32 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------
In my production -- we have 3 nodes Primary RAC with 2 Nodes Physical secondary Standby RAC. Due to some reason we will have to failover means 3 nodes Primary Standby RAC to 2 Nodes secondary physical Standby RAC. share Steps for Failover ( not switchover) from 3 Nodes Primary RAC to 2 Nodes Physical Standby RAC. I know this is generic and can be find from google but issue is how can failover from 3 nodes primary standby rac to 2 nodes secondary physical standby rac.
View 3 Replies View RelatedI have 5 MViews that I want to refresh in two occassions, every sundays and at the 1st of the month. I created a Refresh Group for the weekly and that work fine. But when I tried to created the second Refresh Group for the monthly I get a "materialized view is already in a refresh group".
You can only have a materialized view in one refresh group? What options to I have to refresh it in different intervals?
I have a created a materialized view which is based on a view on remote database. Now how do I refresh the view.
Materialized view is created by
CREATE MATERIALIZED VIEW mv_employee_name
AS SELECT EMPLID, EMPL_NAME
FROM VEMPDATA@REMOTEDB
WHERE REGION = 'US';
how the refersh happens or how do I specify the refresh clause.
REFRESH FAST option is looking for VIEW LOG on the master table but in this case its a remote view, so I cannot create any object on remote db.
A scheduler job is there to refresh some materialized views. All the views are run under single job name. Some mviews are not refreshing and those are not refreshing in the next run too.if we run those mviews manually then they are working fine from then . after some days those views which i told before are not refreshing and this happens frequently and i check the job_run_details views also and i am not seeing any errors in that.
View 6 Replies View RelatedIs there any function to refresh all views and functions in oracle.
View 4 Replies View RelatedI have n number of materialized views in my schema ..I want to refresh all M_views at once by using cursor
View 3 Replies View RelatedHow to refresh a view and where is a log located?
View 2 Replies View RelatedDuring the MV refresh we are getting the following error and not able to fix it .
SQL> EXECUTE DBMS_MVIEW.REFRESH('EMP','F');
BEGIN DBMS_MVIEW.REFRESH('EMP','F'); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into ("XYZ"."EMP"."DESCRIPTION")
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2426
ORA-06512: at line 1
The above column:- Emp.Description is a 'CLOB NOT NULL' .
We have created the MV using the below script
create materialized view EMP refresh on demand for update as select *from ABC.EMP@DBLINK;
My MVIEW has a refresh rate (sysdate + 1 ) + 24/24 So how often it gets refreshed .
View 3 Replies View Related