Difference between NOLOCK and READPAST

 NOLOCK

========

When we use NOLOCK in select command it will allow to read rows are available on dirty pages/uncommitted from buffer cache

Ex: i have table called replication with 4 records 

update one record with out commit 

in another session i try to select table, it not showing any result 

Tried with NOLOCK , it resulting with dirty pages 

READPAST

=========

it will not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped.

SELECT *  FROM [replication] with(readpast)

it will skip modified records until transaction will commit

  

Error: Database cannot be opened. It is in the middle of a restore

This may be a bug in SSMS, after restoring FULL and LOG backups when we start mirroring we get this error - To solve execute bellow commands so that mirroring will start - 

First In Mirror instance:

ALTER DATABASE DBNAME
    SET PARTNER =   
    'TCP://PRINCIPAL SERVER.DOMAIN:MIRRORING PORT NUMBER'  
 

In Principal instance:

ALTER DATABASE DBNAME
    SET PARTNER =   
    'TCP://MIRRIR SERVER.DOMAIN:MIRRORING PORT NUMBER' 

Restore oracle database on development server from production server using RMAN backup (Restore and Recovery Method)

 Step 1) Copy Backup copies from Production to Development server using cp command 

[Source server Backup]$ scp -p bkup_j93m9af4_95849_1_1 bkup_ja3m9ahh_95850_1_1 bkup_jb3m9aju_95851_1_1 bkup_jc3m9amb_95852_1_1 bkup_jd3m9aqj_95853_1_1 bkup_je3m9aql_95854_1_1 bkup_jf3m9aqn_95855_1_1 c-87645678-20250407-00 oracle@Target server:<Backup Location>

Step 2) Once copy done connect to target server verify spfile and create pfile from spfile

[oracle@sreenu ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 7 08:56:30 2025

Version 19.21.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition 2 Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL>

SQL> sho parameter spfile

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      /u01/opt/oracle/dbhome/dbs/spfilePROD.ora                                             

SQL> create pfile from spfile;

File created.

Step 3) Check Listener status and stop Listener on Target server ( It will not allow incoming connections to connect database)

[oracle@sreenu ~]$ lsnrctl status

[oracle@sreenu ~]$ lsnrctl stop

Step 4) Drop Database on target server (Before drooping the database take full backup of the development server )

Note1:Only we can drop/delete database on mount mode only

Note2:Before drooping the database we need to shutdown database and startup restrict mode

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup restrict mount;

ORACLE instance started.

Total System Global Area 5905576504 bytes

Fixed Size                  8938040 bytes

Variable Size            3909091328 bytes

Database Buffers         1979711488 bytes

Redo Buffers                7835648 bytes

Database mounted.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

--------- --------------------

PROD      MOUNTED

SQL> drop database;

Database dropped.

Step 5) Startup database using pfile in nomount mode.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 5905576504 bytes

Fixed Size                  8938040 bytes

Variable Size            3909091328 bytes

Database Buffers         1979711488 bytes

Redo Buffers                7835648 bytes

SQL>

SQL> sho parameter pfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string

SQL>

Note: Database started using by pfile.

Step 6) Restore controlfile from backup using RMAN tool / Prompt 

[oracle@czprg-tcora303 PROD]$ rman target /

connected to target database: PROD (not mounted)

RMAN>

RMAN> restore controlfile from '/u01/opt/oracle/rman_bkup/PROD/c-87645678-20250407-00';

Starting restore at 07-APR-25

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output file name=/u01/opt/oracle/control_files/control01.ctl

output file name=/u01/opt/oracle/control_files/control02.ctl

output file name=/u01/opt/oracle/mpx_PROD/control_files/control03.ctl

Finished restore at 07-APR-25

RMAN>

Step 7) Once controlfile restored we need to bring database to mount mode.

RMAN> alter database mount;

released channel: ORA_DISK_1

Statement processed

RMAN>

Step 8) restore database.

RMAN> restore database;

Step 9) Once restore done , we need to recovery the database.

RMAN> recover database;

Step 10) Once recovery done , we need to open database using resetlogs.

RMAN> alter database open resetlogs;

Statement processed

RMAN>

Step 11) Once restore and recovery done , we need to start database with spfile

Note: Always run database using spfile only.

Note: To change database from pfile to spfile, need to create spfile from pfile and bounce the database.

SQL> create spfile from pfile;

File created.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 5905576504 bytes

Fixed Size                  8938040 bytes

Variable Size            3909091328 bytes

Database Buffers         1979711488 bytes

Redo Buffers                7835648 bytes

Database mounted.

Database opened.

SQL>

Step 12) Verify Database mode and its running with spfile or not.

SQL> sho parameter spfile

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      /u01/opt/oracle/dbhome/dbs/spfilePROD.ora

                                                 

SQL> select name,open_mode,log_mode from v$database;

NAME      OPEN_MODE            LOG_MODE

--------- -------------------- ------------

PROD      READ WRITE           ARCHIVELOG

SQL>

Step 13) Start Listener and check status 

[oracle@sreenu ~]$ lsnrctl start

[oracle@sreenu ~]$ lsnrctl status

Step 14) Ask Application team to verify from application end.

Grant select access to specific object or tables

 We can grant select access to specific Tables / Objects in three ways 

Create Login on SQL server instance 

In my case Login name Appteam

Don't Grant any server role to Login

I need fetch data from TEST_Mirr database 

Don't grant any database role

Default public will be there 

On Database user will be created automatically 

Here for Appteam not showing any tables on the TEST_Mirr database 

1) using T SQL 

Granted select on below two tables for Appteam user

use TEST_Mirr 

Grant select on conn1 to Appteam

Grant select on conn2 to Appteam

2) Using GUI 

Using Admin session go to user properties 

Click on search and select all objects of the types and click ok , it will pop up new dialog box

Select Tables and click OK

This time i am going grant select access on Output_Field table 

After granting select permissions on Output_Field table

3) Using Table Properties 

For which we need to grand select on the table go to that table properties

Go to permissions tab >>> search for a user >>> grant select as shown in below screenshots 

Now we can see all the tables which we granted select access