Garuda Oracle SQL postgresql AWS blog

Monday, 29 January 2018

ORA-01078: failure in processing system parameters ORA-29701: unable to connect to Cluster Synchronization Service

ORA-01078: failure in processing system parameters

ORA-29701: unable to connect to Cluster Synchronization Service

export ORACLE_HOME=/u000/app/grid

export ORACLE_SID=+ASM

sqlplus “/as sysasm”

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 5 11:14:29 2010

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

Connected to an idle instance.

SQL> startup

ORA-01078: failure in processing system parameters

ORA-29701: unable to connect to Cluster Synchronization Service

[oracle@oel5u4-+ASM bin] ./crsctl start resource ora.cssd

CRS-2672: Attempting to start ‘ora.cssd’ on ‘oel5u4’

CRS-2679: Attempting to clean ‘ora.diskmon’ on ‘oel5u4’

CRS-2681: Clean of ‘ora.diskmon’ on ‘oel5u4’ succeeded

CRS-2672: Attempting to start ‘ora.diskmon’ on ‘oel5u4’

CRS-2676: Start of ‘ora.diskmon’ on ‘oel5u4’ succeeded

CRS-2676: Start of ‘ora.cssd’ on ‘oel5u4’ succeeded

If you face the following error

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4000: Command Start failed, or completed with errors.

Then run

crsctl start res ora.crsd –init

[oracle@oel5u4-+ASM bin] ./crs_stat -t

Name Type Target State Host

————————————————————

ora.DATA.dg ora….up.type OFFLINE OFFLINE

ora.FRA.dg ora….up.type OFFLINE OFFLINE

ora….ER.lsnr ora….er.type ONLINE ONLINE oel5u4

ora.asm ora.asm.type OFFLINE OFFLINE

ora.cssd ora.cssd.type ONLINE ONLINE oel5u4

ora.diskmon ora….on.type ONLINE ONLINE oel5u4

ora.orcl.db ora….se.type OFFLINE OFFLINE

[oracle@oel5u4-+ASM bin] sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 7 10:42:02 2012

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

Connected to an idle instance.

SQL> startup

ASM instance started

Total System Global Area 284565504 bytes

Fixed Size 1336036 bytes

Variable Size 258063644 bytes

ASM Cache 25165824 bytes

ASM diskgroups mounted

If you Still face the following error

CRS-4639: Could not contact Oracle High Availability Services

CRS-4000: Command Status failed, or completed with errors.

Then by root user run the following

perl /u000/app/grid/crs/install/rootcrs.pl -verbose -deconfig –force

/u000/app/grid/root.sh

/u000/app/grid/perl/bin/perl -I/u000/app/grid/perl/lib -I/u000/app/grid/crs/install /u000/app/grid/crs/install/roothas.pl

Then run the following using oracle user

./crsctl start resource ora.cssd

Sunday, 28 January 2018

Oracle Clusterware 11gR2 – CRS-4639: Could not contact Oracle High Availability Services

Oracle Clusterware 11gR2 – CRS-4639: Could not contact Oracle High Availability Services

# crsctl stat res -t
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Start failed, or completed with errors.

# crsctl start crs
CRS-4013: This command is not supported in a single-node configuration.
CRS-4000: Command Start failed, or completed with errors.

[root@b1 grid]# cd /u01/app/11.2.0/grid/crs/install
[root@b1 install]# ./roothas.pl -deconfig -force -verbose

[root@b1 install]# cd /u01/app/11.2.0/grid/
[root@b1 grid]# ./root.sh

User ignored Prerequisites during installation
OLR initialization - successful
Adding Clusterware entries to upstart
CRS-2672: Attempting to start 'ora.mdnsd' on 'rac2'
CRS-2676: Start of 'ora.mdnsd' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'rac2'
CRS-2676: Start of 'ora.gpnpd' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac2'
CRS-2672: Attempting to start 'ora.gipcd' on 'rac2'
CRS-2676: Start of 'ora.cssdmonitor' on 'rac2' succeeded
CRS-2676: Start of 'ora.gipcd' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'rac2'
CRS-2672: Attempting to start 'ora.diskmon' on 'rac2'
CRS-2676: Start of 'ora.diskmon' on 'rac2' succeeded
CRS-2676: Start of 'ora.cssd' on 'rac2' succeeded
PRKO-2190 : VIP exists for node rac2, VIP name rac2-vip
Configure Oracle Grid Infrastructure for a Cluster ... succeeded

[oracle@rac2 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE rac2
ora....ER.lsnr ora....er.type ONLINE OFFLINE
ora....N1.lsnr ora....er.type ONLINE ONLINE rac2
ora....N2.lsnr ora....er.type ONLINE ONLINE rac2
ora....N3.lsnr ora....er.type ONLINE ONLINE rac2
ora.asm ora.asm.type ONLINE ONLINE rac2
ora.cvu ora.cvu.type ONLINE ONLINE rac2
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE rac2
ora.oc4j ora.oc4j.type ONLINE ONLINE rac2
ora.ons ora.ons.type ONLINE ONLINE rac2
ora.rac1.vip ora....t1.type ONLINE ONLINE rac2
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application OFFLINE OFFLINE
ora.rac2.gsd application OFFLINE OFFLINE
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip ora....t1.type ONLINE ONLINE rac2
ora.scan1.vip ora....ip.type ONLINE ONLINE rac2
ora.scan2.vip ora....ip.type ONLINE ONLINE rac2
ora.scan3.vip ora....ip.type ONLINE ONLINE rac2

Migrating Database from Filesystem to ASM

SQL> startup
ORACLE instance started.

Total System Global Area 2438529024 bytes
Fixed Size 2230792 bytes
Variable Size 1375733240 bytes
Database Buffers 1056964608 bytes
Redo Buffers 3600384 bytes
Database mounted.
Database opened.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/vishnu/system01.dbf
/u01/app/oracle/oradata/vishnu/sysaux01.dbf
/u01/app/oracle/oradata/vishnu/undotbs01.dbf
/u01/app/oracle/oradata/vishnu/users01.dbf

SQL> select * from v$Log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 4 52428800 512 1 NO
CURRENT 1034168 28-JAN-18 2.8147E+14

2 1 2 52428800 512 1 YES
INACTIVE 998740 28-JAN-18 1010210 28-JAN-18

3 1 3 52428800 512 1 YES
INACTIVE 1010210 28-JAN-18 1034168 28-JAN-18

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/vishnu/control01.ctl
/u01/app/oracle/diag/vishnu/control02.ctl

SQL> ALTER SYSTEM SET control_files='+DATA' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET db_create_file_dest='+DATA' scope=spfile;

System altered.

SQL> create pfile='/u01/app/vishnu.ora' from spfile;

Need to make sure that we have sufficient space on those diskgroups to hold existing database.
Before to create ASM Diskgroups we should have done Capacity Planing. According to that panning diskgroups should be created otherwise during the conversion time it might get fail saying diskgroup space exhausted!

[oracle@rac2 oracle]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jan 28 10:22:30 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: VISHNU (DBID=2778817154)

RMAN> backup as copy database format '+DATA';

Starting backup at 28-JAN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/vishnu/system01.dbf
output file name=+DATA/vishnu/datafile/system.256.966594181 tag=TAG20180128T102257 RECID=1 STAMP=966594187
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/vishnu/sysaux01.dbf
output file name=+DATA/vishnu/datafile/sysaux.257.966594193 tag=TAG20180128T102257 RECID=2 STAMP=966594197
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/vishnu/undotbs01.dbf
output file name=+DATA/vishnu/datafile/undotbs1.258.966594201 tag=TAG20180128T102257 RECID=3 STAMP=966594200
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/vishnu/controlfile/backup.259.966594201 tag=TAG20180128T102257 RECID=4 STAMP=966594201
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/vishnu/users01.dbf
output file name=+DATA/vishnu/datafile/users.260.966594203 tag=TAG20180128T102257 RECID=5 STAMP=966594202
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 28-JAN-18
channel ORA_DISK_1: finished piece 1 at 28-JAN-18
piece handle=+DATA/vishnu/backupset/2018_01_28/nnsnf0_tag20180128t102257_0.261.966594203 tag=TAG20180128T102257 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-JAN-18

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area 2438529024 bytes

Fixed Size 2230792 bytes
Variable Size 1375733240 bytes
Database Buffers 1056964608 bytes
Redo Buffers 3600384 bytes

RMAN> restore controlfile from '/u01/app/oracle/oradata/vishnu/control01.ctl';

Starting restore at 28-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/vishnu/controlfile/current.262.966594361
Finished restore at 28-JAN-18

RMAN> switch database to copy;

using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+DATA/vishnu/datafile/system.256.966594181"
datafile 2 switched to datafile copy "+DATA/vishnu/datafile/sysaux.257.966594193"
datafile 3 switched to datafile copy "+DATA/vishnu/datafile/undotbs1.258.966594201"
datafile 4 switched to datafile copy "+DATA/vishnu/datafile/users.260.9665942

RMAN> recover database;

Starting recover at 28-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 28-JAN-18

RMAN> sql 'alter database open';

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
VISHNU READ WRITE

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/vishnu/datafile/system.256.966594181
+DATA/vishnu/datafile/sysaux.257.966594193
+DATA/vishnu/datafile/undotbs1.258.966594201
+DATA/vishnu/datafile/users.260.966594203

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/abcd/redo03.log

/u01/app/oracle/oradata/abcd/redo02.log

/u01/app/oracle/oradata/abcd/redo01.log
Migrating redo logs into ASM is simple way, just add more redolog groups and then drop existing redolog groups which are in Filesystem. Before to add redolog groups make sure you set DB_CREATE_ONLINE_LOG_DEST_n parameter and it should point to ASM diskgroups.

alter system set db_create_online_log_dest_1=’+DATA’ scope=spfile;
alter system set db_create_online_log_dest_2=’+DATA’ scope=spfile;

SQL> show parameter db_create_online_log_dest

NAME TYPE VALUE
------------------------------------ ---------- ------
db_create_online_log_dest_1 string +LOG1
db_create_online_log_dest_2 string +LOG2

SQL> alter database add logfile group 4;
Database altered.

SQL> alter database add logfile group 5;
Database altered.

SQL> alter database add logfile group 6;
Database altered.

SQL> alter database drop logfile group 2;
Database altered.

SQL> alter database drop logfile group 3;
Database altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system checkpoint;
System altered.

Manually switching logfiles because Current Group can not be dropped.

SQL> select * from v$LOg;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------

1 1 1 52428800 512 1 YES INACTIVE 1046676 29-MAY-12 1047525 29-MAY-12

4 1 2 104857600 512 1 NO CURRENT 1047525 29-MAY-12 2.8147E+14

5 1 0 104857600 512 1 YES UNUSED 0 0

6 1 0 104857600 512 1 YES UNUSED 0 0

SQL> alter database drop logfile group 1;
Database altered.

SQL> select * from v$Log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------

4 1 2 104857600 512 1 NO CURRENT 1047525 29-MAY-12 2.8147E+14

5 1 0 104857600 512 1 YES UNUSED 0 0

6 1 0 104857600 512 1 YES UNUSED 0 0

SQL> select member from v$Logfile;

MEMBER
---------------------------------------------------

+DATAFILE/abcd/onlinelog/group_4.266.784542027

+DATAFILE/abcd/onlinelog/group_5.267.784542035

+DATAFILE/abcd/onlinelog/group_6.271.784542041

Now the entire database got migrated to ASM storage and opened the database WITHOUT RESETLOG

show parameter spfile

connect / as sysdba
SQL> show parameter spfile

NAME TYPE VALUE
------- ------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.ora

SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora' from spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.ora';
#Or simply:
SQL> create pfile from spfile
File created.

SQL> create spfile='+RACDB_DATA' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora';
File created.

echo "SPFILE='+DATA/Vishnu/parameterfile/spfile.268.966596385'" > /u01/app/oracle/product/11.2.0//dbhome_2/dbs/spfilevishnu.ora

ASMCMD [+DATA/Vishnu/parameterfile] > ls
spfile.268.966596385

/u01/app/oracle/product/11.2.0
/dbhome_2/dbs/spfilevishnu.ora

create spfile='+DATA/Vishnu/parameterfile\spfilevishnu.ora' from pfile='/u01/app/oracle/product/11.2.0/dbhome_2/initvishnu.ora';

Friday, 19 January 2018

How to create a pluggable database PDB in a multitenant container database CDB using the files of the seed PDB$SEED

The new architecture provided by Oracle Database 12c enables an Oracle database to function as a multitenant container database (CDB):
it can include zero, one, or many pluggable databases (PDBs), that is a portable collection of schemas, schema objects and nonschema objects.

A multitenant container database (CDB) is formed by the following components:
- the root container (and exactly one root) named CDB$ROOT that stores Oracle-supplied metadata and common users (a database user known in every container);

- the seed template (and exactly one seed) named PDB$SEED used, if you want, to create new PDBs. It's not possible to add objects to or modify objects in the seed: it works only in READ ONLY mode;

The options for creating a pluggable database (the so called PDB) fall into two main categories: copying and plugging in

create a pluggable database (PDB) in a multitenant container database (CDB) using the files of the seed (PDB$SEED);
create it by cloning a source PDB and plugging the clone into the CDB (bear in mind that the source PDB can be in the local CDB or in a remote CDB).

To create a new pluggable database copying the files of the seed you have to ensure that the current container is the root:

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

and your multitenant container database CDB must be also in read/write mode. My CDB is called CDB001:

SQL> select NAME, CDB, CON_ID, OPEN_MODE from V$DATABASE;

NAME   CDB   CON_ID OPEN_MODE
--------- --- ---------- --------------------
CDB001   YES        0 READ WRITE

Your script must include the CREATE PLUGGABLE DATABASE statement and eventually some other clauses, such as the following I used in my script:
- STORAGE: specify the limit of the amount of storage the PDB can use. Omitting this clause is equivalent to specify an unlimited amount;

- DEFAULT TABLESPACE: specify a default permanent tablespace for non-SYSTEM users. When you omit this clause the SYSTEM tablespace will be used as default permanent tablespace for non-SYSTEM users and this is not recommended;
- FILE_NAME_CONVERT: specify the target locations of the data files whereas the source files are those associated with the seed. This parameter is required when Oracle Managed Files is not enabled and the PDB_FILE_NAME_CONVERT initialization parameter is not set
- other clauses you can use are: ROLES, TEMPFILE REUSE and PATH_PREFIX.

SQL> CREATE PLUGGABLE DATABASE PDB002 
  2  ADMIN USER PDB002_ADMIN IDENTIFIED BY oracle
  3  storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M)
  4  DEFAULT TABLESPACE "USERS" DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON 
  5  file_name_convert=('/app/oracle/oradata/CDB001/pdbseed/system01.dbf','/app/oracle/oradata/CDB001/PDB002/system01.dbf','/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf','/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf','/app/oracle/oradata/CDB001/pdbseed/temp01.dbf','/app/oracle/oradata/CDB001/PDB002/temp01.dbf');

Pluggable database created.

From the alert log (vi /app/oracle/diag/rdbms/cdb001/CDB001/trace/alert_CDB001.log): 
Sat Jul 20 16:21:48 2013
CREATE PLUGGABLE DATABASE PDB002 ADMIN USER PDB002_ADMIN IDENTIFIED BY * storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M) DEFAULT TABLESPACE "USERS" DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON file_name_convert=('/app/oracle/oradata/CDB001/pdbseed/system01.dbf','/app/oracle/oradata/CDB001/PDB002/system01.dbf','/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf','/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf','/app/oracle/oradata/CDB001/pdbseed/temp01.dbf','/app/oracle/oradata/CDB001/PDB002/temp01.dbf')
Sat Jul 20 16:22:35 2013
****************************************************************
Pluggable Database PDB002 with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Deleting old file#2 from file$ 
Deleting old file#4 from file$ 
Adding new file#10 to file$(old file#2) 
Adding new file#11 to file$(old file#4) 
Successfully created internal service pdb002 at open
CREATE TABLESPACE USERS DATAFILE  '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON  SEGMENT SPACE MANAGEMENT AUTO
Completed: CREATE TABLESPACE USERS DATAFILE  '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON  SEGMENT SPACE MANAGEMENT AUTO
Sat Jul 20 16:22:49 2013
ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB002 with pdb id - 4 is now marked as NEW.
****************************************************************
Completed: CREATE PLUGGABLE DATABASE PDB002 ADMIN USER PDB002_ADMIN IDENTIFIED BY * storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M) DEFAULT TABLESPACE "USERS" DATAFILE '/app/oracle/oradata/CDB001/PDB002/PDB002_users01.dbf' SIZE 5M REUSE AUTOEXTEND ON file_name_convert=('/app/oracle/oradata/CDB001/pdbseed/system01.dbf','/app/oracle/oradata/CDB001/PDB002/system01.dbf','/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf','/app/oracle/oradata/CDB001/PDB002/sysaux01.dbf','/app/oracle/oradata/CDB001/pdbseed/temp01.dbf','/app/oracle/oradata/CDB001/PDB002/temp01.dbf')
As you can see when you create a pluggable database from the SQL*Plus command line it is open in MOUNTED mode. This is a different behaviour compared with the same operation performed by DBCA: in the final step DBCA is able to open the new pluggable database in READ WRITE mode. 
SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          MOUNTED     4
To open your new pluggable database in READ WRITE mode execute the following alter pluggable database command: 
SQL> alter pluggable database pdb002 open read write;

Pluggable database altered.
Now query again the V$PDBS view: your pluggable database is now available to the application. 
SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          READ WRITE    4

Restoring SQL server

https://www.mssqltips.com/sqlservertip/4110/solve-common-sql-server-restore-issues/

https://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/

1. Restore latest full backup with NORECOVERY(Backup file from USERDATABASES folder).
2. Restore latest Diff backup with NORECOVERY. (Backup file from Differential folder).
3. Restore Tlog files with NORECOVERY and the last Tlog file with RECOVERY (Tlog files from Transactional folder).

RESTORE DATABASE aate73 FROM DISK = 'D:\Restore\Backup\UserDatabases\ATE73\WC_cate73_1_20180111THU010000.BAK'

WITH NORECOVERY, REPLACE,

MOVE N'prdadvocate72' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\advocate73.mdf',

MOVE N'prdadvocate72_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\logs\advocate73_log.ldf'

RESTORE DATABASE advocate73 FROM DISK = 'D:\Restore\Backup\Differential\ADVOCATE73\WC_advocate73_1_20180111THU120001.BAK'

WITH NORECOVERY

RESTORE LOG advocate73 FROM DISK = 'WC_advocate73_1_20180111THU120000.TRN' WITH NORECOVERY

RESTORE LOG advocate73 FROM DISK = 'WC_advocate73_1_20180111THU121501.TRN' WITH NORECOVERY

RESTORE LOG advocate73 FROM DISK = 'WC_advocate73_1_20180111THU123000.TRN' WITH NORECOVERY

RESTORE LOG advocate73 FROM DISK = 'WC_advocate73_1_20180111THU124500.TRN' WITH NORECOVERY

RESTORE LOG advocate73 FROM DISK = 'WC_advocate73_1_20180111THU130000.TRN' WITH RECOVERY

Restore or backup status

SELECT r.session_id AS [Session_Id] ,r.command AS [command] ,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Complete] ,GETDATE() AS [Current Time] ,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time] ,CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min] ,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min] ,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated Hours] ,CONVERT(VARCHAR(1000), ( SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE WHEN r.statement_end_offset = - 1 THEN 1000 ELSE (r.statement_end_offset - r.statement_start_offset) / 2 END) 'Statement text' FROM sys.dm_exec_sql_text(sql_handle) )) FROM sys.dm_exec_requests r WHERE command like 'RESTORE%' or command like 'BACKUP%'

SELECT Req.percent_complete AS PercentComplete

,CONVERT(NUMERIC(6,2),Req.estimated_completion_time/1000.0/60.0) AS MinutesUntilFinish

,DB_NAME(Req.database_id) AS DbName,

Req.session_id AS SPID, Txt.text AS Query,

Req.command AS SubQuery,

Req.start_time AS StartTime

,(CASE WHEN Req.estimated_completion_time < 1

THEN NULL

ELSE DATEADD(SECOND, Req.estimated_completion_time / 1000, GETDATE())

END) AS EstimatedFinishDate

,Req.[status] AS QueryState, Req.wait_type AS BlockingType,

Req.blocking_session_id AS BlockingSPID

FROM sys.dm_exec_requests AS Req

CROSS APPLY sys.dm_exec_sql_text(Req.[sql_handle]) AS Txt

WHERE Req.command IN ('BACKUP DATABASE','RESTORE DATABASE') OR Req.command LIKE 'DBCC%';

File Movement in DR setup

on standby

declare @databaseName varchar(300)
set @databaseName = 'net_contentV3' -- Secondary Database Name
-- 0 = Restore log with NORECOVERY.
-- 1 = Restore log with STANDBY.
select secondary_database,
case restore_mode
when 0 then 'No Recovery'
when 1 then 'Stand by' end AS 'restore_mode'
from msdb.dbo.log_shipping_secondary_databases
where secondary_database = @databaseName

declare @databaseName varchar(300)
set @databaseName = 'Xnet_content_1_V3' -- Secondary Database Name
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(@databaseName);

go to primary

select the the database and right click --then transaction log --change to no recovery.

then open job monitor and disable the copy job and start the copyjob in production.

come to dr .. start the copy and restore job

ALTER DATABASE Xnet_content
MODIFY FILE ( NAME = wss_prod_contentdata3, FILENAME = 'L:\SQL01\MSSQL11.SQLX\MSSQL\DATA\wss_prod_content_1_V3_4.ndf' );

bring the db down.

copy the file physically and delete the old one.

bring up the db.

go to primary and enable the log shipping --change to standby.

start the copy

start the restore

check it is sync

Restore last refresh time in sql server

++++++++++++++++++++
Select Destination_database_name,
restore_date,
database_name as Source_database,
Physical_device_name as Backup_file_used_to_restore,
bs.user_name,
bs.machine_name
from msdb.dbo.restorehistory rh
inner join msdb.dbo.backupset bs
on rh.backup_set_id=bs.backup_set_id
inner join msdb.dbo.backupmediafamily bmf
on bs.media_set_id =bmf.media_set_id
ORDER BY [rh].[restore_date] DESC

USE msdb ;
SELECT
DISTINCT
DBRestored = destination_database_name ,
RestoreDate = restore_date ,
SourceDB = b.database_name ,
BackupDate = backup_start_date
FROM RestoreHistory h
JOIN MASTER..sysdatabases sd ON sd.name = h.destination_database_name
INNER JOIN BackupSet b ON h.backup_set_id = b.backup_set_id
INNER JOIN BackupFile f ON f.backup_set_id = b.backup_set_id
GROUP BY destination_database_name ,
restore_date ,
b.database_name ,
backup_start_date
ORDER BY RestoreDate DESC
GO

_________________________________________________________________________________

WITH LastRestores AS
(
SELECT
DatabaseName = [d].[name] ,
[d].[create_date] ,
[d].[compatibility_level] ,
[d].[collation_name] ,
r.*,
RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1

How to change the open mode of all pluggable databases

When you need to modify the open mode of all your PDBs at the same time (look at this post if you want to change the open mode of only a specific pluggable database) you can use ALTER PLUGGABLE DATABASE command and the ALL option.

As usual you have to ensure that the current container is the root.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

The following statement for example changes the open mode of all your pluggable databases at the same time.

SQL>ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          MOUNTED     3
PDB002          MOUNTED     4

When you need to change the open mode of all pluggable database except for listed ones you can include also the EXCEPT option as in the following example:

SQL> ALTER PLUGGABLE DATABASE ALL EXCEPT PDB001 OPEN READ WRITE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          MOUNTED     3
PDB002          READ WRITE    4

To open all my pluggable databases I can simply execute the following command. It doesn't take care that PDB002 is already open in READ WRITE mode: any error is returned because the pluggable databases are in different open mode.

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          READ WRITE    4

However, if any of your pluggable databases are in READ ONLY mode, then the statement returns the error "ORA-65019: pluggable database PDB001 already open" as you can see:

SQL> ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          MOUNTED     3
PDB002          MOUNTED     4

SQL> ALTER PLUGGABLE DATABASE PDB001 OPEN READ ONLY;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ ONLY    3
PDB002          MOUNTED     4

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;
ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE
*
ERROR at line 1:
ORA-65019: pluggable database PDB001 already open

Even if the statement fails for pluggable database PDB001 it was able to open PDB002 as requested:

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ ONLY    3
PDB002          READ WRITE    4

To avoid the error ORA-65019 you can include the FORCE option in your command:

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE FORCE;

Pluggable database altered.

SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS;

NAME          OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED         READ ONLY    2
PDB001          READ WRITE    3
PDB002          READ WRITE    4

To shutdown all your pluggable databases except one you can use the following command:

SQL> alter pluggable database all except PDB002 close immediate;

Pluggable database altered.

SQL> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB001          MOUNTED
PDB002          READ WRITE

To list some of your pluggable databases you can use the comma as in the following example where I need to open all my pluggable databases (PDB001 and PDB002) except those listed (just PDB001 and PDB002!!). Nothing happens of course.

SQL> alter pluggable database all except PDB002,PDB001 open;

Pluggable database altered.

SQL> select name, open_mode from V$PDBS;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PDB001          MOUNTED
PDB002          READ WRITE