Starting with Oracle Database 12c, we can now store a password file directly into ASM Diskgroup. Also, Oracle has made it default for a password to be case sensitive in the password fil...
Showing posts with label Administration. Show all posts
Showing posts with label Administration. Show all posts
Wednesday, 20 September 2017
12c copy the password file from PRIMARY ASM to the STANDBY ASM.
When you create a RAC database in 12c with DBCA, the password file will be stored in an ASM. In this post i will demonstrate how to refresh/copy the password file from PRIMARY ASM to the STANDBY A...
Monday, 15 September 2014
Recover from a DROP or TRUNCATE table by using RMAN
To recover from a dropped
or truncated table, a dummy database (copy of primary) will be restored and
recovered to point in time so the table can be exported. Once the table
export is complete, the table can be imported into the primary database.
This dummy database can be a subset of the primary database. However, the
'dummy' database must include the SYSTEM, UNDO (or ROLLBACK), and the
tablespace(s) where the dropped/truncated table resid...
How to recreate oraInventory (Central Inventory) in RDBMS Homes if the central inventory is corrupted or lost?
SOLUTION
STEP 1:
Locating the central inventory
The
location of the central inventory can be found from the central inventory
point...
How to Recreate The AWR( AUTOMATIC WORK LOAD ) Repository
The best
way to deinstall/install AWR is as follows:
1. Disable
AWR statistics gathering by setting the statistics level to basic as follows:
Check
settings for parameters as follows:
sqlplus
/nolog
connect /
as sysdba
show
parameter cluster_database
show
parameter statistics_level
show
parameter sga_tar...
Standby MRP needs old log sequence even after restore of incremental backup at standby
At standby
applied incremental backup but MRP process at standby is looking
for very old sequence.
SQL>SELECT
ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence
Received", APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM (SELECT THREAD# ,SEQUENCE#
FROM V$ARCHIVED_LOG&nb...
Saturday, 7 June 2014
How to copy schema stats from one schema to another schema
Here we will see how to copy schema statistics from one schema
to another. Basically there are two methods for doing this:
using
DBMS_STATS or
using
Data Pump
This is especially useful when doing large
data transfers between either schemas or databases and statistics are needed
fast. Remember that data density, row chaining or index cluster factor changed
due to the data import and index rebuilds.
Method 1: Using DBMS_STATS
Extract the statistics
First we need to login as schema owner which
from which we want to extract...
Sunday, 25 May 2014
Refresh of Mview is Throwing ORA-00942
Refresh of Mview is throwing ORA-942 after
successful creation.
Create materialized view
schema_a.client_mview
tablespace users build immediate
refresh force start with trunc(sysdate)+1455/1440
next trunc(sysdate)+1455/1440
as
select client_id, username, createdate, swver
from client_req@TEST_LINK
where swver is not null;
Materialized View Created.
select db_link, username from dba_db_links where db_link like 'TEST_LINK';
DB_LINK USERNAME
--------------------...
Saturday, 10 May 2014
LOG MINING IN ORACLE DATABASE
Suppose data in a table has been deleted.
Audit trail was not enabled, therefore we do not have a clue of the user who has
performed deletion and there are around 30 users that have DELETE rights on
that table. Management want to know the name of the user, here we can use Oracle LOG MINING feature to filter out
the transactions performed during that period.
Note: Make sure the Archive log must be
enabled previously, if you want to use LOG Mining technique.
CONSIDERATIONS
Database : ORCL
Schema : SCOTT Table : STUDENTS
Date of Deletion...
Sunday, 30 March 2014
Rapid Growth of SYSAUX Tablespace
Oracle 10g onwards, The SYSAUX an auxiliary tablespace to the SYSTEM
tablespace created in your database.
Some database components that formerly created and used separate tablespaces
now occupy the SYSAUX tablespace.
Below table shows the components that uses the SYSAUX tablespace as their default tablespace and
the tablespace in which they were stored in earlier releases.
Component
Using SYSAUX
Tablespace in
Earlier Releases
Analytical Workspace
Object Table
SYSTEM
Enterprise Manager
Repository
...
Wednesday, 29 January 2014
ORA-01031: insufficient privileges to sys@standby as sysdba
The
ORA-01031 is very generic error and normally users will receive this error when
users don’t have appropriate privilege.
$oerr ora 01031
01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
// without the appropriate
privilege. This error also occurs if
// attempting to install a
database without the necessary operating
// system privileges.
// ...