Category: backup

How to mirror database Privileges of a Oracle database user to another database user?

Script for Mirroring Privileges of a user:


Scenario: A Database schema named “TEST2” is created and business wants to grant the privileges for the user same as already existing Database user “TEST1”.


Run the below queries as SYS or any Privileged user to get the DDL commands in Oracle for TEST1 user.


SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’TEST1′) FROM DUAL;


 SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,’TEST1′) FROM DUAL;


 SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,’TEST1′) FROM DUAL;

 The output will be a sequence of grant commands related to privileges assigned to TEST1 user. Now replace the string TEST1 with new user name “TEST2” and run the commands.

 This will grant all privileges same as TEST1 user to TEST2 user in Oracle Database.

Oracle RMAN Inremental Backup Algorithm and Change Tracking File

During an incremental backup, RMAN reads the SCN of each data block in the input file and compares it to the checkpoint SCN
of the parent incremental backup. If the SCN in the input data block is greater than or equal to the checkpoint SCN of the
parent, then RMAN copies the block.

Performance of incremental Backup can be improved by enabling block change tracking.When Change tracking is enabled, all the changes to datablocks are recored in change tracking file. RMAN can read the changed SCN’s from change tracking file, instead of reading each and every input file.
One change tracking file is created for the entire database.
By default, the change tracking file is created as an Oracle managed file in DB_CREATE_FILE_DEST. We can specify the name of the block change tracking file by providing the location you choose.



SQL command to enable block change tracking

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
This creates change tracking file in “db_create_file_dest” directory

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING  USING FILE ‘/test1/data/rman_change_track.f’ REUSE;
This creates the change tracking file in the location specified.

SQL command to disable block change tracking

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
By Default,  block change tracking is disabled.
If the change tracking feature is disabled, any existing change tracking files will be deleted

SQL query to find change tracking file Details

select filename, status from v$block_change_tracking

How to Relocate the change tracking file

1.Shut down the database

SHUTDOWN IMMEDIATE

2.Using OS commands, move the change tracking file to a new location.

3.Mount the database and move the change tracking file to a location that has more space.

SQL query to rename change tracking file

ALTER DATABASE RENAME FILE   ‘/test1/data/rman_change_track.f’ TO ‘/newtest1/data1/change_trk.f’;

4.Open the database:

ALTER DATABASE OPEN;

If we cannot afford downtime for database shutdown, change tracking file can be relocated as below

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘new-location’;

Contents of change tracking file will be lost if we use the above method.So its not a preferable method.
RMAN will have to scan the entire file until the next level 0 incremental backup is done

Advantages of RMAN Incremental Backups

What is RMAN Incremental Backup?


Incremental Backups take  back up  of only datafile blocks that have changed after a previous full backup. 
Incremental backups are applicable for databases, individual tablespaces or datafiles.




Benefits of RMAN Incremental Backup:


1.RMAN Incremental backups are used to periodically roll forward an image copy of the database


2.Reduced amount of time needed for Backups


3.Less Network bandwidth is required


4.To get adequate backup performance when the aggregate tape bandwidth available for tape write I/Os is much less than the aggregate disk bandwidth for disk read I/Os


5.To  recover changes to objects created with the NOLOGGING option. 


6.To reduce backup sizes for NOARCHIVELOG databases. Instead of making a whole database backup every time, you can make incremental backups.


Note:


If the database is in ARCHIVELOG mode,  incremental backups can be taken if the database is open;
If the database is in NOARCHIVELOG mode, then we can only make incremental backups after a consistent shutdown.




Click Here to know more about how incremental backup is implemented by RMAN and the imporatnace of block change tracking feature.