MS SQL SERVER – Finding Backup status

–SQL SERVER – Finding Last Backup Time for All Database

SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),’-‘) AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name

——————————————————————————————-

— The following T-SQL Statement lists all of the databases in the server and the last day the backup happened.

SELECT db.name,
case when MAX(b.backup_finish_date) is NULL then ‘No Backup’ else convert(varchar(100),
MAX(b.backup_finish_date)) end AS last_backup_finish_date
FROM sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = ‘D’
WHERE db.database_id NOT IN (2)
GROUP BY db.name
ORDER BY 2 DESC

——————————————————————————————-

— The following T-SQL statement gets all the information related to the current backup location from the msdb database.

SELECT Distinct physical_device_name FROM msdb.dbo.backupmediafamily

Change or Set the MySQL Root password (Windows):

Leave a reply

Change or Set the MySQL Root password (Windows):
================================================

1. Stop your MySQL server completely. This can be done by accessing the Services
window inside Windows XP and Windows Server 2003, where you can stop the MySQL service.

2. Open your MS-DOS command prompt using “cmd” inside the Run window.
Inside it navigate to your MySQL bin folder, such as C:\MySQL\bin using the cd command.

3. Execute the following command in the command prompt: mysqld.exe -u root –skip-grant-tables

4. Leave the current MS-DOS command prompt as it is, and open a new MS-DOS command prompt window.

5. Navigate to your MySQL bin folder, such as C:\MySQL\bin using the cd command.

6. Enter “mysql” and press enter.

7. You should now have the MySQL command prompt working.
Type “use mysql;” so that we switch to the “mysql” database.

8. Execute the following command to update the password:

UPDATE user SET Password = PASSWORD(‘NewPassword’) WHERE User = ‘root’;

However, you can now run any SQL command that you wish.

After you are finished close the first command prompt and type “exit;” in the second command prompt
windows to disconnect successfully. You can now start the MySQL service.

Please note that the 8 step process above can differ depending on the MySQL version you are using,
how you configured your server, etc. However, many times you can still easily
work around a problem that you experience in any of the steps.

To change root password to an empty:
====================================

UPDATE user SET Password = PASSWORD(“”) WHERE User = ‘root’;

use mysql;
update user set password=null where User=’root’;
flush privileges;
quit;

How to Check and Repair MySQL Tables Using Mysqlcheck

Leave a reply

How to Check and Repair MySQL Tables Using Mysqlcheck
=====================================================

Database Name : sms
Table Name: attend

1. Check a Specific Table in a Database

mysqlcheck -c sms attend -u root -p

2. Analyze Tables using Mysqlcheck

# mysqlcheck -a sms attend -u root -p

3. Check All Tables and All Databases

# mysqlcheck -c -u root -p –all-databases

4. Optimize Tables using Mysqlcheck

# mysqlcheck -o sms attend -u root -p

5. Repair Tables using Mysqlcheck

# mysqlcheck -r sms attend -u root -p

SELECT table_name as name, table_rows as rows FROM information_schema.tables as t1 WHERE table_rows > 0

select * from (show table status like ‘%attend%’) as t1;

LIKE ‘attend’ G

MySQL Bin Files Eating Lots of Disk Space

Leave a reply

MySQL Bin Files Eating Lots of Disk Space
==========================================

I get a large amount of bin files in the MySQL data directory called “server-bin.n”
or mysql-bin.00000n, where n is a number that increments.

[root@ENMDB1 mysql]# df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/sda5 9.7G 8.2G 1.1G 89% /var

Solution:
========

We need to periodically RESET MASTER or PURGE MASTER LOGS to clear out the old logs.

For Safe side I taken backup of the main log files:
—————————————————
[root@ENMDB1 mysql]# cp slow-queries.log /mysqldb/DBBACKUP/slow-queries.log_bak
[root@ENMDB1 mysql]# cp mysql-query.log /mysqldb/DBBACKUP/mysql-query.log_bak

Purge BINARY LOGS Logs (leaving December BINARY LOGS)
Note: For retaing current month log files purge to mysql-bin.000469 (Nov 30 05:08 mysql-bin.000469).

mysql> PURGE BINARY LOGS TO ‘mysql-bin.000469’;
Query OK, 0 rows affected (40.85 sec)

[root@ENMDB1 mysql]# df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/sda5 9.7G 888M 8.3G 10% /var

The binary log has two important purposes:

Data Recovery : It may be used for data recovery operations.
After a backup file has been restored, the events in the binary
log that were recorded after the backup was made are re-executed.
These events bring databases up to date from the point of the backup.

High availability / replication : The binary log is used on master
replication servers as a record of the statements to be sent
to slave servers. The master server sends the events contained
in its binary log to its slaves, which execute those events to
make the same data changes that were made on the master.

Oracle11G Data Pump By Using Database Link

1 Reply

Scenario:
Directly importing the TEST01 schema in the production database (oraodrmu) to test database oraodrmt, over
a network by using database link and data pump in Oracle 11g.

Note: When you perform an import over a database link, the import source is a database, not a dump file set, and the data is imported to the connected database instance.
Because the link can identify a remotely networked database, the terms database link and network link are used interchangeably.

=================================================================
STEP-1 (IN PRODUCTION DATABASE – oraodrmu)
=================================================================

[root@szoddb01]>su – oraodrmu

Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant resource to test01;

Grant succeeded.

SQL> grant imp_full_database to test01;

Grant succeeded.

SQL> select owner,object_type,status,count(*) from dba_objects where owner=’TEST01′ group by owner,object_type,status;

OWNER OBJECT_TYPE STATUS COUNT(*)
—————————— ——————- ——- ———-
TEST01 PROCEDURE VALID 2
TEST01 TABLE VALID 419
TEST01 SEQUENCE VALID 3
TEST01 FUNCTION VALID 8
TEST01 TRIGGER VALID 3
TEST01 INDEX VALID 545
TEST01 LOB VALID 18

7 rows selected.

SQL>
SQL> set pages 999
SQL> col “size MB” format 999,999,999
SQL> col “Objects” format 999,999,999
SQL> select obj.owner “Owner”
2 , obj_cnt “Objects”
3 , decode(seg_size, NULL, 0, seg_size) “size MB”
4 from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
5 , (select owner, ceil(sum(bytes)/1024/1024) seg_size
6 from dba_segments group by owner) seg
7 where obj.owner = seg.owner(+)
8 order by 3 desc ,2 desc, 1
9 /

Owner Objects size MB
—————————— ———— ————
OND 8,097 284,011
SYS 9,601 1,912
TEST01 998 1,164

3 rows selected.

SQL> exit

=================================================================
STEP-2 (IN TEST DATABASE – oraodrmt)
=================================================================

[root@szoddb01]>su – oraodrmt

[oraodrmt@szoddb01]>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 3 18:40:16 2012

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ——————–
ODRMT READ WRITE

SQL> create tablespace test_test datafile ‘/trn_u04/oradata/odrmt/test01.dbf’ size 2048m;

Tablespace created.

SQL> create user test01 identified by test123 default tablespace test_test;

User created.

SQL> grant resource, create session to test01;

Grant succeeded.

SQL> grant EXP_FULL_DATABASE to test01;

Grant succeeded.

SQL> grant imp_FULL_DATABASE to test01;

Grant succeeded.

Note: ODRMU is the DNS hoste name.We can test the connection with: [oraodrmt@szoddb01]>sqlplus test01/test01@odrmu

SQL> create directory test_network_dump as ‘/dbdump/test_exp’;

Directory created.

SQL> grant read,write on directory test_network_dump to test01;

Grant succeeded.

SQL> conn test01/test123
Connected.

SQL> create DATABASE LINK remote_test CONNECT TO test01 identified by test01 USING ‘ODRMU’;

Database link created.

For testing the database link we can try the below sql:

SQL> select count(*) from OA_APVARIABLENAME@remote_test;

COUNT(*)
———-
59

SQL> exit

[oraodrmt@szoddb01]>impdp test01/test123 network_link=remote_test directory=test_network_dump remap_schema=test01:test01 logfile=impdp__networklink_grms.log;
[oraodrmt@szoddb01]>

Import: Release 11.2.0.2.0 – Production on Mon Dec 3 19:42:47 2012

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “TEST01”.”SYS_IMPORT_SCHEMA_01″: test01/******** network_link=remote_test directory=test_network_dump remap_schema=test01:test01 logfile=impdp_grms_networklink.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 318.5 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”TEST01″ already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported “TEST01″.”SY_TASK_HISTORY” 779914 rows
. . imported “TEST01″.”JCR_JNL_JOURNAL” 603 rows
. . imported “TEST01″.”GX_GROUP_SHELL” 1229 rows
. . . .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .
. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..
. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..
. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..

Job “TEST01”.”SYS_IMPORT_SCHEMA_01″ completed with 1 error(s) at 19:45:19

[oraodrmt@szoddb01]>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 3 19:46:04 2012

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select owner,object_type,status,count(*) from dba_objects where owner=’TEST01′ group by owner,object_type,status;

8 rows selected.

Owner Objects size MB
—————————— ———— ————
OND 8,065 247,529
SYS 9,554 6,507
TEST01 999 1,164

13 rows selected.

=================================================================
STEP-3 FOR REMOVING THE DATABASE LINK
=================================================================

[oraodrmt@szoddb01]>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 3 19:16:01 2012

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop database link remote_test;

Database link dropped.

Clone Oracle Database Using Hot Backup

2 Replies

1.First get the details of datafiles and archivelog location that present in the original database using the below command.

SQL> Select name from v$datafile;
SQL> archive log list;

2.Get the latest SCN by using the below command.

SQL> select max(first_change#) chng
2 from v$archived_log
3 /

CHNG
———-
424485

3.Bring the database in begin backup mode.

SQL> Alter database begin backup;

Database altered.

Note :- Make sure once you start begin backup mode there shouldn’t be No RMAN backup run on the database till you end the backup mode. Because till 10G if begin backup mode and RMAN backup is start at the same time there is a change of undo segment corruption and its a BUG reported by oracle support and I too faced the same problem, to resolve this we need downtime so be cautious.

4.Check the status whether the datafiles are in backup mode.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 ACTIVE 424935 22-FEB-12
2 ACTIVE 424935 22-FEB-12

5.Now copy the datafiles physically from the original location to the destination.
Example :
SQL> select name from v$datafile;

NAME
——————————————————————————–
/u04/app/oracle/product/10.2.0/oradata/test/system01.dbf
/u04/app/oracle/product/10.2.0/oradata/test/undotbs01.dbf
SQL>exit
$ cd /u04/app/oracle/product/10.2.0/oradata/test/
$ ls -lrt
-rw-r—– 1 oracle staff 209723392 Feb 22 12:29 undotbs01.dbf
-rw-r—– 1 oracle staff 429924352 Feb 22 12:29 system01.dbf
$ cp *.dbf /u03/oradata/clonedb/data

Note :- If you want to clone the database to the different server refer the end of the command for SCP (server level copy command)

6.After copying all the datafiles to destination location stop the backup mode in the original database.

SQL> alter database end backup;

Database altered.

7. Check the status of the datafiles.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 NOT ACTIVE 424935 22-FEB-12
2 NOT ACTIVE 424935 22-FEB-12

8.Archive the current logfile.

SQL> alter system archive log current;

System altered.

9.Get the details of archivelogs that will be needed for recovery while bringing up the clone database.

SQL> select name
2 from v$archived_log
3 where first_change# >= &change_no (424485)
4 order by name
5 /

Enter value for change_no: 424485
old 3: where first_change# >= &change_no
new 3: where first_change# >= 424485 (Enter the no which we got already ref #2)

NAME
——————————————————————————-
/u04/app/oracle/product/10.2.0/oradata/test/arch/1_26_775911007.dbf
/u04/app/oracle/product/10.2.0/oradata/test/arch/1_27_775911007.dbf

Copy the above listed archivelog files to the clone database archivelog location. (These logs are need for point in time recovery since we are cloning the database using hot backup)

10.Create Pfile from the spfile.

SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u04/app/oracle/product/10.2.0
/dbs/spfileprim.ora
SQL> create pfile from spfile;

File created.

11. In original database generate CREATE CONTROLFILE statement by typing the following command.

SQL>alter database backup controlfile to trace;
This will create a trace file containing the “CREATE CONTROLFILE” command to recreate the controlfile in text form.

12.Create the necessary directory on the clone database (destination database) server on your desired location.

Example :- mkdir udump adump cdump bdump arch

udump – user dump destination
bdump – background dump destination
adump – audit dump destination
cdump – core dump destination
arch – Archive log destination

13. Now, go to the USER_DUMP_DEST directory on the original Database server and open the latest trace file.The trace file will have the form “ora_NNNN.trc with NNNN being a number. This file will contain steps and as well as CREATE CONTROLFILE statement. Copy the CREATE CONTROLFILE statement and paste it in a notepad.

14. Edit the file

FROM: CREATE CONTROLFILE REUSE DATABASE “olddbname” RESETLOGS …
TO: CREATE CONTROLFILE set DATABASE “newdbname” RESETLOGS …

Change the word ‘REUSE’ to ‘set’ and the ‘olddbname’ to ‘newdbname’. Also change the datafiles location parameter to clone database location.

15. Now copy the pfile from the original database server to the clone database server and placed it under the $ORACLE_HOME/dbs location. Now open the parameter file in clone database and change the following parameters with the respective current location.

CONTROL FILES
BACKGROUND_DUMP_DEST
USER_DUMP_DEST
CORE_DUMP_DEST
LOG_ARCHIVE_DEST_1

16. In clone database SERVER export ORACLE_SID environment variable and start the instance

$export ORACLE_SID=clone database name
$sqlplus
Enter User:/ as sysdba
SQL> startup nomount pfile=’init.clonedb.ora’;

17.Run create controlfile script to create the controlfile

SQL>@createcontrolfile.sql

Control file created.

18.Check the status of the Database.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ———-
CLONE MOUNTED

19. Also check whether the clone database is pointing to its datafiles and dump files (just for verification)

SQL> select name from v$datafile;

NAME
——————————————————————————–
/u03/oradata/clone/data/system01.dbf
/u03/oradata/clone/data/undotbs01.dbf
/u03/oradata/clone/data/sysaux01.dbf
/u03/oradata/clone/data/users01.dbf

SQL> show parameter dump

NAME TYPE VALUE
———————————— ———– ——————————
background_core_dump string partial
background_dump_dest string /u03/oradata/clone/bdump
core_dump_dest string /u03/oradata/clone/cdump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /u03/oradata/clone/udump

20. Now media recovery is needed because we are cloning from the HOT backup. Follow the below setps.

SQL> recover database using BACKUP CONTROLFILE until cancel;

ORA-00279: change 424935 generated at 02/22/2012 12:29:27 needed for thread 1
ORA-00289: suggestion : /u03/oradata/clone/arch/1_27_775911007.dbf
ORA-00280: change 424935 for thread 1 is in sequence #27

21. Now we need to apply the necessary archive log files. (Refer the point no 9 & 10 apply all the archivelog files )

Specify log: {=suggested | filename | AUTO | CANCEL}
/u03/oradata/clone/arch/1_27_775911007.dbf (Give the archive location and file name)
ORA-00279: change 425120 generated at 02/22/2012 12:37:58 needed for thread 1
ORA-00289: suggestion : /u03/oradata/clone/arch/1_28_775911007.dbf
ORA-00280: change 425120 for thread 1 is in sequence #28
ORA-00278: log file ‘/u03/oradata/clone/arch/1_27_775911007.dbf’ no longer
needed for this recovery

22.Once applied all the neceesary archive log files give cancel .

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

23.Now open the database with open reset logs.

SQL> alter database open resetlogs;

Database altered.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ———-
CLONE READ WRITE

24. Get the latest SCN no in the clone database

SQL> select max(first_change#) chng
2 from v$archived_log
3 /

CHNG
———-
424488 (It will matches with the No if we got it on the Point no #2 )

Command for Server copy (SCP)

If you want to clone the database on the different server, for copying the file from source to destination we need to use SCP comand.

Syntax:

$ scp your_username@remotehost.edu:foobar.txt /some/local/directory

Where, username is your Clone DB server login user id
remotehost is CLONE DB host name
foobar.txt is your filename
/some/local/directory is your destination location
Example :
Consider you are in the source server location (ie., Original DB location IP address 10.250.27.234)

$ pwd

/u04/app/oracle/product/10.2.0/oradata/test/

$ ls -lrt

rw-r—– 1 oracle staff 209723392 Feb 22 12:29 undotbs01.dbf
rw-r—– 1 oracle staff 429924352 Feb 22 12:29 system01.dbf

$ scp oracle@10.251.55.123:undotbs01.dbf /u03/oradata/clone/data/

Where, oracle is your Clone DB server login user id
10.251.55.123 is CLONE DB host name
undotbs01.dbf is your filename
/u03/oradata/clone/data is your destination location

CLONE ORACLE DATABASE WITH COLD BACKUP:

Leave a reply

1. Identify and copy the database files

With the source database started, identify all of the database’s files. The following query will display all datafiles, tempfiles and redo logs:

set lines 100 pages 999

col name format a50

select name, bytes

from (select name, bytes

from v$datafile

union all

select name, bytes

from v$tempfile

union all

select lf.member “name”, l.bytes

from v$logfile lf

, v$log l

where lf.group# = l.group#) used

, (select sum(bytes) as poo

from dba_free_space) free

SQL>Select name from v$datafile;

SQL>Select member from v$logfile;

Make sure that the clone databases file-system is large enough and has all necessary directories.

If the source database has a complex file structure, you might want to consider modifying the

above sql to produce a file copy script.

Stop the source database with:

shutdown immediate

Copy, scp or ftp the files from the source database/machine to the target.

Do not copy the control files across. Make sure that the files have the correct permissions and ownership.

Start the source database up again

startup

2. Produce a pfile for the new database

This step assumes that you are using a spfile. If you are not, just copy the existing pfile.

From sqlplus:

create pfile=’init.ora’ from spfile;

This will create a new pfile in the $ORACLE_HOME/dbs directory.

Once created, the new pfile will need to be edited. If the cloned database is to have a new name,

this will need to be changed, as will any paths. Review the contents of the file and make

alterations as necessary.

Also think about adjusting memory parameters. If you are cloning a production database onto

a slower development machine you might want to consider reducing some values.

Now open the parameter file in clone database and change the following parameters with the respective current location.

CONTROL FILES

BACKGROUND_DUMP_DEST

USER_DUMP_DEST

CORE_DUMP_DEST

LOG_ARCHIVE_DEST_1

And Place the BST4 pfile on /$ORACLE_HOME/dbs

Note. Pay particular attention to the control locations.

3. Create the clone controlfile

Create a control file for the new database. To do this, connect to the source database and request a dump of the current control file. From sqlplus:

alter database backup controlfile to trace as ‘/home/oracle/cr_.sql’

4. Edit the file

FROM: CREATE CONTROLFILE REUSE DATABASE “BST2” RESETLOGS …

TO: CREATE CONTROLFILE set DATABASE “BST4” RESETLOGS …

Change the word ‘REUSE’ to ‘set’ and the ‘BST2’ to ‘BST4′. Also change the datafiles location parameter to BST4 database location.

CONTROL FILES

BACKGROUND_DUMP_DEST

USER_DUMP_DEST

CORE_DUMP_DEST

LOG_ARCHIVE_DEST_1

And Place the BST4 pfile on /DV1_u31/oraBST2/db/tech_st/10.2.0/dbs

5. In clone database SERVER export ORACLE_SID environment variable and start the instance

$export ORACLE_SID=bst4

$sqlplus

Enter User:/ as sysdba

SQL> startup nomount pfile=’initBST4.ora’;

6. Run create controlfile script to create the controlfile

SQL>@createcontrolfile.sql

Trouble shoot:

It is quite common to run into problems at this stage. Here are a couple of common errors and solutions:

ORA-01113: file 1 needs media recoveryYou probably forgot to stop the source database before copying the files.

Go back to step 1 and recopy the files.

ORA-01503: CREATE CONTROLFILE failed

ORA-00200: controlfile could not be created

ORA-00202: controlfile: ‘/u03/oradata/dg9a/control01.ctl’

ORA-27038: skgfrcre: file exists

Double check the pfile created in step 2. Make sure the control_files setting

is pointing at the correct location. If the control_file setting is ok, make sure that the control

files were not copied with the rest of the database files. If they were, delete or rename them.

7. Open the database

SQL>alter database open;

8. Perform a few checks

If the last step went smoothly, the database should be open.

It is advisable to perform a few checks at this point:

Check that the database has opened with:

select status from v$instance;

The status should be ‘OPEN’

Make sure that the datafiles are all ok:

select distinct status from v$datafile;

It should return only ONLINE and SYSTEM.

Take a quick look at the alert log too.

9. Set the databases global name

The new database will still have the source databases global name. Run the following to reset it:

alter database rename global_name to

10. Create a spfile

From sqlplus:

create spfile from pfile;

11. Change the database ID

If RMAN is going to be used to back-up the database, the database ID must be changed.

If RMAN isn’t going to be used, there is no harm in changing the ID anyway – and it’s a good practice to do so.

From sqlplus:

shutdown immediate

startup mount

exit

From unix:

nid target=/

NID will ask if you want to change the ID. Respond with ‘Y’. Once it has finished, start the database up again in sqlplus:

shutdown immediate

startup mount

alter database open resetlogs

12. Configure TNS

Add entries for new database in the listener.ora and tnsnames.ora as necessary.

13. Finished

That’s it!

Temporarily disabling the log shipping to standby database

Leave a reply

Temporarily disabling the log shipping to standby database.

$ sqlplus

SQL*Plus: Release 10.2.0.3.0 – Production on Wed Jul 25 21:07:57 2012

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options.
SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ———-
AIRMAN READ WRITE

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
8941

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
8942

SQL> select status, DEST_NAME, DESTINATION from v$archive_dest where status = ‘VALID’;
SQL> show parameter LOG_ARCHIVE_DEST_2

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_2 string SERVICE=airman_sj LGWR ASYNC V
ALID_FOR=(ONLINE_LOGFILE, PRIM
ARY_ROLE) db_unique_name=airma
n_sj

SQL> show parameter log_archive

NAME                                 TYPE        VALUE
———————————— ———– ——————————
log_archive_config                   string      DG_CONFIG=(airman_kj,airman_sj
)
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=/u01/oradata/airman/a
rchive VALID_FOR=(ALL_LOGFILES
, ALL_ROLES) db_unique_name=ai
rman_kj
log_archive_dest_10                  string
log_archive_dest_2                   string      SERVICE=airman_sj LGWR ASYNC V
ALID_FOR=(ONLINE_LOGFILE, PRIM
ARY_ROLE) db_unique_name=airma

NAME                                 TYPE        VALUE
———————————— ———– ——————————
n_sj
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      ENABLE
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_format                   string      %t_%s_%r.arc
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
SQL> alter system set log_archive_dest_state_2=defer scope=both;

System altered.

SQL> show parameter log_archive_dest_state_2

NAME TYPE VALUE
———————————— ———– ——-
log_archive_dest_state_2 string DEFER

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
8942

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
8943

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
8944

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
8945

How to switch the database to a new UNDO tablespace and drop the old one

Leave a reply

STEP -1
=======

$ sqlplus / as sysdba

SQL> show parameter undo

NAME                                 TYPE        VALUE
———————————— ———– ——————————
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL>

The current undo tablespace as suggested by the initialization parameter undo_tablespace is UNDOTBS1.
Leave this sysdba as is, open another console, log in as user SCOTT and initiate a transaction.

STEP -2
=======

— Create a new undo tablespace

CREATE UNDO TABLESPACE undotbs2
DATAFILE ‘/d01/apps/oradata/oraxpo/undotbs201.dbf’
SIZE 50M AUTOEXTEND ON NEXT 5M;

Tablespace created.

STEP -3
=======

— Switch the database to the new UNDO tablespace.

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

System altered.

STEP -4
=======

— Try to drop the tablespace but failed.

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use

With the alter system set undo_tablespace=UNDOTBS2, the database UNDO tablespace is changed and any
new transaction’s undo data will go to the new tablespace i.e. UNDOTBS2.
But the undo data for already pending transaction (e.g. the one initiated by SCOTT before the database
UNDO tablespace switch) is still in the old tablespace with a status of PENDING OFFLINE. As far as it
is there you cannot drop the old tablespace.

STEP -5
=======

— The query shows the name of the UNDO segment in the UNDOTBS1 tablespace and its status.
Now lets see which users/sessions are running this pending transaction.

set lines 10000
column name format a10

SELECT a.name,b.status
FROM v$rollname a,v$rollstat b
WHERE a.usn = b.usn
AND a.name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name = ‘UNDOTBS1’
);

NAME STATUS
———- —————
_SYSSMU8$ PENDING OFFLINE

column username format a6

SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name = ‘UNDOTBS1’
);

NAME STATUS USERNA SID SERIAL#
———- ————— —— ———- ———-
_SYSSMU8$ PENDING OFFLINE SCOTT 147 4

So this is SCOTT with SID=147 and SERIAL#=4. Since we know now the user, we can go to him/her and
request to end the transaction gracefully i.e. issue a ROLLBACK or COMMIT. However,
if this is not possible (say the user initiated the transaction and left for annual leave 🙂
and trust me this happens) you may go ahead and kill the session to release the undo
segments in the UNDOTBS1 tablespace.

SQL> alter system kill session ‘147,4’;

System altered.

no rows selected

As we can see once the session is kills we don’t see anymore segments occupied in the UNDOTBS1 tablespace.
Lets drop UNDOTBS1.

If you are retaining undo data then you still won’t be able to drop the tablespace because it is still in use by undo_retention.
Let the UNDO_RETENTION time pass and then try to drop the tablespace. In my case it is 900 seconds i.e. 15 minutes.

— After 15 minutes.

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Oracle best practice: Primary and Standby archive crosscheck

Leave a reply

Primary:

STEP -1
========

$ su – orapr1
Password:

STEP -2
=======

GET THE SEQUENCE MAX FROM V$LOG_HISTORY

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
76968

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
76969

SQL> exit

=========================================

ON STANDBY SERVER:

$ ps -ef|grep pmon
oratst 2978     1   0   Sep 08 ?         147:34 ora_pmon_amantst
oracle 3039     1   0   Sep 08 ?         137:34 ora_pmon_airman
e460269 16109 16104   0 18:54:44 pts/1       0:00 grep pmon

$ su – oracle

Password:
mesg: cannot change mode

$ sqlplus

SQL*Plus: Release 10.2.0.3.0 – Production on Thu May 17 18:55:10 2012

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
8548

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
8549

SQL> select * from v$archive_gap;
SQL> select sequence#, archived, applied, status from v$archived_log;

Shanoj

Learn.Share.Grow

MS SQL SERVER – Finding Backup status

Change or Set the MySQL Root password (Windows):

How to Check and Repair MySQL Tables Using Mysqlcheck

MySQL Bin Files Eating Lots of Disk Space

Oracle11G Data Pump By Using Database Link

Clone Oracle Database Using Hot Backup

CLONE ORACLE DATABASE WITH COLD BACKUP:

Temporarily disabling the log shipping to standby database

How to switch the database to a new UNDO tablespace and drop the old one

Oracle best practice: Primary and Standby archive crosscheck